Относительные, абсолютные и смешанные ссылки

Относительная ссылка (Relative reference) указывает на ячейку, основываясь на ее положении относительно ячейки, в которой находится формула, например, «на две строки выше». Ссылки именно такого типа мы использовалив предыдущих примерах. Абсолютная ссылка (Absolute reference) использует для указания на ячейку ее фиксированное положение на листе, например, «ячейка находится в столбце А строки 2». Смешанная ссылка (Mixed reference) содержит относительную и абсолютную ссылку, например, «ячейка находится в столбце А и выше на две строки». Абсолютные и смешанные ссылки особенно полезны при копировании формулы из одного места листа в другое.

Относительная ссылка на ячейку А1 записывается так:

=А1

Абсолютная ссылка на ячейку А1 имеет следующий вид:

=$А$1

Комбинируя абсолютные и относительные ссылки на ячейку А1, можно создать следующие смешанные ссылки:

=$А1 =А$1

Если символ доллара стоит перед буквой, то координата столбца абсолютная, а строки — относительная. Если символ доллара стоит перед числом, то, напротив, координата столбца относительная, а строки — абсолютная.

С помощью клавиши F4 можно быстро изменить тип ссылки, как показано ниже.

1. Выделите ячейку А1 и введите:

=В1+В2

2. Нажмите клавишу F4, чтобы ссылку, находящуюся рядом с точкой вставки в строке формул, изменить на абсолютную. Формула примет вид:

=В1+$В$2

3. Снова нажмите F4, чтобы сделать эту ссылку смешанной (относительная координата столбца и абсолютная координата строки). Формула примет вид:

=В1+В$2

4. Еще раз нажмите F4, чтобы реверсировать эту смешанную ссылку (абсолютная координата столбца и относительная координата строки). Формула примет вид:

=В1+$В2

5. Снова нажмите F4, чтобы вернуться к исходной относительной ссылке.

В каких случаях полезны смешанные ссылки?

При копировании формул в другие строки и столбцы иногда случается, что копируемые формулы содержат ссылки на ячейки, расположенные в этих строках или столбцах. При копировании относительные ссылки автоматически настраиваются, а абсолютные сохраняются. При использовании только относительных или только абсолютных ссылок вам, вероятно, придется подправлять каждую формулу в отдельности. Однако при надлежащем применении смешанных ссылок Excel сможет правильно настроить формулы и без вашего участия. При копировании смешанной ссылки ее часть с предшествующим символом доллара не изменяется, в то время как другая часть этой ссылки настраивается с учетом относительного положения копируемой формулы. Ниже на рисунке приведен лист с формулами, в которых вычисляются ежемесячные выплаты для разной величины ссуды, заданной в строке 2, при различных процентных ставках прибыли, заданных в столбце А. Срок кредита в месяцах указан в ячейке В1.

Чтобы создать такой лист, введите формулу в ячейке ВЗ и затем скопируйте ее в соседние ячейки. Смешанные ссылки в этой формуле при копировании будут настроены так, чтобы для каждой ячейки использовать соответствующую величину ссуды и процентную ставку. Первый аргумент в формуле, $АЗ/12, является смешанной ссылкой на годовую процентную ставку (деление на 12 производится для вычисления месячной процентной ставки). После копирования этот аргумент всегда будет ссылаться на значение в столбце А, а номер строки при этом будет настраиваться, так что можно вводить различные процентные ставки в столбце А и видеть конечный результат в зависимости от этих ставок. Абсолютная ссылка на срок ссуды, $В$1, будет всегда указывать именно на эту ячейку. Третий аргумент в формуле, В$2, является смешанной ссылкой на величину кредита. После копирования формулы в любое место этот аргумент всегда будет ссылаться на значение в строке 2, а номер столбца будет настраиваться так, чтобы ссылка указывала на величину кредита в данном столбце.

ИНТЕРНЕТ

Файл References.xls, использованный в приведенном выше примере, можно найти на Web-узле издательства «Питер» по адресу https://www.piter-press.ru/download/