Гиперссылка - это не просто отображение адреса на ресурс - это активная ссылка, кликнув на которую может быть открыта

  • интернет-страница в браузере по умолчанию
  • локальный файл на текущем ПК или файл на подключенном сетевом диске
  • директория(папка) на локальном ПК или подключенном сетевом диске

Установить гиперссылку можно несколькими способами: через стандартное меню и путем создания функции.

 
СТАНДАРТНОЕ МЕНЮ
и самый распространенный: правый клик мыши по ячейке -Гиперссылка(Hyperlink)(или вкладка Вставка(Insert) -Ссылка(Hyperlink). Откроется окно добавления гиперссылки.

Там можно выбрать вид ссылки:

  • на файл или веб-страницу. Если на веб-страницу, то необходимо в поле "Адрес" указать адрес веб-страницы. Если ссылка на файл - просто выбрать файл в диспетчере файлов.
  • местом в документе. Выбирается необходимый лист и адрес ячейки, на которую необходимо переместиться по нажатии гиперссылки.
  • новым документом. Создается новый документ и сразу ссылка на него. При нажатии гиперссылки будет открыт этот файл.
  • электронной почтой. Указывается адрес электронной почты и тема письма по умолчанию. По нажатии ссылки будет создано письмо на указанный адрес электронной почты и с указанной темой.

Самый серьезный недостаток этого метода в том, что после сортировки таблиц со множеством таких гиперссылок они неизбежно "ломаются", т.е. перестают работать.



 
ФУНКЦИЯ ЛИСТА
Гиперссылки можно создавать на листе через одноименную функцию ГИПЕРССЫЛКА (HYPERLINK). Функция расположена в категории Ссылки и массивы диспетчера функций. Такой способ очень удобен, если необходимо создать много гиперссылок на листе.
Синтаксис функции:
=ГИПЕРССЫЛКА("www.excel-vba.ru";"переход на сайт")
=HYPERLINK("www.excel-vba.ru";"переход на сайт")

  • www.excel-vba.ru - адрес страницы(или путь к файлу или директории), который будет открыт по клике на ячейку
  • переход на сайт - текст, который будет отображаться в ячейке. Может отличаться от текста самой ссылки

Конечно, универсальность создания гиперссылок через функцию именно в том, что можно в отдельном столбце записать адреса интернет-страниц, а в другой просто создать на них ссылки. Допустим, если в столбце А у нас ссылки, то в столбец В мы можем записать и протянуть такую формулу:
=ГИПЕРССЫЛКА(A1;"переход на сайт")
Или другой вариант: у нас есть адреса сайта(для примера возьмем этот: "https://www.excel-vba.ru/"), а в столбце А указаны только адреса страниц, без указания домена. Что-то вроде "kak-excel-vosprinimaet-dannye". Т.е. нам необходимо объединить основной адрес с дополнительным. Тогда можно записать так:
=ГИПЕРССЫЛКА("https://www.excel-vba.ru/"&A1;"переход на страницу сайта")
Теперь при клике на ячейку откроется страница https://www.excel-vba.ru/kak-excel-vosprinimaet-dannye.
А можно сделать еще универсальнее - записать вдобавок адрес сайта не статично внутри функции, а в отдельной ячейке. Пусть это будет ячейка D1. Тогда функция будет выглядеть так:
=ГИПЕРССЫЛКА($D$1&A1;"переход на страницу сайта")
Важно учитывать слеши в ячейках. Т.е. если в D1 записан адрес домена без слеша на конце("https://www.excel-vba.ru/"), то его необходимо будет учесть либо в адресах страниц("/kak-excel-vosprinimaet-dannye"), либо напрямую в функции:
=ГИПЕРССЫЛКА($D$1&"/"&A1;"переход на страницу сайта")
Нетрудно догадаться, что таких ссылок можно создать множество простым копированием формулы в другие ячейки и что немаловажно - в столбце А можно записывать разные окончания ссылок. Если вдруг сайт поменял адрес, но система ссылок осталась прежней - надо будет лишь заменить основной адрес. То же самое можно отнести и к файлам на диске: если поменялась лишь корневая директория - то просто меняем её в функции или в ячейке.

ГИПЕРССЫЛКА на конкретную ячейку листа
Так же можно создать гиперссылку, переходящую на определенную ячейку на листе:
=ГИПЕРССЫЛКА("#Лист2!D4";"Перейти в ячейку D4 Лист2")
Обращаю особое внимание на знак решетки(#) перед именем листа - в данном случае он обязателен, т.к. указывает функции, что переход будет осуществляться внутри листов книги, в которой записана гиперссылка.
Ссылку на ячейку можно указывать динамически, ссылаясь на определенную ячейку, не вписывая её адрес(на примере D4):
=ГИПЕРССЫЛКА("#Лист2!"&D4);"Перейти в ячейку "&D4&" Лист2")
Или более классическая ситуация - в столбце А у нас записаны имена листов, а в столбце В нам необходимо сделать гиперссылки на ячейку D4 отдельно для каждого листа. Иными словами - оглавление. Тогда в ячейку В1 запишем функцию:
=ГИПЕРССЫЛКА("#'"&A1&"'!D4");"Перейти на лист")

Важно: Обращаю внимание на функцию выше - она несколько отличается от приведенных ранее. Если в имени листа содержатся пробелы(или иные знаки препинания, кроме точки и нижнего подчеркивания), то обязательно после знака решетки и перед восклицательным знаком ставить знаки апострофа(')
"#'Москва Январь'!D4"
Если для таких ссылок не указать апостроф, то получим ошибку "Неверная ссылка".
Если же пробелов и иных символов в имени листа нет - указание апострофа не вызовет ошибку. Поэтому для подстраховки, в подобных случаях правильнее всегда указывать апострофы, чтобы избежать неверных ссылок.


Можно так же переходить на ячейки листа в другой книге:
=ГИПЕРССЫЛКА("#'[Продажи 2011.xls]Москва'!D4";"Перейти в книгу продаж - на лист Москва")
Здесь есть существенное ограничение: книга, на которую ссылается функция ГИПЕРССЫЛКА, должна быть открыта. И обязательно должна быть заключена в квадратные скобки, как в примере выше.

Важно: Если в имени книги или листа содержатся пробелы(или иные знаки препинания, кроме точки и нижнего подчеркивания), то обязательно после знака решетки и перед восклицательным знаком ставить апостроф('). Я специально в функции выше привел пример именно с пробелом(Продажи 2011.xls) - для наглядности.
При этом, как уже упоминал выше - неважно, пробел в имени листа или книги:
=ГИПЕРССЫЛКА("#'[Продажи.xls]Москва Январь'!D4");"Перейти в ячейку D4")
И если не уверены в том, будет ли имя листа и книги содержать такие символы(например, если адрес для перехода составляется из ссылок на другие ячейки) - лучше в формуле проставлять апострофы всегда. Ошибкой это не будет и гиперссылка будет работать, даже если таких символов в имени не окажется.

ГИПЕРССЫЛКА для открытия файла
При помощи функции ГИПЕРССЫЛКА так же можно указать путь к файлу, который необходимо открыть по нажатии ячейки:
=ГИПЕРССЫЛКА("C:\Users\Дмитрий\Desktop\Книга 1.xls";"Открыть книгу Книга 1.xls")
Путь должен быть корректным, а имя файла необходимо указывать с расширением.

Открытие файла и переход на нужный лист и нужную ячейку
В качестве адреса для функции ГИПЕРССЫЛКА можно применить и открытие книги и одновременный переход на нужный лист и ячейку этой книги:
=ГИПЕРССЫЛКА("C:\Users\Дмитрий\Desktop\Книга1.xls#'Лист 2'!D4";"Открыть книгу Книга 1 - Лист 2 - ячейка D4")
В некоторых версиях такие переходы могут не работать без каких-либо видимых причин. По крайней мере на момент написания статьи такие случаи были известны.

 
Создание гиперссылки кодом VBA
Создание гиперссылки в ячейке 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.
Т.е. мы можем делать переходы как на конкретные ячейки текущего листа, так и на ячейки других листов. В общем все, как в и функции ГИПЕРССЫЛКА и все так же нельзя сделать одновременно и открытие файла и при этом переход на ячейку в листа в этом файле.
Полностью синтаксис создания гиперссылки выглядит так:
ActiveSheet.Hyperlinks.Add Anchor, Address, [SubAddress], [ScreenTip], [TextToDisplay]

  • 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

Так же см.:
Как массово изменить гиперссылки?
Как получить адрес гиперссылки из ячейки
Как сделать гиперссылку на процедуру?

Loading

29 комментариев

  1. Добрый день! Есть столбец значения которого - это гиперссылки на разные рессурсы.
    Как эти ссылки превратить в слова, из которых эти ссылки состоят

  2. Добрый день!

    Дмитрий, подскажите, пожалуйста, возможно ли перейти на лист, имя которого берется из ячейки?
    К примеру,
    =ГИПЕРССЫЛКА("#"&H12&"'!A1";"Перейти на лист "&H12&" ячейку А1")

    1. вопрос решил) забыл, что имя листа с пробелами и не вставил апострофы.

      =ГИПЕРССЫЛКА("#'"&H12&"'!A1";"Перейти на лист "&H12&" ячейку А1")

      Спасибо вам за ваш сайт!!!
      Ну, очень выручает!!!

  3. Подскажите, Формула ВПР не открывает гиперссылку, которая привязана к значению которую она возвращает.
    Как можно написать формулу, чтобы открывалась гиперссылка?

    1. Катерина, смотря что именно возвращает ВПР. Если она возвращает непосредственно адрес для ссылки, то просто добавить ГИПЕРССЫЛКА:
      =ГИПЕРССЫЛКА(ВПР(что-то там в аргументах);"перейти")
      Если ВПР возвращает просто значение ячейки, а сама ссылка является гиперссылкой внутри искомой ячейки - то здесь без макросов никак не получится.

  4. Добрый вечер!
    Подскажите пожалуйста как сделать автозамену для URL файлов ,если они в разных форматах (http,www и др)?
    Заранее благодарна!

  5. Добрый день,
    в ячейке которая копируется содержится гиперссылка. В ячейке в которую зиписал получился обычный текст (. Как лечить данный код?

    ThisWorkbook.Worksheets(1).Cells(j, l).Value = ThisWorkbook.Worksheets(1).Cells(i, 1).Value

    1. Алексей, если прочитаете статью - то увидите код вставки гиперссылки в ячейку:
      ActiveSheet.Hyperlinks.Add Anchor:=Range("A1"), _
      Address:="/", _
      TextToDisplay:="/"

      Вам нужно делать точно так же, но применительно к своей ситуации:
      ThisWorkbook.Worksheets(1).Hyperlinks.Add Anchor:=ThisWorkbook.Worksheets(1).Cells(j, l), _
      Address:=ThisWorkbook.Worksheets(1).Cells(i, 1).Value, _
      TextToDisplay:=ThisWorkbook.Worksheets(1).Cells(i, 1).Value

      либо и вовсе просто скопировать ячейку полностью:
      ThisWorkbook.Worksheets(1).Cells(i, 1).Copy ThisWorkbook.Worksheets(1).Cells(j, l)

Добавить комментарий

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.