Абсолютная ссылка в Excel — это ссылка со знаком доллара: $A$1. Знак $ закрепляет столбец и строку, и при копировании или протягивании формулы такая ссылка не меняется. Обычная, относительная ссылка A1 при копировании сдвигается вместе с формулой. Есть и смешанные ссылки: $A1 закрепляет только столбец, A$1 — только строку. Быстрее всего ставить знаки $ клавишей F4: поставьте курсор на ссылку в формуле и нажимайте F4, пока не получите нужный вариант.
| Ссылка | Тип | При копировании формулы |
|---|---|---|
| A1 | относительная | меняются и столбец, и строка |
| $A$1 | абсолютная | не меняется |
| $A1 | смешанная | столбец закреплён, строка меняется |
| A$1 | смешанная | строка закреплена, столбец меняется |
Относительные ссылки: формула «помнит» расстояние
По умолчанию все ссылки в Excel относительные. Формула хранит не сам адрес, а смещение: «ячейка на два столбца левее в той же строке». Поэтому, когда вы протягиваете формулу вниз, номера строк в ней растут, а при протягивании вправо меняются буквы столбцов.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Товар | Цена | Кол-во | Сумма |
| 2 | Ручка | 25 | 4 | 100=B2*C2 |
| 3 | Тетрадь | 60 | 3 | 180=B3*C3 |
| 4 | Папка | 140 | 2 | 280=B4*C4 |
В D2 введена формула =B2*C2 и протянута вниз. В D3 она сама превратилась в =B3*C3, в D4 — в =B4*C4: каждая строка считает свою цену и своё количество. Включите «Формулы», чтобы увидеть все три.
В английском Excel: =B2*C2
Это и есть главное удобство Excel: одну формулу пишут один раз и протягивают на тысячи строк. Но иногда сдвигаться не должна часть формулы — например, ссылка на ячейку с курсом валюты, ставкой НДС или итоговой суммой. Для этого и нужен знак $.
Абсолютные ссылки: знак $ закрепляет ячейку
Знак доллара перед буквой столбца запрещает менять столбец, перед номером строки — строку. Ссылка $E$2 при любом копировании остаётся ссылкой на E2. Типичный пример — перевести цены из долларов в рубли по курсу, который записан в одной ячейке.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Товар | Цена, $ | Цена, ₽ | Курс | |
| 2 | Ноутбук | 850 | 80750=B2*$E$2 | 95 | |
| 3 | Монитор | 120 | 11400=B3*$E$2 | ||
| 4 | Мышь | 45 | 4275=B4*$E$2 |
Цена в долларах — относительная ссылка B2, она сдвигается по строкам. Курс — абсолютная $E$2, во всех строках формула берёт одну и ту же ячейку. Поменяйте курс в E2, и пересчитается весь столбец.
В английском Excel: =B2*$E$2
А вот что получится, если забыть знак доллара:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Товар | Цена, $ | Цена, ₽ | Курс | |
| 2 | Ноутбук | 850 | 80750=B2*E2 | 95 | |
| 3 | Монитор | 120 | 0=B3*E3 | ||
| 4 | Мышь | 45 | 0=B4*E4 |
Ссылка на курс съехала вместе с формулой: в C3 стоит =B3*E3, а E3 пустая, поэтому результат 0. Если бы в столбце E был текст, получилась бы ошибка #ЗНАЧ!. Нули и ошибки в протянутой формуле — первый признак того, что где-то не хватает знака $.
В английском Excel: =B3*E3
Смешанные ссылки: $A1 и A$1
Иногда нужно закрепить только одно измерение. Ссылка $A1 не даёт уйти из столбца A, но строка меняется. Ссылка A$1 держится за строку 1, а столбец меняется. Смешанные ссылки нужны, когда формулу протягивают сразу и вниз, и вправо — например, в таблице «цена × количество».
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Цена \ Кол-во | 1 | 5 | 10 |
| 2 | 100 | 100=$A2*B$1 | 500=$A2*C$1 | 1000=$A2*D$1 |
| 3 | 250 | 250=$A3*B$1 | 1250=$A3*C$1 | 2500=$A3*D$1 |
| 4 | 400 | 400=$A4*B$1 | 2000=$A4*C$1 | 4000=$A4*D$1 |
В B2 введена одна формула =$A2*B$1 и протянута на весь блок. $A2 всегда берёт цену из столбца A своей строки, B$1 — количество из строки 1 своего столбца. С абсолютными ссылками во всех ячейках был бы один и тот же результат, а с относительными A2*B1 цена и количество уезжали бы вместе с формулой.
В английском Excel: =$A2*B$1
Как поставить знак $ клавишей F4
Набирать доллары вручную не нужно. Выделите ссылку в строке формул или поставьте на неё курсор в режиме редактирования (F2) и нажмите F4. Каждое нажатие переключает тип ссылки по кругу:
| Нажатие F4 | Ссылка | Что закреплено |
|---|---|---|
| 1 | $A$1 | столбец и строка |
| 2 | A$1 | только строка |
| 3 | $A1 | только столбец |
| 4 | A1 | ничего, исходная ссылка |
- Если выделить в строке формул всю формулу или её часть, F4 переключит сразу все ссылки в выделении.
- Диапазон A2:C100 переключается целиком: курсор на любой его части, F4 — и получится $A$2:$C$100.
- На ноутбуках F4 часто занята мультимедиа: нажимайте Fn+F4 или включите Fn Lock.
- В Excel для Mac вместо F4 используйте ⌘+T.
- Вне режима редактирования F4 делает другое — повторяет последнее действие. Если вместо долларов в ячейке что-то поменялось, нажмите Ctrl+Z и сначала войдите в ячейку.
Остальные полезные сочетания собраны в статье про горячие клавиши Excel.
Как закрепить ячейку в формуле Excel
«Закрепить ячейку в формуле», «зафиксировать ячейку», «сделать ссылку постоянной» — всё это про один и тот же знак $. Порядок такой:
- Выделите ячейку с формулой и нажмите F2 или щёлкните в строке формул.
- Поставьте курсор на ссылку, которую надо закрепить.
- Нажмите F4 один раз — ссылка станет абсолютной, например $E$2.
- Нажмите Enter и протяните формулу.
Не путайте это с командой Вид → Закрепить области: она закрепляет строки и столбцы на экране, чтобы шапка не уезжала при прокрутке, и на формулы никак не влияет.
Где нужны абсолютные ссылки: типовые формулы
Знак $ встречается в большинстве рабочих формул. Вот самые частые случаи.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Месяц | Продажи | Нарастающий итог | Доля |
| 2 | Январь | 120 | 120=СУММ($B$2:B2) | 30%=B2/$B$6 |
| 3 | Февраль | 80 | 200=СУММ($B$2:B3) | 20%=B3/$B$6 |
| 4 | Март | 150 | 350=СУММ($B$2:B4) | 37,5%=B4/$B$6 |
| 5 | Апрель | 50 | 400=СУММ($B$2:B5) | 12,5%=B5/$B$6 |
| 6 | Итого | 400=СУММ(B2:B5) |
Нарастающий итог: начало диапазона $B$2 закреплено, конец B2 относительный, поэтому диапазон растёт с каждой строкой — $B$2:B3, $B$2:B4… Доля: делим на итог в $B$6, который не должен сдвигаться. Столбец D — в процентном формате.
В английском Excel: =SUM($B$2:B4)
| A | B | |
|---|---|---|
| 1 | Имя | Номер вхождения |
| 2 | Анна | 1=СЧЁТЕСЛИ($A$2:A2;A2) |
| 3 | Олег | 1=СЧЁТЕСЛИ($A$2:A3;A3) |
| 4 | Анна | 2=СЧЁТЕСЛИ($A$2:A4;A4) |
| 5 | Ирина | 1=СЧЁТЕСЛИ($A$2:A5;A5) |
| 6 | Олег | 2=СЧЁТЕСЛИ($A$2:A6;A6) |
Тот же приём с СЧЁТЕСЛИ: функция считает, сколько раз имя встретилось с начала списка до текущей строки. Всё, что больше 1, — повторы.
В английском Excel: =COUNTIF($A$2:A4,A4)
- Таблица в ВПР: =ВПР(A2;$E$2:$F$100;2;0). Без долларов диапазон поиска при протягивании съезжает вниз, и последние строки возвращают #Н/Д.
- Условия в СУММЕСЛИМН и СЧЁТЕСЛИ: диапазоны закрепляют, критерий оставляют относительным: =СЧЁТЕСЛИ($A$2:$A$500;D2).
- Константы: курс, ставка НДС, процент скидки, норма — всё, что лежит в одной ячейке и используется во многих формулах.
- Условное форматирование: в формуле правила для выделения целых строк столбец закрепляют, а строку нет: =$C2="Оплачено".
Ссылки на другой лист и другую книгу
Ссылка на другой лист пишется с восклицательным знаком: =Лист2!B3. Если в имени листа есть пробелы или спецсимволы, имя берётся в апострофы: ='Продажи 2026'!B3. Знак $ ставится только перед столбцом и строкой: =Лист2!$B$3, перед именем листа он не нужен.
Ссылка на другую книгу добавляет имя файла в квадратных скобках: ='[Курсы.xlsx]Лист1'!$B$2. Когда вы щёлкаете по ячейке другой книги, Excel сам вставляет абсолютную ссылку — если её надо протягивать, уберите доллары клавишей F4. Что значат все эти символы, разобрано в статье про спецсимволы Excel.
Именованные ячейки вместо знака $
Вместо $E$2 можно дать ячейке имя. Выделите её, впишите имя, например Курс, в поле слева от строки формул и нажмите Enter. Теперь формула =B2*Курс читается без расшифровки, а имя всегда указывает на одну ячейку, как абсолютная ссылка. Именами удобно называть константы и справочные диапазоны для ВПР.
Ссылки в умных таблицах
Если данные оформлены как таблица (Ctrl+T), Excel пишет вместо адресов имена столбцов: =[@Цена]*[@Кол-во]. Такие ссылки заполняют весь столбец сами, и доллары в них не нужны. Но при протягивании формулы вправо ссылка на столбец таблицы сдвигается, как относительная. Чтобы закрепить её, повторите имя столбца как диапазон: =Продажи[[Цена]:[Цена]].
Стиль ссылок R1C1
Если вместо букв столбцов вы видите номера, а формулы выглядят как =R[-1]C*R2C5, включён стиль R1C1. В нём абсолютная ссылка — R2C5 (строка 2, столбец 5), относительная — R[-1]C[2] (на строку выше и на два столбца правее). Вернуть привычный вид: Файл → Параметры → Формулы, снимите галочку «Стиль ссылок R1C1».
Как скопировать формулу, не меняя ссылки
Знаки $ работают при копировании и протягивании. Если нужно перенести формулу как есть, без сдвига ссылок и без правки долларов, есть три способа:
- Через строку формул. Выделите текст формулы в строке формул, нажмите Ctrl+C, затем Esc, перейдите в нужную ячейку и вставьте. Текст вставится один в один.
- Ctrl+' (апостроф; в русской раскладке — Ctrl+Э). Копирует в активную ячейку формулу из ячейки над ней без изменения ссылок.
- Перемещение. Если вырезать ячейку (Ctrl+X) и вставить её в другое место, ссылки внутри формулы не изменятся — зато изменятся ссылки других формул на эту ячейку.
Для целого блока формул: замените через Ctrl+H знак «=» на «#=», чтобы формулы стали текстом, скопируйте блок, а потом верните «=» той же заменой в обоих местах.
Как сделать все ссылки абсолютными сразу
Для одной формулы: выделите её целиком в строке формул и нажмите F4. Для сотни формул быстрее макрос — он переводит ссылки во всех выделенных ячейках:
Sub MakeReferencesAbsolute()
' Делает абсолютными все ссылки в формулах выделенных ячеек
' Вместо xlAbsolute можно указать xlAbsRowRelColumn (A$1), xlRelRowAbsColumn ($A1) или xlRelative (A1)
Dim c As Range
For Each c In Selection.Cells
If c.HasFormula Then
c.Formula = Application.ConvertFormula(c.Formula, xlA1, xlA1, xlAbsolute)
End If
Next c
End Sub
Вставьте код в модуль (Alt+F11 → Insert → Module) и запустите на выделенном диапазоне. Чтобы макрос был доступен во всех книгах, сохраните его в личной книге макросов. Ограничение: ConvertFormula не обрабатывает формулы длиннее 255 символов, такие ячейки придётся поправить вручную.
Знак $ не защищает от вставки строк
Абсолютная ссылка не меняется только при копировании. Если вставить строку над E2 или удалить столбец, Excel перепишет и $E$2, чтобы ссылка продолжала указывать на ту же самую ячейку с курсом, теперь уже $E$3. Обычно это то, что нужно.
Если же формула должна всегда смотреть именно на адрес E2, что бы ни происходило с листом, используйте функцию ДВССЫЛ: =ДВССЫЛ("E2"). Адрес записан текстом, и Excel его не трогает.
Почему формула считает неправильно после протягивания: частые ошибки
- Внизу столбца нули или #Н/Д. Не закреплена ссылка на константу или диапазон поиска: поставьте $ клавишей F4.
- Все строки показывают одно и то же. Закреплено лишнее: например, $B$2 вместо B2 у значения, которое должно меняться по строкам.
- При протягивании вправо таблица «поехала». Нужна смешанная ссылка: $A2, если столбец должен остаться на месте.
- F4 не ставит доллары. Вы не в режиме редактирования (нажмите F2) или на ноутбуке нужно Fn+F4; на Mac — ⌘+T.
- Формула показывается текстом, а не результатом. Ячейка в текстовом формате или перед = стоит пробел или апостроф. Подробнее — в статье про апостроф и знак равенства в начале ячейки.
- Доллары есть, но ссылка всё равно изменилась. На листе вставляли или удаляли строки: знак $ от этого не защищает, нужен ДВССЫЛ.
Абсолютные и относительные ссылки: вопросы и ответы
Что означает знак $ в формуле Excel?
Знак доллара закрепляет часть ссылки при копировании формулы: $A$1 — столбец и строку, $A1 — только столбец, A$1 — только строку. К валюте он отношения не имеет.
Как сделать абсолютную ссылку в Excel?
Поставьте курсор на ссылку в формуле и нажмите F4: A1 превратится в $A$1. Или впишите знаки $ вручную.
Чем относительная ссылка отличается от абсолютной?
Относительная ссылка при копировании формулы сдвигается на столько же строк и столбцов, на сколько сдвинули формулу. Абсолютная всегда указывает на одну и ту же ячейку.
Что такое смешанная ссылка?
Ссылка, в которой закреплена только одна часть: $A1 (столбец) или A$1 (строка). Нужна, когда формулу протягивают и вниз, и вправо.
Как закрепить ячейку в формуле на ноутбуке?
Нажмите Fn+F4 в режиме редактирования формулы. В Excel для Mac — ⌘+T.
Как скопировать формулу без изменения ссылок?
Скопируйте текст формулы из строки формул, нажмите Esc и вставьте в нужную ячейку. Или нажмите Ctrl+' (Ctrl+Э в русской раскладке) в ячейке под формулой.
Работаете с большими таблицами? Удалить, извлечь, заменить или посчитать что угодно в тысячах ячеек можно и без формул — процедурами !SEMTools.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: абсолютная и относительная адресация в Excel
| Задача | Как сделать |
|---|---|
| Ссылка должна сдвигаться за формулой | A1 — относительная, по умолчанию |
| Ссылка не должна меняться при копировании | $A$1 — F4 один раз |
| Закрепить только строку | A$1 — F4 два раза |
| Закрепить только столбец | $A1 — F4 три раза |
| Растущий диапазон | $B$2:B2 |
| Ссылка без $, но постоянная | имя ячейки или ДВССЫЛ("A1") |
Надеюсь, на этой странице вы нашли то, что искали, и теперь уверенно расставляете знаки $ в формулах. Что ещё означают доллар, восклицательный знак, кавычки и квадратные скобки в формулах, читайте в статье про специальные символы Excel.