Как обновить консолидацию в excel

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

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

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

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

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

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

Диапазоны с исходными данными нужно открыть.

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

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

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

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

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

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

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

Открываем поочередно второй, третий и четвертый квартал – выделяем диапазоны данных. Жмем «Добавить».

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

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

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

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

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



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

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

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

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

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

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

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

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

Консолидированная таблица:

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

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

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

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

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

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

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

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

Скачать все примеры консолидации данных в Excel

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

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

• Работаем с несколькими наборами данных
• Консолидированные данные
• Форматируем данные
• Разные рабочие книги
• Обновляем консолидацию

Работаем с несколькими наборами данных

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

Для этого откройте пустой лист в рабочей книге Excel (добавьте новый, если необходимо) и кликните в нём по любой ячейке. На вкладке Data (Данные) нажмите Consolidate (Консолидация), чтобы открылось диалоговое окно Consolidate (Консолидация). Выберите функцию для анализа данных и ссылки на диапазоны, которые нужно свести. В нашем случае мы хотим просуммировать значения, поэтому в поле Function (Функция) выберем Sum (Сумма).

Вы можете выбрать любую из 11 операций: Sum (Сумма), Count (Количество), Average (Среднее), Max (Максимум), Min (Минимум), Product (Произведение), Count Numbers (Количество чисел), StdDev (Смещенное отклонение), StdDevp (Несмещенное отклонение), Var (Смещенная дисперсия) и Varp (Несмещенная дисперсия).

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

Вернувшись в диалоговое окно Consolidate (Консолидация), нажмите Add (Добавить), чтобы добавить первый набор данных к списку диапазонов для консолидации. Проделайте те же шаги, чтобы добавить второй и все остальные наборы данных к списку диапазонов.

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

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

Полезный совет

Вы можете присвоить диапазонам имена, прежде чем начинать процесс консолидации. Для этого выделите диапазон и задайте ему имя в поле Имя слева от строки формул. Когда Вы дадите имена всем диапазонам, то при настройке консолидации поставьте курсор в поле Reference (Ссылка), нажмите F3 и в открывшемся окне Paste Name (Вставка имени) выберите нужный диапазон. Таким образом, Вы можете дать каждому диапазону понятное имя, и тогда позже не придётся вспоминать, что за данные скрываются на листе Лист1 в ячейках A3:F40.

Как видите, в области All References (Список диапазонов) листы располагаются в алфавитном порядке. Прежде чем продолжить, убедитесь, что указали ссылки на все требуемые диапазоны. Отметьте галочкой параметры Use labels in (Использовать в качестве имен): Top Row (Подписи верхней строки) и Left Column (Значения левого столбца). Поставьте галочку также для Create links to source data (Создавать связи с исходными данными) и нажмите ОК.

Консолидированные данные

Когда нажмете ОК, Excel обобщит все выбранные данные на новом листе. Вдоль левого края экрана Вы увидите инструменты группировки, которые можно использовать, чтобы отображать и скрывать данные.

Если был выбран пункт Create links to source data (Создавать связи с исходными данными), то полученные данные ссылаются на содержащие их исходные ячейки. Кликнув по ячейке с данными (не по ячейке с суммой), Вы увидите ссылку на лист и ячейку, содержащую эти данные.

Если вы не отметили параметр Create links to source data (Создавать связи с исходными данными), то полученная консолидация – это просто обобщение данных без каких-либо подробностей, без группировки и содержащее только результаты суммирования.

Полезный совет

Так как эти данные содержат ссылки, Вы можете использовать инструмент Trace Precedents (Влияющие ячейки), чтобы перейти к исходной ячейке, содержащей данные. Для этого кликните по ячейке, содержащей интересующие Вас данные. Откройте вкладку Formulas (Формулы) и найдите кнопку Trace Precedents (Влияющие ячейки). Поскольку исходная ячейка находится на другом листе, наведите указатель мыши на появившуюся чёрную стрелку, чтобы указатель принял вид пустой белой стрелки. Дважды щелкните, чтобы открыть диалоговое окно Go To (Переход) – ссылка на ячейку будет указана в этом окне. Кликните по ссылке и далее нажмите ОК, чтобы перейти к нужному месту.

Форматируем данные

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

Разные рабочие книги

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

Для этого потребуется выполнить те же самые действия, как мы делали только что: выберите пустой лист или добавьте новый, нажав Insert Sheet (Вставить лист) на вкладке Insert (Вставка). Нажмите команду Consolidate (Консолидация). На этот раз вместо того, чтобы выбрать лист в текущей рабочей книге, нажмите кнопку Browse (Обзор), чтобы открыть другую рабочую книгу.

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

Мне стало легче переключаться между открытыми рабочими книгами, когда я добавил кнопку Switch Windows (Перейти в другое окно) на Панель быстрого доступа.

Если включить параметр Create Links to Source Data (Создавать связи с исходными данными), то, когда сведение будет выполнено, все изменения в исходных листах и рабочих книгах будут отображаться и в консолидированных данных. Второй столбец в обобщенных данных все также будет отображать название рабочей книги, а команда Trace Precedents (Влияющие ячейки) быстро перенесет Вас к ячейкам, связанным ссылкой, если соответствующая рабочая книга открыта, но не сработает, если она закрыта.

Обновляем консолидацию

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

На вкладке Data (Данные) нажмите Consolidate (Консолидация) – Вы увидите, что указанные ранее ссылки сохранились. Внесите изменения, добавив или удалив диапазоны, или изменив их размер, и нажмите ОК, чтобы создать консолидацию заново.

Предостережение!

Если Вы включаете параметр Create Links to Source Data (Создавать связи с исходными данными), то Вы не сможете обновить консолидированные данные, нажав на вкладке Data (Данные) команду Consolidate (Консолидация), не удалив предварительно старые результаты. Причина в том, что в таком случае вместо обновления будет сделана попытка вставить одну консолидацию в другую, что в результате приведет к полной чепухе. Вы можете обновить диапазоны, изменяя формулы вручную, но более целесообразно будет создать консолидацию заново.

Если же Вы не включили параметр Create Links to Source Data (Создавать связи с исходными данными), то Ваши сведенные данные представляют из себя просто обобщение, без каких-либо подробностей. В таком случае, чтобы обновить консолидацию, кликните по верхней ячейке, содержащей результаты, затем нажмите на вкладке Data (Данные) команду Consolidate (Консолидация), внесите все необходимые изменения и нажмите ОК.

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

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

​Смотрите также​Чтобы показать путь к​ исходные таблицы новые​ Собрать в один​Consolidate​ просто обобщение, без​ исходными данными), то,​ на ячейку будет​ Отметьте галочкой параметры​ первый набор данных​ Excel. Если каждый​​ ячейку — например,​​ выбраны три диапазоны​ и содержать похожие​ главном листе корпоративных​. Но, в этом​несмещенная дисперсия.​

​При работе с однотипными​ книгам с исходными​

Условия для выполнения процедуры консолидации

​ значения, сверх выбранного​ отчет, чтобы получить​(Консолидация) на вкладке​ каких-либо подробностей. В​ когда сведение будет​

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

​В большинстве случаев используется​ данными, размещенными в​ диапазонами, ставим курсор​ для консолидации диапазона,​ общее представление. С​Data​ таком случае, чтобы​ выполнено, все изменения​ окне. Кликните по​(Использовать в качестве​ для консолидации. Проделайте​ по одному месяцу​ в формуле, не​

1. ​Во всплывающем окне​ отсутствуют пустые строки​

   

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

   

4. ​ и если информация​ вводя, введите формулу​

   

   ​Консолидация​​ или столбцы в​​ итоговые и средние​ захотите в исходную​«Сумма»​ или даже книгах,​ На вкладке «Вид»​ отображаться в объединенном​

   • ​ инструмент «Консолидация».​
   • ​Выберите функцию​
   • ​ по верхней ячейке,​
   • ​ и рабочих книгах​
   • ​ нажмите​
   • ​Top Row​
   • ​ чтобы добавить второй​
   • ​ на всех листах​
   • ​ до того места,​
   • ​нажмите кнопку​
   • ​ любом месте в​

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

   

5. ​ нажимаем кнопку «Перейти​​ отчете. Чтобы можно​​Есть 4 файла, одинаковых​Sum​ содержащей результаты, затем​ будут отображаться и​ОК​(Подписи верхней строки)​ и все остальные​ организована единообразно, то​ куда требуется вставить​Добавить​ списке.​

   

6. ​ о складских запасах​ строки, то придется​В поле​ лучше собрать информацию​ в другое окно».​ было вносить данные​ по структуре. Допустим,​(Сумма), чтобы суммировать​ нажмите на вкладке​

   

7. ​ в консолидированных данных.​, чтобы перейти к​ и​ наборы данных к​ с помощью инструмента​ ссылку, а затем​​. Повторите этот сценарий,​​Разместите каждый диапазон на​

   

   ​ и информацию о​ снять галочку с​«Ссылка»​

   

   ​ воедино. В Microsoft​Выбираем поочередно имена файлов,​ вручную, снимите флажок​ поквартальные итоги продаж​

   

   ​ данные.​Data​ Второй столбец в​ нужному месту.​Left Column​​ списку диапазонов.​​Консолидация​ щелкните лист, используйте​ чтобы добавить все​ отдельном листе, но​ самых популярных товарах​ данного пункта и​указываем диапазон ячеек​ Excel с этой​ выделяем диапазоны в​

   

8. ​ «Создавать связи с​ мебели.​Поместите курсор в следующую​

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

   ​ жмем «Добавить».​Для выхода из меню​​ с помощью «Консолидации​​A1:E4​

   

9. ​(Консолидация), внесите все​ название рабочей книги,​ же, как делали​ для​ одинаковые диапазоны данных,​ листе за весь​ Excel будет завершена​

   

   ​Если требуется автоматическое​ основном листе где​Совет:​ жмем на кнопку​ консолидации. Если этот​

​ инструмента, который называется​Примечание. Показать программе путь​ «Консолидации» и создания​ данных». Сначала проверим,​в документе​ необходимые изменения и​ а команда​ это в обычном​Create links to source​ расположенные на одинаковой​ год.​ адрес имя и​ обновление консолидации таблицы​

​ план для консолидации​

lumpics.ru

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

​ Если вы часто консолидировать​​«OK»​ диапазон находится в​«Консолидация»​ к исходным диапазонам​ сводной таблицы нажимаем​ чтобы​district1.xls​ нажмите​Trace Precedents​ файле Excel. Вы​ data​ позиции, то все,​Для этого откройте пустой​ ячейку листа для​ при изменении исходных​ данных. Excel будет​ данные, он помогут​.​ этом же файле,​. Он предоставляет возможность​

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

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

​ видим, данные его​​ листе, то жмем​ в одну таблицу.​ Либо посредством переключения​ структурированную таблицу. Нажмем​названия столбцов – идентичными​(Добавить).​Достоинство инструмента​ ячейкам, связанным ссылкой,​ рабочей книги. При​ нажмите​ щелкать по ярлычку​ новый, если необходимо)​

Способов консолидации данных

​формулы в таких​создавать связи с исходными​Убедитесь, что всех диапазонов​

​ с единообразным макетом.​​ сгруппированы. Чтобы посмотреть​ кнопку, которая расположена​ Давайте узнаем, как​ на открытую книгу.​ «плюсик» в левом​ (допускается перестановка колонок);​Повторите шаг​Консолидация​ если соответствующая рабочая​ желании можно скрыть​

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

• ​нет пустых строк и​4​в том, что​ книга открыта, но​ этот столбец, кликнув​.​ диапазон будет выбран​ нём по любой​ ошибкам, поскольку очень​. Если это поле​На основном листе щелкните​

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

Как для консолидации

​для документов​ он позволяет оставить​ не сработает, если​ правой кнопкой мыши​

1. ​Когда нажмете​ автоматически. Так что​ ячейке. На вкладке​ просто случайно выбираемых​

   • ​ остается неограниченный, возможность​ левый верхний угол​ статье: Создать шаблон.​ плюсик слева от​Переходим на тот лист,​ Excel​ применяется, когда исходные​ которых сформированы итоговые​Диапазоны с исходными данными​district2.xls​ данные там, где​ она закрыта.​

   • ​ и нажав​ОК​ эта задача гораздо​Data​ неправильной ячейки. Также​ обновления консолидации вручную.​ области, в которой​ Это также идеально​ таблицы.​

   • ​ где расположена таблица,​Естественно, что не все​

2. ​ диапазоны имеют неодинаковую​ суммы по количеству​ нужно открыть.​и​ они изначально находились​

   ​Если данные на листах​​Hide​, Excel обобщит все​ проще, чем кажется​(Данные) нажмите​ может быть сложно​Примечания:​

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

   

4. ​Для консолидированных данных отводим​​district3.xls​​ (т.е. на разных​ изменяются, например, Вы​(Скрыть) в контекстном​ выбранные данные на​ на первый взгляд.​

   ​Consolidate​ ошибку сразу после​ ​

   

5. ​ данные.​

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

   

   ​.​ листах и даже​ добавляете ещё один​ меню. Так Вы​​ новом листе. Вдоль​​Диапазоны для сведения, находящиеся​(Консолидация), чтобы открылось​​ ввода сложные формулы.​​Связи невозможно создать, если​
   ​Примечание:​ Excel.​ способом можно раскрыть​ жмем опять на​​ только те, которые​​ товары. Какие-то наименования​Программа Microsoft Excel позволяет​ новую книгу. Открываем​​Поставьте галочки напротив​​ в разных книгах),​​ офис уже после​​ скроете столбец, но​ левого края экрана​ на разных листах,​ диалоговое окно​Если данные для консолидации​ исходная и конечная​

   ​ Чтобы избежать перезаписи существующие​Консолидация данных по положению​ и любую другую​

   

6. ​ кнопку расположенную справа​​ соответствуют определенным условиям:​​ повторяются, а какие-то​​ выполнять разные виды​​ ее. Ставим курсор​Top Row​ но все равно​

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

   ​столбцы во всех таблицах​​ нет.​

   • ​ консолидации данных:​ в первую ячейку​(Подписи верхней строки),​ объединить их для​

   • ​ делом, добавьте эту​ в будущем Вы​ группировки, которые можно​ быть одинакового размера.​(Консолидация). Выберите функцию​ ячейках разных листов:​ одном листе.​ листе, убедитесь, оставьте​ способами.​Как видим, консолидация данных​ был внесен адрес​ должны иметь одинаковое​Для создания объединенного диапазона​По расположению. Консолидированные данные​ объединенного диапазона.​Left Column​

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

   • ​Консолидация данных по расположению​ в Эксель является​ ячеек.​ название (допускается лишь​ открываем меню «Консолидация».​ имеют одинаковое расположение​Внимание!!! Правее и ниже​

   • ​(Значения левого столбца)​ Да, консолидация не​ лист и сохраните​Одним из главных достоинств​ и скрывать данные.​ столбцов на каждом​

Консолидация данных с помощью формул

​ и ссылки на​ ссылки, которая использует​ диапазона — или​

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

​ листе может отличаться.​​ диапазоны, которые нужно​ ссылку на диапазон​ замените диапазон —​ консолидированные данные.​ областях том же​ благодаря которому можно​ консолидации, чтобы добавить​не должно быть столбцов​ (для примера).​ исходными.​ быть свободно. Команда​Create links to source​ данных, как это​ откройте лист, содержащий​Консолидация​​Create links to source​​ Например, если в​ свести. В нашем​ имен листов. Например​ выберите диапазон, во​Нажмите кнопку​ порядке и использует​ собрать воедино информацию​ уже выбранные нами​

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

​ «Консолидация» заполнит столько​ data​ можно сделать в​ консолидированную информацию, выделите​является то, что​ data​ сентябре мы открыли​ случае мы хотим​ для консолидации данных​ всплывающем окне Консолидация​данные​ одинаковых наклеек. Этот​

support.office.com

Как работает консолидация данных в Excel 2013 – 2003

​ расположенную не только​ ячейки в список​ пустыми значениями;​ из описанных выше​​ по разным принципам.​​ строк и столбцов,​(Создавать связи с​ сводной таблице. Но​ и удалите все​ данные могут располагаться​​(Создавать связи с​​ новый офис, то​ просуммировать значения, поэтому​ в ячейках с​ и обновлять его,​>​ метод используется для​ в разных таблицах​ диапазонов, жмем на​шаблоны у таблиц должны​ способом. Ставим флажки​ Но в консолидированной​​ сколько нужно.​​ исходными данными).​ в случае, когда​ строки со сведенными​ в разных рабочих​ исходными данными), то​ с сентября диапазон​ в поле​ A2 по продажам​

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

Работаем с несколькими наборами данных

​ у «значения левого​ таблице используются одинаковые​Переходим на вкладку «Данные».​Примечание:​ такой функционал не​ данными.​ книгах. Если у​ полученные данные ссылаются​ ячеек придется расширить,​Function​ через маркетинга включительно,​ действия. Создаст новый​(в группе​ нескольких листов, например​​ листах, но даже​​«Добавить»​Рассмотрим, как создать консолидированную​ столбца» и «подписи​ заглавия строк и​

​ В группе «Работа​Если вы не​ требуется, и нужно​На вкладке​ Вас есть несколько​ на содержащие их​ чтобы включить дополнительные​​(Функция) выберем​​ в ячейке E5​​ в ссылке на​​Работа с данными​ отделов бюджета листов,​​ размещенную в других​​.​ таблицу на примере​ верхней строки».​ столбцов.​ с данными» нажимаем​ включите опции​ простое обобщение информации,​Data​​ рабочих книг, содержащих​​ исходные ячейки. Кликнув​​ данные, относящиеся к​​Sum​

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

​Excel объединил информацию по​​ отсутствии постоянных категорий.​​Открывается диалоговое окно вида:​(Подписи верхней строки)​​ такой же форме,​​Consolidate​ и охватывающие различные​ данными (не по​ же самое нужно​Вы можете выбрать любую​ данные:​ удалить предыдущий перед​Функция​Консолидация по категории​ и быстро.​ список.​

​ структуру данных. Каждая​​ трем магазинам по​​ Содержат ссылки на​​На картинке открыт выпадающий​​ и​ как и исходные​(Консолидация) – Вы​ периоды времени, Вы​ ячейке с суммой),​ будет сделать для​ из 11 операций:​Существует много способов сведения​ Консолидация еще раз.​

​функцию, которую вы​: данные в исходных​Автор: Максим Тютюшев​Аналогичным образом, добавляем все​ из них расположена​ категориям. В отчете​ ячейки на других​ список «Функций». Это​Left Column​ данные, то консолидация​ увидите, что указанные​ можете консолидировать их​ Вы увидите ссылку​ каждого листа, содержащего​

​Sum​ данных в Microsoft​ Просто выберите старой​ хотите использовать для​ областях не расположены​Примечание:​ другие диапазоны, которые​ на отдельном листе,​ имеются данные по​ листах.​ виды вычислений, которые​(Значения левого столбца),​ – это простое​ ранее ссылки сохранились.​ при помощи этого​ на лист и​ дополнительные строки.​(Сумма),​ Excel. Вы можете​

Полезный совет

​ ссылки и нажмите​ консолидации данных. По​ в одном и​ Мы стараемся как можно​ будут участвовать в​ хотя по такому​ всем товарам. Независимо​​По отчету сводной таблицы.​​ может выполнять команда​ Excel просуммирует все​ и быстрое решение.​ Внесите изменения, добавив​ инструмента.​ ячейку, содержащую эти​​Вы можете присвоить диапазонам​​Count​​ использовать инструмент​​ клавишу Delete.​ умолчанию используется значение​​ том же порядке,​​ оперативнее обеспечивать вас​ процессе консолидации данных.​ же алгоритму можно​ от того, продаются​ Используется инструмент «Сводная​ «Консолидация» при работе​ ячейки с одинаковым​Урок подготовлен для Вас​ или удалив диапазоны,​​Для этого потребуется выполнить​​ данные.​​ имена, прежде чем​​(Количество),​

​Таблицы​​Нажмите кнопку​​ СУММ.​ но имеют одинаковые​ актуальными справочными материалами​Если же нужный диапазон​ создать консолидированную таблицу​ они в одном​ таблица» вместо «Консолидации​​ с данными. Выберем​​ расположением. Например: ячейка​ командой сайта office-guru.ru​​ или изменив их​​ те же самые​Если вы не отметили​​ начинать процесс консолидации.​​Average​и связанные с​ОК​​Вот пример, в котором​ метки. Используйте этот​​ на вашем языке.​ размещен в другой​ из данных расположенных​​ магазине или во​​ данных».​

Консолидированные данные

​ «Сумму» (значения в​​В2​​Источник: https://www.ablebits.com/office-addins-blog/2011/11/09/consolidating-data-excel/​ размер, и нажмите​ действия, как мы​ параметр​ Для этого выделите​(Среднее),​ ним фильтры, чтобы​, а Excel создаст​

​ выбраны три диапазоны​​ способ, чтобы консолидировать​ Эта страница переведена​​ книге (файле), то​ в разных книгах​ всех трех.​Консолидация данных по расположению​ исходных диапазонах будут​(distric1.xls) + ячейка​Перевел: Антон Андронов​ОК​ делали только что:​Create links to source​ диапазон и задайте​Max​

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

Полезный совет

​, чтобы создать консолидацию​ выберите пустой лист​ data​​ ему имя в​​(Максимум),​ информацию. Вы можете​ Кроме того можно​Выделите данные.​ листов с разными​ текст может содержать​ кнопку​​Открываем отдельный лист для​​ отчета вводим названия​ что исходные таблицы​​Переходим к заполнению следующего​​(district2.xls) + ячейка​Вы можете использовать инструмент​ заново.​ или добавьте новый,​(Создавать связи с​ поле​Min​ создать​ применить форматирование. Бывает​Далее в поле​​ макетами, но одинаковыми​​ неточности и грамматические​«Обзор…»​ консолидированной таблицы.​ строк и столбцов​ абсолютно идентичны. Одинаковые​ поля – «Ссылка».​​В2​​ «​Если Вы включаете параметр​

Форматируем данные

​ нажав​ исходными данными), то​Имя​(Минимум),​Сводную таблицу​ только необходимо отформатировать​ссылка​ метками данных.​ ошибки. Для нас​, выбираем файл на​На открывшемся листе отмечаем​ из консолидируемых диапазонов.​​ не только названия​​Ставим в поле курсор.​(district3.xls). Поскольку наши​Консолидация​Create Links to Source​Insert Sheet​ полученная консолидация –​

Разные рабочие книги

​слева от строки​Product​​, чтобы представить информацию​​ один раз, если​нажмите кнопку​Консолидация данных по категории​ важно, чтобы эта​ жестком диске или​ ячейку, которая будет​ Удобнее делать это​ столбцов, но и​ Открываем лист «1​ листы не идентичны,​» в Excel, чтобы​ Data​

​(Вставить лист) на​ это просто обобщение​ формул. Когда Вы​(Произведение),​ в различных ракурсах.​ не перезапустить консолидации.​Свернуть​​ похоже на создание​​ статья была вам​ съемном носителе, а​​ являться верхней левой​​ путем копирования.​​ наименования строк (см.​​ квартал». Выделяем таблицу​ значит нам нужно,​ объединить листы, расположенные​(Создавать связи с​ вкладке​​ данных без каких-либо​​ дадите имена всем​Count Numbers​

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

​В первую ячейку для​ пример выше). Если​ вместе с шапкой.​ чтобы Excel сложил​​ в одной или​​ исходными данными), то​Insert​ подробностей, без группировки​

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

Обновляем консолидацию

​ диапазон ячеек в​«Данные»​ вводим формулу со​ «тахта» занимает шестую​ появится первый диапазон​ подписями.​ один. На рисунках​ обновить консолидированные данные,​Consolidate​ результаты суммирования.​ курсор в поле​(Смещенное отклонение),​ виде сводки, которая​ приведут к появлению​

​Щелкните лист, содержащий данные,​​ можете легко переупорядочение​​ ли она вам,​​ этом файле. Естественно,​​кликаем по кнопке​ ссылками на исходные​ строку, то в​ для консолидации. Нажимаем​Если вы поставите галочку​ ниже представлены три​ нажав на вкладке​​(Консолидация). На этот​​Так как эти данные​Reference​

Предостережение!

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

​ и 4 это​Открываем поочередно второй, третий​​Create links to source​Прежде, чем начать:​​(Данные) команду​ чтобы выбрать лист​ можете использовать инструмент​F3​Var​ увидеть. В этой​ данных.​ кнопку​ Консолидация по категории,​ удобства также приводим​Точно так же можно​​ ленте в блоке​​ – в ячейку​​ значение должно занимать​​ и четвертый квартал​ data​Если рабочие листы​​Consolidate​​ в текущей рабочей​

​Trace Precedents​​и в открывшемся​​(Смещенная дисперсия) и​ статье я познакомлю​Убедитесь, что все категории,​Развернуть диалоговое окно​ можно создать сводную​ ссылку на оригинал​ произвести некоторые другие​ инструментов​ В2. Формула для​ тоже шестую строку.​ – выделяем диапазоны​(Создавать связи с​ идентичны, гораздо проще​(Консолидация), не удалив​ книге, нажмите кнопку​(Влияющие ячейки), чтобы​ окне​Varp​ Вас с инструментом​ которые не нужно​справа, чтобы вернуться​ таблицу .​ (на английском языке).​ настройки консолидированной таблицы.​«Работа с данными»​

​ суммы: =’1 квартал’!B2+’2​Это наиболее правильный способ​
​ данных. Жмем «Добавить».​
​ исходными данными), Excel​

​ создать внутренние ссылки​

office-guru.ru

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

​ предварительно старые результаты.​Browse​​ перейти к исходной​​Paste Name​(Несмещенная дисперсия).​Консолидация​ консолидировать, должны иметь​ в диалоговое окно​Примечание:​Для подведения итогов и​

​Для того, чтобы автоматически​​.​ квартал’!B2+’3 квартал’!B2.​ объединения данных, т.к.​Таблицы для консолидации отображаются​ создаст ссылки на​ (если все в​ Причина в том,​(Обзор), чтобы открыть​

​ ячейке, содержащей данные.​(Вставка имени) выберите​Кликните в поле​в Excel, с​​ уникальные подписи, которые​​Консолидация​ В этой статье были​ результатов из отдельных​ добавить название столбцов​Открывается окно настройки консолидации​Копируем формулу на весь​ исходные диапазоны идеальны​ в поле «Список​

1. ​ исходные данные. Когда​
2. ​ одной книге) или​ что в таком​​ другую рабочую книгу.​​ Для этого кликните​​ нужный диапазон. Таким​​Reference​
3. ​ помощью которого можно​​ появляются в диапазоне​​.​ созданы с Excel​
4. ​ листов, можно объединить​ в шапку, ставим​​ данных.​​ столбец:​​ для консолидации. Объединим​​ диапазонов».​​ исходные данные изменятся,​​ внешние ссылки (если​
5. ​ случае вместо обновления​​Теперь выберите ссылки, которые​​ по ячейке, содержащей​​ образом, Вы можете​​(Ссылка) и нажмите​​ извлекать данные из​​ только один источник.​
6. ​Если лист, содержащий​​ 2016. Хотя представления​​ данные из каждого​​ галочку около параметра​​В поле​Консолидация данных с помощью​​ таблицы, которые находятся​Чтобы автоматически сделать заголовки​​ консолидированные данные обновятся.​ книг несколько), чтобы​

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

​ и даже рабочих​ находятся в разных​​ объединить в другой​ использовании другой версии​​ листе. Листы может​. Для того, чтобы​требуется установить, какое​ объединяемые данные находятся​Созданы книги: Магазин 1,​ таблицы, ставим галочку​

1. ​OK​​Как видите, листы не​​ в другую, что​

​ Повторите шаги для​

​Formulas​ тогда позже не​
​справа от поля​
​ книг и обобщать​

​ ячейках разных листов:​

office-guru.ru

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

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

​ напротив «подписи верхней​.​ идентичны. Тем не​ в результате приведет​ каждой рабочей книги,​(Формулы) и найдите​

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

​ придётся вспоминать, что​ ввода. Выберите первый​ их на одном​Введите формулу со ссылками​

​Обзор​Выполните указанные ниже действия,​ же книге как​ устанавливаем галочку около​

• ​ будет выполняться при​ на разных листах.​
• ​ Магазин 3. Структура​ строки». Чтобы команда​
• ​Результат:​ менее, прелесть инструмента​

​ к полной чепухе.​ в которой содержаться​

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

​ чтобы консолидация нескольких​ главном листе или​ параметра​ совпадении строк и​ Например, в ячейке​ одинакова. Расположение данных​

​ суммировала все значения​Урок подготовлен для Вас​ «​ Вы можете обновить​

​ интересующие Вас данные.​

​Trace Precedents​ на листе​ консолидации – для​Работаем с несколькими наборами​ листов (по одной​ книгу. После поиска​ рабочих листов в​ в других книгах.​«Значения левого столбца»​

​ столбцов. Это могут​ В5 на листе​

​ идентично. Объединим их​ по каждой уникальной​ командой сайта office-guru.ru​Консолидация​ диапазоны, изменяя формулы​Мне стало легче переключаться​(Влияющие ячейки). Поскольку​Лист1​

​ этого откройте соответствующий​ данных​ на каждый лист).​ и нажмите кнопку​

​ главном листе.​ При объединении данных,​. Если вы хотите,​

​ быть следующие действия:​ «Магазин», в ячейке​ по позициям.​ записи крайнего левого​Источник: http://www.excel-easy.com/examples/consolidate.html​» в том, что​ вручную, но более​ между открытыми рабочими​ исходная ячейка находится​в ячейках​ лист, щелкнув по​Консолидированные данные​ Например, чтобы консолидировать​ОК​Если вы еще не​ чтобы упростить можно​ чтобы при обновлении​

​сумма;​ Е8 на листе​Открываем все три книги.​ столбца – напротив​Перевел: Антон Андронов​ он может с​ целесообразно будет создать​ книгами, когда я​ на другом листе,​A3:F40​ его ярлычку, и​

​Форматируем данные​ данные из листов​, Excel в поле​ сделано, настройте данные​

​ обновить и статистической​ данных в первичных​количество;​ «Склад» и т.п.​ Плюс пустую книгу,​ «значения левого столбца».​Автор: Антон Андронов​ легкостью складывать, подсчитывать,​

​ консолидацию заново.​

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

​ добавил кнопку​ наведите указатель мыши​.​

1. ​ выделите мышью все​Разные рабочие книги​ «Продажи» (в ячейке​ссылка​
2. ​ на каждом листе​ обработки при необходимости​ таблицах обновлялась также​среднее;​Скачать все примеры консолидации​ куда будет помещена​
3. ​ Для автоматического обновления​При выполнении ряда работ​ усреднять и т.д.​Если же Вы не​Switch Windows​
4. ​ на появившуюся чёрную​Как видите, в области​ необходимые данные, включая​Обновляем консолидацию​

​ B4), «Кадры» (в​введите путь к​ составные, сделав следующее:​ собирать данные.​ и вся информация​максимум;​ данных в Excel​ консолидированная таблица. В​ объединенного отчета при​ у пользователя Microsoft​ данные из разных​ включили параметр​(Перейти в другое​ стрелку, чтобы указатель​All References​

​ заголовки строк и​Представьте такую ситуацию: у​ ячейке F5) и​ файлу и добавление​Убедитесь, что все диапазоны​Например, если в каждом​

​ в консолидированной таблице,​минимум;​Если в книге включено​ пустой книге выбираем​ внесении новых данных​ Excel может быть​

1. ​ листов и документов.​Create Links to Source​ окно) на Панель​ принял вид пустой​(Список диапазонов) листы​ столбцов.​ Вас есть данные​ «Маркетинг» (в ячейке​
2. ​ восклицательный знак, путь​ данных представлены в​
3. ​ из региональных офисов​ то обязательно следует​произведение;​ автоматическое вычисление формул,​ верхний левый угол​ в исходные таблицы​ создано несколько однотипных​
4. ​ Это намного проще,​ Data​ быстрого доступа.​ белой стрелки. Дважды​

​ располагаются в алфавитном​Вернувшись в диалоговое окно​ за двенадцать месяцев​ B9) в ячейке​ к. Чтобы выбрать​ формате списка. Каждый​

​ есть свой лист​

​ установить галочку около​количество чисел;​ то при изменении​ чистого листа. Открываем​ – напротив «создавать​ таблиц в одном​ чем использование формул.​(Создавать связи с​

1. ​Если включить параметр​ щелкните, чтобы открыть​ порядке. Прежде чем​Consolidate​
2. ​ ведения бизнеса, информация​ A2 основного листа,​ другие данные можно​ столбец необходимо иметь​ расходов, с помощью​ параметра​
3. ​смещенное отклонение;​

​ данных в исходных​ меню инструмента «Консолидация».​ связи с исходными​ файле или в​Откройте три документа.​ исходными данными), то​Create Links to Source​ диалоговое окно​ продолжить, убедитесь, что​

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

​(Консолидация), нажмите​ по каждому месяцу​ введите следующее:​ нажмите Продолжить.​ метку (верхний колонтитул)​ консолидации можно свести​

​«Создавать связи с исходными​несмещенное отклонение;​ диапазонах объединенная таблица​Составим консолидированный отчет, используя​ данными».​ нескольких книгах.​Откройте пустую книгу. Нажмите​ Ваши сведенные данные​ Data​Go To​

​ указали ссылки на​Add​

​ сохранена на отдельном​Совет:​Вот пример, в котором​ в первой строке​ эти данные на​ данными»​смещенная дисперсия;​ будет обновляться автоматически.​ функцию «Среднее».​Внимание!!! Если вносить в​

​Данные необходимо свести воедино.​ кнопку​

​ представляют из себя​(Создавать связи с​(Переход) – ссылка​ все требуемые диапазоны.​(Добавить), чтобы добавить​ листе рабочей книги​

exceltable.com

​ Чтобы указать ссылку на​

Помогла ли Вам статья?

Консолидация данных – опция в Excel, которая позволяет объединять данные из разных таблиц в одну, а также объединять листы, расположенные в одном или разных файлах, в один. Чем же так полезна консолидация для интернет-маркетолога?

Разберем два наглядных примера.

Пример №1

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

Выгрузив данные в Excel, мы увидим, что в таблице есть строки, которые «по сути» являются дублями.

Это связано с тем, что статистика выгружена из двух рекламных систем (Яндекс.Директ и Google Ads), и в Google Рекламе для ключевых слов используется модификатор широкого соответствия (знак «+»).

То есть, вместо двух строчек доставка цветов москва и +доставка +цветов +москва мы должны получить одну с объединенными данными. Ведь это один ключ и нам нужно оценить его эффективность.

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

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

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

Последовательность действий:

— переходим на соседний лист (так удобнее);

— выделяем ячейку, в которую хотим вставить данные;

— переходим в Данные — Консолидация

В открывшемся окне нас интересуют следующие настройки:

• Функция – Сумма (поскольку хотим суммировать данные);
• Ссылка – выбираем весь диапазон данных на соседнем листе;
• Использовать в качестве имен – ставим галочки для подписи верхней строки и значения левого столбца.

Нажимаем ОК. Если все сделали верно, то на текущем листе получите результат в виде объединенных (консолидированных) данных:

Таблица наша также уменьшилась. До консолидации было 252 строки, после объединения данных их стало 212:

Видео консолидации примера №1:

Пример №2

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

Да, можно просто выгрузить данные из рекламных кабинетов Яндекс.Директ и Google Ads за общий период. Но что делать, когда данные по показам, кликам, расходам и конверсиям привязаны к бизнес-показателям, которых нет в интерфейсах, но которые завязаны на контекстной рекламе? А если в каждый из этих месяцев работали разные рекламные кампании (в один месяц – 10, в другой – 5 и т.д.)? Опять ручной процесс сведения данных.

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

Мы хотим свести данные в одну таблицу. Воспользуемся консолидацией.

В открывшемся окне нас интересуют следующие настройки:

• Функция – Сумма (поскольку хотим суммировать данные);
• Ссылка – выбираем весь диапазон данных на листах поочередно (с помощью кнопки Добавить);
• Использовать в качестве имен – ставим галочки для подписи верхней строки и значения левого столбца.

Галочка Создавать связи с исходными данными позволит производить пересчет консолидированного отчета автоматически при изменении данных в исходных файлах (в нашем примере – это листы сентябрь, октябрь, ноябрь). Нажимаем ОК.

Если все сделали правильно, то на текущем листе получите результат в виде объединенных (консолидированных) данных с трех таблиц в одной:

Слева появятся новые значки:

• 1 – свернуть все связи с исходными данными;
• 2 – развернуть все связи с исходными данными.

При нажатии на + раскроется соответствующая строка, в которой будут отображены данные по каждому из исходных листов.

Видео консолидации примера №2:

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

Понравился эксперимент с видео? Поставь 5.0 статье ->

Data consolidation is a feature in Microsoft Excel that allows you to collect data from different worksheets to one single sheet in the same workbook. This feature also allows the user to perform operations like sum, average, max, min, product, etc on the data to be consolidated. Consolidate meaning in English is to combine a number of things into a single more effective thing. It is a technique that summarizes the data from different worksheets and combines it into one single worksheet. Data consolidation is a useful tool for businesses that helps them to combine data from different worksheets to one after applying operations. For Example, Data consolidation can be used by financial analysts to combine department-wise budgets to make a company budget. If your data has no same cell values from the worksheets that you want to consolidate. Don’t worry. This function has got your back and it will consider them and treat them as a separate value.

Note: If there is any spelling mistake then consolidate function will treat it as a separate column. 

Ways to Consolidate Data in Multiple Worksheets

There are two ways to Consolidation Data:

• By Position: When the data, to be consolidated, presented in different sheets but share the same cell reference like the data in sheet1 starts from B2, and data in sheet2 also starts from B2 and the data has the same labels. Then Data Consolidation by position is used.
• By Category: When the data in sheet1 and sheet2 doesn’t share the same cell reference but have the same labels then Data Consolidation by category is used.

Data Consolidation by Position

Here, I’ll be taking an example in which I want to sum the data from 3 sheets named 2016, 2017, 2018(as shown below) to another sheet, consolidate, in the same workbook but a different sheet.

The data in the 3 sheets naming 2016, 2017 and 2018 is similar as seen in 2016, the sheet with the name consolidate will have the consolidated output of the previous sheets.

Note: Remember that in this method the data in every sheet must be at the same position i.e they share the same cell reference and same labels.

Step 1: In the Consolidate sheet, click on any cell where you want the consolidated data to appear.

Step 2: In the Data Tab> Data Tools>Consolidate 

Step 3: The consolidate dialogue box will appear as shown in the image. In the Function box, you need to select the operation that you want to perform with the data of the sheets. Here, I’m selecting Sum. 

Step 4: Click on the Collapse dialogue(marked in the circle in the image below) and open the sheet, that you want to consolidate, and select the data(as seen in Fig2). The selected data will appear in the Reference option and then click Add.

Step 5: After clicking Add,  the reference will be seen in the All reference option(as shown in the image below). And add all the sheet ranges that you want to consolidate as above. Check on the Top row and Left column checkboxes under Use labels in  and then click OK

You’ll get the consolidated data in the required worksheet.

Note: If you made any changes in the sheets then the changes won’t appear in the consolidated data sheet.

Data Consolidation by Category

There is a problem in the previous method that the cell reference of the data in every sheet must be the same, if not so, excel will create another row/column for the data in the final consolidated sheet. this issue is resolved in consolidation by category. To use this method you need to make only one change in the steps mentioned above.

Step 1: In the Consolidate sheet, click on any cell where you want the consolidated data to appear.

Step 2: In the Data Tab> Data Tools>Consolidate 

Step 3: The consolidate dialogue box will appear as shown in the image. In the Function box, you need to select the operation that you want to perform with the data of the sheets. Here, I’m selecting Sum. 

Step 4: Click on the Collapse dialogue(marked in the circle in the image below) and open the sheet, that you want to consolidate, and select the data(as seen in Fig2). The selected data will appear in the Reference option and then click Add.

Step 5: Select all the checkboxes under Use labels in and then click OK

And you are done. All the data from different sheets with different cell references will be consolidated into a new sheet of the same workbook. 

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

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

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

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

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

=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3

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

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

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

Фактически — это суммирование всех ячеек B3 на листах с 2001 по 2003, т.е. количество листов, по сути, может быть любым. Также в будущем возможно поместить между стартовым и финальным листами дополнительные листы с данными, которые также станут автоматически учитываться при суммировании.

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

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

Рассмотрим следующий пример. Имеем три разных файла (Иван.xlsx, Рита.xlsx и Федор.xlsx) с тремя таблицами:

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

Для того, чтобы выполнить такую консолидацию:

1. Заранее откройте исходные файлы
2. Создайте новую пустую книгу (Ctrl + N)
3. Установите в нее активную ячейку и выберите на вкладке (в меню) Данные — Консолидация (Data — Consolidate). Откроется соответствующее окно:

   

4. Установите курсор в строку Ссылка (Reference) и, переключившись в файл Иван.xlsx, выделите таблицу с данными (вместе с шапкой). Затем нажмите кнопку Добавить (Add) в окне консолидации, чтобы добавить выделенный диапазон в список объединяемых диапазонов.
5. Повторите эти же действия для файлов Риты и Федора. В итоге в списке должны оказаться все три диапазона:

   

 Обратите внимание, что в данном случае Excel запоминает, фактически, положение файла на диске, прописывая для каждого из них полный путь (диск-папка-файл-лист-адреса ячеек). Чтобы суммирование происходило с учетом заголовков столбцов и строк необходимо включить оба флажка Использовать в качестве имен (Use labels). Флаг Создавать связи с исходными данными (Create links to source data) позволит в будущем (при изменении данных в исходных файлах) производить пересчет консолидированного отчета автоматически.

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

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

Ссылки по теме

• Макрос для автоматической сборки данных с разных листов в одну таблицу 
• Макрос для сборки листов из нескольких файлов