Как найти и вернуть активную гиперссылку в Excel?
В повседневной работе с Excel функция ВПР (VLOOKUP) часто используется для поиска значений и связанных с ними данных — возвращаемого результата. Однако распространённая проблема возникает, когда результатом поиска должны быть гиперссылки: вместо кликабельных активных ссылок вы получаете лишь текстовые URL-адреса. Например, если в ячейке с исходными данными содержится веб-ссылка с внедрённой гиперссылкой, после стандартного применения ВПР результат отобразится только как неформатированный текст, как показано ниже.
В таких случаях обеспечение возврата активной гиперссылки — той, по которой можно щёлкнуть и которая открывается в браузере, — повышает удобство использования, экономит время и особенно важно для наборов данных, содержащих веб-адреса, пути к файлам или другие кликабельные ресурсы.
В этом руководстве представлены практические решения для восстановления активных гиперссылок с помощью поиска, разбираются сценарии их применения, подходящие типы данных и возможные ограничения. Вы также узнаете ключевые меры предосторожности, советы по устранению неполадок и рекомендации по выбору оптимального метода для ваших задач в таблице.
- Поиск с возвратом активной гиперссылки по формуле
- Код VBA – Возврат и вставка активной гиперссылки через поиск (для продвинутых сценариев)
Поиск с возвратом активной гиперссылки по формуле
Чтобы найти значение и вернуть его в виде активной гиперссылки, объедините функции HYPERLINK и VLOOKUP. Этот простой подход идеально подходит для случаев, когда исходные данные содержат гиперссылки в виде текстовых URL-адресов (например, «https://www.example.com») или сетевых путей к файлу. Возвращаемое значение станет кликабельной гиперссылкой прямо в вашей таблице.
Предположим, у вас есть таблица с двумя столбцами: один содержит значения для поиска (например, имя), а другой — URL в виде обычного текста или гиперссылки. Чтобы получить соответствующую активную гиперссылку на основе значения, введённого пользователем, выполните следующие действия:
1. Введите следующую формулу в пустую ячейку, где должен отображаться результат:
=HYPERLINK(VLOOKUP(D2, $A$1:$B$8,2, FALSE))2. Нажмите клавишу Enter, чтобы подтвердить. Теперь ячейка отображает гиперссылку в виде активной кликабельной ссылки, как показано ниже:
Параметры и примечания по использованию:
- D2: Ячейка, содержащая значение, которое нужно найти.
- $A$1:$B$8: Диапазон данных, в котором первый столбец содержит значения для поиска, а второй — гиперссылки. Используйте абсолютные ссылки, если планируете копировать формулу.
- 2: Указывает, что гиперссылка находится во втором столбце вашего диапазона.
Советы:
- Если искомое значение не найдено, формула вернёт ошибку #Н/Д. Убедитесь, что значение в диапазоне поиска точно совпадает со значением в таблице.
- Если вы хотите, чтобы отображаемый текст отличался от фактической гиперссылки (например, вместо URL отображалось имя), просто добавьте необязательный второй параметр в функцию HYPERLINK:
=HYPERLINK(VLOOKUP(D2,$A$1:$B$8,2,FALSE),D2)
В этом случае значение из ячейки D2 будет использоваться как текст ссылки. - Этот метод работает только в том случае, если гиперссылки сохранены в виде стандартного URL или текстового пути к файлу. Он не восстанавливает гиперссылки, созданные в Excel с помощью функции «Создать гиперссылку», где отображаемый текст и адрес гиперссылки различаются, а также «дружественные» отображаемые имена, если исходный URL отсутствует в ячейке.
Типичные проблемы и устранение неполадок:
- Если результат не кликабелен, убедитесь, что ваши данные содержат полный и корректный веб-адрес (включая «http://» или «https://»).
- Если результаты отсутствуют или некорректны, проверьте диапазон поиска и убедитесь, что индекс столбца указывает именно на столбец с гиперссылками.
- Для локальных файлов убедитесь, что путь гиперссылки указан в правильном формате (например, «C:\Папка\файл.xlsx»).
Преимущества: Простота настройки — формулу легко протянуть на несколько строк; идеально подходит для таблиц, где гиперссылки хранятся в виде текстовых URI (обычный текст).
Ограничения: Не поддерживает отдельное извлечение отображаемого текста. Если отображаемый текст и адрес гиперссылки различаются, а также если гиперссылки созданы вручную (например, когда в ячейке отображается только текст), такие ссылки не распознаются.
Код VBA – Возврат и вставка активной гиперссылки через поиск (для продвинутых сценариев)
Если вам нужно найти конкретное значение в диапазоне и вернуть его исходную активную гиперссылку — включая как отображаемый текст, так и саму кликабельную ссылку, а не просто текстовый URL, — VBA предлагает надёжное решение. Этот код ищет целевое значение в выбранном вами диапазоне и, обнаружив совпадение, копирует точную гиперссылку (отображаемый текст и адрес) в указанную ячейку. Это особенно полезно, когда отображаемый текст и адрес гиперссылки различаются или когда формулы не справляются с извлечением фактической ссылки.
Этот метод особенно полезен, если ваши данные содержат «дружелюбные» отображаемые имена с внутренними гиперссылками, ссылки на файлы или папки, а также гиперссылки в нестандартных форматах. С помощью VBA вы можете копировать как видимый текст ссылки, так и её внутренний адрес, либо вставлять гиперссылку в новое место на основе результата поиска.
Меры предосторожности: Убедитесь, что макросы включены в вашей среде Excel. Всегда создавайте резервную копию книги перед запуском скриптов VBA, особенно если вы работаете с важными данными.
Преимущества: Обрабатывает сложные случаи — например, гиперссылки, заданные на уровне ячейки («Создать гиперссылку»), а также разделение отображаемого текста («Отображаемый текст») и адреса ссылки («Адрес гиперссылки»). Позволяет обрабатывать гиперссылки пакетами или настраивать результаты.
Ограничения: Требует базового знакомства с VBA и не поддерживается во всех ограниченных или веб-средах Excel.
1. Откройте редактор VBA, выбрав на ленте вкладку Разработчик > Visual Basic. В открывшемся окне выберите Вставка > Модуль и вставьте следующий код в модуль:
Sub LookupAndInsertHyperlink()
Dim LookupValue As String
Dim LookupRange As Range
Dim ResultCell As Range
Dim cell As Range
Dim hyperlinkFound As Boolean
Dim linkAddress As String
Dim linkText As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set LookupRange = Application.InputBox("Select the lookup range (must include display text/cell and hyperlink)", xTitleId, Selection.Address, Type:=8)
Set ResultCell = Application.InputBox("Select the cell to output the hyperlink", xTitleId, "", Type:=8)
LookupValue = Application.InputBox("Enter the value to lookup", xTitleId, "", Type:=2)
hyperlinkFound = False
For Each cell In LookupRange
If cell.Value = LookupValue Then
If cell.Hyperlinks.Count > 0 Then
linkAddress = cell.Hyperlinks(1).Address
linkText = cell.Value
ResultCell.Hyperlinks.Add Anchor:=ResultCell, Address:=linkAddress, TextToDisplay:=linkText
hyperlinkFound = True
Exit For
End If
End If
Next
If Not hyperlinkFound Then
ResultCell.Value = "No matching hyperlink found"
End If
End Sub2. Чтобы запустить скрипт, откройте книгу и нажмите сочетание клавиш Alt + F8, выберите макрос LookupAndInsertHyperlink и нажмите кнопку Выполнить.
3. В появляющихся диалоговых окнах:
- Выберите диапазон для поиска: диапазон данных (включая как значения, так и гиперссылки).
- Выберите ячейку, в которую будет вставлена гиперссылка.
- Введите значение, которое нужно найти. Макрос отыщет соответствующую ячейку, извлечёт её гиперссылку (даже если отображаемый текст отличается от самой ссылки) и вставит эту гиперссылку как активную в выбранное место.
Практические советы и напоминания об ошибках:
- Если значение не найдено или в ячейке отсутствует гиперссылка, в целевой ячейке отобразится сообщение «Совпадающая гиперссылка не найдена».
- Если требуется обработать несколько запросов одновременно, рассмотрите возможность расширения кода VBA или запуска скрипта многократно — по мере необходимости.
- Этот скрипт эффективен даже тогда, когда гиперссылка в ячейке представлена не просто текстовой строкой URL, — он копирует и адрес, и отображаемый текст.
Рекомендации по устранению неполадок:
- Убедитесь, что ваш входной диапазон включает столбец с фактическими гиперссылками.
- Если макрос VBA не запускается, убедитесь, что макросы включены в настройках Excel.
- Если появляется сообщение «Совпадающая гиперссылка не найдена», внимательно проверьте правильность введённого значения и убедитесь, что в этой строке действительно есть соответствующие гиперссылки.
- Всегда сохраняйте книгу перед запуском макросов — на случай, если понадобится отменить внесённые изменения.
Итог:
- Используйте метод с формулой для создания стандартных текстовых гиперссылок и быстрого поиска.
- Используйте метод с VBA для решения более сложных задач — например, для восстановления созданных вручную гиперссылок, создания гиперссылки с заданием как отображаемого текста, так и адреса ссылки, или динамического применения результатов к диапазонам.
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек