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

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

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

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


В этой статье

Задача

Действие

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

Консолидация по расположению

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

Консолидация по категории

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

Консолидация по формуле

Использовать отчет сводной таблицы вместо консолидации

Использование отчета сводной таблицы для консолидации данных

Консолидация по расположению

  1. Организуйте консолидируемые данные на каждом отдельном листе.

    Настройка данных

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

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

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

    • Присвойте каждому диапазону имя: выделите диапазон ячеек и на вкладке Формулы в группе Определенные имена нажмите кнопку Имя диапазона и введите имя диапазона в поле Имя.

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

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

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

    Изображение ленты Excel

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

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

    Путь к файлу будет введен в поле Ссылка, он будет завершаться восклицательным знаком.

  6. Введите имя, назначенное диапазону, и нажмите кнопку Добавить. Повторите этот шаг для всех диапазонов.

  7. Выберите способ обновления консолидации. Выполните одно из следующих действий.

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

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

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

  8. Оставьте пустыми все поля в разделе Использовать в качестве имен. Названия исходных строк и столбцов не копируются в консолидированные данные. Если требуется скопировать названия в консолидированные данные, сделайте это вручную.

К началу страницы

Консолидация по категории

  1. Организуйте консолидируемые данные на каждом отдельном листе.

    Настройка данных

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

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

    • Убедитесь, что подписи столбцов или строк, которые требуется объединить, совпадают с учетом регистра букв. Например, заголовки «Ср. за год» и «Средний за год» различаются и не будут объединены в таблице консолидации.

    • Присвойте каждому диапазону имя: выделите диапазон ячеек и на вкладке Формулы в группе Определенные имена щелкните стрелку, расположенную рядом с полем Присвоить имя и введите имя диапазона в поле Имя.

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

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

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

    Изображение ленты Excel

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

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

    Путь к файлу будет введен в поле Ссылка, он будет завершаться восклицательным знаком.

  6. Введите имя, назначенное диапазону, и нажмите кнопку Добавить. Повторите этот шаг для всех диапазонов.

  7. Выберите способ обновления консолидации. Выполните одно из следующих действий.

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

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

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

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

    Примечания : 

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

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

К началу страницы

Консолидация по формуле

  1. На основном листе введите (вручную или путем копирования) названия строк и столбцов, содержащих консолидируемые данные.

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

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

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

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

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

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

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

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

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

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

К началу страницы

Использование отчета сводной таблицы для консолидации данных

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

К началу страницы

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

Отлично! Что-то еще?

Что мы могли бы улучшить?

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

×