Абсолютные и относительные ссылки в Excel

В Excel формулы могут содержать разные типы ссылок: относительные, абсолютные и смешанные. Когда в одной формуле встречаются как относительные, так и абсолютные ссылки, их поведение зависит от того, как именно вы копируете или редактируете эту формулу, а также от выбранного режима адресации (A1 или R1C1). Ниже приведены десять возможных «примет» (вариантов интерпретации) для формулы, расположенной в ячейке C1, содержащей одновременно абсолютные и относительные ссылки.

Стандартный режим A1 – относительные ссылки меняются при копировании

Если в ячейке C1 записана формула, например:

• =A1+B$1

то A1 – относительная ссылка, а B$1 – смешанная (абсолютная строка, относительный столбец). При копировании формулы в C2 ссылка A1 превратится в A2, а B$1 останется неизменной.

Абсолютная строка, относительный столбец (B$1)

Символ $ перед номером строки фиксирует строку, но столбец остаётся относительным. При копировании вниз строка не меняется, но при копировании вправо столбец меняется.

Относительная строка, абсолютный столбец ($B1)

Если в формуле указано $B1, то столбец B фиксируется, а строка остаётся относительной. При копировании вправо столбец не меняется, но при копировании вниз строка меняется.

Полностью абсолютная ссылка ($B$1)

Обе координаты фиксированы: ни при копировании вниз, ни при копировании вправо ссылка не меняется.

Относительная ссылка без знака $ (A1)

Обе координаты изменяются в зависимости от положения формулы при копировании.

Множественные смешанные ссылки в одной формуле

В одной формуле может быть несколько ссылок разного типа, например:

• =A1*$B$1+C$2

Каждая ссылка будет вести себя независимо от других.

Переключение вида ссылок с помощью клавиши F4

Выбирая ячейку в строке формул и нажимая F4, можно быстро переключать тип ссылки: от относительной к абсолютной, к смешанной и обратно.

Применение режима R1C1 – все ссылки абсолютны по умолчанию

В режиме R1C1 формула вида R[1]C[1] (относительная) превратится в R1C1 (абсолютную) при копировании, если не указать относительные индексы.

Использование именованных диапазонов, которые могут быть как относительными, так и абсолютными

Если вы объявляете диапазон MyRange как =$A$1:$B$10, то в формуле =SUM(MyRange) ссылка будет всегда абсолютной, независимо от положения формулы.

Фиксация ссылки при перемещении ячейки (cut & paste)

Когда вы копируете ячейку с формулой и вставляете её в другое место, Excel автоматически корректирует относительные ссылки. Чтобы избежать этого, используйте Ctrl+Shift+V (Insert Special → Paste Link) или предварительно добавьте знак $.

Понимание поведения абсолютных и относительных ссылок – ключ к правильному формированию формул в Excel. Вот краткое резюме того, как каждая ссылка будет вести себя:

Используйте эти правила, чтобы ваши формулы всегда давали ожидаемый результат независимо от того, где они находятся.