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

Лабораторная работа № 10

Тема: Вычисления в электронных таблицах.
Применение итоговых функций

Время на выполнение – 2 часа

Цель работы: научиться применять формулы в Excel, знать понятие относительной и абсолютной адресации, использовать итоговые функции для вычисления значений.

Основные сведения по теме

Вычисления в электронных таблицах

Формулы

Вычисления в таблицах программы Excel осуществляются при помощи формул. Формула может содержать числовые константы, ссылки на ячейки и функции Excel, соединенные знаками математических операций. Скобки позволяют изменять стандартный порядок выполнения действий. Если ячейка содержит формулу, то в рабочем листе отображается только текущий результат вычисления этой формулы. Чтобы увидеть саму формулу, а не результат ее работы, надо выделить ячейку (сделать ее текущей) и посмотреть на запись, которая отображается в строке формул (рис. 40).

Рис. 40. В ячейке отображается только результат работы формулы,
а сама формула видна в строке формул

Основное правило использования формул в программе Excel состоит в том, что, если значение ячейки действительно зависит от других ячеек таблицы, всегда следует использовать формулу, даже если операцию легко выполнить «в уме»! Это гарантирует, что последующее редактирование таблицы не нарушит ее целостности и правильности производимых в ней вычислений.

Ссылки на ячейки

Взгляните на формулу, представленную на рис. 40. В ней записана операция над содержимым двух ячеек. Вместо чисел в формуле использованы имена ячеек – принято говорить, ссылки на ячейки. Ссылки выполняют роль адресов ячеек, содержимое которых используется в вычислениях. Это означает, что результат расчета зависит от того, какие числа находятся в ячейках, участвующих в вычислении. Таким образом, ячейка, содержащая формулу, является зависимой. Значение в зависимой ячейке подлежит пересчету всякий раз, когда изменяются значения в ячейках, на которые указывают ссылки, входящие в формулу.

Ссылку на ячейку можно задать разными способами:

· во-первых, адрес ячейки можно ввести вручную;

· другой способ состоит в щелчке на нужной ячейке или выборе диапазона, адрес которого требуется ввести. Ячейка или диапазон при этом выделяются пунктирной рамкой.

Все диалоговые окна программы Excel, которые требуют указания номеров или диапазонов ячеек, содержат кнопки, присоединенные к соответствующим полям. При щелчке на такой кнопке диалоговое окно сворачивается до минимально возможного размера, что облегчает выбор нужной ячейки (диапазона) с помощью щелчка или протягивания (рис. 41).

Рис. 41. Диалоговое окно в развернутом и свернутом виде

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

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

Относительная адресация

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

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

Абсолютная адресация

При абсолютной адресации адреса ссылок при копировании формулы не изменяются, так что ячейка, на которую указывает ссылка, рассматривается как постоянная (нетабличная). Для изменения способа адресации при редактировании формулы надо выделить ссылку на ячейку и нажать клавишу F4. Элементы номера ячейки, использующие абсолютную адресацию, предваряются символом $. Например, при последовательных нажатиях клавиши F4 номер ячейки А1 будет записываться как А1, $А$1, А$1 и $А1. В двух последних случаях один из компонентов номера ячейки рассматривается как абсолютный, а другой – как относительный.

Автозаполнение формулами

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

Для примера предположим, что значения в третьем столбце рабочего листа (столбце С) вычисляются как суммы значений в соответствующих ячейках столбцов А и В. Введем в ячейку С1 формулу = А1 + В1. Теперь скопируем эту формулу методом автозаполнения во все ячейки третьего столбца таблицы. Благодаря относительной адресации формула будет правильной для всех ячеек данного столбца. В табл. 2 приведены правила обновления ссылок при автозаполнении вдоль строки иливдоль столбца.

Таблица 2

Правила обновления ссылок при автозаполнении

Ссылка в исходной ячейке	Ссылка в следующей ячейке
при заполнении вправо	при заполнении вниз
А1 (относительная)	В1	А2
$А1 (абсолютная по столбцу)	$А1	$А2
А$1 (абсолютная по строке)	В$1	А$1
$А$1 (абсолютная)	$А$1	$А$1