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

В формулах EXCEL можно сослаться на другую ячейку используя ее адрес. Адрес ячейки в формуле можно записать по-разному, например: А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);
  • нажмите один раз клавишу F4. Ссылка С1 выделится и превратится в $C$1 (при повторных нажатиях клавиши F4 ссылка будет принимать последовательно вид C$1, $C1, C1, $C$1, …). Ссылка вида $C$1 называется абсолютной, C$1, $C1 – смешанными, а С1 - относительной.

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

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

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

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

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

Пусть в столбце А введены числовые значения. В столбце B нужно ввести формулы для суммирования значений из 2-х ячеек столбца А: значения из той же строки и значения выше. Т.е. в B2 должна быть формула: =СУММ(A1:A2), в B3: =СУММ(A2:A3) и т.д (см. файл примера, лист пример1). Решить задачу просто: записав в B2 формулу: =СУММ(A1:A2), протянем ее с помощью Маркера заполнения в ячейку B3 и ниже. Другим вариантом решения этой задачи является использование Именованной формулы. Для этого:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

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

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 3.9 (14 оценок)

Комментарии

Гость (не проверено)

отлично

lyudosik

отлично