Чтобы суммировать и отчеты о результатах отдельных листов, можно объединить данные с каждого листа в главный лист. Листы могут находиться в той же книге, что и главный лист, или в других книгах. При консолидации данных вы собираете данные, чтобы было проще обновлять и агрегировать их по мере необходимости.
Например, если в каждом из региональных офисов есть свой лист расходов, с помощью консолидации можно свести эти данные на главном листе корпоративных расходов. Главный лист также может содержать итоговые и средние значения продаж, данные о складских запасах и информацию о самых популярных товарах в рамках всей компании.
Совет: Если вы часто консолидируете данные, это может помочь создать новые листы из шаблона листа, использующего согласованный макет. Дополнительные сведения о шаблонах см. в статье Создание шаблона. Также советуем добавить в шаблон таблицы Excel.
Существует два способа консолидации данных: по позиции или категории.
Консолидация по позициям. Данные в исходных областях имеют одинаковый порядок и используют одни и те же метки. Используйте этот способ, чтобы консолидировать данные из нескольких листов, основанных на одном шаблоне, например отчетов о бюджете.
Консолидация по категории: данные в исходных областях не расположены в одном и том же порядке, но имеют одинаковые метки. Используйте этот способ, чтобы консолидировать данные из нескольких листов с разными макетами, но одинаковыми метками данных.
-
Консолидация данных по категориям аналогична созданию сводной таблицы. Однако с помощью сводной таблицы можно легко реорганизовать категории. Если требуется более гибкая консолидация по категориям, рассмотрите возможность создания сводной таблицы .
Примечание: Примеры, приведенные в этой статье, были созданы с помощью Excel 2016. Хотя ваше представление может отличаться, если вы используете другую версию Excel, шаги будут одинаковыми.
Чтобы объединить несколько листов в главный лист, выполните следующие действия.
-
Если вы еще этого не сделали, настройте данные на каждом листе, выполнив следующие действия.
-
Убедитесь, что каждый диапазон данных имеет формат списка. Каждый столбец должен иметь метку (заголовок) в первой строке и содержать аналогичные данные. В списке не должно быть пустых строк или столбцов.
-
Поместите каждый диапазон на отдельный лист, но не вводите ничего на главном листе, где планируется объединить данные. Excel сделает это за вас.
-
Убедитесь, что каждый диапазон имеет одинаковый макет.
-
-
На основном листе щелкните левый верхний угол области, в которой требуется разместить консолидированные данные.
Примечание: Чтобы избежать перезаписи существующих данных на главном листе, оставьте достаточно ячеек справа и под этой ячейкой для объединенных данных.
-
Щелкните Data>Консолидация (в группе Data Tools).
-
Выберите в раскрывающемся списке Функцияитоговая функция, которую требуется использовать для консолидации данных. По умолчанию используется функция SUM.
Ниже приведен пример выбора трех диапазонов листа:
-
Выделите данные.
Затем в поле Ссылка нажмите кнопку Свернуть , чтобы сжать панель и выбрать данные на листе.
Щелкните лист с данными, которые вы хотите консолидировать, а затем нажмите кнопку раскрытия диалогового окна справа, чтобы вернуться в диалоговое окно Консолидация.
Если лист, содержащий данные, которые необходимо объединить, находится в другой книге, нажмите кнопку Обзор , чтобы найти эту книгу. После поиска и нажатия кнопки ОК Excel введет путь к файлу в поле Ссылка и добавит восклицательный знак в этот путь. Затем можно продолжить выбор других данных.Ниже приведен пример выбора трех диапазонов листа:
-
Во всплывающем окне Консолидация нажмите кнопку Добавить. Повторите это, чтобы добавить все объединяемые диапазоны.
-
Автоматическое обновление и обновление вручную Если вы хотите, чтобы Excel автоматически обновлял таблицу консолидации при изменении исходных данных, просто установите флажок Создать ссылки на исходные данные . Если этот флажок не установлен, можно обновить консолидацию вручную.
Примечания:
-
Связи невозможно создать, если исходная и конечная области находятся на одном листе.
-
Если необходимо изменить экстент диапазона или заменить диапазон, щелкните диапазон во всплывающем окне Консолидация и обновите его, выполнив описанные выше действия. При этом будет создана новая ссылка на диапазон, поэтому вам потребуется удалить предыдущую ссылку перед консолидацией. Просто выберите старую ссылку и нажмите клавишу DELETE.
-
-
Нажмите кнопку ОК, и Excel создаст консолидацию. При необходимости можно применить форматирование. Необходимо отформатировать только один раз, если вы не выполните консолидацию повторно.
-
Все названия, не совпадающие с названиями в других исходных областях, приведут к появлению дополнительных строк или столбцов в консолидированных данных.
-
Убедитесь, что все категории, которые не требуется консолидировать, имеют уникальные метки, которые отображаются только в одном исходном диапазоне.
-
Если данные для консолидации есть в разных ячейках на разных листах:
Введите формулу со ссылками на ячейки других листов, по одной на каждый лист. Например, чтобы консолидировать данные из листов "Продажи" (в ячейке B4), "Кадры" (в ячейке F5) и "Маркетинг" (в ячейке B9), в ячейке A2 основного листа, введите следующее:
Совет: Ввод ссылки на ячейку, например Sales! B4 — в формуле без ввода введите формулу до точки, в которой требуется ссылка, затем перейдите на вкладку листа и щелкните ячейку. Excel заполтит имя листа и адрес ячейки. ПРИМЕЧАНИЕ. Формулы в таких случаях могут быть подвержены ошибкам, так как очень легко случайно выбрать неправильную ячейку. Также может быть трудно обнаружить ошибку после ввода сложной формулы.
Если данные для консолидации находится в одних и том же ячейках на разных листах:
Введите формулу с трехмерной ссылкой, которая указывает на диапазон имен листов. Например, чтобы объединить данные в ячейках A2 от Sales до Marketing включительно, в ячейке E5 главного листа введите следующее:
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
См. также
Полные сведения о формулах в Excel
Рекомендации, позволяющие избежать появления неработающих формул