Как извлечь гиперссылку из ячейки Excel
Обновлено: 21.11.2024
Иногда вы копируете веб-страницы. Или просто ссылка. Или вы получаете лист Excel со ссылками в нем. В таком случае вы часто хотите извлечь адреса гиперссылок из ячеек. По сути, есть всего три варианта извлечения адреса гиперссылки из ячейки Excel.
Способ 1. Извлечение адресов ссылок вручную
Сначала плохие новости: в Excel нет встроенного способа считывания гиперссылки, например, с помощью формулы. Таким образом, первый подход заключается в вводе адресов гиперссылок вручную.
- Щелкните правой кнопкой мыши ячейку, содержащую адрес гиперссылки.
- Нажмите «Изменить гиперссылку…».
- Скопируйте ссылку из поля адреса и вставьте ее в нужное место.
Этот метод может работать только для нескольких ссылок. Но если у вас есть больше гиперссылок для извлечения, вы можете продолжить со вторым или третьим вариантом. Кроме того, вы можете попробовать нажать на адрес ссылки. Таким образом, откроется окно интернет-браузера, и вы сможете скопировать адрес ссылки из адресной строки.
Способ 2. Использование VBA для возврата адреса гиперссылки
Поскольку у нас есть плохие новости, связанные с первым вариантом, есть и хорошие новости: с помощью короткого макроса VBA по-прежнему можно извлечь адреса ссылок.
Следующий макрос определяет новую функцию Excel. Вы можете использовать его, введя «= PROFEXHyperlinkAddress (A1)» в свою ячейку (вместо A1 вы, конечно, можете использовать любую ссылку на ячейку). После нажатия клавиши ввода отобразится гиперссылка ячейки A1. Если в ячейке нет гиперссылки, ничего не будет показано.
Просто начните с открытия окна VBA (лента «Разработчик» -> «Редактор»). Затем добавьте новый модуль (щелкните правой кнопкой мыши в Project Explorer -> Insert -> Module) и вставьте следующий код в новый макрос. Если вам нужна дополнительная помощь по макросам VBA, обратитесь к этой статье.
Способ 3. Вставка адреса ссылки с помощью «Professor Excel Tools»
Третий вариант не требует использования VBA. Просто загрузите надстройку для Excel Professor Excel Tools снизу (без регистрации, просто активируйте ее в Excel). Для получения адреса ссылки введите =PROFEXHyperlinkAddress(A1) в ячейку. Вместо A1 вы ссылаетесь на ячейку, содержащую ссылку.
Эта функция включена в нашу надстройку Excel «Professor Excel Tools»
Если вы работаете с данными, скопированными из Интернета в Excel, вы часто получаете набор данных, содержащий гиперссылки.
Например, ниже приведен пример набора данных, в котором у меня есть названия некоторых компаний, и эти названия связаны с URL-адресом этой компании.
Теперь, если я хочу извлечь URL-адреса из этих гиперссылок в Excel, для этого нет встроенной функции или формулы.
Однако для этого можно создать собственную формулу с помощью VBA.
В этом руководстве я покажу вам несколько простых способов быстрого извлечения части URL из гиперссылки путем создания простой пользовательской формулы в VBA.
Итак, приступим!
Оглавление
Извлечение URL из гиперссылок с помощью формулы
Несмотря на то, что в Excel имеется более 450+ функций, если вы не можете найти ту, которая вам подходит, вы также можете создать свою собственную формулу.
Ниже приведен набор данных, в котором у меня есть ячейки с гиперссылками, и я хочу извлечь URL-адрес из гиперссылки каждой ячейки.
Ниже приведен код VBA, который создаст пользовательскую функцию (ExtractURL), которая может работать так же, как обычная функция на листах:
Теперь позвольте мне подробно описать, как добавить этот код VBA в:
- Откройте книгу, в которую вы хотите добавить этот код VBA
- Удерживая нажатой клавишу ALT, нажмите клавишу F11 (Opt + F11 или Fn + Opt + F11, если вы используете Mac). Откроется окно редактора VB.
- В левой части окна должна быть панель проводника проекта. Если его там нет, вы можете нажать на вкладку «Вид», а затем нажать на опцию «Проводник проектов».
- Щелкните правой кнопкой мыши любой объект книги, в которой вы хотите получить эту функцию для извлечения URL-адресов из гиперссылок.
- Перейдите к вкладке «Вставка» и щелкните модуль. Это добавит модуль к объектам книги.
- Дважды щелкните модуль. Откроется окно кода для модуля.
- Скопируйте и вставьте приведенный выше код в окно кода модуля.
- Закройте редактор VB
- Щелкните правой кнопкой мыши гиперссылку. Появится контекстное меню.
- В контекстном меню выберите «Редактировать гиперссылку». Excel отображает диалоговое окно «Редактировать гиперссылку». (См. рис. 1.)
- введите URL
- или используйте команду "Вставить гиперссылку"
- или перетащите
- или используйте функцию ГИПЕРССЫЛКИ
- Выберите ячейку, в которую вы хотите добавить гиперссылку.
- На ленте Excel перейдите на вкладку «Вставка» и выберите команду «Гиперссылка»
- ИЛИ нажмите правой кнопкой мыши на ячейку и выберите "Связать".
- ИЛИ используйте сочетание клавиш – Ctrl + K .
Отображаемый текст
В верхней части диалогового окна "Вставить гиперссылку" есть поле "Текст для отображения". То, что вы видите в поле «Отображаемый текст», зависит от того, что находится в ячейке, в которой вы создаете гиперссылку.
Вот 4 различных типа контента, которые влияют на параметр «Текст для отображения»:
- Текст в ячейке
- Пустая ячейка
- Число в ячейке
- Число "Текст" в ячейке
Текст в ячейке
Если выбранная ячейка содержит текст, этот текст появится в поле Текст для отображения.
- Вы можете редактировать текст для отображения текста
- Измененный текст появится в ячейке после создания гиперссылки.
Пустая ячейка
Если выбранная ячейка пуста, имя листа и ячейка A1 появятся в поле Текст для отображения.
- Вы можете редактировать текст для отображения текста
- Измененный текст появится в ячейке после создания гиперссылки.
Число в ячейке
Если выбранная ячейка содержит действительное число, вы не сможете редактировать поле Текст для отображения.
Содержимое коробки затемнено, и отображается следующее сообщение: >
- ПРИМЕЧАНИЕ. Вы увидите то же сообщение, если добавляете гиперссылку к фигуре на листе.
Если вы хотите отредактировать отображаемый текст:
- Создайте гиперссылку, а затем измените значение в ячейке.
- Закройте диалоговое окно "Вставить гиперссылку".
- Замените номер ячейки на текст (отформатируйте как текст или введите апостроф перед номером)
- Снова откройте диалоговое окно "Вставить гиперссылку", и поле "Отображаемый текст" станет доступным для редактирования.
Число "Текст" в ячейке
Если выбранная ячейка содержит "текстовое" число (отформатированное как текст или начинающееся с апострофа), это текстовое число появится в поле "Текст для отображения".
- Вы можете редактировать текст для отображения текста
- Измененный текст появится в ячейке после создания гиперссылки.
Добавить всплывающую подсказку (необязательно)
При наведении указателя на ячейку, содержащую гиперссылку, появляется всплывающая подсказка. Вы можете ввести собственный текст для всплывающей подсказки или оставить этот параметр пустым.
ПРИМЕЧАНИЕ. Если оставить всплывающую подсказку пустой, в всплывающей подсказке появится полный адрес гиперссылки.
Чтобы добавить собственный текст для экранной подсказки:
- В окне "Вставить гиперссылку" нажмите кнопку "Подсказка".
- Введите текст для всплывающей подсказки – веб-сайт Google.
- Нажмите "ОК".
Выберите тип ссылки
На панели «Ссылки на» в окне «Вставить гиперссылку» есть четыре параметра:
- Существующий файл или веб-страница
- Поместить в этот документ
- Создать новый документ
- Адрес электронной почты
В левой части окна "Вставить гиперссылку" выберите нужный тип ссылки.
Существующий файл или веб-страница
Используйте этот параметр, если хотите, чтобы люди могли легко открыть другой файл Excel или перейти на веб-страницу для получения дополнительной информации.
- В списке «Связать с» нажмите «Существующий файл или веб-страница».
- Чтобы создать ссылку на существующий файл, используйте навигацию по папкам, чтобы найти и выбрать нужный файл
- ИЛИ, чтобы создать ссылку на веб-страницу, введите ее URL в поле "Адрес".
Создание гиперссылок с помощью перетаскивания
Если вы создаете гиперссылки в файле Excel, самым быстрым и простым способом является использование перетаскивания. Например, вы можете создать в книге оглавление (TOC) со ссылками на другой лист.
Чтобы увидеть краткий обзор того, как можно создать гиперссылку путем перетаскивания, посмотрите это короткое видео. Письменные инструкции находятся под видео.
Если у вас возникли проблемы с перетаскиванием на другой лист, перетащите его в ближайшую пустую ячейку, затем вырежьте и вставьте на другой лист. Этот анимированный gif показывает эти шаги.
Создание гиперссылки с помощью перетаскивания
Выполните следующие действия, чтобы создать гиперссылки перетаскиванием.
- Сначала сохраните книгу, если она еще не сохранена. (В новой рабочей книге, которая не была сохранена, этот метод не будет работать.)
- Затем щелкните ячейку, на которую хотите установить ссылку. (Вы собираетесь перетащить эту ячейку на другой лист, где вы хотите создать гиперссылку)
- Наведите указатель мыши на границу ячейки и нажмите правую кнопку мыши.
- Чтобы перетащить ячейку на другой лист, нажмите клавишу Alt и перетащите ячейку на вкладку другого листа.
Отключить автоматические гиперссылки в Excel
Если вы не хотите, чтобы гиперссылки автоматически создавались при вводе адреса веб-сайта, вы можете отключить функцию гиперссылок. Чтобы увидеть краткий обзор того, как вы можете предотвратить автоматическое создание автоматических гиперссылок, вы можете посмотреть это короткое видео. Письменные инструкции находятся под видео.
Чтобы отключить гиперссылку::
- На ленте Excel перейдите на вкладку "Файл" и нажмите "Параметры".
- Выберите категорию "Правописание" и нажмите кнопку "Параметры автозамены".
- На вкладке "Автоформат при вводе" снимите флажок с Интернета и сетевых путей с гиперссылками.
- Дважды нажмите "ОК", чтобы закрыть диалоговые окна.
Использовать функцию ГИПЕРССЫЛКИ
Чтобы просмотреть краткий обзор того, как создать гиперссылку с помощью функции ГИПЕРССЫЛКА, посмотрите это короткое видео. Письменные инструкции находятся под видео
Ниже приведены письменные инструкции по использованию функции HYPERLINK для:
Создайте гиперссылку на веб-сайт или электронную почту
Если в Excel есть список адресов электронной почты или веб-сайтов, вы можете создать гиперссылку для каждой строки в отдельном столбце. В этом примере в столбце A указано имя, а в столбце B URL-адрес веб-сайта или адрес электронной почты. Гиперссылки будут созданы в столбце C.
- Вы будете использовать функцию HYPERLINK, которая принимает два аргумента:
- Link_Location: это адрес электронной почты или URL-адрес. Для адреса электронной почты начинайте с «mailto:», а для URL — с «https://»
- Дружественное имя. Этот аргумент является необязательным и позволяет указать «понятное» имя для ссылки. Например, вместо длинного URL можно указать название компании или краткое описание.
Чтобы создать гиперссылку для каждого адреса:
- В ячейке C2 введите формулу: =ГИПЕРССЫЛКА(B2,A2)
- Нажмите Enter, чтобы увидеть гиперссылку в C3. Он использует ссылку в ячейке B2, а текст в ячейке A2 отображается в ячейке C2 в качестве понятного имени.
- Скопируйте формулу в остальные строки.
ГИПЕРССЫЛКА Формулы на файл Excel
С помощью формулы ГИПЕРССЫЛКИ вы можете создать ссылку на место в текущей книге Excel или на другой файл Excel. Посмотрите это видео, чтобы увидеть шаги, а письменные инструкции находятся под видео.
Ниже приведены письменные инструкции по ссылке на файл Excel:
Ссылка в одном файле
Вот примеры формул HYPERLINK в одной книге:
Введите адрес
Необязательно. Введите понятное имя, например "Бюджет", которое появится в ячейке:
Использовать ссылки на ячейки
Используйте оператор &, чтобы создать ссылку.
В этом примере имя листа находится в ячейке B5, адрес ячейки — в C5, а понятное имя — в ячейке D5.
Введите имя диапазона
Для ссылки на именованный диапазон уровня книги в той же книге просто используйте имя диапазона в качестве местоположения ссылки.
Следующая пустая ячейка
Перейти к пустой ячейке в конце списка рабочих листов или именованной таблицы.
Список рабочих листов
В ячейке B1 формула находит первую пустую ячейку после списка рабочего листа в столбце A:
Чтобы найти номер пустой строки, добавьте количество элементов в столбце A и номер строки для начальной ячейки.
Именованная таблица
В ячейке E1 формула находит первую пустую ячейку после именованной таблицы в столбце D:
Чтобы найти номер пустой строки, формула вычисляет СУММУ:
Ссылка на другой файл Excel
Чтобы создать ссылку на другой файл Excel в той же папке, просто используйте имя файла в качестве аргумента link_location для функции HYPERLINK.
=ГИПЕРССЫЛКА("МойФайлC.xlsx","ФайлC")
Для файлов, которые находятся на уровне выше в иерархии, используйте две точки и обратную косую черту для каждого уровня.
-
В этом примере файл находится на 1 уровень выше.
=ГИПЕРССЫЛКА("..\MyFileB.xlsx","FileB")
=ГИПЕРССЫЛКА("..\..\МойФайлA.xlsx","ФайлA")
Удалить гиперссылки
Ниже приведены инструкции по двум методам удаления гиперссылок
Удалить гиперссылки вручную
В Excel 2010 или более поздней версии:
В Excel 2010 можно быстро удалить гиперссылки из выбранных ячеек. (Спасибо Сэму за этот совет.)
- Выберите ячейки, содержащие гиперссылки
- Щелкните правой кнопкой мыши любую выделенную ячейку.
- Нажмите "Удалить гиперссылки".
Чтобы увидеть шаги, посмотрите это видео с советами по Excel.
В Excel 2007 и более ранних версиях:
Чтобы удалить гиперссылки из группы ячеек, можно скопировать и вставить ячейки как значения. Чтобы узнать, как это сделать, посмотрите видео с краткими советами по Excel.
Макрос для удаления всех гиперссылок в выбранных ячейках
Вместо удаления гиперссылок вручную вы можете использовать следующий код макроса. Он удаляет все гиперссылки в выбранных ячейках и оставляет только текст ячейки.
Скопируйте этот код в обычный модуль в своей книге, затем выберите ячейки и запустите макрос delHyperlinks.
Указать гиперссылку
Выполните следующие действия, чтобы указательная рука появилась в любом месте ячейки с гиперссылкой, и узнайте, как выбрать ячейку с гиперссылкой, не переходя по ссылке.
Показать указывающую руку
Если широкая ячейка содержит короткую гиперссылку, указывающая рука будет отображаться только тогда, когда она находится над текстом, а не на пустом месте. Чтобы это исправить:
- Выберите ячейки
- На вкладке "Главная" ленты Excel нажмите команду "Перенос текста".
Выберите ячейку гиперссылки
Если вам нужно выделить ячейку с гиперссылкой, возможно, для редактирования текста, не переходя по ссылке:
- Наведите указатель на ячейку и нажмите левую кнопку мыши на пару секунд.
- Указатель изменит свой вид на большой белый знак плюса, показывая, что ячейка выбрана.
- Теперь вы можете редактировать ячейку с гиперссылкой.
Создать список листов со ссылками (макрос)
Совет. На каждом рабочем листе поместите гиперссылку на оглавление.
Интерактивные гиперссылки для экспорта в PDF
В некоторых версиях Excel у вас могут возникнуть проблемы с гиперссылками при создании файла PDF из книги Excel.
Ссылки в PDF неактивны
В файле PDF некоторые гиперссылки могут быть недоступны, если:
- гиперссылка создана с помощью формулы Excel
- понятное имя гиперссылки отличается от ее местоположения
Избегайте проблемы
Чтобы избежать этой проблемы, выполните следующие действия:
- вручную введите гиперссылки вместо использования функции ГИПЕРССЫЛКИ
- или опустите аргумент понятного имени, чтобы в ячейке отображалась полная ссылка на местоположение.
Создание поддельных гиперссылок в сводной таблице
Гиперссылки нельзя добавлять в сводную таблицу, даже если в исходных данных есть гиперссылки. В этом видео вы увидите, как создать поддельные гиперссылки в сводной таблице, используя несколько строк кода Excel VBA.
Код показан под видео, и код был обновлен для обработки адресов электронной почты.
Создание поддельных гиперссылок в сводной таблице
Извлечь адрес гиперссылки
Для извлечения адреса из гиперссылки можно использовать макрос или определяемую пользователем функцию (UDF).
Макрос для извлечения адреса в соседнюю ячейку
Вы можете использовать макрос, чтобы извлечь адрес из каждой гиперссылки на листе и сохранить его в ячейке справа от каждой ячейки гиперссылки. Спасибо Фабио Пуччинелли за этот пример.
ПРИМЕЧАНИЕ. Гиперссылки, созданные с помощью функции HYPERLINK, не распознаются как гиперссылки рабочего листа. Распознаются только те гиперссылки, которые вы вводите или вставляете.
Создать функцию для извлечения адреса гиперссылки
Нет встроенной функции для извлечения URL-адреса или адреса электронной почты из гиперссылки. Вы можете создать свою собственную функцию Excel — определяемую пользователем функцию (UDF) — для извлечения адреса.
Чтобы просмотреть краткий обзор того, как создать собственную пользовательскую функцию для извлечения местоположения гиперссылки, посмотрите это короткое видео. Письменные инструкции находятся под видео.
ПРИМЕЧАНИЕ. Гиперссылки, созданные с помощью функции HYPERLINK, не распознаются как гиперссылки рабочего листа.
Создать определяемую пользователем функцию гиперссылки (UDF)
Скопируйте этот код в обычный модуль в своей книге, а затем используйте функцию HLink в этой книге, как и любую другую функцию. Например, чтобы найти URL-адрес гиперссылки в ячейке B3, используйте следующую формулу:
Примечание. Если выбрано несколько ячеек, формула покажет гиперссылку (если есть) из первой ячейки в выбранном диапазоне.
Скрыть листы после гиперссылки
В большой книге можно использовать гиперссылки в качестве оглавления, чтобы быстро перейти к определенному листу. Затем на каждом листе создайте еще одну гиперссылку, чтобы вернуться к оглавлению.
Чтобы все выглядело аккуратно, вы можете использовать код, который скрывает каждый лист после того, как вы щелкнете гиперссылку на этом листе. Спасибо Джиму Уильямсу за предложение этой техники.
Читайте также:
Выполнив описанные выше шаги, вы можете использовать функцию ExtractURL в любой ячейке рабочего листа.
С нашим набором данных я могу использовать следующую формулу:
Преимущество использования формулы заключается в том, что она динамична. Таким образом, если вы измените исходные данные (или скопируете и вставите новые данные из Интернета с другими гиперссылками), формула автоматически обновится, чтобы предоставить вам URL-адрес в новом наборе данных.
Обратите внимание, что когда вы добавляете этот код в книгу, вы можете использовать только что созданную функцию только в этой книге. Он не будет работать в других книгах, в которые не был добавлен код.
Если вы хотите, чтобы этот код работал во всей вашей книге, вам нужно добавить его либо в книгу личных макросов, либо создать надстройку и добавить эту надстройку в Excel.
Кроме того, когда вы добавляете код макроса VBA в книгу, вам необходимо сохранить его в формате .XLSM (Excel с поддержкой макросов).
Извлечение URL из гиперссылок в соседней ячейке с помощью макроса VBA
Есть еще один метод, который можно использовать для извлечения всех URL-адресов из ячеек, содержащих гиперссылки.
Хотя здесь также используется макрос VBA, формула не создается. Вместо этого он просто использует код VBA для просмотра каждой выбранной ячейки и извлечения гиперссылки из каждой ячейки в соседней ячейке.
Предположим, у вас есть набор данных, как показано ниже, и вы хотите получить URL-адреса этих гиперссылок в соседнем столбце.
Ниже приведен код VBA для этого:
Приведенный выше код переходит к каждой ячейке в выделенном фрагменте и, если он содержит гиперссылку, извлекает URL-адрес из соседней ячейки (с помощью свойства OFFSET).
Вам необходимо поместить этот код в модуль (те же шаги, что и в разделе выше).
После того как у вас есть этот код в модуле, вы можете запустить этот макрос, нажав зеленую кнопку запуска на панели инструментов или выбрав любую строку в коде и нажав сочетание клавиш F5.
В Mezga есть ряд ячеек, содержащих гиперссылки. Эти гиперссылки состоят из таких слов, как «нажмите здесь» или «дополнительная информация». Другими словами, каждая гиперссылка содержит отображаемый текст, отличный от базового URL-адреса, который активируется при нажатии на ссылку. Мезга хотел бы знать, есть ли способ без использования макроса извлечь базовый URL-адрес для каждой из этих гиперссылок и поместить этот URL-адрес в другую ячейку.
Не используя макросы, вы можете сделать следующее:
Рисунок 1. Диалоговое окно «Редактировать гиперссылку».
Обратите внимание, что это для одной гиперссылки. Если у вас есть целая куча гиперссылок на листе и вы хотите восстановить URL-адреса, вам нужно сделать это для каждой гиперссылки. Очевидно, что это может очень быстро надоесть.
Лекарство от скуки — нравится вам это или нет — это макрос. С помощью макроса получение базового URL-адреса для гиперссылки становится детской игрой. Все, что нужно сделать макросу, — обратить внимание на свойство Address гиперссылки. Ниже приведен пример макроса, который находит каждую гиперссылку на листе, извлекает URL-адрес каждой из них и вставляет этот URL-адрес в ячейку непосредственно справа от гиперссылки.
Вместо макроса "грубой силы" вы также можете создать определяемую пользователем функцию, которая будет извлекать и возвращать URL-адрес любой гиперссылки, на которую он указывает:
В этом случае вы можете разместить его там, где хотите. Если вы хотите, например, чтобы URL-адрес гиперссылки в A1 был указан в ячейке C25, то в ячейке C25 вы должны ввести следующую формулу:
Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию. Нажмите здесь, чтобы открыть эту специальную страницу в новой вкладке браузера.
Биография автора
На его счету более 50 научно-популярных книг и множество журнальных статей. Аллен Уайатт является всемирно признанным автором. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнайте больше об Аллене.
Создавайте гиперссылки путем перетаскивания или с помощью функции HYPERLINK. Удалите гиперссылки вручную или с помощью макроса. Создайте оглавление с помощью макроса.
Создание гиперссылок Excel
На листе Excel можно создать гиперссылку в любой ячейке. Затем щелкните эту гиперссылку, чтобы перейти в указанное место.
Вот четыре способа создать гиперссылку. Нажмите на ссылку, чтобы перейти к инструкциям ниже:
Введите URL веб-сайта
Если вы введете действительный адрес веб-сайта в ячейку, Excel должен автоматически создать гиперссылку, когда вы нажмете Enter
Текст изменится на гиперссылку, и вы сможете щелкнуть ссылку, чтобы перейти на веб-сайт Contextures.
Совет. Если вы хотите запретить автоматическое создание гиперссылок в Excel, следуйте приведенным здесь инструкциям.
Вставить гиперссылку
Еще один способ добавить гиперссылку — использовать встроенную команду Excel — Вставить гиперссылку.
Запустить ссылку
Чтобы использовать команду "Вставить гиперссылку", выполните следующие действия: