Кольцевая и пузырьковая диаграмма на 3 показателя в Excel

Рассмотрим два примера презентации трех показателей на диаграммах Excel. Каждый пример визуализации обладает своими преимуществами и недостатками. Поэтому сделать рациональный выбор – это использовать оба примера одновременно на одном дашборде.

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

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

Подготовка исходных данных

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

В столбце B заполняем исходные значения в процентном формате ячеек. В столбце C формула вычисления доли остатка от 100% после вычитания исходного значения. В столбцах D и E будут находиться формулы для вычисления координат точек. Положение точек динамически изменяется по кругу диаграммы, поэтому нам понадобится в формулах использовать функции синуса для вычисления положения по оси X и функция косинуса для вычисления координат точек по оси Y.

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

Выделяем исходную таблицу чарта в диапазоне A3:C7 и создаем кольцевую диаграмму через меню Insert – Charts – Doughnut. Используем кнопку переключения строк и столбцов. Ведь значения будут изменяться на чарте в одном направлении.

Теперь для украшения Doughnut Chart добавим еще три серии данных используя инструмент «Select Data Source». На данном этапе каждая новая серия будет ссылаться на ячейки значении Y в столбце E, поочередно. Имя каждой серии начинается со слова «Light».

Теперь для всех трех новых серий данных изменим тип чарта на Scatter и в результате получим точки, для которых формулы вычисляли координаты. Настроим и зафиксируем минимальные и максимальные значения на осях координат, чтобы точки находились точно на своих местах.

Точки Scatter все еще не на своем месте, потому что им еще нужно указать не только Y, но и значение X для определения своего места положения на кольцевой диаграмме. Снова воспользуемся инструментом «Select Data Source» и теперь после того, как мы изменили тип чарта на Scatter у нас появилась возможность заполнить еще одно поле во всех трех новых добавленных сериях данных, чтобы указать значение для оси X.

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

При корректировке для третьего и первого значения немного изменим формулы вычисления точек координат по осям XY. Теперь все точки находятся на своих местах.

Создание мини дашборда для презентации в Excel

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

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

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

Сегменты остатков заполним однотонным темным цветом. А секторы значений заполняем градиентной заливкой от такого же темного цвета к цвету маджента под углом 90 градусов.

Из точек на диаграмме создадим красивые звездные огни. Для этого используем настройки цветов и размеров маркера Scatter чарта. Для точек маркеров добавляем эффект свечения. Размер 39 пунктов, а прозрачность 60%. Цвет света – маджента.

Добавим текстовую надпись ссылаясь из элемента TextBox на ячейку с названием исходной таблицы B1. Указываем ссылку в формуле при выделенном элементе TextBox. Только потом форматируем размер и цвет шрифта надписи.

Нам понадобится еще одна формула в ячейке B9 из одной функции вычисления среднего арифметического значения из трех исходных значений. И выведем результат вычисления формулы на визуализацию с помощью ссылки из нового TextBox элемента.

Как сделать интерактивные кнопки для дашборда в Excel

Создаем интерактивные элементы и функционал для визуализации кольцевой диаграммы с тремя значениями. Для этого будем использовать сводную таблицу и кнопки срезов данных. Допустим база данных по трем топ показателям за неделю находится в таблице по адресу диапазона ячеек H2:K8. Для примера заполним таблицу случайными относительными значениями в процентах для каждого дня недели. Используем для этого формулу из функции RANDBETWEEN поделено на 100. Копируем случайные результаты вычисления формулы и вставляем только значения в этот же диапазон, чтобы они больше не обновлялись. Дальше сортируем каждый столбец по отдельности и функцией SUM узнаем набольший средний и наименьший из трех столбцов чтобы соответственно заполнить мини-базу данных.

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

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

На основе сводной таблицы создаем элемент среза данных и создаем ему дизайн.

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

Как сделать пузырьковую диаграмму с тремя переменными в Excel

Теперь для сравнения создадим еще одну визуализацию из пузырьковой диаграммы. Сначала делаем исходную таблицу для чарта в диапазоне ячеек H10:K13. Таблица также состоит из трех переменных значений, как и в предыдущем примере. Значения в столбцах X и Y – это константы, они не изменяются, так нужно для дизайна. Изменяются только переменные значения в последнем столбце Rank.

Создание шаблона пузырьковой диаграммы для трех значений

Выделяем все строки исходной таблицы в диапазоне ячеек H11:K13 и создаем пузырьковую диаграмму выбрав инструмент Insert – Charts - Bubble. Не снимая выделения с нового чарта, выбираем инструмент «Select Data Source» для редактирования ссылок серии данных. Диапазоны значений в аргументах ссылок должны охватывать по вертикали. Все поля заполняем соответственно и без ошибок.

Теперь для осей координат указываем минимальное значение 0% и максимальное 100%.

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

Подключение пузырьковой диаграммы к дашборду

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

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

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

Теперь следует сообщить сводной таблице, что база данных расширена. Для этого изменим аргументы ссылки на новый диапазон охвата данных для сводной таблицы. Воспользуйтесь инструментом анализ сводной таблицы – изменить исходные данные. И в поле ввода «Table Range» укажите ссылку на новый диапазон. В результате в конструкторе появились новые данные. Подключите их, отметив флажком и сводная таблица расшириться.

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

Презентация мини-дашборда в Excel

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

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

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