Связывание и консолидация данных

Связывание — это использование ссылок на исходные ячейки, которые могут находиться на одном или разных листах одной или нескольких рабочих книг.

Консолидация — это комбинирование, накопление или объединение данных. Объединять можно данные, расположенные на одном или нескольких листах одной и той же рабочей книги, а также в разных рабочих книгах. Объединение данных выполнятся путем суммирования, умножения и других действий над данными. Чаще всего данные консолидируются путем их суммирования.

Консолидацию данных можно выполнить различными методами, в том числе, используя:

• · формулы связи, содержащие внешние ссылки;
• · формулы, содержащие функции связи;
• · команду Консолидация в меню Данные.

Консолидация данных с помощью формулы связи. Для консолидации данных с помощью формулы связи необходимо выполнить следующие действия:

• · установите курсор в итоговую ячейку;
• · введите знак = (равно) для ввода формулы;
• · введите формулу с использованием знаков арифметических операций, в которой с помощью мыши указываются ссылки на исходные ячейки.

Например, просуммировать значения из ячеек С5 листов 1, 2 и 3. Результат поместить в ячейку С5 листа 4 одной и той же рабочей книги.

В ячейку Лист4! С5 введена формула = Лист1! С5 + Лист2! С5 + Лист3! С5.

Консолидация данных с помощью функции связи. Данный метод консолидации можно применять, если рабочие таблицы находятся в одной рабочей книге и способы размещения информации в них идентичны. При этом можно воспользоваться любыми стандартными функциями. Ввод формул значительно упрощается, если в рабочей книге исходные листы расположены рядом.

Например, решить задачу суммирования можно с помощью формулы:

= СУММ (Лист1!: Лист5! С5).

Для формирования формулы следует ячейки, которые необходимо просуммировать выделить на соответствующих листах при помощи мыши. Для этого сначала выделяем ячейку на первом листе, а затем, не отпуская Shift, выделяем последний лист.

Объединение данных с помощью команды Консолидация во вкладке Данные. При этом консолидация может выполняться двумя способами: без связи с исходными данными и со связью с исходными данными.

Правила выполнения консолидации:

• 1. Если используются два способа консолидации данных, расположенных в одних и тех же строках, но в разных ячейках, то сначала производится консолидация без связи с исходными данными, а затем — со связью с исходными данными.
• 2. Консолидировать можно блоки ячеек. При выполнении консолидации, сначала выделяется итоговый блок ячеек, а затем блоки ячеек с исходными данными.
• 3. При выполнении консолидации без связи с исходными данными в итоговых ячейках будет число. То есть результат консолидации не изменится, при вводе новых исходных данных.
• 4. При выполнении консолидации со связью с исходными данными, в итоговых ячейках будет автоматически введена формула. То есть при изменении исходных данных автоматически будет изменен результат консолидации. Кроме того, при данном способе слева от заголовков строк, должны появиться символы структуры в виде «+» — плюсов. Нажав на «+», раскрываются исходные данные, которые были консолидированы.

Порядок выполнения консолидации:

• · выделите итоговые ячейки;
• · в меню Данные выберите команду Консолидация;
• · в диалоговом окне Консолидация задайте необходимые параметры.

Первоначально, очистите окно Список диапазонов, используя кнопку Удалить.

В раскрывающемся списке Функция выделите функцию для консолидации (Сумма, Среднее и т. д.).

В поле Ссылка с помощью мыши поочередно выделите исходные диапазоны и добавляйте их в список с помощью кнопки Добавить.

Включите или отключите флажок Создавать связи с исходными данными.

После указания всех параметров в окне Консолидация нажмите на кнопку ОК.