Как сделать воронку продаж в excel

Воронка продаж в Excel – это инструмент, который позволяет визуализировать путь клиента от первого контакта до покупки с точностью до 90%. В отличие от CRM-систем, Excel не требует дополнительных затрат и доступен любому специалисту. Средняя конверсия между этапами воронки составляет 10–30%, но без анализа данных эти цифры остаются абстрактными. В этой статье разберем, как построить воронку с нуля, используя только базовые функции Excel.

Первый шаг – определение ключевых этапов. Стандартная воронка включает: лидогенерация (100%) → квалификация (60%) → презентация (40%) → предложение (25%) → закрытие (15%). Эти значения варьируются в зависимости от ниши: например, в B2B конверсия из лида в сделку редко превышает 5%, а в e-commerce может достигать 20%. Для точного расчета используйте формулу =Количество_на_этапе/Количество_на_предыдущем_этапе*100 в отдельном столбце.

Для визуализации данных создайте таблицу с тремя колонками: Этап, Количество, Конверсия (%). Заполните ее реальными цифрами за последний квартал. Затем постройте диаграмму типа воронка (доступна в Excel 2016 и новее) или используйте гистограмму с накоплением для более ранних версий. Убедитесь, что на графике отображаются не только абсолютные значения, но и проценты перехода – это ключевой индикатор узких мест.

Анализ воронки начинается с выявления этапа с наибольшим падением конверсии. Например, если из 100 лидов только 10 доходят до презентации, проблема может быть в качестве лидов или процессе квалификации. Для проверки гипотез используйте сводные таблицы: сравните источники трафика, демографию клиентов или время обработки заявок. В Excel это делается через Вставка → Сводная таблица с группировкой по нужным параметрам.

Автоматизируйте расчеты с помощью формул. Для динамического обновления конверсии используйте =ЕСЛИОШИБКА(ВПР(…);0), чтобы избежать ошибок при отсутствии данных. Добавьте условное форматирование: выделите красным этапы с конверсией ниже 10% и зеленым – выше 30%. Это позволит мгновенно выявлять проблемные зоны без ручного анализа.

Определение ключевых этапов воронки для вашего бизнеса

Первый шаг – анализ целевой аудитории. Разделите клиентов на сегменты по поведению: посетители сайта, подписчики email-рассылки, те, кто добавил товар в корзину, но не оплатил. Для e-commerce стандартные этапы – «Просмотр», «Добавление в корзину», «Оформление заказа», «Оплата». В B2B добавьте «Запрос демо», «Переговоры», «Заключение контракта». Используйте данные CRM или Google Analytics: если 70% посетителей уходят после просмотра карточки товара, этап «Просмотр» требует оптимизации.

Для SaaS-продуктов воронка часто включает этапы: «Регистрация на бесплатный пробный период», «Активация ключевых функций», «Переход на платный тариф». Отслеживайте метрику Time-to-Value (TTV) – среднее время от регистрации до первого значимого действия пользователя. Если TTV превышает 3 дня, упростите онбординг: добавьте чек-листы, подсказки или видеоинструкции. Пример: Dropbox сократил TTV на 40%, внедрив интерактивное руководство.

В офлайн-бизнесах (рестораны, салоны) этапы выглядят иначе: «Первое посещение», «Повторное посещение», «Покупка абонемента». Используйте программы лояльности для отслеживания: если 60% клиентов не возвращаются после первого визита, введите welcome-скидку 15% на второе посещение. Для фитнес-клубов добавьте этап «Посещение пробного занятия» – конверсия из него в платные абонементы должна быть не ниже 30%.

Не копируйте чужие воронки без адаптации. Если вы продаете сложное оборудование, добавьте этапы «Запрос коммерческого предложения» и «Техническая консультация». Для стартапов с низким трафиком объедините этапы: вместо «Просмотр» и «Добавление в корзину» используйте «Взаимодействие с продуктом» (клики, прокрутка, время на странице). Проверяйте гипотезы: если конверсия из «Заявки» в «Оплату» ниже 10%, тестируйте разные форматы форм (многошаговые vs. одностраничные).

Ключевые метрики для каждого этапа: коэффициент конверсии (CR), среднее время прохождения, стоимость привлечения (CAC). Для этапа «Подписка на рассылку» CR должен быть 5–15% (зависит от ниши), для «Оплаты» – 1–5%. Если CR на этапе «Добавление в корзину» ниже 3%, оптимизируйте UX: уменьшите количество полей в форме, добавьте гарантии возврата. Используйте формулу: CR = (Количество переходов на следующий этап / Количество на текущем этапе) × 100%.

Обновляйте воронку каждые 3 месяца. Исключите этапы с CR ниже 0,5% – они не влияют на продажи, но усложняют аналитику. Для автоматизации используйте Excel-функции: VLOOKUP для сопоставления данных из разных источников, PivotTables для визуализации конверсий. Пример: если воронка выглядит как «Посетители → Подписчики → Клиенты», а CR из подписчиков в клиенты 2%, добавьте этап «Участие в вебинаре» – он может повысить конверсию до 8%.

Настройка структуры таблицы Excel для сбора данных

Первый шаг – определить ключевые метрики воронки. Создайте столбцы для этапов: «Посетители сайта», «Добавлено в корзину», «Оформление заказа», «Оплачено», «Возврат». Каждый этап должен иметь отдельный столбец с уникальным названием, например, «Дата_этапа» или «ID_клиента». Используйте формат даты «ДД.ММ.ГГГГ» для временных меток, чтобы избежать ошибок при сортировке.

Добавьте столбец «Источник трафика» с выпадающим списком: «Поиск», «Соцсети», «Email», «Реклама». Это позволит сегментировать данные без ручного ввода. Для создания списка выделите ячейки, перейдите в «Данные» → «Проверка данных» → «Список» и укажите значения через запятую.

Для отслеживания уникальных пользователей используйте столбец «Client_ID» с уникальными идентификаторами. Если данные поступают из CRM, экспортируйте их с сохранением ID. В Excel примените функцию =СЦЕПИТЬ() для генерации ID из имени и даты, если готовых данных нет: =СЦЕПИТЬ(ЛЕВСИМВ(A2;3);ДЕНЬ(B2);МЕСЯЦ(B2)).

Разделите количественные и качественные данные. В столбцах с числами (например, «Сумма заказа») установите формат «Числовой» с двумя знаками после запятой. Для текстовых данных («Комментарий менеджера») используйте формат «Общий». Это ускорит фильтрацию и анализ.

Добавьте служебные столбцы для расчетов: «Конверсия_этапа» и «Средний_чек». Формула для конверсии: =ЕСЛИОШИБКА(СЧЁТЕСЛИ(D:D;»>0″)/СЧЁТЕСЛИ(A:A;»>0″);0). Для среднего чека: =СУММ(E:E)/СЧЁТЕСЛИ(E:E;»>0″). Зафиксируйте диапазоны с помощью $ (например, $E$2:$E$1000), чтобы формулы не смещались при добавлении строк.

Используйте условное форматирование для визуализации отклонений. Выделите столбец «Конверсия_этапа», перейдите в «Главная» → «Условное форматирование» → «Цветовые шкалы» → «Красный-Желтый-Зеленый». Это поможет мгновенно выявлять проблемные этапы. Для критических значений (например, конверсия ниже 5%) настройте правило с заливкой ячейки красным.

Защитите структуру таблицы от случайных изменений. Выделите все ячейки с формулами, щелкните правой кнопкой → «Формат ячеек» → «Защита» → поставьте галочку «Защищаемая ячейка». Затем перейдите в «Рецензирование» → «Защитить лист» и установите пароль. Оставьте редактируемыми только столбцы для ручного ввода данных.

Импорт и обработка исходных данных о клиентах и сделках

Приведите данные к единому формату: даты – к типу «Дата», суммы – к числовому формату без символов валюты. Создайте вспомогательный столбец для расчёта времени на каждом этапе с помощью формулы =РАЗНДАТ(начальная_дата; конечная_дата; "d"). Для анализа динамики добавьте столбец с месяцем или неделей обращения, используя МЕСЯЦ() или НЕДЕЛЯ(). Проверьте данные на аномалии: отрицательные суммы, даты из будущего, пустые значения в ключевых полях – исправьте их вручную или с помощью фильтров.

Расчет конверсии между этапами с помощью формул

Конверсия между этапами воронки продаж показывает эффективность перехода лидов с одного уровня на другой. Для расчета используйте формулу: (Количество лидов на следующем этапе / Количество лидов на текущем этапе) * 100. Например, если на этапе «Первичный контакт» было 500 лидов, а на «Демонстрация продукта» перешло 120, конверсия составит 24%. Этот показатель критичен для выявления слабых мест.

В Excel формулу удобно реализовать через относительные ссылки. В ячейке для конверсии между этапами введите =B2/A2*100, где A2 – текущий этап, B2 – следующий. Протяните формулу вниз для автоматического расчета по всем этапам. Используйте форматирование процентов (Ctrl+Shift+5) для наглядности.

Для анализа динамики добавьте столбец с разницей конверсии между периодами. Формула: =C2-D2, где C2 – текущий месяц, D2 – предыдущий. Отрицательные значения сигнализируют о падении эффективности. Пример:

При работе с несколькими каналами привлечения используйте сводные таблицы. Выделите диапазон с данными, перейдите на вкладку «Вставка» → «Сводная таблица». В строках укажите этапы, в столбцах – каналы, в значениях – среднюю конверсию. Это позволит сравнить эффективность источников.

Для визуализации трендов постройте график с двумя осями: ось X – этапы воронки, ось Y – количество лидов и конверсия. Выделите данные, выберите «Вставка» → «График» → «Комбинированный». Настройте вторичную ось для процентов. Падение конверсии на 5% и более требует немедленного анализа причин.

Автоматизируйте расчеты с помощью именованных диапазонов. Выделите столбец с лидами на этапе, перейдите в «Формулы» → «Определить имя», задайте имя (например, «ПервичныйКонтакт»). В формуле конверсии используйте =Демонстрация/ПервичныйКонтакт*100. Это упростит обновление данных при изменении структуры таблицы.

Для глубокого анализа добавьте формулу расчета кумулятивной конверсии: =ПРОИЗВЕД(1+C2:E2)-1, где C2:E2 – диапазон конверсий по этапам. Она покажет общую эффективность воронки от первого контакта до продажи. Например, при конверсиях 30%, 20% и 10% кумулятивный показатель составит 5.6% – именно столько лидов дойдут до конца.

Визуализация воронки продаж через диаграммы Excel

Воронка продаж в Excel визуализируется через диаграмму «Воронка» (Funnel Chart), доступную в версиях 2019 и новее. Для её создания подготовьте таблицу с двумя столбцами: «Этап» (например, «Посетители», «Лиды», «Квалифицированные лиды», «Предложения», «Сделки») и «Количество» (числовые значения). Выделите данные, перейдите на вкладку «Вставка» → «Диаграммы» → «Воронка». Excel автоматически построит график, где ширина каждого сегмента пропорциональна значению этапа. Для наглядности добавьте подписи данных: щёлкните правой кнопкой по диаграмме → «Добавить подписи данных» → «Добавить подписи данных».

Если у вас старая версия Excel (до 2019), используйте гистограмму с накоплением или линейчатую диаграмму. Создайте вспомогательный столбец с обратным порядком этапов (от «Сделок» к «Посетителям») и отрицательными значениями. Выделите данные, вставьте гистограмму с накоплением, затем измените её тип на «Линейчатая с накоплением». Удалите легенду, добавьте подписи осей и отформатируйте цвета сегментов: верхний этап – зелёный (#5CB85C), нижний – красный (#D9534F), промежуточные – оттенки серого (#777777).

• Настройте ось Y: щёлкните по ней правой кнопкой → «Формат оси» → установите «Обратный порядок категорий».
• Удалите линии сетки: выделите их → клавиша Delete.
• Добавьте проценты конверсии: в соседнем столбце рассчитайте долю каждого этапа от начального (например, =B2/B$2), затем вставьте подписи данных с этими значениями.

Для анализа динамики используйте комбинированную диаграмму: добавьте второй ряд данных с показателями за предыдущий период. Выделите таблицу, вставьте «Комбинированную диаграмму» (гистограмма + график). Настройте вторичную ось для второго ряда, чтобы сравнить тренды. Пример формулы для расчёта изменения: =(Текущий_период-Предыдущий_период)/Предыдущий_период*100. Выделите ключевые точки цветом: положительные изменения – зелёным, отрицательные – красным.

Автоматизация обновления данных с помощью Power Query

Power Query – инструмент, который сокращает ручную работу по обновлению воронки продаж на 70–90%. В отличие от формул и макросов, он не требует знания VBA или сложных выражений DAX. Достаточно один раз настроить запрос, и Excel будет автоматически подтягивать актуальные данные из CRM, Google Sheets или SQL-базы при каждом открытии файла.

Начните с импорта исходных данных. Перейдите на вкладку Данные → Получить данные → выберите источник (например, Из таблицы/диапазона для Excel-файла или Из веб для API). Если данные хранятся в облаке (Google Sheets, SharePoint), используйте соответствующий коннектор. Для корпоративных систем (Salesforce, HubSpot) установите сторонние надстройки, например, CData Power Query Connector.

Очистка данных в Power Query экономит часы ручной правки. Удалите дубликаты (Главная → Удалить строки → Удалить дубликаты), замените пустые значения на нули (Замена значений), разделите столбцы с датами на день/месяц/год (Разделить столбец → По разделителю). Для числовых полей (например, сумма сделки) преобразуйте текст в формат Десятичное число (Тип данных → Десятичное число).

• Для динамических источников (например, выгрузки из 1С) настройте параметры запроса. Создайте параметр ДатаНачала и ДатаОкончания (Главная → Управление параметрами), затем используйте их в фильтрах запроса. Это позволит обновлять данные за нужный период без ручного редактирования.
• Если данные поступают из нескольких таблиц, объедините их с помощью Объединить запросы (аналог SQL-джойнов). Например, таблицу с лидами и таблицу с закрытыми сделками можно соединить по ID клиента, чтобы получить полную историю взаимодействий.
• Для больших объемов данных (от 100 000 строк) включите опцию Загрузка в модель данных при импорте. Это ускорит обработку и снизит нагрузку на файл.

Настройте автоматическое обновление при открытии файла. Перейдите в Запросы и подключения (Данные → Запросы и подключения), выберите нужный запрос, щелкните правой кнопкой мыши → Свойства. Включите опцию Обновлять при открытии файла. Для облачных источников (Google Sheets, SQL) добавьте расписание обновления (Обновлять каждые N минут).

Обработайте ошибки заранее. Если источник данных изменит структуру (например, добавится новый столбец), Power Query выдаст ошибку. Чтобы избежать сбоев, используйте функцию try…otherwise в расширенном редакторе. Пример для столбца «Сумма сделки»:

try [Сумма сделки] otherwise 0

Это заменит отсутствующие значения на нули вместо ошибок.

Создайте единый источник истины. Если воронка строится на основе нескольких файлов (например, отдельные таблицы для лидов, сделок и платежей), объедините их в один запрос с помощью Добавить запросы. Затем разделите итоговую таблицу на этапы воронки с помощью Столбец с условием (Добавить столбец → Условный столбец). Пример условия для этапа «Квалификация»:

if [Статус] = "Квалифицирован" then "Квалификация" else null

Для продвинутых сценариев используйте функции Power Query. Например, чтобы динамически рассчитывать конверсию между этапами, создайте пользовательскую функцию. В расширенном редакторе напишите:

(ПредыдущийЭтап as number, ТекущийЭтап as number) as number =>
Number.Round((ТекущийЭтап / ПредыдущийЭтап) * 100, 2)

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

Анализ узких мест и корректировка стратегии по результатам

Первый шаг – выявить этапы воронки с аномально низкой конверсией. Например, если переход с лендинга на страницу оплаты составляет 3%, а средний показатель по отрасли – 8–12%, проблема кроется в UX/UI или ценностном предложении. Проверьте время загрузки страницы (должно быть <2 сек), количество полей в форме (оптимально 3–5) и наличие социальных доказательств (отзывы, кейсы). Используйте инструменты Google Analytics или Hotjar для записи сессий пользователей: 68% отказов происходят из-за неочевидного CTA или отсутствия мобильной адаптации.

Сравните данные по сегментам. Если конверсия среди пользователей из email-рассылок в 2 раза выше, чем из платной рекламы, перераспределите бюджет. Анализируйте стоимость лида (CPL) и стоимость клиента (CAC) по каналам: например, при CPL в 500₽ из таргета и 200₽ из SEO, увеличьте долю органического трафика. Исключите каналы с CAC выше среднего чека – они нерентабельны. Для точечной оптимизации используйте A/B-тесты: изменение цвета кнопки может повысить конверсию на 21%, а добавление видео на лендинг – на 80%.

Корректируйте стратегию на основе воронки обратной связи. Если 40% клиентов бросают корзину на этапе доставки, протестируйте бесплатную доставку при заказе от 3000₽ или предложите альтернативные способы оплаты (например, рассрочку). Для B2B-сегмента внедрите автоворонку с напоминаниями: первое письмо через 1 час после отказа, второе – через 24 часа с бонусом (скидка 5% или бесплатная консультация). Отслеживайте LTV (Lifetime Value) – если он ниже 3x CAC, работайте над удержанием: программы лояльности, персонализированные предложения.

Автоматизируйте анализ с помощью Excel. Создайте сводную таблицу с формулой =КОНВЕРТ(этап_ниже;этап_выше) для расчета конверсии между этапами. Добавьте условное форматирование: красный цвет для показателей ниже 5%, желтый – 5–10%, зеленый – выше 10%. Еженедельно обновляйте данные и выделяйте тренды: падение конверсии на 15% за месяц сигнализирует о системной проблеме (например, сбои в CRM или изменение алгоритмов рекламных платформ). Для прогнозирования используйте линейную регрессию: =ТЕНДЕНЦИЯ(известные_значения;известные_аргументы;новые_аргументы) поможет спрогнозировать продажи на следующий квартал.

Вопрос-ответ: