Чтобы сделать консолидацию в Excel, встаньте в пустую ячейку на итоговом листе и откройте «Данные» → «Консолидация». В окне выберите функцию (по умолчанию стоит сумма), по очереди выделите таблицы на других листах и после каждой нажмите «Добавить». Если строки в таблицах идут в разном порядке, включите флажки подписей верхней строки и левого столбца. Нажмите «ОК», и Excel сведёт данные в одну таблицу.
Ниже разберём консолидацию на примере трёх филиалов, сборку из разных файлов с автообновлением, трёхмерную ссылку в формуле и случаи, когда листы нужно просто сложить друг под друга без суммирования. Если основные функции пока путаются, откройте рядом таблицу основных функций Excel с примерами.
CheckroiПодборка курсов по Microsoft Excel685 курсов • 64 школыСравните цены, школы, программу и найдите выгодные предложения по обучениюСравнить→
Что такое консолидация данных в Excel
Консолидация собирает данные из нескольких таблиц в одну и сразу считает по ним итог: сумму, среднее, максимум или количество. Исходные таблицы могут лежать на разных листах одной книги или в разных файлах.
Типичная задача: три филиала ведут продажи каждый на своём листе, а руководителю нужна одна таблица с общими цифрами. Копировать числа руками долго, и при каждой правке филиала итог приходится собирать заново.
В справке Microsoft описаны два способа консолидации, и от выбора зависит результат:
| Способ | Когда подходит | Что включить в окне |
|---|---|---|
| По расположению | Все листы сделаны по одному шаблону: одинаковые строки и столбцы в одинаковом порядке | Флажки подписей не нужны |
| По категориям | Подписи строк и столбцов совпадают, но порядок разный, а где-то есть лишние позиции | Флажки подписей верхней строки и левого столбца |
Если сомневаетесь, выбирайте консолидацию по категориям. Она даёт верный итог и тогда, когда порядок строк совпадает, а консолидация по расположению при перепутанных строках без предупреждения сложит чай с кофе.
Как сделать консолидацию в Excel: пошаговая инструкция
Возьмём три листа с продажами за январь и февраль. На каждом в первой строке заголовки, в первом столбце товары. Порядок товаров разный, а в Казани есть позиция, которой нет в других филиалах.
| Лист | Товар | Январь | Февраль |
|---|---|---|---|
| Москва | Кофе | 120 | 135 |
| Москва | Чай | 80 | 90 |
| Москва | Сахар | 40 | 38 |
| Казань | Чай | 60 | 55 |
| Казань | Кофе | 95 | 100 |
| Казань | Печенье | 30 | 34 |
| Самара | Кофе | 70 | 75 |
| Самара | Сахар | 25 | 27 |
| Самара | Чай | 45 | 50 |
Перед началом проверьте исходные таблицы. В каждой нужна строка заголовков, а пустые строки и столбцы внутри данных лучше убрать. Если вы собираете таблицу с нуля, посмотрите, как сделать таблицу в Excel так, чтобы потом с ней удобно работать.
- Создайте лист «Итог» и щёлкните ячейку A1. Справа и снизу от неё оставьте пустое место: Excel запишет результат поверх всего, что там лежит.
- На вкладке «Данные» в группе «Работа с данными» нажмите «Консолидация».
- В списке «Функция» оставьте сумму. Для средней цены или максимальной выручки выберите другую функцию из того же списка.
- Щёлкните в поле «Ссылка», перейдите на лист «Москва» и выделите таблицу вместе с заголовками, A1:C4. Нажмите «Добавить».
- Повторите для листов «Казань» и «Самара». Все три диапазона появятся в списке под полем.
- Отметьте флажки подписей верхней строки и значений левого столбца. Без них Excel сложит ячейки по позициям и не заметит, что строки в Казани идут в другом порядке.
- Нажмите «ОК».
На листе «Итог» получится такая таблица:
| Товар | Январь | Февраль |
|---|---|---|
| Кофе | 285 | 310 |
| Чай | 185 | 195 |
| Сахар | 65 | 65 |
| Печенье | 30 | 34 |
Печенье попало в итог, хотя продаётся только в Казани: подпись, которой нет на других листах, Excel добавляет отдельной строкой. Об этом прямо сказано в справке Microsoft об объединении данных с нескольких листов.
Совет: заголовок над первым столбцом Excel в итог не переносит, ячейка A1 останется пустой. Впишите туда «Товар» сами и оформите таблицу. Форматирование достаточно сделать один раз, если потом не запускать консолидацию заново.
Как посчитать среднее или добавить новый филиал
Окно консолидации запоминает последние настройки. Если щёлкнуть ту же ячейку итога и снова открыть «Консолидация», в списке останутся все три диапазона.
- Другая функция. Выберите в списке «Функция» среднее или максимум и нажмите «ОК». Excel пересчитает итог по тем же диапазонам. Среднее с условиями и средневзвешенное мы разобрали в статье о том, как найти среднее значение в Excel.
- Новый лист. Выделите таблицу нового филиала, нажмите «Добавить» и «ОК». Позиции, которых не было раньше, появятся новыми строками.
- Лишний лист. Выделите его адрес в списке диапазонов и нажмите клавишу Delete.
Перед повторным запуском очистите старый итог. Иначе новые строки лягут поверх прежних, и в таблице останутся хвосты от прошлого расчёта.
Канал основателя Checkroi Вани Буявца3 700 человек читают мой Телеграм-канал про нейросетиСобрал промпты для Claude Code и ChatGPT, разборы ИИ-инструментов и лайфхаки по продвижению бизнеса в одном местеПрисоединитьсяКонсолидация из разных файлов и автообновление
Исходные таблицы могут лежать и в разных книгах. Отчёты филиалов часто приходят отдельными файлами, и консолидация работает с ними так же, как с листами.
- Откройте все исходные файлы, так диапазоны удобнее выделять мышью.
- В окне «Консолидация» нажмите «Обзор», если нужный файл закрыт. Excel подставит в поле «Ссылка» путь к книге с восклицательным знаком, а диапазон останется дописать.
- Для открытой книги просто переключитесь на неё через панель задач и выделите таблицу. Горячие клавиши, которые ускоряют такую работу, собраны в нашей подборке приёмов для быстрой работы в Excel.
- Добавьте все диапазоны и нажмите «ОК».
Чтобы итог пересчитывался сам, перед нажатием «ОК» отметьте флажок «Создавать связи с исходными данными». Без него консолидация сработает один раз: поменяете цифры в филиале, а итог останется прежним, пока не запустите команду снова.
У связей есть ограничение из прежней версии справки Microsoft: их нельзя создать, если исходная и итоговая таблицы стоят на одном листе. Итог всегда выносите на отдельный лист.
Со связями Excel сворачивает итог в группы: под каждым товаром спрятаны строки по отдельным источникам. Раскройте группу, и увидите, сколько принёс каждый филиал.
Так же устроены группы у промежуточных итогов в Excel: строки с деталями сворачиваются, а итоговые остаются на виду.
Если исходных файлов много и каждый месяц добавляются новые, консолидацию удобнее заменить на Power Query. О нём ниже, в разделе про объединение без суммирования.
Консолидация формулой: трёхмерная ссылка
Когда все листы сделаны строго по одному шаблону, итог можно собрать обычной формулой. Трёхмерная ссылка указывает на одну и ту же ячейку сразу на нескольких листах, идущих подряд.
=СУММ(Москва:Самара!B2)Формула сложит ячейку B2 на всех листах от «Москвы» до «Самары» включительно. Ввести её можно мышью, как описано в справке Microsoft о трёхмерных ссылках:
- Щёлкните ячейку для итога и наберите
=СУММ(. - Щёлкните ярлычок первого листа.
- Зажмите Shift и щёлкните ярлычок последнего листа.
- Выделите ячейку и нажмите Enter.
Вставите новый лист между «Москвой» и «Самарой», и он сразу попадёт в расчёт. Перенесёте лист за пределы этой пары, и его значения из суммы выпадут.
Минус способа виден на нашем примере. В Казани во второй строке стоит чай, а в Москве и Самаре кофе. Формула сложит 120 + 60 + 70 и покажет 250, хотя кофе продали на 285. Трёхмерная ссылка не читает подписи, поэтому годится только для одинаковых шаблонов. Подробнее о самой функции суммы и её ловушках мы писали в статье о том, как посчитать сумму в столбце Excel.
Если ячейки на листах разные, формулу собирают по частям: =Москва!B2+Казань!B3+Самара!B2. Это работает, но ошибиться с адресом легко, а заметить ошибку потом трудно. Для таблиц с разным порядком строк надёжнее консолидация по категориям или функция СУММЕСЛИ по каждому листу.
Как объединить листы в один без суммирования
Иногда считать итог не нужно, а нужно сложить строки всех листов в один длинный список: заявки из трёх отделов, клиенты из нескольких выгрузок. Консолидация для этого не годится, она всегда что-то суммирует или усредняет.
Формула ВСТОЛБИК
Функция ВСТОЛБИК ставит диапазоны друг под друга и возвращает один массив. Для наших филиалов без строки заголовков:
=ВСТОЛБИК(Москва!A2:C4;Казань!A2:C4;Самара!A2:C4)Получится список из девяти строк, и он обновится, как только поменяются исходные листы. Заголовки впишите над формулой один раз руками.
По справке Microsoft о функции ВСТОЛБИК, она есть в Excel для Microsoft 365 и Excel 2024. В Excel 2021 и более ранних версиях её нет. Если у одного диапазона меньше столбцов, чем у остальных, в пустых местах появится ошибка #Н/Д, её убирают обёрткой ЕСЛИОШИБКА.
Дубли после склейки списков почистит команда из нашей инструкции о том, как удалить повторяющиеся значения в Excel.
Power Query: все файлы из одной папки
Если отчёты приходят файлами каждый месяц, сложите их в одну папку без посторонних файлов. Дальше всё делает Power Query, порядок шагов есть в справке Microsoft об импорте из папки:
- Откройте «Данные» → «Получить данные» → «Из файла» → «Из папки».
- Укажите папку с отчётами и проверьте список файлов в окне.
- Нажмите «Объединить» внизу окна и выберите вариант с загрузкой на лист.
- В окне «Объединение файлов» выберите лист или таблицу, которую нужно собрать из каждого файла, и нажмите «ОК».
Power Query соберёт строки всех файлов в одну таблицу на новом листе. Положили в папку новый отчёт, нажмите Ctrl+Alt+F5 или «Обновить все» на вкладке «Данные», и он попадёт в общую таблицу. Комбинация описана в справке об обновлении данных.
Важно, чтобы у всех файлов были одинаковые заголовки столбцов. Порядок столбцов может отличаться, Power Query сопоставит их по именам.
Общую таблицу потом удобно разложить по товарам и месяцам. Для этого подойдёт сводная таблица в Excel. Она считает итоги по категориям так же, как консолидация, но категории в ней можно переставлять мышью.
Какой способ выбрать
| Задача | Способ | Обновляется сам |
|---|---|---|
| Итог по нескольким листам с одинаковыми подписями | Консолидация по категориям | Да, с флажком связей |
| Листы по одному шаблону, нужна одна ячейка итога | Трёхмерная ссылка в СУММ | Да |
| Сложить строки всех листов в один список | ВСТОЛБИК (Microsoft 365, Excel 2024) | Да |
| Собрать десятки файлов, которые прибавляются каждый месяц | Power Query из папки | По кнопке обновления |
| Подтянуть к одной таблице столбец из другой по коду или фамилии | ВПР или ПРОСМОТРX | Да |
Последняя строка часто путается с консолидацией. Если данные второй таблицы надо «приклеить» к первой по общему коду или фамилии, нужна функция ВПР. А найти расхождения между двумя версиями отчёта поможет наша статья о том, как сравнить две таблицы в Excel.
Канал основателя Checkroi Вани БуявцаЗабирайте промпты и обучение по нейросетям в моём Телеграм-каналеБольше 3 700 человек уже применяют Claude Code, ChatGPT и другие нейросети в работе, учёбе, бизнесе и жизниПерейти в каналЧастые ошибки консолидации
| Что видите | Почему | Что сделать |
|---|---|---|
| Одна позиция в итоге дважды, например «Кофе» и «Кофе » с пробелом | На одном из листов в подписи лишний пробел в конце | Приведите подписи к одному виду на всех листах и запустите консолидацию заново |
| Цифры сложились не с теми строками | Консолидация шла по расположению, а порядок строк на листах разный | Включите флажки подписей верхней строки и левого столбца |
| В итоге лишние пустые строки или обрывки таблицы | Внутри исходного диапазона есть пустые строки или столбцы | Уберите пустоты и выделите диапазон заново |
| Итог не меняется после правки филиала | Не стоял флажок связей с исходными данными | Удалите итог и проведите консолидацию снова с флажком |
| В списке диапазонов остался старый адрес | При правке диапазона Excel создаёт новую ссылку, старая остаётся | Выделите старую ссылку в списке и нажмите клавишу Delete |
Если команды «Консолидация» на ленте нет, вы, скорее всего, работаете в браузере. В справке об объединении данных Microsoft объясняет это так: Excel для Интернета эту функцию может не поддерживать, и тогда данные собирают формулами или Power Query. Какие ещё возможности урезаны в браузерной версии, мы разобрали в статье про Excel онлайн.
Если у вас не Excel
В марте 2022 года Microsoft остановила новые продажи в России (страница блога компании из России может открываться не всегда), поэтому во многих компаниях таблицы ведут в других программах.
- LibreOffice Calc. Та же команда называется иначе: «Данные» → «Объединить». В окне те же поля: функция, список диапазонов, кнопка «Добавить» и ячейка для результата. Так описано в справке LibreOffice.
- Google Таблицы. Отдельной команды консолидации нет. Списки с разных листов склеивает функция VSTACK, синтаксис в справке Google о VSTACK, а итоги по категориям потом считает сводная таблица. Как переносить файлы между программами, рассказали в статье о Google Таблицах и Excel.
- Р7-Офис. В руководстве пользователя редактора таблиц Р7 команды консолидации нет, зато есть вкладка «Сводная таблица». Данные с листов сначала собирают в один список, а итог считают сводной.
Где освоить Excel системно
Консолидация закрывает одну задачу: собрать отчёты в общий итог. Дальше обычно нужны Power Query, сводные таблицы, формулы поиска и диаграммы, и собирать их по одной инструкции долго.
Если сведение таблиц занимает у вас полдня каждый месяц, посмотрите, чем занимается специалист по Excel и какие навыки от него ждут работодатели. Пройти программу целиком можно на курсах:
| Курс | Школа | Стоимость со скидкой | В рассрочку | Длительность | Обзор курса от Checkroi |
|---|---|---|---|---|---|
| Excel Перейти на сайт курса |  Нетология | 31 900 ₽ | 1691 ₽/мес. | 8 недель | Обзор курса |
| Power BI & Excel PRO Перейти на сайт курса | Нетология | 38 500 ₽ | 3208 ₽/мес. | 2 месяца | Обзор курса |
| Excel для работы Перейти на сайт курса |  Эдюсон | 16 500 ₽ | 1375 ₽/мес. | 2 недели | Обзор курса |
| Excel + Google Таблицы с нуля до PRO Перейти на сайт курса |  Skillbox | 29 899 ₽ | 2965 ₽/мес. | 4 месяца | Обзор курса |
| Excel для аналитика Перейти на сайт курса |  Moscow Business Academy | 17 200 ₽ | 2866 ₽/мес. | 2 месяца | Обзор курса |
Больше программ — в полном каталоге курсов по Microsoft Excel
Все программы с ценами и отзывами собраны в разделе курсов по Excel и Google Таблицам.
