Динамические гиперссылки между таблицами

Если вы хотя бы в общих чертах знакомы с функцией ВПР (VLOOKUP) (если нет, то сначала бегом сюда), то должны понимать, что эта и другие похожие на неё функции (ПРОСМОТРХ, ИНДЕКС и ПОИСКПОЗ, ВЫБОР и т.д.) всегда выдают в качестве результата значение - число, текст или дату, которые мы ищем в заданной таблице.

Но что, если вместо значения нам хочется получить живую гиперссылку, щёлкнув по которой мы могли бы мгновенно перепрыгнуть к найденному совпадению в другой таблице, чтобы посмотреть на него в общем контексте?

Предположим, что в качестве исходных данных у нас есть большая таблица по заказам для наших клиентов. Для удобства (хотя это и не обязательно) я преобразовал таблицу в динамическую "умную" сочетанием клавиш Ctrl+T и дал на вкладке Конструктор (Design) ей имя таблЗаказы:

Таблица заказов

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

Сводная таблица

Добавим в таблицу заказов столбец с формулой, которая ищет название клиента для текущего заказа на листе Сводная. Используем для этого классическую связку функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH):

Ищем название компании

Теперь завернём нашу формулу в функцию ЯЧЕЙКА (CELL), которую попросим вывести нам адрес найденной ячейки:

Определяем адрес каждого вхождения

И, наконец, вложим всё получившееся в функцию ГИПЕРССЫЛКА (HYPERLINK), которая в Microsoft Excel умеет создавать живую гиперссылку по заданному пути (адресу). Единственное, что неочевидно - придется приклеить к полученному адресу знак "решётки" (#) в начале, чтобы ссылка корректно воспринималась Excel как внутренняя (с листа на лист):

Преобразуем в гиперссылку

Теперь при щелчке мышью по любой из ссылок мы моментально перескочим на на ячейку с названием компании на листе со сводной таблицей.

Улучшение 1. Переход к нужному столбцу

Чтобы было совсем хорошо, немного усовершенствуем нашу формулу, чтобы переход происходил не названию клиента, а к конкретному числовому значению именно в том столбце-месяце, когда выполнен соответствующий заказ. Для этого придётся вспомнить, что функция ИНДЕКС (INDEX) в Excel очень многоплановая и может использоваться, в том числе, и в формате:

=ИНДЕКС( Двумерный_диапазон; Номер_строки; Номер_столбца )

То есть в качестве первого аргумента мы можем указать не столбец с названиями компаний в сводной, а всю область данных сводной таблицы, а в качестве третьего аргумента дописать номер нужного нам столбца. Его легко можно вычислить функцией МЕСЯЦ (MONTH), возвращающей номер месяца для даты сделки:

Добавляем переход к столбцу

Улучшение 2. Красивый символ ссылки

Второй аргумент функции ГИПЕРССЫЛКА - текст, который отображаться в ячейке со ссылкой - можно сделать посимпатичнее, если использовать вместо банальных знаков ">>" нестандартные символы из шрифтов Windings, Webdings и им подобных. Для этого можно использовать функцию СИМВОЛ (CHAR), которая умеет отображать символы по их коду.

Так, например, символ с кодом 56 в шрифте Webdings даст нам красивую двойную стрелку для гиперссылки:

Добавляем красивый значок ссылки

Улучшение 3. Подсветка текущей строки и активной ячейки

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

Для этого щёлкнем правой кнопкой мыши по ярлычку листа Сводная и выберем команду Просмотреть код (View code). В открывшееся окно редактора Visual Basic вставим следующий код:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Cells.Interior.ColorIndex = -4142
    Cells(ActiveCell.Row, 1).Resize(1, 14).Interior.ColorIndex = 6
    ActiveCell.Interior.ColorIndex = 44
End Sub

Как легко сообразить, здесь мы сначала убираем заливку со всего листа, а затем заливаем желтым (код цвета 6) всю строку в сводной, а потом оранжевым (код 44) текущую ячейку.

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

Подсветка ячейки и строки перехода по ссылке

Красота :)

P.S. Только не забудьте сохранить файл в формате с поддержкой макросов (xlsm или xlsb).

Ссылки по теме




01.07.2021 06:22:29
Здравствуйте. Хороший прием на заметку.
02.07.2021 07:08:59
Спасибо!
Отличная статья и видео!

Попытался дополнить макрос, чтобы цветом выделялось перекрестье строки и столбца.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Cells.Interior.ColorIndex = -4142
   Cells(ActiveCell.Row, 1).Resize(1, 25).Interior.ColorIndex = 6
   Cells(ActiveCell.Column, 10).Resize(10000, 1).Interior.ColorIndex = 6
   ActiveCell.Interior.ColorIndex = 44
End Sub

Но при этом выделение столбца происходит с 10-й строки. Что я делаю не так?
А в идеале было бы выделять столбец только до строки с выделенной ячейкой.
02.07.2021 11:08:42
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
lLastCol = Cells(4, Columns.Count).End(xlToLeft).Column

  Cells.Interior.ColorIndex = -4142
  Cells(ActiveCell.Row, 1).Resize(1, lLastCol).Interior.ColorIndex = 6
  Cells(1, ActiveCell.Column).Resize(lLastRow, 1).Interior.ColorIndex = 6
  ActiveCell.Interior.ColorIndex = 44
End Sub
02.07.2021 12:35:23
Вы просто волшебник! ;)
Спасибо!
13.07.2021 17:32:20
Добрый день! Хороший прием. У меня в работе для таких целей снята галочка "Разрешить редактирование в ячейке" и для удобства вынес на кнопку макрос-переключатель этой галочки
20.07.2021 18:03:27
Борис, а как это вам помогает в работе с гиперссылками?
20.07.2021 18:12:10
Помогает перемещаться на исходную ячейку при двойном клике, правда только в том случае, когда в ячейке идет ссылка на определенные ячейки с других листов
18.08.2021 10:11:53
Николай, добрый день.
В своих файлах использую похожий прием из более ранней статьи
динам.гиперссылка
Скажите, есть ли в них какое-то критическое различие? Или просто удобство т.к нет надобности создавать именованную переменную?

По крайней мере это основное, что бросилось в глаза в плане отличий.
Наверх