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

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

Динамический дизайн в Excel начинается с чисел и формул расчетов

Заполняем таблицу исходных данных формулами в каждом соответственном столбце.

Круговая диаграмма будет состоять из 4-х сегментов (+1 скрытый). В исходной таблице 4+1 строки с данными по каждому сегменту.

Структура данных по столбцам исходной таблицы:

1. В первом столбце «Value» находятся исходный показатель для каждого сегмента. Для простоты восприятия обучающего материала сначала заполним все 4 строки данного столбца одинаковыми значениями 25%. После завершения постройки круговой диаграммы можно будет изменять исходные значения в этом столбце и наслаждаться готовым результатом.
2. Во втором столбце «Data X» – подготовка данных для вычисления координат расположения маркеров по оси X, с помощью формулы. Обратите внимание, что здесь в аргументах формулы одновременно используется два типа ссылок: абсолютная $B$2 и относительная B2.
3. В третьем столбце «Data Y» формулы для подготовки данных перед вычислением координат расположения маркеров по оси Y.
4. В четвертом столбце «X» будет уже непосредственно вычисляться положение маркеров по оси X с учетом окружности. Поэтому там будет применяться формула с функциями расчета синуса и числа Пи. На основе предварительно подготовленных данных в столбце «Data X».
5. В пятом столбце «Y» заключительное вычисление положений маркеров с учетом окружности по оси Y. Там будет применяться формула с функциями косинуса и числа Пи. На основе подготовленных данных столбца «Data Y»
6. В пятом ряду исходной таблицы будут формулы для расчета скрытого сегмента и скрытого маркера диаграммы.

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

Подготовка исходных данных для кольцевой диаграммы с динамическим дизайном

Приступим к подготовке данных для расположения декоративных маркеров диаграммы на осях координат XY. На данном этапе постройки диаграммы с динамическим дизайном второй столбец «Data X» в исходной таблице формулу с абсолютным и относительным типом ссылок в аргументах функции: =SUM($B$2:B2)

Такой подход дает нам возможность динамически охватывать необходимую часть диапазона ячеек B2:B5. В результате в столбце C сначала суммируется только одна ячейка с исходным значением столбца B (25%), потом две (25%+25%=50%), три (75%) и четыре (100%).

В то же время третий столбец «Data Y» содержит формулу вычисления остаточной доли от 100% после вычитания исходного значения: =100%-C2.

Четвертый и пятый столбце заполняем формуламы расчета координат для маркеров по осям X и Y используя практически одну и ту же формулу, но в первыйо мы используем функцию вычисления синуса, а во ворой – коссинуса и ссылаемся на соотвествующие столбцы с подготовленными данными:

1. X:

=IF(B2=0,NA(),SIN(C2*2*PI()))

2. Y:

=IF(B2=0,NA(),COS(D2*2*PI()))

Теперь заполним еще одну строку в исходной таблице для скрытого сегмента диаграммы. Он будет отображаться только при исключении любого из сегментов, чтобы заполнить пустое пространство и отобразить долю остатка от 100%:

=100%-SUM(B2:B5)

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

1. E6:

=IF(B6=0,NA(),0)

2. F6:

=IF(B6=0,NA(),1)

Значения в аргументах 0 и 1 являются константами. Они не изменяются так как это последний маркер в последнем сегменте, а он неподвижен при любых изменениях данных на круговой диаграмме. Их следует изменить если будет изменен параметр угла наклона диаграммы, например при 30 градусах значения аргументов будут равны 0.5 и 0.866.

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

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

Далее добавляем ряд для комбинации круговой диаграммы с другим типом чарта с целью украшения маркерами. Выделите одним кликом чарт и выберите инструмент из появившихся дополнительных вкладом меню: Chart Design – Select Data.

В окне настроек параметров «Select Data Source» в разделе «Legend Entries (Series)» нажмите на кнопку «Add». В поле ввода «Series values» укажите ссылку на диапазон =Sheet1!$E$2:$E$6.

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

Далее просто укажите на новый тип чарта выбрав инструмент: Insert – Charts – Scatter.

В результате появились маркеры для стильного оформления круговой диаграммы.

Теперь нам необходимо настроить новый тип чарта Scatter. Для этого снова откройте окно настроек: Chart Design – Select Data. В разделе «Legend Entries (Series)» выберите серию данных с именем «XY» и нажмите на кнопку «Edit».

Теперь здесь три поля ввода вместо двух. Заполняем их соответственно, как показано на рисунке. В поле ввода «Series X values» ссылка на диапазон ячеек: =Sheet1!$E$2:$E$6, а в поле «Series Y values» ссылка =Sheet1!$F$2:$F$6.

Чтобы маркеры распределились на соответственных местах необходимо выполнить калибровку оси координат фиксированными значениями. Для этого выделите одним кликом левой кнопкой мышки вертикальную ось Y и нажмите комбинацию горячих клавиш CTRL+1 для вызова дополнительного окна настройки параметров оси и укажите фиксированные минимальное -1,14 и максимальное +1,15 значение.

Обратите внимание! Для оси X мы указываем другое минимальное -1,15 и максимальное +1,14 фиксированное значение.

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

В результате все декоративные маркеры расположены в нужных местах круговой диаграммы.

Шаблон готов к оформлению дизайна цветовой схемой маркеров градиентами заливками сегментов как показано ниже на рисунке.

Сначала мы увеличиваем размер маркера до ширины сегментов.

Затем настраиваем цвета на маркерах и градиентные заливки на сегментах. Также не забудем настроить цвета для скрытого маркера и сегмента. Сначала нужно их отобразить, для этого изменим одно из исходных значений на 0% чтобы его исключить.

После чего окрашиваем цветом и скрытый сегмент вместе с его скрываемым маркером.

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

Теперь нам нужно переписать формулы для нового распределения положения декоративных маркеров круговой диаграммы. Изменяем формулы в столбце «Data X» и ячейках E6 и F6:

1. Data X:

=SUM($B$2:B2)+100%/(360/30)

2. Ячейка E6:

=IF(B6=0,NA(),0.5)

3. Ячейка F6:

=IF(B6=0,NA(),0.866)

Дальше украшаем дизайн визуализации данных используя фигуры, кнопки срезов и другие стандартные средства приложения MS Excel:

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

Красивый дизайн придает элемент игры в процесс работы. В результате эффективность работы повышается. Используйте возможности Excel для творческого выражения в новых шедеврах визуализации данных. Ваш процесс работы будет более приятным. Красота – это безусловный и натуральный источник удовольствия без побочных эффектов.

Практическое применение кольцевой диаграммы с динамическим дизайном на дашборде можно посмотреть и скачать здесь:

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