§14 Ссылки в формулах

Рассмотрим пример электронной таблицы, созданной для подсчета стоимости купленных продуктов (пример 14.1).

Для вычисления стоимости каждого продукта нужно умножить содержимое ячейки в столбце Цена на содержимое ячейки в столбце Количество. Так, для подсчета стоимости молока нужно ввести формулу =C3*D3 в ячейку E3. Формула в ячейке E4 для подсчета стоимости хлеба будет =C4*D4. Формулы различаются только номерами строк, как и их местоположение в электронной таблице. По своему виду это одна формула, но записанная для различных исходных данных.

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

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

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

Ссылки в формулах, которые изменяются при копировании, называют относительными.

По умолчанию все ссылки для указания адресов ячеек относительные.

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

Для создания абсолютной ссылки в формуле перед именем столбца и (или) номером строки вводится знак $.

У смешанных ссылок при копировании остается неизменяемой только одна часть: ссылка на номер строки (Е$4) или столбца ($Е4).

(Рассмотрите пример 14.3.)

Пусть для таблицы из примера 14.1 требуется перевести стоимость купленных продуктов из белорусских рублей в российские (пример 14.4). Для перевода стоимости молока нужно его стоимость умножить на курс перевода (из белорусских рублей в российские), т. е. в ячейку F4 ввести формулу =E4*D2. Если копировать эту формулу во все остальные ячейки в столбце Стоимость, то ссылки в формуле будут меняться (будем получать =E5*D3, =E6*D4 и т. д.), и результат будет ошибочным. Чтобы вычисления выполнялись верно, при копировании ссылка на ячейку D2 не должна меняться, т. е. быть абсолютной. Правильная формула имеет следующий вид: =E4*$D$2. При копировании формул в ячейки F5, F6… будем получать: E5*$D$2, E6*$D$2, … .

 Пример 14.1. Подсчет стоимости.

Пример 14.2. Копирование формул. Пусть в ячейку F10 введена формула =B5+C7. Нужно скопировать эту формулу в ячейки E10, F8, F13, H10, I9.

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

Пример 14.3. Примеры ссылок.

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

Ссылка $A$4 будет абсолютной ссылкой на ячейку A4, и при копировании формул, содержащих такую ссылку, ссылка изменяться не будет.

Ссылка Е$4 будет изменяться при копировании формул в другие столбцы, но не будет меняться при копировании формул в другие строки.

Ссылка $Е4 будет изменяться при копировании формул в другие строки, но останется неизменной при копировании формул в другие столбцы.

Изменить тип ссылки можно клавишей F4. Сначала вводим адрес ячейки, который по умолчанию является относительной ссылкой. Затем нажимаем F4. Ссылка меняется на абсолютную. Последующие два нажатия F4 дают смешанные ссылки. Затем опять появляется относительная ссылка.

Пример 14.4. Перевод денежных
единиц из одних в другие. Результат:

В ячейку F4 ввели формулу =E4*$D$2. Для копирования формулы в ячейки F5:F8 можно использовать маркер заполнения. Вначале делаем активной ячейку с формулой (F4), а затем при помощи маркера заполнения выделяем область для копирования формулы.

 
1.  Что такое ссылка?
2.  Сформулируйте принцип относительной адресации.
3.  Какие ссылки можно использовать при создании формул?
4.  В чем отличие относительных, абсолютных и смешанных ссылок?

Упражнения

1.   Для электронной таблицы, изображенной в режиме показа формул, определите, в каких формулах использовали относительные, абсолютные и смешанные ссылки.

Как изменятся формулы при копировании в указанные ячейки?

1.  Из ячейки A4 в ячейку A5.
2.  Из ячейки B4 в ячейку C6.
3.  Из ячейки C1 в ячейку D2.
4.  Из ячейки C2 в ячейку C5.

2. Создайте электронную таблицу для вычисления стоимости канцелярских товаров, купленных к началу учебного года:

 

Для подсчета стоимости тетрадей в ячейку D2 введите формулу =B2*C2. Формулу из ячейки D2 скопируйте в ячейки диапазона D3:D7. Оформите таблицу по своему усмотрению.

3.  Для таблицы из упражнения 2 рассчитайте стоимость товаров, если вам предоставили скидку в 0,1 р. для каждого вида товара. Значение скидки внесите в ячейку B8. (Скидка вычитается из стоимости товара.)

4. Измените формулы для таблицы из упражнения 3 с условием того, что скидка на товар составляет 5 %, а не 0,1 р.

5.   Создайте таблицу для вычисления значений функции у = х2 – 3х + 5 на отрезке [–10; 10], если аргумент изменяется с шагом 1. Скопируйте формулы из ячеек A3 и B2.

6. Создайте таблицу для вычисления значений функции у = –х2 + 4,1х – 3. Начальное значение переменной x хранить в ячейке B2, шаг изменения аргумента — в ячейке C2. В ячейку B5 введите формулу, являющуюся ссылкой на начальное значение, в ячейку С5 — формулу для вычисления значения функции. В ячейке B6 должна быть формула, увеличивающая значение аргумента на значение шага (ссылка на значение шага должна быть фиксированной). Используя маркер заполнения, скопируйте формулу из ячейки В5 вниз, пока не получите 10. Формулу из ячейки С5 скопируйте вниз, пока не получите значение функции в точке x = 10.

7. В таблице заданы показания счетчика учета воды за полугодие. Стоимость 1 кубического метра воды ввести в ячейку В2. Начальное показание счетчика хранится в ячейке С2. Определите стоимость воды для каждого месяца: