Как полностью или частично заморозить ячейку в формуле
При написании формулы Excel знак $ в ссылке на ячейку сбивает с толку многих пользователей. Но объяснение очень простое: это всего лишь один способ исправить это. Знак доллара в данном случае имеет только одно предназначение: он указывает, следует ли изменять ссылку при ее копировании. И это краткое руководство предоставляет полную информацию о том, как исправить адрес ячейки, чтобы он не изменился при копировании формулы.
Если вы создаете формулу для одной ячейки в электронной таблице Excel, проблема того, как исправить ячейку, вас не беспокоит. Но если его нужно скопировать или переместить на стол, здесь и кроются подводные камни. Чтобы не сорвать расчеты, некоторые ячейки следует поправить в формулах, чтобы их адреса больше не менялись.
Как упоминалось выше, относительные ссылки на ячейки являются основным значением по умолчанию для любой формулы, созданной в Excel. Но главная их особенность — изменение при копировании и перемещении. Во многих случаях необходимо исправить адрес ячейки в формуле, чтобы не потерять эту ссылку при изменении таблицы. Ниже мы рассмотрим следующие методы:
- Как отремонтировать ячейку вручную.
- С помощью функциональной клавиши.
- Выборочная фиксация по строке или столбцу.
- Исправьте адрес ячейки с именем.
Чтобы предотвратить изменение ссылок на ячейки, строки или столбцы, используйте абсолютную адресацию, которая отличается координатами предыдущей строки или столбца со знаком доллара $.
Поясним это на простом примере.
= A1 * B1
Здесь используются относительные ссылки. Если мы переместим это выражение на 2 ячейки вниз и на 2 ячейки вправо, мы уже увидим
= DO3 * D3
Буква столбца изменилась на 2 позиции, а номер строки — на 2 единицы.
Если в ячейку A1 мы записали информацию, которую нам нужно использовать во многих ячейках нашей таблицы (например, курс доллара, размер скидки и т.д.), То желательно исправить ее, чтобы ссылка на ячейку A1 была не «никогда не ломается»:
= $ A $ 1 * B1
Следовательно, если повторить предыдущую операцию, в результате мы получим формулу
= $ A $ 1 * D3
Ссылка на A1 теперь не относительная, а абсолютная. Вы можете узнать больше об относительных и абсолютных ссылках в этой статье нашего блога.
Это решение проблемы ремонта ячейки: необходимо преобразовать эталон в абсолютный.
Теперь давайте подробнее рассмотрим, как можно заблокировать ячейку, строку или столбец в формуле.
Как вручную зафиксировать ячейку в формуле.
Предположим, у нас уже есть формула в одной из ячеек нашей таблицы.
В ячейке D2 рассчитайте размер скидки:
= SI2 * FA2
Писать такой расчет для каждого товара проблематично и нерационально. Я хотел бы скопировать его из C2 в столбец. Однако ссылка на F2 не должна измениться. В противном случае наши расчеты будут неверными.
Следовательно, ссылку на ячейку F2 в нашем расчете нужно как-то исправить, чтобы предотвратить ее изменение. Для этого мы используем знаки $, чтобы преобразовать его из относительного в абсолютное.
Самый простой выход — отредактировать C2, при этом вы можете дважды щелкнуть по нему мышью или навести на него курсор и нажать функциональную клавишу F2.
Затем с помощью курсора и клавиатуры вставьте знак $ в нужные места и нажмите Enter. У нас есть:
= B2 * $ F $ 2
Другими словами, использование $ в ссылках на ячейки делает их фиксированными и позволяет перемещать формулы в Excel, не меняя их. Теперь вы можете скопировать его, как показано на скриншоте ниже.
Примечание. Хотя мы сказали, что абсолютная ссылка никогда не изменяется в Excel, на самом деле она изменяется, когда вы добавляете или удаляете строки или столбцы на листе. Это изменяет положение заблокированной ячейки. Например, если в нашем случае мы вставляем строку в заголовок таблицы, то адрес ячейки автоматически изменится с $ F $ 2 на $ F $ 3 во всех формулах, которые на нее ссылаются.
Фиксируем ячейку при помощи функциональной клавиши.
Снова откройте ячейку для редактирования и поместите курсор на нужные нам координаты ячейки.
Нажмите функциональную клавишу F4, чтобы изменить тип связи.
Повторное нажатие F4 будет переключать ссылки в следующем порядке:
Чтобы зафиксировать ссылку на ячейку, просто нажмите F4 один раз.
Думаю, это немного дешевле, чем вводить знак доллара вручную.
Частичная фиксация ячейки по строке или по столбцу.
Часто бывает, что вам нужно только заблокировать строку или столбец в адресе ячейки. Для этого используются смешанные соединения.
Вы можете использовать два типа смешанных ссылок:
- При копировании строка сохраняется, а столбец изменяется.
- Столбец заблокирован, а строка изменяется при копировании.
Смешанная ссылка содержит относительную и абсолютную координаты, например $ A1 или A $ 1. Проще говоря, знак доллара используется только один раз.
это соединение может быть достигнуто одним из способов, описанных выше. Вручную выберите позицию и установите знак $ или нажмите F4 не один раз, а два или три раза. Вы можете увидеть это на фото чуть выше.
В результате имеем следующее:
В следующей таблице показано, как заблокировать ссылку на ячейку.
| Фиксированная ячейка | Что происходит при копировании или перемещении | Клавиши на клавиатуре |
| 1 австралийский доллар | Столбец и строка не меняются. | Нажмите F4. |
| 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. Спасибо за чтение, и я надеюсь, что эта информация была полезной!