Главная стр 1
скачать


Департамент образования города Москвы

ГОУ Педагогический колледж № 5


Методическое пособие


Лабораторная работа
по предмету «Информатика»



«Моделирование прогноза
спортивных достижений
с помощью электронной таблицы»

Составитель Медведев Л.Н.

2007 г.

Цель работы


В работе проводится моделирование развития спортивных достижений по отдельным видам олимпийского спорта.

Целью работы является:



  • Изучение понятия тренда как кривой, описывающей статистические данные с некоторой степенью приближения;

  • Изучение механизма построения тренда методом наименьших квадратов (с использованием аппарата построения трендов разных типов в MS Excel)

  • Выявление прогностических свойств тренда и проверка возможности предсказания результатов олимпийских достижений по отдельным видам спорта

  • Практика по работе в MS Excel

В качестве исходного материала используются таблицы олимпийских достижений по разным видам спорта. Использованы те виды спорта, в которых результат может быть измерен метрами или секундами. Таблицы получены с сайта http://www.gbrathletics.com, где данные по длинам приводятся в метрах, по весам – в килограммах, а по времени – в минутах, секундах и сотых долях секунд.

Теоретическая часть


В экономике и технике часто возникает задача подбора функциональной зависимости для двух наборов данных. Независимые переменные xi называют факторами, а зависимые yi — откликами. Функция y=f(x) позволяет предсказывать значение отклика для факторов, не входящих в исходную совокупность.

1. Метод наименьших квадратов

Решим следующую задачу. Дан набор точек (xi,yi), i = 1,..., п. Пусть имеется класс функций F (линейные, квадратичные, экспоненциальные и т.д.). Требуется найти функцию у =f(x) из F, такую, чтобы ее значения f(xi) наилучшим образом приближа­ли значения уi

Что означают слова "наилучшим образом"? Нужно выбрать критерий, насколько одна функция лучше другой. Для этого рассмотрим набор остатков еi = уi - f(xi). Выбором функции f(x) нужно сделать их как можно меньшими. Но для сравнения качества приближения необходимо свернуть еi в одну функцию I(e1,..., еn). Просто сложить остатки нельзя, ведь они могут иметь разные знаки, и тогда ошибки могут взаимно компенси­роваться. Поэтому надо выбирать либо сумму абсолютных зна­чений остатков, либо сумму квадратов остатков. По ряду при­чин удобнее всего выбрать минимизацию квадратов остатков:

1

2. Линейная регрессия

Так как задача отыскания функциональной зависимости очень важна, в Excel введен набор функций, которые позволяют решать эту задачу. Эти функции основаны на методе наимень­ших квадратов. Но регрессионный анализ — это не только ме­тод наименьших квадратов. Относительно исходных данных делаются некоторые статистические предположения. В качестве результата выдаются не только коэффициенты функции, при­ближающей данные, но и статистические характеристики полу­ченных результатов.

Исходные предположения таковы. Считается, что зависи­мость между откликом и факторами имеет вид у = f(x) + , где — некоторая случайная величина. Именно из-за нее возника­ют ненулевые остатки еi. В гауссовской модели простой линей­ной регрессии предполагается, что i независимы и распределе­ны по нормальному закону с нулевым средним и одинаковой дисперсией.

В данной статье эти предположения обсуждаться не будут. Формулы для статистических оценок нужно искать в специаль­ной литературе. Лучше всего обратиться к двухтомнику [1]. Для начинающих может оказаться полезной книга [2]. Подробности о вычислении регрессии в MS Excel можно посмотреть в Приложении в конце статьи.



Для чего может быть использована регрессия?

Пример 1. Поиск связи между ценами на автомобиль и годом его выпуска. Для этого можно взять данные из любой газеты (например, «Из рук в руки») и выписать их в два столбца. В первом столбце записать возраст модели, а во второй столбец – цену в у.е. Если построить график зависимости цена-возраст, то она будет выглядеть хаотично, но в этом хаосе есть, конечно же, своя система. Регрессия помогает выявить ее (см. рис.1, где выборка данных охватывает возрасты от 8 до 18 лет). Более того, с помощью регрессии можно предсказать, какой ожидается цена машины, скажем, 19-20 или 6-7 лет. Конечно, эта оценка не точная, она
статистическая.

П

Рисунок 1

ример 2
. Прогноз спортивных достижений. На рис. 2 приведен график времени бега на 100 м у мужчин по ряду Всемирных олимпиад. По линии тренда



Рисунок 2

(полиномиальный, третьей степени) можно предсказать, что на Олимпиаде 2004 года результат будет 19,84 с. На этом же рисунке овалом обозначено время реально показанное на этой Олимпиаде. Хорошо видно, что, несмотря на не очень высокую точность прогноза (R2= 08928, т.е. погрешность около 11%) результат попал близко к кривой тренда. Реальный результат 2004 года 19,79, т.е. погрешность составляет =(19,84-19,79)/1979=0,0025 или 0,25%. Хорошее попадание!



Порядок выполнения эксперимента по прогнозу олимпийских результатов

Выберите на копии сайта Forall:\Mедведев\1 семестр\Спорт\index.htm свой вариант статистических данных.

Создайте в MS Excel таблицу типа приведенной на рис. 3.


Результаты 100 м мужчины

Год

Сек

1900

22,200

1904

21,600

Е
Рисунок 3 Структура таблицы

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

Заполните таблицу данными с олимпиад от самой ранней до 2000 года, а последнюю олимпиаду оставьте как контрольную. Если есть в таблице формула расчета результатов, то заполните соответствующие ячейки этими формулами, например, как на рис.4

П
Рисунок 4 подсчет времени


остройте точечную диаграмму по данным столбцов Год – результат. Диаграмма может походить на рис. 2. Отметьте с помощью средств панели «Рисование» (например, нарисуйте кружок) результат, который был получен на Олимпиаде 2004 года.

На диаграмме правой кнопкой щелкните по любой из точек. В контекстном меню выберите «Добавить линию тренда». На закладке «Тип» выберите тип тренда, установите на закладке «параметры»:



  • Прогноз – вперед на 4 единицы (ведь Олимпиады идут через 4 года)

  • Показывать уравнение на диаграмме

  • Поместить на диаграмму величину достоверности аппроксимации (R^2)

Нажав ОК, проверьте, проходит ли кривая тренда через точку результата Олимпиады-2004 или близко от нее. Чтобы было хорошо видно, как прошла кривая, подберите размеры диаграммы, установите минимальное и максимальное значение шкалы по оси Y(значений) таким, чтобы график занимал всю площадь диаграммы. Подберите также значения шкалы по оси Х (категорий). Если шрифт уравнения и R2 слишком велик, то, щелкнув по ним дважды, подберите размер шрифта (флажок «Автомасштабирование» снимите) и установите для них обычную заливку.

Если кривая прошла далеко, то, щелкнув по линии тренда дважды, выберите другой тип тренда. Подберите тот тип, который ЛУЧШЕ ВСЕГО ОПИСЫВАЕТ РЕЗУЛЬТАТ ОЛИМПИАДЫ-2004.

Запишите в протокол полученные данные. Сделайте вывод, хорошо ли тренд описывает спортивные результаты и можно ли использовать его для прогнозирования.

ПРОТОКОЛ ЭКСПЕРИМЕНТА

Вариант 0. Исполнитель Иванов И.И., группа 1234Б



Анализировались олимпийские результаты по бегу на 100 м (мужчины):

Вид

бег 100 м

м/ж

м

год

результат

1900

22,20

1904

21,60

1908

22,60

1912

21,70

1920

22,00

1924

21,60

1928

21,80

1932

21,12

1936

20,70

1948

21,10

1952

20,81

1956

20,75

1960

20,62

1964

20,36

1968

19,83

1972

20,00

1976

20,22

1980

20,19

1984

19,80

1988

19,75

1992

20,01

1996

19,32

2000

20,09

2004

19,79

В результате подбора тренда получена аппроксимирующая кривая, приведенная на рис. 2. Точность описания данных R2=0,9149. Тренд полиномиальный, уравнение кривой y = 4*10-6x3 - 0,0246x2 + 47,846x - 30946 + 3*107 погрешность составила

=(19,84-19,79)/1979=000,25 или 0,25%.

Кривая прошла очень близко от точки 23 Олимпиады, что показывает удовлетворительную возможность прогноза спортивного результата по бегу.

Приложение

В Excel используются такие формулы для расчета линий тренда



Арифметическая

Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением: , где m - угол наклона и b - координата пересечения оси абсцисс.



Полиномиальная

Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением: , где b и с1 … с6 — константы.



Логарифмическая

Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением: , где c и b - константы, ln - функция натурального логарифма.



Экспоненциальная

Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением: , где c и b - константы, e - основание натурального логарифма.



Степенная

Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:, где c и b - константы.



Величина R-квадрат, показывающая точность аппроксимации

,

где и



Примечание. Отображаемое вместе с линией тренда значение величины R-квадрат не является корректным. Для логарифмической, степенной и экспоненциальной линий тренда в Microsoft Excel используется несколько видоизмененная модель регрессии.

Скользящее среднее



Примечание. Число точек, образующих линию скользящего среднего, равно числу точек в исходном ряде минус значение периода.

Литература


  1. Дрейпер Н. Прикладной регрессионный анализ. В 2-х кн. — М.:Финансы и статистика, 1986.

  2. Доугерти К. Введение в эконометрику. —М.: ИНФРА-М, 1997

  3. Угринович Н. Д. Информатика и информационные технологии. Учебник для 10-11 классов. —М.: Лаборатория Базовых Знаний, 2002

  4. Персон Р. Microsoft Excel 97 в подлиннике: в 2 т.— СПБ.: BHV, 1998. Том 1





скачать


Смотрите также:
Лабораторная работа по Предмету «Технология деталей»
126.97kb.
Лабораторная работа по предмету «Информатика»
90.23kb.
Лабораторная работа №3 По предмету: Сети Команда telnet
57.34kb.
Программа по учебному предмету «Информатика и икт»
577.01kb.
Программа по учебному предмету «информатика и икт»
670.78kb.
Лабораторная работа №1: Исследование зависимости скорости равноускоренного движения от времени. Лабораторная работа №2
24.03kb.
Лабораторная работа №1 По предмету: Периферийные устройства «Установка и настройка принтера»
58.52kb.
Учебный план по предмету «Информатика» предусматривает усиление линии «Алгоритмизация и программирование»
90.61kb.
Лабораторная работа №1 Изучение автоматической телеграфной станции ат-пс-пд лабораторная работа №2 и зучение телеграфного коммутационного сервера «Вектор-2000»
822.36kb.
Курсовая работа по предмету Информатика на тему
62.25kb.
Лабораторная работа №3 По предмету: sgbd. «Триггеры. Восстановление баз данных.»
37.77kb.
Практическая работа по предмету «Информатика и икт»
78.75kb.