Абсолютные и смешанные ссылки
Один знак доллара решает, будет ли скопированная формула по-прежнему смотреть на ставку налога в B1 или съедет вниз на пустую ячейку.
Почему столбец налога ломается после одного заполнения
Начнём с загадки. Ставка налога, 10%, стоит в B1, а четыре цены стоят в ячейках от A4 до A7. Кто-то вводит в B4 формулу =A4*B1, получает 2, и это верно, а затем протягивает её вниз до B7. Теперь в B5 стоит 0, в B6 стоит #VALUE!, а в B7 стоит 25. Прежде чем читать дальше, попробуй объяснить, почему ошиблась каждая из этих трёх ячеек.
Щёлкни B5 и прочитай формулу: =A5*B2. При заполнении вниз каждая обычная ссылка сдвинулась на одну строку. Ссылка на цену перешла с A4 на A5, как и нужно, но ссылка на ставку тоже перешла с B1 на B2, а B2 пустая. При умножении пустая ячейка считается нулём, поэтому B5 молча показывает 0. B6 теперь умножает на B3, где стоит заголовок Tax; на странице Microsoft об ошибке #VALUE! сказано, что операторы вроде + и * могут не суметь вычислить ячейки с текстом, отсюда и ошибка. B7 умножает на B4, первый результат налога, и получает 12.5 × 2 = 25. Этот ответ выглядит правдоподобно, и потому он хуже всех трёх. Одно заполнение, три неверных ответа, и только один из них похож на ошибку.
Исправление такое: сказать таблице, что B1 никогда не должна сдвигаться. Знак доллара перед частью адреса закрепляет эту часть. $B$1 закрепляет и столбец, и строку, и это абсолютная ссылка: она означает B1, куда бы ни попала формула. Запиши первый налог как =A4*$B$1, протяни вниз и получишь =A5*$B$1, =A6*$B$1 и так далее. Собственный пример Microsoft: формула =$B$4*$C$4 остаётся точно такой же при копировании из D4 в D5. Знак не имеет отношения к деньгам. Это просто метка.
Ставка в отдельной ячейке окупается ещё раз. Если ставка меняется с 10% на 12%, ты правишь B1, и все строки сразу пересчитываются. Если бы ставка была вписана в каждую формулу как *0.1, пришлось бы искать каждую копию, а пропущенная оставила бы неверное число, которое выглядит совершенно обычно. Поэтому всё, что может измениться (ставки, лимиты, курсы валют), ставь в подписанные ячейки наверху листа. Ссылайся на них абсолютными ссылками.
Перетащи, чтобы повернуть. Колёсико или щипок для масштаба.
Закрепляем только строку или только столбец
Можно закрепить только одну часть. В A$1 закреплена строка, а столбец сдвигается; в $A1 наоборот. Таблица Microsoft копирует каждую форму на две ячейки вниз и на две вправо: $A$1 остаётся $A$1, A$1 превращается в C$1, $A1 в $A3, а A1 в C3. Вручную знаки вводят редко. Во время правки формулы поставь курсор на ссылку и нажми F4. Каждое нажатие переводит к следующей форме (A1, $A$1, A$1, $A1), а затем снова к A1. В списке сочетаний клавиш Google для Таблиц тоже указана F4, а на клавиатуре Mac Fn+F4.
| Ссылка | Столбец | Строка | Скопировано на 2 вниз и 2 вправо |
|---|---|---|---|
| A1 | сдвигается | сдвигается | C3 |
| $A$1 | закреплён | закреплён | $A$1 |
| A$1 | сдвигается | закреплён | C$1 |
| $A1 | закреплён | сдвигается | $A3 |
Где смешанные ссылки по-настоящему полезны? В сетке, где одни входные данные идут по верхнему краю, а другие по левому, и классический пример здесь таблица умножения. Поставь числа от 1 до 5 в B1:F1 и ещё раз в A2:A6, затем запиши в B2 формулу =B$1*$A2. Протяни её вправо до F2 и вниз до F6. Каждая из двадцати пяти копий по-прежнему берёт верхнее число из строки 1, а боковое из столбца A. Одна формула правильно заполняет всю сетку.
Перетащи, чтобы повернуть. Колёсико или щипок для масштаба.
- Протягивать =A4*B1 вниз без знака $. Ссылка на ставку съезжает на B2, B3 и B4 вместе с ценами, и вместо трёх налогов получаются 0, #VALUE! и неверное 25.
- Ставить $ не на ту часть: =A4*B$1 протягивается вниз правильно, а =A4*$B1 нет. $B1 закрепляет только столбец, поэтому строка всё ещё сдвигается. Реши, в какую сторону будешь копировать формулу, и закрепи ту часть, которая сдвинулась бы в эту сторону.
- Вписывать ставку в каждую формулу как *0.1. Это работает, пока ставка не изменится. Тогда каждую копию нужно найти и исправить, а пропущенная по-прежнему выглядит нормально.
- Нажимать F4 в Google Таблицах вне формулы и ждать появления $. F4 переключает тип ссылки только во время ввода формулы. В остальных случаях это одна из клавиш повтора действия (Redo).
- Формулу =A4*$B$1 копируют на одну строку вниз. Во что она превращается?
- В строке формул курсор стоит на A1. Ты один раз нажимаешь F4. Во что превращается A1?
- А после второго нажатия F4?
- Формулу =A$1 копируют на две ячейки вниз и на две вправо. Во что она превращается?
- Формулу =$A1 копируют на две ячейки вниз и на две вправо. Во что она превращается?
- Формулу =B$1*$A2 из B2 копируют в C3. Во что она превращается?
- Какое значение даёт в этой таблице =E$1*$A5?
- В B1 стоит 10%, а в B4 формула =A4*B1. Ты протягиваешь B4 вниз до B7. Что идёт не так?
- Ссылка на ставку съезжает с B1, поэтому B5 показывает 0, и строки ниже тоже ошибаются
- Во всех строках #REF!
- Во всех строках одна и та же цена
- Всё в порядке
- Какая ссылка закрепляет строку, но позволяет столбцу сдвигаться?
- A$1
- $A1
- $A$1
- A1
- Какая клавиша переключает тип ссылки на Mac в Google Таблицах?
- Fn+F4
- Ctrl+D
- Ctrl+~
- Shift+F2
- $ закрепляет часть, стоящую после него: $A$1 обе части, A$1 строку, $A1 столбец.
- Используй $B$1 для одной входной ячейки, общей для всех строк.
- F4 перебирает A1, $A$1, A$1, $A1 (в Google Таблицах на Mac Fn+F4).
- Смешанные ссылки позволяют одной формулой заполнить целую сетку.
Источники курса (19)
- Microsoft Support, "Switch between relative, absolute, and mixed references" (support.microsoft.com/en-us/office/dfec08cd-ae65-4f56-839e-5f0d8d0baca9: $ before column and row, F4 to switch, copying two down and two right: $A$1 stays, A$1 to C$1, $A1 to $A3, A1 to C3)
- Microsoft Support, "AVERAGE function" (support.microsoft.com/en-us/office/047bac88-d466-426c-a32b-8f33eb960cf6: text, logical values and empty cells in a reference ignored, zeros included; 10, 7, 9, 27, 2 gives 11, with 5 gives 10; mean 5, median 4, mode 3 for 2 3 3 5 7 10)
- Microsoft Support, "COUNT function" (support.microsoft.com/en-us/office/a59cd7fc-b623-4d93-87a4-d23bf411294c: only numbers counted, dates count, errors and text do not)
- Microsoft Support, "ROUND function" (support.microsoft.com/en-us/office/c018c5d8-40fb-4053-90b1-b3e7f61a213c: num_digits required; 2.15 to 2.2, -1.475 to -1.48, 626.3 to 1000, 1.98 to 0, -50.55 to -100)
- Microsoft Support, "IF function" (support.microsoft.com/en-us/office/69aed7c9-4e8a-4755-a9bc-aa8bbff73be2: value_if_false optional, text in quotes except TRUE and FALSE, 0 and #NAME? problems)
- Microsoft Support, "VLOOKUP function" (support.microsoft.com/en-us/office/0bbc8083-26fe-4963-8ab8-93a18ad188a1: range_lookup TRUE by default, sorted first column, #N/A, #REF!, #VALUE!, absolute table range, wildcards with FALSE)
- Microsoft Support, "XLOOKUP function" (support.microsoft.com/en-us/office/b7fd680e-6d10-43e6-84f9-88eae8bf5929: not in Excel 2016 and Excel 2019, exact match by default)
- Microsoft Support, "How to correct a #VALUE! error" (support.microsoft.com/en-us/office/15e1b616-fbf2-4147-9c0b-0a11a20e409e: + and * may not calculate cells with text, use SUM instead, IFERROR hides all errors)
- Microsoft Support, "Excel specifications and limits" (15 digits, 64 nested levels, 255 arguments)
- Google Docs Editors Help, "VLOOKUP" (support.google.com/docs/answer/3093318: is_sorted TRUE by default, FALSE recommended, search down to a larger value, " Apple" and "Apple " differ, wildcards, IFNA, absolute range)
- Google Docs Editors Help, "XLOOKUP" (support.google.com/docs/answer/12405947)
- Google Docs Editors Help, "ROUND" (support.google.com/docs/answer/3093440: places optional, 0 by default, rounding by magnitude)
- Google Docs Editors Help, "IF" (support.google.com/docs/answer/3093364: order of the answers is the most common problem, IFS)
- Google Docs Editors Help, "AVERAGE" (support.google.com/docs/answer/3093615: text ignored, any number of arguments)
- Google Docs Editors Help, "COUNTA" (support.google.com/docs/answer/3093991: zero-length strings and spaces count)
- Google Docs Editors Help, "Keyboard shortcuts for Google Sheets" (support.google.com/docs/answer/181110: F4 and Fn+F4 while entering a formula, F4 as Redo)
- Pryor Learning and Exceljet (F4 cycle A1, $A$1, A$1, $A1; = comparison not case-sensitive)
- Ablebits (IF without value_if_false returns FALSE)
- Spreadsheet Class (Sheets circular dependency shows #REF!)
Проверено: 2026-10-19