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

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

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

Учебная цель: Ознакомиться с основными методами решения расчетных задач в Excel. Освоить простейшие операции с использованием вставки расчетных формул (функций).

Образовательные результаты, заявленные во ФГОС третьего поколения:

Студент должен

Уметь:

- пользоваться мастером функций,

- копировать формулы,

- форматировать таблицы.

Знать:

- категории встроенных функций,

- приоритеты операций,

- относительные, абсолютные, смешанные ссылки в формулах.

Краткие теоретические и учебно-методические материалы по теме

Задачи Excel удобно использовать для решения различных математических, физических, экономических и других задач. Достаточно правильно расположить информацию на рабочем листе, то есть подготовить начальные данные и определиться с местом расположения результата, а также ввести необходимые формулы расчетов.

В MS Excel имеется большой выбор встроенных функций для обработки как числовых значений, так и данных другого типа, содержащейся в ячейках. Просмотреть имеющиеся категории функций и конкретное назначение отдельной функции можно с помощью мастера функций (называется командой Формулы/Вставить функцию).

Формулы в электронной таблице. В самом простом варианте электронная таблица может быть использована как калькулятор. Любая формула, которая вводится в ячейку, начинается со знака=. Все символы, введенные после этого знака (числа, адреса ячеек, арифметические знаки и др.) будут восприниматься как элементы формулы.

Приоритет операций такой:

·       вычисляются выражения в скобках,

·       производится возведение в степень,

·       выполняется умножение и деление,

·       выполняется сложение и вычитание,

В качестве операторов в формулах используются следующие символы: + = - * / & < > ^

Задачи практической  работы:

1. Выполнить практическую  работу;

2.Оформить отчёт по практической  работе;

Обеспеченность занятия (средства обучения)

Основные источники: 

1.            Учебно-методическая литература:

·              Михеева Е.В. Практикум по информации: учеб. пособие. – М., 2004.

·              Михеева Е.В., Титова О.И. Информатика: учебник. – М., 2005.

·              Семакин И.Г., Хеннер Е.К. Информатика. Задачник-практикум 8–11 кл. (в 2 томах). – М., 2002.

·              Семакин И.Г., Хеннер Е.К. Информатика. Учебник 10-11 кл. – М., 2007.

·              Уваров В.М., Силакова Л.А., Красникова Н.Е. Практикум по основам информатики и вычислительной техники: учеб. пособие. – М., 2005.

·              Угринович Н.Д. и др. Практикум по информатике и информационным технологиям 10–11 кл. – М., 2002.

2.     Лист писчей бумаги формат А4 (210x297мм) с нанесенной ограничительной рамкой, отстоящей от левого края листа на 20мм и от остальных на 5мм со штампом основной надписи 15х185 мм (ГОСТ 2.104-2006).

3.     Ручка с чернилами черного цвета.

4.     Карандаш простой.

5.     Персональный компьютер, MS Excel

Вопросы для закрепления теоретического материала
к практической работе:

1.    Что такое формула?

2.    Как вставить функцию?

3.    Относительные и абсолютные ссылки в формулах.

4.    Смешанные ссылки.

Инструкция по выполнению практической работы

Внимательно изучить раздаточный и краткий теоретический материал.

Изучить порядок выполнения задания.

Методика анализа результатов, полученных в ходе практической  работы

Контролем  является защита отчета по  практическому занятию.

Задания практической работы:

Задание 1. Введите в любую ячейку электронной таблицы формулы и сравните полученный результат с ответом

Задание 2.

Создать таблицу умножения чисел от 1 до 9 (9 строк, 9 столбцов). В ячейке, соответствующей произведению 1*1, должна быть записана формула, которая затем должна быть скопирована во все остальные 80 ячеек (рис.2)

Формула для ячейки B3:  =$A3*B$2

рис.2

Задание 3.  Сформировать на рабочем листе ведомость "Расчет заработной платы работников научно-проектного отдела "Альфа"(рис.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