Вопрос или проблема
Я видел аналогичный вопрос о абсолютных ссылках, Как сохранить ссылку на ячейку таблицы постоянной при вставке строк?, и думаю, что решение может быть схожим.
Я использую таблицу для расчета импеданса и хочу иметь возможность перемещать, копировать и вставлять блоки формул, не нарушая их. Каждый блок берет свои входные данные из четырех ячеек, расположенных прямо над ним. Это в основном работает, но ломается, если я хочу добавить или удалить строки из таблицы.
Я ищу способ указать, что хочу, чтобы ячейка была N ячеек выше текущей, и чтобы при удалении этой строки она по-прежнему ссылалась на ячейку, которая находится N ячеек выше.
При относительной ссылке, если удаляется ссылаемая ячейка, ссылка становится недействительной.
Формула, которая всегда ссылается на ячейку, расположенную на три строки выше ячейки, содержащей формулу (предполагая, что формула находится в A8):
=INDIRECT("R"&ROW(A8)-3&"C"&COLUMN(A8),FALSE)
Вы можете копировать формулу, удалять целевую строку, и она все равно будет ссылаться на то, что находится на три строки выше.
В Excel вы можете записать обозначение ячейки следующим образом: =A1.
Это ссылается на ячейку A1, и если вы скопируете ячейку, в которой находится эта формула, и вставите ее в ячейку ниже, A1 автоматически изменится на A2, так как это ячейка относительно вашей текущей ячейки.
То же самое происходит, если вы вставляете ее в ячейку справа, тогда вы получите =B1.
Поскольку не всегда нужно изменять ячейку, Excel предлагает возможность всегда сохранять строку и/или столбец неизменными, независимо от того, перемещаете вы их или нет.
Вы можете сделать это, добавив знак $ перед буквой или цифрой.
Если вы хотите, чтобы A1 всегда сохраняло A, но 1 было относительным, A должно измениться на $A. Если вы всегда хотите сохранять 1, но A было относительным, 1 должно измениться на $1. Если вы хотите, чтобы оба значения оставались одинаковыми, они оба должны измениться: так будет $A$1.
Четыре варианта могут быть: =A1, =$A1, =A$1 или =$A$1.
Конечно, когда вы удаляете строку, все ячейки пересчитываются, и относительные номера автоматически обновляются. Excel будет пытаться направить их на правильную ячейку. Поэтому, если вы не хотите, чтобы эти ячейки обновлялись и хотите, чтобы при перемещении ячейки указывалась на другую ячейку, добавьте $.
Ответ или решение
Вопрос о том, как сохранить относительную ссылку на фиксированное смещение от текущей ячейки в таблицах, действительно имеет важное значение для решения задач, особенно при выполнении расчетов, где необходимо учитывать данные из определенных ячеек. В данном случае, чтобы сделать ссылку на ячейку, находящуюся, например, на три строки выше текущей ячейки, можно воспользоваться функцией INDIRECT.
Вот как это сделать. Предположим, что ваша формула находится в ячейке A8, вы можете использовать следующий код:
=INDIRECT("R" & ROW() - 3 & "C" & COLUMN(), FALSE)Давайте разберем это выражение:
ROW()вернет номер текущей строки, в которой находится формула.COLUMN()вернет номер текущего столбца.- Используя
ROW() - 3, мы указываем на ячейку, находящуюся на три строки выше текущей. - Функция
INDIRECTпозволяет нам создать ссылку на ячейку, используя строковый адрес, передаваемый в качестве первого аргумента. Второй аргументFALSEуказывает на то, что мы используем R1C1-формат.
Таким образом, если вы скопируете или переместите формулу в другую строку, она всегда будет ссылаться на ячейку, находящуюся на три строки выше ее текущего положения, и она не станет недействительной, даже если строки будут добавлены или удалены в таблице.
Также стоит упомянуть, что использование абсолютных ссылок с $ (как в примере $A$1, $A1, A$1 и A1) в данном случае не даст нужного результата, так как эти ссылки фиксируются на конкретные ячейки и будут изменены при выполнении операций копирования и вставки. Поэтому функция INDIRECT является наиболее подходящим вариантом для ваших нужд, гарантируя, что ссылка останется действительной и корректной даже при изменении структуры таблицы.
Таким образом, этот подход позволяет эффективно выполнять и сохранять формулы, основанные на относительных позициях ячеек, без риска получить недействительные ссылки.