Абсолютная ссылка оставляет ячейку на месте при копировании формулы. В =B2*$E$1 знаки доллара закрепляют E1: протяните формулу вниз, и каждая строка по-прежнему умножает на E1, а B2 сдвигается на B3, B4 и так далее. Чтобы поставить знаки доллара, щёлкните ссылку в формуле и нажмите F4 (на Mac Cmd+T).
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Commission | Rate | 5% |
| 2 | Ana | $12,000 | $600 | ||
| 3 | Ben | $8,500 | $425 | ||
| 4 | Chen | $15,200 | $760 | ||
| 5 | Dina | $9,800 | $490 | ||
| 6 | Eli | $11,000 | $550 |
=B2*$E$1C2 набрана один раз и протянута вниз. Щёлкните C4: её формула =B4*$E$1. Ячейка с продажами сдвинулась на строку 4, а ставка осталась в E1. Замените ставку в E1 на 8%, и обновятся все комиссии.
Относительные и абсолютные ссылки
| Ссылка | Тип | После копирования на строку ниже и на столбец правее |
|---|---|---|
A1 | относительная | B2 |
$A$1 | абсолютная | $A$1 |
A$1 | смешанная: закреплена строка | B$1 |
$A1 | смешанная: закреплён столбец | $A2 |
Обычная ссылка вроде B2 относительная: Excel хранит её как «ячейку на таком-то расстоянии от меня», поэтому копия на строку ниже указывает на строку ниже. Для данных по строкам это именно то, что нужно, и так устроено по умолчанию. Знак $ перед буквой столбца или номером строки закрепляет эту часть.
Классическая ошибка: протянуть вниз без $
Вот снова таблица комиссий, с =B2*E1 в C2 и без знаков доллара. Первая строка верна. Остальные равны 0.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Commission | Rate | 5% |
| 2 | Ana | $12,000 | $600 | ||
| 3 | Ben | $8,500 | $0 | ||
| 4 | Chen | $15,200 | $0 | ||
| 5 | Dina | $9,800 | $0 | ||
| 6 | Eli | $11,000 | $0 |
=B3*E2В C3 стоит =B3*E2: ссылка на ставку сдвинулась вниз на E2, которая пуста, а пустая ячейка считается нулём. Исправьте это здесь: щёлкните C2, замените формулу на =B2*$E$1 и нажмите Enter. Весь столбец исправится, потому что C3:C6 это копии C2. Когда закреплённая ячейка делитель, как в =B2/B7 для доли от итога, та же ошибка показывает #ДЕЛ/0! (по-английски #DIV/0!, именно так ошибку покажет таблица) вместо 0 (обычный случай это процент от итога).
Клавиша F4 ставит знаки доллара
При вводе или редактировании формулы поставьте курсор в ссылку (или сразу после неё) и нажмите F4. Каждое нажатие переходит к следующему виду:
E1 -> $E$1 -> E$1 -> $E1 -> E1
На многих ноутбуках F4 управляет экраном или звуком, тогда нажимайте Fn+F4. На Mac используйте Cmd+T или Fn+F4. Знаки $ можно набрать и вручную.
Смешанные ссылки: закрепить только строку или только столбец
У смешанной ссылки один знак доллара. $A2 всегда читает столбец A, но позволяет строке двигаться; B$1 всегда читает строку 1, но позволяет двигаться столбцу. Если обе стоят в одной формуле, одна формула, протянутая по сетке, строит таблицу умножения:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | x | 1 | 2 | 3 | 4 | 5 |
| 2 | 1 | 1 | 2 | 3 | 4 | 5 |
| 3 | 2 | 2 | 4 | 6 | 8 | 10 |
| 4 | 3 | 3 | 6 | 9 | 12 | 15 |
| 5 | 4 | 4 | 8 | 12 | 16 | 20 |
| 6 | 5 | 5 | 10 | 15 | 20 | 25 |
=$A2*B$1В B2 стоит =$A2*B$1. Щёлкните F6: там =$A6*F$1, номер строки из столбца A, умноженный на номер столбца из строки 1, поэтому ячейка показывает 25. Уберите один знак доллара в B2, и таблица развалится, потому что копии начнут умножать соседние ячейки вместо заголовков.
Тот же приём считает цены списка при нескольких скидках: =$A2*(1-B$1), цены идут вниз по столбцу A, а скидки вправо по строке 1.
Нарастающий итог с наполовину закреплённым диапазоном
Диапазон можно закрепить только с одного конца. =СУММ($B$2:B2) всегда начинается с B2, а её конец сдвигается вниз при протягивании, так что каждая строка складывает всё до себя включительно. Таблицы на этой странице показывают английскую запись, но формулы в них можно вводить и по-русски, с точкой с запятой.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | Total so far |
| 2 | Jan | 420 | 420 |
| 3 | Feb | 380 | 800 |
| 4 | Mar | 510 | 1310 |
| 5 | Apr | 450 | 1760 |
| 6 | May | 470 | 2230 |
=СУММ($B$2:B2)В C6 стоит =SUM($B$2:B6), то есть =СУММ($B$2:B6), и ячейка показывает 2230, итог всех пяти месяцев. Тот же наполовину закреплённый диапазон позволяет формуле =СЧЁТЕСЛИ($A$2:A2;A2) считать, сколько раз значение уже встретилось, и так помечаются повторы после первого.
Практика: одна формула на всю таблицу
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Price | 10% | 20% | 30% |
| 2 | $40.00 | |||
| 3 | $25.00 | |||
| 4 | $60.00 | |||
| 5 | $18.00 |
Ваша очередь: В B2 напишите цену первого товара после скидки из B1. Поставьте $ так, чтобы та же формула, протянутая вправо и вниз до D5, дала все цены таблицы.
Таблица копирует вашу формулу в каждую ячейку B2:D5, как это сделал бы маркер заполнения, а проверка читает все двенадцать результатов. Без правильных знаков доллара копии в строке 3 или в столбце C возьмут не ту цену или не ту скидку.
Абсолютные ссылки на другой лист или таблицу поиска
Со знаками доллара и именем листа всё работает так же: =B2*Settings!$B$1. Важнее всего они при поиске, где таблица должна стоять на месте, а искомое значение двигаться: =ВПР(A2;$E$2:$F$10;2;ЛОЖЬ), протянутая вниз, продолжает искать в E2:F10, а =ВПР(A2;E2:F10;2;ЛОЖЬ) сдвигает таблицу на строку вниз с каждой копией и начинает пропускать первые строки (ВПР). Если закреплённая ячейка используется во многих формулах, можно также дать ей имя через Формулы > Присвоить имя и писать =B2*Rate; имя, заданное так, указывает на одну и ту же ячейку из любой формулы, как $E$1.
Часто задаваемые вопросы
Что означает знак $ в формуле Excel?
Он закрепляет ту часть ссылки, перед которой стоит. В $E$1 закреплены и столбец E, и строка 1, поэтому ссылка остаётся E1, куда бы формулу ни скопировали. E$1 закрепляет только строку, а $E1 только столбец.
Какая горячая клавиша для абсолютной ссылки в Excel?
Щёлкните внутри ссылки при редактировании формулы и нажмите F4 (на многих ноутбуках Fn+F4). Каждое нажатие переключает $A$1, A$1, $A1 и A1 по кругу. На Mac нажмите Cmd+T или Fn+F4.
Чем относительная ссылка отличается от абсолютной?
Относительная ссылка вроде B2 сдвигается при копировании формулы: на строку ниже она становится B3. Абсолютная ссылка вроде $B$2 остаётся $B$2. Используйте абсолютные ссылки для одной ячейки, которая нужна каждой строке, например ставки или итога.
Что такое смешанная ссылка в Excel?
Ссылка с одним знаком доллара: $A2 закрепляет столбец, а строке разрешает двигаться, B$1 закрепляет строку, а столбцу разрешает двигаться. =$A2*B$1, протянутая вправо и вниз по сетке, строит таблицу умножения.
Почему формула показывает 0 или #ДЕЛ/0! после протягивания вниз?
Ссылка, которая должна была остаться на месте, сдвинулась при копировании. Если в строке 2 стоит =B2/B7, в строке 3 получится =B3/B8, а B8 пустая. Закрепите итог формулой =B2/$B$7 и протяните снова.
