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

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

Построение воронки продаж — достаточно распространенная задача. Стандартное решение появилось только в версии Excel 2016 года. В статье описано несколько способ создания воронки продаж в Excel.

Вариант 1. Использование встроенного графика «Воронка»

Этим способом могут воспользоваться те, у кого установлен Excel 2016 версии (или оформлена подписка Office 365). Также у пользователей вашим графиком должна быть версия не ниже 2016 года. Иначе вместо воронки они увидят пустое пространство. Если есть сомнения — переходите к следующему способу.

Первое, что нужно сделать — подготовить сходные данные. К примеру возьмем продажи квартир, коттеджей.

Данные для построения воронки продаж

Выделим диапазон A3:B7 перейдем на вкладку Вставка -> Рекомендуемые диаграммы -> Воронка. Сохраняем выбор нажав кнопку OK.

Вставка диаграммы Воронка

Воронка продаж готова, осталось по своему вкусу отформатировать ее.

Вариант 2. Использование гистограммы для создания воронки

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

В качестве примера буем использовать те же данные, что и в способе 1. Однако добавим дополнительно столбец Отступ (чуть позже будет понятно для чего).

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

Выберем диапазон A3:C7 и вставим линейчатую диаграмму с накоплением (Вставка -> Все диаграммы).

Вставка линейчатой диаграммы

Перевернем воронку на 180 градусов. Для этого выберите ось Y и кликните по ней правой кнопкой мыши. Выберите команду Формат оси.

Формат оси Y в Excel

В группе Положение оси отметьте пункт Обратный порядок категорий . Кстати ось X нам не понадобится, можно смело выделить ее и удалить. С легендой можно поступить аналогично.

На данном этапе уже прорисовывается воронка, однако, для большей красоты, не хватает отступов от оси Y. Давайте их добавим. Для этого в столбце Отступ «протянем» формулу =(МАКС($C$4:$C$7)-C4)/2.

Теперь сделаем бесцветным отступы. Для этого выделим ряд Отступ (зеленый). Щелчком правой кнопки мыши откроем контекстное меню и выберем Формат ряда данных.

Формат ряда данных Воронка продаж

Воронка готова. Можно дополнительно увеличить ширину этапов воронки и сократить зазор между ними. Откроем окно формата ряда данных со значениями воронки (аналогично формату ряда Отступ). Перейдем в Параметры ряда и уменьшим Боковой зазор (к примеру до 20%)

Создание воронкообразной диаграммы на основе данных Excel

Excel для Microsoft 365 Word для Microsoft 365 Outlook для Microsoft 365 PowerPoint для Microsoft 365 Excel для Microsoft 365 для Mac Word для Microsoft 365 для Mac Outlook для Microsoft 365 для Mac PowerPoint для Microsoft 365 для Mac Excel 2021 Word 2021 Outlook 2021 PowerPoint 2021 Excel 2021 для Mac Word 2021 для Mac Outlook 2021 для Mac PowerPoint 2021 для Mac Excel 2019 Word 2019 Outlook 2019 PowerPoint 2019 Excel 2019 для Mac Word 2019 для Mac Outlook 2019 для Mac PowerPoint 2019 для Mac Excel для iPad Excel для iPhone Excel для планшетов с Android Excel для телефонов с Android Excel Mobile Еще. Меньше

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

Воронкообразная диаграмма, представляющая канал продаж; этапы указаны в первом столбце, значения — во втором

Вставка воронкообразной диаграммы в Excel для Windows

Примечание: Эта функция доступна в Excel для Windows только при наличии Office 2019, Office 2021 или подписки на Microsoft 365. Если вы являетесь подписчиком Microsoft 365, убедитесь, что у вас установлена последняя версия Office.

Вставка воронкообразной диаграммы

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

Вкладки

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

Вставка воронкообразной диаграммы в Excel для Mac

Примечание: Эта функция доступна только в Excel для Mac, если у вас есть Office 2019 или у вас есть подписка на Microsoft 365. Если вы являетесь подписчиком Microsoft 365, убедитесь, что у вас установлена последняя версия Office.

Значок каскадной диаграммы

Настройте данные, как в приведенном выше примере. Используйте один столбец для этапов процесса, а другой — для значений.
Выделите данные.
На ленте перейдите на вкладку Вставка , затем щелкните (значок Каскад) и выберите Пункт Воронка.

Совет: В Excel для Mac используйте вкладки Конструктор диаграммы и Формат, чтобы настроить внешний вид диаграммы. Если эти вкладки не отображаются, щелкните в любом месте воронкообразной диаграммы, и вкладки появятся на ленте.

Вставка воронкообразной диаграммы в Excel Mobile и Excel для Android

Настройте данные, как в приведенном выше примере. Используйте один столбец для этапов процесса, а другой — для значений.
Выделите данные.
Выберите Вставка >Диаграмма >Воронка.

Вставка воронкообразной диаграммы в Outlook, PowerPoint и Word

Примечание: Эта функция доступна в этих приложениях, только если у вас есть Office 2019 или у вас есть подписка На Microsoft 365. Если вы являетесь подписчиком Microsoft 365, убедитесь, что у вас установлена последняя версия Office.

Щелкните пустое место в сообщении электронной почты, презентации или документе.
Выберите Вставка >Диаграмма >Воронка.
Появится воронковая диаграмма. Появится небольшое окно с примерами данных. Измените числа на собственные.

Кнопка

Совет: В верхней части окна данных есть кнопка Изменить данные в Microsoft Excel . Если вам нужно больше места для работы с данными, нажмите эту кнопку.

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

Диаграмма воронка продаж в Excel скачать бесплатно

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

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

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

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

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

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

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

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

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

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

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

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

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

Данные для воронки.

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

Конверсия.

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

Пирамида.

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

Элементы.

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

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

Новый элемент.

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

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

Амперсант.

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

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

Воронка продаж в Excel.

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

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

Этапы.

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

ПОВТОР.

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

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

Тип воронки.

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

Шрифт.

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

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

Диаграмма.

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

Полный цилиндр.

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

Область диаграммы.

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

Формат выделенного фрагмента.

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

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

Обратный порядок.

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

Строка столбец.

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

Пример.

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

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

Excel Formula Examples
Создать таблицу
Форматирование
Функции Excel
Формулы и диапазоны
Фильтр и сортировка
Диаграммы и графики
Сводные таблицы
Печать документов
Базы данных и XML
Возможности Excel
Настройки параметры
Уроки Excel
Макросы VBA
Скачать примеры

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

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

Опубликовано 05.05.2013 Автор Ренат Лотфуллин

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

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

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

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

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

Данные для воронки продаж в Excel

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

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

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

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

Вам также могут быть интересны следующие статьи

Что такое Treemap и как его сделать в Excel
Планки погрешностей в Excel — нестандартное использование
Диаграмма водопад (waterfall chart) в Excel
Создание пулевой диаграммы (bullet graph)
Создание диаграммы в виде спидометра в Excel
Диаграмма Ганта в Excel
Создание графика в Excel с отрицательными и положительными значениями
Создание простейшего дашборда в Visio — импорт данных из Excel в Visio
Бесплатный шаблон дашборда в Excel — потребленная энергетическая ценность продуктов
Создание динамической диаграммы в Excel с помощью именованных диапазонов

Рубрика: Визуализация, Диаграммы | Метки: визуализация, диаграмма | 8 комментариев | Permalink

8 комментариев

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

А как гипертрофировать высоту? На больших значениях сверху и мелких внизу в пирамиде ничего не видно, только первые 2 уровня из 5.

Артем, шаг оси нельзя изменить. Есть несколько способов как выйти из ситуации, один из них описан в статье о создании разорванной оси

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

вдруг у кого возникнут вопросы, я вк https://vk.com/tverdohleb_alex

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