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