Шаг 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РАСПОБР