Абсолютные ссылки в Microsoft Excel¶
Абсолютная ссылка на ячейку — это адрес ячейки в электронной таблице Microsoft Excel, при котором обе координаты (столбец и строка) закреплены символом доллара и не изменяются при копировании формулы в другие ячейки. В отличие от относительной ссылки, которая автоматически смещается при переносе формулы, абсолютная ссылка всегда указывает на одну и ту же ячейку, что необходимо при расчётах с фиксированными параметрами — курсами валют, ставками, константами и промежуточными итогами.
¶Виды ссылок в Excel
Excel поддерживает три типа ссылок на ячейки:
| Тип | Пример | Поведение при копировании |
|---|---|---|
| Относительная | A1 | Обе координаты смещаются пропорционально |
| Абсолютная | $A$1 | Обе координаты остаются неизменными |
| Смешанная | $A1 или A$1 | Закреплён только столбец или только строка |
Относительная ссылка — поведение по умолчанию. Если в ячейке D1 записана формула =A1+B1, а её скопировать в D2, формула автоматически превратится в =A2+B2. Это удобно при построении однотипных расчётов, но некорректно, когда один из операндов должен оставаться постоянным.
¶Способы фиксации ячейки
¶Автоматическое переключение клавишей F4
Самый быстрый способ — выделить адрес ячейки в строке формулы и нажать F4. Excel преобразует ссылку по циклу:
A1→$A$1(абсолютная);$A$1→A$1(закреплена строка);A$1→$A1(закреплён столбец);$A1→A1(относительная).
Повторные нажатия F4 возвращают адрес в исходное состояние. На некоторых ноутбуках функциональные клавиши по умолчанию переключают громкость и яркость, поэтому может потребоваться сочетание Fn+F4.
¶Ручной ввод знаков доллара
Символ $ можно вставить непосредственно при наборе формулы: $C$5 или $C5. Этот метод универсален и работает во всех версиях Excel, включая веб-версию и Google Таблицы.
¶Выбор ячейки в режиме правки
При редактировании формулы клик по ячейке листа вставляет относительную ссылку. Чтобы сразу получить абсолютную, достаточно нажать F4 после клика — адрес в формуле закрепится.
¶Типичные сценарии использования
Ставка НДС и другие константы. Ставка 20 % размещается в отдельной ячейке, например E1. Формула =B2*$E$1 при протягивании вниз сохраняет ссылку на E1, а ссылка на B2 последовательно меняется на B3, B4 и так далее.
Кросс-таблицы и матрицы. При умножении вектора значений на вектор заголовков закрепляются строка одного множителя и столбец другого: =$A3*B$2 — типичная конструкция таблицы умножения.
Итоговые формулы. Формула вида =B2/$B$10 вычисляет долю каждой строки от общего итога, который всегда находится в одной и той же ячейке.
Внешние источники данных. При ссылках на другой лист или книгу закрепляются обе координаты: ='Настройки'!$B$2.
¶Смешанные ссылки
Смешанные ссылки полезны при построении двумерных таблиц. Строка $ фиксирует строку, столбец $ — столбец. В формуле таблицы умножения =$A3*B$2 при протягивании по горизонтали меняется только B, по вертикали — только 3, а закреплённые компоненты остаются на месте.
¶Особенности и ограничения
При копировании формулы между разными книгами Excel сохраняет абсолютность ссылок, но при вставке листа в другую книгу ссылки могут преобразоваться в межкнижные. Клавиша F4 работает только в режиме редактирования формулы — при обычном выделении ячейки она не вызывает смены типа ссылки.
В Google Таблицах и LibreOffice Calc используется идентичный синтаксис с символом $, однако клавиша F4 там также поддерживается.
Источники:
- Документация Microsoft по работе с формулами и ссылками на ячейки
- «Excel для чайников» (Bill Jelen), Wiley
- «Microsoft Excel 2019: полный курс самоучитель» (Joan Lambert), Pearson