Excel. Консолидация данных. Функции работы с базой данных

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

Существует несколько способов консолидации:

1. Консолидация с использованием трехмерных ссылок;

2. Консолидация по расположению;

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

Наиболее широкие возможности представляет консолидация с использованием трехмерных ссылок. При использовании этого способа консолидации отсутствуют ограничения на расположение данных в исходных областях.

Для консолидации с помощью трехмерных ссылок необходимо выполнить следующие шаги:

Создать заготовку таблицы консолидации;

Для каждой ячейки в этой таблице вызвать командой Данные – Консолидация диалоговое окно Консолидация и добавить в него ссылки на ячейки, содержащие данные для консолидации.

Недостатком консолидации с использованием трехмерных ссылок является высокая трудоемкость создания консолидированной таблицы

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

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

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

Функция – позволяет выбрать одну из стандартных функций, которые будут использоваться для консолидации;

поле Ссылка – используется для указания ссылки на ячейку или диапазон ячеек с исходными данными для консолидации;

Список диапазонов – после указания после указания ссылки в поле Ссылка она добавляется в Список диапазонов кнопкой Добавить. В результате он будет содержать все диапазоны с исходными данными для консолидации;

флажок Подписи верхней строки – используется при консолидации по категориям для указания того, что верхняя строка содержит заголовки;

флажок Значения левого столбца – используется аналогично предыдущему, но указывает, что заголовки содержатся в левом столбце;

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

Результаты консолидации при создании связей с исходными данными являются структурированными. Линии структуры отображаются левее заголовков строк или выше заголовков столбцов. Структурирование позволяет отображать только строки или столбцы с итоговыми значениями, при необходимости можно раскрыть структуру и отобразить те данные, на основе которых были рассчитаны итоговые значения. Структура допускает использовать до 8 уровней детализации.