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

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

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

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

Если символ доллара стоит перед буквой (например: $А1), то координата столбца абсолютная, а строки — относительная. Если символ доллара стоит перед числом (например, А$1), то, наоборот, координата столбца относительная, а строки — абсолютная. Такие ссылки называются смешанными.

Пусть, например, в ячейке С1 записана формула =А$1+$В1, которая при копировании в ячейку D2 приобретает вид =В$1+$В2.

Относительные ссылки при копировании изменились, а абсолютные — нет.


Практическая работа

Наименование: Использование стандартных функций. Адресация.

Задание 1. Создать таблицу расчета рентабельности про­дукции. Константы вводить в расчетные формулы в виде абсо­лютной адресации.

Исходные данные представлены на рис.1.


Рис. 1. Исходные данные для задания 1

Порядок работы

1. Запустите редактор электронных таблиц Microsoft Excel (при стандартной установке MS Office выполните Пуск/Программы/ Microsoft Excel).

2. Создайте файл «Расчеты» (Файл/Создать).

3. На новом листе электронной книги «Расчеты» создайте таблицу констант (отпускная цена одного изделия) и основную расчетную таблицу по заданию.

4. Введите исходные данные. При вводе номеров в колонку «А» (числа 1, 2, 3 и т.д.) используйте прием авто заполнения ряда чисел. Для этого наберите два первых числа ряда (числа 1 и 2), выделите их мышкой и подведите курсор к правому ниж­нему углу выделенных ячеек до изменения вида курсора на черный крестик. Прихватите мышью маркер авто заполнения и протяните его вниз до нужного значения — произойдет созда­ние ряда натуральных чисел (арифметическая прогрессия).

5. Выделите цветом ячейку со значением константы — отпуск­ной цены 57,00 р.

Рекомендации. Для удобства работы и формирования на­выков работы с абсолютным видом адресации, рекомендуется при оформлении констант окрашивать ячейку цветом, отлич­ным от цвета расчетной таблицы. Тогда при вводе формул ок­рашенная ячейка (т.е. ячейка с константой) будет вам напоми­нанием, что следует установить абсолютную адресацию (набо­ром символа $ с клавиатуры или нажатием клавиши [F4]).

6. Произведите расчеты во всех строках таблицы. Формулы для расчета:

Выпуск продукции = Количество выпущенных изделий х От­пускная цена одного изделия, в ячейку С7 введите формулу = С5*$Е$2 (ячейка Е2 задана в виде абсолютной адресации); Себестоимость выпускаемой продукции = Количество выпущен­ных изделий х Себестоимость одного изделия, в ячейку С8 введите формулу = С5*С6;

Прибыль от реализации продукции = Выпуск продукции — Се­бестоимость выпускаемой продукции, в ячейку С9 введите формулу = С7-С8;

Рентабельность продукции = Прибыль от реализации продук­ции/Себестоимость выпускаемой продукции, в ячейку СЮ вве­дите формулу = С9/С8.

На строку расчета рентабельности продукции наложите Про­центный формат чисел. Остальные расчеты производите в Де­нежном формате.

Формулы из колонки «С» скопируйте авто копированием (за маркер авто заполнения) вправо по строке в колонки «D» и «Е».

7. Выполните текущее сохранение файла {Файл/Сохранить).

Задание 2. Создать таблицу расчета дохода сотрудников организации. Константы вводить в расчетные формулы в виде абсолютной адресации.

Исходные данные представлены на рис. 2.

Порядок работы

1. На очередном свободном листе электронной книги «Расче­ты» создайте таблицу по заданию.

2. Введите значения констант и исходные данные. Форматы данных (денежный или процентный) задайте по образцу задания.

3. Произведите расчеты по формулам, применяя к констан­там абсолютную адресацию.

Формулы для расчетов

Подоходный налог = (Оклад -Необлагаемый налогом доход) х х % подоходного налога, в ячейку D10 введите формулу = (С10-$С$3)*$С$4;

Отчисления в благотворительный фонд = Оклад х % отчисле­ния в благотворительный фонд, в ячейку ЕЮ введите форму­лу - С10*$С$5;

Всего удержано = Подоходный налог - Отчисления в благотво­рительный фонд, в ячейку F10 введите формулу = D10 + E10; К выдаче = Оклад - Всего удержано, в ячейку G10 введите формулу = C10-F10.


4. Постройте объемную гистограмму по данным столбца «К выдаче», проведите форматирование диаграммы.

5. Переименуйте лист электронной книги, присвоив ему имя «Доход сотрудников».

Рис. 2. Исходные данные для задания 2

6. Выполните текущее сохранение файла (Файл/Сохранить).