Консолидация нескольких листов в одной сводной таблице
Консолидация данных представляет собой удобный способ объединения данных из нескольких источников в одном отчете. Например, если в каждом из региональных филиалов вашей компании есть сводная таблица расходов, с помощью консолидации данных можно объединить такие значения в корпоративный отчет о расходах. Такой отчет может содержать итоговые и средние данные по продажам, текущим уровням запасов и наиболее популярным продуктам в рамках всей организации.
Примечание: Другой способ консолидации данных — использование Power Query. Дополнительные сведения см. в справке по Power Query для Excel.
Объединение нескольких диапазонов
Для подведения итогов и обобщения результатов по данным в отдельных диапазонах можно консолидировать их в сводной таблице на главном листе. Диапазоны могут находиться в той же книге, что и главный лист, или в других книгах. Консолидированные данные легче обновлять и обобщать на регулярной основе или по мере необходимости.
Итоговый консолидированный отчет сводной таблицы может содержать следующие поля в области Список полей сводной таблицы, добавляемой в сводную таблицу: «Строка», «Столбец» и «Значение». Кроме того, в отчет можно включить до четырех полей фильтра, которые называются «Страница1», «Страница2», «Страница3» и «Страница4».
Настройка исходных данных
Каждый из диапазонов данных следует преобразовать в формат перекрестной таблицы с совпадающими именами строк и столбцов для элементов, которые вы хотите объединить. В выбранные данные не следует включать итоговые строки и итоговые столбцы. В приведенном ниже примере показано четыре диапазона в формате перекрестной таблицы.
Использование полей страницы
При консолидации данных можно использовать поля страницы, содержащие элементы, которые представляют один или несколько исходных диапазонов. Например, при консолидации данных бюджета отдела маркетинга, отдела продаж и производственного отдела поле страницы может содержать отдельный элемент с данными по каждому из этих отделов, а также элемент, содержащий сводные данные. В примере ниже показана сводная таблица, в которой выбраны одно поле страницы и несколько элементов.
Использование именованных диапазонов
Если велика вероятность того, что в следующий раз при консолидации данных исходный диапазон данных изменится (например, изменится число строк), рекомендуется задать имя для каждого из исходных диапазонов на разных листах. Эти имена можно использовать при консолидации диапазонов на главном листе. В случае расширения исходного диапазона перед обновлением сводной таблицы можно обновить диапазон на отдельном листе для указанного имени таким образом, чтобы включить в него новые данные.
Использование трехмерных привязок или команды «Консолидировать»
В Excel также доступны другие способы консолидации данных, которые позволяют работать с данными в разных форматах и макетах. Например, вы можете создавать формулы с объемными ссылками или использовать команду Консолидация (доступную на вкладке Данные в группе Работа с данными).
Консолидация нескольких диапазонов
Для консолидации нескольких диапазонов вы можете воспользоваться мастером сводных таблиц и диаграмм. В нем можно указать, сколько полей страницы будет использоваться: ни одного, одно или несколько.
Консолидация данных без использования полей страницы
Чтобы объединить данные всех диапазонов и создать консолидированный диапазон без полей страницы, сделайте следующее:
- Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:
- Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.
- Нажмите Настроить панель быстрого доступа (…) в левом нижнем углу под лентой, а затем нажмите Дополнительные команды.
- В списке Выбрать команды из выберите пункт Все команды.
- Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить, а затем — кнопку ОК.
Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.
Консолидация данных с использованием одного поля страницы
Чтобы включить одно поле страницы, содержащее элемент для каждого исходного диапазона, а также элемент для консолидации всех диапазонов, сделайте следующее:
- Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:
- Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.
- Нажмите Настроить панель быстрого доступа (…) в левом нижнем углу под лентой, а затем нажмите Дополнительные команды.
- В списке Выбрать команды из выберите пункт Все команды.
- Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить, а затем — кнопку ОК.
Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.
Консолидация данных с использованием нескольких полей страницы
Вы можете создать несколько полей страницы и назначить имена элементов каждому из исходных диапазонов. Это позволяет выполнять частичную или полную консолидацию. Например, в одном поле страницы могут консолидироваться данных отдела маркетинга и отдела продаж без данных производственного отдела, а в другом поле — данные всех трех отделов. Чтобы создать консолидацию с использованием нескольких полей страницы, сделайте следующее:
- Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:
- Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.
- Нажмите Настроить панель быстрого доступа (…) в левом нижнем углу под лентой, а затем нажмите Дополнительные команды.
- В списке Выбрать команды из выберите пункт Все команды.
- Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить, а затем — кнопку ОК.
Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.
- Если в разделе Во-первых, укажите количество полей страницы сводной таблицы задано число 1, выберите каждый из диапазонов, а затем введите уникальное имя в поле Первое поле. Если у вас четыре диапазона, каждый из которых соответствует кварталу финансового года, выберите первый диапазон, введите имя «Кв1», выберите второй диапазон, введите имя «Кв2» и повторите процедуру для диапазонов «Кв3» и «Кв4».
- Если в разделе Во-первых, укажите количество полей страницы сводной таблицы задано число 2, выполните аналогичные действия в поле Первое поле. Затем выберите два диапазона и введите в поле Второе поле одинаковое имя, например «Пг1» и «Пг2». Выберите первый диапазон и введите имя «Пг1», выберите второй диапазон и введите имя «Пг1», выберите третий диапазон и введите имя «Пг2», выберите четвертый диапазон и введите имя «Пг2».
Объединение данных с нескольких листов
Если данные, которые требуется проанализировать, представлены на нескольких листах или в нескольких книгах, их можно объединить на одном листе с помощью команды «Консолидация». Например, если есть отдельный лист расходов для каждого регионального представительства, с помощью консолидации можно создать на базе этих данных корпоративный лист расходов. Такой лист может содержать итоговые и средние данные по продажам, текущим уровням запасов и наиболее популярным продуктам в рамках всей организации.
Тип консолидации следует выбирать с учетом того, как выглядят объединяемые листы. Если данные на листах расположены единообразно (названия строк и столбцов могут при этом различаться), воспользуйтесь консолидацией по расположению. Если же на листах для соответствующих категорий используются одинаковые названия строк и столбцов (данные при этом могут быть расположены по-разному), используйте консолидацию по категории.
Консолидация по расположению
Для консолидации по расположению диапазон данных на каждом из исходных листов должен иметь формат списка без пустых строк и столбцов.
- Откройте каждый исходный лист и убедитесь, что данные на каждом листе расположены в одинаковом положении.
- На конечном листе щелкните верхнюю левую ячейку области, в которой требуется разместить консолидированные данные.
Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.
- Перейдите в раздел >Консолидация данных.
- В поле Функция выберите функцию, которую excel будет использовать для консолидации данных.
- Выделите на каждом листе нужные данные. Путь к файлу вводится в поле Все ссылки.
- После добавления данных из каждого исходного листа и книги нажмите кнопку ОК.
Консолидация по категории
Для консолидации по категории диапазон данных на каждом из исходных листов должен иметь формат списка без пустых строк и столбцов. Кроме того, категории должны быть последовательно помечены. Например, если один из столбцов называется Сред. , а другой — Среднее, консолидация не просуммирует эти столбцы.
- Откройте каждый из исходных листов.
- На конечном листе щелкните верхнюю левую ячейку области, в которой требуется разместить консолидированные данные.
Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.
- Перейдите в раздел >Консолидация данных.
- В поле Функция выберите функцию, которую excel будет использовать для консолидации данных.
- Установите флажки в группе Использовать в качестве имен, указывающие, где в исходных диапазонах находятся названия: подписи верхней строки, значения левого столбца либо оба флажка одновременно.
- Выделите на каждом листе нужные данные. Не забудьте включить в них ранее выбранные данные из верхней строки или левого столбца. Путь к файлу вводится в поле Все ссылки.
- После добавления данных из каждого исходного листа и книги нажмите кнопку ОК.
Примечание: Любые названия, не совпадающие с названиями в других исходных областях, могут привести к появлению в консолидированных данных отдельных строк или столбцов.
Важно: Microsoft Office для Mac 2011 больше не поддерживается. Перейдите на Microsoft 365, чтобы работать удаленно с любого устройства и продолжать получать поддержку.
Консолидация по расположению
Для консолидации по расположению диапазон данных на каждом из исходных листов должен иметь формат списка без пустых строк и столбцов.
- Откройте каждый из исходных листов и убедитесь в том, что данные на них расположены одинаково.
- На конечном листе щелкните верхнюю левую ячейку области, в которой требуется разместить консолидированные данные.
Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.
Консолидация по категории
Для консолидации по категории диапазон данных на каждом из исходных листов должен иметь формат списка без пустых строк и столбцов. Кроме того, категории должны быть названы одинаково. Например, если один из столбцов называется Сред. , а другой — Среднее, консолидация не просуммирует эти столбцы.
- Откройте каждый из исходных листов.
- На конечном листе щелкните верхнюю левую ячейку области, в которой требуется разместить консолидированные данные.
Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.
Примечание: Любые названия, не совпадающие с названиями в других исходных областях, могут привести к появлению в консолидированных данных отдельных строк или столбцов.
Data consolidating in Excel with examples of usage
Performing a range of works, the user of Microsoft Excel may create to multiple similar tables in one file or in several books.
The data must be brought together in one report for getting the General idea. Tool Consolidation copes with this task.
How to do data consolidation in Excel
There are 4 sheets with the same structure — quarterly results of sales of furniture, for example.
You need to make the General report by using the «data consolidation». First check out to:
- the layouts of all the tables will be the same;
- the names of columns – are identical (it is allowed to rearrange of columns);
- there are no empty rows and columns.
The ranges with the source value need to open.
For consolidated data you need to ascribe and open a new worksheet or a new workbook. Put the cursor in the first cell of the merged range.
Attention. To the right and below of this cell should be free. The team « Consolidation» will fill as many rows and columns as you need.
After that, go to the tab «DATA». In the group «Data Tools», click the button «Consolidate».
There dialog box «Consolidate» is opened:
The team «Consolidation» when working with data may be performed these types of calculations.
Then to choose «Amount» (the values in the source ranges will be added).
Proceed to filling of the next field «Link».
Put the cursor in the field and open the worksheet «1-st quarter», allocated the table together with the cap. In the «Link» field will be the first range of consolidating and click «Add».
Alternately open the second, the third and the fourth quarter – allocated ranges. Click «Add».
The tables for consolidation are shown in the field «The list of ranges».
To automatically make the header for the columns of the consolidated table, put a check next to « signature top of the line» — for summarizing of the team all of the values for each unique entry of the leftmost column opposite «the values of the left column». For automatic updating of the joint report when entering new data into the original tables – in front «create links to source data».
Attention. If you make in the original tables to the new values over the selected for consolidation range, they will not display in the merged report. To be able to enter value by manually, uncheck the «Create links to source data». For exiting from the «Consolidation» menu and creating the PivotTable, click OK.
The consolidated statement represents a structured table. Press the «+» in the left field – there will be values, on the basis of which are formed the totals by quantity and revenue.
The consolidating of data in Excel: practical work
The Microsoft Excel program allows you to perform different types of data consolidation:
- On the location. Consolidated data have the same location and order from the source.
- For the category. Value is organized according to different principles, but in the consolidated table uses the same titles of rows and columns.
- According to the formula. Apply them in the absence of permanent categories. They are refer to the cells in other worksheets.
- In the PivotTable report. Use the tool «The pivot table» instead of «The consolidation» one.
Consolidating data by location (position) implies that the source of the table is identical. The same not only the names of columns, but also the names of the strings (see the example above). If in the range of 1 «Ottoman» it is placed the sixth row, in the range of 2, 3 and 4 this value must also takes the sixth row.
This is the most correct way to combine the value, because the source ranges are ideal for consolidation. Join the tables that are in different workbooks.
The books are created: the Store 1, the Store 2 and the Store 3. The structure is the same and the location of the value is identical also. Combine them by positions.
- Open all three books, plus a blank book, which will be placed a consolidated table. In the blank workbook, choose the upper-left corner of the blank slate. Open the tool menu «Consolidation».
- Prepare the consolidated report, using the function «Average».
- To show the way to books with the original ranges, put the cursor in the field «Link». On the tab «VIEW», click the «Switch to another window».
- Select alternately the names of the files, allocated to ranges in open workbooks – click on «Add».
Links to files of other Workbooks Excel on the disk D:
- ‘D:\[Store1.xlsx]Sheet1’!$A$1:$M$13
- ‘D:\[Store2.xlsx]Sheet1’!$A$1:$M$13
- ‘D:\[Store3.xlsx]Sheet1’!$A$1:$M$13
- ‘D:\[Store4.xlsx]Sheet1’!$A$1:$M$13
The comment: show for the program the path to the source range you may with helping the «Browse» button, or by switching to the opening book.
The consolidated table:
The consolidating data by category is used when the source ranges have different structure. For example, in shops are implemented to different products. Some designations are repeated, and some ones are not.
- To create of the merged range open «Consolidation» menu and select the function «Sum» (for example).
- Add source ranges any of the method, which was above-described. We put the check boxes for «the values of the left column» and «the signatures top of the line».
- Press OK.
Excel united to the information on three shops into categories. The report includes value on all goods, regardless of whether they are sold in one store or in all three.
The examples of data consolidation in Excel
On the sheet for the summary report, enter the names of the row and columns from the consolidation ranges. It will be easier to do by copying.
In the first cell for the values of the united table, enter the formula with references with the sources cells on each worksheet. In our example we`ll put it in the cell B2. The formula for sum is:
Copy the formula to the whole column:
The consolidating data with using formulas is convenient, when the merged value are in different cells on different sheets. For example, in the cell B5 in the worksheet «Store» in the cell E8 on the sheet «Store», etc.
If in the book is included to automatic calculation of formulas, when you change the value in the source ranges the merged table will be updated automatically.
Консолидация данных с нескольких листов
Чтобы суммировать и отчеты о результатах отдельных листов, можно объединить данные с каждого листа в главный лист. Листы могут находиться в той же книге, что и главный лист, или в других книгах. При консолидации данных вы собираете данные, чтобы было проще обновлять и агрегировать их по мере необходимости.
Например, если в каждом из региональных офисов есть свой лист расходов, с помощью консолидации можно свести эти данные на главном листе корпоративных расходов. Главный лист также может содержать итоговые и средние значения продаж, данные о складских запасах и информацию о самых популярных товарах в рамках всей компании.
Совет: Если вы часто консолидируете данные, это может помочь создать новые листы из шаблона листа, использующего согласованный макет. Дополнительные сведения о шаблонах см. в статье Создание шаблона. Также советуем добавить в шаблон таблицы Excel.
Способы консолидации данных
Существует два способа консолидации данных: по позиции или категории.
Консолидация по позициям. Данные в исходных областях имеют одинаковый порядок и используют одни и те же метки. Используйте этот способ, чтобы консолидировать данные из нескольких листов, основанных на одном шаблоне, например отчетов о бюджете.
Консолидация по категории: данные в исходных областях не расположены в одном и том же порядке, но имеют одинаковые метки. Используйте этот способ, чтобы консолидировать данные из нескольких листов с разными макетами, но одинаковыми метками данных.
- Консолидация данных по категориям аналогична созданию сводной таблицы. Однако с помощью сводной таблицы можно легко реорганизовать категории. Если требуется более гибкая консолидация по категориям, рассмотрите возможность создания сводной таблицы .
Примечание: Примеры, приведенные в этой статье, были созданы с помощью Excel 2016. Хотя ваше представление может отличаться, если вы используете другую версию Excel, шаги будут одинаковыми.
Как консолидировать
Чтобы объединить несколько листов в главный лист, выполните следующие действия.
- Если вы еще этого не сделали, настройте данные на каждом листе, выполнив следующие действия.
- Убедитесь, что каждый диапазон данных имеет формат списка. Каждый столбец должен иметь метку (заголовок) в первой строке и содержать аналогичные данные. В списке не должно быть пустых строк или столбцов.
- Поместите каждый диапазон на отдельный лист, но не вводите ничего на главном листе, где планируется объединить данные. Excel сделает это за вас.
- Убедитесь, что каждый диапазон имеет одинаковый макет.
- На основном листе щелкните левый верхний угол области, в которой требуется разместить консолидированные данные.
Примечание: Чтобы избежать перезаписи существующих данных на главном листе, оставьте достаточно ячеек справа и под этой ячейкой для объединенных данных.
- Щелкните Data>Консолидация (в группе Data Tools).
- Выберите в раскрывающемся списке Функцияитоговая функция, которую требуется использовать для консолидации данных. По умолчанию используется функция SUM. Ниже приведен пример выбора трех диапазонов листа:
- Выделите данные. Затем в поле Ссылка нажмите кнопку Свернуть , чтобы сжать панель и выбрать данные на листе.
Щелкните лист с данными, которые вы хотите консолидировать, а затем нажмите кнопку раскрытия диалогового окна справа, чтобы вернуться в диалоговое окно Консолидация.
Если лист, содержащий данные, которые необходимо объединить, находится в другой книге, нажмите кнопку Обзор , чтобы найти эту книгу. После поиска и нажатия кнопки ОК Excel введет путь к файлу в поле Ссылка и добавит восклицательный знак в этот путь. Затем можно продолжить выбор других данных. Ниже приведен пример выбора трех диапазонов листа:
- Во всплывающем окне Консолидация нажмите кнопку Добавить. Повторите это, чтобы добавить все объединяемые диапазоны.
- Автоматическое обновление и обновление вручную Если вы хотите, чтобы Excel автоматически обновлял таблицу консолидации при изменении исходных данных, просто установите флажок Создать ссылки на исходные данные . Если этот флажок не установлен, можно обновить консолидацию вручную.
- Связи невозможно создать, если исходная и конечная области находятся на одном листе.
- Если необходимо изменить экстент диапазона или заменить диапазон, щелкните диапазон во всплывающем окне Консолидация и обновите его, выполнив описанные выше действия. При этом будет создана новая ссылка на диапазон, поэтому вам потребуется удалить предыдущую ссылку перед консолидацией. Просто выберите старую ссылку и нажмите клавишу DELETE.
- Все названия, не совпадающие с названиями в других исходных областях, приведут к появлению дополнительных строк или столбцов в консолидированных данных.
- Убедитесь, что все категории, которые не требуется консолидировать, имеют уникальные метки, которые отображаются только в одном исходном диапазоне.
Консолидация данных с помощью формул
Если данные для консолидации есть в разных ячейках на разных листах:
Введите формулу со ссылками на ячейки других листов, по одной на каждый лист. Например, чтобы консолидировать данные из листов «Продажи» (в ячейке B4), «Кадры» (в ячейке F5) и «Маркетинг» (в ячейке B9), в ячейке A2 основного листа, введите следующее:
Совет: Ввод ссылки на ячейку, например Sales! B4 — в формуле без ввода введите формулу до точки, в которой требуется ссылка, затем перейдите на вкладку листа и щелкните ячейку. Excel заполтит имя листа и адрес ячейки. ПРИМЕЧАНИЕ. Формулы в таких случаях могут быть подвержены ошибкам, так как очень легко случайно выбрать неправильную ячейку. Также может быть трудно обнаружить ошибку после ввода сложной формулы.
Если данные для консолидации находится в одних и том же ячейках на разных листах:
Введите формулу с трехмерной ссылкой, которая указывает на диапазон имен листов. Например, чтобы объединить данные в ячейках A2 от Sales до Marketing включительно, в ячейке E5 главного листа необходимо ввести следующее:
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.