Динамическая гиперссылка
Постановка задачи
Имеем две таблицы на разных листах одной книги: таблицу с заказами от клиентов (на листе Заказы) и таблицу с клиентской базой (лист Клиенты). Работая с таблицей заказов, хочется иметь возможность быстро переходить на таблицу с клиентами, чтобы просмотреть подробные данные по клиенту (адрес, ФИО директора и т.д.). То есть, другими словами, хочется в таблице заказов иметь гиперссылку в каждой строке, при щелчке мышью по которой будет происходить переход на лист Клиенты, причем именно на ту строчку где упоминается данный клиент:
Что-то типа типа функции ВПР (VLOOKUP), но не ради подстановки данных, а для быстрой ссылки из одной таблицы в другую.
Шаг 1. Создаем переменную с именем листа
Для создания гиперссылок в будущем нам понадобится каждый раз прописывать имя текущего файла и имя листа Клиенты, на который пойдут ссылки. Поэтому проще один раз создать переменную в памяти Excel с нужным значением и обращаться к ней потом по мере надобности.
В Excel 2007/2010 для этого можно воспользоваться вкладкой Формулы (Formulas) и кнопкой Диспетчер имен (Name Manager). В более старых версиях выбрать в меню Вставка - Имя - Присвоить (Insert - Name - Define). В открывшемся окне нажмите кнопку Создать (New) и введите туда имя переменной (я назвал ее для примера Мой_Лист) и формулу в строку Диапазон (Reference):
=ПСТР(ЯЧЕЙКА("имяфайла";Клиенты!$A$1);ПОИСК("[";ЯЧЕЙКА("имяфайла";Клиенты!$A$1));256)&"!"
=MID(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1)),256)&"!"
Разберем эту конструкцию на составляющие для понятности:
- ЯЧЕЙКА("имяфайла";Клиенты!$A$1) - функция, которая по адресу заданной ячейки (А1 с листа Клиенты) выдает любые нужные данные по листу и файлу. В данном случае - полный путь к текущему файлу до листа в виде D:\Рабочие документы\Договоры[Бюджет.xls]Клиенты
- Из этой строки нам нужна только часть с именем файла и листа (без диска и папок), поэтому мы ищем первое вхождение квадратной открывающей скобки в строку с помощью функции ПОИСК (FIND) и затем вырезаем из строки все, начиная с этого символа и до конца (256 символов) с помощью функции ПСТР(MID).
- В конце, к вырезанному фрагменту с именем файла и листа приклеиваем восклицательный знак - стандартный разделитель имен листов и адресов ячеек в формулах, т.к. дальше должны будут идти адреса ячеек.
Таким образом эта формула выдает на выходе имя текущего файла в квадратных скобках с расширением с приклееным к нему именем листа и восклицательным знаком. Работу формулы легко проверить - просто введите в любую пустую ячейку =Мой_Лист и нажмите клавишу Enter.
Шаг 2. Создаем гиперссылки
Выделите пустую ячейку в строке напротив первого заказа и введите туда вот такую формулу:
=ГИПЕРССЫЛКА(Мой_Лист&АДРЕС(ПОИСКПОЗ(B2;Клиенты!$A$1:$A$7;0);1);">>")
=HYPERLINK(Мой_Лист&ADDRESS(MATCH(B2,Клиенты!$A$1:$A$7,0),1),">>")
Разберем ее на составляющие аналогичным образом:
- Функция ПОИСКПОЗ(B2;Клиенты!$A$1:$A$7;0) - ищет порядковый номер ячейки в диапазоне А1:А7 на листе Клиенты, где встречается название текущего клиента из B2 (последний аргумент =0 означает поиск точного совпадения, аналогично функции ВПР)
- Функция АДРЕС формирует адрес ячейки (в виде текстовой строки) по номеру строки и столбца, т.е. адрес ячейки с нужным клиентом, куда должна потом ссылаться гиперссылка
- Затем мы приклеиваем к адресу ссылку на файл и лист (переменную Мой_Лист) и используем это в качестве аргумента для функции ГИПЕРССЫЛКА (HYPERLINK), которая, собственно, и создает нужную нам ссылку.
При желании, можно заменить внешнее представление гиперссылки с банальных символов ">>" на что-нибудь поинтереснее с помощью функции СИМВОЛ (CHAR), которая умеет выводить нестандартные символы по их кодам:
=ГИПЕРССЫЛКА(Мой_Лист&АДРЕС(ПОИСКПОЗ(B2;Клиенты!$A$1:$A$7;0);1);СИМВОЛ(117))Так, например, если использовать шрифт Wingdings 3 и символ с кодом 117, то можно получить вот такие симпатичные значки гиперссылок:
Ссылки по теме
- Использование функции ВПР (VLOOKUP) для подстановки значений
- Создание писем с помощью функции ГИПЕРССЫЛКА
=ГИПЕРССЫЛКА(ПОДСТАВИТЬ(ЯЧЕЙКА("адрес";ИНДЕКС(Клиенты!$A$1:$A$7;ПОИСКПОЗ(B2;Клиенты!$A$1:$A$7;0)));"'";"");">>").
З.Ы.: Долго искал почему у меня выдавал ошибку "неверная ссылка"..
пример: ='Лист (1)!'
У меня вот тут чуть другая ситуация: на одном листе есть три переключателя, на нем надо сделать гиперссылку, которая в зависимости от того какой из них включен даст переход на соответствующий лист.
как это можно сделать?
=ГИПЕРССЫЛКА(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ЯЧЕЙКА("адрес";ИНДЕКС('Важные Клиенты'!$A$1:$A$7;ПОИСКПОЗ(B2;'Важные Клиенты'!$A$1:$A$7;0)));"'[";"[");"]";"]'");">>")
По ссылке, рядом с клиентом "Казанский арматурный завод" переход осуществляется на лист "Клиенты" на ячейку "ООО Пневмостроймашина".
Целый день пытаюсь понять причину, но в описании всё так непонятно написано, что разберётся только профессионал. ИЗ файла с примером тоже непонятно, почему там всё нормально происходит, а в моём файле - переход осуществляется на вообще не те ячейки
вот моя ссылка:
ЕСЛИОШИБКА(ГИПЕРССЫЛКА("C:\Documents and Settings\m.dereberya\Рабочий стол\HR\18 РАСЧЕТНЫЕ ВЕДОМОСТИ\2013 ГОД\Все_в_одном2013.xlsx"& АДРЕС(ПОИСКПОЗ(A2;[Все_в_одном.xlsx]TDSheet!$A$7:$A$1420;0);1);СИМВОЛ(62));"не работал")
_спасибо)
Хочу попробовать реализовать этот прием в рамках одной книги - чтобы на одном листе была гиперссылка, которая переходила бы на другой лист и сразу же около нужной ячейки из диапазона на другом листе в этой же книге?
Хочу попробовать реализовать это в гугл таблице.
=HYPERLINK(CELL("address";INDEX(Клиенты!$A$1:$A$7;MATCH(B2;Клиенты!$A$1:$A$7); ));">>";)
1. Создаем переменную с именем листа, но уже немного скорректированную
=ПОДСТАВИТЬ(ПСТР(ЯЧЕЙКА("имяфайла";Клиенты!$A$1);ПОИСК("[";ЯЧЕЙКА("имяфайла";Клиенты!$A$1));256)&"'!";"]";"]'").
С помощью функции "Подставить" дополняем адрес гиперссылки знаками '_ _ _', что позволяет использовать имя листа с пробелами.
2. Далее как описано выше в "Шаг 2. Создаем гиперссылки"
При клике на работающую ссылку (проверенно на другом компьютере) Бала ошибка:
Долго не мог понять, почему. Но...