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

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

В формулах используется три типа ссылок на ячейки и диапазоны.

· Относительные ссылки. При копировании формул эти ссылки автоматически изменяются в соответствии с новым положением формулы.

· Абсолютные ссылки. Эти ссылки не изменяются при копировании формул.

· Смешанные ссылки. В этих ссылках номер строки (или столбца) является абсолютным, а столбца (строки) ‑ относительным.

Отличительной особенностью абсолютных ссылок являются два знака доллара ($): один перед буквой столбца и второй перед номером строки (например, $А$5). Чтобы поставить два знака доллара ($) в адресе ячейки, следует поставить курсор в любом месте адреса ячейки в строке формул и нажать клавишу F4 на клавиатуре один раз.

В Ехсе1 также допускаются смешанные ссылки, в которых только одна часть адреса является абсолютной (например, $А4 или А$4). В этом случае клавишу F4 необходимо нажать два или три раза (соответственно А$4 или $А4). Четвертое нажатие F4 возвращает к относительной ссылке.

Например, если в начале формулы вы вводите =А1, то первое нажатие клавиши F4 преобразует ссылку на ячейку в $А$1, второе ‑ в А$1, третье — в $А1, а четвертое вернет ей первоначальный вид ‑ А1. Нажимайте клавишу F4 до тех пор, пока не появится нужный тип ссылки.

Рис.33. Копирование формул, которые содержат относительные ссылки

Если в ячейке D2 заменим относительные ссылки абсолютными, то получим =$В$2*$С$2.

Если теперь скопировать эту формулу в ячейку D3, то получим неправильный результат. Формулы в ячейках D3 и D2 будут одинаковыми.

Рис.34. Ссылка на ячейку В7 должна быть абсолютной

Количество умножается на цену, а затем результат умножается на процентную ставку комиссионных, значение которой хранится в ячейке В7. Обратите внимание на то, что ссылка на ячейку В7 является абсолютной. Скопировав ячейку D2 в D3, получим =В3*С3*$В$7.

Рис.35. Использование смешанных ссылок на ячейки

На рис.35 показана таблица, в которой используются смешанные ссылки. В левом столбце хранится значение длины прямоугольника, а в верхней строке находится ширина. Далее вычисляется площадь прямоугольника. Например, в ячейке D5 вычисляется площадь прямоугольника, длина которого ‑ 2, а ширина ‑ 1, 5. В ячейку С3 была введена такая формула: =$В3*С$2

Обратите внимание на то, что в формуле используются смешанные ссылки. В ссылке на ячейку В3 абсолютной является ссылка на столбец ($В), а в ссылке на ячейку С2 используется абсолютная ссылка на строку ($2). Скопировав эту формулу во все ячейки диапазона, мы получим правильный результат вычислений. Например, в ячейке F7 будет содержаться такая формула: $В7*F$2.

Если же в ячейке С3 использовать абсолютные или относительные ссылки, то результат окажется неверным.