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

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

Все примеры я построил в Microsoft Excel 2021 (сборка 16.0.17002.20000) с русским интерфейсом, все цифры в статье посчитал Excel, а картинки сняты прямо из него. Учебный файл с данными и готовыми сводными можно скачать и проходить шаги вместе со статьёй.

Что такое сводная таблица и когда она нужна

Сводная таблица (по-английски PivotTable) берёт исходный список и складывает, считает или усредняет значения по группам, которые вы выбрали. Исходные данные она не меняет: отчёт строится рядом, обычно на отдельном листе, и его можно перестроить за пару секунд, просто перетащив поле в другую область.

Сводная нужна, когда строк много, а вопросы к ним однотипные: «сколько по каждому...», «как по месяцам...», «какая доля...». То же самое можно посчитать формулами СУММЕСЛИ, но каждый новый разрез означает новые формулы.

Для примера я сгенерировал журнал продаж небольшого магазина компьютерных аксессуаров за июль, август и сентябрь 2026 года: 240 строк, 3 менеджера, 3 города, 9 товаров в 4 категориях. Данные вымышленные, но цифры в статье настоящие: их посчитал Excel по этому журналу. На этом примере дальше и соберём сводную таблицу в Excel пошагово.

Подготовка данных: что проверить до сводной

Половина проблем со сводными таблицами возникает из-за данных, а не из-за самой сводной. Перед созданием проверьте пять вещей:

Одна строка исходных данных должна описывать одно событие: одну продажу, один платёж, одну заявку.

Оформите данные как умную таблицу

Щёлкните в любую ячейку списка и нажмите Ctrl+T (или Вставка → Таблица). Excel сам определит границы данных. Проверьте, что стоит галочка «Таблица с заголовками», и нажмите ОК. Строки раскрасятся через одну, а в шапке появятся стрелки фильтра.

Главное, что это даёт: сводная, построенная на умной таблице, ссылается на таблицу по имени, а не на диапазон адресов. Новые строки, добавленные внизу таблицы, попадут в сводную при следующем обновлении. Справка Microsoft прямо советует использовать таблицы как источник именно по этой причине.

Я проверил разницу на своём файле. Добавил три продажи на 9 540 ₽ в конец умной таблицы и обновил сводную: общий итог вырос с 812 440 до 821 980 ₽. Затем построил такую же сводную на обычном диапазоне A1:G241 и дописал строку 242: после обновления итог остался 812 440 ₽, потому что сводная по-прежнему смотрела на старый диапазон Данные!R1C1:R241C7.

Имя таблицы видно и меняется на вкладке Конструктор таблиц слева. В моём файле таблица называется «Продажи», и именно это имя стоит в источнике данных сводной.

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

Шаг 1. Вставьте сводную таблицу

1. Щёлкните в любую ячейку умной таблицы. Выделять весь диапазон не нужно.
2. Откройте вкладку Вставка и нажмите Сводная таблица.
3. В окне «Создание сводной таблицы» в поле таблицы или диапазона уже стоит имя «Продажи». Ниже выберите На новый лист.
4. Нажмите ОК.

Excel добавит новый лист с пустой заготовкой сводной, а справа откроет панель полей. Если панель пропала, щёлкните правой кнопкой по сводной и выберите Показать список полей.

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

Шаг 2. Разложите поля по областям

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

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

Я положил «Категорию» в строки, «Город» в столбцы, «Сумму» в значения и получил такой отчёт:

Excel сам назвал поле «Сумма по полю Сумма», подписал строки и столбцы «Названия строк» и «Названия столбцов» и добавил общие итоги справа и снизу. Отчёт сразу отвечает на вопросы: больше всего принесло «Аудио» (285 580 ₽), больше всего продали в Москве (390 180 ₽), а меньше всего выручки дали кабели в Екатеринбурге (9 510 ₽).

Чтобы перестроить отчёт, перетащите поле в другую область или обратно в список полей. Исходные данные при этом не меняются.

Шаг 3. Сумма, количество, среднее: смените операцию

По умолчанию для числового поля Excel считает сумму, для текстового количество. Операцию можно поменять двумя способами:

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

Одно и то же поле можно положить в «Значения» несколько раз и считать по-разному. Я сделал отчёт по товарам, в котором поле «Сумма» стоит четыре раза:

Операция «Количество» считает не штуки товара, а строки исходных данных, то есть число продаж. Колонок купили 56 штук за 36 продаж. Если нужны штуки, кладите в значения поле «Количество» с операцией «Сумма».

Проценты: «% от общей суммы»

Чтобы увидеть долю каждой строки в итоге, щёлкните правой кнопкой по числу → Дополнительные вычисления → % от общей суммы. В моём отчёте на колонки пришлось 20,6% выручки, на внешний SSD 17,2%, на оба вида кабелей вместе около 10%. Там же есть «% от суммы по строке», «% от суммы по столбцу», нарастающий итог и ранжирование.

Сортировка и фильтр в сводной

Отчёт выше отсортирован по выручке от большей к меньшей. Для этого щёлкните правой кнопкой по любому числу в столбце «Выручка» → Сортировка и выберите сортировку по убыванию. Сортировку по названиям (от А до Я) задаёт тот же пункт меню, если щёлкнуть по названию строки, а не по числу.

Фильтровать можно тремя способами:

• стрелка рядом с «Названия строк» или «Названия столбцов»: снимите лишние галочки или задайте условие, например «первые 5 по выручке»;
• поле в области «Фильтры»: над сводной появится выпадающий список;
• срезы и временная шкала, о них ниже.

Группировка по месяцам

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

1. Щёлкните правой кнопкой по любой дате в сводной → Группировать...
2. В окне «Группирование» оставьте даты начала и окончания как есть.
3. В списке «С шагом» выберите Месяцы. Если данные охватывают больше одного года, выделите ещё и Годы, иначе июль 2025 и июль 2026 сложатся в одну строку «июл».
4. Нажмите ОК.

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

Числа группируются так же, только в окне задаётся шаг интервала.

Срезы и временная шкала

Срез это панель с кнопками для фильтра прямо на листе: нажали «Казань», и сводная показывает только Казань. Это нагляднее стрелок фильтра, особенно если отчёт смотрят другие люди.

Чтобы добавить срез, щёлкните в сводную, на вкладке Анализ сводной таблицы нажмите Вставить срез (или Вставка → Срез), отметьте поле «Город» и нажмите ОК. Несколько кнопок выбираются с зажатой Ctrl, кнопка с воронкой в углу среза снимает фильтр.

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

На картинке срез отфильтровал Казань, и общий итог стал 281 680 ₽, ровно столько же, сколько в столбце «Казань» первой сводной. Один срез можно подключить к нескольким сводным, построенным на одних данных: выделите срез и на его вкладке откройте подключения к отчётам. Тогда один щелчок фильтрует сразу весь лист с отчётами.

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

Это главное, что нужно запомнить: сводная таблица не пересчитывается сама, в отличие от формул. Я проверил это на учебном файле: после добавления трёх строк в исходную таблицу итог в сводной оставался прежним, 812 440 ₽, пока я её не обновил. После обновления стало 821 980 ₽.

Способы обновить:

Если сводная построена на обычном диапазоне, а данные выросли, обновление не поможет: нужно расширить источник через Анализ сводной таблицы → Источник данных... Проще один раз превратить данные в умную таблицу и выбрать её как источник.

Вычисляемое поле

Вычисляемое поле это новое поле сводной, которое считается по формуле из других полей. Пример: средняя цена одной проданной штуки по категориям, то есть выручка, делённая на количество.

1. Щёлкните в сводную.
2. Анализ сводной таблицы → Поля, элементы и наборы → Вычисляемое поле...
3. В поле «Имя» напишите «Средняя цена».
4. В поле «Формула» соберите =Сумма/Количество: выделяйте поля в списке и нажимайте Добавить поле, знак деления ставьте с клавиатуры.
5. Нажмите ОК. Новое поле появится в списке и сразу встанет в «Значения».

Важная деталь: формула вычисляемого поля работает с итогами, а не с каждой строкой. Excel сначала складывает выручку и штуки по категории, а потом делит одно на другое. Для «Аудио» получилось 285 580 / 122 = 2 341 ₽ за штуку, для кабелей 418 ₽, а по магазину в целом 1 382 ₽. Это именно средняя цена штуки. «Средний чек» из предыдущего отчёта (3 385 ₽) считает другое: среднюю сумму одной продажи, в которой бывает несколько штук.

Из-за этой особенности вычисляемое поле не подходит для расчётов «по строке», например для =Количество*Цена по каждой продаже: Excel перемножит итоги, и результат будет неверным. Такие столбцы лучше добавить в исходную таблицу обычной формулой. Если данных из соседней таблицы не хватает, например цены лежат в отдельном прайсе, подтяните их функцией ВПР и только потом стройте сводную.

Детализация: откуда взялась цифра

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

Я открыл детализацию ячейки «Периферия / Казань» со значением 77 170 ₽. Excel создал лист «Подробности1» с умной таблицей из 28 строк, сумма которых ровно 77 170 ₽. Это удобный способ проверить подозрительную цифру и найти продажу, которая выбивается из ряда.

Лист детализации это копия данных: правки на нём в исходную таблицу не попадут, после проверки его можно удалить.

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

Обычная сводная строится по одной таблице. Если данные лежат на нескольких листах с одинаковыми столбцами (например «Июль», «Август», «Сентябрь»), их нужно сначала собрать в одну таблицу. В Excel 2021 это удобнее всего сделать через Power Query, он встроен во вкладку Данные:

1. Оформите данные на каждом листе как умную таблицу и дайте таблицам понятные имена.
2. Щёлкните в первую таблицу → Данные → Из таблицы/диапазона. Откроется редактор Power Query. Выберите Закрыть и загрузить в... → «Только создать подключение». Повторите для остальных таблиц.
3. В редакторе Power Query на вкладке Главная нажмите Добавить запросы → Добавить запросы как новые, выберите вариант для трёх и более таблиц и перенесите все месячные таблицы в список добавляемых.
4. Загрузите получившийся запрос на новый лист как таблицу и постройте по ней сводную как обычно.

Power Query складывает таблицы по названиям столбцов, а не по их порядку. Если в одной из таблиц столбца нет, в этих строках будет пусто.

Я проверил этот путь на тех же данных, разрезанных на три листа: 70 продаж в июле, 70 в августе и 100 в сентябре. Объединённая таблица получила все 240 строк, а сводная по месяцам показала те же 250 260, 229 770 и 332 410 ₽, что и сводная по исходному журналу. Когда на месячных листах появятся новые строки, нажмите Данные → Обновить все, чтобы обновились и запрос, и сводная, и сверьте общий итог.

Второй путь называется модель данных: при создании сводной ставится галочка «Добавить эти данные в модель данных», и в одну сводную можно взять поля из нескольких таблиц, связанных общим столбцом. Это нужно, когда таблицы разные, например продажи и справочник товаров с категориями. Для одинаковых таблиц по месяцам модель данных избыточна: проще склеить их в одну через Power Query.

Сводная диаграмма

Сводная диаграмма строится по сводной таблице и меняется вместе с ней. Щёлкните в сводную и нажмите Анализ сводной таблицы → Сводная диаграмма, выберите тип, например гистограмму. Кнопки полей прямо на диаграмме работают как фильтры: если оставить в списке одну категорию, и таблица, и диаграмма покажут только её.

На моей диаграмме видно то, что в таблице заметить сложнее: в августе аудио провалилось почти вдвое (60 920 ₽ против 111 160 ₽ в июле), а в сентябре периферия выросла до 114 120 ₽.

Типичные ошибки и что с ними делать

Текст ошибки про имя поля я получил в Excel 2021 дословно так: «Недопустимое имя поля сводной таблицы. Чтобы создать сводную таблицу, используйте данные, организованные в виде списка с заголовками столбцов». Хватило одной пустой ячейки в шапке.

Ошибку с «Количеством» я тоже воспроизвёл: заменил в копии журнала одну сумму из 240 на текст «1790», и поле «Сумма» при добавлении в значения сразу получило операцию «Количество по полю Сумма» и показало 240 вместо выручки. Числа-текст обычно отмечены зелёным треугольником в углу ячейки. Одна пустая ячейка в столбце «Количество» в моей проверке операцию не поменяла.

Сводная таблица в Google Таблицах

В Google Таблицах сводная делается похоже: выделите данные с заголовками → Вставка → Сводная таблица, выберите новый лист. Справа откроется редактор с теми же областями «Строки», «Столбцы», «Значения» и «Фильтры», поля добавляются кнопкой «Добавить».

Два отличия от Excel, которые стоит знать:

• сводная в Google Таблицах обновляется сама при изменении исходных данных, кнопка «Обновить» не нужна;
• вычисляемое поле задаётся обычной формулой, в которой вместо адресов ячеек стоят названия столбцов.

Учебный файл из статьи я проверял только в Excel.

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