EXCEL
5
I. . 6
1.1. ..................................................... 6
1.2. Excel ... 9
1.3. ... 17
.... 31
II. 32
2.1. ... 32
2.2. Excel 36
2.3. .. 41
58
III. .. 60
3.1. 60
3.2. Excel 62
68
IV. 69
4.1. ... 69
4.1.1. .... 69
4.1.2. .. 73
4.1.3. . 74
4.2. Excel .... 76
4.2.1. 77
4.2.2. . 80
4.2.3. . 82
... 83
V. .... 84
5.1. ... 84
5.1.1. 84
5.1.2. . 85
5.2. Excel 91
5.3. ... 104
120
... 121
. 122
. , .
, ( ), . : , , . 4 . :
|
|
- , , , ;
- l;
- , , ;
- , , .
l, , . , l, .
. . .
I
j Y, Y j =xj, j, .
I II .
:
. (1.1)
(1.1) Y , . (Y, ) , .. (1.1)
Y = M (Y / x) = 0 + 1 . (1.2)
Y , . , Y M (Y / x), . (1.2) :
Y* = M (Y / x) + (1.3)
( i, x i):
= 0 + 1 xi + i, . (1.4)
(1.4) .
i, :
1. i
0 + 1 xi.
2. i i, ..
, .
3. i j ,
.
4. i .
n, (Y, ) (m +1) . m .
n (Y, ).
, .
|
|
,
i = [ Y/X=x ] = b 0 + b 1 xi, . (1.5)
(1.3) Y :
yi = b 0 + b 1 xi + ei, (1.6)
i, b 0, b 1, e i yi, 0, 1, i.
, (Y, ) y 0 x. Y . , (). b 0 b 1 , yi i, (1.5),
min.
, , , i .
b 1 :
b 1 = Cov (X, Y) / , (1.7)
b 0:
b 0 = , (1.8)
= i / n; = i / n; Cov (X, Y) = ; = .
, :
, (1.9)
. (1.10)
S 2 , : S 2 = i 2 /(n 2), i = yi - b 0 - b 1 xi.
.
0 b0 b1 . . , t. b0 b1.
2. .
, b 0 b 1 , 0, 1.
, i = 0,1,
t . i
, i = 0,1,
= 1- . t , /2.
3. .
, . . R2:
R 2 = 1 - i 2 / (yi - )2. (1.11)
(1.11) :
(yi - )2 = ki 2 + ei 2, (1.12)
ki 2 = ( i - )2 . , ;
ei 2 = (yi - i)2 () yi .
(1.11) (1.12) , R 2 , :
R 2 = ki 2 / (yi - )2. (1.13)
, (1.11) (1.13).
- .
Y . , .
|
|
: 1) , .. M (Y / = x); 2) Y / = x.
:
t S , (1.14)
= b 0 + b 1 x; t , = n 2 /2.
:
t S . (1.15)