Как сделать продаж excel
Чтобы оперативно мониторить и своевременно влиять на динамику реализации продукции, коммерческой службе требуются аналитические отчеты, в которых раскрываются различные аспекты процесса реализации.
Большинство современных учетных программ имеет встроенные наборы аналитических отчетов о продажах, но все они формируют показатели только по заданным параметрам отбора. Для ввода новых показателей нужно привлекать программистов.
Если пользователям такой отчетности требуется часто менять структуру отчетов о продажах или создавать новые отчеты, то для самостоятельного решения подобных задач вполне подойдет всем знакомый табличный редактор Excel.
ИНСТРУМЕНТАРИЙ EXCEL ДЛЯ СОЗДАНИЯ АНАЛИТИЧЕСКИХ ОТЧЕТОВ
В табличном редакторе Excel предусмотрен широкий выбор инструментов, с помощью которых можно создать аналитические отчеты на основе данных о реализации продукции. Для успешной работы с этими инструментами от пользователя требуется определенный уровень подготовки. Представим перечень инструментария для создания аналитических отчетов:
- продвинутый уровень — макросы, Power BI;
- хороший уровень — OLAP-кубы, Power Query/Pivot;
- средний уровень — сводные таблицы, формулы.
Рассмотрим особенности применения каждого из указанных инструментов, а также знания и навыки пользователя, которые нужны для их качественного применения.
Макросы
Работа с макросами основана на применении языка программирования VBA, который можно использовать для расширения возможностей MS Excel и других приложений MS Office. С помощью прописанных в макросе команд можно:
- проводить различные обработки и сортировки данных в файле Excel;
- получать информацию из других файлов;
- создавать сводные таблицы;
- добавлять в создаваемые отчеты дополнительные функции, которые невозможно получить обычными средствами Excel.
Чтобы создавать макросы, пользователь должен отлично знать редактор Excel, владеть языком программирования VBA. Приведу в качестве примера запись макроса, с помощью которого в файле Excel автоматически из массива данных формируется сводная таблица:
Очевидно, что работать с макросами может незначительная часть сотрудников, которые создают отчетность в Excel.
Power BI
Power BI по своей сути является отдельным программным продуктом, в который можно загрузить файлы Excel и произвести дальнейшую обработку с целью анализа и визуализации данных.
Power BI включает в себя весь функционал надстроек Excel (Power Query и Power Pivot плюс улучшенные механизмы визуализации из Power View и Power Map). Преимущества данного инструмента: с отчетами может работать сразу несколько пользователей плюс широкий диапазон визуализации показателей отчетов.
Идет тренд к интеграции Excel c Power BI. Например, в Excel 2019 появилась возможность напрямую загружать данные в функционал Power BI. Для этого в меню выбираем:
Файл > Опубликовать > Опубликовать в Power BI.
Передав файл, нажимаем кнопку «Перейти к Power BI», чтобы просмотреть загруженные данные.
Главные сложности использования Power BI: загруженные таблицы Excel нужно дополнительно обрабатывать для корректного включения их данных в отчеты, а формулы для создания отчетов в этой программе отличаются от формул Excel.
Power BI постоянно развивается, однако на сегодняшний момент использовать его для формирования аналитических отчетов достаточно трудоемко.
OLAP-кубы
OLAP (online analytical processing) — аналитическая технология обработки данных в реальном времени, при которой данные из учетной базы выгружаются в файлы Excel, а затем обрабатываются с помощью другого инструмента Excel (сводных таблиц).
Для начала работы нужно создать подключение файла Excel к данным OLAP-куба (Данные → Получение внешних данных), а затем из открывшегося окна перетащить курсором в табличную часть Excel показатели, которые требуются.
В результате будет получена сводная таблица с отчетными данными. Главное ее преимущество — возможность автоматической актуализации данных при каждом подключении к OLAP-кубу.
Power Query/Pivot
Данные инструменты являются надстройками Excel, поэтому работа с ними происходит непосредственно из меню табличного редактора.
Power Query появился в версии Excel 2013 как отдельная надстройка, требующая подключения, а с версии 2016 г. весь функционал Power Query уже встроен по умолчанию и находится на вкладке «Данные → Получить и преобразовать».
Power Query обладает значительными возможностями для целей создания отчетов. С помощью этой надстройки можно:
- загружать данные в Excel из почти 40 различных источников, среди которых базы данных (SQL, Oracle, Access, Teradata), корпоративные ERP-системы (SAP, Microsoft Dynamics, 1C), интернет-сервисы;
- собирать данные из файлов всех основных типов данных (XLSX, TXT, HTML, XM) — поодиночке и сразу из всех файлов указанной папки;
- зачищать полученные данные от лишних пробелов, столбцов или строк, повторов, служебной информации в заголовках, непечатаемых символов и т. д;
- трансформировать таблицы Excel, приводя их в желаемый вид (фильтровать, сортировать, менять порядок столбцов, транспонировать, добавлять итоги, разворачивать кросс-таблицы в плоские и сворачивать обратно);
- подставлять данные из одной таблицы в другую по совпадению одного или нескольких параметров (полностью заменяет формулу ВПР и ее аналоги).
Главная особенность Power Query: все действия по импорту и трансформации данных запоминаются в виде запроса — последовательности шагов на внутреннем языке программирования Power Query, который лаконично называется «М».
Шаги можно отредактировать, воспроизвести любое количество раз (обновить запрос). Поэтому данный инструмент может служить хорошей альтернативой создания макросов или прописания очень сложных формул при построении отчетов.
Power Pivot — надстройка Excel, предназначенная для разнопланового анализа больших объемов данных. Поэтому результат работы с Power Pivot похож на усложненные сводные таблицы.
Общие принципы работы в Power Pivot:
- внешние данные загружают в Power Pivot, который поддерживает 15 различных источников: распространенные базы данных (SQL, Oracle, Access), файлы Excel, текстовые файлы, веб-каналы данных. Если Power Query использовать как источник данных, то возможности загрузки увеличиваются многократно;
- между загруженными таблицами настраиваются связи, то есть создается Модель Данных. Это позволит строить отчеты по любым полям из имеющихся таблиц так, будто это одна таблица;
- при необходимости в Модель Данных добавляют дополнительные вычисления с помощью вычисляемых столбцов (аналог столбца с формулами в «умной» таблице) и мер (аналог вычисляемого поля в сводной таблице). Нужные вычисления записываются на специальном внутреннем языке Power Pivot, который называется DAX (Data Analysis Expressions);
- на листе Excel по Модели Данных строят интересующие отчеты в виде сводных таблиц и диаграмм.
Сводные таблицы
Первый интерфейс сводных таблиц (сводных отчетов) был включен в состав Excel в 1993 г. (в версии Excel 5.0). Этот инструмент изначально создавался для построения отчетов на основе многомерных данных. Он имеет достаточно широкие функциональные возможности.
Реализованный в Excel инструмент сводных таблиц позволяет расположить измерения многомерных данных в области рабочего листа. Упрощенно можно представлять себе сводную таблицу как отчет, лежащий сверху диапазона ячеек (хотя есть определенная привязка форматов ячеек к полям сводной таблицы).
Сводная таблица Excel имеет четыре области отображения информации: фильтр, столбцы, строки и данные. Измерения данных именуются полями сводной таблицы. Эти поля имеют собственные свойства и формат отображения.
С помощью сводных таблиц можно группировать, сортировать, фильтровать и менять расположение данных с целью получения различных аналитических выборок.
Обновление отчета производится простыми средствами пользовательского интерфейса. Данные автоматически агрегируются по заданным правилам. Не требуется дополнительный или повторный ввод какой-либо информации.
Сводные таблицы Excel являются самым востребованным инструментом при работе с многомерными данными в больших объемах информации. Этот инструмент поддерживает в качестве источника данных как внешние источники данных, так и внутренние диапазоны электронных таблиц.
Для работы со сводными таблицами не нужны знания в области программирования VBA или внутренних языков программирования надстроек Excel.
Формулы Excel
Механизм формул появился в первой версии табличного редактора. С тех пор он значительно расширился. На сегодняшний день функционал формул содержит больше сотни наименований. С учетом того что при создании отчетов формулы могут комбинироваться, количество вариантов трудно подсчитать.
Формулы отлично подходят для создания двухмерных отчетов при обработке небольшого объема данных. Преимущество формул в том, что их легко копировать или транспонировать на другие ячейки отчетов, переделать или защитить от изменений.
В редакторе Excel есть встроенный справочник по формулам, что облегчает работу пользователям со средним уровнем владения Excel. Поэтому я предлагаю рассмотреть возможности использования функционала формул при разработке аналитических отчетов из одного источника данных.
ВОЗМОЖНОСТИ ИСПОЛЬЗОВАНИЯ ФОРМУЛ ДЛЯ РАЗРАБОТКИ АНАЛИТИКИ ПРОДАЖ В EXCEL
Вне зависимости от выбора инструментария Excel при разработке аналитических отчетов о реализации продукции в первую очередь создают новую книгу и загружают в нее исходные данные из учетной программы компании для последующей их обработки.
Удобнее всего сделать это путем формирования в учетной программе реестра продаж с нужными показателями и сохранения его в виде файла формата Excel. Далее отчетность будем создавать на отдельных листах этого файла.
Возьмем самые востребованные данные о продажах, на основе которых создаются аналитические отчеты:
- наименование покупателя;
- наименование продукции;
- дата отгрузки продукции покупателю;
- регион реализации продукции;
- сумма реализации продукции;
- валовая прибыль от реализации продукции;
- маржа (процентное соотношение валовой прибыли к сумме реализации).
А. А. Гребенников, главный экономист ГК «Резон»
Материал публикуется частично. Полностью его можно прочитать в журнале «Планово-экономический отдел» № 10, 2020.
Прогнозирование продаж в Excel не сложно составить при наличии всех необходимых финансовых показателей.
В данном примере будем использовать линейный тренд для составления прогноза по продажам на бушующие периоды с учетом сезонности.
Линейный тренд хорошо подходит для формирования плана по продажам для развивающегося предприятия.
Excel – это лучший в мире универсальный аналитический инструмент, который позволяет не только обрабатывать статистические данные, но и составлять прогнозы с высокой точностью. Для того чтобы оценить некоторые возможности Excel в области прогнозирования продаж, разберем практический пример.
Пример прогнозирования продаж в Excel
Рассчитаем прогноз по продажам с учетом роста и сезонности. Проанализируем продажи за 12 месяцев предыдущего года и построим прогноз на 3 месяца следующего года с помощью линейного тренда. Каждый месяц это для нашего прогноза 1 период (y).
Уравнение линейного тренда:
- y — объемы продаж;
- x — номер периода;
- a — точка пересечения с осью y на графике (минимальный порог);
- b — увеличение последующих значений временного ряда.
Допустим у нас имеются следующие статистические данные по продажам за прошлый год.
- Рассчитаем значение линейного тренда. Определим коэффициенты уравнения y = bx + a . В ячейке D15 Используем функцию ЛИНЕЙН:
- Выделяем ячейку с формулой D15 и соседнюю, правую, ячейку E15 так чтобы активной оставалась D15. Нажимаем кнопку F2. Затем Ctrl + Shift + Enter (чтобы ввести массив функций для обеих ячеек). Таким образом получаем сразу 2 значения коефициентов для (a) и (b).
- Рассчитаем для каждого периода у-значение линейного тренда. Для этого в известное уравнение подставим рассчитанные коэффициенты (х – номер периода).
- Чтобы определить коэффициенты сезонности, сначала найдем отклонение фактических данных от значений тренда («продажи за год» / «линейный тренд»).
- Рассчитаем средние продажи за год. С помощью формулы СРЗНАЧ.
- Определим индекс сезонности для каждого месяца (отношение продаж месяца к средней величине). Фактически нужно каждый объем продаж за месяц разделить на средний объем продаж за год.
- В ячейке H2 найдем общий индекс сезонности через функцию: =СРЗНАЧ(G2:G13).
- Спрогнозируем продажи, учитывая рост объема и сезонность. На 3 месяца вперед. Продлеваем номера периодов временного ряда на 3 значения в столбце I:
- Рассчитаем значения тренда для будущих периодов: изменим в уравнении линейной функции значение х. Для этого можно просто скопировать формулу из D2 в J2, J3, J4.
- На основе полученных данных составляем прогноз по продажам на следующие 3 месяца (следующего года) с учетом сезонности:
Общая картина составленного прогноза выглядит следующим образом:
График прогноза продаж:
Алгоритм анализа временного ряда и прогнозирования
Алгоритм анализа временного ряда для прогнозирования продаж в Excel можно построить в три шага:
- Выделяем трендовую составляющую, используя функцию регрессии.
- Определяем сезонную составляющую в виде коэффициентов.
- Вычисляем прогнозные значения на определенный период.
Нужно понимать, что точный прогноз возможен только при индивидуализации модели прогнозирования. Ведь разные временные ряды имеют разные характеристики.
Чтобы посмотреть общую картину с графиками выше описанного прогноза рекомендуем скачать данный пример:
Большинство маркетологов работают с воронкой продаж в CRM-системе. Но эти встроенные дашборды и отчеты подходят не каждому бизнесу. Для кого-то они неинформативны и не отвечают на множество важных вопросов. Как выполняется план продаж? Кто из менеджеров эффективнее на каждом этапе воронки? Насколько один продукт продается лучше другого? Об этом (и не только) воронка в CRM может рассказать не всегда.
Чтобы получить больше информации для анализа, руководителю отдела продаж приходится вручную выгружать данные из CRM в Excel-таблицу, надеясь потом визуализировать воронку продаж. Это не всегда получается хорошо, потому что некоторые функции в Excel и приемы работы с ним не очевидны.
Объясню, как это сделать правильно, затратив наименьшее количество сил и времени. Иллюстрировать визуализацию буду на примере конкретной воронки, в которой 5 этапов. На каждом этапе, как обычно и бывает, количество клиентов уменьшается.
Как построить воронку продаж в Excel 2019
В Excel-2019 построить воронку очень легко, поэтому много рассказывать о вставке диаграммы не буду: на скриншотах ниже все проиллюстрировано. Инструкция проста: выделяем таблицу с исходными данными → «Вставка» → «Рекомендованные диаграммы» → «Воронка».
Останется только откорректировать и подстроить под себя внешний вид. Для этого нажимаем на диаграмму и переходим в блок «Работа с диаграммами» в меню: там можно настроить цвет диаграммы, цвет ее фона и другие элементы оформления.
Как сделать воронку
в Excel 2016, 2013, 2010
В более старых версиях процесс немного сложнее, но все равно возможен: делать это нужно с помощью линейчатых диаграмм. По сути, диаграмма «Воронка» представляет собой ту же линейчатую диаграмму, только выровненную по центру. В прошлых версиях Excel добиться такого выравнивания одним кликом нельзя. Но если приложить немного усилий, все получится.
В первую очередь в таблицу с данными нужно добавить еще один столбец с названием «Подушка»: значения в этом столбце как раз и помогут превратить линейчатую диаграмму в воронку. Значение подушки в каждой строке таблицы необходимо просчитать заранее.
Как просчитать подушку для воронки в Excel?
В каждой строке нового столбца задаем формулу: =($C$19-C20)/2, где C — это столбец, в котором указано количество клиентов на каждом этапе воронки, а 19 и 20 — номера строк с нужными показателями. Формула считает разницу между наибольшим числом клиентов и значением в активной ячейке, а получившееся делит на два.
Выделяем три столбца получившихся данных и вставляем из меню линейчатую диаграмму. Не ждите, что уже на этом этапе Excel выдаст идеальную воронку — это только начало! Подушка в этой диаграмме будет выполнять роль вспомогательных рядов, которые подвинут нужные столбики так, чтобы диаграмма приобрела форму воронки.
Дальше работаем с диаграммой: превращаем ее в воронку и настраиваем внешний вид.
• Меняем сортировку рядов (функция «Обратный порядок категорий»).
• Меняем порядок рядов, чтобы подушка шла первой, в левой части диаграммы.
• Делаем эту подушку бесцветной, то есть убираем предложенную заливку.
• Сокращаем боковой зазор, чтобы между этапами воронки не оставалось дыр.
• Убираем лишние подписи внизу таблицы и легенду.
• Включаем метки данных на диаграмме, чтобы видеть количество клиентов на каждом этапе. Если надо, настраиваем их цвет и размер.
На этом все — получившаяся воронка визуально и функционально не отличается от той, что в новой версии Excel .
Воронка в сводной таблице
Если работаешь с простой маленькой табличкой, вставить диаграмму легко. Однако при визуализации данных или подготовке презентации чаще приходится работать со сводными таблицами. И тут уже сложнее.
Excel не позволяет легко и быстро построить воронку на данных сводной таблицы. Тем не менее, это можно сделать с помощью некоторых хитростей и лайфхаков. Именно ими пользуется моя команда при создании дашбордов. И именно им я обучаю на онлайн-курсах «Обработка данных в Excel» и «Дашборд в Excel».
С помощью некоторых знаний можно не просто построить воронку на основе сводной таблицы, но и создать полноценный интерактивный дашборд на этих данных. То есть сделать так, чтобы наша информационная панель откликалась на действия: нажимаем на имя конкретного менеджера, дашборд сразу же фильтрует данные и выводит нам показатели именно этого менеджера.
Зачем тогда нужны BI-платформы?
Да, в Excel действительно можно построить вполне симпатичный интерактивный дашборд. Но BI-платформа предлагает для этого больше возможностей и к тому же избавляет от дополнительных действий, без которых в Excel не обойтись. На примере той же воронки продаж покажу, как дашборд с ней выглядит и работает в Power BI.
Во-первых, данные в Power BI легко загружаются из любых онлайн-источников, в том числе и из CRM-системы. Уже это можно считать серьезным преимуществом платформы перед Excel, в который данные приходится выгружать вручную.
Во-вторых, воронка в Excel показывает нам только количество сделок на каждом этапе. В то время как воронка в Power BI может рассчитать еще и конверсию на каждом этапе.
Успех бизнеса напрямую зависит от тщательного и своевременного контроля ключевых показателей. Для отслеживания метрик компании идеально подходят дашборды. Как приборная панель автомобиля, они позволяют увидеть самые важные результаты в одном месте. Появляется возможность следить за операционной деятельностью, находить в ней проблемные зоны и получать инсайты на основе реальных измерений. Главное, нет необходимости устанавливать дополнительные программы: в этой статье — инструкция для «чайников», как сделать дашборд в Excel.
Вся нужная информация на такой интерактивной панели подана в виде графиков и диаграмм: это помогает легко воспринимать ее и быстро получать ответы на главные вопросы бизнеса с помощью объективных показателей.
В качестве целей этого инструмента можно выделить следующие:
• объединение информации из разных источников;
• создание единой структурированной системы метрик;
• интерактивное представление результатов.
Кроме того, он позволяет более подробно изучить показатели без погружения в дебри аналитических выгрузок и не требует специальных навыков и знаний от своих пользователей. То есть, если сказать простыми словами, дашборд – это (в Эксель в том числе) продвинутый вариант отчета, который содержит ключевые данные в понятном и наглядном виде.
Как это помогает бизнесу
Создание дашборда требует определенных усилий и времени. Но они с лихвой окупаются, потому что у такого формата подачи данных есть неоспоримые достоинства.
Удобный мониторинг изменений в режиме онлайн — еще одно важное достоинство интерактивных сводок для любой компании. Своевременное создание дашборда в Эксель позволит, например, вовремя заметить падение продаж по сравнению с прошлым месяцем и оперативно принять меры. Или увидеть, по каким проектам план не выполняется, какие менеджеры лучше справляются с определенным этапом в ведении сделок и так далее. Все это становится реальным благодаря сжатию большого объема информации до одной страницы.
Ольга Базалева, руководитель аналитического отдела в РБК, создатель онлайн-школы Excellent, автор книги «Мастерство визуализации данных» и автор блога, поделилась с Нетологией советами, как делать более удобные и понятные коллегам отчёты в Excel.
Сегодня не обойтись без умения работать с информацией: аналитикам, финансистам, маркетологам без этого совсем никуда, но даже в гуманитарных специальностях навык обрабатывать информацию всё больше и больше ценится. Взять хотя бы журналистику данных, которая набирает популярность.
Хотите, чтобы руководитель вас заметил и в глазах коллег вы стали классным профессионалом? Научитесь делать качественные отчёты.
ольга базалева
Создатель онлайн-школы визуализации данных Excellent
курс
Excel
Узнать больше
- Научитесь работать с любыми отчётами в Excel
- Разберётесь, как работать с формулами
- Сможете сортировать, анализировать и визуализировать данные
Встаньте на место начальника. Кому вы готовы платить больше? Специалисту, который присылает ему данные в таком виде?
1. Помните о наглядности
- Располагайте данные логично: обычно это от большего к меньшему.
- Всегда разделяйте разряды в больших числах: 10 000 000, а не 10000000.
- Убирайте лишние знаки после запятой.
- Убирайте яркую сетку, это визуальный мусор. Делайте линии светлыми, а иногда можно обойтись вовсе без сетки.
- Дизайн таблиц должен быть лаконичным и не отвлекать от сути.
- Выделяйте шапку и строки/столбцы с итогами.
- Выделяйте отрицательные значения красным, а второстепенные значения — серым. В Excel это можно сделать автоматически с помощью условного форматирования.
- Визуализируйте данные в таблицах. Можно строить гистограммы непосредственно в ячейках при помощи условного форматирования и создавать миниграфики, которые располагаются в одной ячейке (спарклайны).
Подборка курсов, бесплатных лекций и материалов по Excel
2. Используйте диаграммы
Визуально представленная информация в сравнении с обычным текстом и таблицами воспринимается быстрее и запоминается легче. Графики и диаграммы позволяют сразу увидеть тренды, взаимосвязи, всплески и падения.
3. Пишите выводы в сопроводительном письме
Если руководитель получил ответ на свой вопрос сразу из письма, ему даже не надо тратить время на открытие приложенного файла. Поверьте, это дорогого стоит.
Читать также
4. Делайте отчёты удобными
Если ваш отчёт большой и занимает 10−20 листов в Excel, сделайте навигацию по нему. Это можно реализовать одним из следующих способов:
- создать лист Summary, показать на нём главные цифры и сделать ссылки «Подробнее» с переходом на листы, содержащие все данные;
- сделать оглавление, куда добавить ссылки на каждый лист с аналитикой.
Читать также
5. Автоматизируйте обновление регулярных отчётов
Когда ваши отчёты станут обновляться в считаные минуты, высвободится масса дополнительного времени. Это время можно использовать для своего профессионального развития, а можно взять на себя дополнительную функциональность, что позволит обоснованно попросить повышения.
В Excel есть множество инструментов, которые помогут автоматизировать отчёты:
- настройте выгрузку всех необходимых данных на один лист в Excel, назовём этот лист «Source»;
- создайте необходимые аналитические таблицы на других листах;
- формулами в эти аналитические таблицы подтяните нужные данные с листа Source;
- сделайте связку Power Point — Excel. Тогда при изменении диаграмм в Excel, в презентации Power Point они будут обновляться автоматически.
Так вы создадите отчёт один раз и сможете быстро обновлять его в последующем, просто добавляя новые данные на лист Source, а все остальные таблицы и диаграммы тут же обновятся.
Когда ваши отчёты преобразятся, они станут в разы ценнее. Заказчики данных будут быстро получать ответы на свои вопросы, а набор сухих таблиц превратится в удобные и наглядные аналитические отчёты. Специалистов, которые умеют обрабатывать информацию и помогают принимать бизнес-решения ценят гораздо больше, чем тех, кто занимается простым заполнением непонятных таблиц.
Читайте также: