Консолидация таблиц за разные периоды в Excel: два классических метода
Если вам нужно объединить данные из нескольких таблиц Excel в один сводный отчет, в вашем распоряжении есть два проверенных временем инструмента: команда «Консолидация» и мастер сводных таблиц для нескольких диапазонов. Эти методы подходят для перекрёстных таблиц с одинаковым смыслом подписей строк и столбцов, даже если состав имён и месяцев различается. Они агрегируют числа по совпавшим подписям, а не объединяют произвольные структуры.
Действия ниже показаны для настольного Excel для Windows. Мастер нескольких диапазонов — устаревший, но ещё доступный инструмент; для регулярного объединения подробных данных рассмотрите Power Query. В примере три периода разной длины, а не три календарных квартала.
Ваша цель — получить матрицу сумм по всем именам и месяцам. Если одинаковые имя и месяц встречаются в разных источниках, значения складываются. Не включайте исходные итоговые строки и столбцы в диапазоны.
Метод 1: Команда «Консолидация»
Первый инструмент — это команда «Консолидация» на вкладке «Данные». Перед началом работы выберите верхнюю левую ячейку пустой области назначения и убедитесь, что справа и ниже достаточно места для результата.
- Запустите «Данные → Консолидация» и выберите функцию «Сумма».
- В диалоговом окне используйте кнопку «Ссылка», чтобы указать каждый из ваших диапазонов вместе с подписями верхней строки и левого столбца, и нажимайте «Добавить» после каждого.
- В левом нижнем углу установите флажки «Подписи верхней строки» и «Значения левого столбца».
После нажатия «ОК» будет создан сводный набор, включающий все имена и месяцы из трех исходных таблиц. Первый столбец содержит все уникальные имена, а первая строка — все месяцы.
Обратите внимание на три типичные проблемы: в показанном результате ячейка A1 остаётся пустой, данные в столбце A не отсортированы, а пустые ячейки (где человек отсутствовал в одном из периодов) отображаются как пустые вместо нуля.
Заполнить A1 легко. Для сортировки по имени можно использовать Мгновенное заполнение, чтобы выделить фамилию в отдельный столбец. Чтобы заполнить пустые ячейки нулями, выполните следующие действия:
- Выделите все ячейки, которые должны содержать числа (в показанном результате B2:M12). Заполняйте пропуски нулями только если отсутствие записи действительно означает нулевое значение.
- Выберите «Главная → Найти и выделить → Выделить группу ячеек → Пустые ячейки».
- Введите
0в активную пустую ячейку выделения. - Нажмите
Ctrl + Enter, чтобы заполнить только выделенные пустые ячейки.
Метод 2: Сводная таблица с несколькими диапазонами консолидации
Второй классический инструмент — это сводная таблица с несколькими диапазонами консолидации. Для его использования выполните следующие шаги:
- Откройте «Мастер сводных таблиц и диаграмм»: добавьте эту команду на панель быстрого доступа через «Все команды». В английском интерфейсе Windows также работает старое сочетание
Alt,D, затемP; оно зависит от языка. - В шаге 1 мастера выберите «в нескольких диапазонах консолидации» и нажмите «Далее».
- В шаге 2а выберите «Я создам поля страниц» и нажмите «Далее».
- В шаге 2b используйте кнопку «Ссылка», чтобы указать каждую таблицу. Добавьте каждый диапазон с подписями, не включая готовые итоги. Укажите 0 полей страниц и нажмите «Далее».
- На шаге 3 выберите новый лист или пустую область существующего листа и нажмите «Готово».
Заключение
Оба метода суммируют значения по совпавшим подписям. «Консолидация» без флажка «Создавать связи с исходными данными» создаёт статичный результат; с этим флажком результат связывается с источниками. Сводная таблица использует общие поля «Строка», «Столбец» и «Значение» и требует обновления после изменения источников. Пустоты отображайте нулями только при подходящем смысле данных; в сводной таблице это настраивается в параметрах отображения пустых ячеек.