Анализ продаж в Excel: где таблицы ломаются и когда пора автоматизировать

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

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

Как собрать анализ продаж в Excel: рабочая структура

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

Главный принцип: одна строка - это один день по одной точке. Не неделя, не месяц, не итог. Чем мельче гранулярность исходных данных, тем больше срезов вы потом соберёте из той же таблицы. Если будете писать сразу итоги за месяц, разложить их обратно на дни и точки уже не получится.

Вот минимальный набор столбцов, который закрывает базовый разбор розницы.

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

Формулы, без которых таблица бесполезна

Сами по себе выручка и чеки мало что говорят. Смысл появляется, когда вы считаете производные показатели и сравниваете.

• Средний чек: =Выручка/Количество чеков. Это первый рычаг, который показывает, что покупатель кладёт в корзину.
• Маржа в процентах: =(Выручка-Себестоимость)/Выручка. Без неё рост выручки может оказаться ростом скидок, а вы этого не увидите.
• Доля возвратов: =Возвраты/Выручка. Всплеск возвратов маскирует реальную картину продаж.
• Динамика к периоду: =(Текущая выручка-Прошлая выручка)/Прошлая выручка. Это и есть рост или падение в процентах.

Маленький, но важный приём: завернуть деление в ЕСЛИОШИБКА, чтобы при нулевых чеках таблица не сыпала ошибками. Например =ЕСЛИОШИБКА(Выручка/Чеки;0). На одной точке это мелочь, на тридцати столбцах за месяц такие ошибки ломают сводную.

Сводная таблица: где собирается вся картина

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

По шагам:

1. Выделите весь диапазон с заголовками.
2. Вставка - Сводная таблица.
3. В строки перетащите Точку, в значения - Выручку (сумма) и Количество чеков (сумма).
4. Добавьте вычисляемое поле Средний чек или посчитайте его рядом.
5. В фильтр поставьте Дату или День недели, чтобы переключать периоды.

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

Пара приёмов, которые делают сводную полезнее:

• Включите группировку дат по неделям и месяцам прямо в сводной (правый клик по полю даты, Группировать), чтобы переключаться между дневным и месячным взглядом без перестроения.
• Добавьте срезы (Slicer) по точке и дню недели. Это кнопки-фильтры, которыми удобно листать срезы мышкой, не лазая в выпадающие списки.
• Условным форматированием подсветите ячейки с динамикой: красным минус, зелёным плюс. На сети это сразу выдёргивает глазом точку, которая просела, без чтения каждой цифры.

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

Как сделать LFL-сравнение в Excel

LFL (like for like, сопоставимые продажи) - это сравнение с аналогичным периодом, а не со вчера. Без него любой анализ продаж врёт, потому что суббота и вторник в рознице это разные магазины по поведению, а декабрь и февраль вообще несравнимы.

В Excel LFL делается просто. Добавьте к таблице столбец с выручкой за тот же период прошлого года (или за тот же день недели прошлой недели, если данных за год нет). Рядом столбец с формулой динамики:

LFL, % = (Выручка тек. период - Выручка прошлый период) / Выручка прошлый период

Чтобы подтянуть прошлогоднюю выручку автоматически, а не копировать руками, работает ВПР (VLOOKUP) или ИНДЕКС+ПОИСКПОЗ по ключу из даты и точки. Это уже требует аккуратности: ключ должен быть уникальным, иначе формула подтянет не ту строку. Здесь же кроется первая трещина, к которой мы вернёмся: чем больше точек и дней, тем сложнее держать эти связки без ошибок.

Если у вас сеть, в сводную добавьте сравнение точек между собой по LFL. Просели все разом - причина внешняя, сезон или общий спад. Просела одна - причина локальная, в этой точке. Это самый быстрый фильтр, который отсекает половину ложных гипотез. Подробнее логику декомпозиции я разбирал в материале от каких показателей зависит выручка магазина.

Что Excel делает хорошо

Чтобы было честно, начну с сильных сторон. Excel не враг, и для части задач он остаётся лучшим выбором.

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

Разовый или периодический разбор. Нужно посчитать ABC-анализ ассортимента раз в квартал, прикинуть сезонность за два года, проверить гипотезу по акции - Excel идеален. Гибкость, с которой вы за пять минут строите любой нестандартный срез, не даёт ни один готовый сервис.

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

Ноль затрат на старт. Excel уже есть у всех. Не нужно ничего подключать, настраивать и платить. Для проверки идеи это лучшая стартовая площадка.

Если ваша ситуация описана этими пунктами, дальше можно не читать: ведите в Excel и не тратьте деньги. Проблема возникает у тех, кто перерос этот формат, но продолжает тащить таблицу на себе.

Где Excel ломается

А теперь честно о трещинах. Они не в самом Excel, а в попытке делать им то, для чего он не предназначен: ежедневно сводить несколько точек руками.

Несколько точек

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

Ежедневная сборка

Месячный разбор в Excel делается раз и спокойно. Ежедневный - это та же работа, повторённая 30 раз в месяц, каждое утро, без выходных. На одной точке это десять минут. На сети из пяти-десяти точек выгрузка, сведение, расчёт LFL и сравнение занимают полдня. В реальности этого никто не делает каждый день, поэтому разбор сползает на раз в неделю, а просадку, случившуюся в понедельник, собственник замечает в пятницу. Деньги за эти четыре дня уже потеряны.

Ручные ошибки

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

Excel показывает что, но не говорит почему

Это, пожалуй, главное. Даже идеально собранная таблица отвечает только на вопрос что произошло: выручка точки упала на 12 процентов. Но она не говорит, почему. Просел трафик, конверсия или средний чек? Это внешняя причина или проблема в зале? Раскладку по рычагам приходится делать в голове, каждый раз заново, по каждой точке. Таблица - это сырьё для вывода, а не сам вывод.

Время собственника

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

Признаки, что пора переходить на автоматический сбор

Не каждому нужна автоматизация. Вот честный чек-лист. Если узнаёте у себя три и более пункта, ручной Excel вы уже переросли.

• У вас три и более точки. Сведение нескольких потоков руками каждый день не выживает.
• Разбор делается реже, чем хотелось бы. Планировали смотреть ежедневно, по факту открываете таблицу раз в неделю или когда что-то уже горит.
• Цифры всегда вчерашние или позавчерашние. К моменту, когда отчёт готов, реагировать на просадку уже поздно.
• Регулярно ловите ошибки в таблице. Нашли сдвинутую формулу или выгрузку не за тот день - это не разовый сбой, а системный риск ручной работы.
• Таблицу ведёте вы или один незаменимый человек. Если он в отпуске или уволится, разбор встанет.
• Времени на сборку уходит больше, чем на выводы. Вы оператор таблицы, а не управленец.

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

Если вы узнали себя в этом списке, посмотрите, как устроен Утренний разбор: он собирает те же цифры из вашего товароучёта сам и присылает готовый разбор к 9 утра. Ниже честно о том, что это даёт и где границы.

Что даёт автоматизация

Автоматический сбор - это не магия и не замена головы собственника. Это снятие ручного труда с трёх трещин, о которых шла речь выше.

Данные собираются сами. Сервис подключается к товароучёту (например, к МойСклад) и забирает выручку, чеки, средний чек, маржу и возвраты по каждой точке без выгрузок и копирования. Та самая полдня на сборку освобождается.

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

Готовый вывод вместо сырой таблицы. Хороший сервис не присылает вам очередную таблицу для чтения, а отдаёт разбор: какая точка просела, на сколько и какой рычаг это уронил. Например, формат вроде «точка на Ленина минус 12 процентов, причина в конверсии, трафик в норме». Вы получаете не данные, а направление действия.

Ошибки ручного ввода исчезают. Машина не сдвигает формулу и не путает день. Цифры в отчёте ровно те, что в кассе.

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

Если хотите глубже разобрать саму методику разбора продаж, она подробно расписана в материале анализ продаж розничного магазина, а формат ежедневной сводки - в ежедневном отчёте о продажах.

Excel против автоматического сбора: когда что

Сведём выбор в одну таблицу, чтобы не гадать.

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

FAQ

Как сделать анализ продаж в Excel? Соберите таблицу, где одна строка - это один день по одной точке: дата, точка, выручка, количество чеков, средний чек, себестоимость и маржа. Производные показатели считайте формулами (средний чек = выручка/чеки, маржа в процентах, динамика к периоду), а итоги собирайте сводной таблицей по точкам и периодам. Для честного сравнения добавьте столбец LFL с тем же периодом прошлого года.

Какая структура таблицы анализа продаж правильная? Гранулярность по дням и точкам, метрики по столбцам. Минимум: дата, день недели, точка, выручка, чеки, средний чек, себестоимость, маржа в рублях и процентах, возвраты. Чем мельче исходные данные, тем больше срезов соберёте сводной таблицей потом.

Как сделать LFL-сравнение в Excel? Добавьте столбец с выручкой за аналогичный период прошлого года или за тот же день недели, затем столбец с формулой (текущая минус прошлая) делить на прошлую. Прошлые значения можно подтягивать через ВПР или ИНДЕКС+ПОИСКПОЗ по уникальному ключу из даты и точки. LFL убирает календарный шум и показывает реальную динамику.

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

Когда пора переходить с Excel на автоматизацию анализа продаж? Когда совпадают три и более признака: три и более точки, разбор реже раза в неделю, цифры всегда вчерашние, регулярные ручные ошибки, таблицу тащит один незаменимый человек, на сборку уходит больше времени, чем на выводы. До этого порога Excel экономнее.

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

Excel показывает, почему упала выручка? Нет, таблица показывает только что упало, например выручка точки на 12 процентов. Раскладку на причину (трафик, конверсия или средний чек, внешняя причина или проблема в зале) приходится делать в голове по каждой точке. Автоматический разбор делает эту декомпозицию сам и подсказывает направление.

Сколько времени занимает ежедневный анализ продаж в Excel? На одной точке около десяти минут. На сети из пяти-десяти точек выгрузка, сведение, расчёт LFL и сравнение занимают порядка половины рабочего дня, поэтому ежедневно это в реальности почти не делается.

Что почитать дальше

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

Если у вас от трёх точек и Excel уже не успевает за бизнесом, посмотрите, как работает Утренний разбор. Подключение к товароучёту за пять минут, первая сводка завтра в 9 утра, первые 14 дней без оплаты, данные не уходят с российских серверов.