Консолидация данных
Консолидация - это получение итоговой информации путем объединения данных из нескольких исходных таблиц в одной итоговой таблице. Консолидированная таблица создается путем определенной функциональной обработки данных из исходных таблиц, например, путем их суммирования. Консолидация используется, например, для подведения итогов деятельности организации за определенный период.
Таблицы с исходными данными для консолидации могут находиться на одном листе рабочей книги, на различных листах или в разных рабочих книгах (одновременно можно консолидировать до 255 таблиц).
Наиболее распространены следующие способы консолидации данных:
с помощью формул со ссылками на исходные диапазоны;
по расположению (для данных одинаковым расположением и структурой);
• по категориям (для одинаковых данных, отличающихся по своей структуре).
При консолидации данных с помощью формул исходные диапазоны указываются в ячейках с формулами консолидированной таблицы в виде ссылок. При этом исходные диапазоны могут быть расположены произвольно, например, в разных местах и на разных листах нескольких рабочих книг.
При консолидации по расположению, однотипные данные во всех исходных диапазонах должны иметь одинаковую структуру. Для консолидации таких данных курсор устанавливается в левый верхний угол области данных результирующей таблицы и выполняется команда Данные - Консолидация. В появившемся на экране диалоговом окне (рис. 34) сначала нужно выбрать функцию консолидации в списке Функция (на рисунке выбрана функция Сумма), а затем указать, какие данные нужно консолидировать. Для этого следует щелкнуть мышью по кнопке в строке Ссылка (при этом окно сожмется до размеров строки), а затем по ярлычку листа с данными и выделить нужный диапазон ячеек. После этого следует восстановить диалоговое окно (щелкнуть мышью по кнопке в строке Ссылка) и добавить выделенный диапазон в Список диапазонов, щелкнув мышью по кнопке Добавить. Другие диапазоны добавляются в Список диапазонов таким же способом. Переключателем Создавать связи с исходными данными можно установить динамическую связь результирующей таблицы с исходными данными, при изменении которых будут автоматически обновляться результаты консолидации. Для завершения формирования результирующей консолидированной таблицы в диалоговом окне нужно щелкнуть по кнопке ОК.
Рисунок 34. Диалоговое окно Консолидация данных.
При консолидации по категориям области-источники содержат однотипные данные с разной структурой. Например, на одном листе рабочей книги имеется таблица заработной платы сотрудников отдела (рис. 35а) до его расширения (3 человека), а на другом листе - аналогичная таблица без премии (рис. 35б) после приема на работу еще двух человек.
ФИО |
Зарплата |
Налог |
Премия |
К выдаче |
|
ФИО |
Зарплата |
Налог |
К выдаче |
Курочкин |
3000 |
390 |
1000 |
3610 |
|
Курочкин |
3000 |
390 |
2610 |
Уточкин |
4000 |
520 |
1000 |
4480 |
|
Уточкин |
4000 |
520 |
3480 |
Гуськов |
5000 |
650 |
1000 |
5350 |
|
Гуськов |
5000 |
650 |
4350 |
|
|
|
|
|
|
Петушков |
2000 |
260 |
1740 |
|
|
|
|
|
|
Цыплаков |
1500 |
195 |
1305 |
Рисунок 35. Исходные таблицы для консолидации.
ФИО |
Зарплата |
Налог |
Премия |
К выдаче |
Цыплаков |
1500 |
195 |
|
1305 |
Уточкин |
8000 |
1040 |
1000 |
7960 |
Петушков |
2000 |
260 |
|
1740 |
Курочкин |
6000 |
780 |
1000 |
6220 |
Гуськов |
10000 |
1300 |
1000 |
9700 |
Рисунок 36. Результат консолидации. |
||||