Ход работы
Имеются следующие исходные данные:
Таблица 1
Исходные данные
Y | X |
4,37 | 1,08 |
4,01 | 1,53 |
3,29 | 2,05 |
3,1 | 2,58 |
3,22 | 3,02 |
2,99 | 3,58 |
2,9 | 4,06 |
2,37 | 4,56 |
1,87 | 5,01 |
1,82 | 5,51 |
1,89 | 6,06 |
2,28 | 6,52 |
1,46 | 7,02 |
1,56 | 7,53 |
1,73 | 8,05 |
| 8,48 |
Где - прогнозное значение фактора x.
Исходные данные формируются в первых двух столбцах A3:A17, C3:C17. Блок промежуточный данных – D3:U17. Прогнозные значения вычисляются в 18 ряде.
Для нахождения средних значений столбцов отводим 21 ряд. Используем встроенную функцию СРЗНАЧ.
Для нахождения сумм столбцов отводим 24 ряд и используем встроенную функцию СУММ.
Нахождение оценок параметров
Вводится гипотеза, что между фактором X и показателем Y существует следующая стохастическая зависимость: . Заменой Y1= приводим нелинейную парную регрессию к парной линейной .
Параметры модели вычисляются по формулам:
Значение параметра а находится в ячейке В26. Формула имеет вид =(B21*A21-D21)/(B21^2-E21)
a= 0,06;
Значение параметра b находится в ячейке В27. Формула имеет вид =A21-B21*B26
|
|
b= 0,16
Также найдем оценки параметров с помощью функции ЛИНЕЙН(B3:B17;C3:C17;1;1) в диапазоне ячеек A40:B44.
Таблица 2
Результат работы функции ЛИНЕЙН
S | |
Степени свободы | |
SST | SSE |
Таблица 3
Результат работы функции ЛИНЕЙН для данной задачи
0,060479 | 0,156676 |
0,006999 | 0,035182 |
0,851731 | 0,058277 |
74,67854 | 13 |
0,253625 | 0,044151 |
Расчет теоретических значений У
Рассчитываем теоретические значения У по формуле в ячейках G3:G17. В ячейку F3 вводим формулу =$B$26*B3+$B$27.
Т.к. имеющаяся функция является обратной к линейной, то для нахождения ее теоретического значения в ячейку G3 вводим формулу =1/F3.
Для прогнозного значения х16= 8,48 найдем значение y по формуле ур= .
ур= 1,49.
Выяснение тесноты связи между у и х
Для выяснения тесноты связи можно воспользоваться двумя методами:
-построение точечной диаграммы:
Рис.1 – Точечная диаграмма
- использование коэффициента корреляции r
(-1<=r<=1)
Для нахождения коэффициента корреляции можно воспользоваться следующими формулами:
- Встроенная функция Excel КОРРЕЛ(B3:B17;C3:C17);
r1= 0,92
- Линейный коэффициент корреляции:
а)
r2= 0,92
б)
r3= 0,92
Так как коэффициент корреляции при вычислении всеми тремя способами равен r=0,92, то между у и х наблюдается тесная линейная связь.
Индекс корреляции:
Индекс корреляции вычисляется по следующей формуле:
Значение коэффициента детерминации находится в ячейке В46.
=0,85
Индекс корреляции R=0,92 находится в ячейке B47.Для парной линейной регрессии R2=r2, следовательно R=r=0,92