Excel ссылка отображается как текст
Это простая, но весьма полезная функция, позволяющая создать живую ссылку на:
- любую ячейку на любом листе в книге
- именованный диапазон
- фрагмент умной таблицы
- внешний файл Excel на диске или на сервере в сети
- веб-страницу
Кроме того, эта функция умеет создавать заполненную форму письма email, что сильно помогает при рассылке типовых оповещений.
Синтаксис функции прост:
=ГИПЕРССЫЛКА( Адрес ; Текст )
- Текст - это видимый текст гиперссылки в ячейке (обычно выделенный синим цветом и подчеркиванием, как все гиперссылки), по которому щелкает пользователь. Может быть любым, это роли не играет.
- Адрес - место, куда мы хотим сослаться (и это самое интересное).
Давайте рассмотрим все варианты подробнее.
Ссылка на веб-страницу
Это самый простой и очевидный вариант применения нашей функции ГИПЕРССЫЛКА (HYPERLINK) . Первым аргументом здесь будет полная ссылка на веб-страницу (URL):
Обратите внимание, что адрес должен быть полным, т.е. обязательно начинаться с протокола (обычно "http"). При щелчке по созданной ссылке заданный сайт откроется в браузере по умолчанию.
Ссылка на ячейку внутри книги
Если нужна ссылка на ячейку или диапазон, находящийся внутри текущей книги, то адрес нужно будет указать следующим образом:
Если имя листа содержит пробелы, то его обязательно заключать в апострофы - иначе ссылка не сработает:
Ссылка на ячейку во внешней книге
Можно легко создать ссылку на ячейку во внешней книге. Тогда в качестве первого аргумента нужен будет полный путь к файлу (с расширением!), имя листа и адрес ячейки:
Аналогично внутренней ссылке, если имя листа содержит пробелы, то его необходимо дополнительно заключить в апострофы.
Ссылка на файл на сервере
Также поддерживаются сетевые ссылки, если файл сохранен, например, на корпоративном сервере в общей папке - в этом случае используется путь в формате UNC, начинающийся с двух обратных дробей:
Ссылка на именованный диапазон
Если вы создали именованный диапазон (например, через Формулы - Диспетчер имен - Создать), то можно спокойно использовать это имя в ссылке:
Если нужно сослаться на именованный диапазон во внешнем файле, то потребуется уточнить его имя и путь к нему, как в предыдущем пункте. Имя листа указывать уже не нужно:
Ссылка на умную таблицу или ее элементы
Если вы знакомы с умными таблицами, то знаете какая это полезная штука. При желании, можно легко создать с помощью функции ГИПЕРССЫЛКА (HYPERLINK) ссылку на любой нужный нам фрагмент умной таблицы или на нее целиком. Для этого в первом аргументе нужно указать имя таблицы (например Таблица1) и кодовое обозначение ее элемента:
Поддерживаются следующие обозначения:
Формирование заполненной формы письма email
Это относительно экзотический вариант применения функции гиперссылка, позволяющий создавать заполненный бланк электронного письма:
При щелчке по такой ссылке мы увидим вот такую красоту:
Можно указывать несколько адресатов, получателей обычной и скрытой копий, форматировать текст письма и т.д. Очень удобно для служебных уведомлений и рассылок. Единственное, что нельзя таким образом сделать - прикрепить вложения. Подробнее про все возможности и параметры такого способа рассказано в отдельной статье.
Использование нестандартных символов
Иногда можно сочетать функцию ГИПЕРССЫЛКА с функцией вывода нестандартных знаков СИМВОЛ (CHAR), которую мы уже разбирали. Это позволяет заменить простой текст ссылки на что-то более симпатичное:
Здравствуйте. нередко использую функцию "ГИПЕРССЫЛКА", для правильной работы которой необходимо что бы адрес ячейки (диапазона) был задан текстом. Обычно использую для синтеза ссылки функции ЯЧЕЙКА("адрес";AH370), если адрес только на одну ячейку, либо АДРЕС()&":"&АДРЕС(), если надо сослаться на диапазон. Так как не редко использую с функцией АДРЕС функции ПОИСКПОЗ, ИНДЕКС, что бы ссылка могла смещаться, конечная функция получается громоздкой.
И вот недавно открыл для себя ещё одну полезную функцию СМЕЩ, которую куда проще писать и использовать в функциях СУММ и СЧЁТЕСЛИ, но при попытке использовать её в ГИППЕРСЫЛКЕ возникла проблема. Так как она сама по себе создает ссылку, то для ГИП-КИ её нужно превратить в текст. Для этого использовал функцию ЯЧЕЙКА("адрес";СМЕЩ(a1;;;;7)), но получал адрес лишь первой ячейки диапазона.
Есть ли альтернатива функции ЯЧЕЙКА которая будет целиком преобразовывать адрес диапазона в текст?
Здравствуйте. нередко использую функцию "ГИПЕРССЫЛКА", для правильной работы которой необходимо что бы адрес ячейки (диапазона) был задан текстом. Обычно использую для синтеза ссылки функции ЯЧЕЙКА("адрес";AH370), если адрес только на одну ячейку, либо АДРЕС()&":"&АДРЕС(), если надо сослаться на диапазон. Так как не редко использую с функцией АДРЕС функции ПОИСКПОЗ, ИНДЕКС, что бы ссылка могла смещаться, конечная функция получается громоздкой.
И вот недавно открыл для себя ещё одну полезную функцию СМЕЩ, которую куда проще писать и использовать в функциях СУММ и СЧЁТЕСЛИ, но при попытке использовать её в ГИППЕРСЫЛКЕ возникла проблема. Так как она сама по себе создает ссылку, то для ГИП-КИ её нужно превратить в текст. Для этого использовал функцию ЯЧЕЙКА("адрес";СМЕЩ(a1;;;;7)), но получал адрес лишь первой ячейки диапазона.
Есть ли альтернатива функции ЯЧЕЙКА которая будет целиком преобразовывать адрес диапазона в текст?
И вот недавно открыл для себя ещё одну полезную функцию СМЕЩ, которую куда проще писать и использовать в функциях СУММ и СЧЁТЕСЛИ, но при попытке использовать её в ГИППЕРСЫЛКЕ возникла проблема. Так как она сама по себе создает ссылку, то для ГИП-КИ её нужно превратить в текст. Для этого использовал функцию ЯЧЕЙКА("адрес";СМЕЩ(a1;;;;7)), но получал адрес лишь первой ячейки диапазона.
Есть ли альтернатива функции ЯЧЕЙКА которая будет целиком преобразовывать адрес диапазона в текст?
Gustav, Попытался сделать ваш вариант, но возникло непреодолимое препятствие в моём Excel 2010. Там такой функции нет(
Gustav, Попытался сделать ваш вариант, но возникло непреодолимое препятствие в моём Excel 2010. Там такой функции нет( ZetMenChavo
ZetMenChavo, Как OFFSET так и CELL летучие и их лучше не применять, не смотря на компактность записи, если можно обойтись без них.
Но также вы можете использовать стиль R1C1. Комбинация R1C1:R1C1 даст вам диапазон. вставить между номера строк и столбцов не проблема. а будет короче чем с адрес, но у Address есть оно полезное свойство использовать имя листа и она сама добавляет апострофа по надобности, если в имени есть пробелы.
ZetMenChavo, Как OFFSET так и CELL летучие и их лучше не применять, не смотря на компактность записи, если можно обойтись без них.
Но также вы можете использовать стиль R1C1. Комбинация R1C1:R1C1 даст вам диапазон. вставить между номера строк и столбцов не проблема. а будет короче чем с адрес, но у Address есть оно полезное свойство использовать имя листа и она сама добавляет апострофа по надобности, если в имени есть пробелы. bmv98rus
Но также вы можете использовать стиль R1C1. Комбинация R1C1:R1C1 даст вам диапазон. вставить между номера строк и столбцов не проблема. а будет короче чем с адрес, но у Address есть оно полезное свойство использовать имя листа и она сама добавляет апострофа по надобности, если в имени есть пробелы. Автор - bmv98rus
Дата добавления - 20.12.2020 в 08:43
Есть такая задачка: в таблице Excel ведется регистрация приказов. Кроме обычных реквизитов дата/заголовок и т.д. в таблицу включаются еще и гиперссылки на вордовский файл с приказом и на файл со сканом (файлы лежат на сервере). При этом у каждой из гиперссылок текст, видимый в ячейке - это номер приказа, например, за текстом "№ 256-орг" скрывается адрес "M:\Common\ORD\Приказы\2014\256_орг_2014 0726.docx".
Вопрос: как сделать так, чтобы текст массово заменился на адрес? Можно в столбце рядом, можно в той же ячейке, главное, чтобы в результате появились ячейки с гиперссылками а-ля "M:\Common\ORD\Приказы\2014\256_орг_2014 0726.docx".
База приказов в Excel'е ведется с 2008 года, каждый год образуется до 500 приказов, руками перебивать каждую гиперссылку очень долго и муторно, поэтому очень нужна помощь специалистов!
Есть такая задачка: в таблице Excel ведется регистрация приказов. Кроме обычных реквизитов дата/заголовок и т.д. в таблицу включаются еще и гиперссылки на вордовский файл с приказом и на файл со сканом (файлы лежат на сервере). При этом у каждой из гиперссылок текст, видимый в ячейке - это номер приказа, например, за текстом "№ 256-орг" скрывается адрес "M:\Common\ORD\Приказы\2014\256_орг_2014 0726.docx".
Вопрос: как сделать так, чтобы текст массово заменился на адрес? Можно в столбце рядом, можно в той же ячейке, главное, чтобы в результате появились ячейки с гиперссылками а-ля "M:\Common\ORD\Приказы\2014\256_орг_2014 0726.docx".
База приказов в Excel'е ведется с 2008 года, каждый год образуется до 500 приказов, руками перебивать каждую гиперссылку очень долго и муторно, поэтому очень нужна помощь специалистов! Clervo
Есть такая задачка: в таблице Excel ведется регистрация приказов. Кроме обычных реквизитов дата/заголовок и т.д. в таблицу включаются еще и гиперссылки на вордовский файл с приказом и на файл со сканом (файлы лежат на сервере). При этом у каждой из гиперссылок текст, видимый в ячейке - это номер приказа, например, за текстом "№ 256-орг" скрывается адрес "M:\Common\ORD\Приказы\2014\256_орг_2014 0726.docx".
Вопрос: как сделать так, чтобы текст массово заменился на адрес? Можно в столбце рядом, можно в той же ячейке, главное, чтобы в результате появились ячейки с гиперссылками а-ля "M:\Common\ORD\Приказы\2014\256_орг_2014 0726.docx".
База приказов в Excel'е ведется с 2008 года, каждый год образуется до 500 приказов, руками перебивать каждую гиперссылку очень долго и муторно, поэтому очень нужна помощь специалистов! Автор - Clervo
Дата добавления - 10.08.2014 в 18:16
Похоже, я промахнулась с подфорумом. Прошу модераторов перенести тему на правильное место: в "Вопросы по Excel". Извините.
[moder]Тему перенес (хотя предположу, что нужно было перенести в ветку макросов). А вот Правила форума все-таки прочитайте. В частности, пункт про приложение примера в Excel.
ГС могут быть 2-х видов, какой у Вас в файле - без примера не ясно.
Похоже, я промахнулась с подфорумом. Прошу модераторов перенести тему на правильное место: в "Вопросы по Excel". Извините.
[moder]Тему перенес (хотя предположу, что нужно было перенести в ветку макросов). А вот Правила форума все-таки прочитайте. В частности, пункт про приложение примера в Excel.
ГС могут быть 2-х видов, какой у Вас в файле - без примера не ясно. Clervo
ГС могут быть 2-х видов, какой у Вас в файле - без примера не ясно. Автор - Clervo
Дата добавления - 10.08.2014 в 18:20
У функции ГИПЕРССЫЛКА два параметра: первый - адрес ссылки (путь к файлу), второй - отображаемый текст. Если второй параметр не указан, то отбражается адрес (путь к файлу).
У функции ГИПЕРССЫЛКА два параметра: первый - адрес ссылки (путь к файлу), второй - отображаемый текст. Если второй параметр не указан, то отбражается адрес (путь к файлу). Gustav
У функции ГИПЕРССЫЛКА два параметра: первый - адрес ссылки (путь к файлу), второй - отображаемый текст. Если второй параметр не указан, то отбражается адрес (путь к файлу). Автор - Gustav
Дата добавления - 10.08.2014 в 19:02
Файл прикладываю. Но при пересылке с работы домой гиперы поменялись и сейчас они содержат пути на мой диск С, хотя файлы там никогда не лежали.
Гиперы добавляются тремя людьми. За двоих могу точно сказать, что ссылки устанавливаются через контекстное меню. Версии Exсel у операторов разные - 2007 и 2010.
Файл прикладываю. Но при пересылке с работы домой гиперы поменялись и сейчас они содержат пути на мой диск С, хотя файлы там никогда не лежали.
Гиперы добавляются тремя людьми. За двоих могу точно сказать, что ссылки устанавливаются через контекстное меню. Версии Exсel у операторов разные - 2007 и 2010. Clervo
Гиперы добавляются тремя людьми. За двоих могу точно сказать, что ссылки устанавливаются через контекстное меню. Версии Exсel у операторов разные - 2007 и 2010. Автор - Clervo
Дата добавления - 10.08.2014 в 19:10
Gustav, мне как раз отображаемый текст необходимо заменить на путь к файлу.
Тему сейчас посмотрю, спасибо!
Gustav, мне как раз отображаемый текст необходимо заменить на путь к файлу.
Тему сейчас посмотрю, спасибо! Clervo
Тему сейчас посмотрю, спасибо! Автор - Clervo
Дата добавления - 10.08.2014 в 19:11
Если в ячейке A1 - путь к файлу: M:\Common\ORD\Приказы\2014\256_орг_2014 0726.docx, в ячейке B1 - текст: № 256-орг, то в C1 можно написать формулу:
которая будет отображать текст: № 256-орг
которая будет отображать текст: M:\Common\ORD\Приказы\2014\256_орг_2014 0726.docx.
Щелчок по ссылке в обоих случаях будет приводить к одному и тому же - открытию указанного файла в Word.
P.S. Извлечь ссылку на файл в отдельную соседнюю ячейку (чтобы потом от нее построить гиперссылку) можно при помощи простенькой пользовательской функции:
[vba]
Если в ячейке A1 - путь к файлу: M:\Common\ORD\Приказы\2014\256_орг_2014 0726.docx, в ячейке B1 - текст: № 256-орг, то в C1 можно написать формулу:
которая будет отображать текст: № 256-орг
которая будет отображать текст: M:\Common\ORD\Приказы\2014\256_орг_2014 0726.docx.
Щелчок по ссылке в обоих случаях будет приводить к одному и тому же - открытию указанного файла в Word.
P.S. Извлечь ссылку на файл в отдельную соседнюю ячейку (чтобы потом от нее построить гиперссылку) можно при помощи простенькой пользовательской функции:
[vba]
которая будет отображать текст: № 256-орг
которая будет отображать текст: M:\Common\ORD\Приказы\2014\256_орг_2014 0726.docx.
Щелчок по ссылке в обоих случаях будет приводить к одному и тому же - открытию указанного файла в Word.
P.S. Извлечь ссылку на файл в отдельную соседнюю ячейку (чтобы потом от нее построить гиперссылку) можно при помощи простенькой пользовательской функции:
[vba]
Sub tt()
On Error Resume Next
n_ = Selection.Count
For i = 1 To n_
Selection(i) = Selection(i).Hyperlinks(1).Address
Next i
Selection.Hyperlinks.Delete
On Error GoTo 0
End Sub
[/vba]
Если саму ГС удалять не нужно, то строку Selection.Hyperlinks.Delete убейте.
Немного изменил макрос в 19:20
Sub tt()
On Error Resume Next
n_ = Selection.Count
For i = 1 To n_
Selection(i) = Selection(i).Hyperlinks(1).Address
Next i
Selection.Hyperlinks.Delete
On Error GoTo 0
End Sub
[/vba]
Если саму ГС удалять не нужно, то строку Selection.Hyperlinks.Delete убейте.
Немного изменил макрос в 19:20 _Boroda_
Sub tt()
On Error Resume Next
n_ = Selection.Count
For i = 1 To n_
Selection(i) = Selection(i).Hyperlinks(1).Address
Next i
Selection.Hyperlinks.Delete
On Error GoTo 0
End Sub
[/vba]
Если саму ГС удалять не нужно, то строку Selection.Hyperlinks.Delete убейте.
Немного изменил макрос в 19:20 Автор - _Boroda_
Дата добавления - 10.08.2014 в 19:17
_Boroda_, сделала все по Вашему совету, исключив строку-"убийцу", и вот что получилось:
1) отображение поменялось, ура!
2) видимо, из-за того, что пути ОЧЕНЬ длинные, отображение получилось не полным, без начала пути, т.е. название файла с расширением и название основной директории есть, а названий диска и четырех директорий, предшествующих основной, не поместились.
_Boroda_, сделала все по Вашему совету, исключив строку-"убийцу", и вот что получилось:
1) отображение поменялось, ура!
2) видимо, из-за того, что пути ОЧЕНЬ длинные, отображение получилось не полным, без начала пути, т.е. название файла с расширением и название основной директории есть, а названий диска и четырех директорий, предшествующих основной, не поместились. Clervo
1) отображение поменялось, ура!
2) видимо, из-за того, что пути ОЧЕНЬ длинные, отображение получилось не полным, без начала пути, т.е. название файла с расширением и название основной директории есть, а названий диска и четырех директорий, предшествующих основной, не поместились. Автор - Clervo
Дата добавления - 10.08.2014 в 19:42
Gustav, простите ламера, но не подскажете, как подставляются пользовательские функции? Их надо записывать как макрос?
Gustav, простите ламера, но не подскажете, как подставляются пользовательские функции? Их надо записывать как макрос? Clervo
Как показать ссылку на ячейку (как текст) вместо значения и наоборот в Excel?
Обычно в ячейках формулы отображаются результаты вычислений в Excel, и скучно щелкать каждую ячейку, чтобы просмотреть ссылку на нее. Здесь я расскажу о трех методах отображения всех ссылок на ячейки вместо значений и наоборот в текущем листе, а также для отображения указанных ссылок на ячейки в виде текста вместо значений и наоборот.
- Показывать всю ссылку на ячейку вместо значения и наоборот в указанном диапазоне
- Показывать всю ссылку на ячейку вместо значения и наоборот в активном листе
- Показывать все ссылки на ячейки вместо значений и наоборот на всех листах
Показывать все ссылки на ячейки вместо значений и наоборот с помощью функции Показать формулу
Мы можем быстро показать все ссылки на ячейки вместо вычисленных результатов формул, нажав Формулы > Показать формулу. Смотрите скриншот ниже:
Если вам нужно снова отобразить все рассчитанные результаты в ячейках, нажмите кнопку Показать формулу снова.
Показывать ссылку на ячейку в виде текста вместо значения и наоборот в указанном диапазоне
С другой стороны, некоторые пользователи могут захотеть отображать указанную ссылку на ячейку как текст вместо значений, но не все ссылки на все ячейки формулы. Здесь я рекомендую вам попробовать Kutools for Excel's Преобразовать формулу в текст полезности и Преобразовать текст в формулу утилита.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel
Выделите ячейки формулы, в которых вы хотите отображать ссылки на ячейки в виде текста, а не результатов вычислений, и щелкните Кутулс > Content > Преобразовать формулу в текст.
Примечание: Напротив, для отображения вычисленных результатов вместо формул выберите эти формулы и нажмите Кутулс > Content > Преобразовать текст в формулу. Смотрите скриншот выше.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Показывать все ссылки на ячейки вместо значений и наоборот на всех листах
Как мы знаем, формулы> Показать формулы могут отображать / скрывать формулы только на активном листе. Чтобы показать или скрыть все ссылки на ячейки вместо значений или наоборот на всех листах, попробуйте Kutools for Excel's параметры просмотра функцию.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel
1, Нажмите Кутулс > Показать / Скрыть> Параметры просмотра. Смотрите скриншот ниже:
2. В открывшемся диалоговом окне «Параметры просмотра» установите флажок Формулы вариант, а затем щелкните Применить ко всем листам кнопки и Ok кнопку последовательно. Смотрите скриншот ниже:
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Демонстрация: показывать ссылку на ячейку (как текст) вместо значения и наоборот в Excel
Как преобразовать гиперссылку в обычный текст в Excel?
Предположим, у вас есть несколько интерактивных гиперссылок на листе, и теперь вы хотите преобразовать эти гиперссылки в обычный текст, есть ли у вас какие-нибудь полезные приемы для решения этой задачи? Теперь в этом руководстве представлены некоторые приемы, позволяющие быстро выполнить задание в Excel.
Преобразование гиперссылки в обычный текст из выделения / рабочего листа / книги с помощью Kutools for Excel
Преобразование гиперссылки в обычный текст с помощью меню правой кнопки мыши
В Excel вы можете использовать контекстное меню, чтобы преобразовать интерактивные гиперссылки в обычный текст.
Выберите гиперссылки, которые вы хотите преобразовать, и щелкните правой кнопкой мыши, чтобы отобразить контекстное меню, затем выберите Удалить гиперссылки вариант от него. Смотрите скриншот:
Теперь выбранные гиперссылки преобразованы в обычный текст.
Примечание: В Excel 2007 вы можете удалять только одну гиперссылку за раз. Чтобы удалить несколько гиперссылок, вам нужно повторить этот метод много раз.
Преобразование гиперссылки в обычный текст с помощью VBA
Если вы хотите использовать код VBA для решения задачи, вы можете сделать следующие шаги:
1. Нажмите F11 + Альт ключи одновременно, чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модули показать новый Модули вдова, а затем вставьте ниже код VBA в Модули окно.
VBA: преобразование гиперссылок в простой текст.
3. Нажмите F5 ключ или щелкните Run кнопку для выполнения этого VBA, тогда все гиперссылки на всем листе были преобразованы в обычный текст.
Примечание: С помощью этого VBA все гиперссылки на текущем листе будут преобразованы в обычный текст.
Преобразование гиперссылки в обычный текст из выделения / рабочего листа / книги с помощью Kutools for Excel
Если у вас есть Kutools for Excel, вы можете использовать его Удаление гиперссылок без потери форматирования утилита для преобразования гиперссылок из выделенного, активного листа, выбранных листов или всей книги.
После бесплатная установка Kutools for Excel, сделайте следующее:
Примените утилиту, нажав Кутулс > Ссылка > Удаление гиперссылок без потери форматирования, затем выберите нужный вариант. Смотрите скриншоты:
Читайте также: