Простой пример 3D-диаграммы со значением больше 100% в Excel

Существенная доля экономических и финансовых показателей являются относительными величинами и вычисляются в процентах. Соответственно в отчетах, в презентациях или на дашбордах эти значения презентуются как процентное число со знаком %. Очень часто дизайнеры дашбордов выбирают круговую диаграмму для визуализации данных в относительных значениях. Не менее часто возникает вопрос: «Как на диаграмме показать перевыполнения плана больше чем 100% в Excel?». Данный шаблон является одним из примеров решения этой задачи.

Практическое применение 3D-диаграммы с процентами в Excel

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

  1. ROI – (Return On Investment) рентабельности капитала.
  2. ROA – (Return On Assets) рентабельности активов.
  3. ROEX – (Return On Expenses) рентабельности продукции.
  4. ROS – (Return On Sales) рентабельности продаж.

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

  • ROCE – (Return On Common Equity) рентабельность обыкновенного акционерного капитала;
  • ROIC – (Return On Investment Constant) окупаемость постоянного капитала;
  • ROMI – (Return On Marketing Investment) показывает окупаемость рекламы.

Но существует и множество других финансовых и экономических процентных показателей:

  • WACC – (Weighted Average Cost of Capital) показатель средневзвешенной стоимости капитала
  • TSR – (Residual Total Shareholder Return) совокупная акционерная доходность;
  • RTSR – (Residual Total Shareholder Return) остаточная совокупная доходность акций;
  • ETSR – (Economic Total Shareholder Return экономическая совокупная доходность акционеров;
  • CMER – (Containing Market Equity Return) показатель совокупной рыночной доходности собственного капитала;
  • RMER – (Residual Market Equity Return) показатель остаточной рыночной доходности собственного капитала.

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

Показатель инфляции очень мало вероятно может превышать 100%, но теоретически это вполне реально.

Интересный факт о инфляции! Из истории нам известно давно, что на кануне второй мировой войны (а точнее в 1923 году), инфляция в Германии достигала 3,25*106 процентов (3,25 млн.%) в месяц (фактически цены удваивались каждые 49 часов)!

Даже показатель ROI если в бизнесе он превышает 70% уже вызывает подозрение, так как в большинстве случаев получить такую высокую доходность после уплаты всех налогов практически не реально. Но с другой стороны ROI одной акции за сутки может взлететь до 300% под влиянием рыночной паники. Кстати динамика изменения цен на акции также измеряется в процентах.

Похожий пример с показателем ВВП, который может быть, как абсолютным, так и относительным значением. Если ВВП является относительным значением, тогда мало вероятно, что он будет больше 100%. Но и здесь бывают исключения. Например, по состоянию на 01.02.2021 капитализация мирового фондового рынка $107,8 триллионов – это 123% мирового ВВП.

Еще можно упомянуть о процентных ставках микро-кредитов и просроченной задолженности, которые также могут превышать значение в 100% годовых.

Шаблон диаграммы для визуализации данных с превышением значения 100%

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

Формулы отображения относительных значений больше 100%.
  1. Самый нижний слой с диаграммой показывает стандартно до 100%:
    2. Первый слой диаграммы.
  2. На втором слое от 100% и до 200%:
    3. Второй слой диаграммы.
  3. Соответственно на третьем 200%-300%:
    4. Третий слой диаграммы.

Так можно накладывать большое количество графиков, но сразу стоит отметить что уже на третьем слое диаграммы сильно разрушается смысл визуализации данных. Обязательно должна быть подпись показателя на графике. А ведь как известно идеальная визуализация или инфографика должна легко читаться без текста и подписей, что в данном случае невозможно. Поэтому в данном шаблоне отображение показателей более 200% представлено в качестве примера. Удалить лишние слои диаграмм при необходимости можно в дополнительном окне вызвав его комбинацией клавиш ALT+F10:

Управление слоями ALT+F10.

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

Полезный совет! Если возникает необходимость вводить сразу проценты исходных данных для ячейки A3, тогда следует отредактировать и переписать формулы в диапазоне A1:C2, а также в ячейке H12.

По умолчанию в Excel нет типа объемной кольцевой 3D-диаграммы, возможно появится в новой версии пакета MS Office 2022 релиз которого намечается уже на этот год. Разработчики Microsoft обещают существенные изменения в этой версии Excel 2022. На текущий момент с помощью векторных фигур можно самостоятельно нарисовать кольцевую 3D-диаграмму в Excel, как в этом примере:

Красивая интерактивная 3D визуализация данных.

download file. Скачать кольцевую 3D-диаграмму больше 100% в Excel

Данный шаблон не использует макросы. Интерактивность визуальных изменений на кольцевой 3D-диаграмме реализована с помощью стандартного элемента управления, который кликом мышки изменяет значение сначала в ячейке A4, а потом в A3 – соответственно. Чтобы добавить такой инструмент выберите его на вкладке: «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить». Группу диаграмм можно скопировать и перенести в другой файл вместе с формулами, для использования в своих отчетах и дашбордах при визуализации данных в программе Excel при презентации красивых финансовых отчетов.


en ru