( ) xcel - ' .
1
6. , , ' . 6 - . , ' .
6
| , | ֳ 1 , | , . | |
:
1. ³ . . ' .
2. 6.
3. .
- (). .
- , 1:19 Ctrl 1:19. .
- - (. .4). .
- .
4. ', .
5. , . , ', . ˳.
6. , (R2). .
.4
, . ˳ ( , ) , .
: y = 0.543x + 5221.1,
|
|
|
y - ,
x - .
- 0,543 - , 5221,1 - , . : , , . , ' , . , , . ³ , y ( ). - 5221,1. , , 0, 5220 .
R2, 0,7081. , 71 % ' .
:
- R2 - ;
- , - (3:20;3:20);
- - (3:20; 3:20).
2
ֲ "Ѳ"
, , .
2
, , ' , " , ." ( ), , , " ," " 1 ,", (..1). ( ).
.
1. ³ .1s ? ³.
2. . 6.
3. ? . - . (. 5):
- Y - , , 1:19;
- - , 1 , 1:19; ̳;
- 0 ( ); ( 95 % );
- - ;
- , , , , ;
- .
4. ' ϳ4.
.5 ij Ѳ
, , . .
" " :
- R,, (R2), .
|
|
|
- R- (R2).
- R- . ³ :
1 - (1- R2) * (n - 1) / (n - m - 1),
R2 - , n - , m - . ( ) R2 . R2 , , , . , , 100 , R2 R2.
7
Ѳ
| R | 0.898076207 | |||||||||||||
| R- | 0.806540874 | |||||||||||||
| R- | 0.780746324 | |||||||||||||
| 14348.6222 | ||||||||||||||
| df | SS | MS | F | F | ||||||||||
| 31.26787915 | 4.46087E-06 | |||||||||||||
| 205882959.1 | ||||||||||||||
| - | - | t- | P- | 95% | 95% | |||||||||
| Y- | 36779.49257 | 13165.54282 | 2.793617632 | 0.013634136 | 8717.785046 | 64841.2001 | ||||||||
| 0.382841497 | 0.093439712 | 4.097203328 | 0.000951639 | 0.183679344 | 0.58200365 | |||||||||
| 1 | -358.1412987 | 129.6571733 | -2.762217389 | 0.014524676 | -634.4991918 | -81.7834057 | ||||||||
" " :
- Df - ( );
- SS - ;
- MS - , SS/Df;
- F - ;
- F - , S/S0.
.
( "" " "). "t-" t, .
, (1 - -) 1. , :
Y = 36779.49 + 0.3828* X(1) + (-358.1412)* (2),
Y - ,
X(1) -
(2) - 1
, , , : " 95%", " 95", .
|
|
|
3
ֲ ""
Excel .
, , . , . г :
y = mx + b y = m1x1 + m2x2 +... + b ( x)
y x. m - , x, b - . {mn;mn-1;...;m1;b}, (.). .
:
se1,se2,...,sen - m1,m2,...,mn.
Seb - b (seb = #/, ).
R2 - . г y , ; , 0 1. 1, , y. , 0, y.
Sey - c y.
F - F-. , ' .
Df - . F- . F-, .
Ssreg - .
Ssresd - . y y. .
Mcrosoft Excel y y, ( + ). , r2, , , , ' .
, .
8
| mn | Mn-1 | m1 | B | |
| sen | Sen-1 | se1 | Seb | |
| r2 | Sey | |||
| F | Df | |||
| ssreg | Ssresid |
:
1. ³ .1 - ³.
2. . 6.
3. , . , . 3, ( 1 ). 5 (. .6) 2:G7.
4. .
|
|
|
- .
- , ', :
.6 ij
- ³ y - y, y = mx + b. 2:19;
- ³ x - ' x, y = mx + b. ³ ֳ 1 2:19.
³ y x - , . x , , {1;2;3;...} y.
- - , , , b 0. 1 , b , 0, b 0 m , y = mx. 1.
- - , , . 1, , : {mn;mn-1;...;m1;b:sen;sen-1;...;se1;seb:r2;sey:F;df:ssreg;ssresd} (.8), m b. 1.
5. Ctrl+Shft+Enter.
6. (.7).
9
| -358,1412987 | 0,3828415 | 36779,49 |
| 129,6571733 | 0,09343971 | 13165,54 |
| 0,806540874 | 14348,6222 | #/ |
| 31,26787915 | #/ | |
| #/ |
