Анализ продаж в Excel: выручка, динамика и менеджеры

Кому подходит такой отчёт

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

Если продаж мало, достаточно одного файла с двумя листами — «Данные» и «Аналитика». Когда источников несколько, лучше добавить справочники товаров, менеджеров и каналов, а загрузку данных автоматизировать через Power Query. Но даже в этом случае логика показателей остаётся прежней.

• Собственнику нужны выручка, прибыль, маржа и динамика.
• Руководителю продаж — план/факт, менеджеры, сделки и средний чек.
• Товарному бизнесу — категории, SKU, количество, себестоимость и валовая прибыль.
• Маркетплейсам — дополнительно комиссии, логистика, реклама и возвраты.

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

Главное правило: одна строка — одна продажа или одна товарная позиция заказа. Не объединяйте ячейки и не вставляйте промежуточные итоги внутрь журнала. Тогда фильтры, формулы и сводные таблицы будут работать предсказуемо.

Для большинства задач хватает следующих колонок:

• Дата;
• Номер заказа;
• Товар или SKU;
• Категория;
• Менеджер;
• Количество;
• Цена за единицу;
• Выручка;
• Себестоимость;
• Валовая прибыль;
• Маржа;
• Канал продаж;
• Статус заказа.

Не создавайте отдельный лист для каждого месяца. Единый журнал проще проверять и расширять, а месяц, квартал и год можно получать из даты. Отдельные листы лучше оставить для справочников, плана и итоговой аналитики.

Пошагово: как собрать анализ продаж в Excel

Шаг 1. Очистите исходные данные

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

Шаг 2. Рассчитайте показатели по каждой строке

Добавьте выручку, себестоимость, валовую прибыль и маржу. Если эти поля уже приходят из CRM или учётной системы, не пересчитывайте их без необходимости — сначала сверяйте методику.

Шаг 3. Добавьте период

Для месячного отчёта удобно иметь вспомогательную колонку «Месяц». Это упрощает SUMIFS, фильтрацию и построение графиков. Для недельного анализа можно отдельно хранить номер недели.

Шаг 4. Соберите KPI

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

Шаг 5. Добавьте разрезы

Сгруппируйте результат по товарам, категориям, менеджерам, клиентам и каналам. Не пытайтесь выводить всё сразу: оставьте 3–5 разрезов, по которым действительно принимаются решения.

Шаг 6. Постройте динамику

Для руководительского отчёта обычно достаточно одного графика выручки по месяцам и одного сравнения план/факт или рейтинга менеджеров. Если графиков слишком много, важные сигналы теряются.

Шаг 7. Проверьте итог на контрольной выборке

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

Формулы Excel для ключевых показателей

Допустим, в строке 2 количество находится в F2, цена — в G2, выручка — в H2, себестоимость — в I2, валовая прибыль — в J2, маржа — в K2.

• Выручка строки: =F2*G2
• Валовая прибыль: =H2-I2
• Маржа: =IFERROR(J2/H2;0)
• Общая выручка: =SUM(H2:H1000)
• Выручка выбранного менеджера: =SUMIFS($H:$H;$E:$E;M2)
• Количество заказов менеджера: =COUNTIFS($E:$E;M2;$L:$L;"Продан")
• Рост к прошлому периоду: =IFERROR((N3-N2)/N2;0)

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

Средний чек лучше считать как выручка / количество уникальных заказов. Если один заказ занимает несколько строк из-за нескольких товаров, обычный COUNTA по строкам даст неправильный результат.

Пример расчёта на реальных цифрах

Предположим, менеджер Мария за месяц провела 37 оплаченных заказов на 1 480 000 ₽. Себестоимость проданных товаров составила 930 000 ₽. В прошлом месяце её выручка была 1 250 000 ₽.

• Средний чек: 1 480 000 / 37 = 40 000 ₽.
• Валовая прибыль: 1 480 000 − 930 000 = 550 000 ₽.
• Маржа: 550 000 / 1 480 000 = 37,2%.
• Рост выручки: (1 480 000 − 1 250 000) / 1 250 000 = 18,4%.

Теперь отчёт уже даёт управленческий смысл. Видно не просто «Мария продала на 1,48 млн», а сколько заказов для этого потребовалось, какая прибыль осталась и действительно ли результат улучшился относительно прошлого периода.

Если другой менеджер сделал выручку 1,55 млн ₽, но при марже 24%, сравнение только по обороту будет вводить в заблуждение. Поэтому рейтинг менеджеров полезно строить сразу по нескольким показателям.

Товары, менеджеры, клиенты и каналы: что сравнивать

Товары и категории

Смотрите выручку, количество, валовую прибыль и маржу. Товар-лидер по обороту не всегда лидер по прибыли. Для большого ассортимента дополнительно используйте ABC/XYZ-анализ, чтобы отделить важные позиции от случайных всплесков спроса.

Менеджеры

Выручка показывает объём, но не качество продаж. Добавьте количество сделок, средний чек, маржу и выполнение плана. Если есть лиды, полезно считать конверсию. Отдельный разбор KPI менеджеров есть в материале «KPI менеджеров в Excel».

Клиенты

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

Каналы

Сравнивайте сайт, маркетплейсы, офлайн, партнёров и рекламные источники не только по выручке, но и по расходам. Канал с высокой выручкой может проигрывать после комиссии и стоимости привлечения.

Как оформить лист аналитики

Хороший дашборд должен читаться за несколько секунд. В верхней части разместите 4–6 KPI: выручка, прибыль, заказы, средний чек, маржа и изменение к прошлому периоду. Ниже — динамика и один-два рейтинга.

• фиксируйте заголовки журнала;
• используйте единый формат рублей и процентов;
• выделяйте отрицательную динамику условным форматированием;
• не используйте больше двух-трёх акцентных цветов;
• подписывайте период, к которому относятся KPI;
• не размещайте исходные данные и итоговый дашборд вперемешку.

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

Ошибки, которые искажают анализ продаж

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

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

Средний чек считают по строкам. Когда заказ включает три товара, три строки — это всё равно один заказ. Нужны уникальные номера заказов.

Проценты усредняют неправильно. Итоговую маржу лучше считать как общая прибыль / общая выручка, а выполнение плана — как общий факт / общий план. Среднее из процентов строк может искажать итог.

Сравнивают периоды разной длины. Первые 10 дней текущего месяца нельзя напрямую сравнивать с полным прошлым месяцем. Сравнивайте одинаковое число дней или используйте прогноз.

Нет единого справочника. «Ozon», «Озон» и «OZON» превращаются в три разных канала. То же происходит с товарами и фамилиями менеджеров.

Практические советы для регулярной работы

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

• Храните исходный журнал отдельно от итогового отчёта.
• Зафиксируйте определения: что считается продажей, заказом, возвратом и прибылью.
• Добавляйте новые строки в тот же журнал, а не копируйте файл каждый месяц.
• Проверяйте контрольную сумму после каждой новой загрузки.
• Для плана и факта используйте отдельную таблицу целей и сводите её с продажами по периоду и менеджеру.
• Когда ручная загрузка начинает занимать время, переходите на Power Query или автоматический экспорт из CRM.

Если задача связана именно с отклонениями целей, дополнительно посмотрите руководство по план-факт анализу в Excel. Для анализа этапов до продажи полезна статья о воронке продаж.