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

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

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

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

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

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

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

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

Примечание : Примеры, приведенные в этой статье, относятся к Excel 2016, и интерфейс в вашей версии может немного отличаться. Тем не менее последовательность действий одинакова во всех версиях.

Действия по консолидации

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

    • Необходимо, чтобы все диапазоны данных были представлены в формате списка: первая строка каждого столбца содержит подпись, остальные строки — однотипные данные; пустые строки или столбцы в списке отсутствуют.

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

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

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

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

  3. На вкладке Данные в группе Работа с данными нажмите кнопку Консолидация.

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

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

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

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

  5. Выбор данных

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

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

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

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

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

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

    Примечания : 

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

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

  8. Нажмите кнопку "ОК", чтобы создать консолидацию. Она не будет отформатирована, но вы можете отформатировать ее сами. Это достаточно сделать один раз (если вы снова не запустите консолидацию).

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

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

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

  • Если данные для консолидации находятся в разных ячейках разных листов

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

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

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

  • Если данные для консолидации находятся в одинаковых ячейках разных листов

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

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

Вы знали?

Если у вас нет подписки на Office 365 либо последней версией Office, вы можете ознакомиться с пробной версией:

Попробуйте поработать с Office 365 или последней версией Excel

У вас есть вопрос об определенной функции?

Задать вопрос на форуме сообщества, посвященного Excel

Помогите нам улучшить Excel

У вас есть предложения по улучшению следующей версии Excel? Если да, ознакомьтесь с темами на портале пользовательских предложений для Excel.

См. также

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

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

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

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

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

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

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

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

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

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

×