Динамическая гиперссылка

Постановка задачи

Имеем две таблицы на разных листах одной книги: таблицу с заказами от клиентов (на листе Заказы) и таблицу с клиентской базой (лист Клиенты). Работая с таблицей заказов, хочется иметь возможность быстро переходить на таблицу с клиентами, чтобы просмотреть подробные данные по клиенту (адрес, ФИО директора и т.д.). То есть, другими словами, хочется в таблице заказов иметь гиперссылку в каждой строке, при щелчке мышью по которой будет происходить переход на лист Клиенты, причем именно на ту строчку где упоминается данный клиент:

match-with-hyperlinks1.png

Что-то типа типа функции ВПР (VLOOKUP), но не ради подстановки данных, а для быстрой ссылки из одной таблицы в другую.

Шаг 1. Создаем переменную с именем листа

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

В Excel 2007/2010 для этого можно воспользоваться вкладкой Формулы (Formulas) и кнопкой Диспетчер имен (Name Manager). В более старых версиях выбрать в меню Вставка - Имя - Присвоить (Insert - Name - Define). В открывшемся окне нажмите кнопку Создать (New) и введите туда имя переменной (я назвал ее для примера Мой_Лист) и формулу в строку Диапазон (Reference):

match-with-hyperlinks2.png

=ПСТР(ЯЧЕЙКА("имяфайла";Клиенты!$A$1);ПОИСК("[";ЯЧЕЙКА("имяфайла";Клиенты!$A$1));256)&"!"

=MID(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1)),256)&"!"

Разберем эту конструкцию на составляющие для понятности:

  1. ЯЧЕЙКА("имяфайла";Клиенты!$A$1) - функция, которая по адресу заданной ячейки (А1 с листа Клиенты) выдает любые нужные данные по листу и файлу. В данном случае - полный путь к текущему файлу до листа в виде D:\Рабочие документы\Договоры[Бюджет.xls]Клиенты
  2. Из этой строки нам нужна только часть с именем файла и листа (без диска и папок), поэтому мы ищем первое вхождение квадратной открывающей скобки в строку с помощью функции ПОИСК (FIND) и затем вырезаем из строки все, начиная с этого символа и до конца (256 символов) с помощью функции ПСТР(MID).
  3. В конце, к вырезанному фрагменту с именем файла и листа приклеиваем восклицательный знак - стандартный разделитель имен листов и адресов ячеек в формулах, т.к. дальше должны будут идти адреса ячеек.

Таким образом эта формула выдает на выходе имя текущего файла в квадратных скобках с расширением с приклееным к нему именем листа и восклицательным знаком. Работу формулы легко проверить - просто введите в любую пустую ячейку =Мой_Лист и нажмите клавишу Enter.

Шаг 2. Создаем гиперссылки

Выделите пустую ячейку в строке напротив первого заказа и введите туда вот такую формулу:

match-with-hyperlinks3.png

=ГИПЕРССЫЛКА(Мой_Лист&АДРЕС(ПОИСКПОЗ(B2;Клиенты!$A$1:$A$7;0);1);">>")

=HYPERLINK(Мой_Лист&ADDRESS(MATCH(B2,Клиенты!$A$1:$A$7,0),1),">>")

Разберем ее на составляющие аналогичным образом:

  1. Функция ПОИСКПОЗ(B2;Клиенты!$A$1:$A$7;0) - ищет порядковый номер ячейки в диапазоне А1:А7 на листе Клиенты, где встречается название текущего клиента из B2 (последний аргумент =0 означает поиск точного совпадения, аналогично функции ВПР)
  2. Функция АДРЕС формирует адрес ячейки (в виде текстовой строки) по номеру строки и столбца, т.е. адрес ячейки с нужным клиентом, куда должна потом ссылаться гиперссылка
  3. Затем мы приклеиваем к адресу ссылку на файл и лист (переменную Мой_Лист) и используем это в качестве аргумента для функции ГИПЕРССЫЛКА (HYPERLINK), которая, собственно, и создает нужную нам ссылку.

При желании, можно заменить внешнее представление гиперссылки с банальных символов ">>" на что-нибудь поинтереснее с помощью функции СИМВОЛ (CHAR), которая умеет выводить нестандартные символы по их кодам:

=ГИПЕРССЫЛКА(Мой_Лист&АДРЕС(ПОИСКПОЗ(B2;Клиенты!$A$1:$A$7;0);1);СИМВОЛ(117))

Так, например, если использовать шрифт Wingdings 3 и символ с кодом 117, то можно получить вот такие симпатичные значки гиперссылок:

match-with-hyperlinks4.png

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

 



Александр
15.09.2012 11:29:38
Вариант формулы:
=ГИПЕРССЫЛКА(ПОДСТАВИТЬ(ЯЧЕЙКА("адрес";ИНДЕКС(Клиенты!$A$1:$A$7;ПОИСКПОЗ(B2;Клиенты!$A$1:$A$7;0)));"'";"");">>").
Денис
15.09.2012 11:30:48
Одно очень важное замечание, имя листа, на который нужно перейти, не должно содержать пробелов!
З.Ы.: Долго искал почему у меня выдавал ошибку "неверная ссылка"..
Shtopar
15.09.2012 11:35:48
Чтобы сослаться на имя с пробелами и другими знаками необходимо выделить лист с двух сторон одинарной кавычкой
пример: ='Лист (1)!'
Катерина
15.09.2012 11:31:31
А если лист "Клиенты" находится в другой книге?
Николай Павлов
15.09.2012 11:32:44
Тогда надо скорректировать формулу в именованном диапазоне, чтобы в квадратных скобках было имя (путь) не текущего файла, а того, куда вы хотите сослаться. Надо поэкспериментировать ;).
Александр
15.09.2012 11:33:59
Т.е. нужен полный путь к файлу, как я понимаю?
Boris
20.09.2012 18:01:22
Ну конечно!
:D
Иван
20.09.2012 18:02:35
Спасибо!
24.09.2012 17:53:57
Не за что! :)
23.01.2013 23:08:00
Николай, функция ЯЧЕЙКА выдает адрес в виде '[имя_файла]Лист1!'$A$1 (все кавычки и знаки, как в оригинале). Соответственно функция ГИПЕРССЫЛКА не работает, т.к. адрес некорректен (правильно [имя_файла]'Лист1!'$A$1). С чем это может быть связано и как "лечить"?
24.02.2013 13:34:22
Проблему решил сам. Вся хитрость в длине имени файла. Если имя слишком длинное, формула ЯЧЕЙКА работает некорректно.
05.08.2013 08:18:17
Сделал все как описано выше, за исключение того, что лист Клиенты находится на другой книге. Так вот, если эта вторая книга УЖЕ открыта,то переход работает. Если же нет, то не срабатывает.Есть способы как решить такую задачу?
06.08.2013 16:30:43
Доброго времени суток!
У меня вот тут чуть другая ситуация: на одном листе есть три переключателя, на нем надо сделать гиперссылку, которая в зависимости от того какой из них включен даст переход на соответствующий лист.
как это можно сделать?
14.08.2013 11:40:13
Если имя листа, на который нужно перейти, содержит пробелы, то можно обойти это следующим образом (допустим, лист называется "Важные Клиенты").
=ГИПЕРССЫЛКА(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ЯЧЕЙКА("адрес";ИНДЕКС('Важные Клиенты'!$A$1:$A$7;ПОИСКПОЗ(B2;'Важные Клиенты'!$A$1:$A$7;0)));"'[";"[");"]";"]'");">>")
29.08.2015 23:52:46
Будет ли переход по ссылке именно на нужного клиента, если на листе со списком клиентов применить автофильтр (сортировку), в результате которого клиенты поменяются местами и, соответственно, клиент Иван Иваныч, находясь до сортировки в ячейке А1, после сортировки оказался в ячейке А121?
30.08.2015 15:16:17
Мдааа... Сделал всё, как у вас, однако получается не то, что надо:
По ссылке, рядом с клиентом "Казанский арматурный завод" переход осуществляется на лист "Клиенты" на ячейку "ООО Пневмостроймашина".
Целый день пытаюсь понять причину, но в описании всё так непонятно написано, что разберётся только профессионал. ИЗ файла с примером тоже непонятно, почему там всё нормально происходит, а в моём файле - переход осуществляется на вообще не те ячейки
06.05.2016 09:31:42
Не могу понять опчему не работает данный метод у меня!Уже скопировал пример, и все равно не работает...Помогите мне вправить мозги, в чем может быть дело!
15.07.2016 09:55:12
подскажите, почему у меня при нажатии на гиперссылку выдает сообщение - "Не удается открыть указанный файл"?
вот моя ссылка:
ЕСЛИОШИБКА(ГИПЕРССЫЛКА("C:\Documents and Settings\m.dereberya\Рабочий стол\HR\18 РАСЧЕТНЫЕ ВЕДОМОСТИ\2013 ГОД\Все_в_одном2013.xlsx"& АДРЕС(ПОИСКПОЗ(A2;[Все_в_одном.xlsx]TDSheet!$A$7:$A$1420;0);1);СИМВОЛ(62));"не работал")
_спасибо)
12.08.2016 17:11:45
Все великолепно работает в Excel.  Подскажите пожалуйста, а можно ли как-то это реализовать без создания переменной?
Хочу попробовать реализовать этот прием в рамках одной книги - чтобы на одном листе была гиперссылка, которая переходила бы на другой лист и сразу же около нужной ячейки из диапазона на другом листе в этой же книге?
Хочу попробовать реализовать это в гугл таблице.
23.12.2016 21:28:28
Формула без всяких переменных в комменте №1 от Александра по-русски. Или по-английски:
=HYPERLINK(CELL("address";INDEX(Клиенты!$A$1:$A$7;MATCH(B2;Клиенты!$A$1:$A$7); ));">>";)
17.02.2017 12:58:40
А если количество листов составляет 3 или 4 шт.?
25.12.2017 17:48:38
Как сделать кнопку-ссылку на определенный лист другого файла?
Наверх