К практическому применению электронной таблицы Excel

Разделы: Информатика

Тема:  «К вопросу практического применение табличного редактора Еxcel»

Цели: 

  • наработать умения и навыки работы в ЭТ. Уметь применять ЭТ для выполнения работ, связанных со статистическими данными;
  • унать МНК и уметь выполнять практические работы с использованием МНК.

Задачи:

  • Освоение   способов   построения   по   экспериментальным   данным регрессионной модели и тренда средствами ЭТ «Excel».
  • Освоение приемов прогнозирования количественных характеристик системы по регрессивной модели путем восстановления значений и экстраполяции.

Оборудование: 12 компьютеров, программное обеспечение MS PowerPoint, электронная таблица  MS Excel, раздаточные материалы.

Раздаточные материалы: индивидуально-ориентированный план (ИОП) на урок

Индивидуально-оринетированный план на   «___» ______ 2007 г.

На «3»

На «4»

На «5»

Выполнить работу с презентации в.
Заполнить слайд № 11
Задание № 2, 3 из практической работы

Задание № 4* из Практической работы
Задание № 5 из Практической работы

Разработайте собственную
задачу на данную тему

Примечание:учащиеся уже знают, что не выполнив работы на 3, они не имеют права делать работу на «4» и «5».

1. Лист самопроверки

Самопроверка:

Построение регрессионных моделей с помощью «Excel».

Проверьте, правильно ли  вы подобрали алгоритм работы?

  • По данным таблицы (она дана в презентации и последнее данное надо добавить с 20 слайда) построить точечную диаграмму.
  • Щелкнуть мышью по полю диаграммы правой клавишей.
  • Выполнить команду ДИАГРАММА  + ДОБАВИТЬ ЛИНИЮ ТРЕНДА.
  • В открывшемся диалоговом меню на закладке «ТИП» выбрать «Линейный
    тренд» ( или другой вид функции)
  • Перейти к закладке «ПАРАМЕТРЫ»; установить галочки на флажках
    «ПОКАЗАТЬ УРАВНЕНИЯ НА ДИАГРАММЕ» и «ПОМЕСТИТЬ НА
    ДИАГРАММУ ВЕЛИЧИНУ ДОСТОВЕРНОСТИ АППРОКСИМАЦИИ
    R2».

Диаграмма  готова.  Выполните три диаграммы,  как  вам  было  предложено  в Презентации.
Комментарии:
Таблица «EXCEL» дает возможность пользователю самому   задавать тип тренда, шесть основных функций вполне достаточно для создания продолжения  линии тренда за границы области данных приведенных в исходной таблице,   называется   экстраполяцией.   Для   получения   такого   рисунка   нужно добавить в описанный выше алгоритм еще один шаг: => на вкладке «ПАРАМЕТРЫ» в области «ПРОГНОЗ» в строке «ВПЕРЕД НА» установить 2 единицы.

2. Задания на оставшееся время.

Задания  на закрепление материала

Задание № 2. Дана таблица с прогнозами средней дневной температуры на последнюю неделю мая в различных городах европейской части России. Города упорядочены по алфавиту. Указана также географическая широта этих городов. Построить несколько вариантов регрессионных моделей (не менее трех), отражающих зависимость температуры от широты города. Выбрать наиболее подходящую функцию.

Прогноз температуры в городах России

Город

Широта, гр. с.ш.

Температура
Воронеж 51,5 16
Краснодар 45 24
Липецк 52,6 12
Новороссийск 44,8 25
Ростов на Дону 47,3 19
Рязань 54,5 11
Северодвинск 64,8 5
Череповец 59,4 7
Ярославль 57,7 10

Задание 3. По данным таблицы постройте регрессионную модель с помощью ЭТ, используя линейную, логарифмическую, экспоненциальную, квадратичную и логарифмическую функций. Определите параметры, выберите лучшую модель.

X 2 4 6 8 10 12 14 16 18 20 22 24 26 28
Y 44 32 35 40 30 27 21 25 20 23 18 19 20 16

Задание 4*. Рассчитайте по данным таблицы из задания 2, рассчитать прогноз средней температуры для следующих городов: Сочи - 43,5 гр.с.ш., Москва - 55,7 гр.с.ш., Санкт-Петербург - 60 гр.с.ш., Мурманск - 69 гр.с.ш., Минусинск - 53,8 гр.с.ш.
Примечание: Регрессионная модель может использоваться для прогноза, предсказания значения параметров в точках, не являющихся экспериментальными данными.

Задание 5*. Постройте регрессивную  модель по таблице  «Падение тела» по экспериментальным данным.

 Таблица 1 (продолжение) «Падение тела»

Н(м)

t (сек)

 H(м) t(сек)
6 1,1 18 1,9
9 1,4 21 2,1
12 1,6 24 2,2
15 1,7    

План урока:

  1. Организационный момент (по технологии ИОСО заменяется психологическим настроем).
  2. Задачи на урок.

ХОД УРОКА

Учитель: Здравствуйте! Ус покоились. (Идет  психологическая установка ). Закройте глаза. Представьте себя в море. Солнце сегодня щедрое, вода теплая. Почувствуйте как спокойно вам дышиться, успокаивается пульс, расслабляются мышцы лица. А вокруг тишина…. Послушайте? как ровно бьется сердце… сосчитайте до  8. Откройте глаза! Садитесь.

(Ученики садятся)

Учитель: Сегодня  мы снова в  деловой игре. Вы – профессиональные независимые эксперты. Ваша задача, используя знания по таблице Excel решить важнейший вопрос для нашего города N вопрос о постройке завода. Итак, начинаем…

(На компьютерах установлена презентация к уроку «Применение Excel  в статистике».)

Учитель (официальным тоном):  Сегодня мы пригласили вас, дамы и господа, решить вопрос  о постройке завода, ваше решение  станет окончательным и поставит точку на этой проблеме. Все материалы вы найдете на ваших рабочих местах (жест на компьютерные столы) в файле Таблица «Excel» в статистике. Желаю Вам успешной работы.

Ученики рассаживаются по рабочим местам и начинают работать. За пять  минут до конца урока на экранах компьютеров должен стоять слайд № 11 с решением каждого учащегося. Учитель просматривает результаты – обычно они однозначны – «НЕТ».

Учитель:  Теперь у нас 5 минут рефлексии… Что нового вы узнали про таблицу Еxcel?
Ученик 1: Знаем, что она используется в  статистике…
Ученик 2: Теперь знаем про метод наименьших квадратов…
Ученик 3:   Знаем как получить формулу уравнения, знаем про коэффициент детерминированности R2.
Ученик 4: Если  R2  близко к  1, то формула подобрана оптимально.
Учитель: Какая формула была лучшим решением данной проблемы?
Ученик: Экспотенциальная  функция. Там R2 =9.9.
Учитель: Что понравилось на уроке?
Ученик 5: Интересно было работать самим.
Ученик 1: Тема интересная.
Ученик 3: Необычный урок. Здорово!
Учитель: Спасибо за вашу работу.  Оценки сегодня получили следующие…(при оценивании работы обязательно учитывается самооценка учащегося и оценка учителя, в случае разногласия учитель подробно обязан прокомментировать свою оценку).

Приложение 1: Презентация «Применение ЭТ «Excel» в статистике.

Литература: И.Семакин, Е.Хеннер. Информационные технологии. 11 класс, М. БЛЗ, 2005 г.