Консолидация данных в Excel с примерами использования

При выполнении ряда работ у юзера Microsoft Excel быть может сотворено несколько однотипных таблиц в одном файле либо в нескольких книжках.

Данные нужно свести воедино. Собрать в один отчет, чтоб получить общее представление. С таковой задачей совладевает инструмент «Консолидация».

Как создать объединение данных в Excel

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

Необходимо создать общий отчет при помощи «Консолидации данных». Поначалу проверим, чтоб

• макеты всех таблиц были схожими;
• наименования столбцов – схожими (допускается перестановка колонок);
• нет пустых строк и столбцов.

Спектры с начальными данными необходимо открыть.

Для консолидированных данных отводим новейший лист либо новейшую книжку. Открываем ее. Ставим курсор в первую ячейку объединенного спектра.

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

Перебегаем на вкладку «Данные». В группе «Работа с данными» жмем клавишу «Консолидация».

Раскрывается диалоговое окно вида:

На картинке открыт выпадающий перечень «Функций». Это виды вычислений, которые может делать команда «Консолидация» при работе с данными. Выберем «Сумму» (значения в начальных спектрах будут суммироваться).

Перебегаем к наполнению последующего поля – «Ссылка».

Ставим в поле курсор. Открываем лист «1 квартал». Выделяем таблицу вкупе с шапкой. В поле «Ссылка» покажется 1-ый спектр для консолидации. Жмем клавишу «Добавить»

Открываем попеременно 2-ой, 3-ий и 4-ый квартал – выделяем спектры данных. Нажимаем «Добавить».

Таблицы для консолидации показываются в поле «Перечень диапазонов».

Чтоб автоматом создать заглавия для столбцов консолидированной таблицы, ставим галочку напротив «подписи верхней строчки». Чтоб команда суммировала все значения по каждой неповторимой записи последнего левого столбца – напротив «значения левого столбца». Для автоматического обновления объединенного отчета при внесении новейших данных в начальные таблицы – напротив «создавать связи с начальными данными».

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

Для выхода из меню «Консолидации» и сотворения сводной таблицы жмем ОК.

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

Консолидация данных в Excel: практическая работа

Программка Microsoft Excel дозволяет делать различные виды консолидации данных:

1. По расположению. Консолидированные данные имеют однообразное размещение и порядок с начальными.
2. По группы. Данные организованы по различным принципам. Но в консолидированной таблице употребляются однообразные заглавия строк и столбцов.
3. По формуле. Используются при отсутствии неизменных категорий. Содержат ссылки на ячейки на остальных листах.
4. По отчету сводной таблицы. Употребляется инструмент «Сводная таблица» заместо «Консолидации данных».

Консолидация данных по расположению (по позициям) предполагает, что начальные таблицы полностью схожи. Схожие не только лишь наименования столбцов, да и наименования строк (см. пример выше). Если в спектре 1 «тахта» занимает шестую строчку, то в спектре 2, 3 и 4 это значение обязано занимать тоже шестую строчку.

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

Сделаны книжки: Магазин 1, Магазин 2 и Магазин 3. Структура схожа. Размещение данных идентично. Объединим их по позициям.

1. Открываем все три книжки. Плюс пустую книжку, куда будет помещена консолидированная таблица. В пустой книжке избираем верхний левый угол незапятнанного листа. Открываем меню инструмента «Консолидация».
2. Составим консолидированный отчет, используя функцию «Среднее».
3. Чтоб показать путь к книжкам с начальными спектрами, ставим курсор в поле «Ссылка». На вкладке «Вид» жмем клавишу «Перейти в другое окно».
4. Избираем попеременно названия файлов, выделяем спектры в открывающихся книжках – нажимаем «Добавить».

Примечание. Показать программке путь к начальным спектрам можно и при помощи клавиши «Обзор». Или средством переключения на открытую книжку.

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

1. Для сотворения объединенного спектра открываем меню «Консолидация». Избираем функцию «Сумма» (для примера).
2. Добавляем начальные спектры хоть каким из обрисованных выше методом. Ставим флажки у «значения левого столбца» и «подписи верхней строчки».
3. Жмем ОК.

Excel соединил информацию по трем магазинам по категориям. В отчете имеются данные по всем товарам. Независимо от того, продаются они в одном магазине либо во всех 3-х.

Примеры консолидации данных в Excel

На лист для сводного отчета вводим наименования строк и столбцов из консолидируемых диапазонов. Удобнее созодать это методом копирования.

В первую ячейку для значений объединенной таблицы вводим формулу со ссылками на начальные ячейки всякого листа. В нашем примере – в ячейку В2. Формула для суммы: =’1 квартал’!B2+’2 квартал’!B2+’3 квартал’!B2.

Копируем формулу на весь столбец:

Консолидация данных при помощи формул комфортна, когда объединяемые данные находятся в различных ячейках на различных листах. К примеру, в ячейке В5 на листе «Магазин», в ячейке Е8 на листе «Склад» и т.п.

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

Как свести данные? Консолидация данных в Excel

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

Консолидация в Excel

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

Обычный пример консолидированной отчетности — хоть какой квартальный отчет, приобретенный суммированием 3-х месячных отчетов.

Различают объединение по расположению и объединение по категориям. Различие заключается в степени упорядоченности начальных данных.

Консолидация по расположению

Для обобщения данных из разных таблиц, все эти таблицы должны быть схожими.

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

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

Сведение данных с помощью формул

Консолидирование данных предполагает внедрение какой-нибудь функции, к примеру, сумма либо произведение значений, поиск средних, малых и наибольших значений. Обычный свод данных из нескольких однотипных таблиц можно создать обыкновенными, обычными формулами с помощью функций «СУММ», «ПРОИЗВЕД», «МАКС», «МИН» и т.д.

Обычная консолидация

Для решения задач по сбору разрозненных данных в обобщенную таблицу, существует особый инструмент – «Консолидация» (клавишу можно отыскать на вкладке Данные/Работа с данными/Консолидация). Инструмент довольно мощнейший, дозволяет производить объединение как по расположению, так и по категориям, имеет наиболее 10 применяемых функций, дозволяет создавать связи с начальными данными. Все довольно просто, нажатие на клавишу вызывает окно «Консолидация», в котором выбирается функция, создается перечень из диапазонов начальных данных. При нарушении очередности строк и/либо столбцов в начальных спектрах, задаются адреса диапазонов, включающие в себя подписи строк и столбцов, также ставятся флажки в полях «Подписи верхней строчки» и «Значения левого столбца».

Консолидация с помощью надстройки

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

Для решения задач, выходящих за рамки способностей обычных средств Excel, можно применять надстройки – процедуры и функции, написанные на интегрированном в приложения Microsoft Office, языке программирования. Надстройки просто интегрируются в приложения и расширяют их обычные способности.

С помощью надстройки можно стремительно консолидировать данные из огромного количества рабочих книжек, по разным листам и спектрам.

Надстройка дозволяет:

1. Стремительно создавать перечень начальных рабочих книжек для консолидации;

2. Гибко настраивать листы, содержащие начальные данные, по их видимости, номерам, именам, наличию определенных значений и так дальше;

3. Задавать адреса на итоговом (активном) листе как для 1-го, так и для нескольких диапазонов ячеек;

4. Выбирать одну из более применяемых функций (сумма, произведение, максимум, минимум);

5. Выбирать тип сведения данных (по расположению либо по категориям).

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

Консолидация – это агрегирование (объединение) данных, представленных в начальных областях-источниках – таблицах, перечнях, блоках ячеек и др.

Консолидация данных (рис. 8.1 рис. 8.1) производится в согласовании с избранной функцией обработки. Итог консолидации находятся в области- предназначения. Таблица консолидации создается методом внедрения функции обработки к начальным значениям. Области-источники могут находиться на разных листах либо рабочих книжках их быть может до 2 8 = 256.

В Excel вероятны последующие варианты консолидации данных:

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

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

• все области на одном листе – в ссылках указывается адресок блока ячеек (к примеру, D1:C8 );
• области на различных листах – в ссылках указывается заглавие листа и спектр (к примеру, лист1 !D1:лист2!С8, т.е. с ячейки D1 листа 1по ячейку С8 листа 2);
• области в различных книжках, на различных листах – в ссылках указывается заглавие книжки, заглавие листа, спектр, к примеру [книга1]лист1! D1:[книга2]лист2!С8.

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

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

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

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

Производится команда Данные > Консолидация, выбирается вариант и задаются условия консолидации.

Пример 2. На рабочих листах представлена информация областей-источников в виде структуры на рисунке 8.3 рис. 8.3. Число строк и столбцов – переменное, состав характеристик и виды продуктов могут различаться либо совпадать, при консолидации по категориям они собираются вкупе. Цветом показана консолидируемая область источников.

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

Ссылка может иметь всякую из последующих форм:

Ссылки на ячейки	Источники и предназначение на одном листе
Ссылки на лист и ячейки	Источники и предназначение на различных листах
Ссылки на книжку, лист и ячейки	Источники и предназначение в различных книжках
Полный путь и все ссылки	Источники и предназначение в разных местах диска
Имя поименованной области	Область-источник поименована

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

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

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

Переключатель Создавать связи с начальными данными врубается при консолидации связи области предназначения к областям-источникам.

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

Примечание. Недозволено корректировать ссылки на области-источники (добавлять либо удалять новейшие области-источники) при наличии флага переключателя Создавать связи с начальными данными.

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

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

ЗАДАНИЕ

1. За ранее приготовить 2–е таблицы для консолидации по областям, к примеру продажа продуктов по кварталам в 1 и 2, по аналогии с примером (рис. 8.5 рис. 8.5). Выстроить итоговую таблицу консолидированную по областям.
2. За ранее приготовить 2 таблицы для консолидации по категориям (см. рис. 8.3 рис. 8.3) – дополнить 2-ую таблицу (копию) новеньким столбцом и новейшей строчкой. Выстроить итоговую таблицу, консолидированную по категориям.

Пояснения к выполнению

Консолидация по областям

1. Сделать таблицу собственного варианта («к примеру, Характеристики в 1 квартале») на новеньком листе, переименовать лист в «Консолидация» (два раза щелкнуть мышью по имени и ввести новое имя).

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

3. Выполнить объединение данных по расположению:

• установить курсор в первую ячейку области, где будет размещаться консолидированная таблица, к примеру в ячейку A10 ;
• выполнить команду Данные > Консолидация;
• в диалоговом окне Консолидация избрать из перечня функцию Сумма и установить флажки подписи верхней строчки и значения левого столбца;
• установить курсор в окне Ссылка и выделить блок ячеек А2:D7 (характеристики в 1 квартале);
• надавить клавишу Добавить, в окне Перечень диапазонов покажется ссылка на выделенный спектр;
• установить курсор в окне Ссылка, удалить прежнюю запись и выделить блок ячеек F2 :I7 (характеристики во 2 квартале);
• надавить клавишу Добавить, в окне Перечень диапазонов покажется ссылка на выделенный спектр;
• надавить клавишу ОК и сопоставить приобретенные результаты с рисунка8.5.

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

1. Добавить строчку с продуктом TV-тюнер с надлежащими числами в первую таблицу «Характеристики в 1 квартале» (рис. 8.6 рис. 8.6).

2. Вставить новейший столбец с именованием % реализации и заполнить формулой Продано*100/ Получено. Чтоб выводилось 2 знака опосля запятой, в меню Формат > Ячейки, вкладка Число избрать в поле Числовые форматы строчку Числовой и установить Число десятичных символов – 2;