☰

гиперссылка не меняется после удаления или вставки ячейки

tag-icon • tag-icon microsoft-excel
гиперссылка не меняется после удаления или вставки ячейки

Когда я использую функцию гиперссылки, чтобы указать на ячейку на другом листе той же книги, гиперссылка не подстраивается динамически под ячейку при ее перемещении. То есть я использую формулу =HYPERLINK("#'Calcs. Form'!D12","1-100"), но если я удаляю ячейку D11и Calcs. Formмоя D12ячейка перемещается вверх, моя гиперссылка все равно указывает на ячейку D12 вместо того, чтобы перемещаться вверх на D11.

Единственное решение, которое я нашел, это дать ячейке уникальное имя через определение имени и указать гиперссылку на имя. Предложения? Я что-то упустил в своей формуле? У меня нет набора D12как абсолютного. ( $D$12)

решение1

Проблема в том, что "D12" находится в строке, а не в отдельной ссылке на ячейку. Вы можете быстро обновить это с помощью следующего подхода:

=HYPERLINK("#'Calcs. Form'!D"&ROW('Calcs. Form'!D12),"1-100")

Функция ROW()по-прежнему будет передавать «12» в создаваемую строку вместе с «#'Calcs. Form'!D», но теперь это фактическая ссылка на ячейку, поэтому, если вы удалите, скажем, D3, и нужная ячейка станет ячейкой D11, ссылка в формуле также изменится на D11 и ROW()вернет «11», а не «12», поэтому гиперссылка будет указывать на правильную ячейку.

Вы можете использовать этот метод объединения текста и фактических ссылок на ячейки в любой функции, которой нужна строка. Поэтому вместо того, чтобы давать ему простую, жестко закодированную строку, вы создаете строку с частями, которые должны быть адаптивными (корректируя для удаления ячеек/строк/столбцов, как здесь), или используете ввод из других ячеек для создания динамического адреса (чтобы вы могли создавать, например, «динамические диапазоны» или и то, и другое в этом случае, и INDIRECT()вы можете захотеть динамически построить ссылку). Еще одно применение — возможно, у вас есть много вкладок с одинаково организованными данными, возможно, только для разных месяцев/годов, и вы хотите иметь отчетный лист, на котором пользователь может ввести месяц или год или что-то еще, и данные с правой вкладки, таким образом, динамически выбираются без необходимости вкладывать IF()for каждой вкладки в чудовищную формулу. Очень простой метод, но полезный в миллионе мест.

решение2

Один из способов вставить возможно изменяющуюся ссылку на ячейку как ТЕКСТ в формулу:

«Краткая» форма:
="The cell referenced is"&cell(D12;"address")&" and it contains '"&D12&"'"

Одинаковый:
=CONCATENATE("The cell referenced is";cell("address"D12);" and it contains '";D12;"'")


(Непроверенные формулы- ) Обратите внимание на использование ;в качестве разделителя параметров, это будет ,справедливо, например, для английской локали, а возможно, и для других тоже.

Связанный контент