Линейных моделей

 

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

 

Исходные положения. Решение задач аппроксимации с помощью Пакета анализа становится возможным после загрузки соответствующего приложения, которое представлено в Excel как дополнительная программа (надстройка) – Анализ данных. Для загрузки этой надстройки, ее имя следует отметить в диалоговом окне Надстройки, которое открывается с помощью команды Надстройки из меню Сервис. После установки в списке надстроек опции программы Пакет анализа, соответствующая команда Анализ данных будет доступна при открытии меню Сервис.

Рис. 3. Диалоговое окно Надстройки при установке Пакета анализа

 

Теперь в меню Сервис при вызове команды Анализ данных можно выбрать из списка инструменты Регрессия и Корреляция, которые возвращают информацию о параметрах уравнения регрессии, коэффициенте детерминации и коэффициентах парной корреляции.

 

Рис. 4. Выбор инструмента анализа

 

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

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

у = b₀ + b_1*х₁ + b_2*х₂ +…+ b_n*x_n.

 

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

С помощью полученных параметров уравнения регрессии определить расчетные значения функции и прогнозные значения.

С помощью программы Анализ данных определить параметры однофакторных уравнений регрессии для каждой из независимых переменных в отдельности: у =b₀+b_1*х_{1 ;} у =b₀+b_2*х_{2 ; …} у =b₀+b_n*x_{n .}

По результатам расчетов сделать выводы.

Пример выполнения работы.

Используем исходные данные предыдущих работ. Из меню Сервис вызовем программу Анализ данных. В появившемся окне выберем инструмент Регрессия. В открывшемся диалоговом окне в соответствующих текстовых полях зададим ссылки на ячейки - адреса значений результативного признака - зависимой величины у – Входной интервал Y: Е3: Е12 и адреса значений независимых величин - факторов х₁, …, х_i, …, х_m (m ≤ 16) – Входной интервал X: В3: D12. Уровень надежности (доверительная вероятность) по умолчанию предполагается равным 95%. Если исследователя это значение не устраивает, то рядом со словами Уровень надежности нужно поставить «галочку» и указать требуемое значение. Вывод результатов произведем на новый рабочий лист.

 

 

 

Рис. 5. Диалоговое окно инструмента Регрессия

 

 

 

Рис. 6. Результаты расчета с помощью инструмента Регрессия

Результаты расчета: коэффициент детерминации R ²=0, 96; коэффициенты уравнения регрессии b₀= -305, 1, b₁= 0, 717, b₂= 0, 09, b₃= -1, 557.

Поставив «галочку» рядом со словом константа ноль, исследователь получит b₀=0 по умолчанию. Если нужны значения остатков е _i и их график, то нужно поставить «галочки» рядом со словами Остатки и График остатков. ОК.

Если число в графе Значимость Р превышает 5% - уровень надежности, то принимается гипотеза R²=0. Иначе принимается гипотеза R²≠ 0.

Р-значение - это значения уровней значимости, соответствующие вычисленным t -статистикам. Р -значение

= СТЬЮДРАСП (t -статистика; n - m - 1) (статистическая функция мастера функций fх). Если Р -значение превышает 5% уровень надежности, то соответствующая переменная статистически незначима и ее можно исключить из модели.

Нижние 95% и Верхние 95% - это нижние и верхние границы 95-процентных доверительных интервалов для коэффициентов теоретического уравнения линейной регрессии. Если исследователь согласился с принятым по умолчанию значением доверительной вероятности 95%, то последние два столбца будут дублировать два предыдущих столбца. Если исследователь вводил свое значение доверительной вероятности Р, то последние два столбца содержат значения соответственно нижней и верхней границы Р -процентных доверительных интервалов.

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

Для определения расчетных значений зависимой величины у _расч в ячейку (F3), расположенную рядом с первым значением у _фактич введем формулу:

у = а ₀ + а_1*х₁ + а_2*х₂ +…+ а_n*x_n.

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

Параметры уравнения регрессии отдельно для каждой из независимых переменных (однофакторные модели) рассчитываются аналогично, как и для многофакторной модели, но в текстовом поле инструмента Регрессия указываются адреса только одного ряда данных, соответствующего переменной х ₁, х ₂ и затем х ₃.

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

 

 

Рис. 7. Диалоговое окно инструмента Корреляция

 

 

На новом листе получим матрицу парных коэффициентов корреляции (табл. 1).

 

 

Таблица 1.

Коэффициенты корреляции

	Столбец 1	Столбец 2	Столбец 3	Столбец 4
Столбец 1
Столбец 2	0.54709691
Столбец 3	0.898639153	0.610267956
Столбец 4	0.983456355	0.560539112	0.83832548

 

Порядок выполнения работы

1.Получить у преподавателя данные для расчета.

2.Ввести исходные данные в таблицу Excel.

3.Провести на ЭВМ серию расчетов по определению прогнозных значений зависимой переменной с использованием модели множественной корреляционной линейной зависимости, определению парных коэффициентов корреляции.

4.Определить параметры множественного уравнения регрессии, расчетные значения зависимой переменной, прогнозные значения и коэффициенты корреляции и детерминации.

5.Зафиксировать результаты расчетов в тетради.

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

 

Отчет по работе должен содержать

1.Название и цель работы.

2.Основные теоретические и методические положения.

3.Исходные данные для расчета.

4.Результаты расчета.

5.Выводы по результатам моделирования.