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

В Excel существует два основных типа адресации данных (ссылок): относительные и абсолютные.

Относительная ссылка определяет адрес ячейки по отношению к активной ячейке. Пусть, например, в ячейке В2 имеется ссылка на ячейку А3. В относительном представлении ссылка А3 указывает на ячейку, которая расположена на один столбец левее и на одну строку ниже данной ячейки рисунок 203.

Рисунок 203

Если формула будет скопирована в другую ячейку, то такое относительное указание ссылки сохранится. Например, если формула будет скопирована в ячейку С3, ссылка будет продолжать указывать на ячейку, расположенную на один столбец левее и на одну строку ниже данной ячейки, то есть на ячейку В4 рисунок 204.

Рисунок 204

Если выполнить копирование содержимого ячейки В2 в разные стороны, можно увидеть, что копии будут отличаться от содержимого копируемой ячейки рисунок 205 – режим показа формул). При копировании по горизонтали изменяется имя столбца, при копировании по вертикали – номер строки, при копировании по диагонали – и то и другое. Часто такие изменения адресов нужны при копировании ячеек, чтобы ссылки на ячейки корректировались автоматически.

Рисунок 205

По умолчанию ссылки на ячейки в формулах рассматриваются как относительные.

Абсолютная ссылка задает адрес ячейки в соответствии с его положением на рабочем листе. Перед элементами номера ячейки, использующей абсолютную адресацию, ставятся символы доллара $. Например, для ячейки, расположенной на пересечении столбца В и строки 2, абсолютный адрес буде записан следующим образом: $В$2. При копировании формулы абсолютные адреса в них не меняются, например, при копировании формулы из ячейки В3 в ячейку С4 абсолютный адрес $В$2 не изменился рисунок 206 – режим показа формул.

Рисунок 206

В формулах можно создавать смешанные ссылки. Смешанная ссылка содержит и относительную, и абсолютную компоненты адреса ячейки. Например ссылка В$3 по столбцу относительная, а по строке абсолютная, поэтому она сохраняет адрес при копировании формулы, в которую она входит, по вертикали, а при копировании по горизонтали в третьей строке меняется адрес столбца рисунок 207 – режим показа формул).

Рисунок 207

Таким образом, ссылки на ячейку В3 записываются:

- относительная ссылка: =В3

- абсолютная ссылка: =$В$3

- смешанные ссылки: =$В3 =В$3.

Если перед символом стоит знак $, то соответствующие компоненты адреса являются абсолютными (при копировании не меняются), если знака $ нет – относительными (при копировании могут меняться).

Для быстрого изменения типа ссылки используется клавиша F4. Для изменения способа адресации (относительного в абсолютный) надо выделить адрес в формуле и нажать кнопку F4. При последовательном нажатии клавиши F4 номер ячейки В3 будет записываться как $В$3, В$3, $В3.

Для ускорения ввода абсолютных ссылок и избежания ошибок следует использовать клавишу F4.

Например.

1. Выделите ячейку С2 и наберите =А2+В2.

2. Нажмите F4. Формула примет вид =А2+$В$2.

3. Еще раз нажмите F4 – ссылка станет смешанной и формула примет вид =А2+В$2 (относительный адрес столбца и абсолютный адрес строки).

4. Еще раз нажмите F4 – ссылка преобразуется и формула примет вид =А2+$В2 (абсолютный адрес столбца и относительный адрес строки).

5. Еще раз нажмите F4, и вы вернетесь к исходному относительному типу адресации.

Как уже отмечалось, при копировании абсолютные и относительные адреса ведут себя по-разному. Особенность это – возможность автоматического изменения относительных адресов при копировании и перемещении формул. Формула, где в качестве операндов используются относительные адреса ячеек, воспринимается Excel как шаблон, а адреса ячеек в таком шаблоне – как средство указания на местоположение ячеек относительно ячейки с формулой. После копирования формула с относительными адресами будет ссылаться не на те ячейки, на которые она ссылалась в исходном месте, а на новые ячейки, адреса которых определяются относительно нового положения формулы.

Пример

Пусть в таблицу введены данные, как представлено на рисунке 208 – в режиме показа формул, а на рисунке 209 – в режиме вычислений.

Рисунок 208

В формуле, находящейся в ячейке С3 адреса А1 и В2 являются относительными и отражают взаимное расположение исходных данных, находящихся в ячейках А1 и В2, и формулы, записанной в ячейке С3: ячейка А1 смещена на две клетки вверх и две клетки влево относительно ячейки С3, ячейка В2 смещена на одну клетки вверх и одну клетку влево относительно ячейки С3.

Рисунок 209

Если скопировать содержимое ячейки С3 в ячейки D3 и E3, будем иметь вид показанный на рисунках 210, 211:

Рисунок 210

Рисунок 211

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

Если изменить формулу, находящуюся в ячейке С3, заменив относительные адреса на абсолютные, и скопировать ее содержимое в ячейки D3:E3, получим в режим показа формул рисунок 212, и в – режиме вычислений рисунок 213:

Рисунок 212

Рисунок 213

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

Изменим адреса ячеек в формуле таким образом, чтобы они были смешанными. Например, пусть первый адрес в формуле будет $А1, а второй - В$2. То есть в первом случае абсолютным является адрес столбца, а относительным является – адрес строки, во втором наоборот, адрес столбца является относительным, а адрес строки – абсолютным. Тогда формулы после копирования будут иметь вид представленный на рисунках 214, 215:

Рисунок 214

Рисунок 215