- интернет-страница в браузере по умолчанию
- локальный файл на текущем ПК или файл на подключенном сетевом диске
- директория(папка) на локальном ПК или подключенном сетевом диске
Установить гиперссылку можно несколькими способами: через стандартное меню и путем создания функции.
и самый распространенный: правый клик мыши по ячейке -
Там можно выбрать вид ссылки:
на файл или веб-страницу . Если на веб-страницу, то необходимо в поле "Адрес" указать адрес веб-страницы. Если ссылка на файл - просто выбрать файл в диспетчере файлов.местом в документе . Выбирается необходимый лист и адрес ячейки, на которую необходимо переместиться по нажатии гиперссылки.новым документом . Создается новый документ и сразу ссылка на него. При нажатии гиперссылки будет открыт этот файл.электронной почтой . Указывается адрес электронной почты и тема письма по умолчанию. По нажатии ссылки будет создано письмо на указанный адрес электронной почты и с указанной темой.
Самый серьезный недостаток этого метода в том, что после сортировки таблиц со множеством таких гиперссылок они неизбежно "ломаются", т.е. перестают работать.
Гиперссылки можно создавать на листе через одноименную функцию
www.excel-vba.ru - адрес страницы(или путь к файлу или директории), который будет открыт по клике на ячейкупереход на сайт - текст, который будет отображаться в ячейке. Может отличаться от текста самой ссылки
Конечно, универсальность создания гиперссылок через функцию именно в том, что можно в отдельном столбце записать адреса интернет-страниц, а в другой просто создать на них ссылки. Допустим, если в столбце А у нас ссылки, то в столбец В мы можем записать и протянуть такую формулу:
Или другой вариант: у нас есть адреса сайта(для примера возьмем этот:
Теперь при клике на ячейку откроется страница https://www.excel-vba.ru/kak-excel-vosprinimaet-dannye.
А можно сделать еще универсальнее - записать вдобавок адрес сайта не статично внутри функции, а в отдельной ячейке. Пусть это будет ячейка D1. Тогда функция будет выглядеть так:
Важно учитывать слеши в ячейках. Т.е. если в D1 записан адрес домена без слеша на конце(
Нетрудно догадаться, что таких ссылок можно создать множество простым копированием формулы в другие ячейки и что немаловажно - в столбце А можно записывать разные окончания ссылок. Если вдруг сайт поменял адрес, но система ссылок осталась прежней - надо будет лишь заменить основной адрес. То же самое можно отнести и к файлам на диске: если поменялась лишь корневая директория - то просто меняем её в функции или в ячейке.
Так же можно создать гиперссылку, переходящую на определенную ячейку на листе:
Обращаю особое внимание на знак решетки(#) перед именем листа - в данном случае он обязателен, т.к. указывает функции, что переход будет осуществляться внутри листов книги, в которой записана гиперссылка.
Ссылку на ячейку можно указывать динамически, ссылаясь на определенную ячейку, не вписывая её адрес(на примере
Или более классическая ситуация - в столбце А у нас записаны имена листов, а в столбце В нам необходимо сделать гиперссылки на ячейку D4 отдельно для каждого листа. Иными словами - оглавление. Тогда в ячейку
Важно: Обращаю внимание на функцию выше - она несколько отличается от приведенных ранее. Если в имени листа содержатся пробелы(или иные знаки препинания, кроме точки и нижнего подчеркивания), то обязательно после знака решетки и перед восклицательным знаком ставить знаки апострофа() '
"# ' Москва Январь' !D4"
Если для таких ссылок не указать апостроф, то получим ошибку "Неверная ссылка".
Если же пробелов и иных символов в имени листа нет - указание апострофа не вызовет ошибку. Поэтому для подстраховки, в подобных случаях правильнее всегда указывать апострофы, чтобы избежать неверных ссылок.
Можно так же переходить на ячейки листа в другой книге:
Здесь есть существенное ограничение: книга, на которую ссылается функция ГИПЕРССЫЛКА, должна быть открыта. И обязательно должна быть заключена в квадратные скобки, как в примере выше.
Важно: Если в имени книги или листа содержатся пробелы(или иные знаки препинания, кроме точки и нижнего подчеркивания), то обязательно после знака решетки и перед восклицательным знаком ставить апостроф('). Я специально в функции выше привел пример именно с пробелом(Продажи 2011.xls) - для наглядности.
При этом, как уже упоминал выше - неважно, пробел в имени листа или книги:
=ГИПЕРССЫЛКА("# ' [Продажи.xls]Москва Январь' !D4");"Перейти в ячейку D4")
И если не уверены в том, будет ли имя листа и книги содержать такие символы(например, если адрес для перехода составляется из ссылок на другие ячейки) - лучше в формуле проставлять апострофы всегда. Ошибкой это не будет и гиперссылка будет работать, даже если таких символов в имени не окажется.
При помощи функции ГИПЕРССЫЛКА так же можно указать путь к файлу, который необходимо открыть по нажатии ячейки:
Путь должен быть корректным, а имя файла необходимо указывать с расширением.
В качестве адреса для функции ГИПЕРССЫЛКА можно применить и открытие книги и одновременный переход на нужный лист и ячейку этой книги:
В некоторых версиях такие переходы могут не работать без каких-либо видимых причин. По крайней мере на момент написания статьи такие случаи были известны.
Создание гиперссылки в ячейке A1 активного листа для перехода на страницу сайта http://www.excel-vba.ru/:
ActiveSheet.Hyperlinks.Add Anchor:=Range("A1"), _ Address:="http://www.excel-vba.ru/", _ TextToDisplay:="Перейти на сайт http://www.excel-vba.ru/" |
А код ниже создаст в ячейке А1 гиперссылку для открытия конкретного файла("Книга 1.xls" в папке "C:\Users\Дмитрий\Desktop"):
ActiveSheet.Hyperlinks.Add Anchor:=Range("A1"), _ Address:="C:\Users\Дмитрий\Desktop\Книга 1.xls", _ TextToDisplay:="Открыть файл Книга 1" |
И самый сложный для понимания пример - создание гиперссылок для перехода на конкретную ячейку книги.
Переходим в ячейку В10 текущего листа:
ActiveSheet.Hyperlinks.Add Anchor:=Range("A1"), _ Address:="", SubAddress:="B10", _ TextToDisplay:="Ячейка В10 листа Склад" |
Обращаю особое внимание, что мы уже не указываем текст для аргумента Address. Но мы указываем теперь SubAddress. Именно SubAddress отвечает за адрес ячейки и имя листа, на которые будет осуществляться переход по гиперссылке. Т.е. SubAddress отвечает за ссылки внутри ресурса. При этом так же можно переходить и в другие листы. Например, код ниже создает гиперссылку в ячейке А1, но при клике на неё происходит переход в ячейку В10 листа "Склад":
ActiveSheet.Hyperlinks.Add Anchor:=Range("A1"), _ Address:="", SubAddress:="'Склад'!B10", _ TextToDisplay:="Ячейка В10 листа Склад" |
При этом, если лист "Склад" не активен - то он будет активирован и нас "перебросит" в указанную ячейку В10.
Т.е. мы можем делать переходы как на конкретные ячейки текущего листа, так и на ячейки других листов. В общем все, как в и функции ГИПЕРССЫЛКА и все так же нельзя сделать одновременно и открытие файла и при этом переход на ячейку в листа в этом файле.
Полностью синтаксис создания гиперссылки выглядит так:
Range("A1") - это ячейка, в которой будет создана гиперссылкаAddress("C:\Users\Дмитрий\Desktop\Книга 1.xls" или "http://www.excel-vba.ru/") - в случае указания URL адреса страницы - будет создана ссылка для перехода на эту страницу, а в случае указания полного пути к файлу или папке - будет открыт этот файл или папкаSubAddress() - если указан, то переход осуществляется на указанную ячейку указанного листа -Склад!B10 . Указывать при этом аргумент Address не обязательноScreenTip(Перейти на сайт http://www.excel-vba.ru/) - текст подсказки, которая отображается при наведении на ячейку с гиперссылкойTextToDisplay() - это текст, который будет отображаться в ячейке. Может отличаться от адреса самой ссылки
Правила создания гиперссылок кодом точно те же, что и руками и функцией ГИПЕРССЫЛКА. Основное внимание хочу заострить опять же на создании гиперссылок на ячейки листа. Лучше всегда создавать с указанием знака апострофа:
Короткий пример создания оглавления для текущей книги в новом листе. Гиперссылки ведут на ячейку А2 каждого листа:
'--------------------------------------------------------------------------------------- ' Author : Щербаков Дмитрий(The_Prist) ' Профессиональная разработка приложений для MS Office любой сложности ' Проведение тренингов по MS Excel ' https://www.excel-vba.ru ' info@excel-vba.ru ' Purpose: Создание оглавления с гиперссылками на все листы текущей книги '--------------------------------------------------------------------------------------- Sub CreateContents() Dim ws As Worksheet, wsHead As Worksheet Dim lcurrow As Long 'создаем первым листом лист оглавления Set wsHead = ActiveWorkbook.Sheets.Add(before:=ActiveWorkbook.Sheets(1)) 'номер строки для первой гиперссылки lcurrow = 2 'цикл по листам For Each ws In ActiveWorkbook.Worksheets 'исключаем имя листа с оглавлением If ws.Name <> wsHead.Name Then 'создаем гиперссылку на текущий лист wsHead.Hyperlinks.Add Anchor:=Range("A" & lcurrow), _ Address:="", SubAddress:="'" & ws.Name & "'!A2", _ TextToDisplay:="Перейти на лист " & ws.Name 'увеличиваем счетчик строк для вставки следующей гиперссылки в новую строку lcurrow = lcurrow + 1 End If Next wsHead.Activate End Sub |
Так же см.:
Как массово изменить гиперссылки?
Как получить адрес гиперссылки из ячейки
Как сделать гиперссылку на процедуру?
Добрый день! Есть столбец значения которого - это гиперссылки на разные рессурсы.
Как эти ссылки превратить в слова, из которых эти ссылки состоят
Наталия, прямо под статьей есть ссылка на статью: Как получить адрес гиперссылки из ячейки
Пройдите, прочитайте.
Добрый день!
Дмитрий, подскажите, пожалуйста, возможно ли перейти на лист, имя которого берется из ячейки?
К примеру,
=ГИПЕРССЫЛКА("#"&H12&"'!A1";"Перейти на лист "&H12&" ячейку А1")
вопрос решил) забыл, что имя листа с пробелами и не вставил апострофы.
=ГИПЕРССЫЛКА("#'"&H12&"'!A1";"Перейти на лист "&H12&" ячейку А1")
Спасибо вам за ваш сайт!!!
Ну, очень выручает!!!
Подскажите, Формула ВПР не открывает гиперссылку, которая привязана к значению которую она возвращает.
Как можно написать формулу, чтобы открывалась гиперссылка?
Катерина, смотря что именно возвращает ВПР. Если она возвращает непосредственно адрес для ссылки, то просто добавить ГИПЕРССЫЛКА:
=ГИПЕРССЫЛКА(ВПР(что-то там в аргументах);"перейти")
Если ВПР возвращает просто значение ячейки, а сама ссылка является гиперссылкой внутри искомой ячейки - то здесь без макросов никак не получится.
Добрый вечер!
Подскажите пожалуйста как сделать автозамену для URL файлов ,если они в разных форматах (http,www и др)?
Заранее благодарна!
Добрый день,
в ячейке которая копируется содержится гиперссылка. В ячейке в которую зиписал получился обычный текст (. Как лечить данный код?
ThisWorkbook.Worksheets(1).Cells(j, l).Value = ThisWorkbook.Worksheets(1).Cells(i, 1).Value
Алексей, если прочитаете статью - то увидите код вставки гиперссылки в ячейку:
ActiveSheet.Hyperlinks.Add Anchor:=Range("A1"), _
ThisWorkbook.Worksheets(1).Hyperlinks.Add Anchor:=ThisWorkbook.Worksheets(1).Cells(j, l), _
ThisWorkbook.Worksheets(1).Cells(i, 1).Copy ThisWorkbook.Worksheets(1).Cells(j, l)
Address:="/", _
TextToDisplay:="/"
Вам нужно делать точно так же, но применительно к своей ситуации:
Address:=ThisWorkbook.Worksheets(1).Cells(i, 1).Value, _
TextToDisplay:=ThisWorkbook.Worksheets(1).Cells(i, 1).Value
либо и вовсе просто скопировать ячейку полностью: