Excel дашборд по обслуживанию клиентов — добавление макросов [часть 4 из 4]

Наконец дошли у меня руки закончить начатую серию. В сегодняшней, финальной части нашего руководства по созданию дашборда для сервиса по обслуживанию клиентов мы улучшим юзабилити файла. Рассмотрим, как добавлять макросы и 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: Отображение и скрытие надписей с помощью

Добавление возможности помощи в сложных дашбордах делает жизнь конечных пользователей легче. Но как это сделать?

Все просто, достаточно прочитать мантры и следовать шагам ниже:

  1. Добавляете надписи помощи в ваш дашборд, которые находятся во вкладке Вставка в группе Иллюстрации -> Фигуры -> Выноски.
  2. После того, как все выноски будут добавлены, группируете их, для это выделяете все выноски, щелкаете правой кнопкой, из выпадающего меню выбираете Группировать.
  3. Даете данной группе имя boxHelp
  4. В редакторе макросов в новом модуле пишем макрос (пусть он будет называться showHideHelp), который будет скрывать или показывать группу boxHelp.
  5. Добавляем небольшое текстовое поле с надписью Помощь на лист с дашбордом.
  6. К созданной фигуре назначаем макрос (правый щелчок на группе выносок -> Назначить макрос).

Так что же мы написали в макросе 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 дашбордов. Статьи по теме:



10 комментариев

  1. не совсем понятно за счет чего происходит смена выражений «показать помощь» «скрыть помощь»? Если сделать все по инструкции, то выражение в поле не меняется.

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

  3. Добрый день, Ренат.
    Я новичок, хотела попробовать создать свой дашборт. Изучить самостоятельно написание макроса на описанном вами примере, но к сожалению пройдя по ссылке -скачайте финальный дашборд сервиса по обслуживанию клиентов, файл отображается не корректно, что-то вроде того (см. ниже).
    Была бы признательна, если вы помогли бы с этой проблемой.

    Спасибо.

    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�

  4. Ренат, добрый день!

    Подскажите, пожалуйста, как Вы сделали лист так, чтобы на нем отображалось только содержимое дашборда, а остальные столбцы не были видны?

Добавить комментарий для Елена Отменить ответ

Ваш e-mail не будет опубликован. Обязательные поля помечены *