§ 14. Использование формул. Относительные и абсолютные ссылки

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

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

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

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

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

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

При вводе формулы в ячейку программа вычисляет её значение, используя числа, знаки действий и ссылки на другие ячейки. Ссылка нужна потому, что данные можно хранить отдельно от вычисления: формула обращается к ячейке, а не требует повторно вводить её значение. Поэтому после изменения исходного числа таблица может пересчитать результат без переписывания формулы. Начальный знак «=» сообщает программе, что содержимое ячейки нужно воспринимать как вычисление.

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

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

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

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

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

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

Разбор задач

В ячейке C2 записана формула =A2+B2. Нужно определить, как она изменится при копировании в C3. Обе ссылки относительные: перед буквой столбца и номером строки нет знака $. Формула перемещается на одну строку вниз, поэтому программа сохраняет положение каждой исходной ячейки относительно нового места формулы.

1. Сравните исходную ячейку C2 и новую ячейку C3: формула смещается на одну строку вниз. Столбец C не изменился, а номер строки увеличился на единицу. Такое же смещение применяется к адресам внутри формулы.

2. В ссылке A2 меняется номер строки: A2 становится A3. В ссылке B2 происходит то же самое: B2 становится B3. Буквы столбцов остаются прежними, потому что копирование не перемещало формулу в сторону.

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

Ответ: в C3 будет формула =A3+B3.

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

1. Стоимость получается умножением количества на цену за единицу. Для количества из строки 2 используйте относительную ссылку B2, поскольку при копировании вниз должны подставляться B3, B4 и следующие ячейки. Так формула будет учитывать отдельное количество в каждой строке.

2. Цена находится в E1 и нужна для всех строк расчёта. Если оставить ссылку относительной, при копировании вниз она превратится в E2, затем в E3, хотя цена записана в E1. Поэтому закрепите обе части адреса и запишите $E$1.

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

Ответ: в C2 запишите =B2*$E$1 и скопируйте формулу вниз.

Нужно заполнить диапазон B2:F6 таблицы умножения. Заголовки одного множителя находятся в столбце A, а другого — в строке 1. В каждой ячейке результата нужно перемножить соответствующие заголовки. При копировании формулы по таблице должны меняться номер строки первого множителя и буква столбца второго.

1. Для заголовка в столбце A используйте адрес A2: буква столбца должна оставаться A, а номер строки должен меняться при копировании вниз. Это смешанная ссылка $A2. При переносе вправо она всё равно будет указывать на столбец A.

2. Для заголовка в строке 1 начните с адреса B1: столбец должен меняться при копировании вправо, а строка — оставаться первой. Поэтому запишите B$1. Такая ссылка позволит каждой ячейке брать множитель из заголовка своего столбца.

3. Перемножьте две ссылки и введите формулу в B2. Копирование вправо изменит B$1 на C$1 и далее, а копирование вниз изменит $A2 на $A3 и далее. В угловой ячейке B2 формула обращается к заголовкам A2 и B1, что подтверждает правильность выбора смешанных ссылок.

Ответ: в B2 запишите =$A2*B$1 и скопируйте формулу по диапазону B2:F6.

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

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

Неверно считать, что знак $ всегда закрепляет всю ссылку: в смешанной ссылке он фиксирует только столбец или только строку. Путаница возникает, если смотреть на адрес целиком, а не на положение знака. Например, $A1 не запрещает меняться номеру строки. Чтобы проверить запись, отдельно спросите себя, какая именно часть адреса стоит после знака $.

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

Не нужно вручную менять адрес ссылки после каждого копирования, если нужное поведение можно задать заранее. Ручная правка отнимает время и повышает риск пропустить ячейку или ошибиться в номере строки. Перед исправлением каждой формулы выясните, должна ли ссылка быть относительной, абсолютной или смешанной. Затем проверьте первую скопированную формулу: если адреса ведут к нужным данным, остальные можно заполнить копированием.

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

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

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

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

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