Содержание:
Если вы хотя бы знакомы с функцией ВПР (ВПР) (если нет, то сначала запустите здесь), тогда вы должны понимать, что эта и другие подобные ей функции (ПРОСМОТР, ИНДЕКС и ПОИСК, ВЫБОР и т.д.) всегда выдают в результате ценностное — число, текст или дата, которые мы ищем в данной таблице.
А что, если вместо значения мы хотим получить живую гиперссылку, нажав на которую, мы могли бы моментально перейти к найденному совпадению в другой таблице, чтобы просмотреть ее в общем контексте?
Допустим, у нас есть большая таблица заказов для наших клиентов в качестве входных данных. Для удобства (хотя это и не обязательно) я преобразовал таблицу в динамическое «умное» сочетание клавиш Ctrl+T и дал на счет Конструктор (Дизайн) ее имя tabOrders:
На отдельном листе Консолидированный Я построил сводную таблицу (хотя это не обязательно должна быть именно сводная таблица — в принципе подойдет любая таблица), где по исходным данным рассчитывается динамика продаж по месяцам для каждого клиента:
Добавим в таблицу заказов столбец с формулой, которая ищет на листе имя клиента текущего заказа. Консолидированный. Для этого воспользуемся классическим набором функций ИНДЕКС (ПОКАЗАТЕЛЬ) и БОЛЬШЕ ОТКРЫТОГО (СООТВЕТСТВОВАТЬ):
Теперь давайте обернем нашу формулу функцией CELL (КЛЕТКА), который мы попросим отобразить адрес найденной ячейки:
И наконец, все, что получилось, помещаем в функцию HYPERLINK (ГИПЕРССЫЛКА), который в Microsoft Excel может создавать активную гиперссылку на заданный путь (адрес). Единственное, что не очевидно, так это то, что к полученному адресу придется в начале приклеить решетку (#), чтобы ссылка корректно воспринималась Excel как внутренняя (с листа на лист):
Теперь при нажатии на любую из ссылок мы моментально перепрыгнем на ячейку с названием компании на листе со сводной таблицей.
Чтобы было по-настоящему хорошо, давайте немного улучшим нашу формулу, чтобы переход происходил не на имя клиента, а на конкретное числовое значение именно в столбце месяца, когда соответствующий заказ был выполнен. Для этого надо помнить, что функция ИНДЕКС (ПОКАЗАТЕЛЬ) в Excel очень универсален и может использоваться, в том числе, в формате:
= ИНДЕКС ( XNUMXD_диапазон; Номер строки; Номер_столбца )
То есть в качестве первого аргумента мы можем указать не столбец с названиями компаний в сводной таблице, а всю область данных сводной таблицы, а в качестве третьего аргумента добавить номер нужного нам столбца. Его легко вычислить по функции МЕСЯЦ (МЕСЯЦ), который возвращает номер месяца для даты сделки:
Улучшение 2. Красивый символ ссылки.
Второй аргумент функции HYPERLINK — текст, который отображается в ячейке со ссылкой — можно сделать красивее, если вместо банальных знаков «>>» использовать нестандартные символы из шрифтов Windings, Webdings и им подобных. Для этого вы можете использовать функцию SYMBOL (СИМВОЛ), который может отображать символы по их коду.
Так, например, код символа 56 в шрифте Webdings даст нам красивую двойную стрелку для гиперссылки:
Улучшение 3. Выделение текущей строки и активной ячейки.
Ну а для окончательной победы красоты над здравым смыслом можно еще прикрепить к нашему файлу упрощенный вариант выделения текущей строки и ячейки, на которую переходим по ссылке. Для этого потребуется простой макрос, который мы повесим для обработки события изменения выделения на листе. Консолидированный.
Для этого щелкните правой кнопкой мыши на вкладке листа Сводка и выберите команду Вид код (Вид код). Вставьте следующий код в открывшееся окно редактора 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) текущей ячейкой.
Теперь при выделении любой ячейки внутри ячейки сводки (неважно — вручную или в результате нажатия на нашу гиперссылку) будет подсвечена вся строка и ячейка с нужным нам месяцем:
Красота 🙂
PS Просто не забудьте сохранить файл в формате с поддержкой макросов (xlsm или xlsb).
- Создание внешних и внутренних ссылок с помощью функции ГИПЕРССЫЛКА
- Создание электронных писем с помощью функции ГИПЕРССЫЛКА