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

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

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

Гораздо удобнее было бы записать формулу для всех ячеек, например, столбца D, чтобы в них автоматически записывалось произведение соответствующих ячеек столбцов B и С.

Формула тогда выглядела бы так:

Умножить значение, находящееся на две ячейки левее данной, на значение, расположенное в ячейке слева от данной.

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

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

A	B	C
Цена	Количество	Итого

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

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

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

Символ «$» «запирает» ячейку и Excel не может ее изменить при заполнении формулой или копировании в другую ячейку. Иногда используются смешанные ссылки.

Таким образом, ссылка на ячейку, например A1, может быть записана в формуле четырьмя способами: A1, $A1, A$1, $A$1.

Знак $ перед буквой (именем столбца) означает, что нельзя изменить столбец, перед номером строки – что нельзя изменить строку.

При заполнении ячеек формулой как относительная рассматривается только та часть адреса, перед которой нет символа " $ ".

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

A	B	C	D	E
Накладная
Наименование	Цена	Количество	Итого
Процессор Pentium 166 MMX
Процессор Pentium 233 MMX
Процессор Pentium II 266
Процессор AMD k6-166			=$B$10*$C$10

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

Для этого составим таблицу, как в указанном примере.

	A	B	C	D	E	F	G
1	Продажа мороженного по округам города N (тыс.руб.)
	Лето 2005 г.
			Июнь	Июль	Август	Всего	В процентах
	Цетральный					30%
	Западный						19%
	Северный						28%
	Южный						23%
		Всего					100%

1. Вычислим сумму по строкам F6-F9 и по каждому столбцу (C,D,E,F).

2. Рассчитаем долю Центрального округа за все лето в полной сумме: =F6/F10.

3. Если мы попытаемся скопировать данную формулу в ячейки G7-G10, то в соответствующей ячейке появится запись " #ЗНАЧ!". Это сообщение об ошибочном смещении типов данных. Чтобы запретить Excel механически изменять адрес ячейки, достаточно перед номером столбца и номером строки ввести символ " $ ", т.е. вместо относительного адреса указать абсолютный.

4. Выделим ячейку G6 и щелкнем в Строке формул. Появится формула =G6/G10.

5. Вставим символ $, получим формулу =G6/G$10.

6. Скопируем полученную формулу в G7-G10.

7. Для перевода долей округов в проценты выделим столбец G и щелкнем на кнопке % в панели инструментов Стандартная. Все доли будут умножены на 100 и помечены знаком "%".