Урок по информатике на тему: «Использование массивов для решения экономических задач в 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.

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

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

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

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

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

2. Актуализация знаний - 5 мин.

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

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

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

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

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

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

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

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

Вопросы для беседы:

Предполагаемый ответ

Что называется массивом?

Это упорядоченное множество фиксированного количества переменных одного типа.

Какие бывают массивы?

Двумерные и одномерные.

Как обратиться к элементам массива?

По индексу. У одномерного массива 1 индекс, который является порядковым номером элемента массива. У двумерного массива два индекса: первый номер строки, второй - номер столбца элемента.

Примеры записи массивов:

Одномерный массив В_i:

в₁ в₂ в₃ в₄ в₅

Двумерный А_ij

а₁₁

а₁₂

а₁₃

а₁₄

а₁₅

а₂₁

а₂₂

а₂₃

а₂₄

а₂₅

а₃₁

а₃₂

а₃₃

а₃₄

а₃₅

Какие действия мы выполняли с массивами?

Находили сумму всех элементов, Мах и Min значения, сортировали в возрастающем и убывающем порядке, выполняли выборку элементов, удовлетворяющих определённым условиям.

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

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

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

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

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. Снять у всех надбавки за кабинет. Записать в таблицу изменённые итоговые суммы.

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

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

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

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

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

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

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

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

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

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

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

7