Автор Тема: Excel: Динамическая ссылка или корректная ссылка при смещающихся строках.  (Прочитано 129 раз)

Оффлайн ser-vin

  • Посетитель форума
  • Сообщений: 4
Есть два листа в одном документе и второй вариант два отдельных документа, в которых такие листы:
1 лист       2 лист
111         ааа
222         ббб
444          ггг

Ячейки с буквами на листе 2 привязаны по гиперссылке к ячейкам листа 1. Кликаешь "ааа" и происходит переход точно в ячейку "111".

Мне надо скорректировать Лист 1: вставить строку "333", строка "444" соответственно сместиться вниз:
1 лист    2 лист
111          ааа
222          ббб
333
444          ггг

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

Подскажите как правильно это сделать. Данных много и всё время надо корректировать.

Онлайн Администратор

  • Administrator
  • Сообщений: 1657
Вариант, если нужно работать внутри одного файла

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

И используйте формулу:
=ГИПЕРССЫЛКА("#'Лист1'!A"&ПОИСКПОЗ(C2;Лист1!B:B;0);"ссылка")


Пояснения к формуле

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

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

ПОИСКПОЗ(C2;Лист1!B:B;0)
Ищет на листе 1, во всём столбце B число из ячейки C2 по точному совпадению.
Функция вернёт номер строки и затем номер строки будет подставлен в функцию ГИПЕРССЫЛКА после буквы A и будет сформирован адрес.


Дополнения

Этот вариант можно использовать и с двумя файлами, если файл-цель открыт. Если файл-цель закрыт, то ничего происходить не будет при щелчке по ссылке.
Если захотите использовать этот способ для двух файлов, то нужно изменить синтаксис пути, имени файла и имени листа. Для этого смотрите образец в способе "Вариант, если нужно работать с двумя файлами".

Онлайн Администратор

  • Administrator
  • Сообщений: 1657
Вариант, если нужно работать с двумя файлами

В этом случае нужен ещё один допстолбец в файле-гиперссылке.
Пути в формулах укажите свои.


Перенос папки с двумя файлами в другую папку или на другой компьютер

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

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

Примеры

Если файлы находятся в одной папке, то достаточно указать только имя файла:
=ГИПЕРССЫЛКА("[F_Цель.xlsx]'Лист1'!A"&D2;"ссылка")

Если файлы находятся в одной папке, но внутри этой папки файлы находятся в разных папках, то нужно ещё указать папку:
=ГИПЕРССЫЛКА("[Новая папка\F_Цель.xlsx]'Лист1'!A"&D2;"ссылка")

Оффлайн ser-vin

  • Посетитель форума
  • Сообщений: 4