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

1.1Общие понятия

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

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

В Excel существует два основных метода консолидации данных – консолидация по расположению и консолидация по категориям (по заголовкам).

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

Пример 1. Пусть кафедры строительного факультета (строительных конструкций (СК), технологии и механизации строительства (ТиМС), оснований, фундаментов и инженерной геологии (ОФиИГ)) подают в деканат следующие сведения о студентах:

Составить отчет о составе студентов по факультету

Отчеты, представленные каждой из кафедр, имеют одинаковую структуру (есть одни и те же строки и столбцы, они расположены в одинаковом порядке), поэтому можно использовать консолидацию по расположению.

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

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

Пример 3. Пусть отделку 301 и 305 ауд. выполняют различные бригады по различной стоимости. До выполнения работ каждая бригада заполнила бланк стоимости услуг. Определить суммарные затраты по каждому виду работ, а также суммарный объем (площадь) каждого вида работ.

Для аудитории 305 бланк создан на листе 305.

Для аудитории 301 бланк создан на листе 301.

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

Диапазоны консолидации будут содержать столбцы Вид работы, Площадь, Суммарная стоимость и строки, содержащие данные по видам работ.

Для получения итоговой таблицы в примере можно:

1. Выполнить консолидацию по категориям и для строк и для столбцов.

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

Для строк выполнить консолидацию по расположению нельзя, так как количество строк (видов работ) в консолидируемых диапазонах различно.

Для выполнения консолидации в Excel предназначена команда Данные – Консолидация.