Абсолютные и относительные ссылки в Excel и Google Таблицах
Основы4 минРедакция Innovialabs
Коротко
Знак $ закрепляет часть адреса: $A$1 не меняется при копировании, A1 сдвигается целиком, $A1 закрепляет столбец, A$1 — строку; переключать варианты удобно клавишей F4.
Коротко: что делает знак $
Когда вы копируете формулу в другую ячейку, Excel и Google Таблицы пересчитывают адреса относительно нового места. Знак $ запрещает менять ту часть адреса, перед которой стоит.
| Ссылка | Что закреплено | При копировании вниз | При копировании вправо |
|---|---|---|---|
A1 | ничего | A2, A3… | B1, C1… |
$A$1 | столбец и строка | $A$1 | $A$1 |
$A1 | только столбец | $A2, $A3… | $A1 |
A$1 | только строка | A$1 | B$1, C$1… |
- Относительная ссылка (
A1) — по умолчанию. Подходит, когда формула повторяется построчно. - Абсолютная (
$A$1) — указывает на одну конкретную ячейку: курс, ставку НДС, процент скидки. - Смешанная (
$A1илиA$1) — нужна, когда формулу копируют и вниз, и вправо.
Пример 1: относительная ссылка при копировании вниз
В столбце B — количество, в столбце C — цена. В D2 считаем сумму:
=B2*C2
Протяните формулу вниз — в D3 она станет =B3*C3, в D4 — =B4*C4. Это и есть относительная ссылка: формула «помнит» не адрес, а смещение — «две ячейки левее и одна левее».
Пример 2: абсолютная ссылка на константу
Курс валюты лежит в ячейке F1, нужно пересчитать суммы из D в другую валюту. Если написать в E2 =D2*F1 и протянуть вниз, в E3 получится =D3*F2 — а F2 пустая, и результат станет нулём.
Правильно:
=D2*$F$1
Теперь при копировании меняется только D2, а курс всегда берётся из F1.
Пример 3: смешанные ссылки в таблице умножения
Классическая задача: в столбце A (A2:A10) — числа, в строке 1 (B1:J1) — тоже числа, в B2 нужна формула, которую можно растянуть на весь блок.
=$A2*B$1
$A2— столбец A закреплён, строка меняется: формула всегда берёт множитель из первого столбца.B$1— строка 1 закреплена, столбец меняется: множитель всегда из первой строки.
Тот же приём работает в реальных задачах: план по месяцам × коэффициент по регионам, цена × наценка для разных категорий.
Клавиша F4
Не нужно набирать $ вручную. Поставьте курсор на адрес в строке формул и нажмите F4 — вариант сменится по кругу:
A1 → $A$1 → A$1 → $A1 → A1
- Работает в Excel на Windows и в Google Таблицах. На ноутбуках иногда нужно нажать Fn + F4.
- В Excel на Mac обычно используется Cmd + T или Fn + F4 — зависит от настроек клавиатуры.
- Если выделить диапазон
A1:B5целиком, F4 закрепит обе его границы.
Типичные ошибки
- Константа без $. Самая частая: ссылка на курс, ставку или итог «уезжает» при протягивании, и половина столбца считается от пустых ячеек.
- Лишний $. Закрепили
$B$2там, где нужна построчная ссылка, — во всех строках один и тот же результат. - Закреплён не тот компонент.
A$1вместо$A1: при копировании вниз работает, вправо — ломается. Проверяйте формулу в дальнем углу диапазона. - Диапазон в ВПР или СУММЕСЛИ без $.
=ВПР(A2; Товары!A2:C100; 3; ЛОЖЬ)при копировании вниз превращается вA3:C101, и верхние строки справочника перестают находиться. НужноТовары!$A$2:$C$100. - Вырезание вместо копирования. При вырезании (Ctrl+X) формула переносится без пересчёта адресов. Это не ошибка, но поведение отличается от копирования — учитывайте его.
Как быстро проверить формулы
- Выделите ячейку в конце диапазона и посмотрите формулу: ссылки должны указывать туда, куда вы ожидаете.
- Включите режим показа формул: Ctrl + клавиша Ё (обратная кавычка) в Excel, в Google Таблицах — меню «Вид» → «Показать» → «Формулы».
- В Excel используйте «Влияющие ячейки» на вкладке «Формулы» — стрелки покажут, откуда формула берёт данные.
Альтернатива: именованные диапазоны и таблицы
Вместо $F$1 можно дать ячейке имя, например Курс, и писать =D2*Курс. Имя всегда указывает на одну ячейку, формулу легче читать. В Excel также удобны умные таблицы (Ctrl+T): формулы в них используют названия столбцов и заполняются автоматически, и думать про $ приходится реже.