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

Оффлайн ser-vin

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

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

Мне надо скорректировать Лист 1 вставить строку "333", строка "444" соответственно сместиться вниз.

1 лист    2 лист
111          ааа
222          ббб
333
444          ггг

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

Не знаю как правильно называется динамическая ссылка или корректная ссылка при смещающихся строках.

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

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

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

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

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


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

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

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

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


Дополнения

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

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

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

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


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

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

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

Примеры

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

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

Оффлайн ser-vin

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

Оффлайн ser-vin

  • Посетитель форума
  • Сообщений: 3
Подумав решил что наверное более без проблемный вариант это работа в одном файле. Первый вариант который предлагали.

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

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

Лист 1  это название вкладки где находиться цель
А - столбец в котором цель, соответственно если у меня в колонке D то просто меняю букуву.
С4 - строка в которой стоит цель
Лист 1 снова то же самое что и вначале
B:B;0 -  вот с этим не понял, где столбец цели , где конечной, пробовал но ни чего не получилось. Выскакивает окно с просьбой указать файл и обновить данные, в итоге получаю ссылку с название "0"  и битой ссылкой которая никуда не отсылает.
"ссылка"- это самому прописать надо или автоматом это название подхватывается ?

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

  • Administrator
  • Сообщений: 1622
Работу с двумя файлами смотрите ответ 2, я добавил в него раздел "Перенос папки с двумя файлами в другую папку или на другой компьютер".

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

  • Administrator
  • Сообщений: 1622
Описание этого фрагмента:
ПОИСКПОЗ(C4;Лист1!B:B;0)

Функция ПОИСКПОЗ берёт данные из ячейки C4 и ищет их на листе "Лист1", в столбце B по точному совпадению.

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

  • Administrator
  • Сообщений: 1622
Цитата:
"ссылка"- это самому прописать надо или автоматом это название подхватывается ?

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