Динамические гиперссылки между таблицами
Если вы хотя бы в общих чертах знакомы с функцией ВПР (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).
Ссылки по теме
- Создание внешних и внутренних ссылок функцией ГИПЕРССЫЛКА (HYPERLINK)
- Создание писем функцией ГИПЕРССЫЛКА
Отличная статья и видео!
Попытался дополнить макрос, чтобы цветом выделялось перекрестье строки и столбца.
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-й строки. Что я делаю не так?
А в идеале было бы выделять столбец только до строки с выделенной ячейкой.
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
Спасибо!
В своих файлах использую похожий прием из более ранней статьи
Скажите, есть ли в них какое-то критическое различие? Или просто удобство т.к нет надобности создавать именованную переменную?
По крайней мере это основное, что бросилось в глаза в плане отличий.
Как добавить ограничение на срабатывание макроса (Улучшение 3), чтобы выделялась только строка в определенном диапазоне, например в умной таблице?
Как я понимаю нужна дополнительная проверка, что активная ячейка находится в заданном диапазоне, но не пойму как это прописать.
Разобрался:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("тСПИСОК")) Is Nothing Then 'проверка, что ячейка входит в диапазон
Cells.Interior.ColorIndex = -4142
Cells(ActiveCell.Row, 1).Resize(1, 14).Interior.ColorIndex = 6
ActiveCell.Interior.ColorIndex = 44
End If
End Sub
И если заменить
Resize(1, 14) на Resize(1, Range("тСПИСОК").Columns.Count), то он будет динамически выделить только то количество столбцов, которое содержится в таблице.