Практическая работа № 13
Цель: закрепить умения учащихся составлять формулы с относительными и абсолютными ссылками в электронных таблицах Microsoft Excel.
Приобретаемые навыки и умения: Научиться использовать формулы для вычислений, освоить операцию копирования, научиться использовать абсолютные и относительные адресные ссылки, совершенствовать навыки работы с электронным документом
Время выполнения: 2 часа.
Форма организации работы: практическая работа.
Правила по технике безопасности:
Категорически запрещается:
- Трогать разъемы соединительных кабелей;
- Прикасаться к питающим проводам и устройствам заземления; включать и выключать аппаратуру без разрешения преподавателя.
- При самопроизвольном отключении аппаратуры или появлении необычного звука немедленно сообщите об этом преподавателю.
- Недопустимо размещать на системном блоке, мониторе и периферийных устройствах посторонние предметы: книги, листы бумаги и т. п. Это приводит к постоянному или временному перекрытию вентиляционных отверстий.
- Запрещается внедрять посторонние предметы в эксплуатационные или вентиляционные отверстия компонентов компьютерной системы.
- Запрещается производить какие- либо операции, связанные с подключением или перемещением компонентов компьютерной системы без предварительного отключения питания.
Оборудование рабочего места:
1. ПЭВМ.
2. Методические указания по выполнению практической работы.
3. Программа Excel.
4. Индивидуальные задания.
5. Карточки рефлексии.
I. Краткие теоретические сведения:
Относительные, абсолютные и смешанные ссылки
При записи формулы с целью её дальнейшего копирования нужно определить способ автоматического изменения адресов ячеек, входящих в неё. Для этого в ЭТ определены понятия относительной и абсолютной адресации.
Адрес ячейки относителен, если при копировании формулы он автоматически меняется, «подстраиваясь» под новое положение формулы. Например, когда в ячейку В4 вводится формула =В1+В2, это означает, что в ней к содержимому ячейки, расположенной тремя ячейками левее, прибавится содержимое ячейки, расположенной двумя ячейками левее. И если скопировать эту формулу в ячейку С4, то формула примет вид =С1+С2. Обычно, все адреса в ЭТ заданы относительными.
Абсолютный адрес ячейки при копировании формулы не изменяется. Для его обозначения в ЭТ Excel используется знак $. Например, формула =$В$1+$В$2 при копировании не изменит адресов (а значит, и содержимого) ячеек, входящих в её состав. Допустимо использование и смешанной адресации: $B1, B$2. Это означает, что при копировании один элемент адреса меняется, а другой – нет.
В формулах при обращении к ячейкам используется два способа адресации — абсолютная и относительная адресации. При использовании относительной адресации копирование, перемещение формулы, вставка или удаление строки (столбца) с изменением местоположения формулы приводят к перестраиванию формулы относительно её нового местоположения. В силу этого сохраняется правильность расчётов при любых указанных выше действиями над ячейками с формулами. В некоторых же случаях необходимо, чтобы при изменении местоположения формулы адрес ячейки (или ячеек), используемой в формуле, не изменялся. В таких случаях используется абсолютная адресация. В приведенных выше примерах адресов ячеек и диапазонов ячеек адресация является относительной.
Примеры абсолютной адресации (в Microsoft Excel): $ A$10; $B$5:$D$12; $B$5+$D$12; $M10; K$12 (в предпоследнем примере фиксирован только столбец, а строка может изменяться, в последнем — фиксирована строка, столбец может изменяться).
Примеры относительной адресации: в Microsoft Excel): A10; B5:D12; M10; K12. Т.е. данные вводимые в ячейку с относительной адресацией могут меняться в процессе копирования формулы.
Смешанные ссылки
В некоторых формулах для корректного копирования не требуется полного преобразования ссылки на ячейку из относительной в абсолютную.
Иногда необходимо только обозначить букву столбца или номер строки, которые должны остаться неизмененными во всех копиях исходной формулы.
Ссылки, в которых одна часть относительна, а другая абсолютна, называются смешанными.
Записывается с символом $ только перед буквенной частью:
Записывается с символом $ только перед числовой частью:
II. Порядок выполнения работы:
1. Внимательно изучить теоретический материал, делая для себя записи в тетрадь.
2. Загрузить программу Excel.
3. Создать и рассчитать таблицу на первом листе книги:
ЗАДАНИЕ №1
Создать таблицу расчета рентабельности продукции. Константы вводить в расчетные формулы в виде абсолютной адресации. Исходные данные представлены в таблице 1.
Таблица 1
1. Запустите Мiсrosоft Ехсеl (ПУСК/ Программы/ Мiсrosоft Office / Microsoft Excel).
2. Создайте таблицу «Расчет рентабельности продукции»
3. Введите исходные данные. При вводе номеров в колонку «А» (числа 1,2, 3 и т.д.) используйте прием автозаполнения ряда чисел.
4. Выделите цветом ячейку со значением константы - отпускной цены 57,00 р.
Рекомендации. Для удобства работы и формирования навыков работы с абсолютным видом адресации, рекомендуется при оформлении констант окрашивать ячейку цветом, отличным от цвета расчетной таблицы. Тогда при вводе формул окрашенная ячейка (т.е. ячейка с константой) будет вам напоминанием, что следует установить абсолютную адресацию (набором символа $ с клавиатуры)
5. Произведите расчеты во всех строках таблицы. Формулы для расчета:.
Выпуск продукции = Количество выпущенных изделий * Отпускная цена одного изделий
в ячейку С7 введите формулу = С5 * $Е$2 (ячейка Е2 задана в виде абсолютной адресации); Себестоимость выпускаемой продукции = Количество выпущенных изделий * Себестоимость одного изделия, в ячейку С8 введите формулу = С5*С6;
Прибыль от реализации продукции = Выпуск продукции - Себестоимость выпускаемой продукции, в ячейку С9 введите формулу = С7 - С8;
Рентабельность продукции = Прибыль от реализации продукции/Себестоимость выпускаемой продукции, в ячейку С10 введите формулу = С9/С8.
На строку расчета рентабельности продукции наложите Процентный формат чисел. Остальные расчеты производите в Денежном формате.
Формулы из колонки «С» скопируйте автокопированием (за маркер автозаполнения) вправо по строке в колонки «D» и «Е».
6. Выполните сохранение файла
ЗАДАНИЕ №2
Создать таблицу расчета дохода сотрудников организации. Константы вводить в расчетные формулы в виде абсолютной адресации.
Исходные данные представлены в Таблице 2
Таблица2
- На очередном свободном листе электронной книги «Расчеты» создайте таблицу по заданию.
- Введите значения констант и исходные данные. Форматы данных (денежный или процентный) задайте по образцу задания.
3. Произведите расчеты по формулам, применяя к константам абсолютную адресацию.
Формулы для расчетов:
Подоходный налог=(Оклад -Необлагаемый налогом доход)* % подоходного налога, в ячейку D10 введите формулу = (С10-$С$3)*$С$4;
Отчисления в благотворительный фонд = Оклад* % отчисления в благотворительный фонд, в ячейку Е10 введите формулу = С10*$С$5;
Всего удержано = Подоходный налог - Отчисления в благотворительный фонд, в ячейку F10 введите формулу = D10 + E10; К выдаче = Оклад - Всего удержано, в ячейку G10 введите формулу = C10-F10.
4. Постройте объемную гистограмму по данным столбца «К выдаче», проведите форматирование диаграммы.
ЗАДАНИЕ №3
Создать таблицу, отформатировать и заполнить ее данными: