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

Электронные таблицы · ключевая тема ОГЭ

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

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

Зачем вообще три вида ссылок

Формула в таблице почти никогда не пишется один раз. Её копируют — вниз по столбцу или вправо по строке. И тут возникает вопрос: что должно «поехать» вместе с формулой, а что обязано остаться на месте?

• Цена за штуку лежит в соседней ячейке той же строки — пусть ссылка едет вместе с формулой. Это относительная ссылка.
• Курс доллара или ставка налога записаны в одной-единственной ячейке — на неё должны смотреть все формулы. Это абсолютная ссылка.

Три вида ссылок — это просто три ответа на вопрос «двигать или закрепить?».

Относительная ссылка: едет вместе с формулой

Адрес вроде A1 таблица понимает не как «ячейка A1», а как указание относительно текущего места: «возьми значение на столько-то столбцов левее и на столько-то строк выше». Поэтому при копировании адрес автоматически пересчитывается.

Правило простое: копируем вниз — растёт номер строки; копируем вправо — сдвигается буква столбца. На сколько ячеек сдвинули формулу, на столько же сдвигается и каждая относительная ссылка внутри неё.

Абсолютная ссылка: стоит на месте

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

Если формулу =B1*$E$1 скопировать вниз, поедет только B1, а $E$1 останется собой:

Смешанная ссылка: закреплено наполовину

Доллар можно поставить только перед буквой или только перед номером — закрепится лишь эта часть, вторая останется относительной.

Подсказка для запоминания: доллар «приклеивает» то, что стоит сразу после него. В A$1 приклеена строка 1, в $A1 — столбец A.

Типовая задача ОГЭ

Это самый частый формат экзаменационного вопроса.

В ячейке C2 записана формула =$A3+B$1. Её скопировали в ячейку E4. Какая формула окажется в E4?

Сначала считаем сдвиг: из столбца C в столбец E — это +2 столбца (C→D→E); из строки 2 в строку 4 — это +2 строки. Теперь правим каждую ссылку по её типу.

Ответ: в ячейке E4 будет формула =$A5+D$1.

Проверим сдвиг кодом

Чтобы не ошибиться в подсчёте адресов, удобно представить ссылку как «буква + номер». Запустите программу ниже: она по исходному адресу и величине сдвига сама вычисляет новый адрес относительной ссылки. Так работает таблица «под капотом».

def shift(ref, d_col, d_row):
    # ref вида 'B1': буква столбца + номер строки
    col = ref[0]
    row = int(ref[1:])
    new_col = chr(ord(col) + d_col)   # сдвиг буквы столбца
    new_row = row + d_row             # сдвиг номера строки
    return new_col + str(new_row)

# Формула =A1+B1 скопирована из A1 в C3: +2 столбца, +2 строки
for cell in ['A1', 'B1']:
    print(cell, '->', shift(cell, 2, 2))

Программа печатает новые адреса после копирования формулы =A1+B1 на 2 столбца вправо и 2 строки вниз:

A1 -> C3
B1 -> D3

Видно то же правило, что и в таблицах выше: каждая относительная ссылка сдвигается ровно на столько, на сколько переехала сама формула. Попробуйте поменять shift(cell, 2, 2) на shift(cell, 0, 5) — это будет чистое копирование вниз на 5 строк, и буквы столбцов перестанут меняться.

Частые ошибки

• Двигают абсолютную ссылку. $A$1 при любом копировании остаётся $A$1 — доллары держат оба адреса.
• Путают, что фиксирует доллар. В A$1 закреплена строка, а столбец свободен; в $A1 — наоборот.
• Меняют не ту координату. Копирование вниз меняет номер строки, копирование вправо — букву столбца. Не перепутайте оси.
• Считают сдвиг от 1, а не от исходной ячейки. Сдвиг — это разница между новым и старым адресом формулы, а не сам новый адрес.

Коротко

• Относительная ссылка A1 сдвигается при копировании: вниз растёт строка, вправо — столбец.
• Абсолютная ссылка $A$1 закреплена полностью и не меняется никогда.
• Смешанная (A$1, $A1) фиксирует только то, что стоит сразу после доллара.
• Алгоритм задачи ОГЭ: найти сдвиг по столбцам и строкам, затем изменить каждую незакреплённую часть на этот сдвиг.