2020-09-27
357
Наименование работы: Абсолютные и относительные ссылки, составление сложных условий
Цель работы: Научиться применять абсолютный вид адресации при решении задач. Формировать ОК-2, ОК-4, ОК-5, ОК-6, ПК-1.2, ПК-1.3, ПК-1.6, ПК-3.1, ПК-3.2
Литература:
1. Кунтаева Г.Х., Строева Л.Р. Пакеты прикладных программ. Учебное пособие, 2015
2. Фуфаев Э.В., Фуфаева Л.И. Пакеты прикладных программ, 2014.
Задание:
1. Рассчитайте сумму платежа, за израсходованную электроэнергию за год, используя абсолютные и относительные ссылки на ячейки при создании формул.
Оформите таблицу расчета платежей по образцу.
Проверь себя: Вы должны ввести всего две формулы для вычисления платежа, за израсходованную электроэнергию за год. Внизу таблицу подведите итоговую сумму за год.
2. Создайте электронную таблицу учета платы за квартиру согласно образцу:
- все заголовки выровнять по центру как по горизонтали, так и по вертикали, при определении формата ячейки примените опцию переноса слов;
- столбец № квартиры: 10, 11, 12 … - центральное выравнивание;
- столбец Фамилия: Иванов 1, Иванов2 и т.д.
- столбец Площадь: 60; 59,5; 58 и т.д. (каждая последующая на 0,5 кв.м. меньше предыдущей), выравнивание центральное;
- столбец Сумма: для каждой квартиры умножается значение из графы Тариф, формат денежный;
- столбец Дата оплаты: с 1 марта, каждая следующая квартира производила оплату на день позже предыдущей, формат Дата, полная форма;
- столбец Просрочка: если оплата была произведена до срока оплаты включительно, то автоматически ставится 0 (ноль), если позже срока, то должно подсчитываться количество дней просрочки (Дата оплаты – Срок оплаты). Данные выровняйте по центру;
- столбец Штраф: для каждой квартиры умножается значение из графы Пени за 1 день на значение из графы Просрочка. Формат денежный;
- столбец Итого: суммируются значения из граф Сумма и Штраф, формат денежный;
- в конце ведомости должна автоматически подсчитываться следующая статистика по всем квартирам: общая сумма графы Итого, Средняя площадь, Максимальная просрочка.
3. Создать таблицу, которая формирует ведомость на выплату зарплаты с прогрессивной шкалой подоходного налога. Пусть налог исчисляется по прогрессивной шкале следующим образом:
- с зарплаты, не превышающей 10000 руб., налог составляет 12%, а с части зарплаты, превышающей 10000 руб., взыскивается налог 20% от этой части.
Вначале определим исходные данные задачи: фамилии работников (текст) и размер зарплаты (число с двумя цифрами в дробной части).
Установим, что мы должны рассчитать в задаче величины подоходного налога и получаемой каждым работником суммы.
Для выполнения расчетов запустите Excel, опишите структуру таблицы и введите исходные данные следующим образом.
В ячейку СЗ поместите формулу расчета величины подоходного налога, вычисляемого по прогрессивной шкале. Для этого выделите ячейку СЗ и введите формулу =ЕСЛИ(ВЗ<=10000; ВЗ*0,12;10000*0,12+(ВЗ-10000)*0,2). В этой формуле проверяется условие В3<=10000. Если условие соблюдается, то налог вычисляется по формуле В3*0,12. Если условие ложно, то налог вычисляется по формуле 10000*0,12+(ВЗ-10000)*0,2 (12% от суммы зарплаты в 10000 руб. + 20% от суммы, превышающей 10000 руб.).
В ячейку D3 введите формулу =ВЗ-СЗ для определения суммы разности зарплаты и налога.
Скопируйте формулы из диапазона C3:D3 в диапазон C4:D6. В ячейку В7 введите формулы суммирования результата по столбцу В, для чего, выделив ячейки ВЗ:В6, щелкните кнопку «Автосумма» в панели инструментов Стандартная. Скопируйте формулу вычисления суммы столбца из В7 в C7:D7.
Оформите таблицу, выделив диапазон A2:D7 и выбрав команду Автоформат в меню Формат. В диалоговом окне Автоформат из списка форматов выберите вариант Финансовый 3 и щелкните кнопку «ОК». Измените формат отображения значений в ячейках B3:D7, для чего, выделив этот диапазон, выберите в меню Формат команду ячейки, затем в диалоговом окне Формат ячеек выберите Финансовый формат, в поле Число десятичных знаков задайте отображение двух цифр в дробной части, в поле Обозначение выберите р и щелкните кнопку «ОК» для применения заданного формата ячеек. Сохраните таблицу под именем Расчет зарплаты.
Порядок выполнения работы:
1. Получить допуск к работе;
2. Запустить Microsoft Excel и выполнить задания;
3. Составить отчет;
4. Ответить на контрольные вопросы.
Содержание отчета:
1. Наименование, цель занятия, задание;
2. Выполненное задание;
3. Ответы на контрольные вопросы.
Контрольные вопросы:
1. Перечислите основные виды адресации в электронных таблицах. Чем один вид адресации отличается от другого?
2. В каких случаях применяют абсолютные ссылки? Относительные?
3. Как вводится знак абсолютной адресации?
ПРИЛОЖЕНИЕ
Общий вид условной функции следующий:
ЕСЛИ (< условие >, < выражение 1 >, <выражение 2 >)
<условие> - это логическое выражение, которое может принимать значение ИСТИНА или ЛОЖЬ,
<выражение 1 >, < выражение 2 > могут быть числами, формулами или текстом.
Условная функция, записанная в ячейку таблицы, выполняется так: если < условие> истинно, то значение данной ячейки определит <выражение 1 >, в противном случае <выражение 2 >. Логические выражения строятся с помощью операций отношения >(меньше), <(больше), <=(меньше или равно), >= (больше или равно), = (равно), <>(неравно).
Если задание 2 сделано правильно, то должна получиться следующая таблица:
