Ссылки на ячейки: относительные, абсолютные, смешанные

Примеры формулы и ссылки.xlsx

В прошлом уроке вы научились вводить арифметические формулы со знаком «=» и ссылками на ячейки. При копировании формулы такие ссылки могут смещаться; знак $ позволяет управлять этим смещением.

Механизм относительной ссылки: смещение вместо адреса

Когда вы пишете =A1*B1 в ячейке C1, Excel не запоминает конкретные адреса A1 и B1. Он запоминает смещение: «возьми значение из ячейки, которая находится на два столбца левее меня, и умножь на ячейку, которая на один столбец левее меня». Именно это называется относительной ссылкой.

Посмотрим на конкретном примере. Допустим, в столбцах A и B записаны количество товара и цена за штуку. В C1 вы пишете =A1*B1. Теперь копируете C1 вниз в C2 — формула автоматически становится =A2*B2. Скопируйте ещё раз в C3 — получите =A3*B3. Excel сдвинул обе ссылки на одну строку вниз, потому что сама формула переехала на одну строку вниз.

То же самое работает по горизонтали. Если скопировать C1 вправо в D1, формула превратится в =B1*C1 — каждый столбец в ссылках сдвинулся на один вправо.

Можно сдвигать одновременно. Формула из C1 скопирована в E3 — это на две строки вниз и два столбца вправо. Тогда =A1*B1 станет =C3*D3: оба столбца сдвинулись на два вправо, обе строки — на две вниз.

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

Абсолютная ссылка $A$1: фиксация строки и столбца знаком $

Относительные ссылки удобны, пока каждая строка формулы должна смотреть на свою строку данных. Но бывает иначе: все формулы в столбце должны обращаться к одной и той же ячейке — например, к ячейке B1, где записана ставка НДС 20%.

Если в исходной ячейке C2 оставить формулу =A2*B1, то после копирования в C3 получится =A3*B2, а в C4 — =A4*B3. Вместо ставки из B1 формулы начнут брать значения из других ячеек.

Чтобы этого не произошло, перед буквой столбца и перед номером строки ставят знак доллара: $B$1. Знак $ перед буквой говорит «столбец B не трогать при копировании». Знак $ перед цифрой говорит «строку 1 не трогать при копировании». Вместе они полностью замораживают адрес.

Пример. В ячейке B1 записана ставка 0,20. В C2 написана формула =A2*$B$1 — стоимость товара умножается на ставку НДС. Копируем C2 вниз в C3, C4, C5, C6. В каждой из этих ячеек ссылка на ставку остаётся $B$1, а ссылка на товар меняется: A3, A4, A5, A6. Проверить легко: щёлкнуть на C6 и посмотреть в строку формул — там должно быть =A6*$B$1.

Чтобы не набирать два знака $ вручную, достаточно поставить курсор внутрь адреса ссылки прямо в строке формул и нажать F4. Так можно быстро получить абсолютную ссылку $A$1.

Смешанные ссылки $A1 и A$1: частичная фиксация при распространении в двух направлениях

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

Это удобно в таблице умножения. Пусть числа 1–9 стоят в строке 1, в ячейках B1:J1, и в столбце A, в ячейках A2:A10. Диапазон B2:J10 должен содержать произведение числа из своей строки на число из своего столбца.

В ячейке B2 нужна формула =$A2*B$1. В ссылке $A2 знак $ фиксирует столбец A: при копировании вправо формула продолжит брать первый множитель из столбца A. Строка остаётся относительной и меняется при копировании вниз. В ссылке B$1 зафиксирована строка 1, поэтому второй множитель всегда берётся из верхней строки; столбец меняется при копировании вправо.

Сначала скопируйте B2 вправо до J2. Затем выделите B2:J2 и протяните весь диапазон вниз до строки 10. Контрольные формулы покажут результат:

• D5: =$A5*D$1 — значение 4 из A5 умножается на значение 3 из D1; результат 12.
• J10: =$A10*J$1 — 9 умножается на 9; результат 81.

Без фиксации, в формуле =A2*B1, обе ссылки смещались бы одновременно. Например, при копировании из B2 в J10 она превратилась бы в =I10*J9 и обращалась бы не к заголовкам таблицы.

Клавиша F4 быстро меняет тип выделенной ссылки в формуле:

• A2 → $A$2 — зафиксированы строка и столбец;
• → A$2 — зафиксирована строка;
• → $A2 — зафиксирован столбец;
• → A2 — ссылка снова относительная.

Для верхней строки таблицы нужна ссылка вида B$1, а для левого столбца — $A2. Нажимайте F4, пока знак $ не окажется перед нужной частью адреса.

Ловушка забытого $: диагностика и исправление сдвинувшейся ссылки

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

Типичная ситуация. В B1 записана ставка комиссии 0,05. В C2 написана формула =A2*B1 — цена товара умножается на ставку. Формулу копируют вниз на C3, C4, C5. Excel честно сдвигает относительную ссылку B1: в C3 появляется =A3*B2, в C4 — =A4*B3, в C5 — =A5*B4. Вместо ставки из B1 формулы начинают брать данные из B2, B3, B4 — что бы там ни было: другие числа, текст или пустые ячейки.

Как диагностировать. Выделите несколько скопированных ячеек по очереди и смотрите в строку формул. Если ссылка на ставку каждый раз другая (B1, B2, B3...), значит, она относительная — и именно это причина.

Как исправить:

1. Вернитесь в исходную ячейку C2.
2. Щёлкните в строке формул на адрес B1 внутри формулы.
3. Нажмите F4 один раз — B1 превратится в $B$1.
4. Нажмите Enter, чтобы зафиксировать изменение.
5. Скопируйте C2 вниз заново — маркером заполнения или Ctrl+C → выделить диапазон → Ctrl+V.

Теперь проверьте: выделите C3, C4, C5 и смотрите строку формул. Во всех ячейках ссылка на ставку должна быть $B$1, а ссылка на цену товара — A3, A4, A5 соответственно. Если так — ошибка устранена.

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

Проверка ссылок при копировании

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

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

Excel: формулы и работа с данными