Горизонтальная диаграмма бабочка в Excel скачать шаблон
Горизонтальная диаграмма бабочки в Excel не менее часто используется в анализе и визуализации данных чем вертикальная. Чтобы сделать из вертикальной горизонтальную бабочку или наоборот недостаточно повернуть график с помощью инструмента «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Тип Диаграмм». Имеются некоторые особенности, которые детально рассмотрим ниже.
Как сделать горизонтальную диаграмму бабочку в Excel
Чтобы создать пример построения перевернутой диаграммы бабочки (butterfly chart) в Excel сначала нужно подготовить исходные данные.
Подготовка исходных данных
Смоделируем следующую ситуацию для примера. Допустим из ERP системы был выгружен отчет в виде таблицы по закупкам продажам товаров в штуках для 7-ми магазинов сети торгового предприятия за зимний период времени с декабрь 2019-го по февраль 2020-го года. Всего заполнено 521-а строка листа. Исходная таблица отчета закупок и продаж находятся на листе Excel с именем «Данные»:
Необходимо визуально отобразить соотношение закупок и продаж по данной статистики движении товаров через эти 7 магазинов на протяжении все зимы с 2019-2020 год. Диаграмма вертикальная бабочка прекрасно подходит для сравнительного визуального анализа статистических данных большого объема. Но в стандартных типах диаграмм нет «бабочки». Сконструируем свою «бабочку» из предустановленных в Excel разработчиками, базовых типов: графиков, диаграмм и дополнительных инструментов рисования фигурами.
Так как требуемый период времени для выборки значений из базы данных статистики измеряется в месяцах, в первую очередь преобразуем даты в месяцы. Для этого создадим на этом же листе дополнительный столбец с функцией =МЕСЯЦ():
На этом листе больше никаких изменений выполнять не будем, но будем ссылаться на него из внешних формул.
Создаем еще 2 листа в этой же рабочей книге Excel:
- Главный лист с именем «График» на котором будет представлена интерактивная визуализация данных.
- Дополнительный лист «Обработка» - здесь будут предварительно обрабатывается и подготавливаться данные перед выводом на горизонтальную диаграмму бабочку.
Создание интерактивных элементов управления диаграммой в Excel
И сейчас мы вынуждены забегать немного вперед – сразу в первую очередь создаем на листе «График» элементы управления диаграммой, которой еще не. Для этого выберите инструмент из меню: «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить»-«Переключатель (элемент управления формой)»:
Двойным щелчком по тексту редактируем изменяя его на «Декабрь 2019». А щелкнув по переключателю правой кнопкой мышки вызываем из опции контекстного меню «Формат объекта» окно «Формат элемента управления». В нем же на вкладке «Элемент управления» в поле ввода «Связь с ячейкой:» указываем адрес ссылки на ячейку =Обработка!A10.
Копируем этот переключатель 2 раза и редактируем текст в созданных его копиях «Январь 2020» и «Февраль 2020». Если все сделано правильно, то при переключении между каждым переключателем в ячейке A10 на листе «Обработка» будут возвращены значения 1,2 и 3. В зависимости от того какой включен переключатель первый, второй или третий. Для удобного расположения элементов управления на листе поместим все переключатели в новый элемент: «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить»-«Группа». После чего выделим все элементы и сгруппируем в одну целую группу:
С настройкой и созданием элементов управления – закончили.
Обработка исходных данных для визуализации показателей в Excel
Теперь переходим на лист «Обработка» и создаем в нем таблицу, которую еще нужно заполнить формулами:
Как видно на рисунке в ячейке C10 находится формула =ВЫБОР(A10;12;1;2), которая преобразует значение в ячейке A10 возвращенное переключателями, в порядковый номер месяца: декабрь-12, январь – 1 и февраль – 3. Это позволит нам обращаться к значениям последнего столбца исходной таблицы отчета на листе «Данные».
Дальше составим формулу которая будет делать выборку из нашей базы данных статистики движения товаров в штуках по нескольким условиям:
- Выбрать по магазинам.
- Выборка по показателям количества закупок.
- Выборка по показателям количества продаж.
- При условии, что выборка учитывает также определенный месяц.
В результате формула сделала выборку показателей закупок и продаж по всем магазинам за декабрь (12-й) месяц.
Следующий столбец мы заполняем без формул, а одинаковыми значениями – числом 80. Данный столбец будет использован для среднего ряда горизонтальной диаграммы бабочки. Поэтому он будет фиксированный и его значения будут неизменяемыми константами, определяющими только лишь его размер (а точнее высоту).
Сейчас удачный момент чтобы описать структуру горизонтального графика бабочки. Диаграмма будет состоять из 3-х видимых рядов данных и одного невидимого ряда. В нижнем видимом ряду будут показатели закупок, а в верхнем ряде – показатели продаж. Между ними средний выше описанный ряд. Под нижним рядом, будет еще самый нижний невидимый ряд, который послужит фундаментом, на котором красиво расположатся все остальные ряды. Чтобы все значения среднего ряда были на одном уровне нам нужно установить ширину коридора значений. Коридор значений будет одинаковым числом для сумм значений нижнего видимого и самого нижнего невидимого ряда (фундамента). Значения этих двух рядов чаще всего будут не равными долями, но если их сложить, то все они должны составлять одно и тоже число равное установленному коридору значений. Какое же это будет число? В идеале оно должно быть равно максимальной сумме количества закупок одного из магазинов за 1 месяц. Но мы поступим проще.
Снова мы вынуждены забегать немного вперед и резко переходим к заполнению последнего столбца из одной заполненной ячейки:
Разделив всю общую сумму количества закупок в таблице данных на 3 месяца, мы установили приблизительный коридор знаний. А вычитаемое число – 200 позволяет нам вручную регулировать на какой высоте должен находиться график. То есть им мы управляем положения радов на графике относительно оси Y. Так нам не придется нагружать лишними сложными формулами выборки с множеством условий. А результат получается еще лучше, чем идеальный вариант, так как он управляется пользователем.
Горизонтальная диаграмма бабочка из вертикальной гистограммы
Теперь, когда у нас установлен коридор значений и у нас имеются значения нижнего видимого (красного) ряда, мы вычисляем значения для самого нижнего невидимого ряда (фундамента), таким образом, чтобы сумма этих двух рядов была равна коридору (в данном примере 235,83). Делаем это с помощью простого вычитания. От коридора вычитаем значение видимого ряда и получаем высоту невидимого фундамента:
Если все сделано правильно, тогда все суммы чисел в столбцах B(Закупка) и E(отступ) в каждой строке должны быть равны числу коридора значений указанном в столбце F(отступ + Закупка).
Все данные подготовлены пора делать горизонтальную диаграмму бабочку.
Выделите диапазон ячеек A1:E8 и выберите инструмент: «ВСТАВКА»-«Диаграммы»-«Гистограмма с накоплением»:
Как видно на рисунке пока что нети и малейшего намека на диаграмму типа «бабочка». А теперь обратите внимание на легенду. Главная причина неправильный порядок расположения рядов данных на гистограмме. Исправим этот недостаток выбрав инструмент из дополнительного меню: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Выбрать данные»:
В появившемся окне «Выбор источника данных» в левом разделе «Элементы легенды (ряды):» используя кнопки «Вверх» и «Вниз» настраиваем порядок расположения рядов как показано на рисунке выше.
Если не обращать внимание на первый нижний ряд «отступ» (фундамент), то уже можно заметить, что мы находимся близко у цели. Сделаем самый нижний ряд – невидимым. Выделите его одним кликом правой кнопкой мышки, из появившегося контекстного меню выберите опцию «Формат ряда данных»:
В параметрах ряда «ЗАЛИВКА» отмечаем опцию «Нет заливки».
Далее убираем все лишнее на гистограмме. Щелкаем на кнопку плюс «+» с боку из выпадающего меню снимаем все галочки опций кроме «Легенда»:
Лишние элементы легенды удаляем поочередно кликая и выделяя их, а потом жмем клавишу DELETE на клавиатуре. Кроме того, правой кнопкой вызываем «Формат ряда данных» чтобы уменьшить в параметрах ряда ширину бокового зазора до 30%.
Оформление дизайна диаграммы фигурами Excel
Теперь чтобы красиво оформить диаграмму бабочку воспользуемся фигурами. Выберите инструмент чтобы нарисовать 3 фигуры: «ВСТАВКА»-«Иллюстрации»-«Фигуры»-«Блок-схема: задержка» и здесь же «Пятиугольник»:
Теперь берем просто копируем эти фигуры CTRL+C и вставляем в соответствующие ряды прямо на график CTRL+V:
Полезный совет! При желании можно добавить фигурам или рядам тень, выбрав инструмент: «ФОРМАТ»-«Стили фигур»-«Эффекты фигур»-«Тень».
Теперь остались лишь финальные штрихи – метки подписей на рядах. Поочередно выделяйте каждый ряд и нажав на кнопку плюс «+» отмечайте галочкой опцию «Подписи данных»:
После чего кликаем по подписям правой кнопкой мышки и выбираем опцию «Формат подписей данных» чтобы задать им свой формат отображения как показано выше на рисунке. Цвет и размер шрифта также можно установить используя инструменты на вкладке: «ГЛАВНАЯ»-«Шрифт».
Копируем диаграмму бабочку на главный лист «График» и на нем же создаем информативную табличку, которая подгружает в свои ячейки значения из таблицы на листе «Обработка». Отчет готов к визуальному анализу данных:
Скачать шаблон горизонтальной диаграммы бабочки в Excel
Данный тип диаграмм встречается достаточно часто. Например, графическое представление звука визуально представлено также в виде бабочки. Но и в аналитических инструментах это весьма эффективный способ визуализации данных для сравнительных анализов.
Читайте также: Вертикальная диаграмма бабочка в Excel с процентными.
Здесь описано как сделать вертикальную диаграмму бабочку для таблицы с процентными значениями показателей.
