Как полностью или частично заморозить ячейку в формуле
При написании формулы Excel знак $ в ссылке на ячейку сбивает многих пользователей с толку. Но объяснение очень простое: это просто способ исправить это. Знак доллара в данном случае имеет только одно назначение: он указывает, следует ли менять ссылку при копировании. И это краткое руководство дает полную информацию о том, как вы можете исправить адрес ячейки, чтобы он не менялся при копировании формулы.
Если вы создаете формулу для одной ячейки таблицы Excel, то проблема, как исправить ячейку, вас не беспокоит. Но если его нужно копировать или перемещать по столу, тут есть подводные камни. Чтобы не нарушать расчеты, некоторые ячейки в формулах нужно подправить, чтобы их адреса не менялись.
Как упоминалось выше, относительные ссылки на ячейки используются по умолчанию для любой формулы, созданной в Excel. Но главной его особенностью является изменение при копировании и перемещении. Во многих случаях необходимо зафиксировать адрес ячейки в формуле, чтобы не потерять эту ссылку при изменении таблицы. Далее мы увидим следующие методы:
- Как исправить ячейку вручную.
- С помощью функциональной клавиши.
- Выборочное подтверждение по строке или столбцу.
- Закрепите адрес ячейки именем.
Чтобы предотвратить изменение ссылок на ячейки, строки или столбцы, используется абсолютная адресация, отличающаяся тем, что знак доллара $ ставится перед координатой строки или столбца $.
Мы объясним на простом примере.
=А1*В1
Здесь используются относительные ссылки. Если мы сдвинем это выражение на 2 клетки вниз и на 2 клетки вправо, мы увидим
=С3*D3
Буква столбца изменилась на 2 позиции, а номер строки изменился на 2 единицы.
Если у нас есть информация в ячейке A1, которую нам нужно использовать во многих ячейках нашей таблицы (например, обменный курс доллара, размер скидки и т д.), то рекомендуется исправить ее, чтобы ссылка на ячейку A1 никогда не работала» перерывы»:
=$А$1*В1
В результате, если мы повторим вышеописанную операцию, то получим формулу
=$A$1*D3
Ссылка на А1 уже не относительная, а абсолютная. Подробнее об относительных и абсолютных ссылках вы можете прочитать в этой статье в нашем блоге.
Это решение проблемы фиксации ячейки: нужно сделать ссылку абсолютной.
А теперь давайте подробнее рассмотрим, как можно исправить ячейку, строку или столбец в формуле.
Как вручную зафиксировать ячейку в формуле.
Предположим, у нас уже есть формула в одной из ячеек нашей таблицы.
В ячейке D2 рассчитайте сумму скидки:
=B2*F2
Записывать такой расчет по каждому товару проблематично и нерационально. Я хочу скопировать его из C2 вниз по столбцу. Но при этом ссылка на F2 не должна меняться. В противном случае наши расчеты будут ошибочными.
Поэтому ссылку на ячейку F2 в нашем расчете нужно как-то исправить, чтобы она не менялась. Для этого мы используем знаки $, чтобы преобразовать его из относительного в абсолютное.
Самый простой выход — отредактировать С2, для чего можно либо дважды щелкнуть мышью, либо навести на него курсор и нажать функциональную клавишу F2.
Затем с помощью курсора и клавиатуры вставьте знак $ в нужные места и нажмите Enter. Мы получаем:
=B2*$F$2
Другими словами, использование $ в ссылках на ячейки делает их фиксированными и позволяет перемещать формулы в Excel без их изменения. Теперь вы можете копировать, как показано на скриншоте ниже.
Примечание. Хотя мы говорили, что абсолютная ссылка никогда не меняется в Excel, на самом деле она будет меняться при добавлении или удалении строк или столбцов на листе. Это изменяет положение фиксированной ячейки. Например, если в нашем случае мы вставляем строку в заголовок таблицы, адрес ячейки автоматически изменится с $F$2 на $F$3 во всех формулах, которые на нее ссылаются.
Фиксируем ячейку при помощи функциональной клавиши.
Снова открываем ячейку для редактирования и ставим курсор на нужные нам координаты ячейки.
Нажмите функциональную клавишу F4, чтобы изменить тип ссылки.
Повторное нажатие F4 будет переключать ссылки в следующем порядке:
Чтобы установить ссылку на ячейку, просто нажмите F4 один раз.
Думаю, это несколько удобнее, чем вводить знак доллара вручную.
Частичная фиксация ячейки по строке или по столбцу.
Часто бывает, что нужно исправить только одну строку или столбец в адресе ячейки. Для этого используются смешанные ссылки.
Вы можете использовать два типа смешанных ссылок:
- Строка фиксируется, а столбец изменяется в копии.
- Столбец заблокирован, а строка изменена на копии.
Смешанная ссылка содержит относительную и абсолютную координату, например $A1 или A$1. Короче говоря, знак доллара используется только один раз.
Получить такую ссылку можно любым из способов, описанных выше. Вручную выберите место и установите знак $, либо нажмите F4 не один, а два-три раза. Вы можете увидеть это на изображении чуть выше.
В результате имеем следующее:
В следующей таблице показано, как можно привязать ссылку на ячейку.
| Фиксированная ячейка | Что происходит, когда вы копируете или перемещаете | Клавиши клавиатуры |
| $А$1 | Столбец и строка не меняются. | Нажмите F4. |
| 1 австралийский доллар | Линия не меняется. | Дважды нажмите F4. |
| $А1 | Колонка не меняется. | Нажмите F4 три раза. |
Рассмотрим пример, когда нужно зафиксировать только одну координату: либо столбец, либо строку. И все это в одной формуле.
Предположим, вы хотите рассчитать цены продажи при различных уровнях маржи. Для этого нужно умножить столбец с ценами (столбец B) на 3 возможных значения маржи (записанных в C2, D2 и E2). Вводим выражение для расчета в С3, а затем копируем его сначала вправо по строке, а потом вниз:
=$B3*(1+C$2)
Таким образом, вы можете использовать возможности смешанной ссылки для расчета всех возможных цен по единой формуле.
В первом множителе мы устанавливаем адрес столбца на координаты ячейки. Поэтому при копировании прямо по строке адрес $B3 не изменится: ведь строка по-прежнему третья, а буква столбца фиксирована и измениться не может.
Но во втором множителе мы ставим знак доллара перед номером строки. Поэтому при копировании вправо координаты столбца изменятся и вместо C$2 получим D$2, в результате в D3 получим выражение:
=$B3*(1+D$2)
А когда мы скопируем столбец, все будет наоборот: $B3 изменится на $B4, $B5 и т д., а D$2 не изменится, так как строка «заморожена». В итоге в С4 получаем:
=$B4*(1+C$2)
Самый приятный момент, что мы пишем формулу только один раз и потом копируем. Мы заполняем всю таблицу за один раз и экономим много времени.
А если ваши оценки вдруг изменятся, просто поменяйте цифры в C2:E2, и проблема с пересчетом решится практически моментально.
Если вам нужно изменить относительно абсолютных ссылок (или наоборот) в группе ячеек, во всем столбце или на большой площади, то описанный выше способ ручной корректировки может стать очень громоздкой и скучной задачей. С помощью специального инструмента преобразования формул можно выделить весь диапазон, а затем преобразовать формулы в этих ячейках в абсолютные или относительные ссылки. Или можно просто заменить все формулы их значениями одним щелчком мыши.
Как зафиксировать ячейку, дав ей имя.
Отдельная ячейка или целый диапазон ячеек в Excel также могут быть идентифицированы по имени. Для этого просто выберите нужную ячейку, введите нужное имя в поле Имя и нажмите клавишу Enter.
Вернемся к нашему примеру со скидкой. Давайте попробуем присвоить пользовательское имя ячейке F2, чтобы мы могли использовать его в вычислениях.
Поместите курсор на F2 и затем дайте этому адресу имя, как показано на рисунке выше. В этом случае вы можете использовать только буквы, цифры и знаки подчеркивания, которые могут заменить пробел. Знаки препинания и специальные символы не допускаются. Не будем умничать и назовем это «скидкой».
Теперь вы можете использовать это имя в формулах рабочей книги. Это своего рода абсолютная ссылка, так как она постоянно привязана к координатам конкретной ячейки или диапазона.
Поэтому мы предварительно зафиксировали ячейку F2 с помощью абсолютной ссылки и знака $ —
=B2*$F$2
а теперь проделываем то же самое с помощью его названия «скидка»:
=B2*скидка
Ячейка также надежно фиксируется, при этом формула становится более понятной и читаемой.
Excel понимает, что если в формуле появляется название «скидка», то вместо него следует использовать содержимое ячейки F2.
Вот несколько способов исправить ячейку в формуле в Excel. Спасибо за чтение, и я надеюсь, что эта информация была полезной!