Абсолютные, относительные и смешанные ссылки

 

 

Абсолютные ссылки. Эти ссылки не меняются при копировании формул.

Относительные ссылки. При копировании формул эти ссылки автоматически изменяются в соответствии с новым положением формулы.

Смешанные ссылки. В этих ссылках номер строки или столбца является абсолютным, а столбца (строки) – относительным.

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

На рис. 1 показаны результаты копирования формул с различными типами ссылок. При копировании формулы с относительными ссылками Excel не создает точной копии формулы – в копируемой формуле ссылки изменяются в соответствии с новым местоположением формулы.

Исходная формула содержит инструкцию о сложении значений двух ячеек, которые находятся на 15-16 строк ниже и на 1 столбец левее ячейки, в которой находится формула; результат сложения затем делится на значение ячейки с формулой, которая находится на 17 строк ниже и на 1 столбец левее ячейки. При копировании формулы копируется данная инструкция, а не фактическое содержимое ячейки. Как правило, это и требуется пользователю. Вряд ли вам нужно полностью копировать формулу, ведь в таком случае она будет давать те же результаты, что и первоначальная формула.

Однако бывают случаи, когда необходимо, чтобы адреса ячеек копировались полностью. Пример формулы, содержащей абсолютную ссылку, показан на рис. 1. В данном примере в ячейке В6 содержится формула, использующая абсолютные ссылки на все ячейки. Эта формула имеет вид:

=($А$14+$А$15)/$А$16

Обратите внимание, что в формуле перед буквами столбцов и перед номерами строк стоят знаки доллара. Это означает, что данные ссылки являются абсолютными. При копировании этой формулы в ячейку В7 будет получена следующая формула:

=($А$14+$А$15)/$А$16

В данном случае формула не изменилась, поскольку ссылки абсолютные.

В абсолютных ссылках используются два знака доллара: один перед буквой столбца и другой перед номером строки.

В смешанных ссылках только одна часть адреса абсолютная. На рис. 1 в диапазоне В10:В13 показано действие смешанных ссылок.

Рис.10.1

 

 

В каких случаях используется смешанная ссылка? Например, она необходима в ситуации, показанной на рис. 2. Этот лист будет содержать таблицу чисел, в каждой ячейке которой должно находиться значение, взятое из столбца А, умноженное на значение из строки 1. Формула в ячейке В2 выглядит следующим образом:

=В&1*$А2

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

=G$1*$А5

Рис.10.2

 

 

Изменение типа ссылок

 

 

Неотносительные (т. е. абсолютные и смешанные) ссылки можно ввести вручную, вставив в нужных местах знаки доллара. Можно воспользоваться удобным клавиатурным эквивалентом – клавишей F4. При вводе ссылки в ячейку – либо вручную, либо путем указания – нужно нажать клавишу F4 несколько раз, чтобы программа “прокрутила” по циклу все четыре типа ссылок.

Например, если в начале формулы вы вводите =А1, то первое нажатие клавиши F4 преобразует ссылку на ячейку в $A$1, второе – в А$1, третье – в $А1, а четвертое вернет ей первоначальный вид – А1. Нажмите клавишу F4 до тех пор, пока не появится нужный тип ссылки.

Когда вы даете имя ячейке или диапазону, то по умолчанию используется абсолютная ссылка на это имя. Например, если вы присвоили диапазону А1:А12 имя Прогноз_Продаж, то в поле Формула диалогового окна Присвоение имени ссылка на этот диапазон будет выглядеть так: $A$1:$A$12. Почти всегда это и нужно пользователю. Таким образом, если скопировать формулу, в которой содержится поименованная ссылка, то в скопированной формуле эта ссылка останется без изменений.

 

 

Ссылки на ячейки за пределами текущего рабочего листа

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