Наконец дошли у меня руки закончить начатую серию. В сегодняшней, финальной части нашего руководства по созданию дашборда для сервиса по обслуживанию клиентов мы улучшим юзабилити файла. Рассмотрим, как добавлять макросы и VBA код, который сделает наш дашборд более интерактивным.
Если вы пропустили предыдущие шаги по созданию дашборда, вы можете ознакомиться с ними в следующих статьях:
- дизайн дашборда по обслуживанию клиентов
- система данных и расчетов для дашборда
- создание графиков и визуализация дашборда
- макросы и финальные штрихи к дашборду
Вы, наверное, уже заметили, что в нашем дашборде присутствует два основных макроса:
Макрос №1: Выделение выбранных элементов
Когда пользователь щелкает по элементу для сравнения, запускается небольшой макрос, который сообщает нам, какой элемент выбран. Тем самым, мы можем запустить пересчет и условное форматирование. Как это работает? Проще, чем вы думаете) Мы используем макрос под названием Worksheet_SelectionChange.
Что такое макрос событий?
В Excel существует специальный вид макросов, называемый макросом событий (или проще События). К примеру, если вы хотите произвести, какое-нибудь действие, когда пользователь выбрал ячейку D14, вы можете воспользоваться макросом событий. Excel определяет различные события, так что мы можем провернуть определенные действия, когда пользователь выбрал ячейку, щелкнул по гиперссылке, активировал рабочую книгу, обновил сводную таблицу и т.д.
В нашем случае, нам требуется изменить параметры сравнения, основанные выборе пользователя. Поэтому мы используем событие Worksheet_SelectionChange.
Когда вы добавляете макрос на событие изменения выбора в рабочий лист, Excel будет запускать его каждый раз, когда вы будете выбирать другую ячейку. Давайте посмотрим простой пример демонстрации такого выбора:
Текст кода для вышеописанного события выглядит следующим образом:
1 2 3 | Private Sub Worksheet_SelectionChange(ByVal Target As Range) [valSelection] = "Вы выбрали " & Target.Address End Sub |
Ячейка valSelection связана с текстовым полем, изображенным на демонстрации.
События в дашборде обслуживания клиентов
В нашем дашборде у нас есть небольшое дополнение – макрос необходимо запускать только в случаях, когда выбран один из элементов списка сравнения, находящихся в диапазонах rngSel1 и rngSel2.
Вот где мы воспользуемся дополнительными возможностями VBA – формулой Application.intersect(), которая проверяет, пересекаются ли два диапазона и возвращает диапазон пересечения.
Давайте взглянем на наш макрос событий:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Данный макрос запускается, когда выбирается любая ячейка в дашборде ' Шаг 1: Если пользователь щелкнул на пустую ячейку, ничего не происходит If ActiveCell.Value = "" Then Exit Sub ' Шаг 2: Проверяем, пересекается ли выбранный диапазон со списком слева If Not (Application.Intersect(ActiveCell, Range("rngSel1").Cells) Is Nothing) Then ' Если да, вызываем макрос setOption1 Call setOption1 ' Шаг 3: Проверяем, пересекается ли выбранный диапазон со списком справа ElseIf Not (Application.Intersect(ActiveCell, Range("rngSel2").Cells) Is Nothing) Then ' Если да, вызываем макрос setOption2 Call setOption2 End If End Sub |
Почитав комментарии, все становится очевидно
Макрос №2: Отображение и скрытие надписей с помощью
Добавление возможности помощи в сложных дашбордах делает жизнь конечных пользователей легче. Но как это сделать?
Все просто, достаточно прочитать мантры и следовать шагам ниже:
- Добавляете надписи помощи в ваш дашборд, которые находятся во вкладке Вставка в группе Иллюстрации -> Фигуры -> Выноски.
- После того, как все выноски будут добавлены, группируете их, для это выделяете все выноски, щелкаете правой кнопкой, из выпадающего меню выбираете Группировать.
- Даете данной группе имя boxHelp
- В редакторе макросов в новом модуле пишем макрос (пусть он будет называться showHideHelp), который будет скрывать или показывать группу boxHelp.
- Добавляем небольшое текстовое поле с надписью Помощь на лист с дашбордом.
- К созданной фигуре назначаем макрос (правый щелчок на группе выносок -> Назначить макрос).
Так что же мы написали в макросе showHideHelp?
Просто. Когда пользователь щелкнет по текстовому полю Помощь, мы переключим видимость группы boxHelp с помощью следующего кода:
1 2 | ActiveSheet.Shapes.Range(Array("boxHelp")).Visible = Not ActiveSheet.Shapes.Range(Array("boxHelp")).Visible |
Приставка NOT переключает видимость группы, если видимый, то скрывает, если скрытый, то отображает.
Скачать дашборд сервиса по обслуживанию клиентов
Чтобы лучше разобраться с описанными здесь макросами, скачайте финальный дашборд сервиса по обслуживанию клиентов. Откройте редактор макросов и изучите написанный VBA код.
Дальнейшее развитие дашборда
Мне очень нравится то, что у нас получилось, тем не менее, можно дополнить наш дашборд следующими возможностями:
- Использовать срезы Excel 2010, чтобы сделать выбор элементов в поле сравнений
- Добавить возможность экспорта дашборда в формат PDF
- Добавит возможность комментирования, чтобы понимать, что послужило причиной изменения тренда в ту или иную сторону.
- Добавить возможность настройки периода времени. Мы создали дашборд, который отображает данные за 4 недели. Я думаю, что дашборд был бы гибче, если б можно было указывать дату начала и окончания.
Что еще можно почитать про дашборды?
Если вы ищете примеры, мануалы и руководства по созданию дашбордов, вы находитесь там, где нужно. На ресурсе Exceltip.ru собраны пошаговые инструкций и различные шаблоны, чтобы стать профессионалом в создании Excel дашбордов. Статьи по теме:
- Руководство по созданию дашборда с показателями эффективности (KPI) из 6 частей
- Динамические диаграммы
- Создание дашборда с помощью надстройки PowerView
- Создание дашборда с помощью Visio
не совсем понятно за счет чего происходит смена выражений «показать помощь» «скрыть помощь»? Если сделать все по инструкции, то выражение в поле не меняется.
Андрей, макрос дашборда меняет значение ячейки О3 на листе Расчеты в зависимости от видимости/скрытости группы комментариев на ноль или -1, далее работает обычная формула ЕСЛИ
Подскажите, пожалуйста, каким образом ячейка valSelection связана с текстовым полем внутри фигуры.
Тут фокус такой — любую форму, в которой можно вводить текст, можно связать с ячейкой, таким образом, чтобы текст написанный в ячейке отображался на фигуре, например, прямоугольнике. Делается это просто, выделяем фигуру и в строке функций вписываем адрес ячейки. Теперь любое значение ячейки будет отображаться в текстовом поле фигуры.
Спасибо, Ренат. Оказывается, все гениальное просто 🙂
Я тут нашел чуть более сложный метод с использованием макросов:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Shapes(«наша форма»).TextFrame.Characters.Text = «Вы выбрали » & Target.Address
End Sub
Да, все правильно, в вашем случае не требуется промежуточного этапа занесения данных в ячейку
Добрый день, Ренат.
Я новичок, хотела попробовать создать свой дашборт. Изучить самостоятельно написание макроса на описанном вами примере, но к сожалению пройдя по ссылке -скачайте финальный дашборд сервиса по обслуживанию клиентов, файл отображается не корректно, что-то вроде того (см. ниже).
Была бы признательна, если вы помогли бы с этой проблемой.
Спасибо.
K�����!�A�L������[Content_Types].xml �(����������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������̗�n�0����������0$��[�v��t�E�IM���|�1ۉ�m�؎B��?%��ԕL�`��jB��I@q�5��_O�ӯ$q���I�`BV��������ʀK�Z� )�7�(u����LP�Rh[1��vN
�6z=}�\+ʧ>� ���Y��Ưב̄»���w5!�)8�(]��IuQ����Μ��rW�Jf�
Елена, данный дашборд создавался в Excel 2013, скорее всего у вас более ранняя версия
Ренат, добрый день!
Подскажите, пожалуйста, как Вы сделали лист так, чтобы на нем отображалось только содержимое дашборда, а остальные столбцы не были видны?
Ренат, добрый день!
спасибо за пример! Я не понимаю, меняются цифры на листе расчеты в ячейках E3 и G3?
Подскажите пожалуйста.