Лабораторная работа 4. Использование относительной и абсолютной ссылок

Модуль 2.9. Виды адресации

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

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

При использовании относительной ссылки адрес ячейки или интервала вычисляется относительно той ячейки, где эта ссылка использована.

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

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

Задание 1. Создание относительной ссылки.

1. Откройте файл Упражнения.xlsx.

2. Вставьте в рабочую книгу новый лист с именем Упр.9.

3. В столбец А введите несколько целых чисел.

4. В ячейке В1 введите формулу, вычисляющую возведение в степень 2 значения из ячейки А1.

5. Скопируйте полученную формулу в оставшиеся ячейки столбца В. При распространении формулы ссылки в этих формулах будут автоматически откорректированы, это происходит потому, что в ячейке В1 находится относительная ссылка (рис. 9.1).

Заполнение диапазона В2:В5 формулой возведения в степень можно осуществить тремя способами:

выделить диапазон В1:В5, выберите команду Заполнить, Вниз из группы команд едактирование, которая находится на вкладке ГлавнаяРРРедактирование, расположенной на вкладке Главная или (что проще) использовать клавиатурную комбинацию Ctrl + D;

поставить курсов на маркер заполнения ячейки В1 и, не отпуская левую кнопку мыши, протащить курсор до ячейки В5;

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

Рис. 9.1.

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

6. В ячейки D1:E2 введите указанные числа (рис. 9.2).

Рис. 9.2.

7. В ячейку D3 введите формулу, которая будет суммировать две вышележащие ячейки. Эта формула показана в строке формул на рис. 9.2.

8. Распространите полученную формулу на ячейку E3 с помощью маркера заполнения.

9. Скопируйте формулу сложения двух чисел в ячейку D6 с помощью команд Копировать и Вставить.

10. Распространите формулу вдоль строки 6.

11. Объясните, почему в ячейках получился 0.

Задание 2. Использование относительной ссылки.

1. Введите исходные данные, как показано на рис. 9.3.

2. Скопируйте формулу расчета общей стоимости в ячейки D12 и D13 любым способом, описанным в предыдущем задании.

Рис. 9.3.

Абсолютные ссылки

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

Задание 3. Создание абсолютной ссылки.

1. В ячейку С1 введите формулу, показанную на рис. 9.4.

2. Сначала введите знак равно (=), затем укажите мышью на ячейку А1.

3. Нажмите на функциональную клавишу F4, затем продолжите формулу возведения в степень 2.

4. Распространите на все ячейки столбца С формулу, используя маркер заполнения.

Рис. 9.4.

Каждая ячейка столбца С имеет одно и тоже значение, так как в формуле ячейки С1 указан абсолютный адрес.

Признаком абсолютной ссылки являются знаки доллара перед именем столбца и номером строки.

5. Замените относительные ссылки на абсолютные в ячейке D3, используя функциональную клавишу F4 (рис. 9.5).

Рис. 9.5.

6. Скопируйте формулу сложения двух чисел в ячейку D6.

7. Распространите измененную формулу вдоль строки 6.

8. Объясните, почему в ячейке получилось 3.

Задание 4. Использование абсолютной ссылки.

Налог вводится отдельно от вычисления исключительно для удобства: при изменении налога будет гораздо легче изменить один элемент.

1. Добавьте к таблице, показанной на рис. 9.3, еще один столбец для расчета величины налога в рублях (рис. 9.6).

2. В ячейку Е1 введите формулу, показанную в строке формул на рис. 9.6.

3. Ссылку на общую стоимость оставьте относительной.

4. Ссылку на налог сделайте абсолютной, используя функциональную клавишу F4.

5. Полученную формулу скопируйте в остальные ячейки таблицы.

Рис. 9.6.

6. Добавьте еще один столбец, чтобы рассчитать величину налога в долларах.

7. В ячейку D9 введите курс доллара.

8. В ячейку F11 введите формулу, показанную в строке формул на рис. 9.7.

9. Распространите полученную формулу в нижележащие ячейки.

Рис. 9.7.

10. К таблице, показанной на рис. 9.7, добавьте еще один столбец для расчета величины налога в евро.

11. В ячейку F9 введите значение курса евро.

12. Присвойте этой ячейке имя – курс_евро.

13. В ячейке G9 введите формулу, показанную на рис. 9.8.

14. Распространите полученную формулу на нижележащие ячейки.

Рис. 9.8.

Задание 5. Самостоятельно выполните следующие примеры.

Пример 1. Перейдите на лист Упр.4 и произведите расчет итоговых сумм в долларах и евро, используя абсолютную ссылку на курс доллара, указанный в ячейке D9 на листе Упр.9, и имя курс_евро, имеющийся в списке имен книг и Упражнения(рис. 9.9).

Рис. 9.9.

Пример 2. Необходимо начислить праздничные премии работникам организации (10 человек). Ниже показана структура данных, где на местах формул, которые нужно создать, пока стоят знаки вопроса (рис. 9.10).

Месяц
Работник	Пол	Зарплата	Зарплата+Премия
Иванов И.И.	м		?
Петрова П.П.	ж		?
…	…	…	…

Рис. 9.10. Вид таблицы для примера 2

Указания:

1. Если месяц третий, то женщинам устанавливается премия в размере 5000 руб.

2. Если месяц второй, то мужчинам назначается премия в размере 7000 руб.

3. Если месяц первый, то всем работникам назначается премия в размере 10000 руб. К исходной таблице добавьте еще один столбец и рассчитайте зарплату с учетом премии.

4. В остальные месяцы выплачивается только зарплата.

Лабораторная работа 4. Использование относительной и абсолютной ссылок

1. Откройте книгу Лабораторные_работы.xls и на листе Лаб_4 создайте следующие таблицы: таблицу начисления налогов (рис. 9.11) и таблицу расчета заработной платы (рис. 9.12).

2. При заполнении таблицы придерживайтесь следующего порядка расчета:

- поля «Число иждивенцев» и «Начислено» заполняются самостоятельно;

- «Пенсионный фонд» = 1% от «Начислено»;

- «Облагаемый доход» = «Начислено» - «Пенсионный фонд» - «Число иждивенцев» * МРОТ. При вычислении «Облагаемого дохода» используйте абсолютную ссылку на МРОТ;

- «Подоходный налог» = 12% от «Облагаемого дохода»;

- «Итого» = «Пенсионный фонд» + «Подоходный налог»;

- «К выдаче» = «Начислено» – «Итого»;

- «Налоги на ФОТ» = «Ставка» * «Начислено»

При вычислении налогов на ФОТ сначала примените имя для поля «Начислено», затем используйте его в формуле.

Рис. 9.11. Таблица расчета федеральных отчислений

Рис. 9.12. Таблица расчета заработной платы

Вопросы для самоконтроля

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

2. Что такое абсолютная ссылка?

3. Что такое относительная ссылка?

4. Какая функциональная клавиша позволяет получить другой тип ссылки?

5. Для чего необходимо использовать относительные ссылки?

6. Для чего необходимо использовать абсолютные ссылки?

Тесты

1. Какой вид ссылки является абсолютной?

A. А1

B. $A$1

C. $A1

D. A$1

2. Какой вид ссылки является относительной?

A. А1

B. $A$1

C. $A1

D. A$1

3. Что означает знак $ в формуле =В2*С2*$B$7?

A. Расчеты выполняются в долларах

B. Числу в ячейке присвоен денежный стиль

C. На ячейку В7 сделана абсолютная ссылка

D. На ячейку В7 сделана относительная ссылка

4. Какую формулу вычисления цены в рублях необходимо записать в ячейке D2, чтобы потом ее можно было копировать на ячейки D3:D5 и она оставалась верной?

A. =В2*С2/В7

B. =(В2*С2)/В7

C. =В2*С2/$В$7

D. =$В$2*$С$2/$В$7

5. Какую клавишу следует нажать, чтобы ссылка на ячейку стала абсолютной?

A. F2

B. F4

C. ENTER

D. ESC

6. Для какой цели в формулах используют относительные ссылки?

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

B. Для того чтобы при перемещении формулы ссылка на ячейку изменялась.

C. Для того чтобы в ячейке отображались только положительные значения.

D. Для присвоения числам в ячейке положительных значений.

7. Для какой цели в формулах используют абсолютные ссылки?

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

B. Для того чтобы при перемещении формулы ссылка на ячейку изменялась.

C. Для того чтобы в ячейке отображались только положительные значения.

D. Для присвоения числам в ячейке положительных значений.

8. Какая формула будет указана в ячейке D5 при копировании в нее формулы из ячейки D2?

A. =В2*С2

B. =В5*С2

C. =В2*С5

D. =В5*С5

9. Что такое ссылка?

A. То же, что адрес ячейки.

B. Адрес удаленной ячейки.

C. Указанный в формуле адрес ячейки.

10. Какие виды ссылок существуют?

A. Условные и безусловные.

B. Абсолютные и условные.

C. Относительные и абсолютные.

D. Постоянные и изменяемые.