Офисные прикладные программы MS Office 2007: Microsoft Excel
Абсолютные и относительные ссылки
Практически все формулы включают ссылки на ячейки или диапазоны ячеек. Эти ссылки позволяют формулам работать с данными, содержащимися в этих ячейках и диапазонах, а не просто использовать фиксированные значения. Если формула имеет ссылку на ячейку А1, то при изменении значения в этой ячейке формула автоматически будет пересчитана в соответствии с новым значением. Если не использовать ссылки на ячейки, то при необходимости изменения исходных значений используемых в вычислениях придется вручную редактировать формулы. Поэтому следует определиться с тем, какими ссылки могут быть в принципе и чем одни ссылки отличаются от других.
В формулах используется три типа ссылок на ячейки и диапазоны.
- Относительные ссылки. При копировании формул эти ссылки автоматически изменяются в соответствии с новым положением формулы.
- Абсолютные ссылки. Эти ссылки не изменяются при копировании формул.
- Смешанные ссылки. В этих ссылках номер строки (или столбца) является абсолютным, а столбца (строки) - относительным.
Отличительной особенностью абсолютных ссылок являются два знака доллара ($): один перед буквой столбца и второй перед номером строки (например, $А$5). Чтобы поставить два знака доллара ($) в адресе ячейки, следует поставить курсор в любом месте адреса ячейки в строке формул и нажать клавишу F4 на клавиатуре один раз.
В Excel также допускаются смешанные ссылки, в которых только одна часть адреса является абсолютной (например, $А4 или А$4). В этом случае клавишу F4 необходимо нажать два или три раза (соответственно А$4 или $А4). Четвертое нажатие F4 возвращает к относительной ссылке. Например, если необходимо поставить какую-либо ссылку на А1, то первое нажатие клавиши F4 преобразует ссылку на ячейку в $А$ 1, второе - в А$1, третье — в $А1, а четвертое вернет ей первоначальный вид - А1. Нажимайте клавишу F4 до тех пор, пока не появится нужный тип ссылки.
Различие между разными типами ссылок проявляется при копировании формул.
На рис.30 показана таблица, в ячейке D2 которой находится формула умножения количества наименований товара на его цену. Формула выглядит следующим образом: =В2*С2. Если ее скопировать маркером заполнения на ячейки D3 и D4, то получим изображенную на рисунке таблицу. Поскольку в этой формуле используются относительные ссылки, то при копировании формулы в ячейки D3 и D4 они соответствующим образом изменятся, то есть в ячейке D3 получим формулу: =ВЗ*СЗ, а в ячейке D4 соответственно =В4*С4.
Если в ячейке D2 заменить относительные ссылки абсолютными, то получим =$В$2*$С$2.
Если теперь скопировать эту формулу в ячейку D3, то получим неправильный результат. Формулы в ячейках D3 и D2 будут одинаковыми.
Теперь изменим этот пример и подсчитаем комиссионные. Значение процентной ставки комиссионных хранится в ячейке в 7 (рис.31). Перенесем заголовок Всего на одну ячейку вправо, а в D1 впишем =А7.
В результате в ячейке D1 получим Комиссионные. В ячейку D2 введем формулу =В2*С2*$В$7. Количество умножается на цену, а затем результат умножается на процентную ставку комиссионных, значение которой хранится в ячейке В7. Обратите внимание на то, что ссылка на ячейку В7 является абсолютной. Скопировав ячейку D2 в D3, получим =В3*С3*$В$7. Ссылки на ячейки В2 и С2 изменились, а ссылка на ячейку В7 - нет, т.е. мы получили правильный результат.
На рис.32 показана таблица, в которой используются смешанные ссылки. В левом столбце хранится значение длины прямоугольника, а в верхней строке находится ширина. В остальных ячейках вычисляется площадь прямоугольника соответственно данной длине и ширине. Например, в ячейке D5 вычисляется площадь прямоугольника, длина которого - 2, а ширина - 1,5. Для вычисления площади в ячейку С3 вводится формула = $В3*С$2.
Обратите внимание на то, что в формуле используются смешанные ссылки. В ссылке на ячейку В3 абсолютной является ссылка на столбец ($В), а в ссылке на ячейку С2 используется абсолютная ссылка на строку ($2). Скопировав эту формулу во все ячейки диапазона, мы получим правильный результат вычислений. Например, в ячейке F7 будет содержаться такая формула =$B7*F$2.
При использовании в ячейке С3 абсолютных или относительных ссылок результат окажется неверным.