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

Что такое консолидация данных

Консолидация аналогична консолидации. В Excel это функция, позволяющая объединить несколько диапазонов в один, связать несколько листов, находящихся в нескольких файлах.

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

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

1

Если мы загрузим всю эту информацию в Excel, то увидим, что многие строки по сути повторяются.

2

Это связано с тем, что Яндекс.Директ и Google Ads используют так называемый модификатор гибкого соответствия. Из-за этого нам нужно объединить несколько строк в одну, которая на самом деле является одним ключевым словом, и необходимо оценить ее эффективность.

Многие делают это сами, что требует огромных затрат времени и сил.

3

В Excel есть отдельная функция, позволяющая сделать это, которая называется «Консолидация данных». Возможны и другие способы консолидации данных. Давайте рассмотрим их более подробно.

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

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

Консолидация данных с нескольких таблиц в одну

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

Для чего может использоваться консолидация данных

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

Представителям каких профессий пригодится функция консолидации данных

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

1. Бухгалтер.
2. Инвестор.
3. Предприниматель.
4. Математик.
5. Ученый любой специальности, требующей умения обрабатывать информацию статистически, от математиков до социологов и психологов.

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

Требования к таблицам

Обратите внимание, что не всегда есть возможность использовать «Консолидацию». Для этого исходные таблицы (могут быть на одном листе или на разных листах/книгах) должны соответствовать следующим требованиям:

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

Создание консолидированной таблицы

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

1. откройте отдельный лист для сводной таблицы.
2. На открывшемся листе отметьте ячейку, которая должна быть слева вверху новой таблицы.
3. Находясь во вкладке «Данные», нажмите кнопку «Консолидация», которая находится на ленте в блоке инструментов «Работа с данными».
4. Откроется окно настроек консолидации данных. В поле «Функция» необходимо указать, какое действие должно производиться с ячейками при совпадении строк и столбцов. Это могут быть следующие действия:
   • сумма;
   • толпа;
   • средний;
   • максимум;
   • минимум;
   • работа;
   • количество номеров;
   • отклонение смещения;
   • объективное отклонение;
   • офсетное рассеяние;
   • объективная дисперсия.

   В большинстве случаев используется функция «Сумма».

   

5. В поле «Ссылка» введите диапазон ячеек одной из первичных таблиц, подлежащих консолидации. Если эта область находится в том же файле, но на другом листе, нажмите кнопку справа от поля ввода данных.
6. Перейдите на лист, где находится таблица, выберите нужную область. После ввода данных нажмите еще раз на кнопку справа от поля, где вводился адрес ячеек.
7. Вернитесь в окно настроек консолидации, чтобы добавить уже выделенные нами ячейки в список диапазонов, нажмите кнопку «Добавить». Как видите, область после этого добавляется в список.

   

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

   

   Если нужный диапазон находится в другой книге (файле), сразу нажмите кнопку «Обзор…», выберите файл на жестком диске или съемном носителе, и только после этого выделите диапазон ячеек в этом файле вышеописанным способом. Естественно, файл должен быть открыт.

   

8. Точно так же вы можете сделать некоторые другие настройки сводной таблицы.Чтобы имена столбцов автоматически добавлялись в заголовок, установите флажок рядом с параметром «Ярлыки верхней строки». Чтобы данные суммировались, установите флажок рядом с параметром «Значения для левого столбца». Если вы хотите, чтобы вся информация в сводной таблице обновлялась при обновлении данных в первичных таблицах, не забудьте поставить галочку напротив параметра «Создавать связи с исходными данными». Но в этом случае учтите, что если вы хотите добавить новые строки в исходную таблицу, вам нужно снять этот пункт и пересчитать значения вручную.

   Когда все настройки закончены, нажмите кнопку «ОК».

   

9. Сводный отчет готов. Как видите, данные сгруппированы. Чтобы увидеть информацию внутри каждой группы, нажмите на плюсик слева от таблицы. Содержимое группы теперь видно. Аналогичным образом можно открыть любую другую группу.

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

Читайте также: Подбор параметра в Excel и примеры его использования

Выполняем консолидацию

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

1. Нажмите на иконку для создания нового листа, после чего программа автоматически добавит его справа от текущего и переключит нас на него. Подробная информация об этой операции представлена ​​в нашей отдельной публикации — «Как добавить лист в Excel”.
   Примечание: Вы можете переместить добавленный лист в удобное для нас место (например, в конец списка) с нажатой левой кнопкой мыши, и «зацепить» его за вкладку с названием. При желании лист также можно переименовать (режим редактирования запускается двойным кликом по названию, завершается нажатием Enter).
2. В дополнительном листе мы стоим в ячейке, с которой планируем вставлять сводную таблицу (в нашем случае оставляем ее выделенной по умолчанию). Затем переходим во вкладку «Данные», находим группу инструментов «Работа с данными», где нажимаем на иконку «Консолидировать”.
3. На экране появится небольшое окно с настройками инструмента.
4. Вот следующие варианты:
   • Функция — нажав на применимый вариант, мы откроем список возможных действий, среди которых выберем то, которое необходимо выполнить для консолидированных данных:
     • Сумма (выбрано по умолчанию; используется чаще всего, поэтому оставим для примера);
     • Толпа;
     • Средний;
     • Максимум;
     • Минимум;
     • Работа;
     • Количество номеров;
     • Среднеквадратичное отклонение;
     • Беспристрастное отклонение;
     • Диффузия;
     • Объективная дисперсия.
5. Перейдите в поле «Ссылка», нажав на него. Здесь мы поочередно выбираем диапазон ячеек в исходных таблицах для обработки. Для этого:
   • Переключитесь на лист с первой таблицей.
   • Видим, что в поле автоматически появилось название выбранного листа (но если смены листа не было, то имя добавляться не будет). Теперь с нажатой левой кнопкой мыши выделите таблицу вместе с заголовком (например, из верхней левой ячейки в нижнюю правую). Убеждаемся, что в поле правильно введены координаты выбранных элементов, после чего нажимаем кнопку «Добавить». Кстати, задать/изменить координаты можно и вручную, введя их с клавиатуры, но это не так удобно, как при работе мышкой, к тому же в этом случае есть шанс ошибиться.
     Примечание: чтобы выбрать область из другого файла, сначала откройте его в программе. Затем после запуска функции консолидации в первой книге и в поле «Ссылка» переключаемся на вторую книгу, выбираем в ней нужный лист и выбираем нужный диапазон ячеек. В этом случае имя файла необходимо добавить в начало ссылки. В нашем случае в этом нет необходимости, мы только показали, как это можно сделать.
6. В результате в «Списке диапазонов» появилась первая запись, соответствующая сделанному нами выбору.
7. Возвращаемся к полю «Справочник», удаляем в нем информацию, а затем добавляем координаты двух оставшихся таблиц в «Список диапазонов.
8. Теперь остались последние штрихи — поставьте галочки напротив нужных опций:
   • «Заголовки верхних строк» — в результате объединения в результирующую таблицу будет автоматически добавлен заголовок с учетом первых данных.
   • «Значения левого столбца» — необходимо заполнить левый столбец правильными значениями.
   • «Создавать связи с исходными данными» — очень важная опция, активировав которую, любые изменения в исходных данных будут сразу же отображаться в сводной таблице, которая также будет формироваться с группировкой, что бывает очень удобно. Но следует помнить, что если в дальнейшем потребуется изменить структуру одной из исходных таблиц, процедуру необходимо повторить. То же самое относится и к случаям, когда флажок не установлен.
   • Нажмите OK, когда будете готовы.
9. Excel объединит данные и создаст новую таблицу в соответствии с введенными настройками и выбранными параметрами.
   В нашем случае мы выбрали создание соединения, поэтому получили группировку данных, позволяющую показать/скрыть детали.

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

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

Вам необходимо создать общий отчет с помощью «Консолидации данных». Во-первых, давайте проверим это

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

Области с исходными данными должны быть открыты.

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

Обратите внимание на следующее!!! Справа и снизу эта ячейка должна быть свободна. Команда Consolidate заполнит столько строк и столбцов, сколько необходимо.

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

Откроется диалоговое окно Вид:

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

Продолжаем заполнять следующее поле — «Ссылка».

Ставим курсор в поле. Открываем лист «1 квартал». Выберите таблицу вместе с заголовком. Первая объединяемая область отображается в поле «Ссылка». Нажмите кнопку «Добавить

Открываем по очереди вторую, третью и четвертую четверти — выбираем области данных. Нажмите «Добавить».

Таблицы, которые необходимо консолидировать, отображаются в поле списка Область».

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

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

Нажмите OK, чтобы выйти из меню консолидации и создать сводную таблицу.

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

Способ 1. С помощью формул

У нас есть несколько таблиц одного типа на разных листах одной книги. Например, это:

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

Самый простой способ решить задачу «в лоб» — ввести формулу на форму

=’2001′!B3+’2002′!B3+’2003′!B3

который просуммирует содержимое ячеек В2 с каждого из указанных листов, а затем скопирует его в оставшиеся ячейки вниз и вправо.

Если листов много, проще будет развернуть их все подряд и использовать немного другую формулу:

=СУММ(‘2001:2003’!B3)

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

Способ 2. Если таблицы неодинаковые или в разных файлах

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

Рассмотрим следующий пример. У нас есть три разных файла (Ivan.xlsx, Rita.xlsx и Fedor.xlsx) с тремя таблицами:

Хорошо видно, что таблицы неодинаковые — у них разные размеры и смысловое наполнение. Однако их можно собрать в единый отчет менее чем за минуту. Единственным условием успешного слияния (объединения) таблиц в таком случае является совпадение заголовков столбцов и строк. Именно в первой строке и левом столбце каждой таблицы Excel будет искать совпадения и суммировать наши данные.

Как сделать эту консолидацию:

1. сначала откройте исходные файлы
2. Создайте новую пустую книгу (Ctrl + N)
3. Ставим в нее активную ячейку и выбираем на вкладке (в меню) Данные — Консолидация (Data — Consolidate). Откроется соответствующее окно:
   
4. Поместите курсор в опорную строку и переключитесь на файл Ivan.xlsx, выберите таблицу данных (вместе с заголовком). Затем нажмите кнопку «Добавить» в окне консолидации, чтобы добавить выбранную область в список объединяемых областей.
5. Повторите те же действия для файлов Риты и Федора. В результате в списке должны появиться все три области:
   

Обратите внимание, что в этом случае Excel фактически запоминает расположение файла на диске, и прописывает для каждого из них полный путь (диск-папка-файл-список-адреса ячеек). Чтобы суммирование происходило с учетом заголовков столбцов и строк, необходимо активировать оба флажка Использовать как имя (Использовать метки). Флажок Создать ссылки на исходные данные позволит в дальнейшем (при изменении данных в исходных файлах) автоматически пересчитывать сводный отчет.

После нажатия ОК мы видим результат нашей работы:

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

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

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

Попробуем сделать это на практике. Для этого в строке меню есть специальная команда, которая называется «Консолидация». Во-первых, нам нужно убедиться, что все вышеперечисленные условия соблюдены.

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

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

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

Затем добавьте столько листов, сколько вам нужно. В нашем случае это данные за все остальные кварталы. Мы просто нажимаем «Добавить», выбираем нужные области, а затем повторяем цикл до тех пор, пока не будет добавлено такое количество, которое нам нужно.

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

Важно! Если вы введете данные, выходящие за пределы исходных диапазонов в исходных диапазонах, автоматического обновления не будет.

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

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

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

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

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

Давайте рассмотрим простой пример того, как это работает.

Предположим, у нас есть магазин, в котором продаются товары, отмеченные разными индексами (1, 2, 3, 4 и т д.). У нас есть несколько таблиц, которые их описывают, и некоторые имена повторяются. Что делать в такой ситуации?

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

Получаем такой результат. Мы видим, что Excel смог автоматически объединять данные из таблиц на основе имен столбцов.

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

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

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

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

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

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

Книги сделаны: Цех 1, Цех 2 и Цех 3. Структура одинаковая. Расположение данных идентично. Сгруппируем их по положению.

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

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

Сводная таблица:

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

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

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

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

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

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

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

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

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