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

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

Опишем алгоритм получения с помощью MS Excel регрессионных моделей по МНК
с построением тренда. Сначала следует ввести табличные данные и построить точечную диаграмму, как это показано на рис. 2.12

Можно игнорировать все лишние детали — надписи, легенду, — чтобы получилось так, как на рис. 2.14 а, в качестве подписи к оси ОХ выбрать текст «Линейный тренд».

Далее следует:

= щелкнуть мышью по полю диаграммы;

= На вкладке Макет в группе Анализ нажмите кнопку Линия тренда и выберите Дополнительные параметры линии тренда…;

= в открывшемся окне выбрать тип линии тренда;

= установить галочки на флажках «показывать уравнения на диаграмме» и «поместить на диаграмму величину достоверности аппроксимации R²».

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

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

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

= в области «Прогноз» в строке «вперед на» установить 2 единицы.

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

Задание 1:

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

Задание 2:

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