Построение регрессионных моделей с помощью табличного процессора

Опишем алгоритм получения с помощью MS Excel регрессионных моделей по МНК с построением тренда.

Сначала следует ввести табличные данные и построить точечную диаграмму, как это показано на рис. 2.12 (можно игнорировать все лишние детали — надписи, легенду, — чтобы получилось так, как на рис. 2.14 а, в качестве подписи к оси ОХ выбрать текст «Линейный тренд»). Далее следует: => щелкнуть мышью по полю диаграммы (по одной из точек диаграммы); => выполнить команду => Диаграмма => Добавить линию тренда;

:=> в открывшемся окне на закладке «Тип» выбрать «Линейный тренд»;

=> перейти к закладке «Параметры»; установить галочки на флажках «показывать уравнения на диаграмме» и «поместить на диаграмму величину достоверности аппроксимации R²», щелкнуть по кнопке ОК.

Диаграмма готова. Она будет точно такой, как на рис. 2.14 а. Аналогично можно получить и другие типы трендов. Квадратичный тренд получается путем выбора по­линомиального типа функции с указанием степени 2.

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

Продолжение линии тренда за границы области данных, приведенных в исходной таблице, называется экстраполя­цией. Для получения такого рисунка нужно добавить в опи­санный в описанный выше алгоритм еще одно действие: => на вкладке «Параметры» в области «Прогноз» в строке «вперед на» установить 2 единицы.

Здесь имеются в виду единицы используемого масштаба по горизонтальной оси.

 

Задание для самостоятельного выполнения

1. По данным из следующей таблицы постройте с помощью MS Excel линейную, квадратичную, экспоненциальную и логарифмическую регрессионные модели. Определите параметры, выберите лучшую модель. Произвести прогнозирование по полученной модели: получить значение Y для Х=1, 17, 19, 29.

X

Y

 

2. В следующей таблице приводится прогноз средней дневной температуры на последнюю неделю мая в различных городах европейской части России. Города упорядочены по алфавиту. Указана также географическая широта этих городов. Построить несколько вариантов регрессионных моделей (не менее трех), отражающих зависимость температуры от широты города. Выбрать наиболее подходящую функцию. Произвести прогнозирование по полученной модели: рассчитать прогноз средней температуры для следующих городов: Сочи — 43,5 гр. с. ш., Москва — 55,7 гр. с. ш., Санкт-Петербург — 60 гр. с. ш., Мурманск — 69 гр. с. ш.

 

Город	Широта, гр. с. ш.	Температура
Воронеж	51,5
Краснодар
Липецк	52,6
Новороссийск	44,8
Ростов на Дону	47,3
Рязань	54,5
Северодвинск	64,8
Череповец	59,4
Ярославль	57,7

Контрольные вопросы

1. Какие три характерные задачи управления и планирования решаются с помощью ЭВМ? В чем суть этих задач?

2. Придумайте формулировки трех типовых задач управления и планирования на темы деятельности школы.

3. Какие вам известны формы представления зависимостей между величинами?

4. Что такое математическая модель?

5. Приведите пример известной вам функциональной зависимости (формулы) между характеристиками некоторой системы.

6. Обоснуйте преимущества и недостатки каждой из трех форм представления зависимостей.

7. Что такое статистика? Являются ли результаты статистических расчетов точными?

8. Что такое регрессионная модель?

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

10. Для чего используется метод наименьших квадратов (МНК)?

11. Что такое тренд?

12. Как располагается линия тренда, построенная по МНК, относительно экспериментальных точек?

13. Может ли тренд, построенный по МНК, пройти выше всех экспериментальных точек?

14. В чем смысл параметра R²? Какие значения он принимает?

15. Какое значение примет параметр R², если тренд точно проходит через экспериментальные точки?

16. Что подразумевается под восстановлением значения по регрессионной модели?

17. Что такое экстраполяция?