Если вы задаетесь вопросом, как закрепить ячейку в формуле так, что при копировании ссылка на ячейку не менялась, значит необходимо освоить основу построения формул в Excel и разобраться со ссылками.
Абсолютные и относительные ссылки в Excel как бы понятия не на слуху, но действия с ними любой специалист, работая в электронных таблицах проводит регулярно.
Относительные ссылки
Согласно справки Microsoft (оригинальный текст перевода сохранен):
По умолчанию ссылка на ячейку является относительной ссылкой, которая означает, что ссылка относительна к расположению ячейки. Например, если вы ссылаетесь на ячейку A2 из ячейки C2, вы фактически ссылаетесь на ячейку, которая находится на два столбца слева (C минус A) в одной строке (2). При копировании формулы, содержаной относительную ссылку на ячейку, эта ссылка в формуле изменится.
Перевожу на житейский язык :)
Мы вводим формулу, чтоб получить значения из ячейки D2.
=D2
Но нам нужно еще 10 значений из диапазона D2:D11:
Для того, чтоб получить данные по списку, мы в первой ячейке "хватаемся" за + и протягиваем вниз. Таким образом, мы копируем ячейку.
Формулы при этом уже ссылаются на ячейку ниже по столбцом и отображаются вот так:
Как видите, при копировании адреса ячеек автоматически поменялись относительно шага копирования. То есть если на ячейку вниз, значит и адрес ячейки на 1 ниже. Если копировать вправо по столбцам, то также будет происходить смена столбцов в формуле. Точнее ссылки на ячейки в формулах.
Это и называется относительной ссылкой в формуле.
Абсолютные ссылки
Абсолютные ссылки - это когда необходимо закрепить определенный диапазон для использования формулы или функции. Закрепляется диапазон специальным знаком "доллара" $.
Разберем на примере расчета прайса с разными наценками.
Для заполнения таблицы пишем формулу в ячейке B4 = A4 * (1+B3).
Для проверки формулы я всегда пользуюсь приемом, когда активирую саму формулу в строке fx, либо двойным щелчком. В такой момент Excel подсвечивает нам указанные ячейки и так легче читать формулу. Сейчас формула введена без ограничений, и при обычном копировании вниз мы получим результат как на картинке внизу:
Естессна! нас не устраивает такой результат, ведь сейчас прошло копирование по относительной ссылке. Поэтому закрепляем нашу нужную ячейку со значением наценки (B3) через знак $, а именно B4 = A4 * (1+$B$3).
Таким образом, мы говорим формулке, что ячейка $B$3 не должна меняться. Именно и ответ на вопрос как закрепить ячейку в формуле.
Теперь копируем формулу и проверяем по последней строке. Все работает, ячейка с наценкой не изменилась, строка поменялась по правилу относительной ссылки.
Но рассмотрим еще случаи, так называемые смешанные ссылки.
Смешанные ссылки
Смешанные ссылки - это в целом комбинация абсолютных и относительных ссылок. Вот для продолжения работы над расчетом цен скорректируем формулу следующим образом:
То есть, мы сообщаем формуле следующее поведение:
- ячейка с ценой $A4 закрепляется по столбцу А, а ячейка изменяется по правилу относительной ссылки;
- ячейка с наценкой B$3 закрепляется по 3 строке, а столбец меняется.
В результате после копирования вы получите следующий результат:
Надеюсь, что пример наглядный и вы сможете разобраться как ввод ограничений через абсолютные ссылки могут упростить рутинную работу в экселе.
Если есть вопросы, задавайте. И, пожалуйста, подпишитесь, если вам понравилось. Это мой стимул вытаскивать все свои знания из своей головы, чтоб поскорее приступить к изучению не пройденного еще материала )).
p.s. Есть горячая клавиша установки и снятия знаков доллара в формуле. Делаем так: помещаем курсор в формуле, где есть наш диапазон или ячейки и нажимаем F4. При каждом повторном нажатии меняются правила ссылок. Экспериментируйте.