Типы ссылок EXCEL на ячейку: относительная (A1), абсолютная ($A$1) и смешанная (A$1) адресация

В формулах EXCEL можно сослаться на значение другой ячейки используя ее адрес (=А1). Адрес ячейки в формуле можно записать по-разному, например: А1 или $A1 или $A$1. То, каким образом вы введете адрес в формулу, будет зависеть, как он будет модифицироваться при ее копировании в другие ячейки листа. Это пригодится при как построении обычных формул на листе, так и при создании Именованных формул , задания правил Условного форматирования и при формировании условий Проверки данных .

В подавляющем большинстве формул EXCEL используются ссылки на ячейки. Например, если в ячейке В1 содержится формула =А1+5 , то означает, что в ячейку В1 будет помещено значение ячейки А1 находящейся на пересечении столбца А и строки 1 , к которому прибавлено число 5. Также в формулах используются ссылки на диапазоны ячеек, например, формула =СУММ(А2:А11) вычисляет сумму значений из ячеек А2 , А3 , ... А11 . Однако, формула =СУММ($А$2:$А$11) также вычисляет сумму значений из тех же ячеек. Тогда в чем же разница? Разница проявляется при копировании этой формулы в соседние ячейки.

Абсолютная адресация (абсолютные ссылки)

Для создания абсолютной ссылки используется знак $. Ссылка на диапазона записывается ввиде $А$2:$А$11 . Абсолютная ссылка позволяет при копировании формулы зафиксировать  адрес диапазона или адрес ячейки. Рассмотрим пример.

Пусть в ячейке В2 введена формула =СУММ( $А$2:$А$11 ) , а в ячейке С2 формула =СУММ(А2:А11). Скопировав формулы вниз, например с помощью Маркера заполнения, во всех ячейках столбца В получим одну и ту же формулу =СУММ( $А$2:$А$11 ) , т.е. ссылка на диапазон ячеек при копировании не изменилась . А в столбце С получим другой результат: в ячейке С3 будет формула =СУММ(A3:A12) , в ячейке С4 будет формула =СУММ(A4:A13) и т.д. Т.е. при копировании ссылка была модифицирована .

Какая формула лучше? Все зависит от вашей задачи: иногда при копировании нужно фиксировать диапазон, в других случая это делать не нужно.

Другой пример.

Пусть в диапазоне А1:А5 имеются числа (например, зарплата сотрудников отдела), а в С1 – процент премии установленный для всего отдела. Для подсчета премии каждого сотрудника необходимо все зарплаты умножить на % премии. Рассчитанную премию поместим в диапазоне В1:В5 . Для этого введем в ячейку В1 формулу =А1*С1 . Если мы с помощью Маркера заполнения протянем формулу вниз, то получим в В2:В5 нули (при условии, что в диапазоне С2:С5 нет никаких значений). В ячейке В5 будем иметь формулу =А5*С5 (EXCEL при копировании формулы модифицировал ссылки на ячейки, т.к. их адреса не были записаны в виде абсолютных ссылок).

Чтобы выйти из ситуации - откорректируем формулу в ячейке В1 .

• выделите ячейку В1 ;
• войдите в режим правки ячейки (нажмите клавишу F2 ) или поставьте курсор в Строку формул ;
• поставьте курсор на ссылку С1 (можно перед С , перед или после 1 );
• нажмите один раз клавишу F 4 . Ссылка С1 выделится и превратится в $ C $1 (при повторных нажатиях клавиши F 4 ссылка будет принимать последовательно вид C $1, $ C 1, C 1, $ C $1 , …). Ссылка вида $ C $1 называется абсолютно , C $1, $ C 1 – смешанными, а С1 - относительной .

Такм образом, введем в В1 формулу =А1*$С$1 . Это можно сделать и в ручную, введя знак $ перед буквой столбца и перед номером строки.

Нажмем ENTER и протянем ее вниз. Теперь в В5 будет правильная формула =А5*$С$1 . Всем сотрудникам теперь достанется премия :).

Относительная адресация (относительные ссылки)

Введем в ячейку B1 формулу =А1 , представляющую собой относительную ссылку на ячейку А1 . Что же произойдет с формулой при ее копировании в ячейки расположенные ниже В1 ? После протягивания ее вниз Маркером заполнения , в ячейке В5 будет стоять формула =А5 , т.е. EXCEL изменил первоначальную формулу =A1 . При копировании вправо в ячейку С1 формула будет преобразована в =В1.

Теперь примеры.

Пусть в столбце А введены числовые значения. В столбце B нужно ввести формулы для суммирования значений из 2-х ячеек столбца А : значения из той же строки и значения из строки выше.

Т.е. в B2 должна быть формула: =СУММ(A1:A2) , в B3 : =СУММ(A2:A3) и т.д.

Решить задачу просто: записав в B2 формулу =СУММ(A1:A2) , протянем ее с помощью Маркера заполнения в ячейку B3 и ниже.

Альтернативное решение

Другим вариантом решения этой задачи является использование Именованной формулы . Для этого:

• выделите ячейку B2 (это принципиально при использовании относительных ссылок в Именах ). Теперь B2 – активная ячейка;
• на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
• в поле Имя введите, например Сумма2ячеек ;
• убедитесь, что в поле Диапазон введена формула =СУММ(A1:A2)
• Нажмите ОК.

Теперь в B2 введем формулу = Сумма2ячеек . Результат будет тот, который мы ожидали: будет выведена сумма 2-х ячеек из столбца слева (см. файл примера , лист пример1 ). Если формулу ввести в ячейку B5 , то она будет суммировать ячейки A4:A5 , если ввести в D10 , то – ячейки С9:С10 .

Другими словами, будут суммироваться 2 ячейки соседнего столбца слева, находящиеся на той же строке и строкой выше. Ссылка на диапазон суммирования будет меняться в зависимости от месторасположения формулы на листе, но «расстояние» между ячейкой с формулой и диапазоном суммирования всегда будет одинаковым (один столбец влево).

Относительная адресация при создании формул для Условного форматирования.

Пусть необходимо выделить в таблице, содержащей числа от 1 до 100, значения больше 50, причем, только в четных строках (см. файл примера , лист пример2 ). Построим такую таблицу:

Создадим правило для Условного форматирования :

• выделите диапазон таблицы B 2: F 11 , так, чтобы активной ячейкой была B 2 (важно выделить диапазон начиная с B 2 , а не с F 11 . Во втором случае, активной ячейкой будет F 11 );
• вызовите инструмент Условное форматирование ( Главная/ Стили/ Условное форматирование/ Создать правило/ использовать формулу для … );
• введите формулу =И(ОСТАТ($A2;2)=$I$1;B2>50) ;
• выберите Формат ;
• нажмите ОК

Важно отметить, что, если бы, при создании правила, активной ячейкой была F11 , то формулу необходимо было переписать: =И(ОСТАТ($A11;2)=$I$1;F11>50) . Поменять необходимо только ссылки незафиксированные знаком $: B2 на F11 и $A2 на $A11 .

Внимание! При использовании относительной адресации в Именованных формулах , Именованных диапазонах , Условном форматировании , Проверке данных (примеры см. в соответствующих статьях) необходимо следить, какая ячейка является активной в момент создания формулы (активной может быть только одна ячейка на листе, не смотря на то, что выделено может быть несколько).

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

Смешанные ссылки имеют формат =$В3 или =B$3 . В первом случае при копировании формулы фиксируется ссылка на столбец B , а строка может изменяться в зависимости при копировании формулы.

Предположим, у нас есть столбец с ценами в диапазоне B 3: B 6 (см. файл примера , лист пример3 ). В столбцах С, D , Е содержатся прогнозы продаж в натуральном выражении по годам (в шт.). Задача: в столбцах F , G , H посчитать годовые продажи в рублях, т.е. перемножить столбцы С, D , Е на столбец B . Использование механизма относительной адресации позволяет нам ввести для решения задачи только одну формулу. В ячейку F вводим: =$В3*C3 . Потом протягиваем формулу маркером заполнения вниз до F 6 ,

а затем весь столбец таблицы протягиваем вправо на столбцы G и H .

Обратите внимание, что в формуле =$В3*C3 перед столбцом B стоит значок $. При копировании формулы =$В3*C3 в ячейки столбцов F, G и H , этот значок $ говорит EXCEL о том, что ссылку на столбец B модифицировать не нужно. А вот перед столбцом С такого значка нет и формула в ячейке H6 примет вид =$В6*E6 .

Вводим знак $ в адрес ячейки

Существует несколько возможностей при вводе формулы ввести знак $ в адрес ячейки или диапазона. Рассмотрим ввод на примере формулы =СУММ($А$2:$А$5)

1. Ввести знак $ можно вручную, последовательно вводя с клавиатуры все знаки =СУММ($А$2:$А$5)

2. С помощью клавиши F4 (для ввода абсолютной ссылки):

• Введите часть формулы без ввода $: =СУММ(А2:А5
• Затем сразу нажмите клавишу F4 , знаки $ будут вставлены автоматически: =СУММ( $А$2:$А$5
• Для окончания ввода формулы нажмите ENTER.

Если после ввода =СУММ(А2:А5 в формуле передвинуть курсор с помощью мыши в позицию левее,

а затем вернуть его в самую правую позицию (также мышкой),

то после нажатия клавиши F4 , знаки $ будут автоматически вставлены только во вторую часть ссылки! =СУММ( А2:$А$5

Чтобы вставить знаки $ во всю ссылку, выделите всю ссылку А2:$А$5 или ее часть по обе стороны двоеточия, например 2:$А , и нажмите клавишу F4. Знаки $ будут автоматически вставлены во всю ссылку $А$2:$А$5

3. С помощью клавиши F4 (для ввода относительной ссылки).

• Введите часть формулы без ввода $: =СУММ(А2:А5
• Затем сразу нажмите клавишу F4 , будут автоматически вставлены знаки $: =СУММ( $А$2:$А$5
• Еще раз нажмите клавишу F4 : ссылка будет модифицирована в =СУММ( А$2:А$5 (фиксируются строки)
• Еще раз нажмите клавишу F4 : ссылка будет модифицирована в =СУММ($ А2:$А5 (фиксируется столбец)
• Еще раз нажмите клавишу F4 : ссылка будет модифицирована в =СУММ( А2:А5 (относительная ссылка). Последующие нажатия изменяют ссылку заново по кругу.
• Для окончания ввода нажмите ENTER.

Чтобы изменить только первую или втрорую часть ссылки - установите мышкой курсор в нужную часть ссылки и последовательно нажимайте клавушу F4.

"СуперАбсолютная" адресация

В заключении расширим тему абсолютной адресации. Предположим, что в ячейке B 2 находится число 25, с которым необходимо выполнить ряд вычислений, например, возвести в разные степени (см. файл примера , лист пример4 ). Для этого в столбце C напишем формулу возведения в степень (значения степени введем в столбец D ): =$B$2^$D2 .

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

• при копировании формулы из С3 в Н3 – формула не изменится, и мы получим правильный результат 625 ;
• при вставке нового столбца между столбцами А и В – формула превратится в =$C$2^$E3 , но мы снова получим правильный результат 625 .

Все правильно, т.к. это и есть суть абсолютной адресации: ссылки автоматически модифицируются для сохранения адресации на нужные ячейки при любых модификациях строк и столбцах листа (ну, кроме удаления ячейки с формулой, конечно). Однако бывают ситуации, когда значения на лист попадают из внешних источников. Например, когда созданный пользователем макрос вставляет внешние данные в ячейку B 2 (т.е. всегда во второй столбец листа). Теперь, при вставке столбца между столбцами А и В – формула как и раньше превратится в =$C$2^$E3 , но т.к. исходное число (25) будет вставляться макросом не в С2 , а по прежнему в ячейку B 2 , и мы получим неправильный результат.

Вопрос: можно ли модифицировать исходную формулу из С2 ( =$B$2^$D2 ), так чтобы данные все время брались из второго столбца листа и независимо от вставки новых столбцов?

Решение заключается в использовании функции ДВССЫЛ() , которая формирует ссылку на ячейку из текстовой строки. Если ввести в ячейку формулу: =ДВССЫЛ("B2") , то она всегда будет указывать на ячейку с адресом B2 вне зависимости от любых дальнейших действий пользователя, вставки или удаления столбцов и т.д.

Небольшая сложность состоит в том, что если целевая ячейка пустая, то ДВССЫЛ() выводит 0, что не всегда удобно. Однако, это можно легко обойти, используя чуть более сложную конструкцию с проверкой через функцию ЕПУСТО() :

=ЕСЛИ(ЕПУСТО(ДВССЫЛ("B2"));"";ДВССЫЛ("B2"))

При ссылке на ячейку В2 с другого листа =ДВССЫЛ("пример4!B2") может возникнуть и другая сложность: при изменении названия листа пример4 – формула перестает работать. Но это также можно обойти – см. пример из статьи Определяем имя листа .

Другим способом заставить формулу ссылаться на один и тот же столбец является использование функции СМЕЩ() – об этом читайте статью Как заставить формулу все время ссылаться на один и тот же столбец .