Абсолютная ссылка в Excel - описание, примеры.

В любом, даже базовом пакете "Майкрософт Офис" содержится мощный табличный редактор Excel ("Эксель"). Список доступных в нем функций поистине впечатляет: начиная от возможности сортировки и фильтрации данных и заканчивая возможностью построения на их основе сводных диаграмм. И практически невозможно представить ни один хранимый в редакторе набор данных без вычисляемых посредством формул строк и столбцов.

Формулы в "Эксель"

Любая формула – это команда, содержащая указание, какие действия произвести с той или иной ячейкой. И когда пользователь сталкивается с необходимостью скопировать, «протянуть» формулу на всю строку или столбец, то понимает, что в некоторых случаях часть формулы или формула целиком должна остаться неизменной. Так пользователь узнает, что есть в Excel абсолютные и относительные ссылки. Рассмотрим эти понятия более детально.

Ссылка относительная

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

Рассмотрим простой пример. Создадим небольшой набор данных, состоящий из двух столбцов: «Сотрудник» и «Оклад»:

В параметрах "Экселя" зададим стиль ссылок А1 - такая адресация удобна и понятна большинству пользователей. Здесь А, В, С – имена столбцов, а строки пронумерованы. Таким образом, у ячейки с данными «Закирова Е. М.» адрес - А4. Это небольшое отступление понадобится, когда станем разбираться со ссылками в формулах.

Теперь представим, что мы хотим рассчитать на основе данных по окладу заработную плату каждого сотрудника. Поставим в ячейку С2 знак равенства и введем следующую формулу (основываясь на сведениях, что оклад составляет 40 % от зарплаты): =B2*100/40. Здесь В2 – это оклад первого сотрудника в таблице. Нажмем Enter и подведем указатель мыши к правому нижнему краю ячейки С2, дожидаясь, пока указатель не примет форму тонкого черного крестика. Удерживая нажатой левую клавишу мыши, протянем формулу вниз до конца таблицы, то есть до ячейки С7 (можно заменить данное действие двойным кликом мыши по правому нижнему краю ячейки). Столбец автоматически заполнится данными:

Формулы данных ячеек будут следующими:

Как видим, когда мы протянули формулу вниз по вертикали, имя столбца осталось без изменения (В), а вот номер строчки последовательно изменился. Аналогично, копируя формулу по горизонтали, мы получим неизменное значение строки при изменяющемся номере столбца. Поэтому и ссылка называется «относительная» - копии первой введенной формулы будут изменять ссылку относительно своего положения в диапазоне ячеек листа.

Как видим, разобраться с тем, что такое относительная ссылка в Excel, совсем не трудно.

Перейдем к рассмотрению следующих видов ссылок.

Ссылка абсолютная

Абсолютная ссылка в Excel – следующий распространенный вид ссылок. В этом случае при копировании формулы фиксируются строка и столбец, на которые идет ссылка в формуле.

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

Введем для примера еще один набор данных – месячная надбавка к окладу, одинаковая для всех сотрудников:

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

Когда мы нажмем Enter, то увидим, что в данной ячейке зарплата правильно пересчиталась. А вот когда мы протянем формулу на всех сотрудников, то у них зарплата не пересчитается, ведь используемая относительная ссылка попыталась взять значения из G4..G8, где абсолютно ничего нет. Дабы избежать подобной ситуации, необходимо, чтобы использовалась абсолютная ссылка в Excel. Чтобы зафиксировать столбец или строку, которые изменяться при копировании формулы не должны, необходимо поставить соответственно возле имени столбца или номера строки знак доллара ($).

Изменим нашу формулу на следующую:

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

Рядом с понятием "абсолютная ссылка" в Excel всегда идет понятие ссылки смешанной.

Ссылка смешанная

Рассмотрим этот тип выражений. Смешанная ссылка – это ссылка, у которой при копировании изменяется номер столбца при неизменном номере строки или наоборот. При этом знак доллара стоит соответственно перед номером строки (А$1) либо перед номером столбца ($А1) – то есть перед тем элементом, который изменяться не будет.

Ссылка смешанная применяется намного чаще, чем истинная абсолютная ссылка. Например, даже в простом предыдущем примере мы вполне могли бы заменить формулу =(B2+$G$3)*100/40 на =(B2+G$3)*100/40 и получить тот же самый результат, ведь мы выполняли копирование формулы по вертикали, и номер столбца в любом случае остался бы неизменным. И это не говоря уж о ситуациях, когда действительно надо зафиксировать только номер строки или столбца, а остальное оставить доступным для изменения.

Интересный факт

Интересно будет узнать, что абсолютная ссылка в Excel может быть задана не только самостоятельным указанием знака доллара перед номером строки и/или столбца. "Эксель" позволяет путем нажатия клавиши F4 после указания адреса ячейки выбрать вид ссылок – при первом нажатии ссылка с относительной изменится на абсолютную, при втором – на смешанную с фиксированным номером строки, при третьем – на смешанную с фиксированным номером столбца, ну а при следующем нажатии ссылка снова примет вид относительной. Менять вид ссылки в Excel таким образом очень удобно, ведь при этом нет необходимости прибегать к смене раскладки на клавиатуре.