Как закрепить ячейку в формуле Excel: знак $ и клавиша F4

Чтобы ячейка в формуле не съезжала при протягивании, перед буквой столбца и номером строки ставят знак доллара: $B$1. Быстрее всего — встать курсором на адрес в формуле и нажать F4. Доллар читается как «не меняй то, что идёт за мной»: $B держит столбец, $1 — строку. Закрепляют то, что одно на всю таблицу: курс валюты, ставку налога, процент скидки.

Почему ячейка съезжает

Excel хранит в формуле не адрес как таковой, а расстояние: «взять ячейку на одну строку выше и два столбца левее». Поэтому, когда формулу протягивают вниз, все адреса сдвигаются вместе с ней. Обычно это и нужно: формула =B3*C3 в следующей строке превращается в =B4*C4 и считает свой товар.

Беда начинается, когда в формуле есть ячейка, общая для всех строк. Пусть курс доллара стоит в B1, а цены в долларах — в столбце B, начиная с третьей строки. Формула в C3 — =B3*B1. Протянули вниз — в C4 получилось =B4*B2, в C5 — =B5*B3. Курс уехал вместе со строкой, и вместо него формула умножает на заголовок или на соседнюю цену. Итог — ошибка #ЗНАЧ! в одной строке и неверные числа в остальных.

Исправление — закрепить курс: =B3*$B$1. Теперь при протягивании B3 меняется на B4, B5, а $B$1 остаётся на месте.

Четыре вида ссылок

Первая — относительная ссылка, вторая — абсолютная, третья и четвёртая — смешанные. Первые две закрывают почти все рабочие случаи.

Клавиша F4

Печатать доллары руками не нужно. Встаньте курсором на адрес в строке формул или прямо в ячейке и нажмите F4: B1 станет $B$1. Следующее нажатие даст B$1, потом $B1, потом снова B1 — так перебираются все четыре вида. На ноутбуке F4 иногда срабатывает только вместе с клавишей Fn.

Если в Excel F4 не работает, проверьте, что вы в режиме правки формулы — курсор стоит внутри адреса. В Google Таблицах то же самое делает F4, а на Mac в Excel — Cmd + T.

Как понять, что закреплять

Задайте к каждой ячейке в формуле вопрос: у каждой строки это своё? Цена — своя у каждого товара, значит без доллара. Курс, ставка налога, коэффициент, итог для сравнения — один на всех, значит с долларом.

Ошибиться можно и в другую сторону — закрепить лишнее. Если поставить доллары везде, формула перестанет сдвигаться вовсе: протянули на десять строк, а во всех десяти считается первая. Одинаковые числа во всём столбце — верный признак такой ошибки.

Смешанная ссылка: таблица в обе стороны

Смешанные ссылки нужны, когда формулу пишут один раз и тянут сразу вправо и вниз. Классический пример — таблица умножения: множители строк в столбце A, множители столбцов в строке 1. Формула в B2 — =$A2*B$1. Первая ссылка держит столбец A, но строка у неё едет; вторая держит строку 1, но едет столбец. Протяните её на всю таблицу — и каждая ячейка возьмёт свой множитель слева и сверху.

Так же устроены таблицы «товар × месяц», «цена × скидка», расчёт процентов от разных сумм — везде, где заголовки стоят и по строкам, и по столбцам.

Как проверить закрепление

Протяните формулу и откройте последнюю ячейку — F2 или двойной щелчок. Excel подсветит цветными рамками ячейки, на которые ссылается формула. Если рамка общего числа стоит на месте, а рамка данных сдвинулась вместе со строкой, всё закреплено верно. Если общая рамка уехала вниз — не хватает доллара.

Частые вопросы

Как закрепить ячейку в формуле Excel?

Поставить знак доллара перед буквой столбца и номером строки: $B$1. Быстрее — встать на адрес в формуле и нажать F4.

Что значит знак $ в формуле Excel?

Он запрещает менять то, что идёт за ним, при протягивании формулы: $B держит столбец, $1 — строку, $B$1 — всю ячейку.

Чем абсолютная ссылка отличается от относительной?

Относительная ссылка B1 сдвигается вместе с формулой, абсолютная $B$1 всегда указывает на одну и ту же ячейку.

Почему при протягивании формулы везде одинаковый результат?

Скорее всего, закреплено лишнее: доллары стоят и у ячейки, которая должна сдвигаться. Уберите их у данных строки, оставьте у общего числа. Другая причина — ручной пересчёт: включите автоматический во вкладке «Формулы».

Что делать, если F4 не работает?

Убедиться, что курсор стоит внутри адреса в формуле, и попробовать Fn + F4 — на ноутбуках функциональные клавиши часто заняты. На Mac в Excel вместо F4 — Cmd + T.

Где потренироваться

Закреплению посвящён урок «Закрепить ссылку: знак доллара» курса Excel, а предыдущий, «Протянуть формулу», показывает, как ссылка уезжает. В обоих вы пишете формулы на настоящем листе, и проверка сверяет, что получилось. Если формула не протягивается вовсе — разбор пяти причин в статье «Как протянуть формулу в Excel».