КОНСОЛИДАЦИЯ ДАННЫХ

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

Можно связать консолидированные данные с исходными, чтобы последующие изменения в исходных листах отражались в итоговом листе. Или можно просто консолидировать исходные данные без создания связей.

Консолидировать данные можно по расположению и по категории. Если вы консолидируете данные по расположению, Excel собирает информацию из одинаково расположенных ячеек каждого исходного листа. При консолидации данных по категории Excel использует в качестве основы для объединения листов заголовки столбцов и строк.

Можно консолидировать листы из открытых в данный момент или находящихся на диске книг. Книга, содержащая лист, в который помещается консолидированные данные, должна быть открыта, но исходные книги могут быть закрыты.

После выполнения команды Консолидацияпоявляется диалоговое окно Консолидация.

В поле Функциявы можете выбрать подходящую функцию для использования при консолидации данных.

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

В поле Список диапазонов перечисляются все ссылки области-источника, которые были выбраны для консолидации.

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

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

Кнопка Обзор открывает окно диалога, в котором можно выбрать файл, содержащий области-источники.

Кнопка Добавить добавляет к консолидации ссылку на область-источник, определенную в поле Ссылка. Новая область-источник появляется в окне Список диапазонов.

Кнопка Удалитьудаляет ссылку области-источника, показанную в поле Ссылка.

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

Консолидированные данные будут представлены на Листе 1. Для выполнения консолидации нужно выполнить следующие операции.

1. Активизируйте итоговый рабочий лист.

2. Установите курсор в ячейку, которая будет левым верхним углом итоговой таблицы.

3. Выберете команду Консолидация.

4. В открывшемся диалоговом окне Консолидациясделайте необходимые установки.

5. Нажмите кнопку ОК.

Итоговая таблица будет выглядеть следующим образом.

Команду Консолидацияможно использовать для установления постоянной связи между итоговыми и исходными листами. Чтобы создать эту связь установите в диалоговом окне Консолидация флажок Создавать связи с исходными даннымии выполните консолидацию также, как и без связи.

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