Методические указания и задания к практическим работам по теме Технология обработки числовых данных для студентов 1 курса СПО. Практическая работа 4.

Практическая работа №4.
Проектное задание.
Цель работы.
Применить умения и навыки, приобретенные при работе с табличным процессором Excel.

Порядок выполнения практикума.
Выполните проектную подготовку в рабочей тетради:- проанализируйте задание;- создайте структуру таблицы (названия столбцов, отдельно хранящиеся данные и т д);- изучите краткие теоретические сведения;- вспомните как создаются диаграммы;- вспомните синтаксис функции ЕСЛИ, как применяется функция И при записи условий;- запишите формулы для вычисления значений ячеек в столбцах таблицы.
Узнайте у преподавателя, кто из студентов группы выполняет такой же вариант.
Обсудите совместно решение задачи.
Выполните практикум на компьютере, работая в паре.
Результатом выполнения практикума является:- распечатка выполненного задания на принтере;- все формулы, которые использовались в работе, записанные в тетради.
Краткие теоретические сведения.
Функция =СУММЕСЛИ(диапазон; критерий; [диапазон_суммирования])
Функция СУММЕСЛИ имеет аргументы, указанные ниже.
Диапазон. Обязательный аргумент. Диапазон ячеек, оцениваемых по критериям. Ячейки в каждом диапазоне должны содержать числа, имена, массивы или ссылки на числа. Пустые ячейки и ячейки, содержащие текстовые значения, пропускаются.
Критерий. Обязательный аргумент. Критерий в форме числа, выражения, ссылки на ячейку, текста или функции, определяющий, какие ячейки необходимо просуммировать. Например, критерий можно выразить как 32, ">32", B5, "32", "яблоки" или СЕГОДНЯ().
ВАЖНО. Все текстовые критерии и критерии с логическими и математическими знаками необходимо заключать в двойные кавычки ("). Если критерием является число, использовать кавычки не требуется.
Диапазон_суммирования. Необязательный аргумент. Ячейки, значения из которых суммируются, если они отличаются от ячеек, указанных в качестве диапазона. Если аргумент диапазон_суммирования опущен, Microsoft Excel суммирует ячейки, указанные в аргументе диапазон (те же ячейки, к которым применяется критерий).
Вариант №1
Составить таблицу, которая позволяет автоматизировано начислять стипендию студентам группы. Считать, что начисление стипендии происходит в зависимости от оценок, полученных на 4 экзаменах зимней сессии следующим образом. Оценки на экзамене - 5, 4, 3, 2. Базовая величина стипендии — 10 у.е. Базовую стипендию получают все сдавшие сессию - (нет "двоек"). Сдавшие без “троек” получают 1,5 базовых стипендии. Сдавшие все экзамены на “пятерку” получают 2 базовые стипендии. Не сдавшие (получившие хотя бы одну “двойку”) стипендии не получают. Курс у.е. реальный и может меняться. Все расчеты вести в рублях.
Требования к решению:
Изменение курса у.е. и величины базовой стипендии автоматически ведет к изменению величины стипендии.
Изменение оценки за экзамен автоматически изменяет размер стипендии.
Обеспечить подведение итогов сессии:
стипендиальный фонд группы;
отдельно суммы для всех трех "категорий" студентов, получающих стипендию (отличников, хорошистов, сдавших);
построить диаграмму для иллюстрации доли стипендий различных "категорий".
Рекомендации:
Хранить величину базовой стипендии в отдельной ячейке.
Каждому студенту присвоить "категорию".
"Категория" вычисляется как минимальная оценка среди им полученных за сессию, для чего воспользоваться встроенной функцией МИН().
Вариант №2
Составить таблицу - ведомость на приобретение персональных компьютеров (ПК) для некоторого холдинга. Холдинг − объединение нескольких фирм (12-14). Будем полагать, что он может включать фирмы двух видов: российские и совместные. Считать, что вычисление стоимости ПК происходит следующим образом: базовая стоимость компьютера —1000 USD и может меняться. Курс USD реальный и может меняться. Все расчеты вести в рублях. Российские предприятия платят базовую стоимость плюс налог на добавленную стоимость (НДС) 20%. Совместные предприятия НДС не платят. Каждая фирма покупает несколько компьютеров (от 1 до 100 шт.). Каждая фирма имеет право на скидку в зависимости от количества покупаемых компьютеров. При покупке:
до 10 компьютеров - нет скидки;
от 10 до 25 - скидка 5%;
от 25 до 75 - скидка 10%;
свыше 75 - скидка 15%.
Требования к решению:
Каждая строка обязательно содержит следующую информацию:
название фирмы;
вид фирмы;
количество приобретенных компьютеров;
стоимость компьютеров без скидки и при необходимости с НДС;
скидка;
стоимость со скидкой (к оплате).
Изменение Базовой стоимости и курса USD автоматически ведет к изменению стоимости.
Изменение курса валют автоматически ведет к изменению стоимости.
Вычислить:
общую стоимость (к оплате) по холдингу;
отдельно суммы (к оплате) для двух категорий фирм в зависимости от вида фирмы.
построить круговую диаграмму для иллюстрации доли суммарной стоимости (к оплате) компьютеров для каждого вида фирм.
Рекомендации:
Хранить базовую стоимость и курс USD в отдельных ячейках;
Вариант №3.
Составить таблицу - ведомость на получение денежного пособия пенсионерам из 2-го дома Старсобеса. Считать, что начисление денежного пособия происходит в следующем порядке. Каждый пенсионер имеет базовую пенсию от 100 до 200 у.е. и в зависимости от стажа получает надбавку:
при стаже до 20 лет нет надбавки;
от 20 до 30 лет - 25%;
от 30 до 40 лет - 50%;
свыше 40 - 75%.
Каждый пенсионер платит взнос в страховой фонд. Величина взноса зависит от МРОТ (10 у.е.) и возраста. При возрасте до 65 лет взнос равен двум МРОТ, 65 лет и более - трем МРОТ. На руки пенсионер получает базовую пенсию плюс надбавку минус взнос в страховой фонд.
Требования к решению:
Каждая строка обязательно содержит следующую информацию:
ФИО;
возраст;
стаж;
надбавку;
взнос в страховой фонд;
сумму на руки.
Изменение базовой пенсии и МРОТ ведет к изменению всех величин.
Вычислить:
сумму на руки по собесу;
отдельно сумму на руки для лиц в возрасте до 65 лет и свыше;
Построить круговую диаграмму для иллюстрации доли сумма на руки по группам
Рекомендации:
хранить МРОТ в отдельной ячейке;
Вариант №4.
Составить таблицу, которая позволяет автоматизировано составить ведомость на выплату премиальных спортсменам олимпийцам. В Центре олимпийской подготовки (ЦОП) готовят спортсменов по трем видам: штанга, бокс и дзюдо. Требуется составить таблицу для расчета денежного вознаграждения по итогам соревнований.
Начисление премиальных происходит следующим образом:
каждый спортсмен участвует в одном виде соревнований;
премиальные выплачиваются спортсмену как за каждую завоеванную медаль (первые три места), так и за принесенные очки в общекомандный зачет (за места с 1 по 4);
за первое место (золотую медаль) начисляют 1000 USD и 8 очков в общий зачет;
за второе место (серебряную медаль) - 700 USD и 5 очков,
за третье место (бронзовую медаль) - 500 USD и 3 очка;
за четвертое место - 1 очко.
Требования к решению:
Каждая строка таблицы обязательно содержит следующую информацию:
фамилию спортсмена;
специализация;
завоеванное место;
количество завоеванных очков;
заработанные спортсменом суммы.
Изменение стоимости медали в очках и условных единицах, а также курса USD автоматически ведет к изменению суммы вознаграждения.
Курс USD реальный и может меняться.
Окончательный результат расчетов — в рублях.
Вычислить:
общую сумму очков и денежного вознаграждения по Центру;
отдельно суммы вознаграждения для каждого из видов.
Построить круговую диаграмму для иллюстрации доли суммы вознаграждения для каждой специализации.
Рекомендации:
хранить курс USD в отдельной ячейке;
для начисления вознаграждения завести справочник, в котором столбцы - занятые места, денежное вознаграждение и количество очков.
Вариант №5.
Составить таблицу, которая позволяет автоматизировано составить ведомость на начисление премии рабочим. Две бригады рабочих изготавливают детали трех видов (А, В, С). Стоимость одной детали вида А – 10 USD, вида В - 20 USD, вида С - 15 USD. Каждый рабочий производит детали одного вида. Общее количество работников 12-14 чел. Считать, что начисление премии происходит по следующему принципу: премия начисляется, если изготовлено деталей на сумму больше 2000 USD в размере 10% от этой суммы для рабочих первой бригады и 12% для рабочих второй бригады.
Требования к решению:
Каждая строка таблицы обязательно содержит следующую информацию:
ФИО;
название (номер) бригады;
вид детали;
количество деталей, изготовленных рабочим;
стоимость деталей;
размер премии в USD;
размер премии в рублях;
изменение стоимости каждой детали, изменение курса доллара и перевод работника в другую бригаду автоматически ведет к изменению всех расчетов;
обеспечить подведение итогов: подсчитать общую сумму премий и сумму премий по каждой бригаде;
построить круговую диаграмму для иллюстрации доли премий для первой и второй бригады.
Рекомендации:
хранить курс доллара в отдельной ячейке;
в отдельных ячейках хранить размер премиальных для каждой бригады;
для вычисления стоимости изготовленных деталей завести справочник, в котором отразить вид детали и ее стоимость.
Вариант №6.
Составить таблицу, которая позволяет вычислить стоимость закупленного оборудования трех видов для различных фирм. Несколько фирм (12-14), входящих в объединение, закупают оборудование трех видов. Фирмы могут быть двух типов - совместные и российские. Каждая фирма закупает оборудование одного вида. При закупке оборудования на определенную сумму фирма получает скидку. Стоимость единицы закупленного оборудования 1-го типа - 1000 USD, 2-го − 500 USD, 3-го – 250 USD. При покупке оборудования на сумму свыше 10000 USD для российских фирм действует скидка в размере 10% от общей стоимости, а для совместных −5%.
Требования к решению:
Каждая строка таблицы содержит следующую информацию:
название фирмы;
тип фирмы;
вид закупленного оборудования;
количество единиц оборудования;
стоимость;
скидка;
стоимость с учетом скидки.
Подсчеты вести в рублях.
Изменение стоимости единицы оборудования, курса доллара и типа фирмы автоматически влечет за собой изменение всех вычисляемых величин.
Обеспечить подсчет суммарной стоимости закупленного оборудования с учетом скидки для всех фирм и отдельно для совместных и российских фирм.
Построить круговую диаграмму, отражающую долю от общей стоимости совместных и российских фирм.
Рекомендации:
хранить курс доллара в отдельной ячейке;
в отдельных ячейках хранить размер скидки для каждого типа фирмы;
для расчета стоимости закупленного оборудования завести справочник, в котором отразить вид оборудования и стоимость за единицу.
Вариант №7.
Составить таблицу, которая позволяет профсоюзной организации автоматизировано оформлять заказ на путевки в туристической фирме. Профсоюзная организация предприятия заключает договора на приобретение путевок для своих сотрудников. Количество дней пребывания в пансионатах и домах отдыха определяется сотрудником самостоятельно. Стоимость путевки определяется как произведение базовой стоимости 1 дня на длительность заезда с учетом категории и скидки. Базовая стоимость путевки - 10 у.е./день. Сотрудникам предлагаются путевки трех категорий:
для взрослых - 100% базовой стоимости;
для детей - 60% базовой стоимости;
семейная (2 чел) - 175% базовой стоимости.
Величина скидки на путевку зависит от длительности заезда:
менее 6 дней - скидки нет,
от 6 до 10 дней - скидка 5%,
от 11 до 15 дней - скидка - 10%,
свыше 15 дней - скидка 20%.
Требования к решению:
Все промежуточные расчеты вести в у.е., итоговые - в рублях.Изменение базовой стоимости путевки, курса у.е., и величины скидок автоматически ведет к изменению стоимости заказа.
Отобразить в таблице сведения:
ФИО сотрудника;
категория путевки (взрослая, детская, семейная);
длительность заезда;
скидка в процентах с учетом количества дней заезда;
стоимость путевки в процентах с учетом категории;
стоимость путевки;
стоимость путевки со скидкой;
стоимость путевки в рублях.
Вычислить:
стоимость заказа для профсоюзной организации с учетом скидки;
стоимость заказа по категориям.
Построить круговую диаграмму для иллюстрации суммы заказов по различным категориям путевок.
Рекомендации:
хранить величину базовой стоимости путевки и курс у.е. в отдельной ячейке;
для определения скидки завести справочник, где вход – количество дней заезда, выход - величина скидки.