Руководство по гиперссылкам в Excel: создание, изменение, использование и удаление в Excel

Как известно, гиперссылки обычно используются в интернете для перехода между сайтами. В Excel вы можете создавать гиперссылки не только для открытия веб-страниц, но и для перехода к ячейке, листу, книге и другим объектам. В этом руководстве мы поэтапно объясним, как создавать, изменять, использовать и удалять гиперссылки, чтобы вы быстро освоили работу с ними в Excel.

1.Что такое гиперссылка в Excel?

Гиперссылка в Excel — это ссылка, которая позволяет открыть один из следующих объектов одним щелчком мыши:

1. Файл
2. Определенное место в файле
3. Веб-страница
4. Сообщение электронной почты

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

2. Виды гиперссылок

В Excel существуют два типа гиперссылок: абсолютные и относительные. В этом разделе рассмотрим различия между ними.

2.1 Абсолютные гиперссылки в Excel

Абсолютная гиперссылка содержит полный адрес, включая протокол, домен и путь: protocol://domain/path.

Примеры:

2.2 Относительные гиперссылки в Excel

Относительная гиперссылка всегда содержит меньше информации, чем абсолютная.

Примеры:

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

В Microsoft Excel при создании внешних ссылок можно использовать как абсолютные, так и относительные гиперссылки, однако в этом случае рекомендуется использовать относительные пути. Благодаря относительным гиперссылкам вы сможете перемещать книги без разрыва связей между ними в будущем. Однако это зависит от расположения связанного файла и исходного файла данных:

1. Связанный файл и исходный файл данных находятся в одной папке.
2. Исходный файл данных находится в папке, вложенной в тот же корневой каталог, что и связанный файл.

Например, связанный файл находится по адресу C:\Users\linked.xlsx, а исходный файл данных — по адресу C:\Users\info\source.xlsx. В этом случае, используя относительную гиперссылку, вы можете перемещать оба файла куда угодно, не нарушая и не обновляя гиперссылку, пока исходный файл данных остается в подпапке “info”. Если же используется абсолютная гиперссылка, путь придется обновлять при каждом перемещении файла.

3. Основные способы создания гиперссылки в Excel

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

3.1 Создание гиперссылки с помощью функции Гиперссылка Excel

Самый традиционный способ создания гиперссылки — воспользоваться командой Вставить гиперссылку. В этом разделе мы покажем, как использовать эту команду для создания гиперссылок в Excel.

1. Выделите ячейку, в которую хотите добавить гиперссылку, затем выберите Вставка > Гиперссылка.

Примечания:

2. Затем откроется диалоговое окно Вставить гиперссылку, где вы можете настроить параметры в зависимости от типа создаваемой гиперссылки.

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

В диалоговом окне Вставить гиперссылку, если вы хотите создать внешнюю ссылку на другой файл, выполните следующие действия:

1. Оставьте выбранной опцию Существующий файл или веб-страница в панели Связать с;

2. В списке Искать в укажите файл, на который хотите создать ссылку.

Совет: Вы можете выбрать файл прямо в списке, нажать стрелку раскрывающегося списка Искать в для отображения всех вариантов или нажать значок Обзор файла для открытия диалогового окна Связать с файлом и выбора файла.

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

4. Необязательно: чтобы при наведении курсора на гиперссылку отображалась подсказка, нажмите кнопку Подсказка, введите текст подсказки и нажмите OK.

5. Нажмите OK.

Теперь вы создали гиперссылку на определенный файл. При нажатии на гиперссылку файл откроется сразу.

3.1.2 Создать гиперссылку на веб-сайт

В диалоговом окне Вставить гиперссылку вы также можете создать гиперссылку на веб-страницу следующим образом.

1. Оставьте выбранной опцию Существующий файл или веб-страница в панели Связать с;

2. Заполните поля Адрес и Текст для отображения.

1. Если вы помните адрес веб-страницы, введите его прямо в поле Адрес.
2. Если вы не помните точный адрес веб-страницы, но сохранили его в избранном браузера, нажмите Просмотр веб кнопку в Искать в разделе для открытия браузера. В браузере откройте нужную веб-страницу, затем вернитесь в Вставить гиперссылку диалоговое окно, не закрывая браузер. Вы увидите, что Адрес и Текст для отображения заполняются автоматически текущей открытой веб-страницей.

Совет: Вы можете изменить текст Текст для отображения или добавить Подсказку по необходимости.

3. Нажмите OK.

3.1.3 Создать гиперссылку на определенное место в текущей книге

Чтобы создать гиперссылку на определенное место в текущем листе или книге, настройте диалоговое окно Вставить гиперссылку следующим образом.

1. Оставьте выбранной опцию Существующий файл или веб-страница в панели Связать с;

2. Нажмите кнопку Закладка.

3. В появившемся диалоговом окне Выбор места в документе введите адрес ячейки, к которой хотите перейти, в поле Введите ссылку на ячейку, выберите лист или именованный диапазон по необходимости и нажмите OK.

4. Нажмите OK для завершения настроек, когда вернетесь в диалоговое окно Вставить гиперссылку.

Другой способ: В диалоговом окне Вставить гиперссылку выберите опцию Место в этом документе в панели Связать с, укажите лист/именованный диапазон и ячейку, на которые хотите создать ссылку, затем нажмите OK. См. скриншот:

3.1.4 Создать гиперссылку на адрес электронной почты

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

1. Выберите опцию Адрес электронной почты в панели Связать с;

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

3. В поле Адрес электронной почты введите один или несколько адресов электронной почты, разделяя их точкой с запятой.

4. В поле Тема введите тему письма напрямую.

Совет: После добавления темы письма, если вы хотите включить текст сообщения, добавьте &body=текст сообщения в конец темы, например: FAQs of Kutools for Excel&body=I want to know more about your product.

3. Нажмите OK.

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

Примечание: Чтобы принудительно открыть эту mailto-гиперссылку в Outlook, убедитесь, что Outlook установлен как почтовый клиент по умолчанию на вашем компьютере.

3.2 Создание гиперссылки с помощью функции HYPERLINK

Помимо команды Вставить гиперссылку, вы также можете использовать функцию HYPERLINK для создания гиперссылки в Excel.

Синтаксис

HYPERLINK(link_location, [friendly_name])

Аргументы

Рассмотрим несколько примеров использования функции HYPERLINK для создания гиперссылок в Excel.

Пример1: Ссылка на другой файл с помощью функции HYPERLINK

Допустим, вы хотите создать гиперссылку на файл test.txt, который находится по пути: C:\Users\Win10x64Test\Documents\My files. Используйте функцию HYPERLINK следующим образом:

1. Выделите ячейку для размещения гиперссылки, введите в нее формулу ниже и нажмите Enter.

=HYPERLINK("C:\Users\Win10x64Test\Documents\My files\test.txt","Click to open the txt file named test")

Если связанный файл и исходный файл данных находятся в одной папке, используйте относительную гиперссылку (путь содержит только имя файла и расширение) в функции HYPERLINK следующим образом:

=HYPERLINK("test.txt","Click to open the txt file named test")

Если исходный файл данных находится в папке, вложенной в тот же корневой каталог, что и связанный файл, используйте относительную гиперссылку (путь содержит только имя корневой папки, имя файла и расширение) в функции HYPERLINK следующим образом:

=HYPERLINK("My files\test.txt","Click to open the txt file named test")

После создания гиперссылки щелкните по ней, чтобы открыть файл test.txt напрямую.

Пример2: Ссылка на веб-сайт с помощью функции HYPERLINK

Функция HYPERLINK также поддерживает создание гиперссылок на веб-сайты.

1. Выделите пустую ячейку для размещения гиперссылки, введите в нее формулу ниже и нажмите Enter.

=HYPERLINK("https://www.extendoffice.com/","Extendoffice")

Пример3: Ссылка на определенное место в текущей книге с помощью функции HYPERLINK

Допустим, вы хотите создать ссылку на ячейку D100 листа Sheet4 в текущей книге. Используйте формулу ниже.

1. Выделите пустую ячейку для вывода гиперссылки, введите в нее формулу ниже и нажмите Enter.

=HYPERLINK("#Sheet4!D100","Go to D100 of Sheet4")

Пример4: Ссылка на адрес электронной почты с помощью функции HYPERLINK

Чтобы создать гиперссылку на адрес электронной почты, объедините функцию HYPERLINK с “mailto”.

=HYPERLINK("mailto:"&"email address","text to display")

1. Выделите пустую ячейку, введите в нее формулу ниже и нажмите Enter.

=HYPERLINK("mailto:"&"happysiluvia@gmail.com","email to")

Чтобы добавить два адреса электронной почты в формулу, используйте следующую формулу:

=HYPERLINK("mailto:"&"Email address A"&","&"Email address B","Text to display")

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

4. Создание гиперссылок в различных ситуациях

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

В этом разделе приведены различные ситуации создания гиперссылок и соответствующие методы их реализации.

4.1 Создание нескольких гиперссылок в одной или нескольких ячейках

Используя традиционные методы, можно создать только одну гиперссылку в ячейке за раз. Если нужно добавить несколько гиперссылок в одну или несколько ячеек, воспользуйтесь следующими способами.

4.1.1: Создание нескольких гиперссылок в одной ячейке с помощью фигур

По умолчанию Excel позволяет создавать только одну гиперссылку в ячейке. Однако есть небольшой трюк: используйте фигуры для создания нескольких гиперссылок в одной ячейке.

Допустим, в ячейке B1 есть предложение, как показано на скриншоте ниже, и вы хотите добавить отдельные гиперссылки к текстам “Extendoffice”, Kutools for Excel, Kutools for Outlook и Kutools for Word. Действуйте следующим образом.

1. Сначала отдельно отформатируйте эти тексты как гиперссылки, изменив цвет шрифта на синий и добавив подчеркивание. После этого результат будет выглядеть так:

2. Выберите Вставка > Фигуры > Прямоугольник.

3. Нарисуйте прямоугольник, чтобы перекрыть текст, который нужно сделать гиперссылкой. В данном случае я рисую прямоугольник поверх текста “Extendoffice”. См. скриншот:

4. Щелкните правой кнопкой мыши по прямоугольнику и выберите Формат фигуры в контекстном меню для открытия панели Формат фигуры.

5. В панели Формат фигуры измените параметры Заливка и Линия на Нет заливки и Нет линии на вкладке Заливка и линия.

Теперь фигура стала прозрачной.

6. Оставьте фигуру выделенной, щелкните правой кнопкой мыши и выберите Гиперссылка в контекстном меню.

7. В диалоговом окне Вставить гиперссылку укажите адрес и нажмите OK. См. скриншот:

Совет: В диалоговом окне Вставить гиперссылку вы можете настроить различные типы гиперссылок по необходимости — подробнее см. по ссылке.

8. Повторите шаги2–7 для остальных текстов в ячейке, чтобы добавить к ним гиперссылки.

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

4.1.2 Создание нескольких гиперссылок для нескольких ячеек с помощью удобного инструмента

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

Допустим, у вас есть два столбца: один содержит тексты, другой — соответствующие адреса гиперссылок. Чтобы добавить гиперссылки ко всем текстам на основе соответствующих адресов массово, выполните следующие действия.

1. Выберите Kutools > Ссылка > Конвертировать гиперссылки. См. скриншот:

2. В диалоговом окне Конвертировать гиперссылки настройте параметры следующим образом.

2.1) В разделе Тип преобразования выберите опцию Содержимое ячеек заменяет адреса гиперссылок;

2.2) В Входной диапазон поле нажмите кнопку, чтобы выбрать диапазон ячеек с адресами гиперссылок;

2.3) В Результирующий диапазон поле нажмите кнопку, чтобы выбрать диапазон текстовых ячеек, в которые нужно массово добавить гиперссылки;

2.4) Нажмите OK. См. скриншот:

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

4.2 Создание гиперссылки на каждый лист книги

Допустим, у вас есть рабочая книга с12 листами (от января до декабря), и вам часто нужно переходить между ними для анализа данных. В такой ситуации создание связующей таблицы с гиперссылками на листы значительно упростит навигацию и сэкономит время. В этом разделе подробно рассмотрены два способа быстрого создания связующей таблицы листов в текущей книге.

4.2.1 Создание гиперссылки на каждый лист книги с помощью VBA-кода

Вы можете использовать приведенный ниже VBA-код для создания связующей таблицы листов с гиперссылками в книге.

1. В книге, где нужно создать связующую таблицу, одновременно нажмите клавиши Alt + F11.

2. В окне Microsoft Visual Basic for Applications выберите Вставка > Модуль и скопируйте приведенный ниже VBA-код в окно модуля.

VBA-код: Создание связующей таблицы листов с гиперссылками в текущей книге

Sub CreateIndex()
'Updateby Extendoffice 20210825
    Dim xAlerts As Boolean
    Dim I  As Long
    Dim xShtIndex As Worksheet
    Dim xSht As Variant
    xAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Index").Delete
    On Error GoTo 0
    Set xShtIndex = Sheets.Add(Sheets(1))
    xShtIndex.Name = "Index"
    I = 1
    Cells(1, 1).Value = "INDEX"
    For Each xSht In ThisWorkbook.Sheets
        If xSht.Name <> "Index" Then
            I = I + 1
            xShtIndex.Hyperlinks.Add Cells(I, 1), "", "'" & xSht.Name & "'!A1", , xSht.Name
        End If
    Next
    Application.DisplayAlerts = xAlerts
End Sub

3. Нажмите клавишу F5 для запуска кода.

После этого будет добавлен новый лист с именем “Index” перед всеми листами. Все имена листов с гиперссылками будут перечислены в столбце — вы сможете перейти на нужный лист одним щелчком по имени.

4.2.2 Легко создать гиперссылку на каждый лист книги с помощью удобного инструмента

Если вы не хотите работать с VBA-кодом, рекомендуем воспользоваться функцией Создать связующую таблицу из Kutools для Excel. С помощью этой функции вы сможете создать связующую таблицу листов в книге всего за несколько кликов.

1. В книге, где нужно создать связующую таблицу, выберите Kutools Plus > Лист > Создать связующую таблицу.

2. В диалоговом окне Создать связующую таблицу выполните следующие настройки.

Совет: Количество строк для объединения: при необходимости можно вывести связующую таблицу в2 и более столбца, если список слишком длинный для одного экрана.

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

4.3 Создание динамических гиперссылок на основе значения ячейки

Как показано в демо ниже, есть два листа: один содержит раскрывающийся список, другой — исходные данные для этого списка. Теперь нужно создать гиперссылку рядом со списком, чтобы после выбора элемента из списка клик по гиперссылке сразу переходил к соответствующей ячейке на листе с исходными данными.

Подробнее о создании динамических гиперссылок на основе значения ячейки в Excel.

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

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

4.4.1 Преобразование текста URL в кликабельную гиперссылку с помощью VBA-кода

Вы можете запустить следующий VBA-код для создания гиперссылок из текстовых URL в Excel.

1. Нажмите Alt + F11 для открытия окна Microsoft Visual Basic for Applications.

2. В окне Microsoft Visual Basic for Applications выберите Вставка > Модуль и скопируйте приведенный ниже VBA-код в окно модуля.

VBA-код: Преобразование текста URL в кликабельную гиперссылку в Excel

Sub ConvertToHyperlinks()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    Application.ActiveSheet.Hyperlinks.Add Rng, Rng.Value
Next
End Sub

3. Нажмите F5 для запуска кода. В появившемся диалоговом окне Kutools для Excel выберите диапазон с текстами, которые нужно преобразовать в гиперссылки, и нажмите OK.

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

4.4.2 Преобразование текста URL в кликабельную гиперссылку с помощью удобного инструмента

В этом разделе представлена удобная функция — Конвертировать гиперссылки из Kutools для Excel, которая позволяет легко преобразовать текстовые URL в кликабельные гиперссылки в Excel.

1. Выделите диапазон с текстовыми URL, которые нужно преобразовать в гиперссылки, затем выберите Kutools > Ссылка > Конвертировать гиперссылки.

2. В диалоговом окне Конвертировать гиперссылки настройте параметры следующим образом.

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

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

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

Как показано на скриншоте ниже, в папке находятся разные типы файлов, и вы хотите создать гиперссылки на все или только на определенный тип файлов на листе. Следующие методы помогут вам это сделать.

4.5.1 Автоматический вывод всех имен файлов в папке и создание гиперссылок с помощью VBA-кода

Вы можете использовать приведенный ниже VBA-код для вывода всех имен файлов в определенной папке и одновременного создания гиперссылок. Выполните следующие действия.

1. В книге создайте новый лист для вывода имен файлов с гиперссылками.

2. Одновременно нажмите Alt + F11 для открытия окна Microsoft Visual Basic for Applications.

3. В окне Microsoft Visual Basic for Applications выберите Вставка > Модуль и скопируйте приведенный ниже VBA-код в окно модуля.

VBA-код: Вывести все имена файлов в папке и создать гиперссылки

Sub ListFileNames()
'Update by Extendoffice 20210827
    Dim xFSO As Object
    Dim xFolder As Object
    Dim xFile As Object
    Dim xFiDialog As FileDialog
    Dim xPath As String
    Dim I As Integer
    Set xFiDialog = Application.FileDialog(msoFileDialogFolderPicker)
    If xFiDialog.Show = -1 Then
        xPath = xFiDialog.SelectedItems(1)
    End If
    Set xFiDialog = Nothing
    If xPath = "" Then Exit Sub
    Set xFSO = CreateObject("Scripting.FileSystemObject")
    Set xFolder = xFSO.GetFolder(xPath)
    For Each xFile In xFolder.Files
        I = I + 1
        ActiveSheet.Hyperlinks.Add Cells(I, 1), xFile.Path, , , xFile.Name
    Next
End Sub

4. Нажмите F5 для запуска кода. В появившемся окне Обзор выберите папку с файлами, на которые нужно создать гиперссылки в Excel, затем нажмите OK.

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

Примечание: если в указанной папке есть подпапки, имена файлов из подпапок не будут выведены.

4.5.2 Легко вывести список файлов в папке и создать гиперссылки с помощью удобного инструмента

Приведенный выше VBA-код позволяет вывести только все имена файлов в папке. Рекомендуем воспользоваться функцией Список имен файлов из Kutools для Excel. С помощью этой функции вы сможете легко вывести имена всех файлов или определенных типов файлов с гиперссылками не только из папки, но и из ее подпапок.

1. Выберите Kutools Plus > Импорт и экспорт > Список имен файлов.

2. В диалоговом окне Список имен файлов выполните следующие настройки.

2.1) В Настройки каталога разделе нажмите кнопку, чтобы выбрать папку, в которой нужно вывести имена файлов;

Включить файлы в подкаталогах: отметьте этот пункт, чтобы вывести имена файлов из подпапок;

Включить скрытые файлы и папки: отметьте этот пункт, чтобы вывести имена скрытых файлов.

2.2) В разделе Тип файла вы можете указать, выводить ли имена всех файлов, файлов определенного типа или нескольких типов по необходимости;

2.3) В разделе Другие настройки выберите единицу измерения размера файла для отчета;

2.4) Отметьте поле Создать гиперссылки;

2.5) Нажмите OK. См. скриншот:

Теперь все имена файлов в указанной папке (или папках) выведены с гиперссылками на новом листе, как показано на скриншоте ниже.

На листе вы можете щелкнуть по имени файла для его открытия или по пути к папке для открытия папки.

5. Изменение гиперссылок в Excel

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

5.1 Изменение текста ссылки или расположения с помощью функции Изменить гиперссылку

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

1. Выделите ячейку с гиперссылкой, щелкните правой кнопкой мыши и выберите Изменить гиперссылку в контекстном меню или нажмите Ctrl + K.

2. В диалоговом окне Изменить гиперссылку измените параметры по необходимости и нажмите OK для сохранения изменений.

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

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

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

Если вы хотите изменить внешний вид гиперссылок, по которым еще не переходили, в текущей книге, выполните следующие действия.

1. На вкладке Главная щелкните правой кнопкой мыши по стилю Гиперссылка в поле Стиль и выберите Изменить в контекстном меню.

2. В диалоговом окне Стиль нажмите кнопку Формат.

3. В диалоговом окне Формат ячеек измените параметры на вкладке Шрифт по необходимости и нажмите OK для сохранения изменений.

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

4. После возврата в диалоговое окно Стиль нажмите OK.

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

Примечание: Чтобы изменить внешний вид гиперссылок, по которым уже переходили, щелкните правой кнопкой мыши по стилю Перейденная гиперссылка в поле Стиль на вкладке Главная, выберите Изменить в контекстном меню и выполните те же действия, что и в шагах2–4.

5.3 Массовое изменение путей гиперссылок в Excel

Допустим, вы создали одинаковую гиперссылку для нескольких данных на листе и хотите массово изменить путь гиперссылки на новый. Воспользуйтесь одним из следующих способов.

5.3.1 Массовое изменение путей гиперссылок с помощью VBA-кода

Следующий VBA-код поможет вам массово изменить одинаковый путь гиперссылки на активном листе. Выполните следующие действия.

1. Нажмите Alt + F11 для открытия окна Microsoft Visual Basic for Applications.

2. В окне Microsoft Visual Basic for Applications выберите Вставка > Модуль, и скопируйте приведенный ниже VBA-код в окно модуля. См. скриншот:

VBA-код: Массовое изменение путей гиперссылок

Sub ReplaceHyperlinks()
'Update by Extendoffice 20210831
Dim Ws As Worksheet
Dim xHyperlink As Hyperlink
Dim xOld As String, xNew As String
xTitleId = "KutoolsforExcel"
Set Ws = Application.ActiveSheet
xOld = Application.InputBox("Old text:", xTitleId, "", Type:=2)
xNew = Application.InputBox("New text:", xTitleId, "", Type:=2)
Application.ScreenUpdating = False
For Each xHyperlink In Ws.Hyperlinks
    xHyperlink.Address = Replace(xHyperlink.Address, xOld, xNew)
Next
Application.ScreenUpdating = True
End Sub

3. Нажмите F5 для запуска кода.

4. В появившемся диалоговом окне KutoolsforExcel введите текст адреса гиперссылки, который нужно заменить, и нажмите OK.

5. Во втором появившемся диалоговом окне KutoolsforExcel введите новый текст адреса гиперссылки для замены и нажмите OK.

В данном случае я заменяю все “addin” на “extendoffice” в гиперссылках на текущем листе. Результат показан на скриншоте ниже.

5.3.2 Массовое изменение путей гиперссылок с помощью удобного инструмента

С помощью функции Поиск и Замена в нескольких книгах из Kutools для Excel вы можете легко массово изменить пути гиперссылок не только в выбранном диапазоне, но и на нескольких выбранных листах, во всех открытых книгах или в текущей книге.

1. Выберите Kutools > Поиск > Поиск и Замена в нескольких книгах. См. скриншот:

2. После этого панель Поиск и Замена появится слева от книги, настройте параметры следующим образом.

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

6. Советы по использованию гиперссылок в Excel

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

6.1 Выделение ячейки без открытия гиперссылки

Иногда при выделении ячейки с гиперссылкой она сразу открывается. Как выделить ячейку, не открывая гиперссылку? Воспользуйтесь небольшим трюком из этого раздела.

Щелкните по ячейке, не отпуская левую кнопку мыши, пока курсор не изменится на .

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

6.2 Массовое открытие нескольких гиперссылок с помощью VBA-кода

В этом разделе приведен VBA-код для массового открытия нескольких гиперссылок в Excel.

1. Нажмите Alt + F11.

2. В открывшемся окне Microsoft Visual Basic for Applications выберите Вставка > Модуль и скопируйте приведенный ниже VBA-код в окно модуля.

VBA-код: Массовое открытие нескольких гиперссылок

 Sub OpenHyperLinks()
'Update by Extendoffice 20210831
      Dim xHyperlink As Hyperlink
      Dim WorkRng As Range
      On Error Resume Next
      xTitleId = "KutoolsforExcel"
      Set WorkRng = Application.Selection
      Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
      For Each xHyperlink In WorkRng.Hyperlinks
         xHyperlink.Follow
      Next
End Sub

3. В диалоговом окне KutoolsforExcel выберите ячейки с гиперссылками, которые нужно открыть массово, затем нажмите OK.

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

6.3 Изменение браузера по умолчанию при открытии гиперссылки

При нажатии на гиперссылку она открывается в браузере по умолчанию Windows. Часто на компьютере установлено несколько браузеров. В этом разделе показано, как изменить браузер по умолчанию, чтобы все ссылки из Excel открывались в выбранном вами браузере.

1. В Windows10 нажмите кнопку Искать рядом с кнопкой Пуск в левом нижнем углу окна, введите control panel в поле поиска и выберите Панель управления из найденных результатов.

Совет: В Windows7 нажмите кнопку Пуск в левом нижнем углу окна, затем найдите и выберите Панель управления в меню.

2. В окне Панель управления выберите Программы.

3. В окне Программы выберите Программы по умолчанию. См. скриншот:

4. В окне Программы по умолчанию выберите Задать программы по умолчанию.

5. В окне Параметры вы увидите текущий браузер по умолчанию в разделе Веб-браузер. Нажмите на браузер по умолчанию, чтобы развернуть список, и выберите нужный браузер для открытия ссылок из Excel.

Теперь браузер по умолчанию изменен на выбранный. Закройте все окна, связанные с Панелью управления.

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

6.4 Извлечение URL-адресов из гиперссылок

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

6.4.1 Извлечение URL из одной гиперссылки вручную

Самый распространенный способ получить URL из гиперссылки — скопировать его из диалогового окна Изменить гиперссылку.

1. Щелкните правой кнопкой мыши по ячейке с гиперссылкой, из которой нужно извлечь URL, и одновременно нажмите Ctrl+K для открытия диалогового окна Изменить гиперссылку.

2. В диалоговом окне Изменить гиперссылку перейдите в поле Адрес, нажмите Ctrl + A для выделения всего URL, затем Ctrl + C для копирования, после чего нажмите OK для закрытия окна.

3. Выделите пустую ячейку и нажмите Ctrl+V для вставки скопированного URL.

Примечание: для извлечения URL из нескольких ячеек повторяйте вышеуказанные шаги.

6.4.2 Извлечение URL из одной гиперссылки с помощью пользовательской функции

В этом разделе приведена пользовательская функция для извлечения URL из одной гиперссылки. После этого вы можете использовать маркер автозаполнения для получения всех URL соседних ячеек на основе первого результата. Выполните следующие действия.

1. Нажмите Alt + F11.

2. В открывшемся окне Microsoft Visual Basic for Applications выберите Вставка > Модуль и скопируйте приведенный ниже код в окно модуля.

Function GetURL(pWorkRng As Range) As String
'Update by Extendoffice 20210901
    GetURL = pWorkRng.Hyperlinks(1).Address
End Function

3. Нажмите Alt + Q для закрытия окна Microsoft Visual Basic for Applications.

4. Выделите пустую ячейку рядом с первой ячейкой, из которой нужно извлечь URL, введите в нее формулу ниже и нажмите Enter. Затем перетащите маркер автозаполнения вниз для получения остальных URL.

=GetURL(A2)

Примечание: A2 — это ссылка на ячейку, из которой нужно извлечь URL. При необходимости измените ее.

6.4.3 Извлечение URL из гиперссылок с помощью VBA-кода

Применение приведенного ниже VBA-кода заменит все содержимое ячеек в выбранном диапазоне на URL гиперссылок.

Примечание: перед операцией сделайте резервную копию исходных данных. В данном случае я хочу извлечь все URL из гиперссылок в диапазоне A2:A10, поэтому копирую этот диапазон и вставляю в B2:B10, а затем работаю с новым диапазоном.

1. Нажмите Alt + F11.

2. В открывшемся окне Microsoft Visual Basic for Applications выберите Вставка > Модуль и скопируйте приведенный ниже VBA-код в окно модуля.

VBA-код: Извлечение URL из гиперссылки

Sub Extracthyperlinks()
'Update by Extendoffice 20210901
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    If Rng.Hyperlinks.Count > 0 Then
        Rng.Value = Rng.Hyperlinks.Item(1).Address
    End If
Next
End Sub

3. Нажмите F5 для запуска кода.

4. В появившемся диалоговом окне Kutools для Excel выберите диапазон ячеек, из которых нужно извлечь URL, затем нажмите OK.

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

6.4.4 Легко извлекать URL из нескольких гиперссылок с помощью удобного инструмента

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

1. Выберите Kutools > Ссылка > Конвертировать гиперссылки. См. скриншот:

2. В диалоговом окне Конвертировать гиперссылки настройте параметры следующим образом.

2.1) В разделе Тип преобразования выберите опцию Адреса гиперссылок заменяют содержимое ячеек;

2.2) В Входной диапазон поле нажмите кнопку, чтобы выбрать ячейки с гиперссылками, из которых нужно извлечь URL;

2.3) В Результирующий диапазон поле нажмите  кнопку, чтобы выбрать ячейку для вывода извлеченных URL;

2.4) Нажмите OK.

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

7. Удаление гиперссылок в Excel

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

7.1 Удаление гиперссылок из диапазона с помощью команды Удалить гиперссылки

Вы можете легко удалить все гиперссылки из выбранного диапазона или активного листа с помощью встроенной функции Excel — Удалить гиперссылки.

1. Выделите диапазон, из которого нужно удалить все гиперссылки, щелкните правой кнопкой мыши по любой ячейке в диапазоне и выберите Удалить гиперссылки в контекстном меню.

Или выберите Главная > Очистить > Удалить гиперссылки (этот шаг отсутствует в Excel2007 и более ранних версиях).

Совет: Чтобы удалить все гиперссылки с активного листа, нажмите Ctrl + A для выделения всего листа, щелкните правой кнопкой мыши по любой ячейке и выберите Удалить гиперссылку в контекстном меню.

7.2 Удаление всех гиперссылок с активного листа с помощью VBA-кода

Вы можете воспользоваться приведенным ниже VBA-кодом для одновременного удаления всех гиперссылок с активного листа.

1. На листе с гиперссылками, которые нужно удалить, одновременно нажмите Alt + F11.

2. В открывшемся окне Microsoft Visual Basic for Applications выберите Вставка > Модуль и скопируйте приведенный ниже VBA-код в окно кода.

VBA-код: Удалить все гиперссылки с текущего листа

Sub RemoveHyperlinks()
'Update by Extendoffice 20210901
ActiveSheet.Hyperlinks.Delete
End Sub

3. Нажмите F5 для запуска кода. После этого все гиперссылки будут удалены с активного листа.

7.3 Удаление гиперссылок с сохранением форматирования с помощью функции Очистить гиперссылки

Вышеперечисленные методы удаляют как гиперссылки, так и форматирование ячеек. Иногда требуется удалить только гиперссылки, сохранив форматирование. В этом случае поможет встроенная функция Excel — Очистить гиперссылки.

1. Выделите диапазон, в котором нужно очистить гиперссылки, но сохранить форматирование, выберите Главная > Очистить > Очистить гиперссылки.

Чтобы очистить гиперссылки с текущего листа, нажмите Ctrl + A для выделения всего листа и примените функцию.

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

Как показано на скриншоте ниже, визуально ничего не изменилось, но гиперссылки уже удалены.

Примечания:

7.4 Несколько кликов для удаления гиперссылок из диапазонов, листов или книги с сохранением форматирования

Kutools для Excel предлагает удобную функцию — Очистить гиперссылки и сохранить форматирование, чтобы легко удалить гиперссылки из:

1. Выбранного диапазона;
2. Текущего листа;
3. Нескольких выбранных листов;
4. Всей книги.

1. Выберите Kutools > Ссылка > Очистить гиперссылки и сохранить форматирование и выберите нужный вариант.

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

2. В появившемся диалоговом окне Kutools для Excel нажмите Да для продолжения.

Теперь удаляются только гиперссылки, а форматирование сохраняется.

8. Отключение автоматических гиперссылок в Excel

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

8.1 Отключение автоматической гиперссылки в одной ячейке в Excel

Следующие сочетания клавиш помогут предотвратить автоматическую гиперссылку в одной ячейке в Excel. Выполните следующие действия.

1. Введите текст в формате URL или адреса электронной почты в ячейку и нажмите Enter.

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

2. Одновременно нажмите Ctrl + Z — кликабельный текст гиперссылки станет обычным текстом.

8.2 Отключение автоматических гиперссылок во всем приложении Excel

Вы можете отключить автоматические гиперссылки во всем приложении Excel.

1. Выберите Файл > Параметры.

2. В окне Параметры Excel выберите Правописание в левой панели, затем нажмите кнопку Параметры автозамены в правой части окна.

3. В диалоговом окне Автозамена перейдите на вкладку Автоформат при вводе, снимите флажок Интернет- и сетевые пути с гиперссылками в разделе Заменять при вводе и нажмите OK.

4. Нажмите OK для сохранения изменений при возврате в окно Параметры Excel.

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

Примечание: это изменение повлияет на все книги на вашем компьютере.