Excel

Как сделать гиперссылку на другой лист в excel: Работа со ссылками в Excel

Содержание

Работа со ссылками в Excel

С помощью функции ГИПЕРССЫЛКА можно создать ссылку, которая открывает документ, хранимый на сетевом сервере, интрасетьили в Интернете. При щелчке на ячейку, содержащую функцию ГИПЕРССЫЛКА, приложение Excel откроет файл, расположенный по ссылке.

Синтаксис

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

адрес     — это путь к открываемому документу и его имя, представленные как текст. Адрес может ссылаться на определенное место в документе, например на ячейку или именованный диапазон листа или книги Excel либо на закладку в документе Microsoft Word. Путь может представлять собой путь к файлу на жестком диске, адрес на сервере в формате UNC (в Microsoft Excel для Windows) либо URL-путь в Интернете или интрасети.

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

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

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

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

  • Если аргумент "имя" возвращает значение ошибки (например, #ЗНАЧ!), вместо текста ссылки в ячейке отображается значение ошибки.

Примеры

В следующем примере открывается лист "Бюджетный отчет.

xlsx", хранимый в Интернете по адресу example.microsoft.com/report. Видимый текст ссылки: "Щелкните, чтобы создать отчет":

=HYPERLINK("http://example.microsoft.com/report/budget report.xls", "Click for report")

В следующем примере создается ссылка на ячейку F10 на листе "Годовой" в книге Бюджетные отчеты.xls, которая хранится в Интернете — в расположении с именем example.microsoft.com/report. В ячейке листа, содержащей ссылку, в качестве текста перехода служит содержимое ячейки D1:

=HYPERLINK("[http://example.microsoft.com/report/budget report.xls]Annual!F10", D1)

В следующем примере создается ссылка на диапазон с именем "Итоги по отделу" на листе "Первый квартал" в книге Бюджетные отчеты.xls, которая хранится в Интернете — в расположении с именем example.microsoft.com/report. В ячейке листа, содержащей ссылку, виден текст: "Щелкните, чтобы вывести итоги по отделу за первый квартал":

=HYPERLINK("[http://example. microsoft.com/report/budget report.xls]First Quarter!DeptTotal", "Click to see First Quarter Department Total")

Чтобы создать ссылку на определенное место документа Microsoft Word, необходимо сначала с помощью закладки определить место в документе, куда следует переходить. В следующем примере создается ссылка на закладку "Прибыль за квартал" в документе с именем ГодовойОтчет.doc по адресу example.microsoft.com:

=HYPERLINK("[http://example.microsoft.com/Annual Report.doc]QrtlyProfits", "Quarterly Profit Report")

В приведенном ниже примере Excel для Windows показано, как использовать содержимое ячейки D5 в качестве текста ссылки и открыть файл "1кв.xlsx", расположенный на сервере "ФИНАНСЫ" в общей папке "Балансы". В данном примере используется путь в формате UNC.

=HYPERLINK("\\FINANCE\Statements\1stqtr.xls", D5)

В следующем примере в Excel для Windows открывается файл 1кв. xlsx, который находится в каталоге "Финансы" на жестком диске D. Текстом ссылки служит число, которое содержится в ячейке h20:

=HYPERLINK("D:\FINANCE\1stqtr.xls", h20)

В следующем примере Excel для Windows создается ссылка на область "Итоги" в другой (внешней) книге "МояКнига.xlsx":

=HYPERLINK("[C:\My Documents\Mybook.xls]Totals")

В следующем примере в Microsoft Excel для компьютеров Macintosh в ячейке видна надпись "Щелкните здесь", и при щелчке открывается файл "Первый квартал" из папки "Бюджетные отчеты" на жестком диске Macintosh HD:

=HYPERLINK("Macintosh HD:Budget Reports:First Quarter", "Click here")

Ссылки можно создавать и в пределах одного листа, чтобы переходить от одной ячейки к другой. Например, если в книге "Бюджет" активным является лист "Июнь", приведенная ниже формула создаст ссылку на ячейку E56.

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

=HYPERLINK("[Budget]June!E56", E56)

Для перехода на другой лист той же книги измените имя листа в ссылке. Чтобы создать ссылку на ячейку E56 листа "Сентябрь", замените в предыдущем примере слово "Июнь" словом "Сентябрь".

ГИПЕРССЫЛКА (функция ГИПЕРССЫЛКА) - Служба поддержки Office

=ГИПЕРССЫЛКА("http://example.microsoft.com/report/budget report.xlsx", "Щелкните, чтобы просмотреть отчет")

Открывает книгу, сохраненную по адресу http://example.microsoft.com/report. В ячейке отображается текст "Щелкните, чтобы просмотреть отчет" в качестве текста ссылки.

=ГИПЕРССЫЛКА("[http://example.microsoft.com/report/budget report.xlsx]Годовой!F10"; D1)

Создает гиперссылку на ячейку F10 листа Годовой книги, сохраненной по адресу http://example. microsoft.com/report. В ячейке листа, содержащей гиперссылку, в качестве текста ссылки отображается содержимое ячейки D1.

=ГИПЕРССЫЛКА("[http://example.microsoft.com/report/budget report.xlsx]'Первый квартал'!ИтогиОтдел", "Щелкните, чтобы просмотреть итоги по отделу за первый квартал")

Создает гиперссылку на диапазон ИтогиОтдел на листе Первый квартал книги, сохраненной по адресу http://example.microsoft.com/report. В ячейке листа, содержащей гиперссылку, в качестве текста ссылки отобразится "Щелкните, чтобы вывести итоги по отделу за первый квартал".

=ГИПЕРССЫЛКА("http://example.microsoft.com/Annual Report.docx]КвартПриб", "Квартальный отчет о прибыли")

Чтобы создать гиперссылку на определенное место в файле Word, необходимо сначала с помощью закладки определить место в файле, куда должен выполняться переход. В приведенном ниже примере создается гиперссылка на закладку КвартПриб в файле Annual Report.doc, сохраненном по адресу http://example.microsoft.com.

=ГИПЕРССЫЛКА("\\FINANCE\Statements\1stqtr.xlsx", D5)

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

=ГИПЕРССЫЛКА("D:\FINANCE\1stqtr.xlsx"; h20)

Открывает книгу 1stqtr.xlsx, которая находится в каталоге Finance на жестком диске D. После этого выводится число, которое содержится в ячейке h20.

=ГИПЕРССЫЛКА("[C:\My Documents\Mybook. xlsx]Итоги")

Создает гиперссылку на область Totals в другой (внешней) книге Mybook.xlsx.

=ГИПЕРССЫЛКА("[Книга1.xlsx]Лист1!A10";"Перейти на Лист1 > A10")

Чтобы перейти в другое место на текущем листе, укажите имя книги и имя листа, как в этом примере, где "Лист1" — текущий лист.

=ГИПЕРССЫЛКА("[Книга1.xlsx]Январь!A10";"Перейти на лист Январь > A10")

Чтобы перейти в место на другом листе, укажите имя книги и имя листа, как в этом примере, где "Январь" — другой лист в книге.

=ГИПЕРССЫЛКА(ЯЧЕЙКА("адрес",Январь!A1";"Перейти на лист Январь > A1")

Чтобы перейти в другое место на текущем листе без использования полной ссылки на лист ([Книга1. xlsx]), вы можете воспользоваться этой формулой, где ЯЧЕЙКА("адрес") возвращает текущее имя книги.

=ГИПЕРССЫЛКА($Z$1)

Чтобы быстро обновить все формулы на листе, использующие функцию ГИПЕРССЫЛКА с теми же аргументами, можно поместить целевой объект ссылки на тот же или другой лист, а затем использовать абсолютную ссылку на эту ячейку в качестве аргумента "адрес" в формулах с функцией ГИПЕРССЫЛКА. Изменения целевого объекта ссылки при этом будут немедленно отражаться в формулах.

Как сделать ссылку на вкладку в excel?

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

Сейчас мы пошагово рассмотрим как это можно сделать. Допустим у нас есть ячейка с текстом “Смотрите на листе 2” и мы хотим, чтобы данный текст стал ссылкой, нажав на которую мы должны переместиться на Лист 2.

Для решения данной задачи необходимо выделить ячейку с этим текстом (B4) и далее либо нажав правой кнопкой мыши выбрать пункт меню “Гиперссылка”

 или выбрать данный пункт, перейдя в раздел “Вставка”.

После этого откроется окно вставки гиперссылки. Необходимо перейти в раздел “Место в новом документе” и выбрать необходимый лист, на который требуется сделать ссылку. В нашем пример это Лист 2.

Нажимаем “Ок” и текст в нашем файле Excel превратится в ссылку.

Спасибо за внимание. Будем рады помочь.

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

Абсолютная и относительная ссылка на ячейку в Excel

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

По умолчанию вставляются относительные ссылки на ячейки.

Относительная ссылка позволяет изменять адрес ячеек по строкам и столбцам при копировании формулы в другое место документа. То есть, если скопировать формулу из ячейки А3 в ячейку С3, то для расчета суммы возьмутся новые адреса ячеек: С1 и С2.

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

Рассмотрим следующий пример. Есть таблица, в которую внесены: наименование товара, его цена и количество проданных единиц. Посчитаем итоговую сумму для каждой единицы. В ячейку D6 пишем формулу: =В6*С6. Как видите, ссылки на ячейки в формуле относительные.

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

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

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

Чтобы сделать абсолютную ссылку на ячейку в Excel, нужно добавить знак «$» в адрес ячейки перед названием столбца и строки. Или же поставить курсор в строке формул после адреса нужной ячейки и нажать «F4». В примере, для расчета суммы в ячейке А3,   используется теперь абсолютная ссылка на ячейку А1.

Давайте посчитаем сумму для ячеек D1 и D2. В ячейку D3 скопируем формулу из А3. Как видите, результат вместо 24 – 25. Все из-за того, что в формуле была использована абсолютная ссылка на ячейку $A$1. Поэтому в расчете использовались не ячейки D1 и D2, а ячейки $A$1 и D2.

Рассмотрим для примера такую таблицу: есть наименование товара и его себестоимость. Чтобы определить цену товара для продажи, нужно посчитать НДС. НДС – 20%, и значение написано в ячейке В9. Вписываем формулу для расчета в ячейку С6.

Если мы скопируем формулу в остальные ячейки, то не получим результат. Так как в расчете будут использоваться ячейки В10 и В11, которые не заполнены значениями.

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

Если в строке формул поставить курсор после адреса ячейки и нажать «F4» второй и третий раз, то получится смешанная ссылка в Excel. В этом случае, при копировании может не изменяться или строка – А$1, или столбец – $А1.

Ссылка на другой лист в Excel

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

Ссылка на ячейку с другого листа в формуле будет выглядеть следующим образом: Лист1!А1 – название листа, знак восклицания, адрес ячейки. Если в названии листа используются пробелы, то его нужно взять в одинарные кавычки: ‘Итоговые суммы’ – ‘Итоговые суммы’!А1.

Например, рассчитаем значение НДС для товаров. Таблица, в которой будет рассчитываться формула, находится на Листе1, значение НДС находится на листе с названием Все константы.

На листе Все константы для расчета формулы нам необходимо будет значение, записанное в ячейке В1.

Возвращаемся на Лист1. В ячейку С6 пишем формулу для расчета НДС: ставим «=», затем выделяем ячейку В6 и делаем ссылку на ячейку В1 с другого листа.

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

Если изменить название листа Все константы на Все константы1111, то оно автоматически поменяется и в формуле. Точно также, если на листе Все константы изменить значение в ячейке В1 с 20% на 22%, то формула будет пересчитана.

Для того чтобы сделать ссылку на другую книгу Excel в формуле, возьмите ее название в квадратные скобки. Например, сделаем ссылку в ячейке А1 в книге с названием Книга1 на ячейку А3 из книги с названием Ссылки. Для этого ставим в ячейку А1 «=», в квадратных скобках пишем название книги с расширением, затем название листа из этой книги, ставим «!» и адрес ячейки.

Книга, на которую мы ссылаемся, должна быть открыта.

Ссылка на файл или гиперссылка в Excel

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

Выделите ячейку, в которую необходимо вставить гиперссылку. Это может быть как число, так и текст или рассчитанная формула, или пустая ячейка. Перейдите на вкладку «Вставка» и кликните по кнопочке «Гиперссылка».

Сделаем ссылку на другую книгу Эксель. В поле «Связать с» выбираем «файлом, веб-страницей». Найдите нужную папку на компьютере и выделите файл. В поле «Текст» можно изменить надпись, которая будет отображаться в ячейке – это только в том случае, если ячейка изначально была пустая. Нажмите «ОК».

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

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

Поделитесь статьёй с друзьями:

Гиперссылки широко используются в Интернете для навигации по сайтам и документам. Работая с файлами Excel вы также можете создавать гиперссылки, как на интернет-ресурсы, так и на ячейки, файлы или форму отправку Email.

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

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

  • Перехода в определенное место в текущей книге;
  • Открытия другого документа или перехода к определенному месту в этом документе, например лист в файле Excel или закладке в документе Word;
  • Перехода на веб-страницу в Интернете;
  • Создания нового файла Excel;
  • Отправки сообщения электронной почты по указанному адресу.

Гиперссылку в Excel легко заметить, она выглядит как подчеркнутый текст, выделенный синим цветом:

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

Абсолютные гиперссылки содержат в себе полный интернет-адрес или полный путь на компьютере. Например:

“https://excelhack.ru/funkciya-rept-povtor-v-excel/”

C:DesktopПрезентации

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

funkciya-rept-povtor-v-excel/

DesktopПрезентации

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

Чтобы создать гиперссылку проделайте следующие шаги:

  • Выделите ячейку, в которой вы хотите создать гиперссылку;
  • Нажмите правую клавишу мыши;
  • В выпадающем меню выберите пункт “Ссылка”:
  • В диалоговом окне выберите файл или введите веб-адрес ссылки в поле “Адрес”:
  • Нажмите “ОК”

Ниже, мы подробней разберем как создать гиперссылку:

  • На другой документ;
  • На веб-страницу;
  • На конкретную область в текущем документе;
  • На новую рабочую книгу Excel;
  • На окно отправки Email.

Чтобы указать гиперссылку на другой документ, например Excel, Word или Powerpoint файлы:

  • Откройте диалоговое окно для создания гиперссылки;
  • В разделе “Связать с” выберите “Файлом, веб-страницей”;
  • В поле “Искать в” выберите папку, в которой лежит файл, на который вы хотите создать ссылку;
  • В поле “Текст” введите текст, который будет отображаться в качестве ссылки;
  • Нажмите “ОК”.

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

Чтобы указать гиперссылку веб-страницу:

  • Откройте диалоговое окно для создания гиперссылки;
  • В разделе “Связать с” выберите пункт “Файлом, веб-страницей”;
  • Нажмите на кнопку “Интернет”;
  • Введите адрес веб-страницы в поле “Адрес”;
  • В поле “Текст” укажите текст, отображаемый в виде ссылки.

Для создания гиперссылки на конкретный лист текущего файла Excel или ячейки:

  • Откройте диалоговое окно для создания гиперссылки;
  • В левой колонке диалогового окна под надписью “Связать с” выберите “Файлом, веб-страницей”;
  • В диалоговом окне нажмите кнопку “Закладка…” и выберите лист создания ссылки. В поле “Введите адрес ячейки” укажите ячейку.

Для вставки гиперссылки, после нажатия на которую будет создан новый Excel-файл:

  • Откройте диалоговое окно для создания гиперссылки;
  • В левой колонке диалогового окна под надписью “Связать с” выберите “Новый документ”;
  • В поле “Текст” укажите текст ссылки;
  • В поле “Имя нового документа” укажите название нового Excel-файла;
  • В поле “Путь” укажите место хранения, где будет сохранен новый файл;
  • В поле “Когда вносить правку в новый документ” укажите настройку, когда следует приступить к редактированию нового файла после нажатия ссылки.
  • Нажмите кнопку “ОК”

Для вставки гиперссылки, после нажатия на которую будет создан e-mail:

  • Откройте диалоговое окно для создания гиперссылки;
  • В левой колонке диалогового окна под надписью “Связать с” выберите “Электронная почта”;
  • В поле “Текст” укажите текст ссылки;
  • В поле “Адрес эл. почты” укажите E-mail адрес, куда будет отправлено письмо;
  • В поле “Тема” укажите тему создаваемого письма;
  • Нажмите кнопку “ОК”

Как редактировать гиперсcылку в Excel

Для редактирования уже созданной гиперссылки, кликните по ячейке с ссылкой правой клавишей мыши и в выпадающем меню выберите “Edit Hyperlink”.

В диалоговом окне внесите корректировки в ссылку.

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

  • Перейдите на вкладку панели инструментов “Главная”, затем в раздел “Стили ячеек”:
  • Кликните на “Гиперссылка” правой кнопкой мыши и выберите пункт “Изменить” для редактирования формата ссылки:
  • Кликните на “Открывавшаяся гиперссылка” правой кнопкой мы и выберите пункт “Изменить” для редактирования формата ссылки;
  • В диалоговом окне “Стили” нажмите кнопку “Формат”:
  •  диалоговом окне “Format Cells” перейдите на вкладки “Шрифт” и/или “Заливка” для настройки формата ссылок:
  • Нажмите кнопку “ОК”.

Удаление гиперссылки осуществляется в два клика:

  • Нажмите правой клавишей мыши на ячейки с ссылкой;
  • В выпадающем меню выберите пункт “Удалить гиперссылку”.

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



Первый лист моей книги похож на страницу содержания. Каждая ячейка в столбце А содержит строку адреса IP. Для каждой строки адреса IP существует рабочий лист с именем адреса IP.

Я хочу превратить строку в ячейках столбца а в гиперссылки на соответствующие им листы (ячейка А1 целевого листа).

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

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

excel vba hyperlink
Поделиться Источник Swiftslide     21 марта 2012 в 21:57

7 ответов


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

    У меня есть следующая строка VBA, которая должна создать гиперссылку на другой лист в книге Excel: Dim rMyCell As Range Sheets(All_Tables).Hyperlinks.Add Anchor:=Selection, Address:=Employees! & rMyCell.Address, _ TextToDisplay:=Link Гиперссылки, созданные из этого кода, имеют знаки долларов,...

  • Кнопка создать на ленте Powerpoint 2013, открывающая гиперссылку

    Я хотел бы создать кнопку, которая автоматически открывает гиперссылку из Powerpoint. Достаточно просто создать гиперссылку в Powerpoint (вставить - > гиперссылка), а затем нажать на эту гиперссылку. Я хочу пропустить весь этот процесс и просто иметь возможность иметь кнопку, которая открывает...



16

Я записал макрос, создающий гиперссылку. Это и привело.

ActiveCell.FormulaR1C1 = "=HYPERLINK(""[Workbook.xlsx]Sheet1!A1"",""CLICK HERE"")"

Поделиться curial     21 марта 2012 в 22:10



15

Это код, который я использую для создания индексного листа.

Sub CreateIndexSheet()
    Dim wSheet As Worksheet
    ActiveWorkbook.Sheets.Add(Before:=Worksheets(1)).Name = "Contents" 'Call whatever you like
    Range("A1").Select
    Application.ScreenUpdating = False 'Prevents seeing all the flashing as it updates the sheet
    For Each wSheet In Worksheets
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=wSheet.Name & "!A1", TextToDisplay:=wSheet.Name
        ActiveCell.Offset(1, 0).Select 'Moves down a row
    Next
    Range("A1").EntireColumn.AutoFit
    Range("A1").EntireRow.Delete 'Remove content sheet from content list
    Application.ScreenUpdating = True
End Sub

Надеюсь, это кому-то поможет

Поделиться Lee     06 июля 2015 в 13:26



7

Что-то вроде следующего будет циклически проходить через столбец а в контрольной таблице и превращать значения в ячейках в гиперссылки. Это не то что мне приходилось делать раньше так что пожалуйста извините Багса:

Sub CreateHyperlinks()

Dim mySheet As String
Dim myRange As Excel.Range
Dim cell As Excel.Range
Set myRange = Excel.ThisWorkbook.Sheets("Control").Range("A1:A5") '<<adjust range to suit

For Each cell In myRange
    Excel.ThisWorkbook.Sheets("Control").Hyperlinks.Add Anchor:=cell, Address:="", SubAddress:=cell.Value & "!A1" '<<from recorded macro
Next cell

End Sub

Поделиться whytheq     21 марта 2012 в 22:16


  • Как создать гиперссылку на другой лист Excel в той же книге

    Я использую модуль openpyxl для Python и пытаюсь создать гиперссылку, которая приведет меня на другую вкладку в той же книге Excel. Выполнение чего-то похожего на следующее создает гиперссылку; однако, когда я нажимаю на нее, она говорит мне, что не может открыть файл. from openpyxl import...

  • Могу ли я вырезать и вставить строку на другой лист, используя vba from hyperlink?

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



2

Знак "!" является ключевым элементом. Если у вас есть объект cell (например, "mycell" в следующем примере кода) и вы связываете ячейку с этим объектом, вы должны обратить на это внимание ! элемент.

Вы должны сделать что-то вроде этого:

.Cells(i, 2).Hyperlinks.Add Anchor:=.Range(Cells(i, 2).Address), Address:="", _
     SubAddress:= "'" & ws.Name & "'" & _
     "!" & mycell.Address

Поделиться user3423549     15 марта 2014 в 15:58



1

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

Sub hyper()
 Dim cl As Range
 Dim nS As String

 Set MyRange = Sheets("Sheet1").Range("B16")
 Set MyRange = Range(MyRange, MyRange.End(xlDown))

 For Each cl In MyRange
  nS = cl.Value
  cl.Hyperlinks.Add Anchor:=cl, Address:="", SubAddress:="'" & nS & "'" & "!B16", TextToDisplay:=nS
 Next
End Sub

Поделиться Constanza Garcia     18 июля 2017 в 04:00



1

Если вам нужно гиперссылать Лист1 на все или соответствующие листы, то используйте простой код vba. Если вы хотите создать переключатель, то назначьте этот макрос этой кнопке ex "Home Page".

Вот оно:

Sub HomePage()
'
' HomePage Macro
'


' This is common code to go to sheet 1 if do not change name for Sheet1
    'Sheets("Sheet1").Select
' OR 

' You can write you sheet name here in case if its name changes

    Sheets("Monthly Reports Home").Select
    Range("A1").Select

End Sub

Поделиться Ravi Biruduganti     17 августа 2016 в 12:13



0

В моей реализации ячейка, на которую я ссылался, могла иметь несколько вариантов. Я использовал следующий формат, где 'ws'-это текущий редактируемый лист

For each ws in Activeworkbook.Worksheets
    For i…
       For j...
...
ws.Cells(i, j).Value = "=HYPERLINK(""#'" & SHEET-REF-VAR & "'!" & CELL-REF-VAR & """,""" & SHEET-REF-VAR & """)"

Поделиться John Stowell     02 января 2020 в 22:38


Похожие вопросы:


гиперссылка на лист в excel

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


Как добавить гиперссылку на простую фигуру Excel с помощью apache poi?

Я использую Microsoft Excel 2007. У меня есть простая форма на первом листе excel. Я хотел бы добавить гиперссылку на эту простую фигуру, которая ссылается на другой лист в конкретной строке и...


Гиперссылка на конкретный лист

Я хотел бы открыть конкретный лист Google Sheets по гиперссылке в другой электронной таблице. У меня есть разные ссылки в моей главной электронной таблице, и каждая из них должна иметь гиперссылку...


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

У меня есть следующая строка VBA, которая должна создать гиперссылку на другой лист в книге Excel: Dim rMyCell As Range Sheets(All_Tables).Hyperlinks.Add Anchor:=Selection, Address:=Employees! &...


Кнопка создать на ленте Powerpoint 2013, открывающая гиперссылку

Я хотел бы создать кнопку, которая автоматически открывает гиперссылку из Powerpoint. Достаточно просто создать гиперссылку в Powerpoint (вставить - > гиперссылка), а затем нажать на эту...


Как создать гиперссылку на другой лист Excel в той же книге

Я использую модуль openpyxl для Python и пытаюсь создать гиперссылку, которая приведет меня на другую вкладку в той же книге Excel. Выполнение чего-то похожего на следующее создает гиперссылку;...


Могу ли я вырезать и вставить строку на другой лист, используя vba from hyperlink?

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


VBA добавить якорь (гиперссылку) на активный лист

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


добавьте гиперссылку на лист excel с pandas, которая ссылается на другой лист

Я преобразовал pandas DataFrame в Excel лист, используя df.to_excel . Теперь я хочу добавить гиперссылки на значения в одном столбце. Другими словами, когда клиент увидит мой лист excel, он сможет...


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

Я пытаюсь создать гиперссылку на листе под названием Summary-Champion Specific, которая ссылается на лист под названием Hidden Sheet. Я пробовал использовать следующий код, где P1 и P и итерационные...

Как сделать гиперссылку в Excel - Как сделать гиперссылку в ВК, Ворде, презентации PowerPoint, на HTML и в Excel?

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

Чтобы сделать гиперссылку в Excel , выделите или пустую ячейку, в которой потом напишите текст для перехода, или ячейку с данными, перейдите на вкладку «Вставка» и нажмите кнопочку «Гиперссылка».

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

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

Сделаем гиперссылку на другую ячейку в Excel. Например, текст в ячейке С5 будет вести на ячейку F1. Выделяем С5 и нажимаем кнопочку «Гиперссылка». В следующем окне выбираем «местом в документе». В поле «Текст» будет написан текст, который был в выделенной ячейке, если она была пустая — введите в это поле текст для ссылки. В поле «Адрес ячейки» вводим F1. Нажмите «ОК».

Теперь название «НДС», в шапке таблицы, будет вести непосредственно на ту ячейку, где указано значение НДС.

Чтобы сделать гиперссылку на другой лист в Эксель, выделите нужную ячейку, и нажмите «Гиперссылка». В окне «Вставка гиперссылки» связываем ее с «местом в документе». Введите текст для ссылки, затем укажите адрес ячейки другого листа и в области ниже выделите нужный лист документа. Нажмите «ОК».

В примере выделена ячейка А12, в ней будет текст «Другой лист», который будет вести на ячейку А1 листа «Все константы1111».

Чтобы ссылка вела на другую книгу или запускала нужную программу, в поле «Связать с» выберите «файлом, веб-страницей».

Вставить гиперссылку в Excel также можно используя формулу ГИПЕРССЫЛКА. Перейдите на вкладку «Формулы» и нажмите «Вставить функцию». В следующем окне в поле поиска введите Гиперссылка, затем «Найти». Выделяем одноименную функцию и нажимаем «ОК».

В диалоговом окне «Аргументы функции» нужно ввести в поле «Адрес» — куда будет вести ссылка, в поле «Имя» — текст, который будет в ячейке.

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

Гиперссылка на ячейку текущего листа. Выделяем ячейку А13 и вставляем функцию. Текст в ячейке будет «Значение» и вести она будет на F1.

В дальнейшем, можно не вызывать мастера для создания функции. Просто напишите в строке формул функцию, указав в скобках необходимые аргументы: =ГИПЕРССЫЛКА(F1;Значение).

Чтобы ссылка вела на ячейку другого листа в этой же книге, введите: =ГИПЕРССЫЛКА("[Ссылки.xlsx]’Все константы1111’!A1";"Второй лист«).

«[Ссылки.xlsx]» — имя текущей книги, «’Все константы1111’» — имя листа, «А1» — ячейка на листе, «Второй лист» — текст, который будет использоваться в качестве ссылки.

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

Сделаем ссылку на другую книгу, с названием «Список.xlsx», на второй лист, ячейку В3:

=ГИПЕРССЫЛКА("[C:\Users\Master\Desktop\Эксель\Список.xlsx] Лист2!$B$3";"другая книга, второй лист«).

«[C:\Users\Master\Desktop\Эксель\Список.xlsx]» — указываем полный путь, по которому находится книга Список на компьютере. Найдите через Проводник книгу Список, кликните по ней правой кнопкой мыши, зайдите в «Свойства» и посмотрите полный путь. «Лист2» — название листа, «В3» — адрес ячейки.

Чтобы сделать гиперссылку в Эксель на сайт в Интернете, укажите в двойных кавычках его адрес.

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

Гиперссылки на листе Эксель будет подчеркнуты, по которым Вы переходили, выделены сиреневым цветом, не переходили — синим. Чтобы выделить ячейку с гиперссылкой, кликните по ней и удерживайте левую кнопку мыши, пока указывающий палец, не изменит вид на белый плюсик.

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

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

Поделитесь статьёй с друзьями:

Как создать гиперссылку на другой лист Excel в той же книге Ru Python

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

Я нашел способ сделать это.

Предположим, что один файл .xlsx с именем « workbookEx.xlsx » с двумя листами с именем « sheet1 » и « sheet2 » и нуждающийся в ссылке из одной ячейки ( A1 ) « sheet1 » в другую ячейку ( E5 ) « sheet2 »:

 from openpyxl import load_workbook wb = load_workbook(workbookEx.xlsx) ws = wb.get_sheet_by_name("sheet1") link = "workbookEx.xlsx#sheet2!E5" ws.cell(row=1, column=1).hyperlink = (link) 

Секрет был «#», Excel не показывает вас, но он использует «#» для одинаковых ссылок на файлы, мне просто нужно было скопировать одну и ту же ссылку на файл, созданную в Excel, в документ Word, чтобы увидеть «#».

Также можно опустить имя файла, то есть привязать его к листу активного документа, просто используйте: _cell.hyperlink = '#sheetName!A1' .

Чтобы назвать только что созданную ссылку, просто установите значение ячейки в нужную строку: _cell.value = 'Linkname' .

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

 ws.cell('A1').value = '=HYPERLINK("#sheet2!E5","Link name")' 

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

'=HYPERLINK("{}", "{}")'.format(link, "Link Name")

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

например, ws.cell(row=1, column=1).value = '=HYPERLINK("{}", "{}")'.format(link, "Link Name")

Гиперссылка в excel на другой лист

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

Сейчас мы пошагово рассмотрим как это можно сделать. Допустим у нас есть ячейка с текстом “Смотрите на листе 2” и мы хотим, чтобы данный текст стал ссылкой, нажав на которую мы должны переместиться на Лист 2.

Для решения данной задачи необходимо выделить ячейку с этим текстом (B4) и далее либо нажав правой кнопкой мыши выбрать пункт меню “Гиперссылка”

или выбрать данный пункт, перейдя в раздел “Вставка”.

После этого откроется окно вставки гиперссылки. Необходимо перейти в раздел “Место в новом документе” и выбрать необходимый лист, на который требуется сделать ссылку. В нашем пример это Лист 2.

Нажимаем “Ок” и текст в нашем файле Excel превратится в ссылку.

Функция ГИПЕРССЫЛКА в Excel возвращает ярлык либо гиперссылку на определенный объект, которым может являться веб-страница, файл, сохраненный в постоянной памяти ПК, группа ячеек на листе в книге Excel.

Функция ГИПЕРССЫЛКА и особенности ее аргументов

Функция ГИПЕРССЫЛКА упрощает доступ к объектам, которые как являются частями Excel (ячейки, листы книги), так и частями другим программных продуктов (файл notepad, Word) или страницами в сети интернет. Данная функция имеет следующую синтаксическую запись:

Описание 2-х параметров аргументов функции:

  1. Адрес – текстовое значение, соответствующее имени открываемого объекта и полному пути для доступа к нему. Этот параметр является обязательным для заполнения. Адрес может указывать на конкретную часть документа, к примеру, ячейку или диапазон ячеек, закладку в текстовом редакторе Word. Путь может содержать данные о пути к файлу в файловой системе ПК (например, «C:UserssoulpDocuments») или адресе URL на страницу в сети интернет.
  2. [имя] – текстовое значение, которое будет отображено в качестве текста гиперссылки. Отображается синим цветом с нижним подчеркиванием текста.
  1. Параметры адрес и [имя] принимают значения в виде текстовой строки в кавычках (“адрес_гиперссылки”, “имя_гиперссылки”) либо ссылки на ячейки, в которых содержатся адрес и название объекта соответственно.
  2. В приложении Excel Online (онлайн версия программы Excel для работы через веб-интерфейс) функцию ГИПЕРССЫЛКА можно использовать только для создания гиперссылок на веб-объекты, поскольку приложения-браузеры не имеют доступа к файловым системам устройств.
  3. Если в ячейке, на которую ссылается параметр [имя], установилось значение кода ошибки #ЗНАЧ!, в качестве текста созданной гиперсылки будет также отображено «#ЗНАЧ!».
  4. Для выделения ячейки, содержащей гиперссылку, без перехода по ней необходимо навести курсор мыши на требуемую ячейку, нажать и не отпускать левую кнопку мыши до момента, пока курсор не изменит свою форму на «+». Затем можно отпустить кнопку мыши, требуемая ячейка будет выделена.
  5. В онлайн версии программы Excel для выделения ячейки, содержащей гиперссылку, необходимо навести курсор так, чтобы он выглядел в виде обычной стрелки. Для перехода по гиперссылке необходимо навести курсор непосредственно на ее текст. В этом случае курсор примет вид руки.
  6. Для создания гиперссылки на интернет-ресурс можно использовать следующую запись: =ГИПЕРССЫЛКА(“http://www.yandex.ru/”;”Поисковая система Яндекс”).
  7. Гиперссылка на документ, хранящийся в файловой системе ПК, может быть создана следующим образом: =ГИПЕРССЫЛКА(“C:UserssoulpDownloadsдокумент_2”;”Ссылка на документ_2”). При переходе по данной гиперссылке появится диалоговое окно с предупреждением о возможном наличии вредоносного ПО в файле. Для открытия файла необходимо нажать кнопку «ОК» в этом диалоговом окне. Если по указанному пути файл отсутствует, появится соответствующее уведомление.
  8. Для создания гиперссылки на другой лист в книге Excel можно использовать подобную запись: =ГИПЕРССЫЛКА(“[Книга1.xlsx]Лист2!A1”;”Лист2”). При переходе по данной гиперссылке будет открыт Лист2, а фокус будет установлен на ячейку A1.
  9. Гиперссылку можно вставить используя визуальный пользовательский интерфейс (соответствующий пункт контекстного меню, кнопка на панели задач).



Примеры использования функции ГИПЕРССЫЛКА в Excel

Как создать гиперссылку на файл в Excel? Пример 1. Бухгалтер предприятия выполняет различные расчеты и хранит таблицы данных в Excel в одной книге (Бухгалтерия.xlsx), содержащей множество листов. Для удобства было решено создать отдельный лист с оглавлением в виде гиперссылок на каждый из имеющихся листов.

На новом листе создадим следующую таблицу:

Для создания гиперссылки используем формулу:

Описание аргументов функции:

  • "[Пример_1.xlsx]Прибыль!A1" – полный адрес ячейки A1 листа «Прибыль» книги «Пример_1.xlsx».
  • "Прибыль" – текст, который будет отображать гиперссылка.

Аналогично создадим гиперссылки для остальных страниц. В результате получим:

Динамическая гиперссылка в Excel

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

Для создания ссылки на ресурс http://valuta.pw/ в ячейке D7 введем следующую формулу:

  • http://valuta.pw/ – URL адрес требуемого сайта;
  • "Курсы валют" – текст, отображаемый в гиперссылке.

В результате получим:

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

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

Пример 3. Работник предприятия испытывает трудности с использованием функции ЕСЛИ в Excel. Для решения проблемы в одном из документов он имеет готовую форму для отправки письма по email. Отправка письма происходит по нажатию на гиперссылку. Рассмотрим, как устроена данная форма отправки писем.

Форма выглядит следующим образом:

Значения ячеек B3 и B4 могут быть изменены на усмотрение пользователя (в зависимости от причины обращения в службу поддержки). В ячейке B5 записана функция:

Данная функция выполняет конкатенацию (сцепление текстовых строк, принимаемых в качестве параметров).

  • mailto: – команда отправки письма;
  • B2 – ячейка, содержащая email службы поддержки;
  • "?subject=" – команда для записи темы письма;
  • КОДИР.URL(B3) – функция, которая выполняет преобразование текста темы письма в кодировку URL;
  • "&body=" – команда для записи текста письма;
  • КОДИР.URL(B4) – текст письма в кодировке URL.

В ячейке B6 записана следующая функция:

  • B5 – URL-команда на отправку письма, содержащая тему и текст письма;
  • "Отправить письмо" – имя гиперссылки.

В результате нажатия на гиперссылку будет открыт используемый по умолчанию почтовый клиент, например, Outlook (но в данном случае, стандартный клиент Windows).

Функция ГИПЕРССЫЛКА() , английский вариант HYPERLINK(), создает ярлык или гиперссылку, которая позволяет открыть страницу в сети интернет, файл на диске (документ MS EXCEL, MS WORD или программу, например, Notepad.exe) или перейти к указанному листу (диапазону ячеек) в текущей книге .

Функция ГИПЕРССЫЛКА () имеет следующий синтаксис.

Синтаксис функции

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

Адрес — адрес страницы в сети интернет или путь к файлу на диске. Адрес может указывать на определенное место в Книге, например на ячейку или именованный диапазон. Путь может представлять собой путь к файлу на жестком диске, либо ]]> URL-адрес в Интернете или интрасети. Аргумент Адрес записывается в виде текстовой строки, заключенной в кавычки, или представляет собой ссылку на ячейку, содержащую гиперссылку или путь в виде текстовой строки (без кавычек).

Имя — необязательный аргумент, необходимый для отображения текста гиперссылки (т.е. просто текстовая строка для отображения в гиперссылке). Текст ссылки отображается синим цветом с подчеркиванием. Если этот аргумент опущен, в ячейке в качестве текста ссылки отображается аргумент Адрес. Также как и Адрес, аргумент Имя может быть представлен текстовой строкой, именем или ссылкой на ячейку, содержащей текст. Если аргумент Имя возвращает значение ошибки (например, #ЗНАЧ!), вместо текста ссылки в ячейке отображается значение ошибки.

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

Переход на страницу Интернет

Для перехода на страницу интернет, например excel2.ru ]]> необходимо ввести в ячейку следующую формулу и нажать клавишу ENTER.

=ГИПЕРССЫЛКА(" ]]> http://www.excel2.ru ]]> ";"Перейти на сайт excel2.ru")

Если опустить второй аргумент, то в ячейке вместо слов Перейти на сайт excel2.ru будет просто отображен адрес ]]> http://www.excel2.ru ]]>

Перейти на конкретную страницу можно с помощью вот такой формулы

=ГИПЕРССЫЛКА("http://excel2.ru/articles/imena";"Перейти к статье Имена на сайте excel2.ru")

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

= ГИПЕРССЫЛКА(" ]]> http://example.microsoft.com/report/budget ]]> report.xlsx"; "Щелкните, чтобы просмотреть отчет")

Открываем файл на диске

С помощью функции ГИПЕРССЫЛКА () можно открыть файл, сохраненный на диске компьютера или сервера. Откроем стандартный файл Windows для редактирования текста Notepad.exe

Если на компьютере имеется диск D: на котором в папке Finance имеется файл БазаДанных.xlsx, то открыть его поможет формула

= ГИПЕРССЫЛКА("[D:FinanceБазаДанных.xlsx]лист1!A5";"Открыть Книгу MS EXCEL и перейти на Листе1 в ячейку А5")

После этого, в окне файла БазаДанных.xlsx, станет активной ячейка A5.

Примечание: После ввода формулы с функцией ГИПЕРССЫЛКА() , указывающей на место в другом листе или книге, сохраните книгу перед тестированием гиперссылки, чтобы избежать сообщения об ошибке.

Переходим на другой лист в текущей книге

Предположим, что требуется сделать ссылку с Листа1 на Лист2 в книге БазаДанных.xlsx.

Поместим формулу с функцией ГИПЕРССЫЛКА() в ячейке А18 на Листе1 (см. файл примера ).

=ГИПЕРССЫЛКА("[БазаДанных.xlsx]Лист2!A1";"Нажмите ссылку, чтобы перейти на Лист2 этой книги, в ячейку А1")

Указывать имя файла при ссылке даже внутри одной книги – обязательно. При переименовании книги или листа ссылка перестанет работать. Но, с помощью функции ЯЧЕЙКА() можно узнать имя текущей книги и листа (см. здесь и здесь).

Плохой новостью является то, что если у Вас открыто 2 или более файлов EXCEL (в одном экземпляре программы, см. примечание ниже), то функция ЯЧЕЙКА() может вернуть имя другого открытого файла (если Вы его изменяли). Так что будьте осторожнее с функцией ЯЧЕЙКА() . Поэтому иногда удобнее ссылки внутри книги создавать с помощью стандартного механизма гиперссылок, нажав CTRL+K или через меню Вставка/ Связи/ Гиперссылка (после вызова окна Вставка гиперссылки выберите Связать с: местом в документе).

Примечание: Открыть несколько книг EXCEL можно в одном окне MS EXCEL (в одном экземпляре MS EXCEL) или в нескольких. Обычно книги открываются в одном экземпляре MS EXCEL (когда Вы просто открываете их подряд из Проводника Windows или через Кнопку Офис в окне MS EXCEL). Второй экземпляр MS EXCEL можно открыть запустив файл EXCEL.EXE, например через меню Пуск. Чтобы убедиться, что файлы открыты в одном экземпляре MS EXCEL нажимайте последовательно сочетание клавиш CTRL+TAB – будут отображаться все окна Книг, которые открыты в данном окне MS EXCEL. Для книг, открытых в разных окнах MS EXCEL (экземплярах MS EXCEL) это сочетание клавиш не работает. Удобно открывать в разных экземплярах Книги, вычисления в которых занимают продолжительное время. При изменении формул MS EXCEL пересчитывает только книги открытые в текущем экземпляре.

Составляем оглавление книги

В статье Оглавление книги на основе гиперссылок описан подход к созданию оглавлению.

Гиперссылки в Excel (Полное руководство + примеры)

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

Например, ниже приведен список, в котором у меня есть названия компаний, на которые есть гиперссылки на URL-адрес веб-сайта компании. Когда вы нажимаете на ячейку, она автоматически открывает браузер по умолчанию (в моем случае Chrome) и переходит по этому URL-адресу.

С гиперссылками в Excel можно делать много вещей (например, ссылку на внешний веб-сайт, ссылку на другой лист / книгу, ссылку на папку, ссылку на электронное письмо и т. Д.).).

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

Как вставлять гиперссылки в Excel

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

  • Вручную ввести URL (или скопировать вставку)
  • Использование функции ГИПЕРССЫЛКА
  • Использование диалогового окна «Вставить гиперссылку»

Давайте узнаем о каждом из этих методов.

Введите URL-адрес вручную

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

Ниже приведены шаги, которые превратят простой URL-адрес в гиперссылку:

  1. Выберите ячейку, в которой вы хотите получить гиперссылку.
  2. Нажмите F2, чтобы перейти в режим редактирования (или дважды щелкните ячейку).
  3. Введите URL-адрес и нажмите клавишу ВВОД. Например, если я наберу URL-адрес - https://trumpexcel.com в ячейке и нажму Enter, он создаст на него гиперссылку.

Обратите внимание, что вам нужно добавить http или https для тех URL-адресов, в которых нет www.Если в качестве префикса используется www, будет создана гиперссылка, даже если вы не добавите http / https.

Аналогичным образом, когда вы копируете URL-адрес из Интернета (или другого документа / файла) и вставляете его в ячейку в Excel, он автоматически становится гиперссылкой.

Вставка с помощью диалогового окна

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

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

  1. Выберите ячейку, в которой вы хотите разместить гиперссылку.
  2. Введите текст, на который должна быть сделана гиперссылка.В этом случае я использую текст «Блог Sumit».
  3. Щелкните вкладку «Вставка».
  4. Щелкните кнопку ссылки. Откроется диалоговое окно «Вставить гиперссылку» (вы также можете использовать сочетание клавиш - Control + K).
  5. В диалоговом окне «Вставить гиперссылку» введите URL-адрес в поле «Адрес».
  6. Нажмите кнопку ОК.

Это вставит гиперссылку на ячейку, при этом текст останется прежним.

С помощью диалогового окна «Вставить гиперссылку» можно сделать еще много вещей (например, создать гиперссылку на другой лист в той же книге, создать ссылку на документ / папку, создать ссылку на адрес электронной почты. , так далее.). Все это будет рассмотрено позже в этом руководстве.

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

Другой способ вставки ссылки в Excel - использование функции ГИПЕРССЫЛКА.

Ниже приведен синтаксис:

  HYPERLINK (link_location, [friendly_name])  
  • link_location: Это может быть URL-адрес веб-страницы, путь к папке или файлу на жестком диске, место в документ (например, определенная ячейка или именованный диапазон на листе или книге Excel).
  • [friendly_name]: необязательный аргумент. Это текст, который вы хотите поместить в ячейку с гиперссылкой. Если вы опустите этот аргумент, он будет использовать текстовую строку link_location в качестве понятного имени.

Ниже приведен пример, в котором у меня есть названия компаний в одном столбце и URL-адреса их веб-сайтов в другом столбце.

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

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

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

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

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

Ниже приведены шаги по созданию гиперссылки на лист 2 в той же книге:

  1. Выберите ячейку, в которой вы хотите разместить ссылку
  2. Введите текст, который вы хотите быть гиперссылкой.В этом примере я использовал текст «Ссылка на Sheet2».
  3. Щелкните вкладку Вставка.
  4. Щелкните кнопку ссылки. Откроется диалоговое окно «Вставить гиперссылку» (вы также можете использовать сочетание клавиш - Control + K).
  5. В диалоговом окне «Вставить гиперссылку» выберите параметр «Поместить в этот документ» на левой панели.
  6. Введите ячейку, на которую вы хотите сделать гиперссылку (я использую по умолчанию A1).
  7. Выберите лист, на который вы хотите создать гиперссылку (в данном случае - Sheet2).
  8. Нажмите «ОК».

Примечание. Тот же метод можно использовать для создания гиперссылки на любую ячейку в той же книге. Например, если вы хотите установить ссылку на удаленную ячейку (скажем, K100), вы можете сделать это, используя эту ссылку на ячейку на шаге 6 и выбрав существующий лист на шаге 7.

Вы также можете использовать тот же метод для связывания к определенному имени (именованная ячейка или именованный диапазон). Если у вас есть именованные диапазоны (именованные ячейки) в книге, они будут перечислены в категории «Определенные имена» в диалоговом окне «Вставить гиперссылку».

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

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

Следующая формула сделает это:

 = ГИПЕРССЫЛКА ("#" & "Sheet2! A1", "Link to Sheet2") 

Ниже показано, как работает эта формула:

  • «#» сообщит формуле обратитесь к той же книге.
  • «Лист2! A1» сообщает формулу, с которой должна быть связана ячейка в той же книге.
  • «Ссылка на лист2» ​​- это текст, который отображается в ячейке.

Создание гиперссылки на файл (в той же или разных папках)

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

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

  1. Выберите ячейку, в которой вы хотите разместить гиперссылку.
  2. Щелкните вкладка "Вставка".
  3. Щелкните кнопку ссылки.Откроется диалоговое окно «Вставить гиперссылку» (вы также можете использовать сочетание клавиш - Control + K).
  4. В диалоговом окне «Вставить гиперссылку» выберите параметр «Существующий файл или веб-страница» на левой панели.
  5. Выберите «Текущая папка» в параметрах поиска.
  6. Выберите файл, для которого вы хотите создать гиперссылку. Обратите внимание, что вы можете ссылаться на любой тип файла (файлы Excel и файлы, отличные от Excel).
  7. [Необязательно] Измените текст на отображаемое имя, если хотите.
  8. Нажмите ОК.

Если вы хотите создать ссылку на файл, который находится не в той же папке, вы можете просмотреть файл и затем выбрать его. Чтобы просмотреть файл, щелкните значок папки в диалоговом окне «Вставить гиперссылку» (как показано ниже).

Вы также можете сделать это с помощью функции ГИПЕРССЫЛКА.

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

 = ГИПЕРССЫЛКА («Test.xlsx», «Test File») 

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

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

Здесь также используется та же методика.

Ниже приведены шаги для создания гиперссылки на папку:

  1. Скопируйте адрес папки, для которой вы хотите создать гиперссылку.
  2. Выберите ячейку, в которой вы хотите разместить гиперссылку.
  3. Щелкните вкладку «Вставить».
  4. Щелкните кнопку ссылки. Откроется диалоговое окно «Вставить гиперссылку» (вы также можете использовать сочетание клавиш - Control + K).
  5. В диалоговом окне «Вставить гиперссылку» вставьте адрес папки
  6. Нажмите кнопку «ОК».

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

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

 = ГИПЕРССЫЛКА ("C: \ Users \ sumit \ Desktop \ Test", "Test Folder") 

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

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

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

Ниже приведены шаги по созданию гиперссылки электронной почты:

  1. Выберите ячейку, в которой вы хотите разместить гиперссылку.
  2. Щелкните вкладку «Вставить».
  3. Щелкните кнопку ссылки. Откроется диалоговое окно «Вставить гиперссылку» (вы также можете использовать сочетание клавиш - Control + K).
  4. В диалоговом окне вставки щелкните «Адрес электронной почты» в параметрах «Ссылка на».
  5. Введите адрес электронной почты и строку темы.
  6. [Необязательно] Введите текст, который должен отображаться в клетка.
  7. Нажмите ОК.

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

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

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

 = ГИПЕРССЫЛКА («mailto: [email protected]», «Отправить электронное письмо») 

Обратите внимание, что вам нужно использовать mailto: перед адресом электронной почты в формуле.Это указывает функции ГИПЕРССЫЛКА открыть почтовый клиент по умолчанию и использовать следующий адрес электронной почты.

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

 = ГИПЕРССЫЛКА ("mailto: [email protected],? Cc = & bcc = & subject = Excel is Awesome", "Создать электронную почту ") 

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

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

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

Если у вас всего несколько гиперссылок, вы можете удалить их вручную, но если у вас их много, вы можете использовать для этого макрос VBA.

Удаление гиперссылок вручную

Ниже приведены шаги по удалению гиперссылок вручную:

  1. Выберите данные, из которых вы хотите удалить гиперссылки.
  2. Щелкните правой кнопкой мыши любую выбранную ячейку.
  3. Нажмите на опцию «Удалить гиперссылку».

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

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

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

Ниже приведен код VBA, который удаляет гиперссылки из выбранных ячеек:

 Sub RemoveAllHyperlinks ()
Код от Sumit Bansal @ trumpexcel.com
Selection.Hyperlinks.Delete
End Sub 

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

 Sub RemoveAllHyperlinks ()
Код от Sumit Bansal @ trumpexcel.ком
ActiveSheet.Hyperlinks.Delete
End Sub 

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

Вам необходимо добавить этот код VBA в обычный модуль редактора VB.

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

Вот подробное руководство по удалению гиперссылок в Excel.

Предотвратить автоматическое создание гиперссылок в Excel

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

А некоторых это раздражает.

Если вы относитесь ко второй категории, позвольте мне показать вам способ запретить Excel автоматически преобразовывать URL в гиперссылки.

Причина этого в том, что в Excel есть настройка, которая автоматически преобразует «Интернет и сетевые пути» в гиперссылки.

Вот шаги, чтобы отключить этот параметр в Excel:

  1. Щелкните вкладку Файл.
  2. Щелкните "Параметры".
  3. В диалоговом окне «Параметры Excel» щелкните «Проверка» на левой панели.
  4. Нажмите кнопку «Параметры автозамены».
  5. В диалоговом окне «Автозамена» выберите вкладку «Автоформат при вводе».
  6. Снимите флажок - «Интернет и сетевые пути с гиперссылками»
  7. Нажмите ОК.
  8. Закройте диалоговое окно «Параметры Excel».

Если вы выполнили следующие шаги, Excel не будет автоматически преобразовывать URL-адреса, адреса электронной почты и сетевые пути в гиперссылки.

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

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

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

Однако это можно сделать с помощью VBA.

Например, предположим, что у вас есть набор данных (как показано ниже), и вы хотите извлечь URL-адрес гиперссылки в соседнюю ячейку.

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

Извлечь гиперссылку в соседнем столбце

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

 Sub ExtractHyperLinks ()
Dim HypLnk как гиперссылка
Для каждого выбранного HypLnk.Гиперссылки
    HypLnk.Range.Offset (0, 1) .Value = HypLnk.Address
Следующий HypLnk
End Sub 

Приведенный выше код проходит через все ячейки в выделенном фрагменте (с использованием цикла FOR NEXT) и извлекает URL-адреса в соседней ячейке.

Если вы хотите получить гиперссылки на всем листе, вы можете использовать следующий код:

 Sub ExtractHyperLinks ()
При ошибке Возобновить Далее
Dim HypLnk как гиперссылка
Для каждого HypLnk в ActiveSheet.
    HypLnk.Range.Offset (0, 1) .Value = HypLnk.Address
Следующий HypLnk
End Sub 

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

Извлечение гиперссылки с помощью формулы (созданной с помощью VBA)

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

Но если у вас есть список гиперссылок, который постоянно расширяется, вы можете создать определяемую пользователем функцию / формулу в VBA.

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

Ниже приведен код, который создаст UDF для получения гиперссылок:

 Функция GetHLink (rng As Range) As String
    Если rng (1) .Hyperlinks.Count <> 1 Тогда
        GetHLink = ""
    Еще
        GetHLink = rng.Hyperlinks (1) .Address
    Конец, если
Конечная функция 

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

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

Поиск гиперссылок с определенным текстом

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

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

И нет ... выполнение этого вручную не является вариант.

Вы можете сделать это с помощью замечательной функции Excel - «Найти и заменить».

С его помощью вы можете быстро найти и выбрать все ячейки с гиперссылкой, а затем изменить текст 2019 на 2020.

Ниже приведены шаги для выбора всех ячеек с гиперссылкой и текстом 2019:

  1. Выбрать диапазон, в котором вы хотите найти ячейки с гиперссылками с 2019. Если вы хотите найти на всем листе, выберите весь лист (щелкните маленький треугольник в верхнем левом углу).
  2. Щелкните вкладку «Главная».
  3. В группе «Редактирование» нажмите «Найти и выбрать».
  4. В раскрывающемся списке нажмите «Заменить».Откроется диалоговое окно «Найти и заменить».
  5. В диалоговом окне «Найти и заменить» нажмите кнопку «Параметры». В диалоговом окне отобразятся дополнительные параметры.
  6. В параметрах «Найти» нажмите маленькую направленную вниз стрелку на кнопке «Формат» (как показано ниже).
  7. Щелкните «Выбрать формат из ячейки». Это превратит ваш курсор в значок плюса со значком выбора формата.
  8. Выберите любую ячейку, в которой есть гиперссылка. Вы заметите, что формат отображается в поле слева от кнопки «Формат».Это означает, что выбран формат выбранной ячейки.
  9. Введите 2019 в поле «Найти» и 2020 в поле «Заменить на».
  10. Нажмите кнопку «Заменить все».

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

Вы также можете использовать этот метод, чтобы найти все ячейки с гиперссылками и получить их список. Для этого вместо того, чтобы нажимать «Заменить все», нажмите кнопку «Найти все».Это мгновенно предоставит вам список всех адресов ячеек с гиперссылками (или гиперссылками с определенным текстом, в зависимости от того, что вы искали).

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

Выбор ячейки с гиперссылкой в ​​Excel

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

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

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

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

Выберите ячейку (не открывая URL-адрес)

Это простой трюк.

При наведении курсора на ячейку, в которой есть гиперссылка, вы увидите значок руки (который указывает, что если вы щелкнете по ней, Excel откроет URL-адрес в браузере)

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

Через секунду вы заметите, что значок курсора в виде руки изменится на значок плюса, и теперь, когда вы оставите его, Excel не будет открывать URL-адрес.

Вместо этого он выберет ячейку.

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

Хороший трюк… не так ли?

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

Это еще одна вещь, которая может свести вас с ума.

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

Вот быстрое решение.

Это происходит, когда в этих ячейках включен перенос текста.

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

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

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

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

Пример 1. Создание индекса всех листов в книге

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

Это может быть полезно, если у вас есть данные за 12 месяцев на 12 разных листах и ​​вы хотите создать один индексный лист, который будет связан со всеми этими листами с ежемесячными данными.

Ниже приведен код, который сделает это:

 Sub CreateSummary ()
Создано Sumit Bansal из trumpexcel.com
'Этот код можно использовать для создания сводной таблицы с гиперссылками
Размер x как рабочий лист
Тусклый счетчик как целое число
Счетчик = 0
Для каждого x в листах
Счетчик = Счетчик + 1
Если Counter = 1, то ничего не делать
    С ActiveCell
        .Значение = x.Name
        .Hyperlinks.Add ActiveCell, "", x.Name & "! A1", TextToDisplay: = x.Name, ScreenTip: = "Щелкните здесь, чтобы перейти к рабочему листу"
        С листами (счетчик)
            .Range ("A1"). Value = "Вернуться в" & ActiveSheet.Name
            .Hyperlinks.Add Sheets (x.Name) .Range ("A1"), "", _
            "'" & ActiveSheet.Name & "'" & "!" & ActiveCell.Address, _
            Подсказка: = "Вернуться в" & ActiveSheet.Name
        Конец с
    Конец с
ActiveCell.Смещение (1, 0). Выбрать
Ничего не делать:
Далее x
End Sub 

Вы можете поместить этот код в обычный модуль в книге (в редакторе VB)

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

Подробнее об этом примере можно прочитать здесь.

Примечание. Этот код работает, когда у вас есть лист (в котором вы хотите получить сводку всех листов со ссылками) в начале.Если это не в начале, это может не дать правильных результатов).

Пример 2 - Создание динамических гиперссылок

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

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

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

Например, в приведенном ниже примере я хочу, чтобы гиперссылка в ячейке E2 указывала на веб-сайт компании на основе раскрывающегося списка, выбранного пользователем (в ячейке D2).

Это можно сделать с помощью приведенной ниже формулы в ячейке E2:

 = ГИПЕРССЫЛКА (ВПР (D2, $ A $ 2: $ B $ 6,2,0), «Щелкните здесь») 

В приведенной выше формуле используется Функция ВПР для получения URL-адреса из таблицы слева. Затем функция ГИПЕРССЫЛКА использует этот URL-адрес для создания гиперссылки в ячейке с текстом «Щелкните здесь».

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

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

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

Пример 3 - Быстрое создание простых писем с использованием функции гиперссылки

Как я упоминал ранее в этой статье, вы можете использовать функцию ГИПЕРССЫЛКА для быстрого создания простых писем (с предварительно заполненными электронными письмами получателя и строкой темы).

Идентификатор электронной почты единственного получателя

 = ГИПЕРССЫЛКА (« mailto: [email protected] », «Создать электронную почту») 

Это откроет ваш почтовый клиент по умолчанию с идентификатором электронной почты [email protected] в Поле «Кому».

Идентификатор электронной почты нескольких получателей

 = ГИПЕРССЫЛКА («mailto: [email protected] , def @ trumpexcel.com », «Создать электронную почту») 

Для отправки электронной почты нескольким получателям используйте запятую для отдельные идентификаторы электронной почты.Это откроет почтовый клиент по умолчанию со всеми идентификаторами электронной почты в поле «Кому».

Добавить получателей в список CC и BCC

 = ГИПЕРССЫЛКА ("mailto: abc @ trumpexcel.com, def @ trumpexcel.com ? Cc = 123 @ trumpexcel.com & bcc = 456 @ trumpexcel.com ", "Создать Электронная почта ») 

Чтобы добавить получателей в список CC и BCC, используйте вопросительный знак«? »По окончании аргумента mailto и присоедините CC и BCC с помощью« & ». Когда вы нажимаете на ссылку в Excel, в поле «Кому» отображаются первые 2 идентификатора, 123 @ trumpexcel.com в поле «CC» и [email protected] в поле «BCC».

Добавить строку темы

 = ГИПЕРССЫЛКА ("mailto: abc @ trumpexcel.com, def @ trumpexcel.com? Cc = 123 @ trumpexcel.com & bcc = 456 @ trumpexcel.com &  subject = Excel великолепен ", " Создать электронную почту ") 

Вы можете добавить строку темы, используя & Код темы. В этом случае в поле «Тема» будет добавлено «Excel is Awesome».

Добавить однострочное сообщение в тело

 = ГИПЕРССЫЛКА ("mailto: abc @ trumpexcel.com, def @ trumpexcel.com? cc = 123 @ trumpexcel.com & bcc = 456 @ trumpexcel.com & subject = Excel - это круто &  body = Я люблю Excel  "," Отправить по электронной почте Trump Excel ") 

Это добавило бы одну строку 'I love Excel 'в тело сообщения электронной почты.

Добавить сообщение в несколько строк в теле

 = ГИПЕРССЫЛКА ("mailto: abc @ trumpexcel.com, def @ trumpexcel.com? cc = 123 @ trumpexcel.com & bcc = 456 @ trumpexcel .com & subject = Excel - это круто &  body = Я люблю Excel.% 0AExcel is Awesome  "," Создать электронную почту ") 

Чтобы добавить несколько строк в тело, вам нужно отделить каждую строку с помощью % 0A .Если вы хотите ввести два разрыва строки, добавьте дважды % 0A и так далее.

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

Надеюсь, вы нашли эту статью полезной.

Сообщите мне свои мысли в разделе комментариев.

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

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

Для быстрого доступа к связанной информации в другом файле, книге или на веб-странице вы можете вставить гиперссылку в ячейку листа Microsoft® Excel®.Вы также можете вставлять гиперссылки в определенные элементы диаграммы или изображения.

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

Добро пожаловать на , загрузите рабочую тетрадь для практики.

Применимо к: Microsoft ® Excel® для Windows 2010, 2013 и 2016.

  1. На листе исходной книги щелкните ячейку или объект, в котором вы хотите создать гиперссылку. Вы также можете щелкнуть правой кнопкой мыши ячейку или изображение, а затем нажать Гиперссылка .
  2. На вкладке Вставить в группе Ссылки щелкните Гиперссылка .
  3. Чтобы создать ссылку на место в текущей книге, щелкните Поместить в этот документ .
  4. В поле Или выберите место в этом документе , в разделе Ссылка на ячейку щелкните рабочий лист, на который нужно создать ссылку.
  5. Введите ссылку на ячейку в Введите в поле ссылки на ячейку, а затем нажмите ОК .

Примечание. Вот что нужно сделать, чтобы выбрать предопределенное имя; в списке в разделе «Определенные имена» щелкните имя, представляющее ячейки, на которые вы хотите создать ссылку, а затем нажмите кнопку «ОК».

  1. В поле Текст для отображения введите текст, который вы хотите использовать для представления гиперссылки.
  2. Для отображения полезной информации при наведении указателя мыши на гиперссылку щелкните ScreenTip .
  3. Введите нужный текст в поле Текст всплывающей подсказки , а затем нажмите ОК .

Редактирование гиперссылки

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

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

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

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

Связанные

Советы и приемы Excel, Советы и приемы добавить гиперссылку, отредактировать гиперссылку, Excel, Excel 2013, Excel 2016, Совет Excel, Советы Excel, на основе Excel, BI на основе Excel, гиперссылка, Microsoft Excel, отчетность, книга. постоянная ссылка.

Как связать данные в таблицах и книгах Excel!

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

○ Это руководство содержит партнерские ссылки. Прочтите нашу политику раскрытия информации, чтобы узнать больше.○

Зачем связывать данные электронной таблицы?

Создание ссылок или внешних ссылок на ячейки может избавить от необходимости хранить одни и те же данные на нескольких листах. Это экономит время, уменьшает количество ошибок и улучшает целостность данных. У менеджера по продажам может быть подробная таблица для каждого представителя и сводная таблица для сравнения производительности. Цены могут храниться в мастер-листе, на который ссылаются другие листы.

Как работает связывание данных электронной таблицы

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

Синтаксис формулы связи: = SheetName! CellReference и определяет рабочий лист и ячейку. В приведенном ниже примере мы ввели = Toronto! B6 в целевой лист, чтобы отобразить проданные единицы из электронной таблицы Toronto.

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

Как создать ссылку на лист

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

✦ Ссылка на данные рабочего листа - метод 1

Начинается с конечного листа и заканчивается на исходном листе.

  1. На рабочем листе назначения щелкните ячейку, которая будет содержать формулу связи, и введите знак равенства, но НЕ нажимайте Enter (рисунок 1).
  2. На исходном листе щелкните ячейку с данными для связи (рисунок 2) и нажмите Enter. Excel вернется к конечному листу и отобразит связанные данные. Excel создает формулу связи с относительными ссылками на ячейки: = Париж! B6.

Учебник KeynoteSupport.com

✦ Ссылка на данные рабочего листа - второй метод ✦

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

  1. На исходном листе скопируйте * данные, которые нужно связать.Как и в приведенном выше примере, мы вводим значение ячейки B6 из рабочего листа Paris.
  2. На листе назначения щелкните ячейку, в которой требуется формула связи, и нажмите Вставить ⇒ Вставить ссылку на вкладке «Главная» - см. Рисунок 3. (Старые версии Excel: Правка ⇒ Специальная вставка ⇒ Вставить ссылку)
  3. Целевой рабочий лист отображает значение формулы, а формула связи отображается в строке формул (рисунок 4). С помощью этого метода Excel создает формулу с абсолютной ссылкой на ячейку: = Париж! $ B $ 6.
  4. Вернитесь к исходному листу и нажмите клавишу ESC, чтобы удалить анимированную границу.

* Методы копирования: 1) сочетание клавиш Ctrl + C, 2) контекстное меню, 3) кнопка копирования на вкладке «Главная» ленты.

✦ Записывать формулы вручную - метод 3

Ввести формулы вручную несложно: знак равенства, имя листа, восклицательный знак и ссылка на ячейку: = SheetName! CellReference. Если вам нужно ввести несколько формул, откройте файл Блокнота, введите формулу и скопируйте, вставьте и измените.

Не беспокойтесь об использовании заглавных букв в именах листов. Если имя рабочего листа написано с заглавной буквы, а вы вводите имя в формулу без заглавной буквы, Excel добавит его. И наоборот. См. Раздел «Именование и переименование рабочих листов».

В формулах связывания пробелы или специальные символы должны быть заключены в одинарные кавычки ; например = «Розничные цены»! B6. (Excel автоматически заключает имена рабочих листов C или R в одинарные кавычки.)

Как связать диапазон ячеек

Чтобы связать диапазон ячеек, выберите ячейки и нажмите «Копировать».На листе назначения щелкните ячейку, в которой должна располагаться верхняя левая ячейка диапазона, и нажмите «Вставить» ⇒ Вставить ссылку на вкладке «Главная».

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

Учебное пособие по KeynoteSupport.com

Связывание с листами в разных книгах

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

= [BookName.xlsx] SheetName! CellReference

= [BookName.xlsx] SheetName!
CellReference

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

Перемещение листов со ссылками на внешние ячейки

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

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

Убедитесь, что автоматический расчет включен

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

Excel 2007 и более новые версии

Метод 1 : В разделе «Расчет» на ленте Excel щелкните маленькую стрелку рядом с «Параметры расчета» и выберите «Автоматически», как показано.

Метод второй : Щелкните Файл (или кнопку Excel) и щелкните Параметры Excel в нижней части окна. Слева нажмите «Формулы» и убедитесь, что в разделе «Параметры расчета» установлен флажок «Автоматически».

Excel 2003 и более ранние версии

  1. В верхней строке меню щелкните Инструменты и щелкните Параметры.
  2. Щелкните вкладку «Расчет» и выберите «Автоматически» в разделе «Расчет».

Учебник KeynoteSupport.com

Защитите формулы ссылок в таблицах

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

Учебное пособие по KeynoteSupport.com

Предупреждение системы безопасности Microsoft Office

При открытии целевой электронной таблицы может появиться предупреждение системы безопасности о том, что «Автоматическое обновление ссылок отключено», как показано на изображении ниже.

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

Предполагая, что на самом деле риска нет, и чтобы разрешить процесс связывания, щелкните переключатель «Включить это содержимое» и нажмите OK.

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

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

  • Щелкните «Открыть центр управления безопасностью» в нижнем левом углу отображаемого окна предупреждения и щелкните вкладку «Внешнее содержимое».
  • Во втором разделе нажмите «Включить автоматическое обновление для всех ссылок на книги»

↑ Вернуться к началу

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

Создайте гиперссылку в ячейке, чтобы люди, работающие с листом, могли быстро перейти на соответствующий веб-сайт или другой связанный элемент Smartsheet.

ПРИМЕЧАНИЕ. Создание гиперссылки отличается от связывания ячеек (которое позволяет ссылаться на ячейки на другом листе). Дополнительные сведения о связывании ячеек см. В разделе Ссылки ячеек: консолидация или поддержание согласованности данных.

Создать гиперссылку в ячейке

  1. Щелкните правой кнопкой мыши ячейку, в которую вы хотите вставить гиперссылку, и выберите Гиперссылка .

    Появится окно Гиперссылка .

    СОВЕТ. Вы также можете выбрать ячейку и использовать сочетание клавиш Ctrl + K (Command + K на Mac), чтобы открыть окно гиперссылки.

  2. Выберите вариант Link to URL или Link to other Smartsheet .

    • Ссылка на URL: Если вы выберете эту опцию, введите веб-адрес веб-сайта, на который вы ссылаетесь (например, abcwebsite.com). Smartsheet автоматически добавляет «http: //».

      ПРИМЕЧАНИЕ. Длина URL-адреса ограничена 900 символами. Ссылки на внутренние серверы (например, серверы Microsoft SharePoint) не поддерживаются.

    • Ссылка на другой Smartsheet: При выборе этого параметра щелкните <выбрать лист> .Появится форма Открыть лист . Выберите лист, на который вы хотите создать ссылку, и нажмите OK .

      ПРИМЕЧАНИЕ. Ссылки на листы не отображаются на опубликованном листе. Открытый доступ к листу можно предложить только путем ссылки на опубликованный URL-адрес листа.

  3. Введите или отредактируйте текст в поле Display Text .

    ПРИМЕЧАНИЕ. Это поле является обязательным, если вы выбрали параметр Связать с другим Smartsheet .Если вы выбрали опцию Ссылка на URL-адрес и ничего не вводили в это поле, отобразится URL-адрес.

  4. Нажмите ОК .

Текст ячейки представляет собой действующую ссылку на веб-сайт или другой лист.

ПРИМЕЧАНИЕ. URL-адреса, которые вы вводите непосредственно в ячейку, также будут отображаться как интерактивные ссылки.

Редактировать URL-адрес гиперссылки и отображаемый текст

Если вам нужно изменить отображаемый текст или саму ссылку:

  1. Щелкните ячейку правой кнопкой мыши и выберите Гиперссылка .
  2. Внесите желаемые изменения в ссылку и нажмите ОК .

Удалить гиперссылку

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

Удалить гиперссылку и любой отображаемый текст

Для полного удаления ОБЕИ ссылки и связанного с ней отображаемого текста (оставив ячейку пустой):

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

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

  1. Выберите ячейку, содержащую гиперссылку.
  2. Нажмите клавиши Ctrl + C (command + C на Mac) на клавиатуре.
  3. Щелкните правой кнопкой мыши ячейку, содержащую гиперссылку, и выберите «Специальная вставка».

    Появится форма «Специальная вставка».

  4. Выберите значений.
  5. Нажмите ОК.

Гиперссылки VBA - автоматизация Excel

Гиперссылки VBA

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

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

Добавить гиперссылку с VBA

Метод Hyperlinks.Add позволяет добавить гиперссылку в ячейку с помощью VBA. Это то, что вы могли бы сделать вместо того, чтобы вводить его прямо в ячейку.

Следующий код добавит гиперссылку в ячейку A1:

 Sub AddHyperlinkToCell ()
ActiveSheet.Hyperlinks.Add Range ("A1"), адрес: = "https://www.automateexcel.com/excel/"
Концевой переводник 

Результат:

AutoMacro - Генератор кода VBA

Добавление текста на дисплей с помощью VBA

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

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

 Подложка TextToDisplayForHyperlink ()

ActiveSheet.Hyperlinks.Add Range ("A1"), Address: = "https://www.automateexcel.com/excel/", TextToDisplay: = "Automate Excel"

Концевой переводник 

Результат:

Добавление всплывающей подсказки с помощью VBA

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

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

 Sub ScreenTipForHyperlink ()

ActiveSheet.Hyperlinks.Add Range ("A1"), Address: = "https://www.automateexcel.com/excel/", TextToDisplay: = "Automate Excel", ScreenTip: = "Это ссылка для Automate Excel"

Концевой переводник 

Результат:

Удалить гиперссылку с помощью VBA

Метод Hyperlinks.Delete можно использовать для удаления гиперссылки из ячейки.

Следующий код удалит гиперссылку из ячейки A1 и текст в ячейке.

Обратите внимание, что простое удаление гиперссылки не приводит к удалению самого текста, поэтому, если вы хотите, чтобы текст был удален, вы также должны использовать метод Clear.

 Sub DeleteHyperlinkinCell ()

Диапазон ("A1"). Гиперссылки. Удалить
Диапазон ("A1"). Очистить

Концевой переводник 

Удалить все гиперссылки на листе

Вы также можете удалить все гиперссылки на листе с помощью метода Hyperlinks.Delete.

Следующий код удалит все гиперссылки на первом листе вашей книги:

 Sub RemoveAllHyperlinksInASheet ()

ThisWorkbook.Sheets (1) .Hyperlinks.Delete

Концевой переводник 

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

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

 Sub FollowHyperlinkForWebsite ()

ActiveWorkbook.FollowHyperlink Address: = "https://www.automateexcel.com/excel", NewWindow: = True

Концевой переводник 

Переход по гиперссылке на папку на вашем диске

Следующий код откроет папку ExcelFiles на рабочем столе с помощью метода FollowHyperlink:

 Sub FollowHyperlinkForFolderOnDrive ()

ActiveWorkbook.FollowHyperlink Адрес: = "C: \ Desktop \ ExcelFiles"

Концевой переводник 

Переход по гиперссылке на файл на вашем диске

Следующий код откроет файл Excel с именем WorkbookOne в папке ExcelFiles на рабочем столе с помощью метода FollowHyperlink:

 Подписка FollowHyperlinkForFile ()

ActiveWorkbook.FollowHyperlink Address: = "C: \ Desktop \ ExcelFiles \ WorkbookOne.xlsx", NewWindow: = True

Концевой переводник 

Перейти к ячейке на другом листе той же книги

Вы можете добавить гиперссылку на ячейку на одном листе, которая переводит вас на другой лист в той же книге с VBA. Допустим, вы находитесь на Sheet1, ячейке A1 вашей книги, и вы хотите вставить гиперссылку на Sheet2, ячейку B2 той же книги, вы можете использовать для этого свойство SubAddress.

Следующий код демонстрирует, как это сделать:

 Sub GoToAnotherCellInAnotherSheetInTheSameWorkbook ()

ActiveSheet.Hyperlinks.Add Range ("A1"), Address: = "", SubAddress: = "'" & Sheet2.Name & "'! B2", TextToDisplay: = "Щелкните здесь, чтобы перейти к Sheet2, ячейке B2 той же книги "

Концевой переводник 

Показать все гиперссылки на листе

Вы можете получить доступ к коллекции гиперссылок и отобразить все гиперссылки на рабочем листе в промежуточном окне редактора VBA. Вы должны сначала нажать CTRL-G на клавиатуре или перейти в «Просмотр»> «Промежуточное окно» в редакторе VBE, чтобы просмотреть промежуточное окно.

Следующий код показывает, как просматривать гиперссылки на вашем листе в промежуточном окне:

 Подложка ShowAllTheHyperlinksInTheWorksheet ()

Dim ws как рабочий лист

Установить ws = ThisWorkbook.Sheets (1)

Для каждой ссылки в ws.Hyperlinks
Debug.Print lnk.Address
Следующий линк

Концевой переводник 

Результаты отображаются в промежуточном окне.

Показать все гиперссылки в книге

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

В следующем коде показано, как это сделать, и для этого используется вложенный цикл:

 Подложка ShowAllTheHyperlinksInTheWorkbook ()

Dim ws как рабочий лист

Для каждой страницы в ActiveWorkbook.Worksheets
Для каждой ссылки в ws.Hyperlinks
MsgBox lnk.Address
Следующий линк
Следующий ws

Концевой переводник 

Вы можете узнать больше о циклах For Loops в этом руководстве на нашем сайте.

Использование метода FollowHyperlink для создания писем

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

Приведенный ниже код покажет вам, как создавать электронные письма с помощью метода FollowHyperlink в VBA:

 Sub SendEmailUsingHyperlink ()

Dim msgLink As String

msgLink = "mailto:" & "[email protected]" & "?" & "subject =" & "Hello" & "&" & "body =" & "Как дела?"
ActiveWorkbook.FollowHyperlink (msgLink)

Концевой переводник 

Результат:

Добавление гиперссылки к автофигуре в Excel

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

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

 Sub AddingAHyperlinkToAShape ()

Dim myShape As Shape
Установить myDocument = Worksheets ("Sheet1")
Установите myShape = myDocument.Shapes.AddShape (msoShapeRoundedRectangle, 100, 100, 90, 30)

С myShape
.TextFrame.Characters.Text = "Автоматизировать Excel"
Конец с

ActiveSheet.Hyperlinks.Add Anchor: = myShape, Address: = "https: // www.automateexcel.com/excel "

Концевой переводник 

Результат:

Вставка формулы гиперссылки в ячейку с использованием VBA

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

Краткое напоминание о синтаксисе формулы гиперссылки на листе:

ГИПЕРССЫЛКА (расположение_ссылки, [понятное_имя])

link_location - это ссылка на документ, файл, место в книге или на сайте.

friendly_name - ( Необязательно ) - текстовое или числовое значение, отображаемое в ячейке.

В ячейке C4 вы хотите добавить гиперссылку с понятным текстом для отображения, теперь обычно вы вводите формулу = ГИПЕРССЫЛКА (B4, A4) в C4, чтобы получить следующее:

Вместо этого вы можете использовать VBA для достижения того же результата и вставить эту формулу в ячейку на Sheet1 со следующим кодом:

 Подложка InsertHyperlinkFormulaInCell ()

ActiveWorkbook.Листы ("Лист1"). Диапазон ("C4"). Формула = "= гиперссылка (B4, A4)"

Концевой переводник 

Добавление гиперссылки к кнопке в Access

VBA позволяет работать с гиперссылками и в Access. Допустим, у нас есть кнопка в форме, и когда пользователь нажимает на нее, мы хотим, чтобы он перешел на веб-сайт. Один из способов сделать это - использовать метод Application.FollowHyperlink.

У нас есть образец формы с кнопкой buttonOne, показанной ниже.

Код для этой кнопки будет:

 Кнопка Private SubOne_Click ()

Заявление.FollowHyperlink («https://www.automateexcel.com/excel/»)

Концевой переводник 

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

С гиперссылками в VBA можно работать и в Word.
Допустим, у нас есть текст в Word с надписью «Щелкните здесь, чтобы перейти на веб-сайт автоматизации Excel», который выбран, как показано ниже.

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

 Private Sub TurnASelectionIntoAHyperlink ()

ActiveDocument.Гиперссылки. Добавить привязку: = Selection.Range, Address: = "https://www.automateexcel.com/excel/", ScreenTip: = "Щелкните здесь, пожалуйста", Target: = NewWindow

Концевой переводник 

Результат:

Ссылка на таблицы с использованием определенных имен

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

Объектив

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

Эти гиперссылки были созданы с помощью команды Insert> Link> Insert Link . В появившемся диалоговом окне мы выбираем Place in This Document , а затем выбираем имя листа из списка, например, MyData :

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

… Время проходит…

Позже мы решили переименовать один из этих листов.

К нашему удивлению, когда мы нажимаем на соответствующую гиперссылку, мы получаем неожиданную ошибку о том, что Ссылка недействительна :

Мы получаем эту ошибку, потому что Excel не обновляет автоматически гиперссылки, когда мы меняем имя листа. Excel обновляет формул , когда мы обновляем имя листа. Однако он не отслеживает гиперссылок и таким же образом… отсюда и ошибка.

Не беспокойтесь! Мы просто будем использовать другой вариант гиперссылки при настройке наших ссылок… в частности, мы будем использовать определенные имена.

Ступеньки

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

  1. Создать определенное имя
  2. Ссылка на определенное имя
  3. Проверить

Создание определенного имени

Мы хотим создать определенное имя для каждого из конечных листов.Для этого мы просто переходим к первому целевому листу и выбираем ячейку A1 (или другую ячейку, если хотите). Затем мы вводим желаемое определенное имя (например, DataSheet ) в поле имени и нажимаем Enter :

Примечание. Поле имени находится слева от строки формул.

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

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

Ссылка на определенное имя

После того, как мы настроили все определенные имена, пришло время создать наши гиперссылки. Мы используем ту же команду Insert> Link> Insert Link , которую использовали в прошлый раз. Однако на этот раз мы видим, что наши новые определенные имена появляются под списком листов:

Выбираем соответствующее определенное имя и нажимаем ОК.

Мы повторяем для каждой ссылки, которую мы создаем.

Проверить это

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

Щелкаем по ссылке, и все работает как положено!

… Время проходит…

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

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

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

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

Образец файла

Как сделать гиперссылку на скрытый лист Excel

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

Например, у нас есть «Основной» рабочий лист с гиперссылками на 4 других листа книги.

На изображении ниже видно, что лист «Яблоки» скрыт.

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

Если вы новичок в Excel VBA, почему бы не ускорить процесс обучения, записавшись на курс Excel VBA для начинающих.

Посмотреть видео

Excel VBA для перехода по гиперссылке на скрытый рабочий лист

Начнем с открытия редактора Visual Basic, щелкнув Visual Basic на вкладке «Разработчик» или нажав Alt + F11 .

Нам нужно использовать этот код в событии Worksheet_FollowHyperlink, чтобы оно происходило при нажатии на ссылку. Нам нужно открыть окно кода для рабочего листа «Main», потому что именно там находится событие.

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

Чтобы получить доступ к событию Worksheet_FollowHyperlink, выберите Worksheet из списка объектов, а затем FollowHyperlink из списка процедур.

Теперь мы можем ввести некоторый код в предоставленную подпрограмму Worksheet_FollowHyperlink.

Название этого события вводит в заблуждение. Он называется FollowHyperlink, но на самом деле он не переходит по ссылке. Это просто событие, которое запускается, когда кто-то щелкает гиперссылку на этом листе.

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

Для первого примера код ниже подойдет.

  Private Sub Worksheet_FollowHyperlink (ByVal Target как гиперссылка)
Dim ShtName As String
ShtName = Target.Name
Таблицы (ShtName) .Visible = xlSheetVisible
Таблицы (ShtName) .Выбрать
Концевой переводник  

Этот код объявляет строковую переменную с именем ShtName. Затем он присваивает этой переменной имя гиперссылки (текст, отображаемый гиперссылкой). Для этого нам предоставлена ​​переменная с именем Target.

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

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

Этот второй пример кода не полагается на совпадение текста ссылки и имен листов и является более надежным.

  Private Sub Worksheet_FollowHyperlink (ByVal Target как гиперссылка)
Dim ShtName As String
ShtName = Left (Target.SubAddress, InStr (1, Target.SubAddress, "!") - 1)
Листы (ShtName).Visible = xlSheetVisible
Таблицы (ShtName) .Выбрать
Концевой переводник  

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

Это было сделано путем извлечения всех символов до восклицательного знака в дополнительном адресе. Это связано с тем, что восклицательные знаки разделяют имя листа и адрес или диапазон ячейки в Excel, то есть Sheet3! D2.

Для этого использовалась функция Left, а функция Instr определяла положение восклицательного знака.

Повторное скрытие листа при выходе из

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

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

Этот код будет помещен в событие «Деактивировать» на каждом листе.

Начните с двойного щелчка по листу в окне Project Explorer, чтобы открыть окно его кода.

Выберите Рабочий лист из списка объектов (точно так же, как ранее в этом руководстве), а затем Деактивировать из списка процедур.

И введите строку ниже внутри подпрограммы.

 Private Sub Worksheet_Deactivate () 
Me.Visible = xlSheetHidden
End Sub

Этот код обозначает себя как Я. Хороший простой код, чтобы лист скрывался при деактивации листа.

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *