Консолидация (объединение) данных разных таблиц

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

3.6.1. Консолидация данных по расположению

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

Рассмотрим базу данных результатов экзаменационной сессии, состоящую из нескольких таблиц с оценками студентов по разным предметам.

Задание 1. Для назначения на стипендию необходимо консолидировать данные приведенных выше таблиц с вычислением средней и минимальной оценок каждого студента по итогам сессии. Ход выполнения работы:

Копируем столбцы с фамилиями, именами, отчествами и номером группы в новую область (например, начиная с ячейки А25), которая будет областью результатов консолидации. В ячейку Е25 вводим название нового столбца Средняя оценка, а в ячейку F25 название Минимальная оценка.

Примечание. Результирующая таблица может быть расположена как на текущем, так и на отдельном листе.

 

Щелкнем мышью в ячейке Е26, начиная с которой должны будут расположены результаты консолидации c вычислением средней оценки. Выполним команду меню Данные – Консолидация. В диалоговом окне из раскрывающегося списка в строке Функция выберем Среднее. Поместим курсор в строку Ссылка и в таблице Информатика мышью выделим первый диапазон данных для консолидации E3:E11, щелкнем по кнопке Добавить и выделенный диапазон отразится в строке Список диапазонов. Затем аналогичным образом добавим диапазоны данных K3:K11, E15:E23 и K15:K23 соответственно из таблиц История, Математика и Экономика. Нажмем кнопку ОК. Результаты консолидации с вычислением средней оценки будут помещены в ячейки E26:E34.

Щелкнем мышью в ячейке F26, начиная с которой должны будут расположены результаты консолидации c нахождением минимальной оценки. Выполним команду меню Данные – Консолидация. В диалоговом окне из раскрывающегося списка в строке Функция выберем Минимум. В строке Список диапазонов уже отражены выбранные для предыдущей консолидации диапазоны данных. Если какой-либо диапазон выбран неправильно, его можно убрать из списка, щелкнув на нем мышью и затем нажав кнопку Удалить. В нашем случае ничего в диапазонах данных менять не нужно, поэтому нажмем кнопку ОК. Результаты консолидации с нахождением минимальной оценки будут помещены в ячейки F26:F34.

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

Задание 2. Для формирования сводной ведомости суммировать данные по зарплате сотрудников за январь и февраль, используя приведенные ниже списки данных.

 

Ход выполнения работы:

Копируем столбец с фамилиями в новую область (например, начиная с ячейки А10), которая будет областью результатов консолидации. В ячейки В10:D10 копируем заголовки консолидируемых столбцов (фамилия, зарплата, подоход. налог, сумма к выдаче). Щелкнем мышью в ячейке В11, которая должна стать самой верхней левой ячейкой с результатами консолидации. Выполним команду меню Данные – Консолидация. В диалоговом окне из раскрывающегося списка в строке Функция выберем Сумма. Поместим курсор в строку Ссылка и в таблице Январь мышью выделим первый диапазон данных для консолидации B3:D8 (Примечание: поскольку функция – суммирование – едина для всех столбцов, выделяем их все сразу), щелкнем по кнопке Добавить. Аналогичным образом поместим в строку Список диапазонов диапазон данных G3:I8 из таблицы Февраль и нажмем кнопку ОК.

 

Результаты консолидации для Задания 2:

 

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

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

Задание 3. Для формирования сводной ведомости консолидировать (суммировать) данные по зарплате сотрудников за январь и февраль:

По сравнению с Заданием 2 в таблицу Февраль внесены следующие изменения: в феврале начислена премия и в списке фигурируют два новых сотрудника (по сравнению с январем).

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

Щелкнем мышью в ячейке А10, начиная с которой должна будет расположена таблица с результатами консолидации. Выполним команду меню Данные – Консолидация. В диалоговом окне из раскрывающегося списка в поле Функция выберем Сумма. Поместим курсор в поле Ссылка и в таблице Январь мышью выделим первый диапазон данных для консолидации А2:D8 (включая заголовки столбцов и левые ячейки строк), щелкнем по кнопке Добавить. Аналогичным образом поместим в поле Список диапазонов диапазон данных F2:J10 из таблицы Февраль. В рамке Использовать в качестве имен установим флажки (“галочки”) в подписи верхней строки и значения левого столбца. Нажмем кнопку ОК.

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

 

Результаты консолидации для Задания 3: