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

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

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

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

• формулы связи, содержащие внешние ссылки;

• формулы, содержащие функции связи;

• команду Консолидация в меню Данные.

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

• установите курсор в итоговую ячейку;

• введите знак = (равно) для ввода формулы;

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

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

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

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

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

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

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

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

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

1. Если используются два способа консолидации данных, расположенных в одних и тех же строках, но в разных ячейках, то сначала производится консолидация без связи с исходными данными, а затем – со связью с исходными данными.

2. Консолидировать можно блоки ячеек. При выполнении консолидации, сначала выделяется итоговый блок ячеек, а затем блоки ячеек с исходными данными.

3. При выполнении консолидации без связи с исходными данными в итоговых ячейках будет число. То есть результат консолидации не изменится, при вводе новых исходных данных.

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

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

• выделите итоговые ячейки;

• в меню Данные выберите команду Консолидация;

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

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

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

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

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

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