Консолидация данных

Консолидация данных состоит в создании итоговой таблицы, позволяющей обобщить однородные данные. Например, можно произвести суммирование данных по товарам одних и тех же наименований, хранящихся на разных складах (рис. 18.14). При консолидации значения, приведенные в итоговой таблице, могут рассчитываться на основе исходных данных с использованием различных функций Excel.
Исходные данные (области) могут располагаться на одном или разных листах, в других открытых книгах. При консолидации можно создать связи, обеспечивающие автоматическое обновление данных в итоговой таблице (области назначения) при изменении данных в исходных областях.
Консолидацию данных можно произвести по расположению ячеек, содержащих исходные данные, по категориям, с помощью трехмерных ссылок, сводной таблицы и т.д; При этом во всех исходных диапазонах данные должны быть расположены в одинаковом порядке. Так, если мы рассматриваем количество товаров одного наименования на разных складах, то во всех отчетах, представленных разными складами, строки и столбцы таблиц отчетов должны быть расположены в одинаковом порядке.

Рис. 18.14 Пример таблицы исходных данных для консолидации

Консолидация данных по расположению

Диапазоны, данные которых консолидируются и помещаются в указанный конечный диапазон, называются исходными областями. Исходные области могут располагаться на любом листе или книге, на других открытых листах или книгах, а также на листах Lotus 1-2-3.
Если во всех ведомостях количество упаковок Рыбные продукты приводится в ячейке F3, то при консолидации в итоговой ведомости будут обобщены значения, хранящиеся в этой ячейке во всех ведомостях. Области консолидации

Рис. 18.15 Диалоговое окно Консолидация можно задать либо трехмерными формулами, либо в поле Ссылка (Reference) (рис. 18.15).-

В диалоговом окне Консолидация в раскрывающемся списке Функция (Function) выберите итоговую функцию, которую следует использовать для обработки данных, например, Сумма (Sum) , если будут суммироваться значения. Описание функций, приведенных в списке, дано в таблице 18.3.

Таблица 18.3. Итоговые функции, используемые при построении сводных таблиц и сводных диаграмм

После установки курсора в поле Ссылка (Reference) введите ссылку на первый диапазон данных, который консолидируется в указанный конечный диапазон. Ссылку введите вручную или, если лист, содержащий новую исходную область, является текущим, выделите на нем исходную область. Если исходные области и область назначения находятся на разных листах, используйте имя листа и имя или ссылку на диапазон. Чтобы убрать диалоговое окно Консолидация (Consolidate) на время выбора исходной области, нажмите кнопку Свернуть диалоговое окно (Collapse dialog) в правой части поля. Повторное нажатие на эту кнопку восстанавливает окно.
Если исходные данные находятся в другой книге, которая в данный момент закрыта, нажмите кнопку Обзор (Browse) и выберите книгу. Путь к выбранной ссылке отобразится в поле Ссылка (Reference). Затем добавьте ссылку.
Нажмите кнопку Дoбaвumъ(Add ). Введенная ссылка отобразится в окне Список диапазонов (All references). Повторите эту операцию для всех консолидируемых исходных областей.
Чтобы автоматически обновлять итоговую таблицу при изменении источников данных, установите флажок Создавать связи с исходными данными (Create links to source data). Связи нельзя использовать, если исходная область и область назначения находятся на одном листе. После установки связей нельзя добавлять новые исходные области и изменять исходные области, уже входящие в консолидацию.
При консолидации данных по расположению заголовки категорий исходных областей не копируются автоматически в область назначения. Если в области назначения требуется разместить заголовки, скопируйте или введите их вручную. .

Консолидация данных по категориям

Выберите из раскрывающегося списка Функция (Function) функцию, которую следует использовать для обработки данных (рис. 18.15). Используемые итоговые функции приведены в таблице 18.3. Введите исходную область консолидируемых данных в поле Ссылка (Reference) . Убедитесь, что исходная область имеет заголовок.
Нажмите кнопку Добавить (Add) для добавления диапазона к списку исходных диапазонов консолидации. Повторите эту операцию для всех консолидируемых исходных областей. В наборе флажков Использовать в качестве имен (Use labels in) установите флажки, соответствующие расположению в исходной области заголовков: в верхней строке, в левом столбце или в верхней строке и в левом столбце одновременно.
Чтобы автоматически обновлять итоговую таблицу при изменении источников данных, установите флажок Создавать связи с исходными данными (Create links to source data). Связи нельзя использовать, если исходная область и область назначения находятся на одном листе. После установки связей нельзя добавлять новые исходные области и изменять исходные области, уже входящие в консолидацию.

Содержание раздела