Как сделать сводную таблицу в Excel из списка продаж: пример для начинающих

Задача типовая: выгрузка продаж на сотни строк, а руководителю нужна выручка по менеджерам и товарам к обеду. Справка Microsoft описывает путь из четырёх действий: выделить ячейки, выбрать Insert → PivotTable, указать New Worksheet или Existing Worksheet и нажать OK^[1].

Сводные таблицы в Excel осваивают в три приёма. Сначала изучите базу — области отчёта и функции итогов, затем попробуйте их на учебном журнале в тренажёре и примените к своим данным из учётной программы. Главная ловушка новичка — числа, сохранённые как текст: для нечисловых данных отчёт по умолчанию берёт функцию СЧЁТ, а не сумму^[3], и вместо выручки показывает число строк.

Ключевые факты: сводная таблица в Excel

• Сводная таблица в Excel группирует записи источника и считает итоги в отдельном отчёте — на новом или существующем листе^[1].
• Отмеченные поля отчёт раскладывает сам: нечисловые — в строки, даты и время — в столбцы, числовые — в значения^[1].
• В одном отчёте помещается до 256 полей значений, а в одном поле — до 1 048 576 уникальных элементов^[4].
• Функция СЧЁТ отличается от суммы тем, что включается по умолчанию для пустых и нечисловых данных и считает строки, а не выручку^[3].
• Подсчёт разных элементов работает только при использовании модели данных^[3].
• Справка Microsoft называет Alt+F5 сочетанием для обновления выбранной сводной, а Refresh All — командой для всех отчётов книги^[2].

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

Сводная таблица в Excel собирает итоги по группам из исходного списка строк без единой формулы. Справка Microsoft требует от источника три вещи: данные расположены столбцами, у каждого столбца одна строка уникальных непустых заголовков, двойных заголовков и объединённых ячеек нет^[1]. Исходные данные для примера — журнал продаж, одна строка на сделку:

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

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

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

Разобравшись с источником, переходим к созданию отчёта — на него уходит меньше минуты.

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

Сводная таблица в Excel создаётся с вкладки «Вставка» из выделенного списка с заголовками. Английская справка Microsoft называет путь Insert → PivotTable и перечисляет в блоке Applies To Excel 2016, Excel 2019, Excel 2021, Excel 2024 и Microsoft 365, а также версии 2021 и 2024 для Mac^[1].

1. Щёлкните любую ячейку журнала A1:D9.
2. На вкладке «Вставка» нажмите кнопку сводной таблицы (в английском интерфейсе — PivotTable).
3. Проверьте, что источником выбран весь диапазон или таблица.
4. Укажите место отчёта: новый лист (New Worksheet) удобнее для первого раза.
5. Нажмите «ОК» — слева появится пустой макет, справа список полей.
6. Отметьте флажками «Менеджер» и «Сумма».

Отчёт сам раскладывает отмеченные поля: нечисловые уходят в строки, даты и время — в столбцы, числовые — в значения^[1]. Если поле легло не туда, перетащите его мышью в нужную область. Результат для журнала выглядит так:

Проверить отчёт легко формулой: =СУММЕСЛИ(A2:A9;"Иванов";D2:D9) возвращает 4200, как и строка Иванова. Если сводная не совпадает с формулой, ищите в столбце «Сумма» текст вместо чисел.

Подсказки готовых макетов справка относит к подключённым возможностям Microsoft 365^[1]: при отключённых подключённых функциях рекомендаций не будет, а ручной путь работает всегда.

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

Однако готовый макет — только черновик: области отчёта устроены иначе, чем столбцы обычного листа, и от их выбора зависит вид итогов.

Строки, столбцы, значения и фильтры: пример раскладки полей

Сводная таблица в Excel раскладывает поля по четырём областям: строки, столбцы, значения и фильтры. Каждая область отвечает за свою часть отчёта, а одно поле в строках, столбцах или фильтре может стоять только один раз^[5].

Русская справка называет эти области по-разному: на одной странице — «Названия строк», «Названия столбцов» и «Фильтр отчета»^[5], а функцию итогов в контекстном меню — «Операция резюмирования»^[3]. Ориентируйтесь на смысл и расположение кнопок, а не на дословный перевод.

Перекрёстный отчёт и фильтр по региону

Перетащите «Товар» в столбцы — получится перекрёстная таблица: менеджеры по строкам, чай и кофе по столбцам, выручка на пересечении. Поле «Регион» положите в фильтры над отчётом: выбрали «Север» — итог пересчитался только по северу и стал равен 7600.

В значения одно поле добавляют сколько угодно раз: справка Microsoft предлагает перетащить его в область значений повторно, чтобы рядом стояли сумма и процент^[6]. Чем больше полей в строках, тем глубже вложенность: «Регион» над «Менеджером» даст группы Север и Юг с промежуточными итогами внутри.

Порядок полей внутри области тоже решает вид итогов. Поле выше в области строк становится внешней группой, ниже — вложенной, поэтому связка «Регион» → «Менеджер» даёт другой отчёт, чем «Менеджер» → «Регион». Справка Microsoft о сводных данных добавляет, что числовые поля по умолчанию попадают в «Значения», а нечисловые — в строки^[5].

Остаётся понять, какие итоги отчёт умеет считать, кроме суммы.

Сумма, СЧЁТ или доля: что считает область «Значения»

Сводная таблица по умолчанию складывает числовые поля, а для пустых и нечисловых данных берёт функцию СЧЁТ^[3]. Функцию меняют щелчком правой кнопкой по полю значения: русская справка перечисляет сумму, СЧЁТ, среднее, максимум, минимум, произведение, дисперсию и число разных элементов^[3].

Таблица сводит частые вопросы — выручку, число сделок, средний чек и долю в общей выручке — к функциям Сумма, СЧЁТ, Среднее и команде Show Values As.

Доли и нарастающие итоги задают командой дополнительных вычислений — в английской справке это Show Values As с вариантами % of Grand Total, % of Column Total, % of Row Total, Difference From, Running Total in и рангами^[6]. Для журнала доли менеджеров в сумме дают 100%: Иванов около 29,6%, Петрова 46,5%, Сидоров 23,9%.

Функция применяется ко всему полю сразу, поэтому сумму для одного менеджера и среднее для другого в одном столбце не получить. Число разных элементов работает только при использовании модели данных^[3] — в обычном отчёте без модели этого пункта нет.

Промежуточные и общие итоги включают на вкладке «Конструктор» в группе «Макет»: кнопка «Промежуточные итоги» предлагает варианты «Не показывать промежуточные суммы», показ внизу или в заголовке группы, а кнопка «Общие итоги» включает и отключает итоги для строк и столбцов^[7].

Разобравшись с итогами, переходим к обновлению — без него отчёт быстро устаревает.

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

Сводная таблица обновляется по команде: правка исходного списка сама в итоги не попадёт. Справка Microsoft даёт три способа: кнопка Refresh в группе Data на вкладке PivotTable Analyze, пункт Refresh в контекстном меню и сочетание Alt+F5 для выбранного отчёта^[2].

Новые строки под журналом в обычный диапазон источника не попадут, пока границы не расширены через Change Data Source. Удобнее оформить журнал умной таблицей, которая растёт вместе со списком: справка отмечает, что обновление из таблицы Excel автоматически включает все её изменения^[2].

Данные с нескольких листов собирают через связи: справка о бизнес-аналитике позволяет связать несколько таблиц прямо в сводной^[8]. Отчёт, подключённый к внешним данным, по той же справке нужно периодически обновлять.

Медленное обновление большого источника видно по команде Refresh Status, а прервать его можно через Cancel Refresh^[2]. Флажок Autofit column widths on update подгоняет ширину столбцов при каждом обновлении — снимите его, если ширина настроена вручную. Когда в книге несколько сводных на одном журнале, команда Refresh All избавит от обхода каждой по очереди.

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

Когда сводная таблица не подойдёт

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

• Если у столбцов нет заголовков, заголовки повторяются или стоят в две строки с объединёнными ячейками, отчёт не соберётся корректно — сначала приведите шапку к одной строке^[1].
• Когда итог нужен в ячейке рядом с данными или в другой формуле, проще написать СУММЕСЛИ по условию прямо на листе, чем ссылаться на ячейку отчёта.
• При источнике OLAP функции сведения недоступны для выбора^[3], и операцию по полю не сменить.
• Если показателей больше 256, они не уместятся: это предел полей значений в одном отчёте, а уникальных элементов в поле — не больше 1 048 576^[4].
• Если нужно число разных клиентов, а модели данных нет, функции «Число разных элементов» не будет^[3].

Инструмент не заменяет формулы, а дополняет их: итоги по группам удобно смотреть в сводной, а расчёт в каждой строке журнала — скидка, налог, срок — остаётся формулам. Итоговую цифру для письма руководителю можно скопировать как значения на отдельный лист: тогда она не изменится после следующего обновления. Если источник подключён к внешним данным, его по справке нужно периодически обновлять^[8] — без этого цифры устареют.

Google Таблицы строят похожий отчёт через меню Insert → Pivot table: в боковой панели кнопкой Add добавляют поля в Rows, Columns и Values, и каждому столбцу тоже нужен заголовок^[9]. Остальные приёмы анализа — диаграммы, срезы, дашборды — собраны среди всех уроков тренажёра по анализу данных Excel.