Практическое занятие №7 Тема: Теория относительности адресов. Относительная и абсолютная адресация ms excel

Цель занятия. Изучение информационной технологии органи­зации расчетов с абсолютной адресацией данных (при работе с константами) в таблицах MS Excel

Краткие теоретические сведения.

В EXCEL существуют два основных способа адресации яче­ек: относительные и абсолютные ссылки. Относительная ссылка показывает, что адрес ячейки вычисляется в относительной системе координат с началом в текущей ячейке. Относительные ссылки имеют вид Al, B1 и т.п. Именно в таком виде мы задавали адреса ячеек в предыдущих уроках. В случае абсолютной ссылки адрес ячейки вычисляется в абсолют­ной системе координат и не зави­сит от текущей ячейки. Абсолютные ссылки имеют вид $А$1, $В$1 и т.д. Таким образом, для указа­ния абсолютного адреса при за­писи формулы необходимо ис­пользовать символ $.

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

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

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

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

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

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

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

2. Откройте файл «Расчеты», созданный в Практических ра­ботах № 1-3 (Файл/Открыть).

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

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

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

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

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

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

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

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

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

= С9/С8.

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

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

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

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

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

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

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

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

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

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

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

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

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

К выдаче = Оклад - Всего удержано, в ячейку G10 введите формулу

= C10-F10.

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

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

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

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

Задания для самостоятельной работы

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

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

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

Содержание отчета

1. Номер практического занятия, тема.

2. Цель занятия.

3. Краткие теоретические сведения.

4. Ответы на контрольные вопросы.

Контрольные вопросы.

1. Как автоматически заполнить несколько ячеек подряд в строке или столбце комбинациями текста и чисел?

2. Как автоматически заполнить строку или столбец списком?

3. Как создать свой собственный список для автозаполнения?