Excel: Ссылки относительные и абсолютные

Часто при использовании формул в Excel после ввода формулы в одну ячейку необходимо скопировать или распространить ее на блок ячеек.

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

Ссылка в Excel — адрес ячейки или связного диапазона ячеек.

Адрес ячейки определяется пересечением столбца и строки, например: A1, C16.

Адрес диапазона ячеек задается адресом верхней левой ячейки и нижней правой, например: A1:C5.

Ссылки в Excel бывают 3-х типов:

• Относительные ссылки (пример: A1);
• Абсолютные ссылки (пример: $A$1);
• Смешанные ссылки (пример: $A1 или A$1).

Относительные ссылки

«Относительность» ссылки означает, что из данной ячейки ссылаются на ячейку, отстоящую на столько-то строк и столбцов относительно данной.

Пример.

В ячейке А6 формула ссылается на две ячейки (С3 и С4), отстоящие от данной на два столбца вправо и на три (С3) и две (С4) ячейки выше.

При копировании или «протаскивании» c помощью Маркера заполнения формулы, например, в ячейку А7  формула  изменяется  (Excel пересчитывает адреса всех относительных ссылок в ней в соответствии с новым положением ячейки).

Теперь формула в ячейке А7  ссылается на ячейки С4 и С5. Названия ссылок изменились, но осталось неизменным их положение относительно ячейки, в которой находится формула (два столбца вправо и на три (С4) и две (С5) ячейки выше).

Относительные ссылки целесообразно использовать в формулах в двух случаях:

1. Если формулу не предполагается копировать в другие ячейки.
2. Если формулу необходимо скопировать в  идентичные ячейки.

Абсолютные ссылки

Если формула требует, чтобы адрес ячейки оставался неизменным при копировании, то должна использоваться абсолютная ссылка. Для этого перед символами ссылки устанавливаются символы "$" (формат записи $А$1).

Абсолютные ссылки в формулах используются в случаях:

1. Необходимости применения в формулах констант.
2. Необходимости фиксации диапазона для проведения расчетов.

Пример.

В диапазоне  А1:А5 указаны зарплаты сотрудников отдела, а в С1 – процент премии, установленный для всего отдела. Подсчитаем  премию каждого сотрудника и поместим в диапазоне В1:В5.

Для расчета премии первого сотрудника  введем в ячейку В1 формулу =А1*С1.

Если мы с помощью Маркера заполнения протянем формулу вниз, то получим  в ячейке В2 формулу =А2*С2, в ячейке В3 -  =А3*С3 и т.д.  Так как в ячейках диапазона С2:С5 нет значений, то в  диапазоне В2 : В5 получаем нули.
Для исправления ошибки, необходимо зафиксировать в формуле ссылку на ячейку С1, т.е. заменить относительную ссылку С1 на абсолютную $C$1.

Для этого:

• выделите ячейку  В1
• в  Строке формул поставьте  знак «$» перед буквой столбца и адресом строки  $С$1. Более быстрый способ — в  Строке формул поставьте курсор на ссылку  С1 (можно перед С, перед или после 1) и  нажмите один раз клавишу «F4». Ссылка С1 выделится и превратится в $C$1.
• нажмите ENTER

Формула приняла вид « =А1*$С$1».
Маркером  заполнения протяните полученную формулу вниз.

Теперь  диапазон  В2: В5 заполнен значениями премий сотрудников.