Применение ролловер эффекта в отчетах Excel

Как я уже писал в одной из предыдущих статей, ролловер эффект — это визуальное выделение объекта при наведении на него мышкой. Данный эффект вы можете наблюдать на большинстве веб сайтов или приложений, когда пытаетесь пользоваться, например, навигацией (меню) сайта. При наведении курсора мышки на один из элементов, поле подсвечивается или возникает еще одно выпадающее меню. Таким образом сайт реагирует на наши действия и возникает ощущение отклика программы. К сожалению, подобный механизм отсутствует в Excel, но его можно легко реализовать с помощью функции ГИПЕРССЫЛКА и небольшого макроса.

Читатель блога Exceltip, Марьян Лынив, прислал свой интересный способ внедрения ролловер эффекта в отчеты Excel, который я с удовольствием публикую.

Доброго времени суток.

В этой статье я хотел бы рассказать, как с помощью ролловер эффекта визуализировать отчет, скажем, по  продажам. Результат будет примерно вот таким:

ролловер эффект excel

Ну-с, поехали…

Есть у нас данные о продажах с нескольких филиалов в разрезе торговых представителей за определенный отрезок времени.

ролловер эффект excel

Открываем VBA редактор и создаем две пользовательские функции и две процедуры:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Function RollOverEffect1(OurRange As Range)
Dim i As Integer
i = OurRange.Row
If Cells(1, 15) <> i Then Cells(1, 15) = i 'вытягиваем номер строки, на которую наведен курсор

Range(Cells(1, 16), Cells(1, 21)).Value = _
Range(Cells(i, 7), Cells(i, 12)).Value 'вытягиваем данные для построения диаграммы

Call ShowChart 'вызываем процедуру отображения диаграммы
End Function

Function RollOverEffect2(OurRange As Range)
Call HideChart 'вызываем процедуру скрытия диаграммы
End Function

Sub ShowChart()
With ActiveWindow.VisibleRange
ActiveSheet.ChartObjects("Диаграмма").Top = .Top 'определяем фиксированное положение диаграммы
End With                                                                                'по вертикали
ActiveSheet.ChartObjects("Диаграмма").Visible = True 'отображаем диаграммы
End Sub

Sub HideChart()
ActiveSheet.ChartObjects("Диаграмма").Visible = False
End Sub

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

Теперь нам нужно, грубо говоря, скопировать нашу таблицу с помощью функции ГИПЕРССЫЛКА. Делаем это следующим образом. В первую пустую строку после таблицы, где будут содержаться имена филиалов и торговых представителей (желтый диапазон) записываем формулу:

=ЕСЛИОШИБКА(ГИПЕРССЫЛКА(RollOverEffect1(A3);A3);A3)

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

=ЕСЛИОШИБКА(ГИПЕРССЫЛКА(RollOverEffect2(B1);B1);B1)

Во всех остальных ячейках, которые являются пустыми и прилегают к таблице-дубликату (красный диапазон), формула будет такова (это делается для того, чтобы при выходе курсора за пределы таблицы диаграмма исчезала):

=ЕСЛИОШИБКА(ГИПЕРССЫЛКА(RollOverEffect2(A40);"");"")

Вот что должно получится:

ролловер эффект excel

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

Следующий наш шаг – определение условного форматирования для подсветки ячеек. Выделяем необходимый диапазон, выбираем условное форматирование с использованием формулы, пишем формулу "= СТРОКА(G2)=$O$1" (G2 – первая ячейка диапазона; $O$1 – ячейка с номером строки, которую мы получаем с помощью функции RollOverEffect1) и выбираем форматирование.

Остались последние штрихи – скрыть значения ячеек, которые заполняет функция RollOverEffect1, на пример, сделаем одинаковыми цвет текста и фона. И напоследок маленький совет – если текст в ячейке с ролловер эффектом очень короткий, эффект мы получим только когда наведем курсор именно на текст; этот момент легко можно упразднить установив для этих ячеек перенос текста.

Скачать файл с примером внедрения ролловер эффекта в отчете Excel.

Друзья, если у вас есть интересные способы применения трюков и хитростей в Excel, не стесняйтесь, присылайте свои статьи мне на ящик exceltip[гав-гав]mail.ru, мы их обязательно опубликуем.


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

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

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

  2. Здравствуйте, Ренат!

    У меня есть 2 вопроса.
    1. Возможно ли применить ролловер-эффект для диаграммы? Необходимо чтобы один из столбцов гистограммы подсвечивался при наведении курсора на него. Например, на показатель 2013 г.
    2. Возможно ли использование двух гиперссылок для одной ячейки. Т.е. необходимо чтобы при нажатии на ячейку осуществлялся переход на другой лист и при этом работал ролловер-эффект для ячеек?

      • Спасибо! По интерактивной диаграмме то что нужно. Только, к сожалению, в данном примере необходимо, чтобы диаграмма была постоянно активной. Т.е. в случае когда диаграмм несколько нужно переключаться между ними, чтобы получить нужный эффект + эффект притормаживает)) Просто пытаюсь сделать дашборд, при этом хотелось бы сделать его интерактивным. Что-то в виде вот этого https://gallery.idashboards.com/preview/?guestuser=webhospitality&dashID=66

    • Здравствуйте, spirit_MU.
      По поводу Вашего второго вопроса, думаю, это осуществить можно, но только при двойном щелчке мыши. Для этого необходимо для Вашего листа создать событие BeforeDoubleClick. Вот простенький пример.

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

      Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
      Dim WorkRange As Range, CheckRange As Range
      Dim i As Integer
      Set WorkRange = Range(Cells(1, 1), Cells(7, 1))
      Set CheckRange = Intersect(WorkRange, Target)
      If Not (CheckRange Is Nothing) Then
      i = Target.Row
      Sheets(i).Activate
      End If
      End Sub

      P.S. Можно было бы сделать и при одном щелчке используя событие SelectionChange, но, к сожалению, данное событие не работает, если в ячейке есть гиперссылка.

  3. Доброе время суток)
    Хочу сделать ролловер по вашему примеру, делаю все по инструкции но еффект не получается и не могу понять почему. У меня даже не виходит отобразить диапазон с данними для построения графика не говоря уже обо всем остальном. записываю макрос как в вашем примере,но фокус не получается
    Помогите понять как это сделать.
    Возможно можна вам отправить файл со своей работой, чтоб вы посмотрели и помогли мне?

    • Добрый день, Маръяна. В таких ситуациях мне обычно помогает файл с примером, используемого трюка. Попробуйте скачать файл и разобраться на практике, как он работает. Если не поможет, будем по шагам разбирать, что у вас не получается)

      • Здраствуйте, Ренат, я с самого начала скачала файл с примером и уже на его основании досих пор пытаюсь сделать так же. Но у меня не получается.
        Роботу начала с того, что сделала исходную таблицу и затем с помощью функций гиперссылки построила таблицу на основание которой и должен получаться эффект. Затем я скрыла исходную таблицу и скопировала макрос из вашего примера в свою роботу. (Название моего рабочего листа такое же как у Вас)
        Следующим этапом моей работы было условное форматирования ячеек Р5:U5, G3:L3, G2:L2, М19, G3:L35, как у Вас.
        Область построения графика Р5:U5 (название графика как в макросе)…но ничего не происходит, график не работает, данные не отображаются.
        У меня даже не получается сделать выделение синим цветом при наведении мишки на стоку таблицы.

  4. Огромное Вам СПАСИБО!!!!!
    Я наконец поняла что я делала не так почти месяц!!!
    Ище один вопрос: подскажите пожалуйста как сделать подсветку ячеек таблицы при наведении курсора мышки другим цветом?

  5. Добрый день!
    не могу разобраться с пунктом «строим диаграмму на основе того диапазона, который нам дает наша функция RollOverEffect1»
    Где отображается этот диапазон? в каких ячейках должны появляться данные?
    Расскажите пожалуйста поподробнее (или может быть подскажете где почитать)?

    • Добрый день, Юлия. Задача функции RollOverEffect1 — вытянуть данные из строки таблицы, на которую наведен курсор, и поместить их на определенный диапазон на листе. В нашем случае — это диапазон P1:U1, который является источником данных для диаграммы. То есть, каждый раз, когда курсор перемещается на следующую строку таблицы, значения диапазона P1:U1 меняются, соответственно и меняются данные для диаграммы.

  6. Добрый день

    Подскажите функцию, что бы диаграмма строилась на основании данных выбранной строки (Не навели курсора, а встали курсором (1 клик))

    Как называется то действие? Где можно почитать и посмотреть пример.

    Заранее спасибо.

  7. Здравствуйте, очень крутая статья, но для меня сложновата пока) У меня цель гораздо проще — при наведении курсора на ячейку, хотелось бы чтобы отображалась некоторая область (таблица) с другого листа этого же excel-документа (типа комментариев в виде таблицы). Тема с примечаниями к ячейке в виде рисунков мне не оч подходит. Хотелось бы элегантное решение в виде макроса, могу ли я как-то использовать команды из предложенного Вами макроса? пытаюсь понять куда двигаться…

  8. Здравствуйте
    Как понять какое имя у диаграммы?
    Перенёс процедуру в другой файл не хочет скрывать диаграмму
    Всё время ошибка Не удаётся открыть указанный файл

  9. Здравствуйте. Экспериментирую с Роллер эффектом. Всё описанное в статье работает. Круто! Но есть и не описанные в статье чудеса.
    Есть проблема. В дальнейшем с моей программой буду работать не только я. Не я — те, которые потом, будут не только елозить по ячейкам, но и жать на левую кнопу мыши, что вызывает ошибку. У меня не получается её перехватить.
    Ответьте, пожалуйста, в любом случае: знаете, как обойти шнягу? Есть ли варианты? Тема крайне интересна, но пока в моём случае сыра.
    Работаю на 2003.
    С уважением.

  10. Добрый день,

    Спасибо за помощь. Было бы здорово если бы еще эта формула была бы на англиском так как у кого Microsoft на анг. языке эти скрипты не работают.

Добавить комментарий

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