Как сделать консолидацию данных в excel
Консолидация данных в программе Microsoft Excel
При работе с однотипными данными, размещенными в разных таблицах, листах или даже книгах, для удобства восприятия лучше собрать информацию воедино. В Microsoft Excel с этой задачей можно справиться с помощью специального инструмента, который называется «Консолидация». Он предоставляет возможность собрать разрозненные данные в одну таблицу. Давайте узнаем, как это делается.
Условия для выполнения процедуры консолидации
Естественно, что не все таблицы можно консолидировать в одну, а только те, которые соответствуют определенным условиям:
Создание консолидированной таблицы
Рассмотрим, как создать консолидированную таблицу на примере трех таблиц, имеющих одинаковый шаблон и структуру данных. Каждая из них расположена на отдельном листе, хотя по такому же алгоритму можно создать консолидированную таблицу из данных расположенных в разных книгах (файлах).
В поле «Функция» требуется установить, какое действие с ячейками будет выполняться при совпадении строк и столбцов. Это могут быть следующие действия:
В большинстве случаев используется функция «Сумма».
Как видим, после этого диапазон добавляется в список.
Аналогичным образом, добавляем все другие диапазоны, которые будут участвовать в процессе консолидации данных.
Если же нужный диапазон размещен в другой книге (файле), то сразу жмем на кнопку «Обзор…», выбираем файл на жестком диске или съемном носителе, а уже потом указанным выше способом выделяем диапазон ячеек в этом файле. Естественно, файл должен быть открыт.
Для того, чтобы автоматически добавить название столбцов в шапку, ставим галочку около параметра «Подписи верхней строки». Для того, чтобы производилось суммирование данных устанавливаем галочку около параметра «Значения левого столбца». Если вы хотите, чтобы при обновлении данных в первичных таблицах обновлялась также и вся информация в консолидированной таблице, то обязательно следует установить галочку около параметра «Создавать связи с исходными данными». Но, в этом случае нужно учесть, что, если вы захотите в исходную таблицу добавить новые строки, то придется снять галочку с данного пункта и пересчитать значения вручную.
Когда все настройки выполнены, жмем на кнопку «OK».
Теперь содержимое группы доступно для просмотра. Аналогичным способом можно раскрыть и любую другую группу.
Как видим, консолидация данных в Эксель является очень удобным инструментом, благодаря которому можно собрать воедино информацию расположенную не только в разных таблицах и на разных листах, но даже размещенную в других файлах (книгах). Делается это относительно просто и быстро.
Консолидация данных в Excel (+ примеры использования)
В ходе работы у человека, который пользуется Excel, может появляться несколько таблиц, которые находятся в нескольких разных файлах. И нам нужно все эти данные свести в одну кучу, чтобы иметь общее представление о том, что там. Чтобы это сделать, существует инструмент «Консолидация». Сегодня мы детально разберем, как его правильно использовать и какие фишки и секреты есть в Эксель, чтобы добиться максимальной эффективности.
Что такое консолидация данных
Консолидация – это то же самое, что и объединение. В Эксель это функция, позволяющая соединить несколько диапазонов в один, соединять несколько листов, которые находятся в нескольких файлах.
Это может быть полезным для маркетолога, а также для представителей других специальностей. Давайте приведем несколько ситуаций, в которых консолидация данных может оказаться востребованной.
Предположим, нами была запущена контекстная реклама, где есть такие показатели, как клики, расходы, транзакции и доход. И нами была создана такая табличка.
1
Если мы загрузим всю эту информацию в Excel, то увидим, что много строк по сути повторяются.
2
Причина этого в том, что в Яндекс.Директ и Google Ads используется так называемый модификатор широкого соответствия. Из-за этого нам требуется объединить несколько строк в одну, которая является фактически одним ключевым словом, и его эффективность нужно оценивать.
Многие люди делают это самостоятельно, что требует огромного вложения времени и усилий.
3
В Excel есть отдельная функция, которая позволяет делать это, которая называется «Консолидация данных». Также возможны некоторые другие способы консолидации данных. Давайте их рассмотрим более подробно.
Консолидация данных с нескольких листов
Предположим, нам нужно объединить данные, которые находятся на нескольких листах. Это можно делать даже в тех случаях, когда таблицы имеют разный формат. Правда, используемые инструменты будут в этом случае несколько различаться. Точная последовательность действий, какие нужно выполнять в этом случае, будет приведена ниже.
Консолидация данных с нескольких таблиц в одну
Несколько разных таблиц могут размещаться на различных листах или же на одном. В этом случае есть свои особенности, как правильно работать.
Для чего может использоваться консолидация данных
Консолидация данных может использоваться в целом ряде сфер, начиная бухгалтерским учетом и заканчивая лайф-менеджментом. Фактически любая сфера жизни, в которую проник Эксель, может быть связанной с этой программой. И как только появляется несколько таблиц, которые нужно объединить в одну, появляется необходимость в том, чтобы воспользоваться соответствующим функционалом Excel.
Представителям каких профессий пригодится функция консолидации данных
Профессий, в которых может использоваться консолидация данных, огромное количество. Фактически это любая специальность, связанная с обработкой информации, в том числе, и обучение в университете. В целом, представители следующих профессий могут использовать эту возможность в своей работе:
А также ряд представителей других профессий. Даже если вас в этом списке нет, все равно рекомендуется изучить. как использовать консолидацию данных, чтобы в нужный момент не изучать этого дополнительно, а уже знать к той временной точке.
Как сделать консолидацию данных в Excel
А теперь давайте перейдем к разбору способов выполнения консолидации данных в электронных таблицах. Их много. Но сначала нужно разобрать универсальные критерии, на предмет соответствия которым всегда проверяется таблица или лист.
Условия для выполнения процедуры консолидации
Несмотря на то, что Эксель – очень умная программа, которая неприхотлива к условиям, все же есть несколько критериев, которым должна соответствовать таблица, чтобы Эксель смог ее объединить с другой, такой же самой.
Давайте их перечислим более подробно:
Консолидация по расположению
Это один из основных методов объединения данных. В этом случае ячейки, которые нужно объединять, должны находиться на одной и той же позиции, что и изначальная. Очень удобно использовать этот способ, чтобы объединять диапазоны, которые размещены на разных листах. Как один из примеров, где можно использовать консолидацию по расположению, можно привести бухгалтерию, где четыре квартальных отчета нужно объединить в один годовой. С помощью консолидации по расположению можно создать бюджет один раз, а потом обновлять его каждый квартал. Это потребует значительно меньше времени, чем составлять новый годовой отчет с нуля.
Давайте попробуем сделать это на практике. Для этого на панели меню существует специальная команда, которая так и называется «Консолидация». Для начала нам нужно удостовериться, что все условия, приведенные выше, выполнены.
Далее нам нужно создать новую книгу или лист, в которую будет выводиться результирующая таблица с объединенными данными. Открываем его (или ее, если это книга), после чего нажимаем левой кнопкой мыши по левой верхней ячейке (если она не была выделена сама). Важно убедиться, что под ней и справа от нее нет абсолютно никакой информации, потому что консолидированная таблица будет занимать столько места, сколько ей требуется. После этого переходим на вкладку «Данные», на которой есть кнопка с соответствующим названием. Нажимаем на «Консолидация», после чего открывается диалоговое окно, которое выглядит следующим образом.
6
Видим, что слева находится перечень функций, которые могут использоваться при объединении. Это самые разные вычисления, такие как сумма значений, несмещенная дисперсия, среднее арифметическое и другое. Мы выберем первый вариант, потому что он наиболее часто используемый и простой для понимания. В этом случае ячейки в соответствующих местах будут просто складываться между собой.
После этого начинаем заполнять следующее поле – «Ссылка». Для этого нужно поставить в этом поле курсор. После этого открываем первый лист (в нашем случае это данные за первый квартал). Все данные выделяем (в том числе, и шапку), после чего там появится первый диапазон, который будет суммироваться с дальнейшим. После этого нажимаем кнопку «Добавить».
7
Далее добавляем такое количество листов, которое требуется. В нашем случае это данные за все остальные кварталы. Просто нажимаем «Добавить», выделяем требуемые диапазоны, а потом повторяем цикл до тех пор, пока не будет добавлено такое количество, которое нам необходимо.
8
Все диапазоны, которые были добавлены для объединения, отображаются в соответствующем поле, возле которого есть надпись «Список диапазонов». Также можно более гибко задавать настройки, указывая флажочками возле подходящих по смыслу пунктов «Подписи верхней строки» или «Значения левого столбца». С помощью этих параметров можно определять, что использовать в качестве имен. Также есть кнопка «Создавать связи с исходными данными», которая позволяет автоматически изменять данные в консолидированной таблице, если в исходном материале что-то редактируется.
9
В таком случае допускается только ручной ввод новых значений, для чего предварительно нужно убрать галочку «Создавать связи с исходными данными». Чтобы в конечном итоге выйти из меню консолидации и сгенерировать готовую таблицу, объединяющую сразу несколько диапазонов, нужно нажать кнопку «ОК».
В этой таблице есть возможность управлять отображением отдельных строк, нажимая плюсик или минус слева от основного содержимого листа.
Консолидация по категории
В этом случае в качестве ориентира для объединения будут использоваться названия столбцов или другие метки. В этом случае необязательно добиваться того, чтобы макет был идеально таким же самым. Данные могут быть разбросаны очень сильно, но если они правильно отмечены, Excel их сможет объединить в кучу.
Есть очень много сходств между настройкой консолидации по категории и созданием сводной таблицы. Правда, у последней есть несколько преимуществ по сравнению с обычной консолидацией, поскольку сводная таблица поддерживает более гибкие варианты консолидаций.
Давайте приведем простой пример, как это работает.
Предположим, у нас есть магазин, в котором продаются товары, обозначенные разными индексами (1, 2, 3, 4 и так далее). У нас есть несколько таблиц, описывающих их, и некоторые наименования повторяются. Что делать в такой ситуации?
4
Последовательность действий очень простая. Сначала необходимо открыть меню «Консолидация». Мы в появившейся панели выберем функцию «Сумма». Вы же можете выбрать другую в качестве той, которая будет использоваться для соединения информации. После этого нажимаем кнопку ОК.
5
У нас получается такой результат. Видим, что Excel смог в автоматическом режиме объединить данные из таблиц, исходя из названий столбцов.
Какой способ консолидации лучше всего выбрать? Все зависит от того, как будет использоваться таблица. Но специалисты рекомендуют все же вариант по расположению, поскольку идеально объединяться будут как раз те данные, которые полностью соответствуют друг другу по макету.
Консолидация данных в Excel при помощи формул
Предположим, у нас есть несколько таблиц, которые связаны между собой тематически, и их нужно объединить. Например, такие.
10
=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3
=СУММ(‘2001 год:2003 год’!B3)
С помощью этого метода можно объединять данные из нескольких разных таблиц.
Консолидация данных в Excel: практика
Предположим, у нас есть такая таблица.
11
В случае с ней лучше всего подходит способ по категориям, поскольку шаблоны таблиц одинаковые. При этом таблицы, которые нужно объединить, находятся в различных документах. Всего нам нужно объединить три книги. Последовательность действий следующая:
Также можно воспользоваться кнопкой «Обзор», чтобы указать путь к файлу Экселю. Или же банальным переключением стандартными средствами Windows.
В результате, мы получаем такую таблицу.
12
Консолидация данных в Excel: примеры
Примеров консолидации данных в Эксель выше было предостаточно на каждый случай. Но есть еще один, который тоже хорошо было рассмотреть. Для начала вводим наименования рядов и колонок из диапазонов, которые в дальнейшем мы собираемся объединять. Легче всего просто скопировать их.
13
После этого в первой ячейке вводим такую формулу.
=’1 квартал’!B2+’2 квартал’!B2+’3 квартал’!B2.
После этого соответствующие ячейки всех трех листов объединятся. Далее осталось просто скопировать эту формулу на всю колонку.
14
Такой способ консолидации удобно применять в ситуациях, когда данные располагаются на разных листах.
Таким образом, консолидация данных – это вовсе не сложно. Если все делать правильно, то можно сделать годовой отчет буквально за несколько минут.
Консолидация данных с нескольких листов
Для подведения итогов и обобщения результатов по данным на отдельных листах можно консолидировать их на каждом листе. Листы могут быть в той же книге, что и он, или в других книгах. Консолидированные данные собираются таким образом, чтобы их было проще обновлять и общую обработку.
Например, если в каждом из региональных офисов есть свой лист расходов, с помощью консолидации можно свести эти данные на главном листе корпоративных расходов. Главный лист также может содержать итоговые и средние значения продаж, данные о складских запасах и информацию о самых популярных товарах в рамках всей компании.
Совет: Если вы часто консолидируете данные, можно создать новые таблицы на их шаблоне с согласованным макетом. Дополнительные сведения о шаблонах см. в статье Создание шаблона. Также советуем добавить в шаблон таблицы Excel.
Консолидацию данных можно консолидировать двумя способами: по позиции или категории.
Консолидация по положению:данные в исходных областях должны быть в том же порядке и с одинаковыми подписями. Используйте этот способ, чтобы консолидировать данные из нескольких листов, основанных на одном шаблоне, например отчетов о бюджете.
Консолидация по категории: данные в исходных областях не расположены в одном и том же порядке, но имеют одинаковые метки. Используйте этот способ, чтобы консолидировать данные из нескольких листов с разными макетами, но одинаковыми метками данных.
Консолидация данных по категориям аналогична созданию сводной. Тем не менее вы можете легко реорганизовывать категории в одной из них. Если вам нужна более гибкая консолидация по категориям, создайте ее.
Примечание: Примеры в этой статье были созданы в Excel 2016. Хотя представление может отличаться, если вы используете другую версию Excel, действия будут те же.
Выполните указанные здесь действия, чтобы консолидировать несколько данных на этом примере.
Если вы еще не сделали этого, для этого нужно сделать следующее:
Убедитесь, что каждый диапазон данных имеет формат списка. В первой строке каждого столбца должна быть надпись (заглавная строка) и похожие данные. В списке не должно быть пустых строк или столбцов.
Разведите каждый диапазон на отдельный таблицу, но не вводите ничего на этом онеке, где вы планируете консолидировать данные. Excel сделает это за вас.
Убедитесь, что каждый диапазон имеет одинаковый макет.
На основном листе щелкните левый верхний угол области, в которой требуется разместить консолидированные данные.
Примечание: Чтобы не переписать существующие данные на эталонном телефоне, оставьте достаточное количество ячеек справа и под этой ячейкой для консолидированных данных.
Щелкните «Консолидация> данных» (в группе «Инструменты для работы с данными»).
Выберите в раскрывающемся списке Функцияитоговая функция, которую требуется использовать для консолидации данных. По умолчанию функция СУММ.
Вот пример, в котором выбраны три диапазона:
Затем в поле «Ссылка» нажмите кнопку «Свернуть», чтобы уменьшить панель и выбрать данные на этом поле.
Щелкните лист с данными, которые вы хотите консолидировать, а затем нажмите кнопку раскрытия диалогового окна справа, чтобы вернуться в диалоговое окно Консолидация.
Если таблица с данными, которые необходимо консолидировать, находится в другой книге, нажмите кнопку «Обзор», чтобы найти ее. После того как вы нажмете кнопку «ОК»,Excel введите путь к файлу в поле «Ссылка» и примените к этому пути восклицательный пункт. После этого можно продолжить выбор других данных.
Вот пример, в котором выбраны три диапазона:
Во всплываемом окне «Консолидация» нажмите кнопку «Добавить». Повторите эти действия, чтобы добавить все диапазоны, которые нужно консолидировать.
Автоматическое обновление и обновление вручную Если вы хотите, чтобы таблица консолидации обновлялся автоматически при изменениях исходных данных, просто проверьте поле «Создание связей с исходными данными». Если этот поле по-прежнему не заблокировали, вы можете обновить консолидацию вручную.
Связи невозможно создать, если исходная и конечная области находятся на одном листе.
Если вам нужно изменить или заменить диапазон, щелкните его во всплывающее окте «Консолидация» и обновите с помощью действий выше. При этом будет создана новая ссылка на диапазон, поэтому вам потребуется удалить предыдущую ссылку перед консолидацией. Просто выберите старую ссылку и нажмите клавишу DELETE.
Нажмите кнопку«ОК», и Excel вы сможет создать консолидацию. При желании вы можете применить форматирование. Это необходимо сделать только один раз, если только вы не повторно не повторно запланирование консолидации.
Все названия, не совпадающие с названиями в других исходных областях, приведут к появлению дополнительных строк или столбцов в консолидированных данных.
Убедитесь, что все категории, которые вы не хотите консолидировать, имеют уникальные подписи, которые отображаются только в одном диапазоне исходных источников.
Если данные для консолидации находится в разных ячейках разных таблиц:
Введите формулу со ссылками на ячейки других листов, по одной на каждый лист. Например, чтобы консолидировать данные из листов «Продажи» (в ячейке B4), «Кадры» (в ячейке F5) и «Маркетинг» (в ячейке B9), в ячейке A2 основного листа, введите следующее:
Совет: Чтобы ввести ссылку на ячейку, например «Продажи»! B4— в формуле без ввода текста введите формулу до того места, где нужно ввести ссылку, затем щелкните ячеку на вкладке и щелкните ячейку. Excel заполнит имя и адрес ячейки листа. ПРИМЕЧАНИЕ. При использовании формул в таких случаях можно легко сделать ошибку, так как можно случайно выбрать неправильная ячейка. Ошибку также сложно обнаружить после ввода сложной формулы.
Если данные для консолидации находится в одинаковых ячейках на разных таблицах:
Введите формулу с трехмерной ссылкой, которая указывает на диапазон имен листов. Например, чтобы консолидировать данные в ячейках A2 от «Продажи» до «Маркетинг» включительно, в ячейку E5 нужно ввести следующую ячейку:
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.