Использование гиперссылок в Excel

В Excel можно использовать гиперссылки (как на сайтах и так далее).

Что это такое?

Гиперссылка — своеобразный текст (кнопка), который посылает вас на какой-либо сайт, либо место на компьютере, а может быть вообще на какой-либо файл.

Итак, давайте начнём!

Создание гиперссылок в Excel

Есть несколько вариантов создания гиперссылки в Excel:

• При параметрах Excel по-умолчанию можно просто вставить URL;

• Используя функцию ГИПЕРССЫЛКА;

• С помощью функции «Вставить Гиперссылку».

Итак, начнём!

Просто записать URL в ячейку

При стандартных настройках Excel, если вы напишете какой-либо URL в ячейку, он автоматически сделает из неё гиперссылку.

Давайте попробуем сделать это!

Пошаговая инструкция:

• Выделяем ячейку;

• Вводим URL (например, https://google.com).

Готово, гиперссылка создана.

Так же и при копировании URL в ячейку.

Использование функции

В Excel есть отдельная функция вставка гиперссылки. Она довольно удобная, потому что позволяет вставлять ссылку не только на URL, а еще и на файл или директорию. Также есть возможность указать отображаемое в ячейке имя ссылки.

Как это сделать?

Пошаговая инструкция:

• Выделяем ячейку;

• Вводим имя, которое должно отображаться в ячейке;

• Щелкаем на «Вставка»;

• «Гиперссылка»;

• В открывшемся окне вводим URL;
• Подтверждаем.

Готово, результат вы видите на картинке ниже:

Используем функцию ГИПЕРССЫЛКА

Еще один вариант — использовать функцию ГИПЕРССЫЛКА

Её синтаксис:

ГИПЕРССЫЛКА (адрес, имя)

• адрес: адресом может выступать URL какого-либо ресурса, файл, а так же директория на компьютере;

• имя: имя ссылки указывать не обязательно, только для того чтобы это было более «красиво», если вы укажете имя, в ячейке будет отображен не адрес, а то значение, которое вы указали в этом аргументе.

Допустим у нас есть следующая табличка:

Мы использовали функцию ГИПЕРССЫЛКА и создали гиперссылку с указанием её имени. Это будет выглядеть так:

Создаем гиперссылку на лист

Итак, с URL разобрались. А что если нам нужно создать ссылку на лист? В том же файле Excel.

Как это сделать?

Пошаговая инструкция:

• Выделите ячейку и откройте функцию «Вставить гиперссылку»;
• В открывшемся окне нажмите «На этот документ»;

• А также укажите ячейку, на которую мы ссылаемся;

• Укажите лист;

• Подтвердите.

Таким образом мы можем создать ссылку на любую ячейку, любого листа в нашем файле Excel.

Этого же результата мы можем добиться используя функцию ГИПЕРССЫЛКА.

Формула будет такой:

=ГИПЕРССЫЛКА("#"&"ЛИСТ2!A1"; "ССЫЛКА НА ЛИСТ2").

Как это работает?:

• «#» оператор говорит о том, что мы ссылаемся на тот же файл Excel;

• «ЛИСТ2!A1» говорит о том, что мы ссылаемся на ЛИСТ2 и ячейку A1;

• «ССЫЛКА НА ЛИСТ2” — это просто имя ссылки.

Создаем гиперссылку на файл

Таким же образом можно создать и ссылку на файл.

Допустим, мы хотим открыть файл Лист Microsoft Excel.xlsx:

• Выделяем ячейку и открываем функцию «Вставка гиперссылки»;

• В открывшемся окне функции выбираем «файлом, веб-страницей»;

• Находим наш файл и жмем на него левой кнопкой мышки;

• Указываем имя ссылки (сверху);

• Подтверждаем.

В случае, если ваш файл находится где-то «далеко», вы можете использовать эту кнопку для просмотра и выбора файлов с компьютера:

Так же и с функцией ГИПЕРССЫЛКА.

Вот формула:

Microsoft Excel.xlsx"; "Test").

Если файл находится не в той же папке, что и текущий открытый файл Excel, вам нужно указать полный путь.

Создаем гиперссылку в директорию

Тут тоже самое.

Пошаговая инструкция:

• Выделяем ячейку и открываем функцию «Вставка гиперссылки»;

• В окне, в параметре «Адрес» пропишите путь до нужной папки;

• Подтвердите.

Так же и с формулой функции ГИПЕРССЫЛКА.

Вот она:

=ГИПЕРССЫЛКА("C:\Users\exceluser\Desktop\Test1"; "Test1")

Создаем гиперссылку на почту

А теперь давайте создадим ссылку на почту.

Пошаговая инструкция:

• Выделите ячейку и откройте функцию «Вставка гиперссылки»;

• Выберите «E-mail адрес»;

• Введите нужные данные;

• Подтвердите.

Если вы щелкните на ячейку, Excel автоматически откроет почтовый клиент по-умолчанию и активирует функцию «Отправить письмо»;

Так же и с функцией ГИПЕРССЫЛКА.

Формула:

=ГИПЕРССЫЛКА("mailto:test@google.com", "Письмо")

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

Удаление гиперссылок

Как это сделать

Пошаговая инструкция:

• Выделите ячейки в которых нужно удалить гиперссылки;

• Щелкните «Удалить гиперссылку».

Если вам нужно удалить все гиперссылки на листе, вы можете выделить все ячейки CTRL + A и проделать те же действия.

Отключаем автосоздание гиперссылок

Вообще, это очень удобно, что Excel создает гиперссылки автоматически, при внесии в ячейку URL адреса. Но это подходит не всем.

Дело в том, что в Excel по-умолчанию активирована функция автосоздания гиперссылок. Но мы можем без проблем её отключить.

Как это сделать?

Пошаговая инструкция:

• Щелкните на «Файл» -> «Параметры»;

• В открывшемся окне, откройте вкладку «Правописание»;

• Далее — «Параметры автозамены…»;

• Вкладка «Автоформат при вводе»;

• И отключите первую опцию;

• Подтвердите.

Теперь Excel не будет автоматически создавать гиперссылки. Даже если вы впишете в ячейку URL адрес.

Также не забывайте о том, что эта настройка изменяется не для конкретного файла, а для Excel в общем.

Извлекаем адрес сайта из гиперссылки

Допустим, вам нужно «достать» URL адреса из гиперссылок. Что же делать? Ведь в программе нет отдельной функции для этого.

Но можно легко сделать это с помощью Visual Basic

Поместить данные в столбик рядом

Код Visual Basic:

Sub ExtractHyperLinks() 
Dim HypLnk As Hyperlink 
For Each HypLnk In Selection.Hyperlinks
     HypLnk.Range.Offset(0, 1).Value = HypLnk.Address 
Next HypLnk 
End Sub

Код рассмотренный выше, работает только для выделенных ячеек. Он “перетаскивает” URL адреса в столбик рядом.

Этот «вытащит» URL адреса для всего листа, независимо от того, что выделено:

Sub ExtractHyperLinks() 
On Error Resume Next 
Dim HypLnk As Hyperlink 
For Each HypLnk In ActiveSheet.Hyperlinks
     HypLnk.Range.Offset(0, 1).Value = HypLnk.Address 
Next HypLnk 
End Sub

Но эти коды не сработают, если ваша гиперссылка создана с помощью функции ГИПЕРССЫЛКА.

Постоянное извлечение адресов сайта из гиперссылок

Коды рассмотренные ранее вытаскивают URL адреса из гиперссылок за один раз. Но что если у вас табличка с ссылками, которых постепенно становится больше и больше. В таком случае вам нужна функция, которая будет быстро вытаскивать URL адрес в соседний столбик по одной ячейке.

Код:

Function GetHLink(rng As Range) As String
     If rng(1).Hyperlinks.Count <> 1 Then
         GetHLink = ""
     Else
         GetHLink = rng.Hyperlinks(1).Address
     End If End Function

Также как и с кодами ранее, функция не сработает если гиперссылка создана функцией ГИПЕРССЫЛКА.

Ищем гиперссылку с определенным текстовым значением

Бывают такие таблички, в которых очень много гиперссылок. В таком случае вам нужно научиться быстро ориентироваться в них и находить нужные.

Допустим, у нас есть такой набор данных:

И нам нужно поменять 2019-год на 2020-год во всей таблице.

Естественно, руками делать это не вариант, нужен универсальный способ.

Так как то, о чем мы говорим, называется замена, мы можем использовать эту функцию.

Как это сделать?

Пошаговая инструкция:

• Щелкните «Главная»;

• «Найти и выделить»;

• «Заменить»;

• Нажмите на «Параметры > >»;

• «Формат» как указано на картинке;

• «Выбрать формат из ячейки»;

• А теперь нажмите на любую ячейку, в которой содержится гиперссылка;

• Ну, а теперь введем сами аргументы для замены;

• И заменяем.

Готово!

Таким же образом, можно собрать список всех ячеек с гиперссылками внутри.

Как это работает?

Мы указали формат ячеек, которые хотим найти для Excel. Он понял, что в них содержатся гиперссылки и выдал нам все ячейки с ними.

Выделяем ячейку с гиперссылкой

Довольно проблемно выделить ячейку, в которой находится гиперссылка. Так как при нажатии левой кнопкой мыши Excel сразу же откроет вам сайт (или файл/директорию) на которую и ссылается эта гиперссылка.

Выделяем ячейку, при этом не открыв URL

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

То нажмите левой кнопкой мыши на неё и немного подержите.

Буквально сразу же вы увидите следующее:

Если увидели смело отпускайте кнопку мыши, Excel не будет открывать ссылку.

Таким образом — ячейка была выделена, теперь можно делать с ней все, что пожелаете.

Выделяем ячейку через пустое место в ней.

Тоже практичный прием, вы можете увеличить ширину ячейки и кликнуть по пустому месту.

В таком случае ячейка будет выделена, а ссылка при этом не откроется.

Варианты использования

Давайте посмотрим, в каком случае создание гиперссылки может быть невероятно удобно!

Список всех листов файла и ссылки на них

В случае, когда у вас ежемесячная отчетность, вы можете поместить отчеты за каждый месяц в один файл и сделать список с ссылками на каждый месяц. Это можно сделать с помощью Visual Basic.

Код:

Sub CreateSummary() 
Dim x As Worksheet 
Dim Counter As Integer 
Counter = 0 
For Each x In Worksheets 
Counter = Counter + 1 
If Counter = 1 Then GoTo Donothing
     With ActiveCell
         .Value = x.Name
         .Hyperlinks.Add ActiveCell, "", x.Name & "!A1", 
TextToDisplay:=x.Name, ScreenTip:="Click here to go to the Worksheet"
         With Worksheets(Counter)
             .Range("A1").Value = "Back to " & ActiveSheet.Name
             .Hyperlinks.Add Sheets(x.Name).Range("A1"), "", _
             "'" & ActiveSheet.Name & "'" & "!" & ActiveCell.Address, _
             ScreenTip:="Return to " & ActiveSheet.Name
         End With
     End With 
ActiveCell.Offset(1, 0).Select 
Donothing: 
Next x 
End Sub

Результат выполнения будет примерно таким:

Запускайте этот макрос на первом листе файла, иначе он сработает некорректно.

Как создать динамическую гиперссылку?

Обычно гиперссылка это статические данные. Вы просто указываете ссылку на какой-либо сайт или место и она ведет туда.

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

В нашем примере будет точно так:

Такого эффекта можно добиться используя формулу:

=ГИПЕРССЫЛКА(ВПР(D2;$A$2:$B$6;2;0); "Нажми сюда")

ВПР получает адрес компании из таблицы слева.

И после того как выбор компании был сделан, она меняет его в функции гиперссылка.

Довольно полезные знания, но я пока таких таблиц не встречал.

Отправка писем

Итак, давайте создадим отправку писем.

Код:

=ГИПЕРССЫЛКА("mailto:123@google.com","Отправка письма")

Таким образом откроется почта и будет открыта отправка письма с указанным получателем.

Отправляем нескольким людям:

=ГИПЕРССЫЛКА("mailto:123@google.com,321@google.com";"Отправка письма")

Добавьте тему:

=ГИПЕРССЫЛКА("mailto:123@google.com;321@google.com&subject=Тема письма","Отправка письма")

Таким образом мы добавили тему.

Добавляем текст:

=ГИПЕРССЫЛКА("mailto:321@google.com,123@google.com&subject=Тема&body=Текст","Отправка письма")

Таким образом письмо отправится с текстом “Текст”.

А так можно отправить несколько строк текста:

=ГИПЕРССЫЛКА("mailto:321@google.com,123@google.com&subject=Тема&body=Текст1%0AТекст2","Отправка письма")

Оператор %0A разделяет строки текста.

Надеюсь эта статья была полезной для вас!

Жду ваших комментариев.