Как зафиксировать ячейку в формуле Excel
Знак доллара и клавиша F4: почему формула съезжает при протягивании и как закрепить только строку или только столбец.
Коротко
Когда формулу протягивают вниз, адреса в ней сдвигаются вместе с ней: =C5*D5 в шестой строке станет =C6*D6. Обычно это и нужно. Но если в формуле есть ячейка, которая не должна двигаться — курс, ставка, итог, — её закрепляют знаком $.
$ ставится перед тем, что нужно удержать: $C держит столбец, C$5 держит строку, $C$5 держит и то и другое. Читать легко: доллар — это гвоздь, он прибивает то, что стоит сразу за ним.
Руками доллары не набирают. Поставьте курсор на адрес в формуле и нажимайте F4: C5 → $C$5 → C$5 → $C5 → и обратно к C5. Четыре нажатия — полный круг. На ноутбуках без отдельного ряда F-клавиш может понадобиться Fn + F4.
То же самое формулой в Excel
=M5*$N$4 — цена умножается на курс из одной ячейки; курс закреплён полностью=E5/$E$13 — доля строки в итоге — знаменатель один на весь столбец=B$4*$A5 — таблица умножения: одна формула на всю сетку, заголовки закреплены по строке, боковик — по столбцу=ВПР($H$5;$B$7:$E$12;3;ЛОЖЬ) — справочник в ВПР закрепляют всегда, иначе он сползёт=СУММ($M$13:M13) — нарастающий итог: начало закреплено, конец едет внизФайл с тремя случаями закрепления
Курс в жёлтой ячейке и столбец пересчёта, таблица умножения 10 × 10 из одной формулы и столбец нарастающего итога. Три случая, которых хватает на девяносто пять процентов задач.
Файл
zakrepit-yacheyku.xlsx · формулы работают в Excel 2016 и новее
и в Google Таблицах · без макросов · формулы проходят автопроверку
перед публикацией
Пошагово
- Напишите формулу как обычно, кликая по ячейкам.
- Поставьте курсор на тот адрес, который не должен двигаться — достаточно щёлкнуть по нему в строке формул.
- Нажмите F4. Появятся оба доллара:
$N$4. - Нужно закрепить только строку или только столбец — нажимайте F4 ещё раз: порядок всегда один и тот же, полный круг за четыре нажатия.
- Enter и протяните формулу. Закреплённая ссылка останется на месте, остальные сдвинутся.
- Проверьте результат в средней строке: если там ноль или
#ДЕЛ/0!, знаменатель сполз — значит доллар стоит не там.
Где обычно ломается
- F4 не работает — на ноутбуке F-клавиши переключены на громкость и яркость. Fn + F4 либо переключить режим в настройках клавиатуры. Второй вариант — набрать
$вручную, ничего страшного. - Закрепили всё подряд — формула перестала протягиваться осмысленно.
$нужен только там, где ссылка должна остаться на месте; остальное трогать не надо. $внутри умной таблицы. У таблиц со структурными ссылками (Продажи[Сумма]) закрепление устроено иначе: имя столбца само по себе не съезжает, доллары там не нужны.- Ссылка на другой лист.
=Данные!$C$5— доллары ставятся после восклицательного знака, к имени листа они не относятся. - Скопировали формулу вбок, а не вниз. Тогда съезжают столбцы, а не строки, и закреплять нужно букву:
$C5, а неC$5. Это и есть смешанная ссылка.