Excel. Консолидация данных. Функции работы с базой данных
Если данные по однотипным объектам находятся на различных рабочих листах или в различных книгах, то для их совместного анализа возникает необходимость объединить их в одну таблицу. Подобная операция называется консолидацией. При консолидации данные по различным объектам каким либо образом обобщаются, например, суммируются. Консолидация в Excel обычно выполняется с использованием одной из стандартных функций: сумма, среднее, максимум, минимум. Консолидированная таблица обычно сохраняет связь с исходными таблицами и при внесении в них изменений автоматически пересчитывается.
Существует несколько способов консолидации:
1. Консолидация с использованием трехмерных ссылок;
2. Консолидация по расположению;
3. Консолидация по категориям.
Наиболее широкие возможности представляет консолидация с использованием трехмерных ссылок. При использовании этого способа консолидации отсутствуют ограничения на расположение данных в исходных областях.
Для консолидации с помощью трехмерных ссылок необходимо выполнить следующие шаги:
Создать заготовку таблицы консолидации;
Для каждой ячейки в этой таблице вызвать командой Данные – Консолидация диалоговое окно Консолидация и добавить в него ссылки на ячейки, содержащие данные для консолидации.
Недостатком консолидации с использованием трехмерных ссылок является высокая трудоемкость создания консолидированной таблицы
В том случае, если исходные данные по различным объектам размещены в одинаковом порядке, то удобно использовать консолидацию по расположению. Например, этот способ можно использовать для консолидации данных нескольких листов, созданных на основе одного шаблона. В отличие от консолидации с помощью трехмерных ссылок такой способ консолидации создает результирующую таблицу не по отдельным ячейкам, а сразу целиком.
Консолидация по категориям используется в том случае, когда требуется обобщить данные из таблиц, имеющих одинаковые заголовки строк или столбцов. Порядок консолидации сохраняется прежним, но дополнительно необходимо использовать флажки из группы. Использовать в качестве имен для указания вида заголовков.
Для выполнения любого вида консолидации используется диалоговое окно Консолидация. Оно вызывается командой Данные – Консолидация. Это окно содержит следующие элементы:
Функция – позволяет выбрать одну из стандартных функций, которые будут использоваться для консолидации;
поле Ссылка – используется для указания ссылки на ячейку или диапазон ячеек с исходными данными для консолидации;
Список диапазонов – после указания после указания ссылки в поле Ссылка она добавляется в Список диапазонов кнопкой Добавить. В результате он будет содержать все диапазоны с исходными данными для консолидации;
флажок Подписи верхней строки – используется при консолидации по категориям для указания того, что верхняя строка содержит заголовки;
флажок Значения левого столбца – используется аналогично предыдущему, но указывает, что заголовки содержатся в левом столбце;
флажок Создать связь с исходными данными – установка этого флажка позволяет создать таблицу консолидации, содержащую ссылки на исходные данные. В этом случае при изменении исходных данных автоматически будет изменяться и таблица консолидации. Дополнительно создается структура.
Результаты консолидации при создании связей с исходными данными являются структурированными. Линии структуры отображаются левее заголовков строк или выше заголовков столбцов. Структурирование позволяет отображать только строки или столбцы с итоговыми значениями, при необходимости можно раскрыть структуру и отобразить те данные, на основе которых были рассчитаны итоговые значения. Структура допускает использовать до 8 уровней детализации.