Организация вычислений в электронных таблицах. Абсолютные и относительные ссылки

Вопросы:

·     Что такое ссылка в электронных таблицах?

·     Какие типы ссылок существуют?

·     Как в электронных таблицах осуществляются вычисления?

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

Есть несколько типов ссылок:

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

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

·     Смешанные – это ссылки в формулах, содержащие абсолютную и относительную части.

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

Для этого нужно:

1.  выделить ячейку с формулой;

2.  на вкладке Главная в разделе Буфер обмена нажать копировать (или нажать сочетание клавиш ctrl+С);

3.  выделить диапазон ячеек, в который копируется формула;

4.  на вкладке Главная в разделе Буфер обмена нажать вставить (или нажать сочетание клавиш ctrl+V);

Разберемся подробнее с типами ссылок.

Относительные ссылки.

Использовать относительные ссылки – это все равно, что следовать указанию направления движения по улице. Например: Прогуливаясь по городу, вы поинтересовались у прохожего: «Как пройти к обелиску?». Прохожий ответил: "Вам нужно пройти прямо по улице, затем свернуть налево". Однако, если каждый раз выходить из различных начальных мест и придерживаться этих инструкций, к обелиску вы не попадёте.

То есть относительная ссылка – это ссылка, которая зависит от того, где находится формула.

Например: пусть в ячейку С2 записана формула: =В2+3. В ячейке С2 находится относительная ссылка на ячейку В2. Данную запись табличный процессор воспринимает так: к содержимому ячейки, которая находится слева от ячейки с формулой, нужно прибавить 3.

Если мы скопируем данную формулу и вставим в другое место, то относительная ссылка будет изменяться по следующим законам:

·     если вставлять формулы вдоль строки, то в ссылке изменяется буква в имени столбца;

·     если вставлять формулы вдоль столбца, то в ссылке изменяется номер строки;

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

Таким образом: Относительные ссылки – это ссылки в формулах, которые изменяются при копировании.

Решим задачу с использование относительных ссылок.

Средняя заработная плата по данным Федеральной службы государственной статистики на 2014 г. составила 32 600 рублей. Анализ сведений по отчетам ФСГС показывает, что ежегодно средняя зарплата увеличивается на 8 %. Анализ сделан на основе сравнения зарплат по месяцам за последние 3 года. Сделаем в электронных таблицах расчет предполагаемой средней зарплаты в ближайшие 5 лет.

Внесем в таблицу исходные данные. В ячейку А1 запишем «Год», в А2 – «Текущий», далее в ячейки А3, А4, А5, А6 и А7 соответственно 1, 2, 3, 4, 5.

В ячейку В2 – 32 600. Затем в ячейку В3 запишем формулу: =В2+0,08*В2 и скопируем формулу в диапазон ячеек В4:В7.

Мы в электронной таблице сделали расчет предполагаемой заработной платы через 5 лет. Предположительно зарплата составит в среднем 47 900 рублей.

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

Следующий тип ссылок - Абсолютные ссылки.

Абсолютная ссылка — это ссылка, которая не зависит от того, где находится формула.

Абсолютная ссылка на ячейку или диапазон ячеек будет всегда ссылаться на один и тот же адрес строки и столбца. Если сравнивать с указанием направления движения – то прохожий ответит, например, так: "Обелиск находится на пересечении Кутузовского проспекта и Большой Дорогомиловской улицы". Вне зависимости от места старта это будет приводить к одному и тому же месту.

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

Абсолютная ссылка может быть создана только при наборе формулы, где перед адресом строки и столбца вводится знак доллара $. Например, $D$2. При изменении адреса ячейки, содержащей формулу, абсолютная ссылка не изменится.

Например: в ячейке С2 записана формула: =$В$2+3. При копировании формулы в другие ячейки вдоль строки или столбца таблица будет выглядеть следующим образом.

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

Решим задачу с использованием абсолютных ссылок.

На одном из заводов в некотором городе работают Иванов, Петров и Сидоров. Известно, что Иванов отработал 40 часов, Петров – 30, а Сидоров – 32 часа. Нужно рассчитать зарплату работникам за отработанное время. Известно также, что 1 час рабочего времени стоит 100 рублей.

Заполним таблицу. В ячейку E2 запишем формулу: =$C$1*D2 и скопируем ее в ячейки E3 и E4.

Включим режим отображения формул. Для этого нажимаем вкладку Формула, в разделе зависимости формул выберем показать формулы. Теперь мы видим, что абсолютная ссылка  C1 не изменялась, а относительная ссылка D2 – изменялась.

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

Рассмотрим следующий тип ссылок – Смешанные ссылки.

Если в формуле последовательно нажимать клавишу F4, то знаки доллара перед именем столбца или номером строки добавляются или убираются. Мы получаем так называемые Смешанные ссылки. У смешанных ссылок при копировании остается неизменной либо ссылка на номер строки, либо ссылка на имя столбца.

Пример со смешанными ссылками. В ячейке С2 записана формула: =$В2+3. Здесь ссылка на столбец абсолютная, а ссылка на строку –относительная. При копировании формулы в другие ячейки вдоль строки или столбца таблица будет выглядеть следующим образом.

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

Таким образом, смешанные ссылки – это ссылки в формулах, содержащие абсолютную и относительную части.

Решим задачу с использованием смешанных ссылок.

В магазины поступил товар с артикулами ТВ01, ТВ02 и ТВ03 с соответствующими закупочными ценами 306 руб., 228 руб. и 237 руб. В первом магазине наценка на товар – 17 %, а во втором – 25 %. Нам требуется рассчитать отпускную стоимость товара при наценках, с учётом того, что закупочная цена фиксирована.

Заполним таблицу. В ячейку D4 запишем формулу, содержащую смешанные ссылки. При копировании формулы в другие ячейки мы получим таблицу следующего вида.

Включим режим отображения формул. Для этого нажимаем вкладку Формула, в разделе зависимости формул выберем «Показать формулы».

Обратите внимание! Мы рассчитывали отпускную стоимость товара по одной и той же формуле, исходные данные для которой находились в разных ячейках. У смешанных ссылок при копировании относительная часть адреса изменяется, а абсолютная часть адреса не изменяется.

Важно запомнить:

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

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

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

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