Сводная таблица помогает собрать отчёт из списка операций: посчитать продажи по городам, сравнить категории и проверить общий итог. Достаточно подготовить исходные строки и указать, какие поля нужно группировать, а какие суммировать.
Ниже разберём небольшой пример в настольном Excel для Windows. Для этой задачи подходят Excel в составе Office 2021, Office 2024 и Microsoft 365. Power Pivot и модель данных в примере не понадобятся.
Подготовьте исходную таблицу
Одна строка должна описывать одну операцию, а каждый столбец хранить один вид данных. Оставьте единственную строку заголовков. Уберите объединённые ячейки, пустые разделительные строки и промежуточные итоги из диапазона, который будете анализировать.
Для проверки скопируйте этот учебный набор в Excel. Суммы условные, они не относятся к ценам магазина.
| Заказ | Город | Категория | Сумма |
|---|---|---|---|
| 1001 | Москва | Периферия | 3000 |
| 1002 | Самара | Накопители | 5000 |
| 1003 | Москва | Накопители | 7000 |
| 1004 | Самара | Периферия | 2000 |
| 1005 | Москва | Периферия | 4000 |
| 1006 | Самара | Накопители | 1000 |
Выделите диапазон и нажмите Ctrl + T. Укажите, что таблица содержит заголовки. На вкладке конструктора таблицы задайте имя SalesData. При добавлении строк Excel сможет расширять такую таблицу; после обновления сводной новые строки попадут в расчёт.
Проверьте столбец «Сумма». Значения должны быть числами, без вписанного в ячейки слова «рублей». Денежное обозначение задают форматом. Если вы получили данные в CSV, сначала выполните импорт CSV с проверкой кодировки и типов столбцов.
Создайте сводную на отдельном листе
- Щёлкните любую ячейку внутри
SalesData. - Выберите «Вставка → Сводная таблица», затем источник «Из таблицы или диапазона», если Excel предлагает выбор.
- Проверьте, что в поле источника указана ваша таблица.
- Выберите размещение на новом листе и нажмите «ОК».
Справа появится список полей. Если его нет, щёлкните внутри сводной и включите «Список полей» на вкладке её анализа. Названия вкладок немного различаются между выпусками Excel.
Распределите поля по областям
Перетащите «Город» в строки, «Категория» в столбцы, «Сумма» в значения. Поле «Заказ» пока оставьте невыбранным. В ячейках отчёта нужны суммы операций на пересечении города и категории.
У Москвы должно получиться 7000 по периферии и 7000 по накопителям, всего 14 000. У Самары соответственно 2000 и 6000, всего 8000. Общий итог равен 22 000.
Теперь попробуйте поменять местами город и категорию. Числа останутся теми же, изменится расположение групп. Для отчёта по ассортименту удобнее держать категории в строках; для сравнения городов оставьте исходный вариант.
Исправьте «Количество» вместо «Суммы»
Если в значениях появились единицы и количество записей, откройте параметры поля значений и проверьте операцию. Excel может выбрать подсчёт, когда встречает текст или пустые значения в числовом поле.
Сначала исправьте данные в исходной таблице, затем обновите сводную и выберите «Сумма». Одного переключения операции недостаточно: текстовые значения при суммировании не превратятся в нужные числа. Вы рискуете получить аккуратный отчёт с заниженным итогом.
В том же окне задайте числовой формат для всего поля, например с разделителем тысяч. Так вы оформите значения отчёта согласованно. Не вписывайте исправленные суммы поверх рассчитанных ячеек сводной.
Добавьте строку и обновите отчёт
Допишите в SalesData заказ 1007: Москва, Периферия, 500. Убедитесь, что строка вошла в таблицу. Затем щёлкните сводную правой кнопкой и выберите «Обновить».
Общий итог должен стать 22 500, сумма Москвы 14 500, а её периферии 7500. Проверяйте результат обновления, даже если в вашей версии Excel есть автоматический режим. Поведение зависит от выпуска и настроек книги.
Если вы построили сводную по фиксированному диапазону и дописали строки за его пределами, проверьте «Изменить источник данных» на вкладке анализа. Команда обновления читает выбранный источник, поэтому строка вне него в отчёт не попадёт.
Проверьте смысл показателей перед отправкой
Сравните общий итог с исходными данными без фильтров. Затем проверьте несколько групп вручную, особенно если названия городов или категорий набирали разные сотрудники. «Москва» и «Москва » с лишним пробелом могут потребовать очистки исходного текста.
В этом примере на каждый заказ приходится одна строка. В реальной выгрузке один заказ может занимать несколько строк с товарами. Операция «Количество» посчитает заполненные записи, поэтому число строк нельзя без проверки назвать числом уникальных заказов.
Сохраните книгу в .xlsx и подпишите, какой период отражает отчёт. Перед отправкой уберите случайные фильтры либо укажите их рядом с таблицей: получатель должен понимать, какие данные вошли в итог.