Здавалка
Главная | Обратная связь

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

Тема: КОРРЕЛЯЦИОННЫЙ И РЕГРЕССИОННЫЙ АНАЛИЗ В EXCEL

ЛАБОРАТОРНАЯ РАБОТА №1

ОПРЕДЕЛЕНИЕ КОЭФФИЦИЕНТА ПАРНОЙ КОРРЕЛЯЦИИ В ПРОГРАММЕ EXCEL

Корреляционная связь - это неполная, вероятностная зависимость между показателями, которая проявляется только в массе наблюдений.

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

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

Необходимые условия применения корреляционного анализа:

1. Наличие достаточно большого количества наблюдений о величине исследуемых факторных и результативных показателей.

2. Исследуемые факторы должны иметь количественное измерение и отражение в тех или иных источниках информации.

Применение корреляционного анализа позволяет решить следующие задачи:

1.Определить изменение результативного показателя под воздействием одного или нескольких факторов.

2. Установить относительную степень зависимости результативного показателя от каждого фактора.

Задание 1.

Имеются данные по 20 сельскохозяйственным хозяйствам. Найти коэффициент корреляции между величинами урожайности зерновых культур и качеством земли и оценить его значимость. Данные приведены в таблице.

Таблица. Зависимость урожайности зерновых культур от качества земли

Номер хозяйства Качество земли, балл х Урожайность, ц/га у
19,5
20,5
20,8
21,4
23,3
24,5
24,2
26,8
27,2
30,2

 

 

1. Для нахождения коэффициента корреляции использовать функцию КОРРЕЛ.

2. Значимость коэффициента корреляции проверяется по критерию Стьюдента.

 

Для рассматриваемого примера r=0,99, n=18.

 

Для нахождения квантиля распределения Стьюдента используется функция СТЬЮДРАСПОБР со следующими аргументам: Вероятность –0,05, Степени свободы –18.

Сравнив значение t-статистики с квантилем распределения Стьюдента сделать выводы о значимости коэффициента парной корреляции. Если расчетное значение t-статистики больше квантиля распределения Стьюдента, то величина коэффициента корреляции является значимой.


ПОСТРОЕНИЕ РЕГРЕССИОННОЙ МОДЕЛИ СВЯЗИ МЕЖДУ ДВУМЯ ВЕЛИЧИНАМИ

 

Задание 2.

По данным задания 1:

1) построить уравнение регрессии (линейную модель), которое характеризует прямолинейную зависимость между качеством земли и урожайностью;

2). выполнить проверку адекватности полученной модели.

 

Ый способ.

1. На листе Excel выделить массив свободных ячеек из пяти строк и двух столбцов.

2. Вызвать функцию ЛИНЕЙН.

3.Указать для функции следующие аргументы: Изв_знач_y- столбец значений показателя Урожайность, ц/га; Изв_знач_x- столбец значений показателя Качество земли, балл; Константа –1, Стат– 1 (позволяет вычислить показатели, используемые для проверки адекватности модели. Если Стат– 0, то такие показатели вычисляться не будут.

4. Нажать комбинацию клавиш Ctrl-Shift-Enter.

В выделенные ячейки выводятся коэффициенты модели, а также показатели, позволяющие проверить модель на адекватность (таблица 2).

Таблица 2
a1 a0
Se1 Se0
R2 Se
F n-k-1
QR Qe

 

a1, a0 – коэффициенты модели;

Se1 Se0 – стандартные ошибки коэффициентов. Чем точнее модель, тем меньше эти величины.

R2 – коэффициент детерминации. Чем он больше, тем точнее модель.

F – статистика для проверки значимости модели.

n-k-1– число степеней свободы (n-объем выборки, k- количество входных переменных; в данном примере n=20, к=1)

QR – сумма квадратов, обусловленная регрессией;

Qe – сумма квадратов ошибок.

 

5. Для проверки адекватности модели найти квантиль распределения Фишера Ff. с помощью функции FРАСПОБР. Для этого в любой свободной ячейке ввести функцию FРАСПОБР со следующими аргументами: Вероятность – 0,05, Степени_свободы_1–1, Степени_свободы_2–18. Если F> Ff, то модель адекватна исходным данным

6. Проверить адекватность построенной модели, используя расчетный уровень значимости (P). Ввести функцию FРАСП со следующими аргументами: X– значение статистики F, Степени_свободы_1–1, Степени_свободы_2– 18. Если расчетный уровень значимости P<α =0,05, то модель адекватна исходным данным.

Й способ.

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

 

1. Выбрать команду Сервис/Анализ данных/Регрессия. В диалоговом окне установить: Входной интервал Y – значения показателя Урожайность, ц/га,Входной интервал X – значения показателя Качество земли, балл.

2.Установить флажок Метки. В области Параметры вывода выбрать переключатель Выходной интервал и указать ячейку, с которой будет начинаться вывод результатов. Для получения результатов нажать кнопку ОК.





©2015 arhivinfo.ru Все права принадлежат авторам размещенных материалов.