Шаг 4. Построение регрессионных моделей по нелинейным формам связи.

Подобрать наилучшую форму связи между результативным и независимым фактором при помощи построения диаграммы рассеяния.

Вызываем команду ВСТАВКА – ДИАГРАММА. Появится меню МАСТЕР ДИАГРАММ шаг 1 из 4 (рис. 2.1).

Рис. 2.1. Шаг 1 меню МАСТЕРА ДИАГРАММ

Выбираем вид диаграммы: точечная. Нажимаем кнопку ДАЛЕЕ. Появится меню Шаг 2 из 4: выбор диапазона исходных данных (рис. 2.2).

Рис. 2.2. Выбор диапазона исходных данных Рис. 2.3. Закладка РЯД меню МАСТЕРА ДИАГРАММ

Установим курсор в окошко Диапазон и выделим курсором мыши диапазон исходных данных (см. рис. 2.2). Ряды: в столбцах. Компьютер распознает первый столбец исходных данных как независимую переменную, поэтому перейдем в закладку РЯД и проверим, чтобы Значения X и Значения Y соответствовали данным рабочей таблицы (см. рис. 2.3). Нажимаем кнопку ДАЛЕЕ.

Шаг 3 из 4: параметры вывода. Можно установить различные параметры оформления диаграммы. Кнопка ДАЛЕЕ. Шаг 4 из 4: определяем, где поместить диаграмму. Нажимаем кнопку ГОТОВО. Диаграмма рассеяния для рассматриваемого примера представлена на рис. 2.4.

Рис. 2.4. Диаграмма рассеяния

Построим линию тренда. Выделим поле диаграммы. Меню: ДИАГРАММА – ДОБАВИТЬ ЛИНИЮ ТРЕНДА. Появится меню: ЛИНИЯ ТРЕНДА (рис. 2.5). Выберем любой тип линии тренда, кроме типа «Линейная фильтрация». Начнем с линейного. Зайдем на закладку: ПАРАМЕТРЫ (рис. 2.6) и установим флажки: Показывать уравнение на диаграмме,Поместить на диаграмму величину достоверности аппроксимации.

Рис. 2.5. Выбор типа линии тренда   Рис. 2.6. Установка параметров линии тренда

После нажатия кнопки ОК на графике появится линия тренда, ее уравнение и коэффициент детерминации (см. рис. 2.7).

Рис. 2.7. Линия тренда

Уравнение, которое отражено на диаграмме, должно совпадать с тем уравнением, которое мы построили при помощи АНАЛИЗ ДАННЫХ – РЕГРЕССИЯ при выполнении соответствующего варианта лабораторной работы № 1.

Далее, чтобы подобрать нужный вид линии тренда по коэффициенту детерминации R2, устанавливаем курсор мыши на линии тренда и нажимаем правую клавишу. Появится контекстное меню, из которого нужно выбрать: ФОРМАТ ЛИНИИ ТРЕНДА (см. рис. 2.8). Выберем другой тип линии в меню ЛИНИЯ ТРЕНДА. Например, логарифмическая. Запишем получившееся уравнение и коэффициент детерминации. Затем снова воспользуемся контекстным меню для получения аналогичных данных по полиномиальной 2-й степени, степенной и экспоненциальной линии тренда.

Рис. 2.8. Вход в меню «Формат линии тренда»

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

Таблица 2.2

№ п.п. Уравнение регрессии Примечания
1. Логарифмическое Уравнение удовлетворительного качества, однако хуже экспоненциального.
2. Полиномиальное 2 степени Наибольший R2, однако коэффициент при x статистически незначим.
3. Степенное Уравнение удовлетворительного качества, однако хуже экспоненциального.
4. Экспоненциальное Второй по величине R2, коэффициент при x статистически значим. Наилучшее из всех уравнений.

Шаг 3. Найти прогнозное значение результата по наилучшему из построенных уравнений.

Итак, мы выбрали экспоненциальное уравнение (см. табл. 2.2). Выразим y через x в явном виде: . Рассчитаем прогноз: тыс. ед. Значит, если инвестиции будут равны 19,5 тыс. д.е., то объем производства должен составить 61 тыс. 580 ед.

Шаг 4.Оформить отчет о проделанной работе.

План отчета.

1.Укажите фамилию, имя, название группы, номер варианта.

2.Запишите линейное уравнение регрессии в формате:

3.Дайте экономическую интерпретацию параметров уравнения.

4.Оцените тесноту линейной зависимости факторов.

5.Охарактеризуйте качество модели.

6.Сделайте вывод о статистической значимости параметров и уравнения в целом.

7.Сделайте общий вывод о качестве уравнения и возможности его использования для прогноза.

8.Рассчитайте прогнозное значение результативного показателя по известному значению независимого фактора.

9.Запишите уравнение парной нелинейной регрессии, которое наилучшим образом описывает зависимость факторов и рассчитайте прогноз результативного показателя.

ПРИЛОЖЕНИЯ

Приложение А

Протокол регрессионного анализа.

Регрессионная статистика

Наименование показателя Принятые наименования Формула
Множественный R Коэффициент множественной корреляции
R-квадрат Коэффициент детерминации R2
Нормированный R-квадрат Скорректированный R2
Стандартная ошибка Среднеквадратическое отклонение от модели
Наблюдения Количество наблюдений n n

Дисперсионный анализ

  df SS – сумма квадратов MS – среднее значение F-критерий Фишера Значимость F
Регрессия
Остаток    
Итого      
Коэффициенты Стандартная ошибка t-статистика P-Значение
Y-пересечение b0
x1 b1
x2 b2

Таблица «Регрессионная статистика» включает множественный коэффициент корреляции, коэффициент детерминации, нормированный коэффициент детерминации, стандартная ошибка и количество наблюдений. Столбец df таблицы «Дисперсионный анализ» содержит число независимых переменных, количество степеней свободы, столбец SS – объясненную сумму квадратов, сумму квадратов остатков и общую сумму квадратов результативного фактора, столбец MS -- объясненную сумму квадратов и сумму квадратов остатков на одну степень свободы, столбец F – расчетное значение статистики Фишера. В столбце значимость F расположена вероятность ошибки расчета (если это значение меньше уровня значимости , то Fрасч > Fкр.).

В третьей таблице в столбце Коэффициенты находятся свободный коэффициент и коэффициенты при переменных уравнения регрессии. Каждому коэффициенту соответствует значение t-статистики (одноименный столбец таблицы). P-Значение показывает вероятность ошибки расчета t-статистики, причем если P-Значение меньше уровня значимости , то tрасч > tкр.

Приложение Б

Расчет критических значений t и F.

Значение tкр можно получить при помощи статистической функции, которая имеет формат СТЬЮДРАСПОБР(< >; <n – m – 1>), где α – уровень значимости, по умолчанию α = 0,05; n – число наблюдений; m – количество независимых переменных в модели. Устанавливаем курсор в пустой ячейке. Вызываем команду ВСТАВКА – ФУНКЦИЯ – СТАТИСТИЧЕСКИЕ (рис. 5.1). Выбираем функцию СТЬЮДРАСПОБР и вводим значения вероятности и количества степеней свободы (рис. 5.2).

Рис. 5.1. Мастер функций Рис. 5.2. Функция СТЬЮДРАСПОБР

Значение Fкр можно получить при помощи статистической функции, которая имеет формат FРАСПОБР(< >; <m>; <n – m – 1>), где α – уровень значимости, по умолчанию α = 0,05; n – число наблюдений; m – количество независимых переменных в модели. Вызываем команду ВСТАВКА – ФУНКЦИЯ – СТАТИСТИЧЕСКИЕ – FРАСПОБР (рис. 5.3).

Рис. 5.3. Функция FРАСПОБР

Наши рекомендации