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

Копирование без использования буфера обмена

Установить указатель мыши на рамку области выделения (он превратится из белого крестика в черный крестик со стрелочками) и при нажатой клавише Ctrl перетащить данные в нужное место. Если нужно копировать один из параметров ячейки или при вставке раздвинуть клетки, нужно тащить выделение не левой, а правой клавишей мыши.

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

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

	A	B	C
			-1
	-10

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

	A	B	C
			-1
	-10
		=СУММ(В1:В4)

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

	A	B	C
			-1
	-10
	=СУММ(А1:А4)	=СУММ(В1:В4)	=СУММ(С1:С4)

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

	A	B	C
			-1
	-10
		=СУММ(В1:В4)
		=СУММ(В2:В5)

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

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

	A	B	C
	0,0
	0,1
	0,2
	0,3
	0,4
	0,5
	0,6
	0,7
	0,8
	0,9
	1,0

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

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

(1)

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

(2)

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

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

	A	B	C
		начальная точка	0,0
		конечная точка	1,0
		количество точек
		шаг	=(С2-С1)/С3

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

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

	A	B	C
	=С1	начальная точка	0,0
		конечная точка	1,0
		количество точек
		шаг	=(С2-С1)/С3

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

	A	B	C
	0,0	начальная точка	0,0
	=А1+С4	конечная точка	1,0
		количество точек
		шаг	=(С2-С1)/С3

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

	A	B	C
	0,0	начальная точка	0,0
	=А1+С4	конечная точка	1,0
	=А2+С5	количество точек
		шаг	=(С2-С1)/С3

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

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

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

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

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

	A	B	C
	0,0	начальная точка	0,0
	=А1+$С$4	конечная точка	1,0
		количество точек
		шаг	=(С2-С1)/С3

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

	A	B	C
	0,0	начальная точка	0,0
	=А1+$С$4	конечная точка	1,0
	=А2+$С$4	количество точек
		шаг	=(С2-С1)/С3

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

	A	B	C
	0,0	начальная точка	0,0
	=А1+$С$4	конечная точка	1,0
	=А2+$С$4	количество точек
	=А3+$С$4	шаг	=(С2-С1)/С3
	=А4+$С$4
	=А5+$С$4
	=А6+$С$4
	=А7+$С$4
	=А8+$С$4
	=А9+$С$4
	=А10+$С$4

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

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

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

	J	K	L	M
		=M2

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

	J	K	L	M
		=M1
	=L2	=M2	=N2
		=M3	=N3

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

	J	K	L	M
		=M$2
	=L$2	=M$2	=N$2
		=M$2	=N$2

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

	J	K	L	M
		=$M1
	=$M2	=$M2	=$M2
		=$M3	=$M3

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

	J	K	L	M
		=$M$2
	=$M$2	=$M$2	=$M$2
		=$M$2	=$M$2

Дата добавления: 2013-12-13