§ 14. Ссылки в формулах

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

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

Абсолютная ссылка — ссылка на ячейку, адрес которой не изменяется при копировании формулы. Знак $ ставится перед обозначением столбца и номером строки: например, $C$4. Это позволяет многократно использовать в вычислениях одну ячейку, например ячейку с постоянным коэффициентом.

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

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

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

Адрес ячейки складывается из обозначения столбца и номера строки: например, B3 — ячейка на пересечении столбца B и строки 3. Такое устройство позволяет однозначно находить ячейку в таблице и записывать её адрес в формуле. Если формула обращается к нескольким ячейкам, по их адресам можно понять, откуда берутся данные для вычисления.

Ссылка без знаков $ обычно является относительной, потому что формулу часто нужно повторить для соседних строк или столбцов. Таблица сохраняет не конкретный адрес, а смещение от формулы до нужной ячейки. Например, если формула находится в C2 и ссылается на A2, нужная ячейка расположена на две колонки левее и в той же строке. При переносе формулы вниз сохраняется это положение, поэтому ссылка станет A3.

Знак $ сообщает таблице, что соответствующую часть адреса при копировании менять не надо. В записи $A$1 закреплены и столбец A, и строка 1, поэтому ссылка останется на ячейку A1 при переносе в любую сторону. Если знак стоит только перед буквой или только перед числом, закрепляется одна часть адреса. Это позволяет управлять изменением ссылки точнее, чем при обычном копировании.

Смешанные ссылки помогают заполнять формулами таблицу, где вычисление движется одновременно вправо и вниз. Например, для умножения значения из столбца A на значение из строки 1 ссылка на столбец A должна сохранять букву столбца, а ссылка на строку 1 — номер строки. Запись =$A2*B$1 решает эту задачу: при копировании меняются номер строки в первой ссылке и буква столбца во второй. Поэтому формула подстраивается под каждую позицию таблицы.

После копирования формулы полезно проверить её адреса, а не только получившееся число. Сначала определите, какие данные должны участвовать в вычислении для новой ячейки. Затем сравните это с изменившимися ссылками: относительные части должны сдвинуться, а закреплённые — остаться прежними. Такая проверка помогает обнаружить ошибку до того, как неверный результат попадёт в другие вычисления.

Виды ссылок и их изменение при копировании формулы

В таблице видно, какая часть адреса сохраняется при копировании. Сопоставьте вид ссылки с направлением, в котором перемещается формула, и проверьте, должны ли изменяться столбец, строка или обе части.

Разбор задач

Задача 1. В ячейке C2 записана формула =A2+B2. Как изменится формула после копирования в C3? Нужно учесть, что ссылки записаны без знаков $, а значит, обе части адресов могут смещаться вместе с формулой.

1. Определяем направление переноса: формула перемещается из строки 2 в строку 3, то есть на одну строку вниз.
2. В ссылках A2 и B2 столбцы не меняются, потому что формула не сдвинулась по горизонтали. Номера строк увеличиваются на единицу.
3. Записываем новые адреса A3 и B3, сохраняя знак сложения между ними.

Ответ: в C3 появится формула =A3+B3. Она складывает значения из той же строки, что и сама формула.

Задача 2. В столбце B записаны цены товаров, а в E1 — постоянный коэффициент. Составьте формулу в C2 для умножения цены на коэффициент и скопируйте её вниз. Цена должна браться из строки с формулой, а коэффициент при любом переносе должен оставаться в E1.

1. Для цены выбираем относительную ссылку B2: при копировании вниз номер строки должен меняться вместе с товаром.
2. Для коэффициента выбираем абсолютную ссылку $E$1: обе части адреса закреплены, поэтому ссылка не сдвинется.
3. Записываем умножение цены на коэффициент, используя знак * между ссылками.

Ответ: в C2 вводим =B2*$E$1. После копирования формулы в C3 получится =B3*$E$1: ссылка на цену изменится, а адрес коэффициента останется прежним.

Задача 3. Даны значения в столбце A и в строке 1. Нужно заполнить формулами таблицу произведений, используя значение из столбца A и значение из строки 1. Формулу вводят в область пересечения этих данных и копируют по таблице вправо и вниз.

1. Ссылка на значение из столбца A должна всегда указывать на столбец A, но номер строки должен соответствовать строке результата. Поэтому используем $A2.
2. Ссылка на значение из строки 1 должна сохранять номер строки, но менять столбец при движении вправо. Поэтому используем B$1.
3. Перемножаем обе ссылки. При копировании вниз изменится строка в $A2, а при копировании вправо — столбец в B$1.

Ответ: для ячейки B2 подходит формула =$A2*B$1. Например, при копировании вправо в C2 она станет =$A2*C$1, а при копировании вниз в B3 — =$A3*B$1.

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

Ошибка — считать, что адрес относительной ссылки останется прежним после копирования. Это происходит, если воспринимать A1 только как постоянное имя ячейки и не учитывать смещение от формулы. Проверьте, насколько переместилась формула и какие адреса должны находиться на таком же расстоянии от неё.

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

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

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

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

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

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

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