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

16 March 2018
5k full reads
5 min.
8k story viewsUnique page visitors
5k read the story to the endThat's 63% of the total page views
5 minutes — average reading time

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

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

Итак, представим ситуацию. Мы получили задачу подготовить 4 варианта инвестирования средств компании в заранее отобранные хедж-фонды. В итоговом отчете мы должны показать рентабельность, ликвидность, надежность и структуру распределения средств для каждого варианта.

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

У нас есть 3 заранее согласованных фонда, в каждом из которых предлагают несколько пакетов ценных бумаг для инвестиций и 3 валюты вклада: евро, доллар и рубль. Мы подготовили 4 варианта распределения средств между фондами в % от общего итога. Именно эти варианты мы и должны визуализировать.

Для начала организуем данные на листе в следующем виде:

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

Обратите внимание, что у нас есть 4 таблицы:

1) Распределение по хедж-фондам;

2) Распределение по пакетам (внутри каждого хедж-фонда);

3) Распределение по валютам (внутри каждого пакета);

4) Показатели эффективности.

Колонку "Дашборд" расположите рядом с первым столбцом, но пока оставьте пустой. В неё будет формулами подтягиваться тот вариант, который нужно отобразить на диаграмме. В остальные - занесите варианты распределения средств. Обратите внимание, что в каждой таблице проценты даны от общей суммы средств (то есть итог по каждому из вариантов в любой таблице будет 100%). Это важный момент, который пригодится нам при построении диаграммы со структурой распределения.

Теперь добавим на лист элемент управления "Переключатель". С его помощью мы будем выбирать, какой вариант отобразить на диаграмме. Выберите "Разработчик" - "Вставить" - "Элементы управления формы" - "Переключатель".

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

Курсор примет форму тонкого крестика. Зажмите левую кнопку мыши и нарисуйте на листе небольшой прямоугольник. Кликните по нему правой кнопкой мыши, выберите "Изменить текст", введите "Вариант №1" и нажмите Esc. Теперь выделите объект, скопируйте и вставьте. Дайте ему имя "Вариант №2". По аналогии создайте переключатели для 3-ого и 4-ого вариантов.

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

Кликните правой кнопкой мыши по любому из них и выберите "Формат объекта". В открывшемся окне на вкладке "Элемент управления" в окне "Связь с ячейкой" укажите любую удобную Вам и свободную ячейку на листе (лучше вводить вместе с именем листа, чтобы переключатель работал и на других листах). К остальным переключателям ячейка привяжется автоматически.

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

Теперь в связанную ячейку будет выводиться номер переключателя, который выбран в текущий момент (одновременно может быть выбран только один переключатель). Можете поэкспериментировать и убедиться, что при выборе выключателя с определенным вариантом, номер варианта появляется в ячейке автоматически.

Осталось выровнять переключатели и для удобства сгруппировать (как быстро выравнивать объекты мы рассказывали в этом видео).

А вот теперь давайте введем формулу в колонки "Дашборд". Нам понадобится функция ИНДЕКС. Первым аргументом укажем диапазон из 4 ячеек с вариантами (справа от активной ячейки), а вторым - ячейку, связанную с переключателями.

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

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

Теперь всё организовано, перейдем к созданию диаграмм.

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

Выделите два первых столбца таблицы с распределением по хедж-фондам (шапку не выделяйте). Теперь выберите "Вставка" - "Диаграммы" - "Кольцевая".

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

Покрасьте сектора диаграммы в нужные цвета (клик правой кнопкой мыши на секторе - "Формат ряда данных" - "Заливка и границы" - "Сплошная заливка"). Теперь удалите с диаграммы название и легенду.

Теперь на вкладке "Конструктор" кликните "Выбрать данные". В открывшемся окне выберите "Добавить ряд". Введите имя "Пакеты", укажите диапазон с распределением долей в таблице "Распределение по пакетам" и нажмите "ОК".

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

Аналогичным образом добавьте ряд "Валюты" (не забывайте, что выделять нужно значения столбца "Дашборд").

Диаграмма примет вот такой пёстрый вид.

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

Теперь кликните на любой ряд правой кнопкой мыши - "Формат ряда данных" - "Параметры ряда" - "Диаметр отверстия в % от общего размера" = 10%.

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

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

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

Поставьте курсор между двумя точками с запятой и выберите во второй таблице первую колонку (колонку с названиями пакетов). Получится вот такая формула:

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

Чтобы завершить ввод, нажмите Enter. Теперь для этого ряда можно включить метки данных.

Хедж-фонды мы будем различать по цвету, а валюты - по типу штриховки ("Заливка" - "Узорная заливка"). Получится примерно следующее (легенда нарисована вручную с помощью надписей и фигур):

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

Первая часть дашборда готова.

Создание кольцевой шкалы для показателей эффективности

Осталось графически изобразить три показателя эффективности. Сделаем это на кольцевой шкале. Введите в свободном столбце одну под другой несколько единиц. Сколько введете - столько и будет мини-секторов на шкале. Например, чтобы каждый сектор составлял 15 градусов, нужно ввести 360/15 = 24 единицы.

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

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

Теперь в таблице с показателями эффективности добавьте в конце колонку (дадим ей заголовок "Пустой") и введите в нее формулу =1-B22, где B22 - ячейка столбца "Дашборд". Протяните вниз на все показатели.

Кликните по диаграмме и на вкладке "Конструктор" нажмите "Выбрать данные". Добавьте ряд, указав следующие настройки:

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

Обратите внимание, что в строке "Значения" указана сначала ячейка с рентабельностью из столбца "Дашборд", а затем - соответствующая ячейка из добавленного столбца "Пустой".

На вкладке "Конструктор" нажмите "Изменить тип диаграммы" и задайте следующие настройки:

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

Для ряда "Рентабельность" сектор из колонки "Дашборд" сделайте прозрачным, а второй - залейте белым цветом, установив прозрачность в 25%.

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

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

Сделайте аналогичные диаграммы (только другого цвета) для показателей "Ликвидность" и "Надежность".

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

Организация дашборда

Когда диаграммы готовы, организуйте их в дашборд. На новом листе создайте большой белый прямоугольник ("Вставка" - "Фигуры"), в котором мы и разместим все элементы. Кликните по нему правой кнопкой мыши и выберите "На задний план" Перенесите с предыдущего листа на новый все диаграммы, а также переключатели и расположите на белом прямоугольнике.

Добавьте нужные заголовки с помощью фигуры "Надпись". Можно украсить всё это картинкой или значком из библиотеки ("Вставка" - "Значки"). Значки - векторные, и прекрасно масштабируются, но доступны только в новых версиях Excel.

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

Когда всё расположите, можете сгруппировать все объекты дашборда, чтобы его можно было быстро перемещать и масштабировать целиком, не двигая отдельные элементы по очереди. На выходе получаем вот такой простой, но интерактивный отчёт:

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

Оформление - дело вкуса. Главное, чтобы Вы усвоили все приемы, использованные при построении дашборда. Для закрепления можете изучить файл-пример. Он доступен по этой ссылке.

Поддержать наш проект и его дальнейшее развитие можно вот здесь.

Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot

С уважением, команда tDots.ru