Урок 4. Относительная и абсолютная адресация

Цель урока. Научиться использовать в ЭТ относительную, абсолютную и смешанную адресацию ячеек.

Общие сведения

Адрес ячейки в ЭТ может быть относительным, абсолютным или смешанным. Относительный адрес имеет вид А4, абсолютный – $А$4 и смешанный – $А4 или А$4, т.е. приз­наком абсолютного адреса является символ $. С точки зрения вычисления значения по формуле, включающей адрес ячейки, нет разницы, какой вид адресации имеет данная ячейка. Способ адресации приобретает актуальность лишь при копировании значения формулы на соседние ячейки.

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

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

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

Разновидностью абсолютной адресации является смешанная, при которой фиксируется либо столбец, а строка может меняться ($А4), либо строка, а столбец может меняться (А$4).

Задания на урок

А. Создание ЭТ товарного счета с абсолютной адресацией.

Задания. 1. Загрузить с дискеты свой файл ЭТ и скопировать таблицу с листа ”Урок 2_Формат счета” на очередной пустой лист и дать этому листу имя ”Урок 4_Адресация”.

2. Вставить новый столбец перед столбцом D: <Выделить весь столбец D щелчком мыши по его обозначению / Вставка / Столбцы>.

3. Новому столбцу в ячейке D3 дать имя Цена, руб. В этот столбец ниже заносится стоимость 1 шт. комплектующих в руб. путем умножения стоимости 1 шт. в у.е. на курс у.е.

4. В ячейку В18 ввести текст “Курс у.е.”, в ячейку С18 – “30” (одна у.е. стоит 30 руб.) и придать этой ячейке денежный формат. Текст в ячейке В18 прижать к правой границе.

5. В ячейку D4 ввести формулу =С4*С18. Аналогичные формулы нужно ввести в ячейки D5: D13, для чего выполнить копирование формулы из ячейки D4 в указанные ячейки. В результате получим везде значение 0. Если встать на ячейку D5 и посмотреть формулу, то мы увидим =С5*С19. Так как в формуле использованы относительные адреса, то при копировании адрес С4 изменился на С5 (и это правильно), но и адрес С18 изменился на С19, чего быть не должно, т.к. в ячейке С19 ничего нет и, естественно, результат получился нулевым.

6. Для устранения этой ошибки нужно ввести адрес С18 как абсолютный ($С$18 или С$18). Для коррекции формулы нужно встать на ячейку D4, щелкнуть мышью правее формулы в строке формул (или просто выделить адрес С18) и нажимать клавишу F4 до появления адреса $С$18 или С$18, после чего нажать <Enter> (знак $ можно вводить и непосредственно с клавиатуры).

7. Выполнить копирование формулы из ячейки D4 в ячейки D5:D13 и убедиться в том, что получается правильный результат.

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

8. Добавить справа в ЭТ еще один столбец, для чего ввести в ячейку G3 название ”Сумма, руб ” и самостоятельно заполнить его. Оформить новый столбец единым стилем всей таблицы (т.е. выполнить обрамление и заливку).

9. Встать на ячейку F14 и выполнить: <Сервис / Зависимости / Выбрать пункт Влияющие ячейки>. Блок ячеек F4:F13, влияющий на содержимое ячейки F14, выделяется и стрелкой показано его влияние на ячейку F14.

Убрать стрелки: <Сервис / Зависимости / Выбрать пункт Убрать все стрелки>. Снова встать на ячейку F14 и выполнить: <Сервис / Зависимости / Выбрать пункт Зависимые ячейки> и посмотреть результат.

По своему выбору посмотреть зависимости для других ячеек. Вывод зависимостей помогает определить источник ошибки в ячейке (Урок 1).

Б. Создать ЭТ товарного счета для своего варианта. Открыть следующий свободный лист и дать ему имя ”Урок 4_Счет-вариант”. На данном листе создать ЭТ для своего варианта. Задания на разработку ЭТ даются в словесной формулировке, в соответствии с которой вы должны создать таблицу и расположить в них данных по строкам и столбцам.

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

Вариант 1. Разработать ЭТ товарного счета на продажу партии телевизоров следующих моделей:

Sony (диагональ 54 см)	цена без НДС 6450 руб.	кол-во – 1 шт.
Sony (диагональ 45 см)	цена без НДС 4830 руб.	кол-во – 2 шт.
Hitachi (диагональ 54 см)	цена без НДС 5975 руб.	кол-во – 4 шт.
Panasonic (диагональ 54 см)	цена без НДС 5600 руб.	кол-во – 3 шт.

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

Вариант 2. Разработать ЭТ товарного счета на продажу партии обуви следующих моделей:

Кроссовки Nike	Китай	стоимость 150 у.е.	кол-во – 10 шт.
Кроссовки Adidas	США	стоимость 300 у.е.	кол-во – 5 шт.
Ботинки мужские	Россия	стоимость 25 у.е.	кол-во – 15 шт.
Галоши	Россия	стоимость 1 у.е.	кол-во – 20 шт.

В ЭТ отобразить итоговые суммы в рублях, вырученные за каждую модель, а также общую сумму от продажи всей партии. Принять, что на все товары предоставляется скидка 10%. Принять курс у.е.=30 руб, это значение поместить в отдельную ячейку вне таблицы. При изменении курса у.е. вся ЭТ должна автоматически пересчитываться.

Вариант 3. Разработать ЭТ товарного счета на продажу партии товаров народного потребления следующих наименований:

Замок висячий	цена без НДС 58 руб.	кол-во – 10 шт.
Замок врезной	цена без НДС 180 руб.	кол-во – 25 шт.
Радиатор отопительный	цена без НДС 320 руб.	кол-во – 7 шт.
Молоток	цена без НДС 14 руб.	кол-во – 15 шт.

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

Вариант 4. Разработать ЭТ товарного счета на продажу партии видеомагнитофонов следующих моделей:

Funai	стоимость 88 у.е.	кол-во – 5 шт.
Hitachi	стоимость 130 у.е.	кол-во – 3 шт.
JVC	стоимость 107 у.е.	кол-во – 6 шт.
Panasonic	стоимость 207 у.е.	кол-во – 8 шт.

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

Вариант 5. Разработать ЭТ товарного счета на продажу партии книг по Информатике следующих наименований:

Хомоненко А.Д. MS Word	цена без НДС 75 руб.	кол-во – 8 шт.
Гончаров А. MS Excel	цена без НДС 98 руб.	кол-во – 5 шт.
Каратыгин С.А. MS Access	цена без НДС 83 руб.	кол-во – 4 шт.
Винтер Р. MS Office 97	цена без НДС 110 руб.	кол-во – 3 шт.

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

Вариант 6. Разработать ЭТ товарного счета на продажу путевок следующих направлений:

Италия	стоимость 400 у.е.	кол-во – 4 шт.
Греция	стоимость 300 у.е.	кол-во – 5 шт.
Кипр	стоимость 350 у.е.	кол-во – 3 шт.
Израиль	стоимость 380 у.е.	кол-во – 8 шт.

В ЭТ отобразить итоговые суммы в у.е. и рублях, вырученные за путевки в каждую страну, а также общую сумму от продажи всей партии путевок. Принять, что на все путевки предоставляется скидка 5%. Принять курс у.е.=29 руб, это значение поместить в отдельную ячейку вне таблицы. При изменении курса у.е. вся ЭТ должна пересчитываться.

Вариант 7. Разработать ЭТ товарного счета на продажу партии тортов в коробках следующих наименований:

Птичье молоко	цена без коробки – 47	87 руб.	кол-во – 4 шт.
Медовый	цена без коробки – 51	59 руб.	кол-во – 2 шт.
Кокосовый	цена без коробки – 55	03 руб.	кол-во – 3 шт.
Илем	цена без коробки – 51	42 руб.	кол-во – 5 шт.

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

Вариант 8. Разработать ЭТ товарного счета на проживание в санатории в номерах разной категории (б/п - без питания):

Люкс-апартаменты	цена за 1 сутки б/п 1340 руб.	время проживания – 10 дней	число путевок – 3
Полулюкс	цена за 1 сутки б/п 1000 руб.	время проживания – 15 дней	число путевок – 5
Одноместный	цена за 1 сутки б/п 800 руб.	время проживания – 20 дней	число путевок - 8

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

Показать результаты преподавателю, получить его подпись и сохранить документ на своей дискете (результаты урока: _ Лист ”Урок 4_Адресация” – таблица товарного счета на ПК _ Лист ”Урок 4_Счет-вариант” – таблица товарного счета для своего варианта).