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

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

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

Совет: При частом объединении данных возможно создание новых листов на основе шаблона листа, который использует одинаковый макет. Дополнительные сведения о шаблонах см. в статье Создание шаблона. Также советуем добавить в шаблон таблицы Excel.

Существует два способа консолидации данных: по положению или категории.

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

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

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

Примечание: Примеры, приведенные в этой статье, были созданы в Excel 2016. Если вы используете другую версию Excel, это значит, что это не отличается от представления.

Чтобы объединить несколько листов в главный лист, выполните указанные ниже действия.

  1. Если вы еще не сделали этого, настройте данные на каждом листе, выполнив указанные ниже действия.

    • Убедитесь, что каждый диапазон данных представлен в виде списка. Каждый столбец должен иметь метку (верхний колонтитул) в первой строке и содержать похожие данные. В списке не должно быть пустых строк или столбцов.

    • Разместите каждый диапазон на отдельном листе, но не вводите ничего на главную лист, на котором вы планируете консолидировать данные. Excel сделает это автоматически.

    • Убедитесь, что все диапазоны имеют одинаковый макет.

  2. На основном листе щелкните левый верхний угол области, в которой требуется разместить консолидированные данные.

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

  3. Щелкните данные>Консолидация (в группе Работа с данными ).

    Группа "Работа с данными" на вкладке "Данные"

  4. Выберите в раскрывающемся списке Функцияитоговая функция, которую требуется использовать для консолидации данных. Функция по умолчанию — Sum.

    Ниже приведен пример выбора трех диапазонов на листе.

    Диалоговое окно "Консолидация данных"

  5. Выделите данные.

    Затем в поле ссылка нажмите кнопку Свернуть , чтобы свернуть панель, и выберите данные на листе.

    Кнопка "Свернуть" в диалоговом окне "Консолидация данных"

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

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

    Ниже приведен пример выбора трех диапазонов на листе.

    Диалоговое окно "Консолидация данных"

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

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

    Примечания: 

    • Связи невозможно создать, если исходная и конечная области находятся на одном листе.

    • Если вам нужно изменить диапазон или заменить диапазон, щелкните диапазон в меню консолидация и обновите его, выполнив описанные выше действия. При этом будет создана новая ссылка на диапазон, поэтому вам потребуется удалить предыдущую ссылку перед консолидацией. Просто выберите старую ссылку и нажмите клавишу DELETE.

  8. Нажмите кнопку ОК, и Excel создаст консолидацию. Кроме того, вы можете применить форматирование. Только один раз, если вы не перезапустите консолидацию, вам нужно только выполнить форматирование.

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

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

Если данные для консолидации находятся в разных ячейках на разных листах, выполните указанные ниже действия.

Введите формулу со ссылками на ячейки других листов, по одной на каждый лист. Например, чтобы консолидировать данные из листов "Продажи" (в ячейке B4), "Кадры" (в ячейке F5) и "Маркетинг" (в ячейке B9), в ячейке A2 основного листа, введите следующее:

Ссылка на несколько листов в формуле Excel
 

Совет: Для ввода ссылки на ячейку (например, Sales! B4 — в формуле без ввода введите формулу до той точки, где она должна быть, а затем щелкните ярлычок листа, а затем выберите ячейку. Excel заполнит имя листа и адрес ячейки автоматически. Примечание. в таких случаях могут воздержаться ошибки, так как в этом случае очень просто случайно выделить неверную ячейку. Кроме того, может быть трудно выявить ошибку после ввода сложной формулы.

Если данные для консолидации находятся в одинаковых ячейках на разных листах, выполните указанные ниже действия.

Введите формулу с трехмерной ссылкой, которая указывает на диапазон имен листов. Например, чтобы объединить данные в ячейках a2 из продаж через Объединенный по маркетингу, в ячейке @ @ главном листе нужно ввести следующее:

Объемная ссылка на листы в формуле Excel

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

Общие сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Поиск и исправление ошибок в формулах

Сочетания клавиш и функциональные клавиши в Excel

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Нужна дополнительная помощь?

Совершенствование навыков работы с Office
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×