Закрепить ячейку: $ и F4

Чтобы перевести цены из рублей в евро, каждую делят на курс. Сам курс удобно держать в одной ячейке: если он изменится, достаточно поправить одно число. В таблице ниже курс лежит в F1, а формула =B2/F1 в C2 делит на него цену наушников: 7 360 ₽ превращаются в 80 евро.

Для первой строки всё верно. Но если протянуть эту формулу вниз, она сломается:

Почему формула ломается

После протягивания в C3 и ниже вместо цен стоит ошибка #ДЕЛ/0!. Так Excel сообщает, что формула делит на ноль или на пустую ячейку.

При протягивании Excel сдвигает в формуле все адреса. Цене это и нужно: в каждой строке своя. Но вместе с ценой съезжает и курс. В C3 формула становится =B3/F2, в C4 =B4/F3, а курс лежит только в F1, ниже пусто.

С умножением ещё коварнее: умножение на пустую ячейку ошибки не даёт, а молча возвращает 0.

Знак $ закрепляет адрес

Чтобы адрес не менялся при протягивании, в нём ставят знак доллара: $F$1. Знаков два, и у каждого своя работа: знак перед F держит на месте букву столбца, а знак перед 1 держит номер строки.

Такой адрес называется абсолютной ссылкой: он указывает на F1, куда бы ни попала формула. Адрес без знаков, как B2, называется относительной ссылкой и при протягивании сдвигается. В правильной формуле для C2 есть обе: =B2/$F$1. Цена у каждой строки своя, а курс один на всех.

На русской раскладке клавиатуры знака доллара нет, и переключаться ради него неудобно. Проще клавиша F4: дважды щёлкните по ячейке с формулой, поставьте курсор на адрес F1 и нажмите F4. Адрес превратится в $F$1. На ноутбуках Mac нажимайте fn + F4.

Следующие нажатия F4 перебирают по кругу остальные варианты: F$1, $F1 и F1 без знаков. Если нажали лишний раз, нажимайте дальше, пока снова не появится $F$1.

Почините лист ниже: формулу в нём уже протянули без закрепления.

Какую ссылку закреплять

Закрепляют ссылку на одну общую ячейку, которая нужна каждой строке:

• курс валюты;
• тариф за доставку;
• итог, на который делят каждую строку.

Ссылки на данные самой строки оставляют без $: им как раз нужно сдвигаться. Если закрепить и их, например написать =$B$2/$F$1, во всех строках окажется цена наушников.

Один знак вместо двух

Раз у каждого знака своя работа, поставить можно и один. При протягивании вниз в адресе меняется только номер строки: F1, F2, F3. При протягивании вправо меняется только буква: F1, G1, H1. Знак $ не даёт меняться той части, перед которой стоит.

Посмотрите, что делает каждый знак:

Получается четыре варианта адреса:

• F1 сдвигается в обе стороны;
• $F$1 не сдвигается никуда;
• F$1 держит номер строки: вниз адрес не меняется, а вправо меняется буква;
• $F1 держит букву столбца: вправо адрес не меняется, а вниз меняется номер.

Адреса с одним знаком называют смешанными ссылками.

Курсу в прайсе выше хватило бы и одного знака, F$1: формулу тянули только вниз, и два знака там тоже работают. Один знак становится нужен, когда формулу тянут сразу в две стороны.

В прайсе ниже цены товаров идут по строкам, а курсы евро и юаня лежат в первой строке, каждый над своим столбцом. Всю таблицу посчитала одна формула =$B3/C$1: её написали в C3 и протянули вправо и вниз.