ОТНОСИТЕЛЬНЫЕ И АБСОЛЮТНЫЕ ССЫЛКИ НА ЯЧЕЙКИ

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

Теоретические сведения

До выполнения работы разберемся с понятиями абсолютной, смешанной и относительной ссылками.

В предыдущей лабораторной работе при создании формул Вы использовали относительные ссылки. Именно относительные ссылкиформируются в новых формулах по умолчанию (см. пп.1.5–1.9 в лабораторной работе № 2). Использование автозаполнения (а также любого перемещения или копирования) для формул с относительными ссылками автоматически корректирует их адреса на величину переноса. Например, при автозаполнении (или перемещении) формулы со ссылкой на ячейку А1 на одну ячейку вправо, ссылка в формуле изменится на В1, а при перемещении на две ячейки вниз – ссылка превратится в А3 пропорционально расстоянию перемещения.

Т.е., при перемещении или копировании формул с относительными адресами происходит индексирование ссылок (автоматическая корректировка адреса на величину переноса). В рассмотренных ранее примерах лабораторной работы № 2 именно автоматическая корректировка относительных адресов позволила избежать трудоемкого ручного многократного ввода формул в каждую ячейку диапазонов по-отдельности.

Если же необходимо, чтобы при копировании формулы ссылка в ней на определенную ячейку оставалась неизменной, т.е. осуществлялась неиндексируемая (неизменяемая) ссылка на ячейку, то такую ссылку следует определить как абсолютную. Абсолютные адреса при изменении (перемещении или копировании) формул не изменяются, поскольку абсолютная ссылка задает зафиксированную позицию ячейки. Признаком абсолютной ссылки является наличие двух знаков "$", например: $A$1. На рисунке стрелками показано, что при копировании абсолютной ссылки $A$1 в другие ячейки, она не изменяется.

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

Тип адресации (относительная, абсолютная, смешанные) меняется циклически в результате нажатий функциональной клавиши [F4] при вводе в формулу адреса ячейки. Например, ссылка А1 при каждом следующем нажатии клавиши [F4] будет меняться следующим образом: A1 ® $A$1 ® A$1 ® $A1 ® A1 ® $A$1 и т. д. по кругу.

Итак, если в ссылке на ячейку используются два символа $, то она называется абсолютным адресом (например: $A$1), если символов $ в ссылке нет – относительным адресом (например: A1), а если используется один символ $ – смешанным адресом (например: $A1 или A$1, т. е. смешанная ссылка содержит либо абсолютный адрес столбца и относительный адрес строки, либо относительный адрес столбца и абсолютный адрес строки).

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

Задание

1. Организация вычислений в таблице Кафе "РОЗА"

1.1. Изучите теоретические сведения этой лабораторной работы.

1.2. Создайте в своей папке книгу MS Excel с именем Фамилия_3_n (где Фамилия – Ваша фамилия, n – номер Вашего индивидуального варианта).

1.3. Переименуйте Лист1 на Кафе "РОЗА".

1.4. Сделайте копию листа Кафе "РОЗА" (ячейки D6:F11 с таблицей) из файла первой лабораторной работы Фамилия_1_n.xls в файл Фамилия_3_n.xls.

1.5. Измените ширину столбцов и высоту строк согласно содержимому заполненных ячеек.

1.6. Вставьте в верхний колонтитул слева – свою фамилию, справа – факультет (институт).

1.7. В ячейку D1 впишите текст "Динамика, в %" и скопируйте формат ячейки С1 на ячейку D1. Сформируйте в ячейках D2:D6 формулы для расчета динамики продаж по каждому пункту:

Для ячеек D2:D6 выберите формат с учетом отображения сотых долей (двух знаков после запятой).

1.8. Выполните сортировку данных столбца Динамика – по убыванию.

1.9. В ячейку А7 впишите слово "ВСЕГО", а в ячейках В7 и С7 вычислите суммарное значение для каждого из числовых столбцов (функция СУММ). Задайте границы для заполненных ячеек.

1.10. Вставьте столбцы для каждого года с вычислением процентного отношения значений по пунктам. Задайте для этих столбцов процентный формат с отображением значений ячейки с учетом десятых (рис. 3.1).

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

Рис. 3.1. Вид результирующей таблицы Кафе "РОЗА" после выполнения всех вычислений

1.12. Вставьте для каждого из годов (после столбцов "В %") еще два столбца Место (столбцы E и Н) и вычислите ранг (порядковый номер) объема продаж каждого товара с помощью функции РАНГ. Задайте числовой формат ячеек – Общий. Выпишите в свой протокол одну из используемых формул.

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

1.14. Задайте ширину заполненных ячеек по их содержимому.

1.15. С помощью условного форматирования выделите красным полужирным курсивом положительные значения столбца Динамика, в % (см. рис. 3.1).

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

1.17. По ячейкам B1:C6 создайте диаграмму с типом Объемный вариант круговой диаграммы и размещением на отдельном листе (рис. 3.2).

Рис. 3.2. Вид диаграммы

1.18. Сохраните изменения в файле Фамилия_3_n.xls.