Фиксация одной ячейки абсолютными ссылками

Очень часто требуется направить множество формул на одно общее значение: ставку налога, обменный курс, целевое значение или фиксированную комиссию.

Это единственное значение хранится в одной ячейке, и каждая другая формула должна продолжать ссылаться на неё независимо от места копирования. Абсолютная ссылка делает это возможным.

Абсолютная ссылка содержит знак доллара перед столбцом и строкой, например $A$1. Она полностью зафиксирована.

Скопируйте формулу, содержащую $A$1, в любое место листа — эта часть всегда будет указывать на ячейку A1. Перемещаться будут только незакреплённые ссылки в формуле.

Представьте, что ставка налога с продаж 0.08 находится в ячейке E1. Итоговые суммы заказов расположены в столбце B. Чтобы добавить налог к каждому заказу, введите в C2:

=B2*$E$1

Ссылка B2 является относительной, поэтому перемещается вместе со строкой, а $E$1 — абсолютной, поэтому всегда указывает на ставку налога.

Заполните формулу из C2 вниз — в C2, C3, C4 — и посмотрите, что происходит с каждой частью:

• B2 превращается в B3, затем в B4 (относительная ссылка, перемещается)
• $E$1 каждый раз остаётся $E$1 (абсолютная ссылка, зафиксирована)

Таким образом, в строке 3 получается =B3*$E$1, а в строке 4 — =B4*$E$1. Ставка налога никогда не теряется.

Забудьте о знаках доллара и напишите =B2*E1. При копировании вниз E1 смещается к E2, E3, а эти ячейки пусты.

Ваш столбец с налогом заполняется нулями. Числа выглядят неправильно, но причина проста: ссылка на ставку была относительной, хотя должна была быть абсолютной.

Чтобы быстро преобразовать E1 в $E$1, щёлкните E1 в строке формул и один раз нажмите F4. Excel вставит оба знака доллара за вас.

Это гораздо быстрее и менее подвержено ошибкам, чем вводить символы вручную, особенно в длинных формулах.

Предположим, в $B$1 хранится курс USD к EUR, равный 0.92, а суммы в долларах расположены в столбце A. Преобразуйте каждую из них с помощью формулы:

=A2*$B$1

Скопируйте формулу на сотни строк, и при каждой конвертации будет использоваться один и тот же курс. Один раз измените курс в B1, и весь столбец мгновенно пересчитается.

Поскольку обе части зафиксированы, абсолютная ссылка остаётся неизменной при копировании вниз, вправо или в обоих направлениях.

Скопируйте =B2*$E$1 вправо, и она превратится в =C2*$E$1: B сместится в C, но $E$1 останется на месте. Такая фиксация работает в любом направлении.

Абсолютные ссылки полезны и внутри функций. Чтобы найти долю каждого значения от фиксированной суммы в B1:

=A2/$B$1

Или чтобы сравнить каждый результат с целевым значением в D1:

=IF(A2>=$D$1,"Pass","Fail")

Зафиксированная ячейка остаётся точкой отсчёта во всех строках.

Главное преимущество абсолютной ссылки — единая точка управления. Поскольку каждая формула считывает данные из одной и той же зафиксированной ячейки, изменение этой ячейки сразу обновляет весь столбец.

Измените налоговую ставку в E1 с 0.08 на 0.09, и каждая формула =B2*$E$1 мгновенно пересчитается. Не нужно редактировать десятки формул: один ввод управляет ими всеми.

Опытные пользователи часто размещают константы, ставки и целевые значения в небольшой подписанной области в верхней части листа, а затем ссылаются на них абсолютно.

Так формулы остаются понятными, а изменение одного введённого значения обновляет весь отчёт. Именно знак доллара направляет все формулы к этому значению.

Проверьте, насколько хорошо вы поняли абсолютные ссылки.

Абсолютная ссылка, например $A$1, фиксирует и столбец, и строку, поэтому при копировании всегда указывает на одну и ту же ячейку.

• Используйте её для общих значений, например ставок, комиссий и целевых значений
• Сочетайте её с относительными ссылками, чтобы перемещались только нужные части
• Нажмите F4, чтобы мгновенно добавить оба знака доллара

Далее вы научитесь фиксировать только одну часть с помощью смешанных ссылок.