Как извлечь линк (ссылку) из гиперссылки Excel. Как сделать ссылку в экселе на сайт


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

Как быстро найти файл в папке? Excel - самый популярный табличный редактор

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

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

Гиперссылка (hyperlink) — можно сказать второй слой текста, в котором заключена ссылка на другой объект (файл, сайт, программу и т.п.) и при нажатии на нее вы переходите в нужное место.

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

Вид гиперссылки в Excel

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

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

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

 

Нажав на эту кнопку у нас появиться окно «Вставка гиперссылки». В данном окне вам будут предложены варианты создания гиперссылок. У вас есть возможность создать ссылку, в том же документе, на другом листе, нажав в пункте «Связать с..» функцию «Место в документе». Или просто вставьте имеющийся адрес в раздел Адрес.

Гиперссылка 2

Гиперссылка 2

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

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

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

Этому посвящена отдельная статья.

Создание оглавления c гиперссылками в Excel

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

Так же можно воспользоваться макросом:

Sub SpisokListov()    Dim sheet As Worksheet    Dim cell As Range    With ActiveWorkbook       For Each sheet In ActiveWorkbook.Worksheets          Set cell = Worksheets(1).Cells(sheet.Index, 1)          .Worksheets(1).Hyperlinks.Add anchor:=cell, Address:="", SubAddress:="'" & sheet.Name & "'" & "!A1"          cell.Formula = sheet.Name       Next    End With End Sub

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

 

Поделитесь нашей статьей в ваших соцсетях:

Похожие статьи

(Visited 1 011 times, 4 visits today)

Как быстро найти файл в папке? Excel - самый популярный табличный редактор

excelworks.ru

Функция ГИПЕРССЫЛКА() в MS EXCEL. Примеры и методы

Функция ГИПЕРССЫЛКА(), английский вариант 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

=ГИПЕРССЫЛКА("c:\windows\notepad.exe";"Открыть 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)

excel2.ru

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

Привет, корешки. Допустим, у нас есть куча урлов в Excel. И нам надо преобразовать их в ссылки, чтобы прокликать каждую. Вот наша куча урлов:

urlyurly

Нам нужно выделить ячейку например рядом с первым урлом, перейти во вкладку «Формулы» — «Ссылки и массивы» — «ГИПЕРССЫЛКА».

giperssylkagiperssylka

Затем щелкаем на ячейку с первым урлом и жмем ОК.

giperssylka-2giperssylka-2

Ну а дальше нам остается только протянуть ячейки с формулой.

giperssylka-3giperssylka-3

Все, корешки, теперь у нас есть список ссылок.

znet.ru

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

Гиперссылки в Microsoft Excel

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

Интересно: Создание гиперссылок в Microsoft Word

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

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

Способ 1: вставка безанкорных гиперссылок

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

Вписываем ссылку в любую область листа.

Ссылка на веб-сайт в Microsoft Excel

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

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

Гиперссылка на электронную почту в Microsoft Excel

Способ 2: связь с файлом или веб-страницей через контекстное меню

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

  1. Выделяем ячейку, в которую собираемся вставить связь. Кликаем правой кнопкой мыши по ней. Открывается контекстное меню. В нём выбираем пункт «Гиперссылка…».
  2. Переход к созданию гиперссылки в Microsoft Excel

  3. Сразу после этого открывается окно вставки. В левой стороне окна расположены кнопки, нажав на одну из которых пользователь должен указать, с объектом какого типа хочет связать ячейку:
    • с внешним файлом или веб-страницей;
    • с местом в документе;
    • с новым документом;
    • с электронной почтой.

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

  4. Связь с файлом или веб-страницей в Microsoft Excel

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

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

    Выбор файла в Microsoft Excel

    Внимание! Для того, чтобы иметь возможность связать ячейку с файлом с любым расширением в окне поиска, нужно переставить переключатель типов файлов в положение «Все файлы».

  8. После этого координаты указанного файла попадают в поле «Адрес» окна вставки гиперссылки. Просто жмем на кнопку «OK».

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

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

Если вы хотите вставить ссылку на веб-ресурс, то в поле «Адрес» нужно вручную вписать url или скопировать его туда. Затем следует нажать на кнопку «OK».

Вставка ссылки на веб-страницу в Microsoft Excel

Способ 3: связь с местом в документе

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

  1. После того, как выделена нужная ячейка и вызвано через контекстное меню окно вставки гиперссылки, переключаем кнопку в левой части окна в позицию «Связать с местом в документе».
  2. Связь с местом в документе в Microsoft Excel

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

    Ссылка на другую ячейку в Microsoft Excel

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

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

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

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

Ещё одним вариантом является гиперссылка на новый документ.

  1. В окне «Вставка гиперссылки» выбираем пункт «Связать с новым документом».
  2. Связать с новым документом в Microsoft Excel

  3. В центральной части окна в поле «Имя нового документа» следует указать, как будет называться создаваемая книга.
  4. Наименование новой книги в Microsoft Excel

  5. По умолчанию этот файл будет размещаться в той же директории, что и текущая книга. Если вы хотите сменить место расположения, нужно нажать на кнопку «Изменить…».
  6. Переход к выбору места размещения документа в Microsoft Excel

  7. После этого, открывается стандартное окно создания документа. Вам нужно будет выбрать папку его размещения и формат. После этого нажмите на кнопку «OK».
  8. Окно создания документа в Microsoft Excel

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

Создания нового документа в Microsoft Excel

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

Способ 5: связь с электронной почтой

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

  1. В окне «Вставка гиперссылки» кликаем по кнопке «Связать с электронной почтой».
  2. В поле «Адрес электронной почты» вписываем e-mail, с которым хотим связать ячейку. В поле «Тема» можно написать тему письма. После того, как настройки выполнены, жмем на кнопку «OK».

Настройка связи с электронной почтой в Microsoft Excel

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

Способ 6: вставка гиперссылки через кнопку на ленте

Гиперссылку также можно вставить через специальную кнопку на ленте.

  1. Переходим во вкладку «Вставка». Жмем на кнопку «Гиперссылка», расположенную на ленте в блоке инструментов «Ссылки».
  2. Гиперссылка на ленте в Microsoft Excel

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

Окно вставка гиперссылки в Microsoft Excel

Способ 7: функция ГИПЕРССЫЛКА

Кроме того, гиперссылку можно создать с помощью специальной функции.

  1. Выделяем ячейку, в которую будет вставлена ссылка. Кликаем на кнопку «Вставить функцию».
  2. Переход в мастер функций в Microsoft Excel

  3. В открывшемся окне Мастера функций ищем наименование «ГИПЕРССЫЛКА». После того, как запись найдена, выделяем её и жмем на кнопку «OK».
  4. Мастер функций в Microsoft Excel

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

Аргументы функции в Microsoft Excel

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

Ссылка в Microsoft Excel

Урок: Мастер функций в Excel

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

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

Интересно: Как удалить гиперссылки в Майкрософт Ворд

Способ 1: удаление с помощью контекстного меню

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

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

Способ 2: удаление функции ГИПЕРССЫЛКА

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

Удаление ссылки в Microsoft Excel

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

Ссылка удалена в Microsoft Excel

Способ 3: массовое удаление гиперссылок (версия Excel 2010 и выше)

Но что делать, если в документе очень много гиперссылок, ведь ручное удаление займет значительное количество времени? В версии Excel 2010 и выше есть специальная функция, с помощью которой вы сможете удалить сразу несколько связей в ячейках.

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

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

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

Гиперссылки удалены в Microsoft Excel

Если вы хотите произвести удаление во всем документе, то сначала наберите на клавиатуре сочетание клавиш Ctrl+A. Этим вы выделите весь лист. Затем, кликнув правой кнопкой мыши, вызывайте контекстное меню. В нём выберите пункт «Удалить гиперссылки».

Удаление всех гиперссылок на листе в Microsoft Excel

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

Способ 4: массовое удаление гиперссылок (версии ранее Excel 2010)

Что же делать, если у вас на компьютере установлена версия ранее Excel 2010? Неужели все ссылки придется удалять вручную? В данном случае тоже имеется выход, хоть он и несколько сложнее, чем процедура, описанная в предыдущем способе. Кстати, этот же вариант можно применять при желании и в более поздних версиях.

  1. Выделяем любую пустую ячейку на листе. Ставим в ней цифру 1. Жмем на кнопку «Копировать» во вкладке «Главная» или просто набираем на клавиатуре сочетание клавиш Ctrl+C.
  2. Копирование в Microsoft Excel

  3. Выделяем ячейки, в которых расположены гиперссылки. Если вы хотите выбрать весь столбец, то кликните по его наименованию на горизонтальной панели. Если нужно выделить весь лист, наберите сочетание клавиш Ctrl+A. Кликните по выделенному элементу правой кнопкой мыши. В контекстном меню дважды перейдите по пункту «Специальная вставка…».
  4. Переход в окно специальной вставки в Microsoft Excel

  5. Открывается окно специальной вставки. В блоке настроек «Операция» ставим переключатель в позицию «Умножить». Жмем на кнопку «OK».

Специальная вставка в Microsoft Excel

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

Гиперссылки удалены в программе Microsoft Excel

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

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

Помогла ли вам эта статья?

Да Нет

lumpics.ru

Как получить ссылку из ячейки с гиперссылкой в Excel? Вытаскиваем адрес ссылки формулой

Как убрать пробелы в Excel? Как посчитать проценты в Excel? Расчет процента от числа. Разница чисел в процентах

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

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

получить ссылку из ячейки

получить ссылку из ячейки

Разберемся как вытащить гиперссылку из такой ячейки

Как вывести адрес ссылки для одной ячейки?

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

Ссылка отдельно 2

Ссылка отдельно 2

В открывшемся окне вы можете скопировать ссылку из графы Адрес:

Ссылка отдельно 4

Ссылка отдельно 4

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

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

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

На днях у меня возникла задача, было нужно вытащить ссылки более чем из 10 тыс ячеек. Не делать же такое вручную. Я сначала поискал решение в интернете и нашел отличное вариант. Публикую для своих читателей решение с прекрасного сайта. Для применения способа запишите текст, приведенный ниже в окно модуля VBA (Ctrl+F11). Если не знаете о чем я веду речь, советую прочитать статью про макросы.

Текст создаваемой функции будет следующий:

Function Получить_Ссылку(ByVal rCell As Range) As String     Dim s As String     If rCell.Hyperlinks.Count = 0 Then         If Mid$(rCell.Formula, 2, 9) = "HYPERLINK" Then            Получить_Ссылку= Mid$(rCell.Formula, 13, InStr(13, rCell.Formula, Chr(34)) - 13)         Else            Получить_Ссылку = "В ячейке нет гиперссылки!"         End If     Else         s = rCell.Hyperlinks(1).SubAddress         If s <> "" Then s = "#" & rCell.Hyperlinks(1).SubAddress         Получить_Ссылку= rCell.Hyperlinks(rCell.Hyperlinks.Count).Address & s     End If End Function

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

Ссылка отдельно 5

Ссылка отдельно 5

Конечно, она будет доступна и в меню функций.

Введите адрес ячейки как аргумент этой функции и протяните формулу на нужное количество ячеек. Все!

Ссылка отдельно 6

Ссылка отдельно 6

Удобно, еще раз спасибо Excel-vba.ru за отличный сайт.

 

Поделитесь нашей статьей в ваших соцсетях:

Похожие статьи

(Visited 2 447 times, 18 visits today)

Как убрать пробелы в Excel? Как посчитать проценты в Excel? Расчет процента от числа. Разница чисел в процентах

excelworks.ru

Ссылки в Excel

Добрый день, уважаемые посетители statanaliz.info. Когда пользуешься различными функциями (формулами) Excel, то часто приходится обращать внимание на ссылки. Ссылки в Excel, как известно, бывают разными: относительными, абсолютными, внешними, в виде имен диапазонов и др. В этой статье я расскажу кое-что интересное про excelевские ссылки. Полезно должно быть не только новичкам.

Первым делом уясним, что такое относительные и абсолютные ссылки и зачем конструкторы Excel их придумали. Вопрос не праздный т.к. при работе с большими массивами данных формула прописывается обычно один раз, а затем «протягивается» (копируется) на другие ячейки (например, в итоговой строке или итоговом столбце таблицы). Корректность такого «протягивания» сильно зависит от типа ссылки. Здесь есть два варианта. Первый: диапазон, на который ведет ссылка, «перемещается» вслед за копируемой ячейкой, то есть остается на одном месте относительно ячейки с формулой. Второй: ссылка ведет на один и тот же адрес независимо от того, куда копируется формула. Недооценка важности вида ссылки приводит к ошибкам в расчетах и необходимости коррекции формул. Все это как минимум тормозит работу. Помните, что каждое лишнее действие вносит свой вклад в бесполезно потраченное время.

Рассмотрим простой пример. Нужно сложить два числа. Сделать это легко, прописав в свободной ячейке (например, внизу) знак «=» и затем через знак «+» сослаться на складываемые ячейки. Если чисел много, то суммировать лучше через функцию СУММ, указав сразу весь диапазон суммирования.

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

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

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

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

=A1,

где буква обозначает столбец, а число – строку.

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

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

Рассмотрим другой пример, когда нужно рассчитать долю каждого значения к итогу. Такой расчет весьма распространен. В том же АВС-анализе, например. Введем формулу для расчета доли первого числа.

Теперь попробуем «протянуть» формулу вниз для расчета остальных долей. Сумма долей должна получиться ровно 100%. Однако уже на втором значении видно: что-то пошло не так.

Числитель «поехал» вниз правильно, а вот знаменатель должен был остаться на месте. Чтобы все получилось правильно, нужно зафиксировать ссылку на итог, чтобы она не «ездила» вслед за формулой. Короче говоря, ссылку на итоговую сумму нужно сделать абсолютной, для чего перед названием строки и столбца ставится значок доллара $ (<Shift+4> в английской раскладке):

=$A$1

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

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

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

=A$1

=$A1

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

Как быстро установить символ доллара $

Если ставить значок доллара с клавиатуры классическим методом, то потребуется вначале перейти в английскую раскладку, а затем нажать <Shift+4>. Скажем прямо, это долго и неудобно. Гораздо быстрее войти в режим редактирования формул с помощью клавиши <F2> (если вы набираете формулу вручную, то уже находитесь в этом режиме), установить курсор на нужной ссылке и нажать <F4>. Ссылка начнет менять свой режим «абсолютности». После нажатия <F4> относительная ссылка станет абсолютной (и по строкам, и по столбцам). Если нажать <F4> еще раз, то абсолютной станет только строка, следующее нажатие сделает абсолютным только столбец. Очередное нажатие <F4> сделает ссылку снова относительной. И так по кругу. Снова абсолютная, только строка, только столбец, относительная и т.д. Последовательным нажатием <F4> останавливаетесь на нужном варианте.

Очень удобно, не нужно переключать раскладку и искать $. Эта же кнопка работает и в диалоговом окне «Вставки функции». Например, при написании формулы ВПР или СУММЕСЛИ нужно внимательно смотреть на ссылающиеся диапазоны, поэтому значки $ используются очень часто.Как я уже говорил выше, если диапазон, на который нужно сослаться находится в той же книге (файле Excel), пусть даже и на другом листе, то Excel по умолчанию устанавливает относительные ссылки (без значка «$»). А вот ссылки на другие книги Excel по умолчанию делает абсолютными (вставляет «$» где только можно). Но про другие листы и книги поговорим в следующем параграфе.

Ссылки на другие листы и книги

Excel умеет ссылаться не только в пределах одного листа, но и на другие листы и даже другие файлы Excel. Такой функционал значительно расширяет диапазон вычислений. К примеру, вам прислали по почте отчет о продажах. У вас есть своя база данных об остатках. Нужно на основе этих данных рассчитать оборачиваемость остатков. Первым делом данные нужно связать между собой, то есть данные из одного файла «подтянуть» к соответствующим позициям другого файла. Можно использовать функцию ВПР или СУММЕСЛИ со ссылкой на другой файл. Все работает исправно. Только в очередной раз обращаю внимание на тип ссылок, чтобы все получилось правильно.

Если в формулах есть ссылки на другие файлы, то следует обратить внимание на следующее. Во-первых, при открытии файла с внешними связями Excel назойливо напоминает об этом выскакивающим посреди экрана окошком. Это, конечно, помогает не забывать, что в файле есть внешние ссылки, но лучше, чтобы их вообще не было. Во-вторых, при изменении или удалении внешнего источника мгновенно меняются и результаты вычислений, что нужно далеко не всегда. В третьих, если возникнут ошибки в расчетах, то на обнаружение их источника может потребоваться дополнительное время, т.к. данные находятся в другом файле. В общем, сразу после вычислений с использованием данных из других книг внешние ссылки рекомендуется удалить: Данные → Подключения → Изменить связи → Разорвать связь.

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

Стиль ссылок

И еще кое-что про ссылки. Иногда они выглядят несколько непривычно. Это особый стиль ссылок, при котором адрес ячейки A1 имеет наименование R1C1 (первая строка (R1), первая колонка (C1)). Такой стиль ссылок иногда может быть полезен, но довольно редко. Поэтому неплохо уметь возвращать нормальный вид адреса ячейки. Для этого нужно выполнить следующие действия: Файл → Параметры → Формулы и далее нужно снять галочку с пункта «Стиль ссылки R1C1».

На этом я заканчиваю свой рассказ про типы ссылок в Excel.

Всего доброго и до новых встреч.

statanaliz.info

Как извлечь линк (ссылку) из гиперссылки Excel

Гиперссылки в ExcelЯ с столкнулась с тем, что нужно было извлечь ссылки со списка текста с гиперссылками в Excel. Решение я нашла в этой статье How to extract a URL from a hyperlink on Excel и перевела ее.

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

  • Открыть документ Excel.
  • Выделите столбик с гиперссылками
  • Перейти в режим VBA (Alt+F11)
  • Вставить новый модуль (Insert > Module)
  • Скопировать код (ниже) и вставить в открывшееся окно

    Sub ExtractHL() Dim HL As Hyperlink For Each HL In ActiveSheet.Hyperlinks HL.Range.Offset(0, 1).Value = HL.Address Next End Sub

    Sub ExtractHL()

    Dim HL As Hyperlink

    For Each HL In ActiveSheet.Hyperlinks

    HL.Range.Offset(0, 1).Value = HL.Address

    Next

    End Sub

  • Нажать F5, чтобы запустить макрос
  • Выйти из VBA (Alt+Q)

После это в столбике справа должны появится отдельные ссылки.

Option 2: If you plan to add more hyperlinks to the spreadsheet and need to store the formula on the sheet Open up a new workbook. Get into VBA (Press Alt+F11) Insert a new module (Insert > Module) Copy and Paste the Excel user defined function below Get out of VBA (Press Alt+Q) Use this syntax for this custom Excel function: =GetURL(cell,[default_value])

Function GetURL(cell As range, _ Optional default_value As Variant) 'Lists the Hyperlink Address for a Given Cell 'If cell does not contain a hyperlink, return default_value If (cell.range("A1").Hyperlinks.Count <> 1) Then GetURL = default_value Else GetURL = cell.range("A1").Hyperlinks(1).Address & "#" & cell.range("A1").Hyperlinks(1).SubAddress End If End Function

Function GetURL(cell As range, _

Optional default_value As Variant)

'Lists the Hyperlink Address for a Given Cell

'If cell does not contain a hyperlink, return default_value

If (cell.range("A1").Hyperlinks.Count <> 1) Then

GetURL = default_value

Else

GetURL = cell.range("A1").Hyperlinks(1).Address & "#" & cell.range("A1").Hyperlinks(1).SubAddress

End If

End Function

Статьи по теме:

Закладка.

stepfor.top