График сравнения объема продаж с прошлым месяцем в Excel

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

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

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

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

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

И так перейдем непосредственно к веселой визуализации данных на для скучной статистической информации в отчете. Из программы ERP-системы были выгруженные продажи за отчетный период последние 2 полных месяца. Таблица отчета, экспортированная в файл Excel и представляет собой следующий вид исходных данных:

Выполним визуализацию этих исходных данных для визуального анализа в 3 простых шага:

1. Подготовка данных.
2. Обработка данных для построения графика.
3. Интерактивная визуализация данных с элементами управления.

Подготовка данных о продажах перед сравнением с предыдущим периодом в Excel

Для подготовки данных заполним первый столбец в диапазоне ячеек A2:A62 необычной формулой, которая сгенерирует нам ID-коды для упрощенной выборки значений из исходной таблицы:

Данные коды ID будут использоваться в формуле выборки значений для функции ВПР с несколькими условиями. Каждый код из трех цифр следует расшифровывать так:

1. Первое число — это порядковый номер дня месяца.
2. Второе число – это номер недели в текущем месяце.
3. Третье число – это порядковый номер месяца в году.

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

С подготовкой данных мы закончили и переходим к их обработке.

Обработка данных для визуального сравнения объемов продаж на графике в Excel

Создайте новый лист в Excel с именем «Обработка» и разместите на нем следующую таблицу, которую мы должны заполнить формулами:

Как видно выше на рисунке, пока только две ячейки таблицы B1 и B2 содержат формулы:

1. ="Прошлый месяц неделя №"&D2 – формула в ячейке B1.
2. ="Текущий месяц неделя №"&D2 – формула в ячейке B2.

Теперь заполните диапазон пустых ячеек B2:C7 в таблице обработки одной большой формулой для выборки исходных значений из листа «Данные» функцией ВПР по условиям:

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

С обработкой закончили – переходим к визуализации данных.

Визуализация данных по сравнению продаж с предыдущим периодом в Excel

Создайте новый лист с именем «График» и сделайте в нем таблицу:

Как видно выше на рисунке на этот раз на этапе создания таблица содержит 3 формулы, а точнее внешние ссылки на лист «Обработка» в ячейках: B2, B4и C4.

Диапазон ячеек таблицы B5:C10 заполняем следующей формулой:

Теперь создаем элемент управления графиком и таблицами – полоса прокрутка с помощью, которой мы будем прокручивать номера анализируемых неделей. Для этого выберите инструмент: «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить»-«Полоса прокрутки». Далее рисуем горизонтальную полосу прокрутки размером с диапазон двух ячеек B3:C3:

После чего кликаем по элементу управления правой кнопкой мышки и из появившегося контекстного меню выберите опцию: «Формат объекта». В появившемся окне «Форматирования объекта» на вкладке параметров «Элемент управления» вносим следующие изменения в 3 поля ввода:

1. Минимальное значение: 1.
2. Максимальное значение: 5 (так как в одном месяце 5 неполных недель).
3. Связь с ячейкой: Обработка!$D$2 – в эту ячейку полоса прокрутки будет передавать числовые значения от 1-го и до 5-ти (то есть порядковые номера недель в месяцах).

В результате мы теперь одним кликом мышки изменяем все значения в ячейках таблицы. Чтобы убедиться, что все сделано правильно – протестируйте.

Построение графика для технического сравнительного анализа продаж в Excel

Измените значение в ячейке B2 на листе «График» на число 2 используя элемент управления графиком – полосу прокрутки. После чего выделите диапазон ячеек A4:C10 и выберите инструмент: «ВСТАВКА»-«Диаграммы»-«График»

Далее кликаем правой кнопкой мышки по оранжевой линии графика и из контекстного меню выбираем опцию «Формат ряда данных» чтобы внести свои настройки: «ПАРАМЕТРЫ РЯДА»-«Заливка»-«ЛИНИЯ»-«Сплошная линия»-«Ширина»– 1,5 пунктов и здесь же «Тип штриха» – круглые точки:

Далее кликаем на кнопку плюс «+» расположенную рядом с графиком и из выпадающего меню снимаем галочки с опций «Название диаграммы», «Сетка», а затем отмечаем галочкой опцию «Полосы повышения и понижения»:

Теперь красиво оформим полосы с помощью фигур красных (понижение) и зеленых (повышения) стрелок. Но сначала их нужно нарисовать выбрав инструмент: «ВСТАВКА»-«Иллюстрации»-«Фигуры»-«Фигуры стрелок»-«Стрелка вверх»:

С помощью опции «Формат фигуры» в контекстным меню вызванного кликом правой кнопкой мышки по фигуре «стрелка вверх» мы изменяем ее цвет: «Формат фигуры»-«Параметры фигуры»-«ЗАЛИВКА»-«Градиентная заливка»-«Точки градиента», для второй точки просто «Цвет»-зеленый, а для первой точки градиента также зеленый цвет, но с прозрачностью 60%. Аналогичным образом создаем красную стрелку вниз.

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

1. Сначала скопируйте в буфер обмена Windows зеленую стрелку выделив ее и нажав комбинацию горячих клавиш CTRL+C.
2. Кликните правой кнопкой мышки на любой полосе повышения и выберите инструмент: «Формат полосы повышения и понижения»-«ПАРАМЕТРЫ ПОЛОС»-«ЗАЛИВКА»-«Рисунок или текстура»-«Буфер обмена» и здесь же «ГРАНИЦА»-«Нет линий»:

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

Скачать график сравнения объема продаж в Excel

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