Перейти к основному контенту
Поддержка
Войдите с помощью учетной записи Майкрософт
Войдите или создайте учетную запись.
Здравствуйте,
Выберите другую учетную запись.
У вас несколько учетных записей
Выберите учетную запись, с помощью которой нужно войти.

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

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

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

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

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

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

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

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

Выполните следующие действия, чтобы объединить несколько листов в master лист:

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

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

    • Поместите каждый диапазон на отдельный лист, но не вводите ничего на master листе, где планируется консолидация данных. Excel сделает это за вас.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Примечания: 

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

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

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

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

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

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

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

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

Совет: Ввод ссылки на ячейку, например Sales! B4 — в формуле без ввода введите формулу до точки, в которой требуется ссылка, затем перейдите на вкладку листа и щелкните ячейку. Excel заполтит имя листа и адрес ячейки. ПРИМЕЧАНИЕ. Формулы в таких случаях могут быть подвержены ошибкам, так как очень легко случайно выбрать неправильную ячейку. Также может быть трудно обнаружить ошибку после ввода сложной формулы.

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

Введите формулу с трехмерной ссылкой, которая указывает на диапазон имен листов. Например, чтобы объединить данные в ячейках A2 от Sales до Marketing включительно, в ячейке E5 master листа необходимо ввести следующее:

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

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

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

См. также

Полные сведения о формулах в Excel

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

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

Сочетания клавиш и горячие клавиши в Excel

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

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

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

Нужны дополнительные параметры?

Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.

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

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

Насколько вы удовлетворены качеством перевода?
Что повлияло на вашу оценку?
После нажатия кнопки "Отправить" ваш отзыв будет использован для улучшения продуктов и служб Майкрософт. Эти данные будут доступны для сбора ИТ-администратору. Заявление о конфиденциальности.

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

×