Воронка продаж в Excel: конверсия, формулы и практический пример
Как превратить журнал сделок в управленческий отчёт: считаем переходы, деньги, прогноз, менеджеров, скорость и причины потерь на реальном примере.
1. Что должна показывать рабочая воронка продаж
Воронка нужна не ради диаграммы. Её задача — ответить на четыре управленческих вопроса: сколько новых возможностей появилось, где сделки теряются, сколько денег находится в работе и какой результат можно ожидать к концу периода. Поэтому основу отчёта лучше строить из единого журнала сделок, а визуализацию добавлять уже после расчётов.
Для типичного B2B-процесса достаточно пяти понятных этапов: Лид → Контакт → Встреча → Предложение → Оплата. Если процесс другой, названия можно заменить, но каждый этап должен иметь однозначный критерий перехода. Например, Встреча означает не назначенный звонок, а фактически состоявшуюся встречу. Иначе менеджеры будут трактовать статусы по-разному, а конверсия потеряет смысл.
Полезно одновременно смотреть две воронки: по количеству сделок и по сумме. Первая показывает эффективность процесса, вторая — потенциальную выручку. Одна крупная сделка почти не изменит количество лидов, но может радикально изменить денежный прогноз.
2. Структура Excel-файла: пять листов вместо одной перегруженной таблицы
Практичная книга состоит из пяти листов. «Сделки» — исходный журнал без ручных итогов. «Справочники» — менеджеры, источники, этапы и причины отказа. «Воронка» — агрегаты по этапам. «Менеджеры» — сравнение сотрудников. «Дашборд» — ключевые показатели и графики для руководителя. Такое разделение проще проверять и расширять, чем файл, где ввод данных, формулы и диаграммы смешаны на одном листе.
На листе «Сделки» заведите столбцы: ID, дата создания, клиент, менеджер, источник, этап, сумма, вероятность, дата следующего действия, дата закрытия, результат и причина отказа. ID должен быть уникальным. Даты храните именно как даты Excel, суммы — как числа, а этапы выбирайте из выпадающего списка справочника.
Не удаляйте проигранные сделки и не перезаписывайте их как будто их не было. Статус «Проиграна», дата закрытия и причина отказа дают материал для анализа потерь. Если воронка начинается с большого потока обращений, можно отдельно вести учёт заявок и передавать квалифицированные обращения в журнал сделок. Для этого можно использовать учёт заявок в Excel.
3. Формулы Excel для количества, суммы и конверсии
Пусть на листе «Сделки» этап находится в столбце F, сумма — в G, менеджер — в D, дата создания — в B. На листе «Воронка» в A2:A6 записаны этапы. Количество сделок на этапе можно считать формулой =СЧЁТЕСЛИМН(Сделки!$F:$F;A2). Денежный объём этапа — =СУММЕСЛИМН(Сделки!$G:$G;Сделки!$F:$F;A2). Эти формулы автоматически пересчитаются при изменении журнала.
Если в B2 находится число лидов, а B3 — число сделок, дошедших до следующего этапа, конверсия перехода рассчитывается как =ЕСЛИОШИБКА(B3/B2;0). Ячейке задайте процентный формат. Для общей конверсии от лида до оплаты используйте =ЕСЛИОШИБКА(B6/B2;0). Важно не складывать проценты переходов: общая конверсия получается делением конечного количества на начальное либо произведением последовательных коэффициентов.
Для анализа конкретного менеджера добавьте критерий. Например, число оплаченных сделок Анны: =СЧЁТЕСЛИМН(Сделки!$D:$D;"Анна";Сделки!$F:$F;"Оплата"). Сумма её оплат: =СУММЕСЛИМН(Сделки!$G:$G;Сделки!$D:$D;"Анна";Сделки!$F:$F;"Оплата"). В рабочем файле лучше ссылаться на ячейку с именем менеджера, а не писать имя внутри формулы.
Создать воронку продаж в Excel
Укажите этапы и показатели — SmartXLSX соберёт персональный XLSX с расчётами и дашбордом.
Открыть инструмент →4. Практический пример: 200 лидов и поиск главного провала
Допустим, за месяц получили 200 лидов. До контакта дошли 160, до встречи — 96, предложение получили 58 клиентов, оплатили 29. Конверсии переходов составят 80,0%, 60,0%, 60,4% и 50,0%. Общая конверсия лид → оплата равна 29 / 200 = 14,5%.
Средний оплаченный чек — 85 000 ₽. Фактическая выручка тогда равна 29 × 85 000 = 2 465 000 ₽. Руководитель видит не только итог, но и рычаг роста. Например, если повысить переход Контакт → Встреча с 60% до 70% при неизменных следующих конверсиях, получится примерно 160 × 70% × 60,4% × 50% ≈ 34 оплаты. Это около пяти дополнительных сделок, или примерно 425 000 ₽ выручки при том же среднем чеке.
Такой расчёт полезнее абстрактной цели «продавать больше»: он показывает конкретный участок процесса. В нашем примере работа с назначением и проведением встреч потенциально важнее, чем увеличение рекламного трафика. Но гипотезу нужно проверять по причинам отказа и качеству лидов, а не только по одному проценту.
5. Денежная воронка и взвешенный прогноз выручки
Сделки на ранних этапах нельзя считать будущей выручкой один к одному. Для прогноза каждому этапу можно назначить историческую вероятность закрытия: например Контакт — 20%, Встреча — 35%, Предложение — 60%. Для сделки на 300 000 ₽ на этапе Предложение взвешенная сумма составит 300 000 × 60% = 180 000 ₽.
Если сумма сделки в G2, а вероятность в H2, формула взвешенного прогноза проста: =G2*H2. Общий прогноз по открытым сделкам — =СУММ(Сделки!I:I), если результат хранится в отдельном столбце I. Ещё лучше рассчитывать вероятность через справочник этапов с ПРОСМОТРX, чтобы менеджер не мог вручную поставить любой процент.
Не путайте взвешенный pipeline с планом продаж. План — целевой результат, факт — уже оплаченные сделки, pipeline — потенциальные сделки, а взвешенный прогноз — оценка ожидаемой части pipeline. На дашборде эти четыре числа должны стоять отдельно. Для регулярного сравнения цели и результата полезен отдельный план-факт анализ. Подробнее — в руководстве по план-факт анализу в Excel.
6. Менеджеры, источники и причины потерь
Разрез по менеджерам должен показывать не только выручку. Добавьте входящие лиды, количество оплат, общую конверсию, средний чек, длительность сделки и сумму открытого pipeline. Тогда видно, почему результаты различаются: одному сотруднику достаётся больше лидов, другой лучше конвертирует встречи, третий работает с более крупными клиентами.
Источники анализируйте тем же способом. Реклама может дать 100 лидов и 8 оплат, а партнёрский канал — 30 лидов и 9 оплат. По числу обращений реклама выглядит сильнее, но по конечной конверсии партнёрский источник эффективнее. Если известны рекламные расходы, следующий уровень анализа — стоимость лида и стоимость оплаченного клиента.
Причины потерь лучше выбирать из короткого справочника: дорого, нет бюджета сейчас, выбрали конкурента, не подошёл продукт, не вышел на связь, дубль/нецелевой лид. Свободный текст оставьте для комментария. Иначе появятся десятки вариантов одной причины, которые невозможно нормально агрегировать.
7. Как контролировать скорость воронки, а не только конверсию
Две одинаковые по конверсии воронки могут сильно отличаться по качеству. Если одна закрывает сделку за 12 дней, а другая за 45, бизнес получает деньги с разной скоростью. Поэтому храните дату создания, дату перехода или хотя бы дату последнего изменения этапа и дату закрытия.
Возраст открытой сделки можно считать как =СЕГОДНЯ()-B2, если B2 — дата создания. Для просроченного следующего действия используйте условие =И(J2<>"";J2<СЕГОДНЯ();K2<>"Оплачена"). Такие строки удобно выделять условным форматированием. Это превращает отчёт из пассивной статистики в инструмент ежедневного управления.
Для руководителя полезны три контрольных показателя: медианная или средняя длительность сделки, количество сделок без следующего действия и сумма зависшего pipeline старше выбранного порога. Они часто обнаруживают проблему раньше, чем месячная выручка.
8. Типичные ошибки и контрольные проверки
Первая ошибка — считать этапы вручную отдельными цифрами. После первого же изменения статуса итог расходится с журналом. Вторая — хранить несколько сделок в одной строке клиента. Одна строка должна соответствовать одной возможности продажи с собственным ID. Третья — менять названия этапов вручную: «Предложение», «КП», «Коммерческое» становятся тремя разными категориями.
Четвёртая ошибка — сравнивать менеджеров без учёта периода и объёма выборки. Конверсия 50% на четырёх лидах не обязательно лучше 25% на двухстах. Пятая — смешивать созданные в месяце сделки с оплатами месяца. Для когортного анализа используйте дату создания, для финансового факта — дату оплаты.
Добавьте проверки: количество уникальных ID должно совпадать с числом строк сделок; сумма оплат на дашборде — с суммой оплаченных строк журнала; конверсия не должна превышать 100% при последовательной модели этапов; открытая сделка должна иметь ответственного и следующее действие. Перед управленческой встречей полезно сверять эти четыре условия.
9. Как собрать дашборд и внедрить воронку в работу
На верхней части дашборда достаточно шести KPI: новые лиды, оплаты, общая конверсия, выручка, открытый pipeline и взвешенный прогноз. Ниже разместите таблицу переходов по этапам, сравнение менеджеров, источники и причины потерь. Диаграмма должна помогать заметить отклонение, а не заменять исходные числа.
Обновляйте журнал ежедневно, а разбор воронки проводите раз в неделю. На встрече обсуждайте не каждую строку подряд, а отклонения: этап с просевшей конверсией, крупные зависшие сделки, просроченные действия, изменение качества источников и прогноз относительно плана. В конце месяца фиксируйте итоговые коэффициенты — они пригодятся для реалистичного плана следующего периода.
Если не хочется вручную собирать структуру и формулы, используйте инструмент SmartXLSX для воронки продаж. Для соседних задач пригодятся материалы о плане продаж, KPI менеджеров и анализе продаж: вместе они связывают цель, ежедневный процесс и итоговый результат. См. также план продаж, KPI менеджеров и анализ продаж.
Частые вопросы
Как посчитать конверсию воронки в Excel?
Разделите число сделок, дошедших до следующего этапа, на число сделок предыдущего этапа. Например, 96 встреч из 160 контактов дают 60%. Для защиты от деления на ноль используйте ЕСЛИОШИБКА.
Какие листы нужны для воронки продаж?
Практичный минимум: Сделки, Справочники, Воронка, Менеджеры и Дашборд. Исходные данные лучше не смешивать с ручными итогами и диаграммами.
Как прогнозировать выручку по открытым сделкам?
Назначьте этапам вероятности закрытия и умножайте сумму каждой сделки на вероятность её текущего этапа. Сумма этих значений даст взвешенный прогноз, который нужно показывать отдельно от факта и плана.
Нужно ли учитывать проигранные сделки?
Да. Сохраняйте результат, дату закрытия и стандартизированную причину отказа. Иначе вы увидите место потери, но не поймёте её причину.
Как сравнивать менеджеров корректно?
Смотрите одновременно объём лидов, оплаты, конверсию, средний чек, pipeline и длительность сделки на сопоставимом периоде. Один процент без размера выборки может вводить в заблуждение.
Можно ли вести воронку без CRM?
Да, для небольшого отдела Excel может быть достаточен, если есть единый журнал, справочники, уникальные ID и дисциплина обновления. При большом числе пользователей и сложных автоматизациях специализированная CRM обычно удобнее.