Light-electric.com

IT Журнал
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

Постановка задачи

Имеем две таблицы на разных листах одной книги: таблицу с заказами от клиентов (на листе Заказы) и таблицу с клиентской базой (лист Клиенты). Работая с таблицей заказов, хочется иметь возможность быстро переходить на таблицу с клиентами, чтобы просмотреть подробные данные по клиенту (адрес, ФИО директора и т.д.). То есть, другими словами, хочется в таблице заказов иметь гиперссылку в каждой строке, при щелчке мышью по которой будет происходить переход на лист Клиенты, причем именно на ту строчку где упоминается данный клиент:

Что-то типа типа функции ВПР (VLOOKUP), но не ради подстановки данных, а для быстрой ссылки из одной таблицы в другую.

Шаг 1. Создаем переменную с именем листа

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

В Excel 2007/2010 для этого можно воспользоваться вкладкой Формулы (Formulas) и кнопкой Диспетчер имен (Name Manager) . В более старых версиях выбрать в меню Вставка — Имя — Присвоить (Insert — Name — Define) . В открывшемся окне нажмите кнопку Создать (New) и введите туда имя переменной (я назвал ее для примера Мой_Лист) и формулу в строку Диапазон (Reference) :

=ПСТР(ЯЧЕЙКА(«имяфайла»;Клиенты!$A$1); ПОИСК(«[«;ЯЧЕЙКА(«имяфайла»;Клиенты!$A$1)) ;256) &»!»

Разберем эту конструкцию на составляющие для понятности:

  1. ЯЧЕЙКА(«имяфайла»;Клиенты!$A$1) — функция, которая по адресу заданной ячейки (А1 с листа Клиенты) выдает любые нужные данные по листу и файлу. В данном случае — полный путь к текущему файлу до листа в виде D:Рабочие документыДоговоры[Бюджет.xls]Клиенты
  2. Из этой строки нам нужна только часть с именем файла и листа (без диска и папок), поэтому мы ищем первое вхождение квадратной открывающей скобки в строку с помощью функции ПОИСК (FIND) и затем вырезаем из строки все, начиная с этого символа и до конца (256 символов) с помощью функции ПСТР (MID) .
  3. В конце, к вырезанному фрагменту с именем файла и листа приклеиваем восклицательный знак — стандартный разделитель имен листов и адресов ячеек в формулах, т.к. дальше должны будут идти адреса ячеек.

Таким образом эта формула выдает на выходе имя текущего файла в квадратных скобках с расширением с приклееным к нему именем листа и восклицательным знаком. Работу формулы легко проверить — просто введите в любую пустую ячейку =Мой_Лист и нажмите клавишу Enter.

Шаг 2. Создаем гиперссылки

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

Разберем ее на составляющие аналогичным образом:

  1. Функция ПОИСКПОЗ(B2;Клиенты!$A$1:$A$7;0) — ищет порядковый номер ячейки в диапазоне А1:А7 на листе Клиенты, где встречается название текущего клиента из B2 (последний аргумент =0 означает поиск точного совпадения, аналогично функции ВПР)
  2. Функция АДРЕС формирует адрес ячейки (в виде текстовой строки) по номеру строки и столбца, т.е. адрес ячейки с нужным клиентом, куда должна потом ссылаться гиперссылка
  3. Затем мы приклеиваем к адресу ссылку на файл и лист (переменную Мой_Лист) и используем это в качестве аргумента для функции ГИПЕРССЫЛКА (HYPERLINK) , которая, собственно, и создает нужную нам ссылку.

При желании, можно заменить внешнее представление гиперссылки с банальных символов «>>» на что-нибудь поинтереснее с помощью функции СИМВОЛ (CHAR) , которая умеет выводить нестандартные символы по их кодам:

Так, например, если использовать шрифт Wingdings 3 и символ с кодом 117, то можно получить вот такие симпатичные значки гиперссылок:

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Гиперссылка в Excel — создание, изменение и удаление

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

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

1) Напрямую в ячейку

2) C помощью объектов рабочего листа (фигур, диаграмм, WordArt…)

3) C помощью функции ГИПЕРССЫЛКА

4) Используя макросы

Добавление гиперссылки напрямую в ячейку

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

Либо, аналогичную команду можно найти на ленте рабочей книги Вставка -> Ссылки -> Гиперссылка.

Привязка гиперссылок к объектам рабочего листа

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

Либо, аналогичным способом, как добавлялась гиперссылка в ячейку, выделить объект и выбрать команду на ленте. Другой способ создания – сочетание клавиш Ctrl + K – открывает то же диалоговое окно.

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

Читать еще:  Коэффициент вариации формула в excel

Добавление гиперссылок с помощью формулы ГИПЕРССЫЛКА

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

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

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

Например, если я введу в ячейку формулу =ГИПЕРССЫЛКА(Лист2!A1; «Продажи»). На листе выглядеть она будет следующим образом и отправит меня на ячейку A1 листа 2.

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

=ГИПЕРССЫЛКА(«http://exceltip.ru/»;»Перейти на Exceltip»)

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

Добавление гиперссылок с помощью макросов

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

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

Гиперссылки бывают внешние и внутренние. Универсальным способом их добавления является использование сочетаний клавиш Ctrl+K. После нажатия этого сочетания у Вас появится диалоговое окно Добавление гиперссылки:

Теперь Вы можете выбрать тип ссылки (внутренняя — местом в документе, или внешняя — файлом, веб-страницей, новым документом или электронной почтой), указать текст ссылки и путь. Но это общеизвестный способ, а мы рассмотрим варианты, как ещё можно создать гиперссылку.

Итак, для внешних ссылок:

Самый распространённый способ — это простое копирование нужного адреса из браузера и вставка его в ячейку. Начиная с версии Excel 2000, программа распознаёт URL если в тексте есть следующие сочетания символов (буквы только английского алфавита, регистр роли не играет):

  • http://
  • www.
  • ftp://
  • mailto:
  • file://
  • news:
  • mail@pochta

Если Вам не нужно автораспознавание URL и Вы хотите ввести Веб-адрес как текст, то следует поставить перед ним апостроф ( ). После окончания ввода (нажатия Enter) апостроф визуально пропадёт (хотя его можно будет увидеть в строке формул) и останется только текст адреса. На печать апостроф также не выводится.

Второй способ — это использование функции =ГИПЕРССЫЛКА(). Например ссылка на это сайт будет выглядеть так:

в английской версии:

При этом двойные кавычки Вам ставить не надо, функция сделает это сама.

И ещё один, достаточно экзотический и малоизвестный способ. Этот способ не требует использование мышки, но к сожалению, подходит только для Microsoft Internet Explorer, как к продукту выпущенному той же компанией, что и сам Excel.

  • Откройте Веб-страницу, адрес которой необходимо получить как гиперссылку в Excel;
  • Перейдите в Excel, выделите ячейку в которую необходимо вернуть гиперссылку и нажмите сочетание клавиш Ctrl+K;
  • Нажмите Alt+Tab для перехода в Internet Explorer;
  • Нажмите Alt+Tab для перехода назад в Excel;
  • Нажмите ОК

Для внутренних ссылок:

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

выберите пункт Создать гиперссылку. Вот и всё 🙂 Подробнее смотрите на видео.

Функция ГИПЕРССЫЛКА() в EXCEL

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

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

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

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

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

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

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

Читать еще:  Excel символ переноса строки в ячейке

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

Для перехода на страницу интернет, например 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 пересчитывает только книги открытые в текущем экземпляре.

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

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

Выводим диапазоны имен

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

Пусть в книге создано 3 именованных диапазона: квартал1, квартал2, квартал3 .

В ячейках А42:А44 выведем перечень имен (нажав клавишу F3 и далее нажав Все имена , см. статью Имена ). В ячейке С42 запишем формулу =ГИПЕРССЫЛКА(«[_Функция_ГИПЕРССЫЛКА.xlsx]»&A42;A42) и скопируем ее вниз (убедитесь, что Ваша книга, имя которой указано в формуле называется правильно).

Теперь, после нажатия гиперссылки, будет выделен соответствующий диапазон (на рисунке ниже отображено окно после нажатия ссылки Квартал1 ).

Добавляем кнопки навигации в панель быстрого доступа

Если в книге много гиперссылок, то по аналогии с Веб Браузерами можно использовать кнопки Назад и Далее в панели Быстрого доступа .

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

Затем, отобразите все команды и добавьте кнопки Назад и Далее.

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

How to dou

Вставка гиперссылок на листы в Excel макрос

Table of Contents:

Гиперссылки автоматизируют рабочие таблицы Excel 2013, открывая другие документы Office и рабочие книги Excel и рабочие листы всего лишь щелчком мыши. Неважно, находятся ли эти документы на вашем жестком диске, сервер в локальной сети (LAN) или веб-страницы в Интернете или интранет компании.

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

Читать еще:  Условное форматирование в excel 2003

Гиперссылки, которые вы добавляете в листы Excel, могут быть следующих типов:

Текстовые записи в ячейках (называемые гипертекстом, обычно форматированные как подчеркнутый синий текст)

Клип и импортированную графику из файлов, которые вы вставили в рабочий лист

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

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

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

Нажмите кнопку «Гиперссылка» на вкладке «Вставка» ленты или нажмите Alt + NI или просто нажмите Ctrl + K.

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

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

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

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

Чтобы гиперссылка переместила указатель ячейки на другую ячейку или диапазон ячеек в той же книге, нажмите кнопку «Место в этом документе». Затем введите адрес ячейки или диапазона ячеек в текстовом поле «Тип ячейки» или выберите нужное имя листа или имя диапазона из поля «Выбрать место в этом документе».

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

Как только вы начнете вводить адрес электронной почты в текстовом поле E-mail Address, Excel вставляет текстовое письмо: перед тем, что вы набрали. (mailto: это тег HTML, который сообщает Excel, чтобы открыть вашу электронную почту при нажатии гиперссылки.)

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

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

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

(Необязательно) Чтобы добавить экранную подсказку к гиперссылке, которая появляется, когда вы позиционируете указатель мыши над гиперссылкой, нажмите кнопку «ScreenTip», введите текст, который вы хотите отобразить рядом с указателем мыши в поле ScreenTip, а затем нажмите «ОК».

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

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

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

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

Ссылка на основную публикацию
Adblock
detector