Ссылки в excel

Содержание:

Что такое гиперссылка
Как отформатировать гиперссылку в Excel
Динамическая гиперссылка в Excel
Активный URL в ячейке Excel
Функция ГИПЕРССЫЛКА() в MS EXCEL
Функция ГИПЕРССЫЛКА и особенности ее аргументов
Внешние ссылки в Эксель
Абсолютная и относительная ссылка на ячейку в Excel
Изменение типа ссылки: относительная, абсолютная, смешанная
Абсолютные и относительные гиперссылки
Что такое гиперссылка
Как найти круговые ссылки в Excel?
Абсолютная и относительная ссылка на ячейку в Excel
Адресация ячеек в диапазоне
Типы ссылок на ячейки в формулах Excel
Как преобразовать кучу текстовых URL-адресов в активные гиперссылки в Excel?
Способ 3: Создание макроса для быстрой активации ссылок
Динамическая гиперссылка в Excel

Что такое гиперссылка

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

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

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

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

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

Относительные, абсолютные и смешанные ссылки в excel

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

Перейдите на вкладку панели инструментов “Главная”, затем в раздел “Стили ячеек”:

Кликните на “Гиперссылка” правой кнопкой мыши и выберите пункт “Изменить” для редактирования формата ссылки:

Кликните на “Открывавшаяся гиперссылка” правой кнопкой мы и выберите пункт “Изменить” для редактирования формата ссылки;
В диалоговом окне “Стили” нажмите кнопку “Формат”:

в диалоговом окне “Format Cells” перейдите на вкладки “Шрифт” и/или “Заливка” для настройки формата ссылок:

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

Как сделать ссылки в html? тег ссылки, примеры

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

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

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

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

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

Активный URL в ячейке Excel

Относительные и абсолютные ссылки

Работая в Excel с большим массивом ссылок иногда требуется, чтобы текстовые URL ссылки на страницу сайта были в виде активной гиперссылки, по которой можно кликнуть мышкой и перейти на саму страницу, самый простой способ в этом случае — это активировать гиперссылку в ячейке Excel, нажав клавишу F2 и затем Enter.

Когда у вас всего несколько строк в таблице, то проделать эту процедуру не составит никакого труда, но если у вас несколько сотен строк или даже несколько тысяч, то такие монотонные действия займут уйму времени. Так как сделать ссылку в excel на web сайт активной и кликабельной? Чтобы ускорить этот процесс его нужно автоматизировать с помощью VBA (Visual Basic) макроса Excel!

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

кнопка «электронной почтой»

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

функции «Копировать», нажимаем

квартал1, квартал2, квартал3. на Лист2 этойФункция ГИПЕРССЫЛКА(), английский вариант Next cl End дать ссылку ии Вы хотите пользователя (в зависимости окне. Если по для заполнения. Адрес можно применять при изменить структуру документа.Теперь ячейка будет связана ячейки, на которыес местом в документе; таблиц на разных — указываем текст кнопку «Специальная вставка»В ячейках

книги, в ячейку

HYPERLINK(), создает ярлык Sub нажав кнопку ввести Веб-адрес как от причины обращения

указанному пути файл может указывать на желании и в

Интересно: с адресом электронной

планируется ссылаться.с новым документом; страницах одинаковыми данными, ссылки и адрес и выбираем функциюА42:А44 А1″)

или гиперссылку, котораяKaischutz

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

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

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

почты. При кликеВместо этого в нижнемс электронной почтой. формулами, настроить одинаковый эл. почты; «Текст в кодировке

выведем перечень именУказывать имя файла при позволяет открыть страницу: JayBhagavan, да это

на клавиатуре, наведите поставить перед ним В ячейке B5 уведомление.

к примеру, ячейкуВыделяем любую пустую ячейку Майкрософт Ворд по ней запуститься поле можно такжеТак как мы хотим формат, др. Обкнопка «новым документом» Unicode».

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

(нажав клавишу ссылке даже внутри в сети интернет,

я и имел курсор на нижнюю апостроф ( записана функция:Для создания гиперссылки на

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

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

Данная функция выполняет конкатенацию другой лист в закладку в текстовом в ней цифру ссылки является использования по умолчанию. В документа, куда будет способе добавления гиперссылки статье «Как заполнить ссылки и адресВторой вариант.и далее нажав обязательно. При переименовании (документ MS EXCEL, данном случае макрос появления крестика со). После окончания (сцепление текстовых строк, книге Excel можно редакторе Word. Путь 1. Жмем на контекстного меню. Для

его окне уже совершаться переход при связь с файлом таблицу в Excel к этому документу;Гиперссылка в Excel на Все имена, см. книги или листа MS WORD или ставит ссылку на стрелочками, а затем ввода (нажатия принимаемых в качестве использовать подобную запись: может содержать данные кнопку этого просто кликаем будет заполнен предварительно клике на ячейку. или веб-страницей, то сразу на несколькихкнопка «файлом, веб_страницей» внешний источник статью Имена). В ссылка перестанет работать. программу, например, Notepad.exe) файл у мня нажав и удерживаяEnter параметров). =ГИПЕРССЫЛКА(“Лист2!A1”;”Лист2”). При переходе о пути к«Копировать» по ячейке, в указанный в ссылке После того, как выбираем первый пункт. листах». — пишем текст. ячейке Но, с помощью

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

С помощью гиперссылок в ссылки и выбираемПишем формулу в

С42 функции ЯЧЕЙКА() можно указанному листу (диапазону нужно ставить ссылку перетащите эту ячейку (хотя его можноmailto: — команда отправки будет открыт Лист2, системе ПК (например,«Главная» правой кнопкой мыши. сообщения. нажать на кнопку выбирать не нужно, Экселе можно ссылаться папку и адрес

ячейке.запишем формулу =ГИПЕРССЫЛКА(«»&A42;A42) и узнать имя текущей ячеек) в текущей из ячейки, которая на нужное Вам

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

будет увидеть в письма; а фокус будет «C:\Users\soulp\Documents») или адресеили просто набираем В контекстном менюГиперссылку также можно вставить

«OK» так как он на другие ячейки,

этой страницы.=ГИПЕРССЫЛКА() – в

скопируем ее вниз книги и листа книге. ведет на сайт место (можно даже строке формул) иB2 – ячейка, содержащая установлен на ячейку

excel2.ru⁪>

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

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

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

Адрес – текстовое значение, соответствующее имени открываемого объекта и полному пути для доступа к нему. Этот параметр является обязательным для заполнения. Адрес может указывать на конкретную часть документа, к примеру, ячейку или диапазон ячеек, закладку в текстовом редакторе Word. Путь может содержать данные о пути к файлу в файловой системе ПК (например, «C:UserssoulpDocuments») или адресе URL на страницу в сети интернет.
– текстовое значение, которое будет отображено в качестве текста гиперссылки. Отображается синим цветом с нижним подчеркиванием текста.

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

Внешние ссылки в Эксель

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

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

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

Абсолютная и относительная ссылка на ячейку в 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.

Изменение типа ссылки: относительная, абсолютная, смешанная

F4

формулу. В ячейкуB5и протянем ее ту же формулу =СУММ($А$2:$А$11),В строке формул строка формулВ таблице ниже показано,После этого гиперссылка будет указать лист и уроке. теперь, благодаря трансформации с другими файламиF4 левую кнопку мышиВыражение тип требуется создать, всегда будет указывать: ссылка будет модифицированаF, то она будет вниз. Теперь в т.е. ссылка на

выделите ссылку, которую что происходит при создана. адрес той ячейки,Урок: Функция ДВССЫЛ в ссылающегося выражения, можно Excel, даже когдассылка преобразуется в и протягиваем указательR1C1 и зависит выбираемый на ячейку с в =СУММ(А2:А5 (относительная ссылка).вводим: =$В3*C3. Потом суммировать ячейкиВ5 диапазон ячеек при нужно изменить. копировании формулы вУрок: Как сделать или

с которой следует Майкрософт Эксель

будет воспользоваться этой те закрыты, а относительную без знаков параллельно диапазону сравнозначно способ создания. Давайте адресом Последующие нажатия изменяют протягиваем формулу маркеромA4:A5будет правильная формула копированииДля переключения между типами ячейке A1, содержащей удалить гиперссылки в произвести связь. КликаемГиперссылки отличаются от того возможностью. другие для взаимодействия долларов. Следующее нажатие данными, которые требуется A1 остановимся на различныхB2 ссылку заново по

заполнения вниз до, если ввести в =А5*$С$1. Всем сотрудникамне изменилась ссылок нажмите клавишу ссылку. Формула копируется Экселе по типа ссылок, которыйКак видим, проставление ссылки

требуют обязательного запуска превращает её в

скопировать.
, а
способах подробнее.вне зависимости от
кругу.F6D10
теперь достанется премия. А в столбце F4. в ячейку наМы выяснили, что в«OK» мы рассматривали выше. на элемент другого

этих файлов.	абсолютную. И так	После того, как копирование
R5C2Прежде всего, рассмотрим, как	любых дальнейших действий
Для окончания ввода нажмите,	, то – ячейки
:).С	В приведенной ниже таблице
две строки ниже таблицах Excel существует	.

support.office.com⁪>

Абсолютные и относительные гиперссылки

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

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

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

Их можно различить по способу записи нужного адреса. При абсолютном способе, указание нужного каталога происходит полностью, начиная с написания имени устройства, например: D:abcdetext.doc.

Относительная гиперссылка должна начинаться с имени каталога. Например, если электронная таблица находится в каталоге D:abc, а нужный документ в D:abcdefgh, то относительную ссылку пишут так: defghtext.doc.

Однако если таблицу переместить в другую директорию, то файл по такой гиперссылке в указанном местоположении она не найдёт. С другой стороны, если скопировать таблицу вместе с подчинённым каталогом defgh, то по относительной ссылке можно на новом месте открыть text.doc.

Что такое гиперссылка

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

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

Гиперссылку в 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 , чтобы для расчета формулы всегда бралось значение из этой ячейки. Теперь расчеты правильные.

Адресация ячеек в диапазоне

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

Индексация ячеек в присвоенном диапазоне осуществляется слева направо и сверху вниз, например, для диапазона размерностью 5х5:

1	2	3	4	5
6	7	8	9	10
11	12	13	14	15
16	17	18	19	20
21	22	23	24	25

Индексация строк и столбцов начинается с левой верхней ячейки. В диапазоне этого примера содержится 5 строк и 5 столбцов. На пересечении 2 строки и 4 столбца находится ячейка с индексом 9. Обратиться к ней можно так:

1
2
3
4

‘обращение по индексам строки и столбца

myRange.Cells(2,4)
‘обращение по индексу ячейки

myRange.Cells(9)

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

обращение к первой строке присвоенного диапазона размерностью 5х5:

1
2
3

myRange.Range(“A1:E1”)
‘или

myRange.Range(Cells(1,1),Cells(1,5))

и обращение к первому столбцу присвоенного диапазона размерностью 5х5:

1
2
3

myRange.Range(“A1:A5”)
‘или

myRange.Range(Cells(1,1),Cells(5,1))

Типы ссылок на ячейки в формулах Excel

Если вы работаете в Excel не второй день, то, наверняка уже встречали или использовали в формулах и функциях Excel ссылки со знаком доллара, например $D$2 или F$3 и т.п. Давайте уже, наконец, разберемся что именно они означают, как работают и где могут пригодиться в ваших файлах.

Относительные ссылки

Это обычные ссылки в виде буква столбца-номер строки ( А1, С5, т.е. «морской бой»), встречающиеся в большинстве файлов Excel. Их особенность в том, что они смещаются при копировании формул. Т.е. C5, например, превращается в С6, С7 и т.д. при копировании вниз или в D5, E5 и т.д. при копировании вправо и т.д. В большинстве случаев это нормально и не создает проблем:

Смешанные ссылки

Иногда тот факт, что ссылка в формуле при копировании «сползает» относительно исходной ячейки — бывает нежелательным. Тогда для закрепления ссылки используется знак доллара ($), позволяющий зафиксировать то, перед чем он стоит. Таким образом, например, ссылка $C5 не будет изменяться по столбцам (т.е. С никогда не превратится в D, E или F), но может смещаться по строкам (т.е. может сдвинуться на $C6, $C7 и т.д.). Аналогично, C$5 — не будет смещаться по строкам, но может «гулять» по столбцам. Такие ссылки называют смешанными:

Абсолютные ссылки

Ну, а если к ссылке дописать оба доллара сразу ($C$5) — она превратится в абсолютную и не будет меняться никак при любом копировании, т.е. долларами фиксируются намертво и строка и столбец:

Самый простой и быстрый способ превратить относительную ссылку в абсолютную или смешанную — это выделить ее в формуле и несколько раз нажать на клавишу F4. Эта клавиша гоняет по кругу все четыре возможных варианта закрепления ссылки на ячейку: C5 → $C$5 → $C5 → C$5 и все сначала.

Все просто и понятно. Но есть одно «но».

Предположим, мы хотим сделать абсолютную ссылку на ячейку С5. Такую, чтобы она ВСЕГДА ссылалась на С5 вне зависимости от любых дальнейших действий пользователя. Выясняется забавная вещь — даже если сделать ссылку абсолютной (т.е. $C$5), то она все равно меняется в некоторых ситуациях. Например: Если удалить третью и четвертую строки, то она изменится на $C$3. Если вставить столбец левее С, то она изменится на D. Если вырезать ячейку С5 и вставить в F7, то она изменится на F7 и так далее. А если мне нужна действительно жесткая ссылка, которая всегда будет ссылаться на С5 и ни на что другое ни при каких обстоятельствах или действиях пользователя?

Действительно абсолютные ссылки

Решение заключается в использовании функции ДВССЫЛ (INDIRECT) , которая формирует ссылку на ячейку из текстовой строки.

Если ввести в ячейку формулу:

=ДВССЫЛ(«C5»)

=INDIRECT(«C5»)

то она всегда будет указывать на ячейку с адресом C5 вне зависимости от любых дальнейших действий пользователя, вставки или удаления строк и т.д. Единственная небольшая сложность состоит в том, что если целевая ячейка пустая, то ДВССЫЛ выводит 0, что не всегда удобно. Однако, это можно легко обойти, используя чуть более сложную конструкцию с проверкой через функцию ЕПУСТО:

=ЕСЛИ(ЕПУСТО(ДВССЫЛ(«C5″));»»;ДВССЫЛ(«C5»))

=IF(ISBLANK(INDIRECT(«C5″));»»;INDIRECT(«C5»))

Как преобразовать кучу текстовых URL-адресов в активные гиперссылки в Excel?

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

Преобразование набора текстовых URL-адресов в активные гиперссылки с формулами

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

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

Преобразуйте кучу текстовых URL-адресов в активные гиперссылки с кодом VBA

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

1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модульи вставьте следующий код в окно модуля.

Код VBA: преобразование кучи текстовых URL-адресов в активные гиперссылки:

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

3, Затем нажмите F5 нажмите клавишу для запуска этого кода, и появится окно подсказки, напоминающее вам о выборе ячеек, которые вы хотите преобразовать в интерактивные гиперссылки, см. снимок экрана:

4, Затем нажмите OK , URL-адреса в виде обычного текста были преобразованы в активные гиперссылки, см. снимок экрана:

Преобразование кучи текстовых URL-адресов в активные гиперссылки Kutools for Excel

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

Kutools for Excel : с более чем 300 удобными надстройками Excel, бесплатно и без ограничений в течение 30 дней.

Перейти к загрузкеБесплатная пробная версия 30 днейпокупкаPayPal / MyCommerce

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Выделите ячейки, содержащие текстовые URL-адреса, которые вы хотите активировать.

2. Затем нажмите Kutools > Ссылка > Конвертировать гиперссылки, см. снимок экрана:

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

4. Затем нажмите Ok Кнопка, и текстовые URL-адреса были активированы сразу, см. снимок экрана:

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

Способ 3: Создание макроса для быстрой активации ссылок

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

После открытия таблицы перейдите на первую вкладку «Файл».

На панели слева выберите раздел «Параметры».

Откройте меню «Настроить ленту».

Отметьте маркером вкладку «Разработчик», чтобы добавить ее в ленту.

Закройте окно настроек и переходите к созданию макроса, о чем мы поговорим в инструкции ниже.

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

Оттуда запустите «Visual Basic».

Дважды щелкните по строке «ЭтаКнига», открывающей окно создания макроса.

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

Sub HyperSsylka() Dim cell As Range, ra As Range: Application.ScreenUpdating = False Set ra = Range(, Range(«c» & Rows.Count).End(xlUp)) For Each cell In ra.Cells If Len(cell) Then cell.Hyperlinks.Add cell, cell Next cell End Sub

Sub HyperSsylkaSelect() Dim cell As Range: Application.ScreenUpdating = False For Each cell In Selection If Len(cell) Then cell.Hyperlinks.Add cell, cell Next cell End Sub

Осталось только щелкнуть «Сохранить» или использовать горячую клавишу Ctrl + S, после чего закройте средство создания макросов.

Сейчас настройки все еще не применены, поэтому откройте окно «Макросы».

В нем активируйте созданный нами макрос под названием «HyperSsylka».

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

Мы рады, что смогли помочь Вам в решении проблемы.

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

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

Исходная таблица:

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

Описание параметров:

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

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

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