§ 27. Относительные и абсолютные ссылки

Определения и обозначения

Относительная ссылка — ссылка на ячейку, координаты которой при копировании формулы изменяются относительно нового положения формулы. Например, A2 при переносе на строку вниз превращается в A3. Такая ссылка нужна, когда одна и та же схема вычислений повторяется в разных строках или столбцах.

Абсолютная ссылка — ссылка, в которой зафиксированы и столбец, и строка; знак $ ставится перед каждой частью, например $A$1. При копировании адрес полностью сохраняется. Это удобно для обращения к постоянному числу, коэффициенту или другой неизменной ячейке.

Смешанная ссылка — ссылка, в которой закреплена только одна координата: столбец, например $A1, или строка, например A$1. Незакреплённая часть при копировании изменяется, а закреплённая остаётся прежней. Такие ссылки нужны в таблицах, где формулу копируют одновременно вправо и вниз.

Копирование формулы — перенос формулы в другую ячейку с автоматическим изменением относительных частей ссылок. Таблица смотрит, на сколько строк и столбцов переместилась формула, и повторяет такое же смещение. Поэтому перед копированием нужно понять, какие адреса должны двигаться, а какие следует зафиксировать.

Как это устроено

В формуле адрес ячейки задаёт ссылку на её содержимое, поэтому важно отличать саму формулу от её адресов. Например, в записи =A2+B2 действие сложения остаётся тем же, а адреса могут измениться. При копировании таблица не просто переносит символы, а перестраивает относительные ссылки согласно новому положению.

Относительная ссылка указывает положение ячейки относительно ячейки с формулой. В формуле из B2 ссылка A2 находится на один столбец левее и в той же строке. Если формулу перенести на несколько строк вниз, сохранится это взаимное положение, поэтому ссылка сместится вниз на такое же число строк.

Абсолютная ссылка сохраняет прежний адрес при копировании, потому что знаки $ фиксируют букву столбца и номер строки. Такая запись сообщает таблице: расстояние до этой ячейки пересчитывать не нужно. Поэтому ссылка на постоянный коэффициент не начнёт случайно указывать на другую строку или столбец.

Смешанная ссылка фиксирует только одну координату, поскольку знак $ ставится лишь перед столбцом или строкой. Если закреплён столбец, меняется только номер строки; если закреплена строка, меняется только буква столбца. Это позволяет одной формуле правильно работать при перемещении в двух направлениях.

Выбор типа ссылки зависит от задачи. Относительные ссылки удобны для вычислений по строкам или столбцам, абсолютные — для обращения к постоянной ячейке, а смешанные — при заполнении большой таблицы вправо и вниз. Перед копированием полезно отдельно проверить каждую ссылку и решить, какая её часть должна изменяться.

Как меняются ссылки при копировании формулы

Сначала найдите в ссылке знак $ и определите, какая координата закреплена. Затем представьте направление копирования: вправо меняются столбцы, вниз — строки. Если знаков $ нет, изменяются обе координаты согласно смещению.

Разбор задач

Задача 1. В ячейке B2 записана формула =A2+1. Какую формулу получим, скопировав её в B3? Ссылка A2 относительная, поэтому нужно проследить изменение строки.

1. Формула перемещается из B2 в B3, то есть на одну строку вниз.
2. Столбец ссылки A не меняется, потому что перемещения вправо или влево нет.
3. Номер строки 2 увеличивается на единицу и становится 3, поэтому получается формула =A3+1.

Ответ: в ячейке B3 будет записана формула =A3+1. Число 1 не связано с адресом ячейки и поэтому остаётся без изменений.

Задача 2. В B2 записана формула =A2*$D$1. Как она изменится при копировании в B5? Здесь одна ссылка относительная, а другая абсолютная, поэтому их нужно рассматривать отдельно.

1. Формула перемещается на три строки вниз: из строки 2 в строку 5.
2. Ссылка A2 относительная, поэтому её столбец A сохраняется, а номер строки 2 превращается в 5.
3. В ссылке $D$1 обе координаты закреплены знаками $, поэтому она остаётся $D$1.

Ответ: в B5 получится формула =A5*$D$1. При вычислении будет взято содержимое A5 и умножено на постоянное значение из D1.

Задача 3. В B2 записана формула =$A2*B$1. Какая формула получится после копирования в D4? Перенос идёт на два столбца вправо и на две строки вниз, а в каждой смешанной ссылке закреплена своя координата.

1. В ссылке $A2 столбец A закреплён, поэтому он не изменяется, а строка 2 смещается вниз на две позиции и становится 4.
2. В ссылке B$1 строка 1 закреплена, поэтому она остаётся прежней, а столбец B смещается на два столбца вправо и становится D.
3. Подставляем новые части в исходную запись и получаем =$A4*D$1.

Ответ: после копирования в D4 формула будет =$A4*D$1. Такой результат показывает, что каждая часть смешанной ссылки изменяется независимо от другой.

Типичные ошибки

Первая ошибка — считать, что любая ссылка при копировании остаётся неизменной. Так бывает, если забывают, что адрес без знаков $ является относительным. Проверьте себя: сравните положение исходной и новой ячейки и измените ссылку на такое же смещение.

Вторая ошибка — поставить знак $ только перед буквой столбца и назвать ссылку абсолютной. Запись $A1 закрепляет лишь столбец, а строка всё ещё меняется. Для полной фиксации проверьте наличие двух знаков: $A$1.

Третья ошибка — путать записи $A1 и A$1. В первой закреплён столбец, во второй — строка, поэтому при копировании они ведут себя по-разному. Можно мысленно спросить: стоит ли знак перед буквой или перед числом?

Четвёртая ошибка — изменять адрес вручную, не учитывая направление и величину копирования. Из-за этого легко сместить ссылку не на столько строк или столбцов, сколько нужно. Сначала определите перенос, например два столбца вправо и одну строку вниз, а затем изменяйте только незакреплённые части.

Связь с другими темами

До изучения ссылок нужно уметь читать адрес ячейки: буква обозначает столбец, а число — строку. Также понадобятся ввод формул и копирование содержимого ячеек, иначе невозможно увидеть действие разных типов ссылок.

Эти знания применяются при заполнении формулой целых диапазонов и расчётах с постоянными коэффициентами. Они помогают создавать таблицы вычислений, где одна формула быстро распространяется на множество строк и столбцов без ручного исправления адресов.

Что запомнить

• В относительной ссылке при копировании меняются и столбец, и строка согласно смещению.
• В записи $A$1 закреплены и столбец, и строка.
• В записи $A1 закреплён столбец, но не строка.
• В записи A$1 закреплена строка, но не столбец.
• Знак $ ставят перед той частью адреса, которую нужно зафиксировать.
• При копировании формулы проверяй, какие части каждой ссылки должны измениться.