Абсолютные и относительные ссылки в Microsoft Excel¶

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

¶Принцип относительных ссылок

По умолчанию Excel записывает адреса ячеек в формулах как относительные. Если в ячейке C1 записана формула =A1+B1, а её скопировать в C2, то формула автоматически преобразуется в =A2+B2. Это удобно при построении однотипных вычислений по строкам и столбцам, но становится проблемой, когда часть данных должна оставаться неизменной.

Типичный пример — таблица с фиксированным коэффициентом или ставкой. Пусть в ячейке D1 находится ставка НДС, а в столбце A — суммы без НДС. Формула =A2D1, скопированная вниз, станет =A3D2, =A4*D3 и далее, что приведёт к неверным результатам. Для корректной работы ссылка на D1 должна быть зафиксирована.

¶Способы фиксации ячейки

¶Запись символа доллара вручную

Самый прямой способ — ввести знак $ перед адресом строки или столбца непосредственно при написании формулы:

¶Клавиша F4

Быстрый способ переключить тип ссылки — выделить адрес ячейки в строке формул и нажать F4. Excel последовательно перебирает четыре варианта: A1 → $A$1 → A$1 → $A1 → A1. Повторные нажатия возвращают исходное состояние. На некоторых ноутбуках комбинация может требовать одновременного нажатия Fn+F4.

¶Выделение ячейки курсором

При редактировании формулы клик по ячейке листа вставляет её адрес. Если в момент клика нажата клавиша F4 (удерживается или нажимается сразу после), адрес вставляется сразу в абсолютном виде.

¶Смешанные ссылки

Когда фиксировать нужно только одну часть адреса, применяются смешанные ссылки. Распространённый сценарий — таблица умножения или расчёт итогов по матрице. Пусть в строке 1 находятся коэффициенты B1:E1, а в столбце A — значения A2:A5. Формула в B2 записывается как =$A2*B$1: ссылка на столбец A зафиксирована, ссылка на строку 1 зафиксирована. При протягивании вправо и вниз все 16 ячеек корректно обращаются к нужным коэффициентам.

¶Именованные диапазоны как альтернатива

Excel поддерживает именованные диапазоны — пользовательские метки, присваиваемые ячейке или группе ячеек. Если выделить D1 и ввести в поле имени Ставка, формула =A2*Ставка при копировании не изменит ссылку на ставку. Именованные диапазоны сами по себе являются абсолютными, что устраняет необходимость помнить о символах $. Менеджер имён открывается сочетанием Ctrl+F3.

¶Ссылки между листами и книгами

Фиксация ячеек работает и для ссылок на другие листы. Запись ='Лист2'!$A$1 обращается к ячейке A1 листа «Лист2» независимо от того, куда копируется формула. Для ссылок на внешние книги формат выглядит как ='[Книга1.xlsx]Лист1'!$A$1. При фиксации таких ссылок знак $ ставится перед буквой столбца и номером строки после восклицательного знака.

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

Наиболее частая ошибка — забытая фиксация при протягивании формулы вниз, из-за чего ссылки «уезжают» и формула обращается к пустым ячейкам. Excel сигнализирует об этом ошибкой #ССЫЛКА!, если смещение уходит за пределы листа, но чаще результатом оказываются просто неверные числа без видимых признаков ошибки. Другая ошибка — избыточная фиксация: $A$1 там, где достаточно $A1, лишает формулу гибкости. Проверить поведение ссылок можно, скопировав формулу в соседнюю ячейку и убедившись, что адреса изменились (или не изменились) ожидаемым образом.

Источники:

• Microsoft Support. «Абсолютные и относительные ссылки в Excel»
• Excel Easy. «Референсы (ссылки) в Excel»
• Книга: Дж. Уокенбах. «Excel 2019. Библия пользователя»