Решение задач методами численной математики в MS Excel

Лабораторная работа №18

«Решение задач методами численной математики в MS Excel»

Цели:

1.      Закрепить навыки работы с электронными таблицами и применение их для решения задач.

2.      Развивать  практические и исследовательские навыки по решению задач с помощью электронных таблиц.

Выполнить задания:

2.      Сформировать на рабочем листе ведомость "Расчет заработной платы    работников научно-проектного отдела "Альфа"(рис.1)

Рис. №1

Рекомендации по созданию ведомости

·         В ячейку А2 поместить название ведомости – Расчет заработной платы работников научно-проектного отдела «Альфа»  отцентрировать по левому краю (например, командой Формат/Ячейки/вкладка Выравнивание либо соответствующей кнопкой        По правому краю на панели инструментов. Объединить в строке 2 столбцы от А до K.

·         В ячейки А3:К3 ввести названия полей ведомости: №п/п, Фамилия И.О., Должность, Тарифная ставка, Стаж, k, Надбавка за стаж, Итого, Процент налога, Удержать, Выплата.

·          К шапке ведомости – к каждому столбцу – создать скрытые примечания (рис.2). Примечания создаются командой Вставка/Примечание (также можно использовать панель инструментов Рецензирование, которая добавляется командой Вид/Панели инструментов/Рецензирование

- № п/п – номер работника отдела;

- Фамилия И.О.-  заносятся все фамилии на момент заполнения ведомости;

- Должность – занимаемая должность на момент заполнения ведомости;

- Тарифная ставка – денежный эквивалент занимаемой должности;

- Стаж – вносится целое число отработанных лет на момент заполнения ведомости;

- k- коэффициент за стаж работы

- Надбавка за стаж – денежный эквивалент за стаж работы;

- Итого – начисление заработной платы с учетом тарифной ставки и стажа работы;

- Процент налога – определяет процент отчислений в бюджет;

- Удержать – денежный эквивалент отчислений в бюджет;

- Выплата – сумма, предназначенная к выдаче.

Рис.2

·         При расчетах к ведомости учитывать следующее:

- k, Надбавка за стаж, Итого, Процент налога, Удержать, Выплата – вычисляются с помощью соответствующих формул, с  использованием автозаполнения или копирования формулы.

- Коэффициент k присваивается из следующего расчета: 0,2 – отработано до 5 лет включительно, 0,2 – от 5 до 10 лет включительно, 0,25- от 10 до 15 лет включительно, 0,3 – свыше 15 лет. Формула для ячейки F4:

=ЕСЛИ(Е4<=5;0,1;ЕСЛИ(И(Е4>5;E4<=10);0,2;ЕСЛИ(И(Е4>10;Е4<=15);0,25;0,3)))

- Надбавка за стаж – денежный эквивалент за стаж работы. Формула для ячейки G4:

=D4*F4

Пользовательский формат числа для ячейки G4:

# ##0,00р.;

(вводится командой Формат/Ячейки/вкладка Число, из списка Числовые форматы выбрать Все форматы и поле Тип ввести указанный формат)

- Итого – тарифная ставка с учетом стажа. Формула для ячейки H4:

=D4+G4

Пользовательский формат числа для ячейки H4:

# ##0,00р.;

- Процент налога – учитывает, что: 2%- начисление (по Итого) составляет до 7000 р. включительно, 10%- более 7000р.  до 10 000 р. включительно, 20%- более 10 000 р. до      25 000 р. включительно, 35% - превышающие 25 000 р. Формула для ячейки I4:

=ЕСЛИ(Н4<=7000;0,02;ЕСЛИ(И(H4>7000;Н4<=10000);0,1;ЕСЛИ(И(Н4>10000;H4<=25000);0,2;0,35)))

Формат числа для ячейки I4 – Процентный

- Удержать – денежный эквивалент налогов. Формула для ячейки J4:

=H4*14

Пользовательский формат числа для ячейки J4:

# ##0,00р.;

- Выплата – сумма к выдаче: Итого без  Удержать

·         Требования к столбцу Стаж:

-  Создать пользовательский формат данных, учитывающий стаж работы: до 5 лет – данные представлены желтым цветом, от 5 до 10 – синим, от 10 до 15 – зеленым, свыше 15 -красным.

- Воспользоваться командой Формат/Ячейки и ввести пользовательский формат для ячейки E4:

[Красный]#  ##0;

               - В случае ввода отрицательного числа лет должно появляться соответствующее окно. Для проверки ввода чисел использовать команду Данные/Проверка/вкладка Сообщение об ошибке. (рис. 3,4)

Рис.3

Рис.4

·         Для поля Тарифная ставка – вывести постоянное сообщение: Тарифная ставка. Будьте внимательны при вводе тарифной ставки (рис.5), для получения которого использовать команду Данные/Проверка/вкладка Сообщение для ввода.

Рис.5

3.      Оформить отчет по лабораторной работе.

4.