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

Относительную адресацию также рассмотрим на примере построения формул. Введем в ячейку 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 ячейки соседнего столбца слева, находящиеся на той же строке и строкой выше. Ссылка на диапазон суммирования будет меняться в зависимости от месторасположения формулы на листе, но «расстояние» между ячейкой с формулой и диапазоном суммирования всегда будет одинаковым (один столбец влево).

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

Смешанные ссылки имеют формат =$В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