Використання гіперпосилань в Excel

В Excel можна використовувати гіперпосилання (як на сайтах тощо).

Що це таке?

Гіперпосилання – своєрідний текст (кнопка), який посилає вас на який-небудь сайт, або місце на комп'ютері, а може взагалі на який-небудь файл.

Отже, почнемо!

Створення гіперпосилань в Excel

Є кілька варіантів створення гіперпосилання в Excel:

  • При параметрах Excel за промовчанням можна просто вставити URL-адресу;
  • Використовуючи функцію ГІПЕРСИЛАННЯ;
  • За допомогою функції «Вставити Гіперпосилання».

Тож почнемо!

Просто записати URL в осередок

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

Спробуємо зробити це!

Покрокова інструкція:

  • Виділяємо комірку;
  • Вводимо URL (наприклад, https://google.com).

Готово, гіперпосилання створено.

Так само і при копіюванні URL в комірку.

Використання функції

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

Як це зробити?

Покрокова інструкція:

  • Виділяємо комірку;
  • Вводимо ім'я, яке має відображатись у осередку;
  • Натискаємо на «Вставка»;

Використання гіперпосилань в Excel

  • «Гіперпосилання»;

Використання гіперпосилань в Excel

  • У вікні вводимо URL;
  • Підтверджуємо.

Використання гіперпосилань в Excel

Готово, результат ви бачите на малюнку нижче:

Використання гіперпосилань в Excel

Використовуємо функцію ГІПЕРСИЛАННЯ

Ще один варіант – використовувати функцію ГІПЕРСИЛАННЯ

Її синтаксис:

Гіперпосилання (адреса, ім'я)

  • адреса: адресою може бути URL будь-якого ресурсу, файл, а також директорія на комп'ютері;
  • Ім'я: Ім'я посилання вказувати не обов'язково, тільки для того, щоб це було “красивіше”, якщо ви вкажете ім'я, в осередку буде відображено не адресу, а те значення, яке ви вказали в цьому аргументі.

Допустимо, у нас є наступна табличка:

Використання гіперпосилань в Excel

Ми використовували функцію ГІПЕРСИЛАННЯ і створили гіперпосилання із зазначенням її імені. Це буде виглядати так:

Використання гіперпосилань в Excel

Створюємо гіперпосилання на аркуш

Отже, із URL розібралися. А якщо нам потрібно створити посилання на лист? У тому самому файлі Excel.

Як це зробити?

Покрокова інструкція:

  • Виділіть комірку та відкрийте функцію «Вставити гіперпосилання»;
  • У вікні, натисніть «На цей документ»;

Використання гіперпосилань в Excel

  • А також вкажіть осередок, на який ми посилаємось;

Використання гіперпосилань в Excel

  • Вкажіть аркуш;

Використання гіперпосилань в Excel

  • Підтвердіть.

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

Цього ж результату ми можемо досягти, використовуючи функцію ГІПЕРСИЛАННЯ.

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

=ГІПЕРСИЛКА(“#”&”ЛИСТ2!A1”; “ПОСИЛКА НА ЛИСТ2”).

Як це працює?:

  • “#” оператор говорить про те, що ми посилаємося на той же файл Excel;
  • «ЛИСТ2!A1» свідчить, що ми посилаємося на ЛИСТ2 і осередок A1;
  • «ПОСИЛКА НА ЛИСТ2» – це просто ім'я посилання.

Створюємо гіперпосилання на файл

Таким же чином можна створити посилання на файл.

Допустимо, ми хочемо відкрити файл Аркуш Microsoft Excel.xlsx:

  • Виділяємо комірку та відкриваємо функцію «Вставка гіперпосилання»;
  • У вікні функції вибираємо «файлом, веб-сторінкою»;
  • Знаходимо наш файл і тиснемо на нього лівою кнопкою мишки;

Використання гіперпосилань в Excel

  • Вказуємо ім'я посилання (згори);
  • Підтверджуємо.

Якщо ваш файл знаходиться десь «далеко», ви можете використовувати цю кнопку для перегляду та вибору файлів з комп'ютера:

Використання гіперпосилань в Excel

Так само і з функцією гіперпосилання.

Ось формула:

Microsoft Excel.xlsx”; “Test”).

Якщо файл знаходиться не в тій же папці, що й поточний відкритий файл Excel, потрібно вказати повний шлях.

Створюємо гіперпосилання в директорію

Тут теж саме.

Покрокова інструкція:

  • Виділяємо комірку та відкриваємо функцію «Вставка гіперпосилання»;
  • У вікні, в параметрі “Адреса” пропишіть шлях до потрібної папки;

Використання гіперпосилань в Excel

  • Підтвердіть.

Так само і з формулою функції ГІПЕРСИЛАННЯ.

Ось вона:

=ГІПЕРСИЛКА(“C:UsersexceluserDesktopTest1”; “Test1”)

Створюємо гіперпосилання на пошту

А тепер давайте створимо посилання на пошту.

Покрокова інструкція:

  • Виділіть комірку та відкрийте функцію «Вставка гіперпосилання»;
  • Виберіть “E-mail адресу”;
  • Введіть потрібні дані;

Використання гіперпосилань в Excel

  • Підтвердіть.

Якщо ви натисніть на осередок, Excel автоматично відкриє поштовий клієнт за замовчуванням і активує функцію «Надіслати листа»;

Так само і з функцією гіперпосилання.

Формула:

=ГІПЕРСИЛКА(“mailto:test@google.com”, “Лист”)

При надсиланні листа обов'язково вказувати оператор mailto, він говорить Excel про те, що потрібно відкрити поштовий клієнт, і спробувати надіслати листа на задану адресу.

Видалення гіперпосилань

Як це зробити

Покрокова інструкція:

  • Виділіть осередки у яких потрібно видалити гіперпосилання;

Використання гіперпосилань в Excel

  • Натисніть «Видалити гіперпосилання».

Якщо вам потрібно видалити всі гіперпосилання на аркуші, ви можете виділити всі осередки CTRL + A і зробити ті ж дії.

Відключаємо автостворення гіперпосилань

Взагалі, це дуже зручно, що Excel створює гіперпосилання автоматично, при внесенні в осередок URL-адреси. Але це не всім.

Справа в тому, що в Excel за замовчуванням активовано функцію автостворення гіперпосилань. Але ми можемо без проблем її вимкнути.

Як це зробити?

Покрокова інструкція:

  • Клацніть на “Файл” -> “Параметри”;

Використання гіперпосилань в Excel

  • У вікні, відкрийте вкладку «Правопис»;

Використання гіперпосилань в Excel

  • Далі – “Параметри автозаміни …”;

Використання гіперпосилань в Excel

  • Вкладка “Автоформат під час введення”;

Використання гіперпосилань в Excel

  • І вимкніть першу опцію;

Використання гіперпосилань в Excel

  • Підтвердіть.

Тепер Excel не автоматично створюватиме гіперпосилання. Навіть якщо ви впишете в осередок URL-адресу.

Також не забувайте про те, що це налаштування змінюється не для конкретного файлу, а для Excel загалом.

Вилучаємо адресу сайту з гіперпосилання

Допустимо, вам потрібно «дістати» URL-адреси з гіперпосилань. Що ж робити? Адже програма не має окремої функції для цього.

Використання гіперпосилань в Excel

Але це можна легко зробити за допомогою 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 Як 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).

Так само як і з кодами раніше, функція не спрацює, якщо гіперпосилання створено функцією ГІПЕРСИЛАННЯ.

Шукаємо гіперпосилання з певним текстовим значенням

Бувають такі таблички, у яких дуже багато гіперпосилань. У такому разі вам потрібно навчитися швидко орієнтуватися в них та знаходити потрібні.

Допустимо, у нас є такий набір даних:

Використання гіперпосилань в Excel

І нам потрібно поміняти 2019-рік на 2020-рік у всій таблиці.

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

Оскільки те, про що ми говоримо, називається заміна, ми можемо використовувати цю функцію.

Як це зробити?

Покрокова інструкція:

  • Клацніть “Головна”;

Використання гіперпосилань в Excel

  • «Знайти та виділити»;

Використання гіперпосилань в Excel

  • “Замінити”;

Використання гіперпосилань в Excel

  • Натисніть «Параметри > >»;

Використання гіперпосилань в Excel

  • “Формат” як зазначено на картинці;

Використання гіперпосилань в Excel

  • «Вибрати формат із комірки»;

Використання гіперпосилань в Excel

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

Використання гіперпосилань в Excel

  • А тепер введемо самі аргументи для заміни;

Використання гіперпосилань в Excel

  • І замінюємо.

Використання гіперпосилань в Excel

Готово!

Таким же чином можна зібрати список всіх осередків з гіперпосиланнями всередині.

Як це працює?

Ми вказали формат осередків, які бажаємо знайти для Excel. Він зрозумів, що вони містять гіперпосилання і видав нам всі осередки з ними.

Виділяємо комірку з гіперпосиланням

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

Виділяємо комірку, при цьому не відкривши URL

Тут все досить просто, якщо ви бачите осередок з посиланням, як на прикладі нижче:

Використання гіперпосилань в Excel

Натисніть лівою кнопкою миші на неї і трохи потримайте.

Буквально відразу ж ви побачите таке:

Використання гіперпосилань в Excel

Якщо побачили сміливо відпускайте кнопку миші, Excel не відкриватиме посилання.

Таким чином, осередок був виділений, тепер можна робити з ним все, що забажаєте.

Виділяємо комірку через порожнє місце у ній.

Також практичний прийом, ви можете збільшити ширину осередку і натиснути на порожнє місце.

Використання гіперпосилань в Excel

У такому разі осередок буде виділено, а посилання при цьому не відкриється.

Варіанти використання

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

Список всіх аркушів файлу та посилання на них

У випадку, коли ви маєте щомісячну звітність, ви можете помістити звіти за кожен місяць в один файл і зробити список з посиланнями на кожен місяць. Це можна зробити за допомогою Visual Basic.

Код:

Sub CreateSummary() Dim x As Worksheet Dim Counter As Integer Counter = 0 Для кожного x In Worksheets Counter = Counter + 1 If Counter = 1 Then GoTo Donothing With ActiveCell .Value = x.Name .Hyperlinks.Add ActiveCell, ” .Name & “!A1″, TextToDisplay:=x.Name, ScreenTip:=”Натисніть на Worksheet” З 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

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

Використання гіперпосилань в Excel

Запускайте цей макрос на першому аркуші файлу, інакше він спрацює неправильно.

Як створити динамічне гіперпосилання?

Зазвичай гіперпосилання – це статичні дані. Ви просто вказуєте посилання на будь-який сайт або місце, і вона веде туди.

Але що якщо ви хочете, щоб гіперпосилання в осередках підлаштовувалися під користувача? Наприклад, коли користувач вибирає компанію, гіперпосилання автоматично змінюється на сайт цієї компанії.

У нашому прикладі буде так:

Використання гіперпосилань в Excel

Такого ефекту можна досягти, використовуючи формулу:

=ГІПЕРСИЛКА(ВПР(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 поділяє рядки тексту.

Сподіваюся, ця стаття була корисною для вас!

Чекаю на ваші коментарі.

nBook
Додати коментар