Урок по информатике на тему "Использование функций для решения экономических задач в MS Excel" (10-11 класс)

Урок по информатике на тему:  «Использование массивов для решения экономических задач в MS Excel на примере создания Тарификации для учителей».

Дата проведения:           06.02.2013 г.

Класс:                                  11

Тип урока:                          Урок - практика

Цели урока:

Образовательная: обучить приёмам преобразования и обработки данных в электронных таблицах. Получить навыки работы с таблицами в MS Excel. Дать необходимые знания по использованию функции ИНДЕКС категории Ссылки и массивы при составлении Тарификационной ведомости. Научить учащихся использовать специальные функции для организации  и обработки массивов в электронных таблицах.

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

Развивающая: Развитие коммуникативных умений учащихся “Ученик - Компьютер” посредством электронных таблиц. Развитие умения логически рассуждать. Развитие аналитическо-синтезирующего мышления, умения проводить аналогии, делать выводы и обобщать.

Методы обучения: беседа, объяснение учителя, практическая работа на компьютере.

Оборудование: компьютеры семейства IBM PC/AT. процессора Intel Celeron 1,5 GHz; RAM – 256 Mb; HDD 80 GB; Операционная система Windows . Пакет MS Office.

Раздаточный материал: «Формулы и последовательность выполнения расчётов».

Межпредметные связи: экономика, математика.

Тип урока: урок-практика.

Структура урока.

I.                          Организационный момент – 1-2мин.

II.                      Актуализация знаний – 5 мин.

III.                   Изучение нового материала – 25-30 мин.

IV.                   Подведение итогов: -3-5 мин.

V.                     Домашнее задание – 1-2 мин.

I.             Организационный момент:

-              Проверить готовность учащихся к уроку (Наличие дневников и тетрадей).

-              Проверить списочный состав. Объявление темы: «Использование массива при решении задач в MS Excel» и цели урока: «Создать тарификационную ведомость для педагогических работников», где автоматически будет производится расчёт заработной платы при изменении категории педагогического работника, нагрузки и процента надбавок.

II.          Актуализация знаний.

Фронтальная беседа.

Вопросы для беседы:	Предполагаемый ответ
Что называется массивом?	Это упорядоченное множество фиксированного количества переменных одного типа.
Какие бывают массивы?	Двумерные и одномерные.
Как обратиться к элементам массива?	По индексу. У одномерного массива 1 индекс, который является порядковым номером элемента массива. У двумерного массива два индекса: первый номер строки, второй – номер столбца элемента.
Примеры записи массивов:
Одномерный массив Вi:	в1 в2 в3 в4 в5
Двумерный Аij	а11 а12 а13 а14 а15 а21 а22 а23 а24 а25 а31 а32 а33 а34 а35
Какие действия мы выполняли с массивами?	Находили сумму всех элементов, Мах и Min значения, сортировали в возрастающем и убывающем порядке, выполняли выборку элементов, удовлетворяющих определённым условиям.

Подведение к новой теме.

Рассмотрим, как можно использовать массивы при решении задач различных областей практической деятельности, например, в экономике. Для этого выполним практическую работу: «Составление тарификационной ведомости с автоматическим перерасчётом заработной платы в условиях  изменения категории учителя, нагрузки, доплат в % и минимальной заработной платы» (см. Приложение 1 «Тарификационная ведомость»). Здесь будем использовать метод выборки элемента массива по его индексу.

III.       Изучение нового материала.

Для расчёта предварительной оплаты труда учителя необходимо знать, что:

1.      Ставка – это нагрузка учителя в месяц, которая составляет 72 часа.

2.      Квалификационная категория учителя бывает: высшая, первая, вторая, третья, нулевая.

3.      Каждой категории соответствует свой разряд:

Категория	Разряд
нулевая	10
третья	11
вторая	12
первая	13
высшая	14

4.      У каждой категории есть свой коэффициент для расчета оплаты за ставку (72 часа в месяц), например:

Категория	Разряд	Коэффициент
нулевая	10	2,06
третья	11	2,8
вторая	12	3
первая	13	3,7
высшая	14	4,2

5.      Оплата за ставку вычисляется по формуле:        

Минимальная заработная плата коэффициент.

ВНИМАНИЕ! Все указанные величины вымышленные!

Объяснение нового материала:

В любом диапазоне ячеек текущего листа введём произвольные числа – это и будет массив. Покажем порядковые номера чисел в рядом стоящем столбце.

1	8,2
2	5,09
3	12
4	0,88
5	66
6	43
7	2,5

В столбце А введем в произвольном порядке любые номера от 1 до 7, можно повторяющиеся. Теперь осуществим выборку элементов из массива, согласно индексам, расположенным в столбце А. Для этого выбираем ячейку В1, куда введём формулу с помощью мастера функций;

F(x)—Ссылки и массивы—ИНДЕКС—Массив; №строки; №столбца— откроется окно, через которое введём диапазон массива и адрес индекса. В полученной формуле фиксируем адреса диапазона (абсолютные адреса начала и окончания) массива и копируем формулу вниз до конца номеров. В столбце В появятся числа, соответствующие порядковому номеру, указанному в столбце А. Если изменять значения в заданном массиве, то автоматически изменятся соответствующие значения в столбце В,

Переходим к созданию тарификационной ведомости.

Загрузите из файла Тарификация.xls подготовленную на предыдущем уроке электронную таблицу с заполненными полями (см. Приложение 2 «Заготовка таблицы»).

Дано:

1.      Фамилия И.О. учителя;

2.      Предмет;

3.      Категория учителя в виде разряда (10, 11, 12, 13, 14);

4.      Нагрузка учителя за месяц;

5.      Доплаты в % за проверку тетрадей и (или) за классное руководство и (или) за кабинет и (или) за вредность;

6.      Таблица коэффициентов для вычисления оплаты за ставку;

7.      Минимальная заработная плата -  3250руб.

Найти:

1.      Коэффициент в соответствии с категорией;

2.      Оплату за ставку – оплата 72 часов в месяц;

3.      Оплату за нагрузку;

4.      Доплаты в рублях;

5.      Оплату по тарифу (заработную плату).

Ставка учителя составляет 72 часа в месяц, т.е. 18 часов в неделю. Оплата за ставку зависит от категории учителя. Существует 5 категорий: высшая, первая, вторая, третья и нулевая. Каждой категории соответствует свой разряд от 14 до 10 и  свой коэффициент расчёта оплаты труда, которые отражены в таблице.

Категория	Разряд	Коэффициент
не используется	1	0
не используется	2	0
не используется	3	0
не используется	4	0
не используется	5	0
не используется	6	0
не используется	7	0
не используется	8	0
не используется	9	0
нулевая	10	2,06
третья	11	2,8
вторая	12	3
первая	13	3,7
высшая	14	4,2

Оплата за ставку равна минимальной заработной плате умноженной на соответствующий коэффициент.

Для автоматизации расчёта организуем массив коэффициентов, в котором индексами будут выступать разряды. MS Excel допускает использование массивов, начиная с индекса 1. Поэтому дополним наш массив нулями до первого разряда (индекса).

Алгоритм расчёта:

1.      В ячейку В20 поместим Минимальный размер заработной платы на текущий день, например, 3250.

2.      Организуем массив коэффициентов в любом свободном диапазоне ячеек листа, например, в диапазоне Н20:Н33.

	Н
20	0	1
21	0	2
22	0	3
23	0	4
24	0	5
25	0	6
26	0	7
27	0	8
28	0	9
29	2,06	10
30	2,8	11
31	3	12
32	3,7	13
33	4,2	14

3.      Выбираем первую пустую ячейку столбца D (Коэффициент по разряду).

4.      Вызываем мастер функций, т.е.  в строке формул нажимаем на кнопку f(x), при этом откроется диалоговое окно «Мастер функций - шаг 1 из 2».

5.      Выбираем функцию ИНДЕКС категории Ссылки и массивы и нажимаем на ОК. Из появившегося списка выбираем массив; номер строки; номер столбца и ОК. Переходим на второй шаг, где в текстовом поле Массив указываем диапазон массива коэффициентов Н20:Н33, для этого достаточно выделить диапазон Н20:Н33 на рабочем листе. Переходи в  текстовое поле Номер_строки. Здесь указываем  адрес той ячейки, которая содержит порядковый номер элемента в массиве (индекс), а это адрес D3.

6.      Текстовое поле Номер_столбца не указываем, т.к. наш массив одномерный и второго индекса у него не будет, нажимаем на ОК.

7.      В выбранной ячейке установится коэффициент, соответствующий категории учителя. В строке формул отобразиться формула =ИНДЕКС(H20:H33;D3);

8.      Перед тем как копировать формулу,  необходимо закрепить на своём месте массив коэффициентов, для этого заменяем в формуле относительную адресацию массива на абсолютную: в строке формул выбираем адрес H20 и нажимаем на кнопку F4, то же самое делаем с адресом H33. Формула примет вид  =ИНДЕКС($H$20:$H$33;D3). Закрепим формулу клавишей Enter.

9.      Вернёмся на ячейку D3 и скопируем формулу для всех учителей, воспользовавшись маркером автозаполнения.

10.  Вычисляем Оплату за ставку (См. приложение 3):

Оплата за ставку=Минимальная заработная плата×Коэффициент. Для этого выбираем первую пустую ячейку столбца «Оплата за ставку», т.е. F3 и  вводим формулу   =B20*D3. Перед копированием формулы закрепляем в ней адрес ячейки с минимальной заработной платой. В строке формул выбираем адрес В20 и нажимаем на F4. Получаем формулу  =$B$20*D3.

11.  Скопируем формулу для всех учителей.

12.  Вычисляем Оплату за нагрузку:

Оплату за нагрузку=Оплата за ставку:72×Нагрузка. Выбираем ячейку G2 и вводим формулу =F3/72*G3.

13.  Копируем формулу для всех учителей.

14.  Все процентные надбавки переводим в рубли по правилу: Надбавки за классное руководство, за проверку тетрадей и за кабинет рассчитываются от оплаты за ставку, оплата за вредность рассчитываются от оплаты за нагрузку:

-в ячейку M3 вводим формулу =F3/100*I3 и копируем для всех учителей;

-в ячейку N3 вводим формулу =F3/100*J3 и копируем для всех учителей;

-в ячейку O3 вводим формулу =F3/100*K3 копируем для всех учителей;

-в ячейку P3 вводим формулу =H3/100*L3 и копируем для всех учителей.

15.  Для заполнения столбца Итого доплат:

Итого доплат=Доплата за кл.рук. в руб. + Доплата за проверку тетрадей в руб. + Доплата за кабинет в руб. + Доплата за вредность в руб

в ячейку Q3 вводим формулу: =СУММ(M3:P3).

16.  Скопируем формулу для всех учителей.

17.  Заполняем столбец Итого зар. Плата:

Итого зар. Плата = Оплата за нагрузку + Итого доплат, для чего в ячейку R2 вводим формулу: Формула =H3+Q3.

18.  Скопируем формулу для всех учителей.

19.  В следующей свободной строке выполним расчёт итога по столбцам:

-  Оплата за часы (Н);

-  Доплата за кл. рук. в рублях (М);

-  Доплата за проверку тетрадей в рублях (N);

-  Доплата за кабинет в рублях (O).

-  Доплата за вредность в рублях (P);

20.  Оформляем таблицу по своему вкусу: обрамление, заливка, цвет шрифта.

Компьютерный эксперимент.

Заполнение табличек (см. Приложение 4).

1.      Записать в тетрадь итоговые суммы.

2.      Изменить разряд у учителя Алёшина Т.В. с 12 на 13 при этом обратите внимание на автоматическое изменение всех расчётных величин у этого учителя.  Записать в тетрадь изменённые итоговые суммы.

3.      У учителя Новикова М.О. изменить нагрузку с 60 часов до 88. Записать в таблицу изменённые итоговые суммы.

4.      Снять у всех надбавки за кабинет. Записать в таблицу изменённые итоговые суммы.

IV.       Подведение итогов.

Сегодня мы научились использовать функции электронных таблиц в конкретных ситуациях, а именно для автоматизации технологического процесса предварительной расчёта заработной платы учителей.

Давайте подведём итоги.

В чём вы видите преимущества использования электронной таблицы при вычислении заработной платы учителей?

Предполагаемые выводы учащихся:

1.      Составив один раз расчётную таблицу, её можно использовать много раз, изменяя входные данные.

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

3.      Автоматизированный расчёт облегчает труд работника и ЗНАЧИТЕЛЬНО убыстряет процесс расчёта.

Сохраните выполненные работы в своих папках. Я проверю и объявлю оценки на следующем занятии.

V.          Домашнее задание.

Отработать навыки составления таблицы по заданному образцу (см. Приложение 5). Выполненную работу принести на любом электронном носителе информации.