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