Лабораторная работа № 5. Определение параметров моделей нелинейных зависимостей в форме, определенной пользователем
Цель работы: изучение порядка нахождения параметров уравнения нелинейной регрессии в форме, определенной пользователем и методики оценки достоверности уравнения и коэффициентов регрессии.
Исходные положения. Для нахождения параметров линейного уравнения регрессии используется функция ЛИНЕЙН, для нелинейного уравнения – функция ЛГРФПРИБЛ. Обычно анализ данных производят имея представления о форме зависимости. Если не подходит линейная форма, используют нелинейную функцию. Если же обе функции – ЛИНЕЙН и ЛГРФПРИБЛ не дают удовлетворительного результата, то в этом случае уравнение регрессии можно находить в виде функции, вид которой назначает пользователь. Например, уравнение нелинейной регрессии можно искать в виде полинома второй степени:
у = а0 + а1х1 + а2х2 + а3х12 + а4х22 + а5х1х2.
Такую зависимость можно представить в линейной форме (линеаризовать) путем замены квадратов и произведений переменных другими переменными. Например, введем замену х3=х12, х4=х22, х5=х1х2; тогда уравнение примет вид:
у = b0 + b1х1 + b2х2 + b3х3 + b4х4 + b5х5.
Оценка достоверности ( α ) величины R2 уравнения регрессии производится с помощью Статистической функции FРАСП. В диалоговое окно ввести адрес ячейки, содержащей Fрасч.; число степеней свободы, равное количеству аргументов уравнения (х1, х2 …); адрес ячейки, содержащей число степеней свободы (df).
α = FРАСП(A18;2;8)
Величина α - это вероятность того, что зависимость y от хi отсутствует. Для оценки достоверности наличия зависимости у от хi нужно из единицы вычесть значение, полученное с помощью функции FРАСП.
Достоверность
значения определяемых величин b0
и bi
оценивается
с помощью вероятности, найденной из
распределения Стьюдента. Для этого
нужно вычислить величины ti=bi
/i
(i=
).
Далее определим δ-вероятность того, что
значения bi
и i
не
достоверны: вызвать статистическую
функцию СТЬЮДРАСП
и в диалоговое окно ввести адрес
найденного значения ti
(для
исключения отрицательных величин
используем функцию абсолютной величины
ABS);
адрес ячейки, содержащей число степеней
свободы (df)
– число измерений минус число параметров
модели; хвосты – 2 (это признак используемого
двухстороннего распределения Стьюдента):
δ =СТЬЮДРАСП(ABS(A21);В18;2)
Определим (1 - δ) – вероятность того, что значения bi достоверны.
Пример выполнения работы. Введем исходные данные в таблицу:
|
A |
B |
C |
1 |
x1 |
x2 |
y |
2 |
17 |
2 |
50 |
3 |
15 |
17 |
250 |
4 |
13 |
12 |
400 |
5 |
12 |
14 |
500 |
6 |
10 |
10 |
550 |
7 |
9 |
20 |
600 |
8 |
8 |
11 |
550 |
9 |
7 |
15 |
400 |
10 |
6 |
13 |
375 |
11 |
4 |
18 |
150 |
12 |
1 |
5 |
50 |
Используя функцию листа ЛИНЕЙН определим параметры уравнения регрессии, коэффициент детерминации и среднеквадратические отклонения:
15 |
18.73282693 |
6.21542 |
61.32999 |
16 |
11.36621921 |
12.82976 |
203.4766 |
17 |
0.259628468 |
192.5387 |
#Н/Д |
18 |
1.402692872 |
8 |
#Н/Д |
19 |
103998.9033 |
296569.3 |
#Н/Д |
Определим оценки достоверности коэффициента детерминации и коэффициентов уравнения регрессии:
|
A |
B |
C |
D |
15 |
18.73282693 |
6.21542 |
61.32999 |
|
16 |
11.36621921 |
12.82976 |
203.4766 |
α |
17 |
0.259628468 |
192.5387 |
#Н/Д |
=FРАСП(A18;2;8) |
18 |
1.402692872 |
8 |
#Н/Д |
1 - α |
19 |
103998.9033 |
296569.3 |
#Н/Д |
=1-D17 |
20 |
t2 |
t1 |
t0 |
|
21 |
=A15/A16 |
=B15/B16 |
=C15/C16 |
|
22 |
δ2 |
δ1 |
δ0 |
|
23 |
=СТЬЮДРАСП(ABS(A21); $B$18;2) |
=СТЬЮДРАСП(ABS(B21);$B$18;2) |
=СТЬЮДРАСП(ABS(C21);$B$18;2) |
|
24 |
1- δ2 |
1- δ1 |
1- δ0 |
|
25 |
=1-A23 |
=1-B23 |
=1-C23 |
|
В результате получим: коэффициент детерминации R2=0.259 , оценка достоверности по большинству параметров малозначима.
15 |
18.73283 |
6.2154196 |
61.32999 |
|
16 |
11.36622 |
12.829756 |
203.4766 |
α |
17 |
0.259628 |
192.53872 |
#Н/Д |
0.300468 |
18 |
1.402693 |
8 |
#Н/Д |
1 - α |
1
|
103998.9 |
296569.28 |
#Н/Д |
0,699532 |
20 |
t2 |
t1 |
t0 |
|
21 |
1.648114 |
0.4844535 |
0.301411 |
|
22 |
δ2 |
δ1 |
δ0 |
|
23 |
0.137942 |
0.641049 |
0.770787 |
|
24 |
1- δ2 |
1- δ1 |
1- δ0 |
|
25 |
0.862058 |
0.358951 |
0.229213 |
|
Воспользуемся функцией листа ЛГРФПРИБЛ для определения параметров нелинейной модели и оценим значимость параметров:
|
A |
B |
C |
D |
27 |
1.119235523 |
1.033221055 |
48.65104965 |
|
28 |
0.045577723 |
0.0514464 |
0.815926393 |
α |
29 |
0.438003529 |
0.77206645 |
#Н/Д |
=FРАСП(A29;2;8) |
30 |
3.117482412 |
8 |
#Н/Д |
1 - α |
3
|
3.716579003 |
4.768692826 |
#Н/Д |
=1-D29 |
32 |
t2 |
t1 |
t0 |
|
33 |
=ABS(LN(A27)/A28) |
=ABS(LN(B27)/B28) |
=ABS(LN(C27)/C28) |
|
34 |
δ2 |
δ1 |
δ0 |
|
35 |
=СТЬЮДРАСП(ABS(A33);$B$30;2) |
=СТЬЮДРАСП(ABS(B33);$B$30;2) |
=СТЬЮДРАСП(ABS(C33);$B$30;2) |
|
36 |
1- δ2 |
1- δ1 |
1- δ0 |
|
37 |
=1-A35 |
=1-B35 |
=1-C35 |
|
При расчете параметра t при использовании формулы: ti=abs(ln(bi)/ln(i)), необходимо использовать натуральный логарифм в числителе, а в знаменателе использовать логарифм не следует, поскольку в данном случае функция возвращает натуральные логарифмы среднеквадратических отклонений.
|
A |
B |
C |
D |
27 |
1.119235523 |
1.033221055 |
48.65104965 |
|
28 |
0.045577723 |
0.0514464 |
0.815926393 |
α |
29 |
0.438003529 |
0.77206645 |
#Н/Д |
0,099755 |
30 |
3.117482412 |
8 |
#Н/Д |
1 - α |
31 |
3.716579003 |
4.768692826 |
#Н/Д |
0,900245 |
32 |
t2 |
t1 |
t0 |
|
33 |
2.471511891 |
0.635246794 |
4.761058615 |
|
34 |
δ 2 |
δ 1 |
δ0 |
|
35 |
0.038619312 |
0.543005729 |
0.001424864 |
|
36 |
1- δ2 |
1- δ1 |
1- δ0 |
|
37 |
0.961380688 |
0.456994271 |
0.998575136 |
|
Результат расчета представлен в следующем виде:
Как видно, коэффициент детерминации невелик, а значимость параметров выше, чем при использовании линейной функции.
Используем модель зависимости в форме, определенной пользователем. Введем исходные данные и преобразуем ряды данных, как это показано в таблице. Новые ряды данных получаются путем возведения рядов х1 и х2 в квадрат и перемножения значений в этих рядах.
В результате получим:
|
E |
F |
G |
H |
I |
J |
1 |
x1 |
x2 |
x1^2 |
x2^2 |
x1x2 |
y |
2 |
17 |
2 |
289 |
4 |
34 |
50 |
3 |
15 |
17 |
225 |
289 |
255 |
250 |
4 |
13 |
12 |
169 |
144 |
156 |
400 |
5 |
12 |
14 |
144 |
196 |
168 |
500 |
6 |
10 |
10 |
100 |
100 |
100 |
550 |
7 |
9 |
20 |
81 |
400 |
180 |
600 |
8 |
8 |
11 |
64 |
121 |
88 |
550 |
9 |
7 |
15 |
49 |
225 |
105 |
400 |
10 |
6 |
13 |
36 |
169 |
78 |
375 |
11 |
4 |
18 |
16 |
324 |
72 |
150 |
12 |
1 |
5 |
1 |
25 |
5 |
50 |
Анализ результатов полученных с помощью последней модели, показывает, что коэффициент детерминации в данном случае значительно выше, чем для линейной и нелинейной зависимостей. Оценки достоверности также более значимы для модели, определенной пользователем.
|
E |
F |
G |
H |
I |
J |
K |
15 |
2.437183 |
2.413082 |
-11.5036 |
-91.5865 |
188.9164 |
255.8772 |
|
16 |
0.354402 |
0.449605 |
0.687025 |
13.85134 |
11.11549 |
66.93317 |
α |
17 |
0.990664 |
27.3488 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д |
4,529E-05 |
18 |
106.11 |
5 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д |
1- α |
19 |
396828.4 |
3739.784 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д |
0,999954 |
20 |
t5 |
t4 |
t3 |
t2 |
t1 |
t0 |
|
2
|
6.876886 |
5.367111 |
-16.7441 |
-6.61211 |
16.99578 |
3.822876 |
|
22 |
δ5 |
δ4 |
δ3 |
δ2 |
δ1 |
δ0 |
|
23 |
0.000995 |
0.003022 |
1.39E-05 |
0.00119 |
1.29E-05 |
0.012337 |
|
24 |
1- δ5 |
1- δ4 |
1- δ3 |
1- δ2 |
1- δ1 |
1- δ0 |
|
25 |
0.999005 |
0.996978 |
0.999986 |
0.99881 |
0.999987 |
0.987663 |
|
Порядок выполнения работы
1.Получить у преподавателя данные для расчета.
2.Ввести исходные данные в таблицу Excel.
3.Провести на ЭВМ серию расчетов по определению параметров множественной корреляционной зависимости для линейной, нелинейной модели и модели, определяемой пользователем.
4.Определить оценку достоверности коэффициентов корреляции и детерминации.
5.Зафиксировать результаты расчетов в тетради.
6.Сделать выводы по результатам моделирования и записать в тетради.
Отчет по работе должен содержать
1.Название и цель работы.
2.Основные теоретические и методические положения.
3.Исходные данные для расчета.
4.Результаты расчета.
5.Выводы по результатам моделирования.