Вертикальная диаграмма бабочка в Excel с процентами скачать

Диаграмма бабочка достаточно часто используется в анализе данных. Например, звуковые дорожки в редакторах музыкальных файлов отображаются на горизонтальной диаграмме бабочки (butterfly chart). Также ее можно встретить вертикальную бабочку в маркетинговых сравнительных анализах, при визуализации показателей бизнес-моделей и т.п. Но по умолчанию в Excel пока нет диаграммы бабочки в предустановленном наборе типов графиков и диаграмм. Но при желании можно собрать такую диаграмму самому из предложенных аналитических инструментов визуализации данных в Excel.

Как сделать вертикальную диаграмму бабочку в Excel

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

Категория А – динамические сотрудники от которых на прямую зависит рост прибыли фирмы. Например: менеджеры по продажам, мерчендайзеры, супервайзеры, продавцы, маркетологи и т.д. У данной категории сотрудников чаще могут быть факторы, влияющие на их ежемесячный оклад – персональные бонусы, премии, но с другой стороны планы продаж, сезонность и т.п.
Категория B – статические сотрудники, с активностью работы которых не коррелируется их доход так как они не могут прямо повлиять на рост продаж своими трудовыми обязанностями. Но без них фирма не сможет функционировать, а при их низких показателях KPI существенно снизиться спад прибыли. К данной категории сотрудников относиться: бухгалтеры, юристы, кладовщики, грузчики, водители, охранники, клининг-менеджеры и др. В данной категории сотрудников ежемесячный оклад практически неизменчив.

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

На листе «График» сделайте таблицу исходных данных с показателями KPI для обеих групп сотрудников по всем 5-ти отделам:

Перед тем как построить строить диаграмму бабочка следует сначала подготовить больше исходных данных. Для построения графика нам понадобиться еще 3 дополнительных столбца:

В первом будут находиться одинаковы значения «50%» - данный столбец будет центральным радом данных на диаграмме. А его значения – это его ширина, доля от остальных рядов.
Второй столбец содержит формулу =100%-B2 для вычисления доли отступа от левого края диаграммы. Поэтому данный столбец будет самым первым рядом данных на графике бабочка.
Третий столбец содержит в свих ячейках формулу =100%-C2 для вычисления доли отступа от правого края. Данный столбец является самым последним рядом данных.

Сразу стоит отметить, что в каждой строке таблице одинаковая сумма чисел и она составляет 250%:

Данное число 250% мы будем использовать в настройках горизонтальной оси X на графике.

Вертикальная диаграмма бабочка из горизонтальной линейчатой гистограммы

Построим прототип диаграммы бабочка выделив диапазон ячеек A2:F6 всей таблицы целиком и выбрав инструмент: «ВСТАВКА»-«Диаграммы»-«Линейчатая с накоплением»:

Сразу же переходим на дополнительное меню и выбираем инструмент: «РАБОТА С ДИАГРАММАМИ»-«Конструктор»-«Данные»-«Выбрать данные» и вносим свои настройки в расположение рядов на графике:

Как показано выше на рисунке сначала нажимаем на кнопку-переключатель «Строка/Столбец», чтобы переключить значения рядов в категории. А потом используя кнопки «Вверх» и «Вниз» располагаем положения текущих рядов в строгом необходимом нам порядке. Соответственно плану где были описаны дополнительные столбцы – «KPI по отделам – ширина» (значения 50%) строго по середине и т.д.

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

В первую очередь выполним настройку горизонтальной оси X. Кликаем правой кнопкой мышки по оси X и из появившегося контекстного меню выбираем опцию «Формат оси»:

Как упоминалось выше сумма всех чисел в каждой строке таблицы равна 250%, поэтому максимальная граница в параметрах оси X составляет 2,5 (тоже самое что и 250% в процентном формате ячеек).

Как видно на рисунке – верхний отдел на графике у нас 5-й, а нужно сделать обратный порядок расположения отделов на оси Y.

Сразу же переходим к настройкам оси Y по таким же порядкам действий, но изменяем другие параметры:

Убираем все лишнее на диаграмме (кроме легенды) используя контекстное меню опций, вызванных кнопкой плюс «+»:

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

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

Стильный дизайн диаграммы из фигур и средств рисования в Excel

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

Для точек градиента следует указать 2 желтых оттенка с кодами цвета:

RGB: 255;192;0.
RGB: 255;230;153.

Копируем фигуру (CTRL+C) выделяем правый ряд и вставляем ее прямо в график (CTRL+V):

Аналогичные действия выполняем создания второй фигуры левому ряду. Только коды цветов будут отличатся:

RGB: 237;125;0.
RGB: 255;202;143.

Кроме того, фигуру предварительно следует отразить по горизонтали выбрав соответственный инструмент «Повернуть» из ее дополнительного меню «ФОРМАТ»:

И наконец делаем третью фигуру черного цвета сгруппировав ее из двух фигур «Пятиугольник»:

Также удаляем лишние элементы легенды выделяя их кликая по ним левой кнопочкой мышки по отдельности.

Оформление подписей данных рядов графика

Теперь отдельно для каждого ряда добавляем подписи данных. Сначала нужно выделить ряд (например, правый), а потом нажать на кнопку плюс «+» рядом с диаграммой и отметить галочкой опцию «Подписи данных»:

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

Для среднего ряда следует внести другие настройки для формата подписей данных:

Цвет шрифта можно изменять из меню «ГЛАВНАЯ»-«Шрифт»-«Цвет» и другие желаемые опции.

При желании можно для каждого ряда добавить тень из меню «РАБОТА С ДИАГРАММАМИ»-«ФОРМАТ»-«Стили фигур»-«Эффекты фигур»-«Тень»-«Снизу со смещением». А после помещаем диаграмму бабочку на главный лист график:

Скачать шаблон диаграммы бабочка в Excel

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

Здесь подробно описано как сделать горизонтальную диаграмму бабочку для анализа значений показателей в числовом формате.