Относительный и абсолютный адрес ячейки

При копировании в MS Excelреализована возможность, позволяющая очень быстро создавать большие таблицы, в которых есть клетки с одинаковыми формулами. MS Excelпозволяет не думать об изменении адресов клеток. При копировании формулы, находящиеся в клетках входного диапазона и содержащие адреса ячеек, автоматически корректируются. При коррекции эти адреса изменяются так, чтобы они отражали их новое местоположение, а именно чтобы их положение относительно скопированной (выходной) клетки было таким же, как в копируемой.

Например, пусть имеется следующая таблица:

В ячейку B5 занесем формулу =СУММ(В1:В4):

В столбцах А и С тоже посчитаем сумму элементов. Можно набрать в ячейках А5 и С5 соответствующие формулы, а можно скопировать формулу из ячейки В5. Тогда в ячейках А5 и С5 будет формула, в которой ссылки на адреса суммируемых элементов будут скорректированы Excel относительно направления копирования (будут изменены адреса столбцов):

Можно скопировать формулу =СУММ(В1:В4) и в ячейку В6. В этом случаем адреса ссылок снова будут изменены относительно направления копирования (меняются адреса строк):

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

Например, требуется построить диапазон значений разбиения отрезка [0,1] на 10 интервалов. То есть построить такой ряд данных:

Можно воспользоваться маркером заполнения. Но чаще бывает удобным построить соответствующую формулу.

Так шаг разбиения отрезка [a,b] на n частей вычисляется по формуле:

(1)

Каждая точка отрезка вычисляется по формулам:

(2)

В нашем примере шаг равен:

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

В ячейке С4 содержится формула =(С2-С1)/С3 для вычисления шага по формуле (1).

Теперь начнем заполнять данные столбца А. В ячейку А1 занесем ссылку на начальную точку:

В ячейку А2 запишем формулу, вычисляющую следующее значение по формуле(2):

Теперь скопируем формулу =А1+С4 в ячейку А3:

Видим, что адреса ячеек при копировании скорректировались.
Но содержимое ячейки С5 пусто, да и в формуле должна быть ссылка на ячейку С4, т.к. в ней содержится значение шага.

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

Чтобы при копировании адреса клеток не корректировались, их нужно из относительных сделать абсолютными - поставить в формуле перед соответствующей координатой клетки знак “$”.

Знак “$” можно вставить с клавиатуры, а можно выделить необходимую ссылку в формуле и нажать клавишу F4.

Отредактируем нашу формулу:

Теперь скопируем ее в ячейку А3:

Итак, в формуле адрес ячейки С4, содержащей значение шага, не изменился. Значение шага теперь при дальнейшем копировании будет браться из одной и той же ячейки.

Фиксировать, т.е. делать абсолютным, можно отдельно названия строк и названия столбцов.

Если адрес относительный, то он будет меняться в направлении копирования или перемещения формулы, абсолютный адрес будет сохраняться.

Например, в ячейку К4 занесем формулу =М2 и рассмотрим, как она будет изменяться при копировании, если фиксировать название строки или столбца.

1. Адрес относительный = M2:

2. Зафиксируем название строки = M$2:

3. Зафиксируем название столбца = $M2:

4. Сделаем адрес полностью абсолютным = $M$2: