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

При работе в Microsoft Office Excel могут быть созданы однотипные таблицы, в которых совпадает шапка, строки и настройки форматирования, но отличаются значения. Редактор обладает встроенной функцией объединения данных из нескольких мест в одно. Сегодня рассмотрим такой инструмент как консолидация в excel.

Что это такое

Консолидация данных представляет собой совмещение значений нескольких листов на отдельном рабочем листе excel. Рассмотрим структуру:

1. На Панели управления переходите во вкладку Данные и ищете одноименную кнопку.

2. Открывается новое диалоговое окно, которое содержит настройки инструмента.

Поле Функция содержит различные критерии объединения значений. В частности Сумма возвращает результат сложения чисел из всех выделенных диапазонов.

В строке Ссылка отмечаете необходимое количество столбцов и строк на рабочем листе.

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

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

Блок настроек подписи содержит три позиции значение каждой из них следующее:

1. Подписи верхней строки используются для автоматического создания шапки итоговой таблицы.
2. Значение левого столбца нужно для суммирования чисел последнего столба слева.
3. Создавать связи с исходными данными нужно для того, чтобы новые значения, которые вносятся в первоначальные таблицы, были отражены в сводной.

Важно! Чтобы осталась возможность ручного редактирования консолидированной таблицы, последнюю галочку ставить не надо. Таким образом можно обновлять начальные и итоговые табличные области вне зависимости от выбранного диапазона для объединения.

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

Примеры использования

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

Решение:

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

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

Важно! Таблица должна быть пустой снизу и справа от выбранной ячейки.

1. Нажимаете ОК и видите результат консолидации – годовое количество проданных товаров и объем выручки за год по каждой единице.

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

Данная пошаговая инструкция наглядно показывает принцип и методы работы с инструментом редактора excel.

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

Как видите, функция консолидации полезна для учета товаров в магазинах, что значительно облегчает ведение бухгалтерии и помогает построить планы развития. Стоит отметить, что принцип работы и местонахождение инструмента в excel 2010, 2013 и 2016 годов идентичны. Вариантов применения этого инструмента достаточно много. Данные можно объединять по расположению, категории или формуле. Все зависит от условий поставленной задачи.