Сводные таблицы в Excel: как сделать по шагам, обновить, сгруппировать даты и собрать из нескольких листов

Сводная таблица в Excel собирает строки исходных данных в группы и сама считает по ним итоги: сумму, количество, среднее или долю от общего. Формулы писать не нужно. Из выгрузки продаж на тысячу строк она за минуту делает отчёт «продажи каждого продавца по месяцам», а если перетащить поля иначе, получится «регион по товарам».

Шаги одинаковы в Excel 2016, 2019, 2021, 2024 и Microsoft 365. Названия кнопок даны по Excel из Microsoft 365 для Windows; где в старых версиях или на Mac написано иначе, это отмечено. Пример во всех шагах один: лист с продажами и столбцами «Дата», «Продавец», «Товар», «Регион», «Количество», «Сумма».

Что проверить в данных до создания сводной

Сводная читает данные как список. Поэтому у каждого столбца должен быть свой заголовок в одной строке, а сама таблица идёт без пустых строк, пустых столбцов и объединённых ячеек. В одном столбце один тип данных: в «Дате» только даты, в «Сумме» только числа.

Строку «Итого» внизу источника уберите. Для сводной это ещё одна продажа, и общий итог удвоится.

Потом сделайте из диапазона таблицу Excel. Щёлкните любую ячейку с данными, нажмите Ctrl+T и подтвердите, что первая строка содержит заголовки. Тогда строки, которые вы допишете в таблицу завтра, попадут в сводную при обычном обновлении. Если источник остался диапазоном вида A1:F1201, новые строки ниже 1201-й сводная не увидит, пока вы вручную не поменяете источник.

Как сделать сводную таблицу: шаги на примере

1. Щёлкните любую ячейку таблицы с продажами.
2. На вкладке «Вставка» нажмите «Сводная таблица». Если в Microsoft 365 вместо окна открылось меню (оно появляется при щелчке по стрелке под кнопкой), выберите «Из таблицы/диапазона».
3. В окне «Сводная таблица из таблицы или диапазона» проверьте поле «Таблица или диапазон»: там должно стоять имя таблицы, например «Таблица1». Ниже оставьте «Новый лист» и нажмите «ОК». В Excel 2016 и 2019 окно называется «Создание сводной таблицы», шаги те же.
4. На новом листе слева появится пустая заготовка, справа панель «Поля сводной таблицы». Вверху панели список столбцов, внизу четыре области: «Фильтры», «Столбцы», «Строки» и «Значения».
5. Перетащите поле «Продавец» в «Строки», а «Сумма» в «Значения». Получится столбец продавцов, напротив каждого его выручка, внизу общий итог.
6. Перетащите «Дата» в «Столбцы». Excel 2016 и новее сам разложит даты на годы, кварталы и месяцы (подробнее в разделе о группировке).
7. Перетащите «Регион» в «Фильтры». Над таблицей появится выпадающий список: можно оставить в отчёте один регион или несколько.

Окно создания сводной

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

Панель полей сводной таблицы

Панель с полями видна, только пока выделена ячейка сводной. Если и с выделенной ячейкой панели нет, включите её кнопкой «Список полей» на вкладке «Анализ сводной таблицы».

Есть и вариант без ручной раскладки: «Вставка» → «Рекомендуемые сводные таблицы». Excel предложит несколько готовых макетов по вашим данным. Microsoft относит рекомендации к подключённым возможностям Microsoft 365: данные анализирует облачная служба ИИ, и если подключённые возможности отключены, рекомендуемые сводные недоступны.

Сумма, количество, среднее и проценты

Для числового столбца Excel ставит «Сумму», для текстового «Количество». Бывает, что по столбцу «Сумма» сводная пишет «Количество по полю Сумма». Microsoft объясняет это так: в столбце есть пустые ячейки или значения, которые Excel не считает числами.

Переключить операцию на сумму можно, но Microsoft предупреждает, что пустые и нечисловые значения тогда идут в расчёт как нули. Если среди них были цифры, сохранённые текстом (так бывает после выгрузки из учётной программы или открытия CSV), итог получится меньше настоящего, и сводная об этом не скажет. Поэтому сначала исправьте источник: у таких ячеек в углу зелёный треугольник, пункт «Преобразовать в число» в меню значка переводит их в числа. Для целого столбца удобнее «Данные» → «Текст по столбцам» и сразу «Готово». Затем обновите сводную.

Операцию меняют правым щелчком по любому числу сводной: «Итоги по» → «Среднее», «Максимум», «Минимум» и другие. Тот же выбор есть в окне «Параметры поля значений», вкладка «Операция»: оно открывается из стрелки у поля в области «Значения».

Параметры поля значений

Доля от общего итога включается там же правым щелчком: «Дополнительные вычисления» → «% от общей суммы». Можно перетащить «Сумму» в «Значения» дважды. Первую оставьте суммой, вторую переключите на проценты, и в отчёте рядом будут выручка продавца и его доля в продажах.

Формат чисел задавайте через «Параметры поля значений» → кнопка «Числовой формат». Так формат привязан к полю и не слетает, когда сводная перестраивается.

Количество уникальных значений (сколько разных клиентов купило товар) обычная сводная не считает. Для этого при создании поставьте флажок «Добавить эти данные в модель данных», и в списке операций появится «Число разных элементов». Модель данных есть только в Excel для Windows, на Mac этого пункта нет.

Группировка дат по месяцам, кварталам и годам

С Excel 2016 даты в строках или столбцах группируются сами: вместо сотни дней появляются «Годы», «Кварталы» и «Месяцы».

Если группировка не нужна, нажмите Ctrl+Z сразу после того, как перетащили поле.

Чтобы выбрать уровни самим, щёлкните правой кнопкой по любой дате в сводной и выберите «Группировать». В окне «Группировка» отметьте нужные шаги. Если выбрать только «Месяцы», январь 2025 и январь 2026 сольются в одну строку «янв». Для отчёта за несколько лет отмечайте вместе «Месяцы» и «Годы».

Числа группируются так же: например, суммы сделок по диапазонам с шагом 10 000. В окне задают начальное и конечное значение и шаг. Текстовые элементы объединяют вручную: выделите нескольких продавцов с Ctrl, правый щелчок → «Группировать», и Excel создаст «Группа1», которую можно переименовать прямо в ячейке.

Автоматическую группировку дат отключают в «Файл» → «Параметры» → «Данные»: флажок «Отключить автоматическое группирование столбцов даты и времени в сводных таблицах». На Mac он находится в «Excel» → «Настройки» → «Таблицы и фильтры». Флажок действует на новые сводные, уже собранную группировку снимают командой «Разгруппировать».

Если Excel отвечает «Выделенные объекты нельзя объединить в группу», в столбце дат источника есть даты, записанные текстом, или ячейки с ошибками; иногда мешают и пустые строки. Найти их проще автофильтром по столбцу: настоящие даты Excel показывает деревом по годам и месяцам, а текст стоит отдельными строками в конце списка. Исправьте их тем же «Текстом по столбцам», обновите сводную и группируйте снова.

Если на одном источнике построены две сводные, группировка в одной меняет и другую: они хранят общий снимок данных.

Как обновить сводную и почему не появились новые строки

Сводная хранит свой снимок данных. Обычные формулы Excel пересчитывает при каждом изменении ячейки, а сводной для этого нужна отдельная команда, иначе она так и будет показывать данные на момент создания или последнего обновления. После правок щёлкните по ней правой кнопкой и выберите «Обновить» или нажмите Alt+F5. Все сводные и подключения книги разом обновляет Ctrl+Alt+F5 или «Анализ сводной таблицы» → «Обновить» → «Обновить все». В Excel 2016 и 2019 эта вкладка ленты называется «Анализ» и стоит в группе «Работа со сводными таблицами». Автоматическое обновление сводных при изменении источника Microsoft на 9 октября 2026 года показывает только участникам программы Microsoft 365 Insider, в обычных версиях сводную обновляют вручную.

Обновили, а новых строк нет? Значит, источник задан диапазоном с жёстким концом. Сделайте из данных таблицу (Ctrl+T), затем «Анализ сводной таблицы» → «Изменить источник данных» и вместо адреса впишите имя таблицы, например «Таблица1». С этого момента всё, что вы допишете в таблицу, будет попадать в отчёт.

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

Чтобы не забывать про обновление, откройте «Анализ сводной таблицы» → «Параметры», вкладка «Данные», и включите «Обновлять данные при открытии файла». Если после каждого обновления у отчёта прыгает ширина столбцов, на вкладке «Макет и формат» того же окна снимите «Автоматически изменять ширину столбцов при обновлении» и оставьте «Сохранять форматирование ячеек при обновлении».

Сортировка, срезы и временная шкала

Чтобы наверху стоял продавец с самой большой выручкой, щёлкните правой кнопкой по любой сумме и выберите «Сортировка» → «Сортировка от максимального к минимальному». Оставить в отчёте только пятёрку лучших можно фильтром: стрелка у «Названия строк» → «Фильтры по значению» → «Первые 10», и в окне фильтра поменяйте 10 на 5.

Фильтр в области «Фильтры» работает, но прячет выбор в выпадающем списке.

Срез показывает тот же выбор кнопками: «Анализ сводной таблицы» → «Вставить срез», отметьте «Регион» и «Продавец». Щелчок по кнопке оставляет в отчёте один регион, несколько выбирают с Ctrl. Один срез может управлять несколькими сводными, если они построены на одном источнике: правый щелчок по срезу → «Подключения к отчетам».

Для дат есть временная шкала: «Анализ сводной таблицы» → «Вставить временную шкалу», поле «Дата». Это полоса, на которой мышью выделяют месяцы или кварталы, а уровень переключают между годами, кварталами, месяцами и днями. Срезы появились в Excel 2010, временная шкала в Excel 2013. На Mac шкала есть в Excel 2021, 2024 и Microsoft 365.

Вычисляемое поле: формула внутри сводной

Допустим, нужна средняя цена продажи по каждому товару. Добавлять столбец в источник не обязательно: «Анализ сводной таблицы» → «Вычисления» → «Поля, элементы и наборы» → «Вычисляемое поле». В поле «Имя» напишите «Цена», в «Формула» соберите =Сумма/Количество (поля вставляются двойным щелчком из списка), нажмите «Добавить» и «ОК».

Вычисляемое поле работает с итогами.

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

Если же нужен расчёт по каждой строке (скидка только на часть продаж, условие ЕСЛИ по каждой сделке), вычисляемое поле не подойдёт. Добавьте столбец с формулой в исходную таблицу и обновите сводную: новый столбец появится в списке полей.

Сводная из нескольких листов или таблиц

Здесь два разных случая, и решения у них разные.

Первый: одинаковые таблицы на разных листах, например продажи за каждый месяц на своём листе. Сводная лучше всего работает с одним списком, поэтому листы стоит объединить. Если это нужно один раз, скопируйте их друг под друга в одну таблицу и добавьте столбец «Месяц». Если листы будут пополняться, соберите их через Power Query командой добавления запросов: она склеивает таблицы по одинаковым заголовкам, а при обновлении подтягивает новые строки. Лист за новый месяц нужно один раз добавить в запрос. В Microsoft 365 и Excel 2024 те же строки можно склеить формулой ВСТОЛБИК. Старый мастер сводных таблиц с режимом «Несколько диапазонов консолидации» (его кнопку приходится выносить на панель быстрого доступа) Microsoft считает устаревшим: он сводит листы, но вместо ваших заголовков даёт поля «Строка», «Столбец» и «Значение».

Второй случай: разные таблицы, связанные общим столбцом. Например, в продажах есть только фамилия продавца, а отдел записан в отдельном справочнике сотрудников. Обычно отдел подтягивают в продажи функцией ВПР, но сводная умеет связать таблицы сама:

1. Оформите обе таблицы через Ctrl+T.
2. Создайте сводную по продажам с флажком «Добавить эти данные в модель данных».
3. «Данные» → «Отношения» → «Создать»: таблица продаж и столбец «Продавец», связанная таблица справочника и её столбец «Продавец». В справочнике каждая фамилия должна встречаться один раз.
4. В панели полей переключитесь на вкладку «Все» и перетаскивайте поля из обеих таблиц.

Модель данных работает только в Excel для Windows. На Mac остаётся путь через ВПР или ПРОСМОТРX: подтянуть отдел столбцом в таблицу продаж и строить сводную по ней.

Как превратить сводную в обычную таблицу и отдать дальше

По умолчанию сводная показывает всё в сжатой форме: продавцы и товары в одном столбце лесенкой. Для пересылки и дальнейших формул удобнее плоский вид. На вкладке «Конструктор» откройте «Макет отчета» → «Показать в табличной форме», там же выберите «Повторять все подписи элементов». В «Промежуточные итоги» поставьте «Не показывать промежуточные итоги».

Если получателю не нужна живая сводная, выделите её целиком, скопируйте и на новом листе вставьте как значения («Вставить» → «Значения»). Получится обычная таблица без связи с источником, её можно сортировать и править как угодно.

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

Когда вы ставите в обычной ячейке «=» и щёлкаете по числу сводной, Excel вместо ссылки вида B5 вставляет функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Она достаёт значение по названиям полей и продолжает работать, даже если сводная перестроилась и нужное число переехало в другую ячейку. Протягивать её вниз бесполезно: продавец и месяц записаны в ней текстом, и все копии вернут одно и то же число. Если нужны обычные ссылки, откройте «Анализ сводной таблицы» → «Сводная таблица» → стрелка у «Параметры» и снимите «Создать GetPivotData».

Ошибки при создании и обновлении

«Недопустимое имя поля сводной таблицы». У какого-то столбца в выбранном диапазоне нет заголовка. Обычно в диапазон попал лишний пустой столбец справа или заголовок стоит в объединённой ячейке. Подпишите столбец или сузьте диапазон.

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

«Недопустимая ссылка на источник данных». Источник удалён, переименован или лежит на недоступном сетевом диске. Мешает и имя файла: если книгу открыли прямо из браузера, не сохранив, Excel может работать с её временной копией с именем вида «Отчет[1].xlsx», а квадратные скобки в имени файла ломают ссылку. Сохраните файл под нормальным именем через «Сохранить как» и создайте сводную заново.

Строка «(пусто)» в отчёте означает, что в источнике в этом поле есть пустые ячейки. Заполните их или уберите «(пусто)» флажком в фильтре поля. Пустые клетки в самих итогах заменяют нулём или прочерком: «Параметры» → «Макет и формат» → «Для пустых ячеек отображать».

Удалить сводную целиком можно так: «Анализ сводной таблицы» → «Выделить» → «Всю сводную таблицу», затем Delete. Если сводная одна на листе, проще удалить сам лист. Попытка стереть только часть отчёта закончится сообщением, что эту часть сводной таблицы изменить нельзя. Поправить цифру прямо в сводной тоже не выйдет: правьте источник и обновляйте.

СВОДПО и ГРУПППО: сводная формулой в Microsoft 365

В Microsoft 365 появились функции, которые делают то же самое формулой. ГРУПППО группирует по строкам, СВОДПО по строкам и столбцам. Например, =ГРУПППО(B2:B1201;F2:F1201;СУММ) выведет продавцов из столбца B с суммой продаж из столбца F. Результат пересчитывается сам, как любая формула, кнопка «Обновить» не нужна.

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

Microsoft отмечает обе функции как функции Microsoft 365. В Excel 2016 и 2019 их точно нет, а для 2021 и 2024 справка Microsoft противоречит сама себе, поэтому рассчитывать на них в бессрочных версиях не стоит. Сами сводные таблицы есть во всех версиях, разница в мелочах: модель данных и «Число разных элементов» только в Windows, временная шкала на Mac с версии 2021.

Сравнить версии Excel

5.09 отзывов

Microsoft Excel 2024 — бессрочный ключ с привязкой

6 070 ₽5 970 ₽

В корзину

4.8833 отзыва

Microsoft Office 2024 Home — бессрочный ключ с привязкой

12 970 ₽11 970 ₽

В корзину

4.95479 отзывов

Microsoft 365 Персональный — подписка на 1 год для России

6 970 ₽

В корзину

5.09 отзывов

Microsoft Excel 2024 — бессрочный ключ с привязкой

6 070 ₽5 970 ₽

В корзину

4.8833 отзыва

Microsoft Office 2024 Home — бессрочный ключ с привязкой

12 970 ₽11 970 ₽

В корзину

4.95479 отзывов

Microsoft 365 Персональный — подписка на 1 год для России

6 970 ₽

В корзину

Дистрибутивы: Microsoft Excel 2024, Microsoft Office 2024 Home, Microsoft 365 Персональный (Personal).

Частые вопросы

Почему сводная таблица не обновляется автоматически?

Сводная строится по сохранённому снимку данных и не следит за источником. После правок нажмите Alt+F5 или «Обновить» в контекстном меню. Чтобы данные обновлялись хотя бы при открытии книги, включите «Обновлять данные при открытии файла» в параметрах сводной на вкладке «Данные».

Как сделать сводную таблицу из нескольких листов?

Если листы одинаковые по структуре, объедините их в одну таблицу: вручную или через Power Query, если данные приходят регулярно. Если это разные таблицы с общим столбцом, добавьте их в модель данных и свяжите через «Данные» → «Отношения». Модель данных доступна только в Excel для Windows.

Как посчитать количество уникальных значений в сводной таблице?

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

Как удалить сводную таблицу, но оставить цифры?

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

Почему в сводной таблице «Количество», а не «Сумма»?

В столбце источника есть пустые ячейки или текст. Исправьте источник (заполните пустые ячейки, преобразуйте числа из текста), обновите сводную и переключите операцию на «Сумму» через «Итоги по».