Функции и формулы
Абсолютные, относительные и смешанные ссылки в Excel
Ссылки в Excel — это адреса ячеек внутри формулы для тех, кто протягивает расчёт на всю таблицу и хочет, чтобы курс или ставка не сдвигались. По умолчанию адрес относительный и при копировании меняется, а знак $ закрепляет столбец, строку или оба сразу[1]. Ниже — виды адресов, клавиша F4, таблица умножения, пример с курсом и случаи, где доллар не помогает.
Формулу пишут один раз, а потом протягивают на сотни строк, и каждая копия должна смотреть на правильные ячейки. Справка Microsoft напоминает, что по умолчанию ссылка на ячейку относительная и при копировании формулы меняется[1]. Если курс валюты или ставка налога стоят в одной ячейке, такая ссылка уезжает вместе с формулой, и расчёт ломается уже во второй строке.
Путь к надёжной формуле короткий: найти ячейку-константу вроде курса или ставки, ввести формулу со ссылкой на неё, проверить после копирования, куда сдвинулись адреса, исправить ссылку знаком доллара или клавишей F4 и протянуть формулу на свои данные. Ссылки в Excel после этого ведут себя предсказуемо в любой строке таблицы.
Ключевые факты: ссылки в Excel
- Ссылка на ячейку по умолчанию относительная и меняется при копировании формулы[1].
- При копировании из A1 на две строки вниз и два столбца вправо адрес A1 становится C3, а $A$1 остаётся $A$1[1].
- Клавиша F4 переключает типы ссылок в выделенном адресе, а в Excel для Mac то же делает Command+T[2].
- Смешанная ссылка отличается от абсолютной тем, что фиксирует только столбец $A1 или только строку A$1[4].
- Знак доллара не меняет значение ячейки и действует только при копировании формулы[4].
Относительная, абсолютная и смешанная ссылка: синтаксис со знаком $
Ссылка указывает формуле, из какой ячейки брать значение, а знак $ закрепляет часть адреса при копировании. Относительная ссылка A1 меняется целиком, абсолютная $A$1 не меняется, смешанная фиксирует только столбец $A1 или только строку A$1[4].
Справка Microsoft показывает разницу на одной формуле, скопированной из A1 на две строки вниз и два столбца вправо[1]:
| Ссылка | Вид | Что закреплено | После копирования |
|---|---|---|---|
A1 | относительная | ничего | C3 |
$A$1 | абсолютная | столбец и строка | $A$1 |
A$1 | смешанная | только строка | C$1 |
$A1 | смешанная | только столбец | $A3 |
Доллар перед буквой закрепляет столбец, перед цифрой — строку. На значение ячейки знак не влияет: =$B$1 и =B1 вернут одно и то же число, разница появится только при копировании. По стилю A1 столбцы обозначаются буквами от A до XFD, то есть до 16 384 столбцов, а строки — номерами от 1 до 1 048 576[4].
Ссылки в Excel пишут не только на одну ячейку, но и на диапазон: A10:A20 — часть столбца, B15:E15 — часть строки, H:H — весь столбец[4]. Доллары в диапазоне ставят у обеих границ, например $F$2:$G$4, иначе при протягивании «поплывёт» одна из них. Такая запись нужна справочникам и прайсам, к которым обращается каждая строка отчёта.
Разобравшись с записью, переходим к тому, как поставить доллары быстро, не набирая их вручную.
Как закрепить ячейку в формуле клавишей F4
Закрепить ячейку в формуле быстрее всего клавишей F4: выделите ссылку в строке формул и нажмите клавишу, чтобы переключать типы ссылок[2]. Справка относит этот способ к Excel 2016, Excel 2019, Excel 2021, Excel 2024, Microsoft 365 и Excel Web App[2].
- Введите в B3 начало формулы
=A3*B1, не нажимая Enter. - Поставьте курсор на ссылку B1 в строке формул.
- Нажмите F4 и проверьте результат: ссылка превратится в
$B$1. - Нажимайте F4 ещё, если нужна смешанная ссылка с одним долларом.
- Нажмите Enter и протяните формулу вниз — адрес курса останется на месте.
На Mac сочетание другое: выделите ссылку в строке формул и нажмите Command+T, чтобы перебирать варианты[1]. В Google Таблицах при вводе формулы работает F4, а на Mac — Fn+F4[5].
Посмотреть, на какие ячейки смотрит готовая формула, тоже просто. Дважды щёлкните ячейку с формулой: каждая ссылка выделится своим цветом, а цветную рамку можно перетащить мышью, и адрес в формуле изменится вслед за ней[3]. Так ошибку видно до того, как формулу протянули на всю таблицу. Ссылки в Excel на соседние ячейки при этом подсвечиваются разными цветами, и по рамкам сразу понятно, какая из них сдвинулась. Закрепите курс и протяните формулу в тренажёре: урок проверяет, что после копирования адрес константы не сдвинулся.
Однако полное закрепление нужно не всегда: в таблицах с двумя осями один доллар работает лучше двух.
Смешанные ссылки в Excel или абсолютные: чем отличается таблица умножения
Смешанные ссылки в Excel отличаются от абсолютных тем, что фиксируют одну ось, а вторую оставляют свободной[4]. Такой адрес нужен, когда одну формулу копируют и вниз, и вправо: множитель по строкам берётся из столбца A, множитель по столбцам — из строки 8.
| A | B | C | D | |
|---|---|---|---|---|
| 8 | 1 | 2 | 3 | |
| 9 | 1 | =$A9*B$8 → 1 | ||
| 10 | 2 | =$A10*C$8 → 4 | ||
| 11 | 3 | =$A11*D$8 → 9 |
В B9 стоит формула =$A9*B$8. При копировании вправо $A9 не меняет столбец, а B$8 меняет столбец и держит строку. При копировании вниз всё наоборот: строка у $A9 растёт, а B$8 остаётся в строке 8. Одна формула заполняет всю таблицу. Смешанную ссылку ставят той же клавишей F4: повторные нажатия меняют тип ссылки, пока в адресе не останется один доллар в нужном месте[2].
| Задача | Ссылка | Почему |
|---|---|---|
| курс или ставка в одной ячейке | $B$1 | не должна сдвигаться никуда |
| множитель из первого столбца | $A9 | столбец закреплён, строка меняется |
| множитель из первой строки | B$8 | строка закреплена, столбец меняется |
| обычный расчёт по строке | A3 | сдвигается вместе с формулой |
Ссылки в Excel с полным закреплением $A$9 и $B$8 здесь не сработают: каждая копия умножала бы 1 на 1. Без долларов вовсе формула при копировании уедет от заголовков и начнёт умножать соседние произведения. Проверить таблицу удобно по диагонали: там должны стоять квадраты чисел. Тот же приём работает для любой сетки «строка × столбец»: цена по тарифу и месяцу, норма по смене и участку.
Остаётся понять, что именно ломается, если доллар забыли поставить там, где он нужен.
Пример с курсом валют: что ломается без знака доллара
Ссылка на курс без знака доллара сдвигается вниз вместе с формулой, и уже вторая строка умножает сумму не на курс, а на соседнюю ячейку. Пример: курс 92,5 стоит в B1, суммы в долларах — в A3:A5.
| A | B | |
|---|---|---|
| 1 | Курс | 92,5 |
| 2 | Сумма, $ | Сумма, ₽ |
| 3 | 100 | =A3*B1 → 9250 |
| 4 | 250 | =A4*B2 → #ЗНАЧ! |
| 5 | 40 | =A5*B3 → 370000 |
Первая строка верна. Во второй адрес курса сдвинулся на B2, где стоит заголовок, и формула вернула #ЗНАЧ!. В третьей ссылка попала на B3 с результатом первой строки, и сумма превратилась в 370000 — без всякой ошибки, что опаснее всего. Такую подмену выдаёт только проверка ссылок: двойной щелчок по B5 покажет цветную рамку вокруг B3 вместо B1.
| A | B | |
|---|---|---|
| 3 | 100 | =A3*$B$1 → 9250 |
| 4 | 250 | =A4*$B$1 → 23125 |
| 5 | 40 | =A5*$B$1 → 3700 |
С долларами адрес курса не меняется ни в одной копии, и ссылки в Excel на курс одинаковы во всех строках. Константу лучше держать в отдельной ячейке и ссылаться на неё, чем вписывать число в формулу: справка Microsoft советует выносить константы, чтобы их было проще менять[4].
| Симптом | Причина | Что сделать |
|---|---|---|
| #ЗНАЧ! во второй строке | адрес константы сдвинулся на заголовок | закрепить адрес знаком доллара |
| неправдоподобно большие числа ниже | ссылка попала на результат соседней строки | проверить ссылку двойным щелчком |
| итог доли больше 100% | делитель не закреплён | поставить $ перед строкой итога |
Именно поэтому полезно знать и границы знака доллара: есть задачи, где он ничего не решает.
Когда знак $ не поможет: прокрутка, стиль R1C1 и много констант
Знак $ в ссылке не поможет, если задача не в копировании формулы: доллар отвечает только за поведение при копировании[4]. Вот случаи, где нужен другой инструмент.
- Когда строку заголовков нужно видеть при прокрутке, доллар ничего не закрепит на экране — это отдельная команда, она названа во врезке ниже.
- Если включён стиль R1C1, долларов нет вовсе: абсолютный вариант выглядит как
R2C2, а относительный — какR[-2]C[4]. Стиль переключают в параметрах Excel в разделе «Формулы»[4]. - Когда нужна ячейка с соседнего листа, перед буквой столбца пишут имя листа и восклицательный знак, например
=Лист2!B2[3]. - Если констант много — курс, ставка налога, норма, — записи с долларами трудно читать: ячейке удобнее дать понятное имя.
Закреплённые ссылки чаще всего встречаются в процентах и долях — пример с делением на итог есть на странице о расчёте долей и процентов. Остальные приёмы работы с формулами собраны в обзоре формул Excel для работы с данными.
Частые вопросы
Как закрепить ячейку в формуле в Excel?
Ячейку в формуле закрепляют знаком доллара: выделите её в строке формул и нажмите F4, получится $B$1[2]. Ссылки в Excel с долларами не сдвигаются при протягивании, а в экселе для Mac вместо F4 нажимают Command+T[1].
Чем абсолютная ссылка отличается от относительной?
Абсолютная ссылка $A$1 отличается тем, что при копировании не меняется, а относительная A1 сдвигается вместе с формулой[4]. Относительный вариант стоит по умолчанию.
Что такое смешанная ссылка?
Смешанная ссылка — это запись, где закреплена только строка A$1 или только столбец $A1[4]. Она нужна, когда формулу копируют и вниз, и вправо, как в таблице умножения.
Почему F4 не ставит доллары?
Клавиша F4 не ставит доллары, если курсор стоит не внутри обозначения ячейки или вы работаете в Excel для Mac: там нужен Command+T[1]. Поставьте курсор на B1 в строке формул и нажмите клавишу ещё раз.
Что происходит с абсолютными ссылками при копировании формулы?
При копировании $A$1 не меняется и остаётся $A$1 в любой ячейке[1]. Сдвигаются только буква или номер без доллара.
Источники
- [1] Microsoft, справка «Переключение между относительными и абсолютными ссылками», 2026
- [2] Microsoft, справка «Switch between relative, absolute, and mixed references», 2026
- [3] Microsoft, справка «Создание и изменение ссылки на ячейку», 2026
- [4] Microsoft, справка «Общие сведения о формулах в Excel», 2026
- [5] Google, справка «Keyboard shortcuts for Google Sheets - Computer - Google Docs Editors Help», 2026