Практическое занятие №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 Исходные данные для задания
Содержание отчета
Номер практического занятия, тема.
Цель занятия.
Краткие теоретические сведения.
Ответы на контрольные вопросы.
Контрольные вопросы.
Как автоматически заполнить несколько ячеек подряд в строке или столбце комбинациями текста и чисел?
Как автоматически заполнить строку или столбец списком?
Как создать свой собственный список для автозаполнения?
|
