Материал: 64
51
Консолидация данных
Понятие консолидации
Консолидация позволяет обобщать однородные данные, расположенные на одном или разных листах рабочей книги или даже в нескольких рабочих книгах.
Консолидация выполняется различными способами: по расположению или категориям данных, с помощью создания сводных таблиц. Можно предусмотреть сохранение связи между исходными и консолидированными данными, при этом все изменения информации в исходных диапазонах ячеек будут отображаться автоматически в консолидированной таблице.
Для выполнения консолидации по расположению или категориям следует установить указатель в ячейке, которая будет левым верхним углом диапазона, планируемого для вывода результатов консолидации. Затем нужно нажать кноп-
ку Консолидация в группе Работа с данными на вкладке ленты Данные. Поя-
вится диалоговое окно Консолидация, с помощью которого требуется определить значения параметров консолидации (рисунок 34).
Рисунок 34 – Диалоговое окно для определения параметров консолидации
В открывающем списке Функция: следует выбрать операцию, которая будет применяться для обработки данных (например, Сумма или Максимум). С помощью поля Ссылка указываются диапазоны ячеек, в которых расположены исходные данные. Эти действия выполняются для каждого диапазона по очереди. После выделения диапазона и нажатия кнопки Добавить, координаты диапа-
52
зона отобразятся в области Список диапазонов:. Ошибочно выбранный диапазон исключается из списка диапазонов с помощью кнопки Удалить. Если этого требуют условия решаемой задачи, можно включить флажок Создавать связи с исходными данными. Процесс консолидации данных завершается нажатием в диалоговом окне Консолидация кнопки ОК.
Консолидация по расположению
Этот вид консолидации требует, чтобы все исходные таблицы имели одинаковую структуру. Например, на рисунке 35 представлены данные о валютных операциях банка, имеющего три филиала. Порядок расположения названий валют и сведений о том, какая операция совершалась (купля или продажа), для каждого филиала идентичны.
Рисунок 35 – Консолидация данных по расположению (выделены диапазоны с исходными данными и ячейка, указанная в качестве левого верхнего угла диапазона для вывода полученных результатов)
53
При выполнении консолидации по расположению флажки подписи верх-
ней строки и значения левого столбца группы Использовать в качестве имён
в диалоговом окне Консолидация необходимо выключить. Обратите внимание, что в диапазоны с исходными данными можно не включать названия строк и столбцов таблиц с информацией для каждого филиала. Независимо от того, будут выделены названия или нет, отобразятся только консолидированные данные, подписи столбцов и строк придётся копировать или вводить вручную.
Консолидация по категориям
Этот вид консолидации не требует, чтобы исходные таблицы имели одинаковую структуру – консолидация реализуется по названиям строк и столбцов диапазонов (рисунок 36).
Рисунок 36 – Консолидация данных по категориям (выделены диапазоны с исходными данными и ячейка, указанная в качестве левого верхнего угла диапазона для вывода полученных результатов)
54
При консолидировании данных по категориям названия строк и столбцов таблиц с исходными данными необходимо выделить (рисунок 36). В диалоговом окне Консолидация обязательно следует включить флажки подписи верхней строки и значения левого столбца (рисунок 36).
В таблице с данными, консолидированными по категориям, названия строк и столбцов будут созданы MS Excel автоматически (вручную потребуется ввести только текст Валюта).
Консолидация данных с помощью сводных таблиц
При выполнении этого вида консолидации данные обобщаются только по категориям. Процесс консолидации реализуется с помощью мастера сводных таблиц и диаграмм.
Рационально добавить кнопку для запуска мастера на панель быстрого доступа. Для этого нужно выполнить следующие действия:
–нажать кнопку со стрелкой рядом с панелью быстрого доступа и выпол-
нить команду Дополнительные команды;
–в открывающемся списке Выбрать команды из: диалогового окна Па-
раметры Excel выбрать пункт Все команды;
–из предложенного списка команд выбрать команду Мастер сводных таблиц и диаграмм и нажать кнопку Добавить;
–нажать кнопку ОК.
После запуска с помощью созданной кнопки мастера сводных таблиц и диаграмм на экране появится окно для указания источника, в котором находятся данные для построения сводной таблицы. На этом шаге работы мастера следует установить переключатель Создать таблицу на основе данных, находящихся в: в положение в нескольких диапазонах консолидации.
На шаге 2а работы мастера определяется, как будут созданы поля в области страницы макета сводной таблицы (в MS Excel 2007 эта область называется Фильтр отчёта). Нужно установить переключатель в положение Создать поля страницы.
На шаге 2б работы мастера формируется список диапазонов исходных данных для консолидации. В этом же диалоговом окне указывается количество полей страницы сводной таблицы (в рассматриваемом примере переключатель следует установить в положение 1, так как планируется, что фильтрация сводной таблицы по страницам будет выполняться только по филиалам банка). После
55
этого для каждого диапазона в поле со списком Первое поле: вводится название элемента (в примере Северный, Южный, Восточный).
На третьем шаге работы мастера указывается, где будет создана сводная таблица – на новом или существующем листе рабочей книги.
После нажатия кнопки Готово на рабочем листе отобразится созданная сводная таблица и будет активизировано диалоговое окно Список полей свод-
ной таблицы (рисунок 37).
Рисунок 37 – Результаты консолидации данных с помощью мастера сводных таблиц и диаграмм
Не исключено, что построенная с помощью мастера сводная таблица не будет соответствовать пожеланиям и требованиям пользователя. Например, в таблице, изображённой на рисунке 37, вместо содержательных заголовков полей выводятся надписи Страница 1, Названия столбцов и Названия строк, вместо