- •2. Табличный процессор ms excel
- •2.1. Запуск и выход из ms excel
- •2.2. Основные элементы окна ms excel
- •Архитектура ms excel
- •2.4. Работа с файлами
- •2.5. Типы ссылок
- •Способы перемещения по ячейкам таблицы и листам
- •Выделение объектов в ms excel
- •2.8. Ввод данных в таблицу
- •2.9. Операции форматирования данных
- •2.10. Редактирование листов
- •2. 11. Связывание и консолидация данных
- •2.12. Работа со списком
- •2.12.1. Ввод, редактирование, поиск данных в списке
- •2.12.2. Использование фильтров
- •2.12.3. Сортировка списков
- •2.12.4. Создание промежуточных итогов для анализа списка
- •2.12.5. Сводные таблицы
- •2. 13. Построение диаграмм
- •Практические задания Задание 1. Ms Excel. Настройка экрана ms Excel. Создание рабочей таблицы.Форматирование и редактирование таблицы
- •2. Настройки ms Excel.
- •3. Способы перемещения в таблице
- •4. Способы выделения объектов таблицы
- •5. Создание учебной таблицы.
- •6. Редактирование учебной таблицы
- •7. Форматирование учебной таблицы.
- •8. Работа с таблицей
- •9. Сохранение рабочей книги
- •10. Завершение работы.
- •Задание 2. Ms Excel Консолидация данных. Построение диаграмм. Подготовка документа к печати
- •1. Открытие документа.
- •2. Консолидация данных.
- •3. Построение диаграмм.
- •4. Подготовка таблицы к печати
- •Задание 3. Работа с базой данных. Поиск данных. Фильтрация данных. Сортировка и расчет промежуточных итогов. Сводные таблицы
- •1. Работа с учебной базой данных.
- •2. Поиск данных
- •3. Фильтрация данных
- •4. Сортировка данных и подсчет промежуточных итогов
- •5. Сводные таблицы
- •6. Завершение работы
- •Пример учебной таблицы ms Excel
- •Пример учебной базы данных для ms Excel
2. 11. Связывание и консолидация данных
Связывание – это использование ссылок на исходные ячейки, которые могут находиться на одном или разных листах одной или нескольких рабочих книг.
Консолидация – это комбинирование, накопление или объединение данных. Объединять можно данные, расположенные на одном или нескольких листах одной и той же рабочей книги, а также в разных рабочих книгах. Объединение данных выполнятся путем суммирования, умножения и других действий над данными. Чаще всего данные консолидируются путем их суммирования.
Консолидацию данных можно выполнить различными методами, в том числе, используя:
формулы связи, содержащие внешние ссылки;
формулы, содержащие функции связи;
команду Консолидация в меню Данные.
Консолидация данных с помощью формулы связи. Для консолидации данных с помощью формулы связи необходимо выполнить следующие действия:
установите курсор в итоговую ячейку;
введите знак = (равно) для ввода формулы;
введите формулу с использованием знаков арифметических операций, в которой с помощью мыши указываются ссылки на исходные ячейки.
Например, просуммировать значения из ячеек С5 листов1,2 и 3. Результат поместить в ячейку С5 листа 4 одной и той же рабочей книги.
В ячейку Лист4!С5 введена формула = Лист1!С5 + Лист2!С5 + Лист3!С5
Консолидация данных с помощью функции связи. Данный метод консолидации можно применять, если рабочие таблицы находятся в одной рабочей книге и способы размещения информации в них идентичны. При этом можно воспользоваться любыми стандартными функциями. Ввод формул значительно упрощается, если в рабочей книге исходные листы расположены рядом.
Например, решить задачу суммирования можно с помощью формулы:
= СУММ(Лист1! : Лист5!С5)
Для формирования формулы следует ячейки, которые необходимо просуммировать выделить на соответствующих листах при помощи мыши. Для этого сначала выделяем ячейку на первом листе, а затем, не отпуская Shift, выделяем последний лист.
Объединение данных с помощью команды Консолидация во вкладке Данные. При этом консолидация может выполняться двумя способами: без связи с исходными данными и со связью с исходными данными.
Правила выполнения консолидации:
1. Если используются два способа консолидации данных, расположенных в одних и тех же строках, но в разных ячейках, то сначала производится консолидация без связи с исходными данными, а затем – со связью с исходными данными.
2. Консолидировать можно блоки ячеек. При выполнении консолидации, сначала выделяется итоговый блок ячеек, а затем блоки ячеек с исходными данными.
3. При выполнении консолидации без связи с исходными данными в итоговых ячейках будет число. То есть результат консолидации не изменится, при вводе новых исходных данных.
4. При выполнении консолидации со связью с исходными данными, в итоговых ячейках будет автоматически введена формула. То есть при изменении исходных данных автоматически будет изменен результат консолидации. Кроме того, при данном способе слева от заголовков строк, должны появиться символы структуры в виде «+» – плюсов. Нажав на «+», раскрываются исходные данные, которые были консолидированы.
Порядок выполнения консолидации:
выделите итоговые ячейки;
в меню Данные выберите команду Консолидация;
в диалоговом окне Консолидация задайте необходимые параметры.
Первоначально, очистите окно Список диапазонов, используя кнопку Удалить.
В раскрывающемся списке Функция выделите функцию для консолидации (Сумма, Среднее и т.д.).
В поле Ссылка с помощью мыши поочередно выделите исходные диапазоны и добавляйте их в список с помощью кнопки Добавить.
Включите или отключите флажок Создавать связи с исходными данными.
После указания всех параметров в окне Консолидация нажмите на кнопку ОК.