Воронка продаж – инструмент аналитики и оптимизации бизнес-процесса. Концепция описывает и демонстрирует психологию потребителя. Не все люди, которые увидят рекламу продукта, станут реальными клиентами. Часть потенциальных покупателей вовсе не заинтересуется товаром. Часть – уйдет к конкурентам. Часть – не устроит цена.

Как рассчитать число потребителей продукта, чтобы бизнес приносил прибыль? Как правильно собирать статистические данные о продажах? Контролировать все эти процессы позволяет воронка.

Функциональные возможности инструмента

1. Осмысленный контроль над процессом перехода потенциального покупателя к сделке.
2. Визуализация конверсии по каждому этапу. Видно, как заполняется воронка продаж, – можно проанализировать действенность канала продвижения.
3. Можно прогнозировать, как заполняться этапы через месяц, два, три… На основе прогнозов составляется план объема продаж по каждому менеджеру, по предприятию в целом.
4. Воронка продаж позволяет анализировать эффективность работы каждого менеджера, отдела продаж.

Этапы воронки

Их количество и наполнение зависят от вида бизнеса. Графически воронка продаж выглядит как перевернутая пирамида. Символичная демонстрация «входа-выхода» клиентов: не все заинтересованные в продукте люди дойдут до покупки. Часть потенциальных покупателей откажется от сделки на том или ином этапе.

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

В общем виде воронка включает следующие секции:

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

Данные берутся из результатов статистических исследований, отчетов отдела продаж, менеджеров, БД.

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

Готовых инструментов для создания воронки в Excel не существует. Тем не менее, ее можно сделать.

Для начала сделаем табличку со статистическими данными:

Следующий шаг – посчитаем конверсию на каждом из этапов. Для этого поделим значение текущей секции на значение предыдущей и в ячейке с итогом выставим процентный формат:

Далее переходим на вкладку «Вставка» — рисунки SmartArt. Из предложенных вариантов самым очевидным является «Пирамида».

Появилась стандартная пирамида с тремя элементами.

Но в нашем примере 4 секции. Поэтому добавим еще один элемент. Ставим курсор в область текста – жмем Enter.

Можно ввести любой текст – на данном этапе это не имеет значения. Автоматически в пирамиде появляется еще один элемент.

Предварительный макет готов. Можно поиграть с цветами, стилями. Например, сделать пирамиду трехмерной.

На следующем этапе объединим значения ячеек в столбце А (названия секций) со значениями ячеек в столбце В (количественное выражение секций. Сделаем это с помощью символа амперсант.

Чтобы подставить эти данные в макет, нужно преобразовать объект в формате SmartArt в обычную фигуру. Без этого шага мы не сможем добиться автоматического обновления фигуры при внесении изменений в таблицу.

Для этого нужно на вкладке «Конструктор» нажать кнопку «Преобразовать фигуры». Теперь по очереди щелкаем на каждую ячейку – щелкаем по строке формул – делаем ссылку на объединенную ячейку. Получаем такую простенькую воронку:

Рассмотрим, как посчитать воронку продаж в Excel другим способом.

Для примера возьмем другую таблицу значений:

Используя функцию ПОВТОР, создадим следующую фигуру:

Все значения – это 100%. Функция поставила столько заданных элементов, сколько составляет доля каждого значения в общем объеме значений.

Придадим схеме привычный вид воронки:

Такого эффекта можно добиться с помощью форматирования шрифта и выравнивания. Вот заданные параметры:

Диаграмма «Воронка продаж» в Excel

Используем ту же таблицу с данными. Выделяем диапазон А2:В7. Переходим на вкладку «Вставка». В группе «Диаграммы» выбираем «Объемную нормированную гистограмму с накоплением».

Щелкаем правой кнопкой мыши по диаграмме. Выбираем «Формат ряда данных». Устанавливаем галочку напротив «Полный цилиндр».

Далее переходим на вкладку «Формат». Находим меню «Текущий фрагмент». Раскрываем список и выбираем «Область диаграммы».

Следующий шаг – открываем «Формат выделенного фрагмента».

Выделяем вертикальную ось значений. Открываем меню «Формат оси».

Ставим галочку «Обратный порядок значений».

Теперь чтобы объединить все конусы в один с накоплением показателей, в меню «Работа с диаграммами» переходим на вкладку «Конструктор» и жмем на инструмент «Строка/столбец», чтобы поменять местами значения осей.

Добавляем подписи данных. Убираем сетку, легенду, оси. Форматируем по своему усмотрению.

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

Воронка продаж в Excel скачать бесплатно

Примечание. Если нужно сделать плоскую диаграмму, тогда на вкладке «Поворот объемной фигуры» выставляем для оси Х и Y нулевые значения.

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

Что такое воронка продаж?

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

Давайте предположим, что вы дизайнер дашбордов в организации по продажам и это ваши данные в Excel.

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

Создание воронки продаж в Excel?

Давайте построим диаграмму, используя данные сверху. Наиболее подходящим вариантом для нас станет пирамидальная диаграмма. Стоит отметить, что с приходом Excel2013, возможность построения Цилиндрической, Конусной и Пирамидальной диаграмм немного изменилась. Теперь, чтобы построить их, необходимо создать объемную гистограмму и изменить фигуру столбцов в окне Формат ряда данных.

1. Выделяем диапазон с данными A2:B6 и переходим по вкладке Вставка в группу Диаграммы -> Рекомендуемые диаграммы. В появившемся окне Вставка диаграммы переходим во вкладку Все диаграммы и выбираем Объемная нормированная гистограмма с накоплением. Жмем ОК
2. Выделяем диаграмму и переходим по вкладке Работа с диаграммами -> Формат в группу Текущий фрагмент. В выпадающем меню выбираем любой ряд данных и щелкаем Формат выделенного. В появившейся справа панели Формат ряда данных во вкладке Параметры ряда ставим маркер напротив Полная пирамида.
3. В правой панели Формат ряда данных, щелкаем по стрелке, находящейся справа от поля Параметры ряда. Из выпадающего меню выбираем Область диаграммы.
4. В панели Формат области диаграммы переходим во вкладку Эффекты -> Поворот объемной фигуры. И выставляем значения Вращение вокруг оси X и Y по нулям.
5. Еще раз вызываем выпадающее меню, нажатием по треугольнику рядом с полем Параметры диаграммы и выбираем Вертикальная ось (значений). В панели Формат оси во вкладке Параметры оси ставим галочку напротив Обратный порядок значений.
6. Переходим по вкладке Работа с диаграммами -> Конструктор в группу Макеты диаграмм и щелкаем по Добавить элемент диаграммы. В выпадающем меню выбираем Подписи данных -> Выноска данных. В правой панели щелкаем по треугольнику рядом с полем Параметры диаграммы и выбираем Ряд «Поиск заказчиков» Подписи данных. Во вкладке Параметры подписей ставим галочки напротив Имя ряда и Значение.
7. Убираем все не нужные элементы: Сетку, Оси, Название диаграммы, Легенду. Форматируем ее на свой лад. Наша воронка продаж готова.

Скачать файл с примером воронки продаж

В этой статье мы поговорим о том, как создать диаграмму «Воронка продаж» в Excel. Такой график можно создать за 5 простых шагов из нескольких линейчатых диаграмм с накоплением.

Что такое диаграмма-воронка?

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

Зачем используют диаграмму «Воронка продаж»?

График-воронка даёт читателю наглядную картинку о стадиях процесса. В нашем случае мы продаем трубопроводы (см. рисунок ниже). На Этапе 1 (Stage 1), где происходит первый контакт с потенциальным клиентом (Initial Contact), мы имеем самое большое число клиентов, но лишь небольшая доля этих самых клиентов доходит до Этапа 5 (Stage 5), на котором происходит покупка.

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

Строим воронку продаж за 5 простых шагов

График-воронка может быть создан за 5 простых шагов из линейчатой диаграммы с накоплением.

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

Шаг 1: Подготавливаем данные и создаем линейчатую диаграмму с накоплением

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

Формула промежуточного ряда: Эта формула находит максимальное значение в указанном диапазоне, вычитает из него значение текущей строки и делит результат на 2.

=(МАКС($D$7:$D$11)-D8)/2
=(MAX($D$7:$D$11)-D8)/2

Когда данные будут готовы, выделяем их и создаём Линейчатую диаграмму с накоплением (Stacked Bar Chart).

Шаг 2: Устанавливаем обратный порядок категорий

Первоначально при создании диаграммы категории будут расположены в порядке обратном тому, как они перечислены на листе. Чтобы выстроить данные в обратном порядке:

1. Кликните правой кнопкой мыши по вертикальной оси (на которой перечислены имена категорий) и выберите Формат оси (Format Axis).
2. В разделе Параметры оси (Axis Options) отметьте галочкой опцию Обратный порядок категорий (Categories in reverse order).

Теперь категории располагаются в том же порядке, как они перечислены на листе.

Шаг 3: Устанавливаем боковой зазор равным 0%

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

1. Кликните правой кнопкой мыши по любой из полос диаграммы и выберите Формат ряда данных (Format Data Series).
2. В разделе Параметры ряда (Series options) установите Боковой зазор (Gap Width) равным 0%.

Шаг 4: Убираем заливку промежуточного ряда

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

1. Кликните правой кнопкой мыши по любой полосе промежуточного ряда и выберите Формат ряда данных (Format Data Series).
2. В разделе Заливка (Fill) выберите Нет заливки (No fill).

Шаг 5: Подготавливаем диаграмму к презентации

Теперь можно настроить вид диаграммы и убрать всё лишнее. Excel добавляет множество элементов (сетку, легенду и прочие), которые в данном случае не нужны.

Вот что я изменил в настройках внешнего вида моей диаграммы:

1. Удалил легенду
2. Удалил вертикальные линии сетки
3. Удалил линию вертикальной оси
4. Удалил горизонтальную ось
5. Изменил цвет заливки и контура полос воронки
6. Изменил заголовок диаграммы
7. Добавил подписи данных и выровнял их по центру.

Где еще можно использовать воронку продаж?

В приведённом выше примере использованы результаты подсчёта клиентов на разных этапах продажи трубопровода. Кроме этого, график-воронку можно использовать чтобы показать:

• Число учащихся на каждом уровне обучения (студент, выпускник, кандидат наук и так далее)
• Число сотрудников на каждом уровне организации (подчинённый, менеджер, управляющий и так далее)
• Размер дебиторской задолженности по срокам давности (30, 60, 90 дней)
• Возрастное распределение клиентов / населения (20, 30, 40 лет и так далее)

Какие же ещё существуют области применения для диаграммы «Воронка продаж«? Пожалуйста, задавайте любые вопросы и оставляйте предложения ниже в комментариях. Спасибо!

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: /> Перевел: Антон Андронов

Правила перепечаткиЕще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение:

Наверняка Вы много раз видели такой инструмент как воронка продаж и интересовались как сделать воронку продаж в Excel.
Мы рассмотрим несколько способов построения воронки в зависимости от конкретных целей.

Способ 1. Фиксированная высота секции.

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

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

Построение вспомогательной таблицы

Вспомогательный ряд («Ряд 1» в примере) необходим для выравнивания секций воронки продаж по центру:

Расшифровка формул вспомогательной таблицы

Выделяем диапазон данных и строим линейчатую диаграмму с накоплением. Для этого переходим на панель вкладок и выбираем Вставка -> Диаграмма -> Линейчатая -> Линейчатая с накоплением:

Создание линейчатой диаграммы с накоплением

Удаляем легенду диаграммы и делаем невидимым вспомогательный ряд данных «Ряд 1» — нажимаем правой кнопкой мыши на ряд и выбираем Формат ряда данных -> Заливка -> Нет заливки:

Скрытие ряда данных диаграммы

Меняем на вертикальной оси в параметрах порядок категорий на обратный — нажимаем правой кнопкой мыши на вертикальную ось и выбираем Формат оси -> Параметры оси -> Обратный порядок категорий:

Форматирование осей диаграммы

Убираем зазор ряда данных — нажимаем правой кнопкой мыши на ряд диаграммы и выбираем Формат ряда данных -> Параметры ряда -> Боковой зазор и устанавливаем зазор равным 0%, и аналогично добавляем подпись данных (Формат ряда данных -> Добавить подпись данных):

Удаление зазора и добавление подписей данных

Форматируем воронку продаж в зависимости от предпочтений и получаем окончательный вариант:

Окончательный вид воронки продаж (способ 1)

Способ 2. Плавающая высота секции.

Выделяем диапазон данных и строим пирамидальную гистограмму с накоплением. Для этого переходим в панели вкладок на Вставка -> Диаграмма -> Гистограмма -> Пирамидальная с накоплением:

Создание пирамидальной гистограммы с накоплением

Нажимаем правой кнопкой мыши на диаграмму и выбираем Выбрать данные, меняем строки и столбцы местами:

Изменение значений строк и столбцов местами

Сделаем из объемной диаграммы плоскую, для этого нажимаем правой кнопкой мыши на диаграмму и выбираем Формат области диаграммы -> Поворот объемной фигуры и выставляем значения углов поворота на ноль:

Преобразование объемной диаграммы в плоскую

Меняем на вертикальной оси в параметрах порядок категорий на обратный — нажимаем правой кнопкой мыши на вертикальную ось и выбираем Формат оси -> Параметры оси -> Обратный порядок категорий:

Форматирование осей диаграммы

Далее форматируем воронку продаж на свое усмотрение (добавляем подписи данных, меняем цвета слоев и т.д.):

Окончательный вид воронки продаж (способ 2)

Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!

Воронка продаж в excel

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

Постановка задачи построения воронки продаж в excel

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

Исходные данные для воронки продаж

Для примера возьмем самые простые данные по уровням воронки продаж.

Этап продаж	Кол-во (ед)	Сумма (руб.)
Переговоров	150	7 800
Обращений	500	26 000
Контактов	322	16 744
Счетов	75	3 900
Оплат	25	1 300
Повторных заказов	10	590

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

Если на основе колонки «количество» построить линейчатую диаграмму, то мы получим не совсем то, что хотели.

Воронка продаж — вариант «по умолчанию».

Для того, чтобы привести диаграмму к привычному виду придется немного постараться. Пример далее приведен для столбца «количество». Алгоритм построения воронки продаж по сумме будет аналогичным. Пример сделан  в программе Excel 2010, для других версий программ используете аналогичные инструменты.

Алгоритм построения воронки продаж в Excel

1. Формируем два столбца значений. Для этого используем формулы: -а и а, где а – значение ряда данных. Это необходимо, чтобы получить симметричные диаграммы. Формально «размах» диаграммы будет больше значения в два раза, но для «картинки» это не имеет значения, тем более, что реальные данные воронки продаж мы позже отобразим на диаграмме.
2. Строим линейчатую диаграмму по двум колонкам значений. Результат будет примерно такой:

   Воронка продаж — исходная диаграмма.

3. Для каждого из рядов поочередно изменяем «формат ряда данных» – устанавливаем: «Перекрытие рядов» – «С перекрытием» — 100% ; «Боковой зазор» – «без зазора» — 0%; «Заливка» — «Сплошная заливка», «Цвет заливки» – на ваш выбор.
4. Переходим на вкладку «Макет».
   1. Убираем легенду – значение «Нет»
   2. Выделяем ряд положительных значений. Устанавливаем «подписи данных» — «у основания, внутри»
   3. Основная горизонтальная ось – значение «Нет»
5. Выделяем ось с названиями этапов. Устанавливаем «Подписи оси» — «Внизу» (если хотите разместить названия уровней воронки продаж справа), или «Вверху» (если названия уровней воронки продаж должны быть слева). Ставим отметку в чекбоксе «Обратный порядок категорий».
6. Вставляем название диаграммы.
7. Форматируем шрифт подписи значений.

Воронка продаж готова.

Воронка продаж в excel — итоговый вариант

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

Оформление воронки продаж на ваше усмотрение.

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

Буду благодарен, если поделитесь своими комментариями.

Поделиться

• 26
• 3
•  
•  
•  29Поделились