Консолидация данных с нескольких листов
Чтобы суммировать и отчеты о результатах отдельных листов, можно объединить данные с каждого листа в главный лист. Листы могут находиться в той же книге, что и главный лист, или в других книгах. При консолидации данных вы собираете данные, чтобы было проще обновлять и агрегировать их по мере необходимости.
Например, если в каждом из региональных офисов есть свой лист расходов, с помощью консолидации можно свести эти данные на главном листе корпоративных расходов. Главный лист также может содержать итоговые и средние значения продаж, данные о складских запасах и информацию о самых популярных товарах в рамках всей компании.
Совет: Если вы часто консолидируете данные, это может помочь создать новые листы из шаблона листа, использующего согласованный макет. Дополнительные сведения о шаблонах см. в статье Создание шаблона. Также советуем добавить в шаблон таблицы Excel.
Способы консолидации данных
Существует два способа консолидации данных: по позиции или категории.
Консолидация по позициям. Данные в исходных областях имеют одинаковый порядок и используют одни и те же метки. Используйте этот способ, чтобы консолидировать данные из нескольких листов, основанных на одном шаблоне, например отчетов о бюджете.
Консолидация по категории: данные в исходных областях не расположены в одном и том же порядке, но имеют одинаковые метки. Используйте этот способ, чтобы консолидировать данные из нескольких листов с разными макетами, но одинаковыми метками данных.
- Консолидация данных по категориям аналогична созданию сводной таблицы. Однако с помощью сводной таблицы можно легко реорганизовать категории. Если требуется более гибкая консолидация по категориям, рассмотрите возможность создания сводной таблицы .
Примечание: Примеры, приведенные в этой статье, были созданы с помощью Excel 2016. Хотя ваше представление может отличаться, если вы используете другую версию Excel, шаги будут одинаковыми.
Как консолидировать
Чтобы объединить несколько листов в главный лист, выполните следующие действия.
- Если вы еще этого не сделали, настройте данные на каждом листе, выполнив следующие действия.
- Убедитесь, что каждый диапазон данных имеет формат списка. Каждый столбец должен иметь метку (заголовок) в первой строке и содержать аналогичные данные. В списке не должно быть пустых строк или столбцов.
- Поместите каждый диапазон на отдельный лист, но не вводите ничего на главном листе, где планируется объединить данные. Excel сделает это за вас.
- Убедитесь, что каждый диапазон имеет одинаковый макет.
- На основном листе щелкните левый верхний угол области, в которой требуется разместить консолидированные данные.
Примечание: Чтобы избежать перезаписи существующих данных на главном листе, оставьте достаточно ячеек справа и под этой ячейкой для объединенных данных.
Щелкните лист с данными, которые вы хотите консолидировать, а затем нажмите кнопку раскрытия диалогового окна справа, чтобы вернуться в диалоговое окно Консолидация.
Если лист, содержащий данные, которые необходимо объединить, находится в другой книге, нажмите кнопку Обзор , чтобы найти эту книгу. После поиска и нажатия кнопки ОК Excel введет путь к файлу в поле Ссылка и добавит восклицательный знак в этот путь. Затем можно продолжить выбор других данных. Ниже приведен пример выбора трех диапазонов листа:
- Связи невозможно создать, если исходная и конечная области находятся на одном листе.
- Если необходимо изменить экстент диапазона или заменить диапазон, щелкните диапазон во всплывающем окне Консолидация и обновите его, выполнив описанные выше действия. При этом будет создана новая ссылка на диапазон, поэтому вам потребуется удалить предыдущую ссылку перед консолидацией. Просто выберите старую ссылку и нажмите клавишу DELETE.
- Все названия, не совпадающие с названиями в других исходных областях, приведут к появлению дополнительных строк или столбцов в консолидированных данных.
- Убедитесь, что все категории, которые не требуется консолидировать, имеют уникальные метки, которые отображаются только в одном исходном диапазоне.
Консолидация данных с помощью формул
Если данные для консолидации есть в разных ячейках на разных листах:
Введите формулу со ссылками на ячейки других листов, по одной на каждый лист. Например, чтобы консолидировать данные из листов «Продажи» (в ячейке B4), «Кадры» (в ячейке F5) и «Маркетинг» (в ячейке B9), в ячейке A2 основного листа, введите следующее:
Совет: Ввод ссылки на ячейку, например Sales! B4 — в формуле без ввода введите формулу до точки, в которой требуется ссылка, затем перейдите на вкладку листа и щелкните ячейку. Excel заполтит имя листа и адрес ячейки. ПРИМЕЧАНИЕ. Формулы в таких случаях могут быть подвержены ошибкам, так как очень легко случайно выбрать неправильную ячейку. Также может быть трудно обнаружить ошибку после ввода сложной формулы.
Если данные для консолидации находится в одних и том же ячейках на разных листах:
Введите формулу с трехмерной ссылкой, которая указывает на диапазон имен листов. Например, чтобы объединить данные в ячейках A2 от Sales до Marketing включительно, в ячейке E5 главного листа необходимо ввести следующее:
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Сводные таблицы в Excel с примерами и описанием
Создание сводной таблицы в Excel из нескольких листов и таблиц для быстрого формирования сводных отчетов по данным. Примеры применения сводных таблиц на практике.
Создание сводных таблиц
Обучение сводным таблицам Excel на примерах.
Формирования отчетов с помощью сводной таблице на примере работы с клиентской базой данных. Изменение структуры отчетов в настройках полей сводной таблицы.
Обновление данных в сводных таблицах Excel.
Представление и обновление данных в сводных таблицах: подробная инструкция с картинками. Учимся обновлять отчет при внесении новых данных в исходный диапазон. Настраиваем автоматическое обновление.
Управление сводными таблицами в Excel.
Работа со сводными таблицами при фильтрации, сортировке, группировке. Использование формул с добавлением вычисляемых полей. Вставка дополнительных столбцов в сводный отчет.
Примеры работы со сводными таблицами в Excel.
Работа с мастером создания сводных таблиц. Продвинутые настройки сводных таблиц, которые отображают их широкие возможности при формировании гибко-настраиваемых отчетов.
Работа со сводными таблицами в Excel на примерах.
Примеры по работе над сводными таблицами в Excel: автоматическое обновление, объединение нескольких файлов, группировка по дате, добавление вычисляемого поля и детализация данных в отчетах.
Создание сводной таблицы Excel из нескольких листов.
Создание сводной таблицы в Excel на основе данных из нескольких разных листов и сторонних источников. Быстрое создание и настройка отчета с помощью мастера сводных таблиц.
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры
Объединение данных с нескольких листов
Если данные, которые требуется проанализировать, представлены на нескольких листах или в нескольких книгах, их можно объединить на одном листе с помощью команды «Консолидация». Например, если есть отдельный лист расходов для каждого регионального представительства, с помощью консолидации можно создать на базе этих данных корпоративный лист расходов. Такой лист может содержать итоговые и средние данные по продажам, текущим уровням запасов и наиболее популярным продуктам в рамках всей организации.
Тип консолидации следует выбирать с учетом того, как выглядят объединяемые листы. Если данные на листах расположены единообразно (названия строк и столбцов могут при этом различаться), воспользуйтесь консолидацией по расположению. Если же на листах для соответствующих категорий используются одинаковые названия строк и столбцов (данные при этом могут быть расположены по-разному), используйте консолидацию по категории.
Консолидация по расположению
Для консолидации по расположению диапазон данных на каждом из исходных листов должен иметь формат списка без пустых строк и столбцов.
- Откройте каждый исходный лист и убедитесь, что данные на каждом листе расположены в одинаковом положении.
- На конечном листе щелкните верхнюю левую ячейку области, в которой требуется разместить консолидированные данные.
Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.
Консолидация по категории
Для консолидации по категории диапазон данных на каждом из исходных листов должен иметь формат списка без пустых строк и столбцов. Кроме того, категории должны быть последовательно помечены. Например, если один из столбцов называется Сред. , а другой — Среднее, консолидация не просуммирует эти столбцы.
- Откройте каждый из исходных листов.
- На конечном листе щелкните верхнюю левую ячейку области, в которой требуется разместить консолидированные данные.
Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.
Примечание: Любые названия, не совпадающие с названиями в других исходных областях, могут привести к появлению в консолидированных данных отдельных строк или столбцов.
Важно: Microsoft Office для Mac 2011 больше не поддерживается. Перейдите на Microsoft 365, чтобы работать удаленно с любого устройства и продолжать получать поддержку.
Консолидация по расположению
Для консолидации по расположению диапазон данных на каждом из исходных листов должен иметь формат списка без пустых строк и столбцов.
- Откройте каждый из исходных листов и убедитесь в том, что данные на них расположены одинаково.
- На конечном листе щелкните верхнюю левую ячейку области, в которой требуется разместить консолидированные данные.
Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.
Консолидация по категории
Для консолидации по категории диапазон данных на каждом из исходных листов должен иметь формат списка без пустых строк и столбцов. Кроме того, категории должны быть названы одинаково. Например, если один из столбцов называется Сред. , а другой — Среднее, консолидация не просуммирует эти столбцы.
- Откройте каждый из исходных листов.
- На конечном листе щелкните верхнюю левую ячейку области, в которой требуется разместить консолидированные данные.
Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.
Примечание: Любые названия, не совпадающие с названиями в других исходных областях, могут привести к появлению в консолидированных данных отдельных строк или столбцов.
Консолидация нескольких листов в одной сводной таблице
Консолидация данных представляет собой удобный способ объединения данных из нескольких источников в одном отчете. Например, если в каждом из региональных филиалов вашей компании есть сводная таблица расходов, с помощью консолидации данных можно объединить такие значения в корпоративный отчет о расходах. Такой отчет может содержать итоговые и средние данные по продажам, текущим уровням запасов и наиболее популярным продуктам в рамках всей организации.
Примечание: Другой способ консолидации данных — использование Power Query. Дополнительные сведения см. в справке по Power Query для Excel.
Объединение нескольких диапазонов
Для подведения итогов и обобщения результатов по данным в отдельных диапазонах можно консолидировать их в сводной таблице на главном листе. Диапазоны могут находиться в той же книге, что и главный лист, или в других книгах. Консолидированные данные легче обновлять и обобщать на регулярной основе или по мере необходимости.
Итоговый консолидированный отчет сводной таблицы может содержать следующие поля в области Список полей сводной таблицы, добавляемой в сводную таблицу: «Строка», «Столбец» и «Значение». Кроме того, в отчет можно включить до четырех полей фильтра, которые называются «Страница1», «Страница2», «Страница3» и «Страница4».
Настройка исходных данных
Каждый из диапазонов данных следует преобразовать в формат перекрестной таблицы с совпадающими именами строк и столбцов для элементов, которые вы хотите объединить. В выбранные данные не следует включать итоговые строки и итоговые столбцы. В приведенном ниже примере показано четыре диапазона в формате перекрестной таблицы.
Использование полей страницы
При консолидации данных можно использовать поля страницы, содержащие элементы, которые представляют один или несколько исходных диапазонов. Например, при консолидации данных бюджета отдела маркетинга, отдела продаж и производственного отдела поле страницы может содержать отдельный элемент с данными по каждому из этих отделов, а также элемент, содержащий сводные данные. В примере ниже показана сводная таблица, в которой выбраны одно поле страницы и несколько элементов.
Использование именованных диапазонов
Если велика вероятность того, что в следующий раз при консолидации данных исходный диапазон данных изменится (например, изменится число строк), рекомендуется задать имя для каждого из исходных диапазонов на разных листах. Эти имена можно использовать при консолидации диапазонов на главном листе. В случае расширения исходного диапазона перед обновлением сводной таблицы можно обновить диапазон на отдельном листе для указанного имени таким образом, чтобы включить в него новые данные.
Использование трехмерных привязок или команды «Консолидировать»
В Excel также доступны другие способы консолидации данных, которые позволяют работать с данными в разных форматах и макетах. Например, вы можете создавать формулы с объемными ссылками или использовать команду Консолидация (доступную на вкладке Данные в группе Работа с данными).
Консолидация нескольких диапазонов
Для консолидации нескольких диапазонов вы можете воспользоваться мастером сводных таблиц и диаграмм. В нем можно указать, сколько полей страницы будет использоваться: ни одного, одно или несколько.
Консолидация данных без использования полей страницы
Чтобы объединить данные всех диапазонов и создать консолидированный диапазон без полей страницы, сделайте следующее:
- Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:
- Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.
- Нажмите Настроить панель быстрого доступа (…) в левом нижнем углу под лентой, а затем нажмите Дополнительные команды.
- В списке Выбрать команды из выберите пункт Все команды.
- Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить, а затем — кнопку ОК.
Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.
Консолидация данных с использованием одного поля страницы
Чтобы включить одно поле страницы, содержащее элемент для каждого исходного диапазона, а также элемент для консолидации всех диапазонов, сделайте следующее:
- Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:
- Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.
- Нажмите Настроить панель быстрого доступа (…) в левом нижнем углу под лентой, а затем нажмите Дополнительные команды.
- В списке Выбрать команды из выберите пункт Все команды.
- Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить, а затем — кнопку ОК.
Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.
Консолидация данных с использованием нескольких полей страницы
Вы можете создать несколько полей страницы и назначить имена элементов каждому из исходных диапазонов. Это позволяет выполнять частичную или полную консолидацию. Например, в одном поле страницы могут консолидироваться данных отдела маркетинга и отдела продаж без данных производственного отдела, а в другом поле — данные всех трех отделов. Чтобы создать консолидацию с использованием нескольких полей страницы, сделайте следующее:
- Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:
- Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.
- Нажмите Настроить панель быстрого доступа (…) в левом нижнем углу под лентой, а затем нажмите Дополнительные команды.
- В списке Выбрать команды из выберите пункт Все команды.
- Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить, а затем — кнопку ОК.
Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.
- Если в разделе Во-первых, укажите количество полей страницы сводной таблицы задано число 1, выберите каждый из диапазонов, а затем введите уникальное имя в поле Первое поле. Если у вас четыре диапазона, каждый из которых соответствует кварталу финансового года, выберите первый диапазон, введите имя «Кв1», выберите второй диапазон, введите имя «Кв2» и повторите процедуру для диапазонов «Кв3» и «Кв4».
- Если в разделе Во-первых, укажите количество полей страницы сводной таблицы задано число 2, выполните аналогичные действия в поле Первое поле. Затем выберите два диапазона и введите в поле Второе поле одинаковое имя, например «Пг1» и «Пг2». Выберите первый диапазон и введите имя «Пг1», выберите второй диапазон и введите имя «Пг1», выберите третий диапазон и введите имя «Пг2», выберите четвертый диапазон и введите имя «Пг2».