Использование абсолютных и относительных ссылок.

П ри подготовке расчетных формул для таблицы Excel следует четко различать относительные и абсолютные ссылки. Если при записи формулы производится ссылка на ячейку только с использованием имени столбца и номера строки без знака $, то говорят об относительной ссылке. Например, формула в ячейке А5 (см. Рис. 1.5). При копировании формулы, содержащей относительные ссылки, и вставке ее в другое место, ссылки будут изменяться. Например, формула из ячейки А5 копируется в ячейки А6А10. В каждой из этих ячеек будет содержаться формула, которая ссылается на одну ячейку выше.

Если необходимо, чтобы ссылки не изменялись при копировании формулы в другие ячейки, пользуются абсолютными ссылками. Например, в ячейках В4В10 записана формула реализующая вычисление: x=dx*(i-1). В данном случае необходимо, чтобы при копировании формулы из ячейки В4 в ячейки В5В10 во всех ячейках была ссылка на одну и ту же ячейку В1, содержащую значение dx. Поэтому используют абсолютную ссылку на ячейку В1 в виде $B$1. При копировании формулы абсолютные ссылки не изменяются. В рассмотренном примере используется и относительные ссылки на ячейки содержащие значения i.

Таким образом для создания абсолютной ссылки необходимо вставить знак доллара ($) перед той частью ссылки, которая не должна изменяться.

Примеры:

В1 – относительная ссылка (при копировании изменяются имя столбца и номер строки);

$В1 – абсолютная ссылка на столбец и относительная на строку (фиксируется столбец);

В$1 –относительная ссылка на столбец и абсолютная на строку (фиксируется строка);

$B$1 – абсолютная ссылка на ячейку (фиксируются и столбец и строка).

При работе с различными листами ссылка содержит также имя листа, отделенное от ссылки на ячейку восклицательным знаком (!), например Лист1!A1 или Лист1!$B$1.

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

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

В приведенном примере (Рис.1.12) необходимо провести расчет функции f=x+y²+k

где k - коэффициент, значение которого в данном расчете постоянно, но может меняться в зависимости от варианта расчета;

x,y - независимые переменные (аргументы).

В расчетной формуле при обращении к ячейке, где находится значение k, используется полностью абсолютная ссылка $B$2.

При обращении к ячейкам, содержащим X, используется абсолютная ссылка по имени столбца и относительная по номеру строки. При обращении к ячейкам, содержащим Y, наоборот.

Использование имен, определяемых пользователем

Обычно в расчетах при наборе формулы используют имена ячеек, содержащих данные в общепринятом стиле. Например: A1 или R1C1 . Это не всегда удобно, особенно при описании сложных и громоздких вычислений. Пакет EXCEL позволяет присваивать ячейкам уникальные имена, отражающие характер или назначение описываемых величин. Например: Аргумент_1, Масса_детали, Температура_воздуха.

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