Прайс на тысячу позиций лежит в почте, а руководитель спрашивает: «Сколько у нас товаров в каждой категории и какая средняя цена по брендам?» Считать руками через фильтры — полчаса кропотливой работы, и завтра всё по новой. Для таких вопросов в Excel есть инструмент, который отвечает за пару минут: сводная таблица. Разбираем, какие ответы она даёт на данных прайса и как не сломать её ещё до построения.
На какие вопросы отвечает сводная таблица
Сводная берёт плоскую таблицу и схлопывает её в компактный отчёт: в строках — значения выбранного поля, на пересечениях — посчитанные по ним числа. На прайсе типовые ответы выглядят так:
- Сколько товаров в каждой категории — категорию в строки, артикул в значения, функция «Количество».
- Средняя цена по бренду — бренд в строки, цену в значения, функция «Среднее». Сразу видно, кто у вас в премиуме, а кто в эконом-сегменте.
- Распределение остатков — сумма остатков по категориям или складам: где полки забиты, а где пусто.
- Ценовая вилка группы — функции «Минимум» и «Максимум» по цене внутри категории.
Готовый отчёт легко переключается: перетащили в строки другое поле — получили новый разрез без единой формулы. Поэтому сводная — первый инструмент быстрой разведки ассортимента.
Подготовка файла: сводная требует плоскую таблицу
Главное условие — исходник должен быть плоским: одна строка шапки, дальше только товары. Прайс поставщика обычно устроен иначе: сверху логотип и реквизиты, шапка в две строки, между группами товаров — строки-разделы «Сантехника», «Крепёж», внизу «Итого». Каждый такой элемент ломает сводную.
Ручная чистка на тысяче строк занимает вечер, поэтому файл удобнее прогнать через конвертер:
- Шаг «Структура». Указываете, с какой строки начинается таблица, — реквизиты и пустые строки сверху отсекаются. Многострочная шапка схлопывается в одну строку, строки-разделы удаляются. Чем опасны такие вставки, подробно разобрано в статье про пустые строки и разделы в прайсе.
- Шаг «Колонки». Действие «Убрать пробелы» чистит артикулы и названия, «Заменить текст» вычищает «руб.» и единицы измерения из цен — числа должны быть числами. Лишние колонки просто удаляются.
- Шаг «Фильтры». Убираются строки «Итого» и позиции без остатка, если они в отчёте не нужны.
На выходе — XLSX со значениями без формул: готовый фундамент для сводной. Проверить можно бесплатно: файлы до 2 МБ обрабатываются без регистрации, а настройки сохраняются в шаблон — на следующем прайсе того же поставщика подготовка займёт минуту.
Строим сводную: четыре шага
В Excel механика одинаковая во всех свежих версиях:
- Поставьте курсор в любую ячейку таблицы и на вкладке «Вставка» нажмите «Сводная таблица». Диапазон Excel определит сам, размещение — новый лист.
- Перетащите поле-разрез в область «Строки» — категорию, бренд или склад. Сводная покажет список уникальных значений.
- Перетащите числовое поле в область «Значения» — цену, остаток или артикул. Для чисел по умолчанию ставится сумма, для текста — количество.
- Смените функцию при необходимости: правый клик по значениям → «Параметры полей значений» → «Среднее», «Количество», «Минимум». Там же задаётся числовой формат, чтобы убрать копейки у среднего.
Дальше отчёт можно дробить: поле в области «Колонки» даст двумерную таблицу (бренды внутри категорий), поле в «Фильтры» — быстрый переключатель по поставщику или складу.
Типовые ошибки, из-за которых сводная врёт
- Пустые строки внутри таблицы. Excel обрезает диапазон по первому разрыву — часть товаров в отчёт просто не попадает. Поэтому пустые строки вычищают до построения, а не после.
- Текст в числовой колонке. Если хоть одна ячейка цены содержит текст — пробел, «руб.», дефис вместо числа — сводная молча переключается с суммы на количество, и вместо денег вы видите число позиций. Классика жанра — неразрывные пробелы в ценах из выгрузок 1С; их разбор и лечение описаны в статье про пробелы в цифрах.
- Пустые ячейки в шапке. Excel откажется строить отчёт и потребует имена для всех полей.
- Устаревшие данные. Сводная не пересчитывается сама: после замены исходника — правый клик по отчёту → «Обновить». Если строк стало больше, прежний диапазон их не захватит. Надёжнее заранее превратить исходник в «умную таблицу» — тогда диапазон расширяется сам.
Сводная и ABC-анализ: в каком порядке применять
Инструменты решают разные задачи. Сводная — мгновенный обзор: сколько у вас позиций и как они распределены по категориям, брендам и ценам. Она отвечает на вопрос «что у нас есть», но не «что у нас работает». Для второго вопроса нужен ранжированный разбор по продажам — ABC-анализ ассортимента, где позиции делятся на группы по вкладу в выручку.
Рабочая связка выглядит так: сначала сводной за десять минут смотрите структуру прайса и находите раздутые категории, затем по этим категориям считаете ABC и решаете, что выводить. Обе процедуры стоят на одном фундаменте — чистом плоском файле, так что подготовка через конвертер делается один раз на оба отчёта.
Частые вопросы
Сводная посчитала количество вместо суммы — что сломалось?
В числовой колонке завёлся текст: пробел, «руб.», дефис вместо нуля или неразрывный пробел из выгрузки 1С. Сводная не умеет суммировать текст и молча переключается на подсчёт. Найдите и почистите такие ячейки, затем обновите отчёт — функция вернётся к сумме.
Можно ли строить сводную прямо по файлу поставщика?
Технически — да, практически — бессмысленно. Реквизиты сверху, двухстрочная шапка и строки-разделы попадут в отчёт как отдельные категории, а «Итого» задвоит суммы. Сначала приведите файл к плоскому виду: одна шапка, только товарные строки, числа без текста.
Как обновить сводную после прихода нового прайса?
Правый клик по отчёту → «Обновить» — если новые данные встали на место старых и вписались в прежний диапазон. Если строк стало больше, диапазон нужно расширить в настройках источника или заранее превратить исходник в умную таблицу: тогда обновление всегда захватывает все строки.
Чем сводная лучше обычных фильтров?
Фильтры показывают сами строки, сводная — агрегаты по ним: количество, сумму, среднее. Вопрос «какая средняя цена по бренду» фильтром не решается вовсе, а сводная отвечает на него одним перетаскиванием поля.