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

Чтобы создать Pie Chart с наглядной презентацией показателей более 100% в Excel можно воспользоваться нестандартным решением. Сконструируем свой пользовательский Pie Chart с нуля.

Создание Pie Chart более чем 100% или 200% в Excel

Часто возникает потребность презентовать перевыполнение плана на визуализации данных дашборда Excel.

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

У нас имеются 3 исходных значения:

1. Продажи по факту (ячейка F2).
2. Установленный план продаж (ячейка G2).
3. Расчет выполнения плана продаж (в ячейке G3 формула расчета: =F2/G2).

Визуализация данных о выполнении плана продаж будет выполняться по двум сценариям:

1. Факт успешных продаж – перевыполнение плана более 100 процентов. Сценарий визуализации для Pie Chart: фрагмент доли процента перевыполнения плана презентуется эффектом отдельного слоя, наложенного на заполненную диаграмму в 100% в виде спирали.
2. Показатель слабых продаж – невыполнение плана в результате. Показатель выполнения плана = меньше, чем 100 процентов. Внешний вид диаграммы – «незаполненный» и состоит всего лишь из одного фрагмента с размером общей доли невыполненного плана.

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

Разработка Pie Chart больше 100 процентов шаг за шагом

Сначала сделаем фон диаграммы в стиле нестандартной фигуры – «спираль» на 360 градусов для первого сценария. Заполняем столбец из 360 ячеек формулами как исходными значениями.

Столбец номер 1

Заполоните диапазон ячеек A3:A362 формулой:

Данная формула заполняет значениями размера радиусов с уменьшением (-10%/360) расстояния от центра. Это данные для будущих 360 радиусов, длина каждого меньше на 0,000277778 предыдущего (если длина самого первого радиуса будет = 1). Таким образом мы нарисуем формулой не круг, а спираль для фона будущего Pie Chart.

Столбец 2

В диапазоне ячеек B3:B362 будет использована формула с выводом значений при условии второго сценария (невыполнение плана продаж <100%) иначе значение – 0:

Так как в значении ячейки G3 больше, чем 100 процентов – (105%), формула возвращает значение – 0.

Столбец 3

Диапазон ячеек C3:C362 заполнен формулой для презентации размера доли процента перевыполнения плана:

Обратите внимание! Формула заполнила значениями только 18 ячеек – это размер доли перевыполненного плана (5%). Если мы хотим на круге в 360 градусов показать долю заполнения 5% нам нужно экспонировать подсветкой цветом 18 градусов. То есть 360 * 5% = 18. Проверили результат – формулы работают как часы!

Столбец 4

В последнем столбце в диапазоне D3:D362 будет формула для рубежа (место пересечения уровня плана продаж на значении 100%) на диаграмме:

В четвертом столбце можно было бы не использовать формулу, а заполнить все ячейки значением 0 кроме первой (где значение 1) и 360-ой (значение также 1). Но если потребуется в диаграмме использовать именные диапазоны, тогда понадобиться данная формула. Важно только уточнить че ее в именном диапазоне следует вводить как формулу массива через комбинацию горячих клавиш (CTRL+SHIFT+ENTER). Но в данном примере мы не используем именной диапазон и вводим как обычную формулу, для демонстрационного примера возможности и перспективы расширять функционал.

Разработка дизайна диаграммы

Мы сконструировали все исходные данные для диаграммы. Осталось выделить диапазон A3:D362 и создать Pie Chart с помощью инструмента – Insert → Charts → Radar → Filled Radar:

Далее делаем настройки для Pie Chart и оформляем красивыми цветами и фигурами.

Дизайн для сценария 1

В центре диаграммы размещаем фигуру овал и получаем кольцевую диаграмму. Цвета следует задавать в разделе Marker Options.

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

Дизайн для сценария номер 2

Красиво оформленный дизайн визуализации легко и гармонично вливается в любой дашборд.

Как создать диаграмму боле чем 200%

Аналогичным способом можно создать диаграмму больше 200% и даже больше, чем 300%. Для этого нужно создать еще пару столбцов с формулами, а результат будет выглядеть так:

Это уже за границами предела читабельного вида визуализации. Поэтому лучше использовать другие стили чартов, когда необходимо показать перевыполнение плана больше, чем 200% и больше, чем 300%, 400%, 500%… В любом случае пример с Pie Chart больше чем на 300%, с формулами добавлен в файл Excel на листе Example, который можно скачать ниже по ссылке.

Как и где использовать Pie Chart с показателем больше 100%

Пример практического использования Pie Chart больше чем на 100% на дашборде для анализа данных аффилированного маркетинга в Excel:

Скачать пример анализ данных аффилированного маркетинга в Excel

В программе Excel присутствуют возможности и для других решений презентации визуализации более чем на 100% на диаграмме. Существует много способов для создания нестандартных Pie Charts без использования макросов. Много таких примеров находятся в других статьях этого сайта.