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

Как сделать чарт тип бабочка с возможностью сортировки по левому или по правому крылу на выбор пользователя? Это вполне реально реализовать в приложении MS Excel стандартными инструментами без использования макросов. Суть в том, что чарт бабочка обычно не сортируется. Если же и выполняется сортировка, то чаще всего по убыванию. Но реализовать ее можно только по одному крылу, чтобы не нарушить достоверность данных. Мы же кастомизируем свою умную бабочку, которая будет порхать крыльями с помощью сортировки то по левому, то по правому крылу. Реализуем это все с помощью пользовательского интерфейса управления визуализацией данных. Это будет эффектно и практически полезно применимо.

Как сортировать по убыванию с помощью формул в Excel

Для чарта вертикальная бабочка требуется минимум 2 столбца исходных однотипных данных. В нашей исходной таблице их 3:

1. Категории товаров.
2. Количество продаж.
3. Остатки на складах.

Сразу приступаем к сортировке и предварительной подготовки исходных данных. Сначала создадим первую дополнительную таблицу для сортировки всех значений по второму столбцу «Sales» по убыванию. Назовем первую дополнительную таблицу «Sort by Sales». Для сортировки используем умные формулы, которые умеют сортировать дубликаты (если такие имеются):

Формула в первом столбце дополнительной таблицы «Sort by Sales»:

=INDEX($B$1:$B$7,SMALL(IF($C$1:$C$7=G1,ROW($C$1:$C$7)),COUNTIF($G$1:G1,G1)))

Формула во втором столбце – обычная сортировка значений по убыванию:

=LARGE($C$2:$C$7,A2)

Обратите внимание! Дополнительные таблицы сортировки должны начинаться с первой строки. Они используют сложную формулу, которая умеет правильно и эффективно обрабатывать дублирующиеся значения при сортировке. Подробно описано в этой статье: Как отсортировать данные с дубликатами для графика Excel.

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

Аналогичным образом строим вторую дополнительную таблицу для промежуточной сортировки остатков по убыванию. Назовем вторую дополнительную таблицу «Sort by Invertory»:

Далее нам потребуется создать промежуточную таблицу, которая будет автоматически заполняться, предварительно отсортированными данными из первой дополнительной таблицы «Sort by Sales» или из второй «Sort by Invertory».

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

Выбор таблицы для заполнения будет завесить от значения в ячейке C9 (1 или 2).

Формулы для заполнения столбцов «Sales» и «Invertory»:

=CHOOSE($C$9,G1,INDEX($C$2:$C$7,MATCH(B11,$B$2:$B$7,0)))

=CHOOSE($C$9,INDEX($D$2:$D$7,MATCH(B11,$B$2:$B$7,0)),J1)

Теперь добавим элемент управления значением в ячейке C9.

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

Выберите инструмент: «Developer» - «Insert» - «Form Control» - «Option Button (Form Control)».

Кликните правой кнопкой мышки по элементу «Option Button» и в появившемся контекстном меню выберите опцию «Format Control». В появившемся окне «Format Object» на вкладке «Control» в поле ввода «Cell Link:» укажите ссылку на ячейку C9.

Теперь при переключении по элементам «Option Button» значение в ячейке C9 будет автоматически изменяться на 1 или 2. Соответственно в промежуточной таблице будут обновляться ее значения.

Далее переходим к созданию последней таблицы для шаблона чарта бабочки.

Подготовка исходных данных для шаблона визуализации

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

=MAX($C$11:$D$16)*$F$9-G11

В следующем столбце мы просто ссылаемся на второй столбец «Sales» промежуточной таблицы.

Формула вычисления отступов в центральной части чарта бабочка, между барами в месте расположения подписей категорий:

=MAX($C$11:$D$16)*$H$9

В следующем столбце мы просто ссылаемся на третий столбец «Invertory» в промежуточной таблице.

Заполняем последний столбец формулами вычисления отступов баров с правой стороны крыла бабочки:

=MAX($C$11:$D$16)*$J$9-I11

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

Создание шаблона чарта типа бабочка с интерактивной сортировкой

Выделите диапазон ячеек F10:J16 и выберите инструмент: «Insert» - «Charts» - «2-D Bar» - «100% Stacked Bar».

Теперь чтобы шаблон чарта стал быть похожим на бабочку, сделаем несколько настроек в его параметрах.

Гистограмма крылья бабочки

Одним кликом левой кнопкой мышки выделите вертикальную ось Y и нажмите комбинацию горячих клавиш CTRL+1. В появившемся дополнительном окне параметров «Format Axis» в разделе «Axis position» выберите опцию «Categories in reverse order» чтобы бары расположились в обратном порядке.

Одним кликом левой кнопкой мышки выделите бары последней серии данных и нажмите комбинацию горячих клавиш CTRL+1. В появившемся дополнительном окне параметров «Format Data Series» в разделе «Fill» выберите опцию «No fill».

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

• indent LEFT;
• middle;
• indent RIGHT.

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

Теперь нажмите на кнопку с большим плюсом, которая появляется при выделении одним кликом по области чарта. В появившемся выпадающем контекстным меню снимите флаги с опций «Axes», «Gridlines» и «Legend».

Переходим к важной части – добавления подписей данных. Сначала мы просто включаем режим отображения подписей данных только для рядов с прозрачной заливкой фона.

Добавление и настройка подписей баров горизонтальной гистограммы

Снова кликаем на кнопку с большим плюсом возле чарта и отмечаем флажком опцию «Data Labels», а из выпадающего меню выбираем опцию для:

• indent LEFT – «Inside Left»;
• middle – «Center»;
• indent RIGHT – «Inside Base».

Далее нам нужно в настройках подписей изменить источники данных. Для этого одним кликом левой кнопкой мышки выделите центральные подписи данных и нажмите комбинацию горячих клавиш CTRL+1 или кликните по подписям правой кнопкой мышки и из появившегося контекстного меню выберите опцию «Format Data Labels». В появившемся дополнительном окне в разделе «Label Options» поставьте флажок на опции «Value From Cells» и нажмите на кнопку «Select Range». В результате появится новое дополнительное окно «Data Label Range» в его единственном поле ввода «Select Data Label Range» укажите ссылку на диапазон ячеек первого столбца промежуточной таблицы: =Sheet1!$B$11:$B$16.

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

1. Левое крыло: =Sheet1!$C$11:$C$16.
2. Правое крыло: =Sheet1!$D$11:$D$16.

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

Презентация дизайна чарта бабочка

Воспользуйтесь цветовой схемой, показанной ниже на картинке в качестве примера для декоративного оформления дизайна визуализации.

Скачать отсортированную диаграмму-бабочку в Excel

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

Дашборд для управления KPI планами в Excel.