Ссылки в формулах

Согласитесь достаточно трудно было бы, если бы в формулах необходимо было вводить значения снова и снова. Электронные таблицы потеряли бы свою «мощь» если бы отсутствовал механизм позволяющий использовать ссылки в формулах. Проще говоря, ссылка это простой механизм, указывающий, что в формуле необходимо использовать/подставлять значение из другой ячейки, адрес которой указан ссылкой.

Три типа ссылок

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

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

Самый распространенный вид ссылок. Выглядит как А3, В1 и т.д. и т.п. Почему же такие ссылки называются относительными? Всё просто при копировании формулы, вот сейчас ключевой момент понимания относительности и абсолютности ссылок, изменяются ссылки, используемые внутри формулы. Измениться может строка или столбец, а может и то и другое. Происходит это, потому что относительная ссылка указывает, по сути, не на определенную ячейку, а на смещение, на котором находятся значение используемые в формуле относительно ячейки с формулой.

Например, пусть в ячейке В1 будет формула =А1+А2. В формуле использованы две относительные ссылки, Excel будет воспринимать их как сложение двух чисел из ячеек, первая из которых находится на один столбец левее, а вторая на один столбец левее и одну строку ниже. То есть воспринимается не как указание на конкретную ячейку, а ссылку относительно текущей позиции формулы. Поэтому ссылка и называется относительная. Если теперь мы скопируем формулу и вставим ее в ячейку С2 то формула примет вид =В2+В3.

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

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

Смешанные ссылки.

Что было раньше, яйцо или курица? Какой тип ссылок является частным случаем другой, абсолютная или смешанная? Абсолютная ссылка точно указывает на определенную ячейку, указывая знак «$» мы как бы фиксируем имя столбца или номер строки. Фиксируем его фактический в относительной ссылке, и если в абсолютной ссылке фиксированы и столбец и строка. То в смешанной что то одно. Отсюда и вопрос абсолютная ссылка – это смешанная у которой фиксирована строка и столбец. Или относительная – это абсолютная у которой фиксирован только один элемент ссылки. Да и соответственно при копировании формулы содержащей смешанную ссылку изменяться будет только незафиксированный элемент. Так для ссылки $A10 при копировании будет изменяться строка, но ни когда столбец.

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

Кстати, а знаете ли к чему приводит нажатие клавиши «F4» при вводе формулы? Если при вводе формулы, вы вводите ссылку то нажатие «F4» будет по циклу менять тип редактируемой ссылки. Перебираться варианты будут в следующей последовательности А1 — $A$1 – A$1 — $A1.

Кстати вариант два. Ссылки на именованные диапазоны всегда абсолютные. Это и логично, задавая какому то диапазону имя, Вы предполагаете что это имя будет всегда указывать именно на этот диапазон начиная вот отсюда и вон до туда. Он не должен «плавать» если формула вдруг поменяет своё местоположение. Поэтому и определенно что именованные диапазоны всегда представляются абсолютными ссылками.

Ссылки на другие рабочие листы

Ссылаться можно не только на ячейки в текущей рабочей книге. Можно ссылаться и на ячейки расположенные на других рабочих листах. Для этого достаточно вначале ссылки указать имя рабочего листа. Написать что то вроде:

=Имя_рабочего_листа!адрес_ячейки

Можно воспринимать восклицательный знак как – внимание ссылка на другой лист.

=Лист2!А$2

В этом примере мы указываем, что данные необходимо получить с рабочего листа «Лист2», из ячейки А2. При этом если мы будем по текущему рабочему листу копировать формулу, то изменяться будет только имя столбца, ссылка на рабочий лист и строку будет неизменна.

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

=`Расходы на взятки`!A$2

Ссылки на другие рабочие книги

Ну и наконец, ссылаться можно и на другие рабочие книги. При этом ссылка будет выглядеть как:

=[Имя_рабочей_книги]Имя_рабочего_листа!адрес_ячейки

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

=[Бухгалтерия.xlsx] ` Расходы на взятки`!A$2

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

=’c:\Мои документы\[Черная бухгалтерия.xlsx]Расходы на взятки`!A$2

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