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

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

Подготовка формул и исходных данных

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

1. A2:B2 – здесь находятся данные для базовой кольцевого чарта Excel. В ячейке A2 – исходные данные и ячейка выделена цветом для наглядности. A2 не содержит формул и ее значения можно изменять. В ячейке B2 – находится формула вычисления свободной доли от исходного значения в процентах: =100%-A2.

2. Диапазон A3:B3 – промежуточные формулы для конвертации значений с учетом наклона круговой диаграммы на 30 градусов. В целях создания привлекательного дизайна было принято решения немного наклонить кольцевую диаграмму по часовой стрелке на 30 градусов. Это повлияет на соответствие координат расположения маркера на диаграмме. Если диаграмму не наклонять на 30 градусов, а оставить в изначальном базовом положении, то эти формулы не нужно использовать. Но в данном случае потребуется корректировка исходных значений. Поэтому в ячейку A3 вводим формулу: =A2+100%/(360/30), а в ячейке B3 формула: =100%-A3.

3. C2-D2. Для создания стильного дизайна кольцевой диаграммы будем использовать комбинацию из двух типов чартов. Поэтому будет две серии данных. В диапазоне C2-D2 находятся данные для второй серии, то есть для второго чарта (Scatter). Это будут координаты расположения декоративного маркера на диаграмме. Так как в координатах следует учитывать окружность формулы, будут использоваться функции вычисления синуса и косинуса. В ячейке C2 формула: =SIN(A3*2*PI()), а ячейке D2 формула: =COS(A3*2*PI()).

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

Создание шаблона для динамической кольцевой диаграммы в Excel

Для начала создаем макет шаблона из базового круговой диаграммы Excel. Выделите первый диапазон исходных данных A2:B2 и выберите инструмент: Insert – Charts – Doughnut.

Нажмите комбинацию горячих клавиш CTRL+1 чтобы вызвать окно настроек «Format Data Series». В разделе «Series Options» измените параметр «Angle of first slice» на 30 градусов.

Далее не снимайте выделение с чарта выберите вкладку из дополнительного меню «Chart Design», а затем инструмент «Select Data» для вызова диалогового окна настроек «Select Data Source».

В разделе «Legend Entries (Series)» нажмите на кнопку «Add» и в появившемся диалоговом окне «New (Edit) Series» в поле ввода «Series Values:» укажите адрес третьего диапазона ячеек C2-D2.

Выберите вторую серию данных на чарте. Кликните по ней левой кнопкой мышки или воспользуйтесь меню настроек через вызов комбинации горячих клавиш CTRL+1 как показано ниже на рисунке. Затем задайте другой тип чарта для этой серии данных воспользовавшись инструментом: Insert – Charts – Scatter.

Далее настраиваем параметры маркера. Не снимая выделение со второй серии данных «Marker» переходим на вкладку дополнительного меню «Chart Design» и выбираем инструмент «Select Data».

Редактируем ссылки на ячейки с данными для полей Series X value: C2 и Series X value: D2. Как показано выше на рисунке.

Теперь нам нужно выполнить калибровку положения маркера на оси координат XY. Для этого мы установим фиксированные максимальные и минимальные значения для вертикальной и горизонтальной оси координат чарта Scatter. Выделите одну из осей (например, вертикальную ось Y) и снова нажмите комбинацию клавиш CTRL+1 (или правой мышкой по элементу и в контекстом меню опция Format Axis) для вызова дополнительного окна параметров. Там в разделе Axis Options изменяем значения полей Minimum (-1.05) и Maximum (+1.05).

Для подтверждения после ввода значений в каждом поле жмем клавишу Entre, чтобы подпись Auto изменилась на Reset. Так указано, что автоматически изменяемые значения изменили статус на фиксированные неизменяемые.

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

Изменяем толщину формы кольцевой диаграммы. Выделите первую серию данных и в окне параметров установите максимальное значение Doughnut Hole Size на 90%.

Также измените размер маркера во второй серии данных (то есть кликните по нему). В дополнительном окне в разделе Marker Options укажите значение 8 в поле «Size».

Дизайн динамической кольцевой диаграммы в Excel

Теперь осталось настроить дизайн так как показано на видеоуроке в начале статьи.

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

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

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