Создаем интерактивный дашборд на основе кольцевых диаграмм в Excel
Возможности визуализации данных в Excel значительно шире, чем может показаться на первый взгляд. Стандартными средствами можно реализовывать очень интересные вещи. В этой статье мы разберем пару трюков с кольцевой диаграммой, которые помогут нам сделать интерактивный дашборд.
Итак, представим ситуацию. Мы получили задачу подготовить 4 варианта инвестирования средств компании в заранее отобранные хедж-фонды. В итоговом отчете мы должны показать рентабельность, ликвидность, надежность и структуру распределения средств для каждого варианта.
Подготовка исходных данных
У нас есть 3 заранее согласованных фонда, в каждом из которых предлагают несколько пакетов ценных бумаг для инвестиций и 3 валюты вклада: евро, доллар и рубль. Мы подготовили 4 варианта распределения средств между фондами в % от общего итога. Именно эти варианты мы и должны визуализировать.
Для начала организуем данные на листе в следующем виде:
Обратите внимание, что у нас есть 4 таблицы:
1) Распределение по хедж-фондам;
2) Распределение по пакетам (внутри каждого хедж-фонда);
3) Распределение по валютам (внутри каждого пакета);
4) Показатели эффективности.
Колонку "Дашборд" расположите рядом с первым столбцом, но пока оставьте пустой. В неё будет формулами подтягиваться тот вариант, который нужно отобразить на диаграмме. В остальные - занесите варианты распределения средств. Обратите внимание, что в каждой таблице проценты даны от общей суммы средств (то есть итог по каждому из вариантов в любой таблице будет 100%). Это важный момент, который пригодится нам при построении диаграммы со структурой распределения.
Теперь добавим на лист элемент управления "Переключатель". С его помощью мы будем выбирать, какой вариант отобразить на диаграмме. Выберите "Разработчик" - "Вставить" - "Элементы управления формы" - "Переключатель".
Курсор примет форму тонкого крестика. Зажмите левую кнопку мыши и нарисуйте на листе небольшой прямоугольник. Кликните по нему правой кнопкой мыши, выберите "Изменить текст", введите "Вариант №1" и нажмите Esc. Теперь выделите объект, скопируйте и вставьте. Дайте ему имя "Вариант №2". По аналогии создайте переключатели для 3-ого и 4-ого вариантов.
Кликните правой кнопкой мыши по любому из них и выберите "Формат объекта". В открывшемся окне на вкладке "Элемент управления" в окне "Связь с ячейкой" укажите любую удобную Вам и свободную ячейку на листе (лучше вводить вместе с именем листа, чтобы переключатель работал и на других листах). К остальным переключателям ячейка привяжется автоматически.
Теперь в связанную ячейку будет выводиться номер переключателя, который выбран в текущий момент (одновременно может быть выбран только один переключатель). Можете поэкспериментировать и убедиться, что при выборе выключателя с определенным вариантом, номер варианта появляется в ячейке автоматически.
Осталось выровнять переключатели и для удобства сгруппировать (как быстро выравнивать объекты мы рассказывали в этом видео).
А вот теперь давайте введем формулу в колонки "Дашборд". Нам понадобится функция ИНДЕКС. Первым аргументом укажем диапазон из 4 ячеек с вариантами (справа от активной ячейки), а вторым - ячейку, связанную с переключателями.
Так как мы заранее организовали данные очень удобно, можно закрепить ссылку на ячейку с номером варианта и скопировать формулу во все другие ячейки всех столбцов "Дашборд".
Теперь всё организовано, перейдем к созданию диаграмм.
Создание кольцевой диаграммы-структуры
Выделите два первых столбца таблицы с распределением по хедж-фондам (шапку не выделяйте). Теперь выберите "Вставка" - "Диаграммы" - "Кольцевая".
Покрасьте сектора диаграммы в нужные цвета (клик правой кнопкой мыши на секторе - "Формат ряда данных" - "Заливка и границы" - "Сплошная заливка").
Теперь удалите с диаграммы название и легенду. Теперь на вкладке "Конструктор" кликните "Выбрать данные". В открывшемся окне выберите "Добавить ряд". Введите имя "Пакеты", укажите диапазон с распределением долей в таблице "Распределение по пакетам" и нажмите "ОК".
Аналогичным образом добавьте ряд "Валюты" (не забывайте, что выделять нужно значения столбца "Дашборд").
Диаграмма примет вот такой пёстрый вид.
Теперь кликните на любой ряд правой кнопкой мыши - "Формат ряда данных" - "Параметры ряда" - "Диаметр отверстия в % от общего размера" = 10%.
Приступим к цветовому оформлению диаграммы. Дело в том, что при таком варианте построения можно добавить подписи данных только к одному ряду. Самые многочисленные подписи у нас в ряде "Пакеты". Добавим к нему имена категорий. Кликните на ряд "Пакеты" левой кнопкой мыши. В строке формул отобразится формула РЯД.
Поставьте курсор между двумя точками с запятой и выберите во второй таблице первую колонку (колонку с названиями пакетов). Получится вот такая формула:
Чтобы завершить ввод, нажмите Enter. Теперь для этого ряда можно включить метки данных.
Хедж-фонды мы будем различать по цвету, а валюты - по типу штриховки ("Заливка" - "Узорная заливка"). Получится примерно следующее (легенда нарисована вручную с помощью надписей и фигур):
Первая часть дашборда готова.
Создание кольцевой шкалы для показателей эффективности
Осталось графически изобразить три показателя эффективности. Сделаем это на кольцевой шкале. Введите в свободном столбце одну под другой несколько единиц. Сколько введете - столько и будет мини-секторов на шкале. Например, чтобы каждый сектор составлял 15 градусов, нужно ввести 360/15 = 24 единицы.
Теперь выделите столбец с единицами и вставьте кольцевую диаграмму. Удалите с нее легенду и название, а все сектора залейте одним и тем же ярким цветом. Можете по вкусу уменьшить диаметр "дырки от бублика".
Теперь в таблице с показателями эффективности добавьте в конце колонку (дадим ей заголовок "Пустой") и введите в нее формулу =1-B22, где B22 - ячейка столбца "Дашборд". Протяните вниз на все показатели.
Кликните по диаграмме и на вкладке "Конструктор" нажмите "Выбрать данные". Добавьте ряд, указав следующие настройки:
Обратите внимание, что в строке "Значения" указана сначала ячейка с рентабельностью из столбца "Дашборд", а затем - соответствующая ячейка из добавленного столбца "Пустой".
На вкладке "Конструктор" нажмите "Изменить тип диаграммы" и задайте следующие настройки:
Для ряда "Рентабельность" сектор из колонки "Дашборд" сделайте прозрачным, а второй - залейте белым цветом, установив прозрачность в 25%.
Теперь добавьте на лист фигуру "Надпись". Кликните на ней левой кнопкой мыши и в строке формул введите ссылку на ячейку с рентабельностью столбца "Дашборд". Удалите у надписи заливку и границы, а цвет, размер и шрифт настройте под себя. Поместите надпись в центр кольцевой диаграммы, а затем сгруппируйте их в единый объект. Теперь при выборе любого варианта значение рентабельности будет показано в центре диаграммы.
Сделайте аналогичные диаграммы (только другого цвета) для показателей "Ликвидность" и "Надежность".
Организация дашборда
Когда диаграммы готовы, организуйте их в дашборд. На новом листе создайте большой белый прямоугольник ("Вставка" - "Фигуры"), в котором мы и разместим все элементы. Кликните по нему правой кнопкой мыши и выберите "На задний план" Перенесите с предыдущего листа на новый все диаграммы, а также переключатели и расположите на белом прямоугольнике.
Добавьте нужные заголовки с помощью фигуры "Надпись". Можно украсить всё это картинкой или значком из библиотеки ("Вставка" - "Значки"). Значки - векторные, и прекрасно масштабируются, но доступны только в новых версиях Excel.
Когда всё расположите, можете сгруппировать все объекты дашборда, чтобы его можно было быстро перемещать и масштабировать целиком, не двигая отдельные элементы по очереди. На выходе получаем вот такой простой, но интерактивный отчёт:
Оформление - дело вкуса. Главное, чтобы Вы усвоили все приемы, использованные при построении дашборда. Для закрепления можете изучить файл-пример. Он доступен по этой ссылке.
Поддержать наш проект и его дальнейшее развитие можно вот здесь.
Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot
С уважением, команда tDots.ru