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

Подготовка к работе.

Ссылки на ячейки и диапазоны ячеек.

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

Стиль ссылок A1.

По умолчанию Excel использует стиль ссылок A1, определяющий столбцы буквами (от A до IV, всего не более 256 столбцов), а строки номерами (от 1 до 65536). Эти буквы и номера называются заголовками строк и столбцов. Для ссылки на ячейку введите букву столбца, а следом номер строки. Например, ссылка D50 указывает на ячейку, расположенную на пересечении столбца D и строки 50. Для ссылки на диапазон ячеек введите адрес ячейки, находящейся в левом верхнем углу диапазона, двоеточие (:), а затем адрес ячейки, находящейся в правом нижнем углу диапазона. Ниже приведены примеры ссылок.

Для указания ссылки на
Ячейку в столбце A и строке 10	A10
Диапазон ячеек в столбце А и строках с 10 по 20	A10:A20
Диапазон ячеек в строке 15 и столбцах с B по E	B15:E15
Все ячейки в строке 5	5:5
Все ячейки в строках с 5 по 10	5:10
Все ячейки в столбце H	H:H
Все ячейки в столбцах с H по J	H:J
Диапазон ячеек в столбцах с А по E и строках с 10 по 20	A10:E20

Стиль ссылок R1C1.

Также можно использовать стиль ссылок, в котором нумеруются как строки, так и столбцы. Стиль ссылок R1C1 полезен при вычислении положения столбцов и строк в макросах. В стиле ссылок R1C1 Excel указывает положение ячейки буквой "R", за которой идет номер строки, и буквой "C", за которой идет номер столбца. В стиле R1C1 расположение ячейки указывается следующим образом: после буквы R ставится номер строки ячейки, а после буквы С — номер столбца. Например, абсолютная ссылка R1C1 эквивалентна абсолютной ссылке $A$1 в стиле A1. Если ячейка A1 является текущей, то относительная ссылка R[1]C[1] указывает на ячейку, расположенную на одну строку ниже и на один столбец правее, то есть на ячейку B2.

В следующей таблице содержатся примеры ссылок R1C1.

Ссылка	Значение
R[-2]C	Относительная ссылка на ячейку, расположенную на две строки выше и в том же столбце
R[2]C[2]	Относительная ссылка на ячейку, расположенную на две строки ниже и на два столбца правее
R2C2	Абсолютная ссылка на ячейку, расположенную во второй строке и во втором столбце
R[-1]	Относительная ссылка на строку, расположенную выше текущей ячейки
R	Абсолютная ссылка на текущую строку

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

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

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

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

Различия между относительными и абсолютными ссылками.

Относительные ссылки.

При создании формулы ссылки обычно учитывают расположение относительно ячейки, содержащей формулу. В следующем примере ячейка B6 содержит формулу =A5; искомое значение находится на одну чейку выше и левее ячейки B6. Такая ссылка называется относительной. При копировании формулы с относительными ссылками Excel автоматически изменяет ссылки во вставляемой формуле для указания на другие ячейки в соответствии с положением формулы. В приведенном ниже примере формула в ячейке B6 ( =A5), которая ссылается на ячейку, расположенную на одну строку выше и на один столбец левее, скопирована в ячейку B7. Excel изменил формулу в ячейке B7 на =A6, ссылаясь на ячейку, которая расположена на одну строку выше и на один столбец левее чейки B7.

Абсолютные ссылки.

Если необходимо, чтобы ссылки не изменялись при копировании формулы в другую ячейку, воспользуйтесь абсолютными ссылками. Например, если имеется формула, умножающая содержимое ячейки A5 на ячейку C1 (=A5*C1), то при копировании формулы в другую ячейку изменятся обе ссылки. Для создания абсолютной ссылки на ячейку C1, поставьте знак доллара ($) перед той частью ссылки, которая не должна изменяться. Чтобы создать абсолютную ссылку на ячейку C1, поместите знак доллара так, как показано в примере:

=A5*$C$1

Переключение между относительными и абсолютными ссылками.

Если формула записана, но необходимо поменять относительные ссылки на абсолютные (и наоборот), выберите ячейку с формулой. В строке формул выделите ссылку, которую необходимо изменить, и нажмите F4. Каждое нажатие F4 переключает тип ссылки в следующей последовательности: абсолютный столбец и абсолютная строка (например, $C$1); относительный столбец и абсолютная строка (C$1); абсолютный столбец и относительная строка ($C1); относительный столбец и относительная строка (C1). Например, если в формуле выбран адрес $A$1 и нажата F4, ссылка становится A$1. Нажав F4 еще раз, получаем $A1 и т. п.

Трехмерные ссылки.

Трехмерные ссылки используются при необходимости анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. Excel использует все листы, хранящиеся между начальным и конечным именами, указанными в ссылке. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от Лист2 до Лист13 включительно.

Ввод ссылки на одну и ту же ячейку или группу ячеек в нескольких листах при помощи трехмерной ссылки:

Указать ячейку, в которую следует ввести функцию.

Набрать = (знак равенства), ввести имя функции, а затем ввести открывающую круглую скобку.

Указать ярлычок первого листа, на который нужно сослаться.

Удерживая нажатой клавишу SHIFT, указать последний лист, на который необходимо сослаться.

Выделить ячейки, на которые необходимо сослаться.