Реклама
Этот сайт использует Яндекс.Метрику для анализа посещаемости. Подробнее
Таблицы и сводные таблицы

Консолидация таблиц за разные периоды в Excel: два классических метода

forest_paw 21.10.2025 379

Консолидация таблиц за разные периоды в Excel: два классических метода

Если вам нужно объединить данные из нескольких таблиц Excel в один сводный отчет, в вашем распоряжении есть два проверенных временем инструмента: команда «Консолидация» и мастер сводных таблиц для нескольких диапазонов. Эти методы подходят для перекрёстных таблиц с одинаковым смыслом подписей строк и столбцов, даже если состав имён и месяцев различается. Они агрегируют числа по совпавшим подписям, а не объединяют произвольные структуры.

Действия ниже показаны для настольного Excel для Windows. Мастер нескольких диапазонов — устаревший, но ещё доступный инструмент; для регулярного объединения подробных данных рассмотрите Power Query. В примере три периода разной длины, а не три календарных квартала.

Первый файл содержит имена в столбце A и данные за январь, февраль и март.
Первый файл содержит имена в столбце A и данные за январь, февраль и март.
Второй файл содержит имена в столбце A и данные за апрель, май, июнь, июль и август. Обратите внимание, что здесь пять месяцев вместо трех, как в первом файле.
Второй файл содержит имена в столбце A и данные за апрель, май, июнь, июль и август. Обратите внимание, что здесь пять месяцев вместо трех, как в первом файле.
Последний файл содержит имена в столбце A и данные за сентябрь, октябрь, ноябрь и декабрь.
Последний файл содержит имена в столбце A и данные за сентябрь, октябрь, ноябрь и декабрь.
Карикатура: три исходные таблицы объединяются в одну большую таблицу.
Карикатура, иллюстрирующая процесс объединения. Три мультяшных таблицы вверху бегут навстречу друг другу с криком "Давайте объединимся в одну таблицу!". Внизу большая таблица (с тремя лицами и шестью руками) содержит все данные из трех исходных листов.

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

Метод 1: Команда «Консолидация»

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

  1. Запустите «Данные → Консолидация» и выберите функцию «Сумма».
  2. В диалоговом окне используйте кнопку «Ссылка», чтобы указать каждый из ваших диапазонов вместе с подписями верхней строки и левого столбца, и нажимайте «Добавить» после каждого.
  3. В левом нижнем углу установите флажки «Подписи верхней строки» и «Значения левого столбца».
Диалоговое окно Консолидации. В раскрывающемся списке Функция выбрано Сумма. Поле Ссылка используется для указания следующего диапазона для консолидации. Список Все ссылки показывает области для объединения. В левом нижнем углу флажки для использования подписей в верхней строке и/или левом столбце.
Диалоговое окно Консолидации. В раскрывающемся списке Функция выбрано Сумма. Поле Ссылка используется для указания следующего диапазона для консолидации. Список Все ссылки показывает области для объединения. В левом нижнем углу флажки для использования подписей в верхней строке и/или левом столбце.

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

Результат консолидации. Имена из всех трех списков отображаются в A2:A12. Вверху все 12 месяцев с января по декабрь. В центре сетки — числа. Некоторые ячейки, которые должны быть числовыми, остаются пустыми вместо 0.
Результат консолидации. Имена из всех трех списков отображаются в A2:A12. Вверху все 12 месяцев с января по декабрь. В центре сетки — числа. Некоторые ячейки, которые должны быть числовыми, остаются пустыми вместо 0.

Обратите внимание на три типичные проблемы: в показанном результате ячейка A1 остаётся пустой, данные в столбце A не отсортированы, а пустые ячейки (где человек отсутствовал в одном из периодов) отображаются как пустые вместо нуля.

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

  1. Выделите все ячейки, которые должны содержать числа (в показанном результате B2:M12). Заполняйте пропуски нулями только если отсутствие записи действительно означает нулевое значение.
  2. Выберите «Главная → Найти и выделить → Выделить группу ячеек → Пустые ячейки».
  3. Введите 0 в активную пустую ячейку выделения.
  4. Нажмите Ctrl + Enter, чтобы заполнить только выделенные пустые ячейки.
Заполните пустые ячейки нулями и примените стиль таблицы.
Заполните пустые ячейки нулями и примените стиль таблицы.

Метод 2: Сводная таблица с несколькими диапазонами консолидации

Второй классический инструмент — это сводная таблица с несколькими диапазонами консолидации. Для его использования выполните следующие шаги:

  1. Откройте «Мастер сводных таблиц и диаграмм»: добавьте эту команду на панель быстрого доступа через «Все команды». В английском интерфейсе Windows также работает старое сочетание Alt, D, затем P; оно зависит от языка.
  2. В шаге 1 мастера выберите «в нескольких диапазонах консолидации» и нажмите «Далее».
Мастер сводных таблиц и диаграмм: выбор нескольких диапазонов консолидации.
Нажмите Alt+D, P, чтобы открыть старый мастер сводных таблиц и диаграмм. В шаге 1 из 3 выберите "в нескольких диапазонах консолидации".
  1. В шаге 2а выберите «Я создам поля страниц» и нажмите «Далее».
  2. В шаге 2b используйте кнопку «Ссылка», чтобы указать каждую таблицу. Добавьте каждый диапазон с подписями, не включая готовые итоги. Укажите 0 полей страниц и нажмите «Далее».
В шаге 2b из 3 мастера укажите все три диапазона. Укажите 0 полей страниц.
В шаге 2b из 3 мастера укажите все три диапазона. Укажите 0 полей страниц.
  1. На шаге 3 выберите новый лист или пустую область существующего листа и нажмите «Готово».
Готовая сводная таблица содержит все 12 месяцев вверху и все имена из любого списка слева. Одна проблема: если человек отсутствовал в таблице Q1, его продажи за январь, февраль и март отображаются как пустые, а не как ноль.
Готовая сводная таблица содержит все 12 месяцев вверху и все имена из любого списка слева. Одна проблема: если человек отсутствовал в таблице Q1, его продажи за январь, февраль и март отображаются как пустые, а не как ноль.

Заключение

Оба метода суммируют значения по совпавшим подписям. «Консолидация» без флажка «Создавать связи с исходными данными» создаёт статичный результат; с этим флажком результат связывается с источниками. Сводная таблица использует общие поля «Строка», «Столбец» и «Значение» и требует обновления после изменения источников. Пустоты отображайте нулями только при подходящем смысле данных; в сводной таблице это настраивается в параметрах отображения пустых ячеек.

Поделиться:

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