gototopgototop

Материалы Excel

Сейчас на сайте

Сейчас 224 гостей онлайн
Вставить в таблицу Excel. Гиперссылка в Excel на другой лист.

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

      Гиперссылка в Excel – это выделенные слова, значки, эл. адрес, нажимая на которые переходим по ссылке, это ссылка на другое месть в документе, в интернете, т.д. Как сделать и где применить гиперссылку, смотрите в статье «Как сделать гиперссылку в Excel».
      Здесь рассмотрим, как устанавливается гиперссылка в Excel на другой лист, на конкретную ячейку в таблице на другом листе.  Разберем два способа. Эти способы пригодятся для перехода из списка классов на строку с данными классного руководителя в другой таблице или, из таблицы заказов, покупок на строку с данными этого конкретного покупателя, заказчика в другой таблице, т.д. В таблице могут быть как цифры, так и слова, текст, значки, т.д.
      Итак, создаем динамическую гиперссылку в Excel
      Первый вариант.
      Гиперссылка в Excel на на другой лист.
Создаем две таблицы. У нас такие две таблицы с данными.
      Первая таблица на одном листе – «Лист заказов».
Вторая таблица на другом листе – «Заказчики».В первой таблице «Лист заказов» в ячейке D2 пишем такую формулу.
 =ГИПЕРССЫЛКА(ПОДСТАВИТЬ(ЯЧЕЙКА("адрес";ИНДЕКС(Заказчики!$A$1:$A$3;ПОИСКПОЗ('Лист заказов'!B2;Заказчики!$A$1:$A$3;0)));"";"");">>>")  
      В формуле написали В2 -  это первая ячейка с данными в таблице «Лист заказов». Можно указать адрес любой ячейки.
«>>>» - мы написали этот значок в формуле, чтобы именно этот значок стал ссылкой в ячейке. Можно написать другой значок. Ссылку на статью с кодами других значков смотрите ниже.
     Копируем формулу вниз по столбцу D. Получилась такая таблица с гиперссылками.Нажимаем на стрелку – ссылку в любой ячейке столбца D и откроется таблица «Заказчики» на ячейке той строки, ссылку которой мы выберем.
      Например, нам нужно найти контактные данные заказчика «ИП Петров А.О». Нажимаем на гиперссылку в ячейке D3 (в строке ИП Петров А.О.). Excel откроет лист «Заказчики» на строке ИП Петров А.О. Получится так.Формула с другими символами ссылки будет такая.
=ГИПЕРССЫЛКА(ПОДСТАВИТЬ(ЯЧЕЙКА("адрес";ИНДЕКС(Заказчики!$A$1:$A$3;ПОИСКПОЗ('Лист заказов'!B2;Заказчики!$A$1:$A$3;0)));"";"");СИМВОЛ(149))
      Формулу пишем такую же, но меняем последнюю часть формулы. Меняем ">>>" на СИМВОЛ(149). Код символа можно поставить любой. Получится так.
Чтобы снова перейти с листа «Заказчики» на лист заказов, нажимаем на ярлык листа «Лист заказов».
      Второй вариант.
      Как сделать гиперссылку в Excel на ячейку на другом листе.
Чуть дольше займет времени, но, кому что удобнее, кому что подойдет.
      Первый шаг.
Сделаем две таблицы, как в первом варианте.
      Второй шаг.
Присваиваем имя листу «Заказчики» с контактными данными заказчиков, покупателей. Как присвоить имя, какие условия, т.д., смотрите в статье «Присвоить имя в Excel ячейке, диапазону, формуле».
      На закладке «Формулы» в разделе «Определенные имена» нажимаем на кнопку «Присвоить имя».
В появившемся диалоговом окне в строке «Имя» пишем любое имя. Мы написали «Конт_данные». Помним, что пропусков в имени не должно быть.
В строке «Диапазон» пишем такую формулу.  
=ПСТР(ЯЧЕЙКА("имяфайла";Заказчики!$A$1);ПОИСК("[";ЯЧЕЙКА("имяфайла";Заказчики!$A$1));256)&"!"
      Диалоговое окно «Создание имени» заполнили так.      Третий шаг.
На листе «Лист заказов» в ячейку D2 пишем такую формулу.
 =ГИПЕРССЫЛКА(Конт_данные&АДРЕС(ПОИСКПОЗ(B2;Заказчики!$A$1:$A$3;0);1);">")
      В формуле написали В2 -  это первая ячейка с данными в таблице «Лист заказов».
«>» - мы написали этот значок в формуле, чтобы именно этот значок стал ссылкой в ячейке. Можно написать другой значок. Ссылку на статью с кодами других значков и как написать такую формулу, смотрите ниже.
      Копируем формулу по столбцу. Получилась такая таблица с ссылками.    Гиперссылка в Excel на другой лист.В формуле в ячейке D3 мы поставили значок такой ''>>>'' и ссылка получилась длинной стрелкой.
      Всё установили. Проверяем. Нажимаем на листе «Лист заказов» на ссылку в ячейке D3 (в строке "ИП Петров А.О.").
Excel перешел на лист «Заказчики» именно в строке на ячейку «ИП Петров А.О.».
      Несколько способов сделать ссылку на ячейку смотрите в статье "Сделать закладки в таблице Excel" здесь.
     Коды символов в Excel.
В этой формуле можно установить другие символы. Примеры кодов других символов, которые можно ставить в формулу вместо стрелки, смотрите в статье «Символ в Excel».
      Формула с кодами символов будет такая.
=ГИПЕРССЫЛКА(Конт_данные&АДРЕС(ПОИСКПОЗ(B2;Заказчики!$A$1:$A$3;0);1);СИМВОЛ(043))
Получилось так.
Если нужно разместить мало информации, то можно установить примечание, чтобы не переходить в другую таблицу. Как установить примечание в ячейке, смотрите в статье "Как сделать примечание в Excel" тут.
     Есть способы, как удалить гиперссылку. Но что делать, когда гиперссылок целый список, и нам нужно, чтобы все эти ссылки были написаны простым текстом. Как преобразовать сразу все гиперссылки в простой текст, читайте в статье "Как удалить все гиперссылки в Excel сразу".