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

Практическая работа №4.

Проектное задание.

Цель работы.

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

Порядок выполнения практикума.

1.      Выполните проектную подготовку в рабочей тетради:
- проанализируйте задание;
- создайте структуру таблицы (названия столбцов, отдельно хранящиеся данные и т д);
- изучите краткие теоретические сведения;
- вспомните как создаются диаграммы;
- вспомните синтаксис функции ЕСЛИ, как применяется функция И при записи условий;
- запишите формулы для вычисления значений ячеек в столбцах таблицы.

2.      Узнайте у преподавателя, кто из студентов группы выполняет такой же вариант.

3.      Обсудите совместно решение задачи.

4.      Выполните практикум на компьютере, работая в паре.

5.      Результатом выполнения практикума является:
- распечатка выполненного задания на принтере;
- все формулы, которые использовались в работе, записанные в тетради.

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

Функция     =СУММЕСЛИ(диапазон; критерий; [диапазон_суммирования])

Функция СУММЕСЛИ имеет аргументы, указанные ниже.

Диапазон.  Обязательный аргумент. Диапазон ячеек, оцениваемых по критериям. Ячейки в каждом диапазоне должны содержать числа, имена, массивы или ссылки на числа. Пустые ячейки и ячейки, содержащие текстовые значения, пропускаются.

Критерий.  Обязательный аргумент. Критерий в форме числа, выражения, ссылки на ячейку, текста или функции, определяющий, какие ячейки необходимо просуммировать. Например, критерий можно выразить как 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%.

Требования к решению:

·         Все промежуточные расчеты вести в у.е., итоговые - в рублях.

·         Изменение базовой стоимости путевки, курса у.е., и величины скидок автоматически ведет к изменению стоимости заказа.

·         Отобразить в таблице сведения:

ü ФИО сотрудника;

ü категория путевки (взрослая, детская, семейная);

ü длительность заезда;

ü скидка в процентах с учетом количества дней заезда;

ü стоимость путевки в процентах с учетом категории;

ü стоимость путевки;

ü стоимость путевки со скидкой;

ü стоимость путевки в рублях.

·         Вычислить:

ü стоимость заказа для профсоюзной организации с учетом скидки;

ü стоимость заказа по категориям.

·         Построить круговую диаграмму для иллюстрации суммы заказов по различным категориям путевок.

Рекомендации:

·         хранить величину базовой стоимости путевки и курс у.е. в отдельной ячейке;

·         для определения скидки завести справочник, где вход – количество дней заезда, выход - величина скидки.