:
:
1 .
2 .
3
1
, . , , , . , (, ) (, ), .. () , , , 5 .
MS Excel - . ( 4.1), y=mx+b. ( 4.2), y=bmx, m b . MS Excel r2, , . m b .
, , , .. .
- , , , .
4.1
x 1 | x 2 | ... | i | ... | n | |
y | x1 | y2 | ... | yi | ... | yn |
b
4.1 4.2
( 4.1, 4.2). =f(x), .
|
|
( ) , (), ().
. , , :
y=mx+b, (1)
:
- ;
- ;
m ;
b - .
, (1) , , .
2 .
, . Excel , ..
y=bmx, (2)
, .
, , , . , ( ), .. (2) , . , , , , .
. , . (1) (2) (3) (4):
=mlxl + m2x2 +... + mnxn + b (3)
= b mlxl m2x2 ... mnxn, (4)
:
xl, x2,..., xn - .
, , , , , , ..
Excel 5 ((...), (...), (...), (...), (...)) 2 - (...) POCT(...). .
1. =(._._y;. _. x;;) (5)
m b (1).
__ y __ x - y x ( ), (1).
- , , b 0. , b .
|
|
- , , .
( 0), m b, . 4.2:
4.2
mn | mn-1 | ... | m2 | m1 | b |
sen | sen-1 | ... | se2 | se1 | seb |
r2 | sev | ... | #/ | #/ | #/ |
F | df | ... | #/ | #/ | #/ |
ssreg | ssresid | ... | #/ | #/ | #/ |
se1,se2,...,sen - m1,m2,...,mn.
seb - b (seb #/, .. , . ).
r2 - . y , ; , 0 1. 1, , .. y. , 0, y.
sey - y ( ).
F - F-, F- . F- , .
df - c . F- . F-, .
ssreg - .
ssresid - o .
#/ , .
m y -:
(m):
, , m, (x1,y1) (x2,y2); m = (y2 - y1)/(x2 - x1).
y - (b):
y - , b, y , y.
y = mx + b. m b, y , y x . (. ).
, , . , , , , .
2. (5)
=(_a_; _ _; ;), (6)
, m b (2).
3. =(__y; __x;__x;) (7)
, , .
__ x - , y.
__ x , , x. .
4. (7)
=(__y; __x; ) (8)
:
(5)-(8) , .. , . :
1) (5)-(8) , . , , . 1, , . 2, . , .1;
|
|
2) . , .. Excel . . 1 9 ( 0), 0. , , .
3) , Shift + Ctr l + Enter. , . 2.
MS Excel , , .
, , :
1) , ;
2) , , , ,
3) (, , ..), (, , r 2, () .);
4) .
, , , :
, ,
, ,
.
, .
1. .
) , . 6 , , . 1 6 ( ) 1...G1 4.3.
4.3
y (133 890 ., 135 000 ., 135 790 ., 137 300 ., 138 130 ., 139 100 .), 2:G2 ( ). , .. 7, 8, 9 H1...J1. , , , , , H2 :
= (2:G2;B1:G1;H1).(10)
b OK 2 : 140230 . ( 2) I2, J2 8 9 .
, 142361,1 .
) , , , . (10) :
|
|
= (2:G2;;H1:G1).(11)
(11) (1:2:3:4:5:6) __, 6 , . (11) ;;.
(H1:G1) 3 , (140230:141282,7:142361,1).
2 ) . , D2:E7 (. 4.3). .
4.3
... | D | E | |
1 | - | -t○, . | |
2 | 1 | 2 | |
3 | 2 | 3 | |
4 | 3 | 4 | |
5 | 4 | 7 | |
6 | 5 | 12 | |
7 | 6 | 18 |
D8:E12 ( . 3), =(E2:E7;D2:D7;1;1), Ctrl+Shift+Enter, :
4.4
3,1428571 | -3,3333333 |
0,5408484 | 2,106302 |
0,8940887 | 2,2625312 |
33,767442 | 4 |
172,85714 | 20,47619 |
, m = 3,143 0,541, b = -3,333 2,106, .. , . 2,
= 3,143∙ -3,333 (12)
= 8 : = 3,143∙ 8 3,333 = 21,81 ○.
. 2,263, .. 2,263. , = 8 = 21,81 2,263 .
( r2) c 0,894 89,4%, .. , 89,4% (12) 3. .
) , = (E2:E7;;G2:G5) , , , G2:G5 7 10. 2:5, Ctrl+Shift+Enter, {18,667; 21,80952; 24,95238; 28,09524}, .. = 21,809 ≈21,81.
) , = f (x). :
=(,_._; ._._ ;._ ._)
2 =(8;E2:E7;D2:D7) 21,809. , , .
, .
3. ) . 2.
. 2 , , 2, . (, .) Excel = b∙mx c , b m. , .
F8:G12, =(E2:E7;D2:D7;1;1), Ctrl+Shift+ Enter, . 4.5:
4.5
1,56628015 | 1,196513 |
0,02038299 | 0,07938 |
0,99181334 | 0,085268 |
484,599687 | 4 |
3,52335921 | 0,029083 |
, m = 1,556, b = 1,197, .. :
= 1,197∙(1,556 ) (13)
m, b y 0,02, 0,07 0,08 . r 2 = 0,992, .. 99,2%.
. 2 (99,2%) m, b y, (13).
|
|
= 8 = 1,197 ∙ 34,363 = 41,131 0,085 ○.
) , :
=(__;__;__;).
F14:F17,
=(E2:E7;D2:D7;G2:G5;), {27,6696434; 43,3384133; 67,8800967; 106,319248}, .. =8 = 43,34 . . ), .
. , , ( ), .. , (, , , . 4.2 .) . . 4.2 3 .
3
4. , . :
y - ;
x1 - ;
x2 - ;
x3 - ;
x4 - .
11 1500 ( 4.6):
4.6
A | B | C | D | E | |
1 | 1 -, 2 | 2 - | 3 - | 4- , | , .. |
2 | 2310 | 2 | 2 | 20 | 42000 |
3 | 2333 | 2 | 2 | 12 | 144000 |
4 | 2356 | 3 | 1.5 | 33 | 151000 |
5 | 2379 | 3 | 2 | 43 | 151000 |
6 | 2402 | 2 | 3 | 53 | 139000 |
7 | 2425 | 4 | 3 | 23 | 169000 |
8 | 2448 | 2 | 1.5 | 99 | 126000 |
9 | 2471 | 2 | 2 | 34 | 142000 |
10 | 2494 | 3 | 3 | 23 | 163000 |
11 | 2517 | 4 | 4 | 55 | 169000 |
12 | 2540 | 2 | 3 | 22 | 149000 |
"" .
, (x1,x2,x3,x4) (y),.. .
.
1.
14:18 ( . 1),
= (E2:E12;A2:D12;;),
Ctrl+Shift+ Enter,
4.7:
4.7
A | B | C | D | E | |
14 | -234.237 | 2553.210 | 12529.7682 | 27.6413 | 52317.830 |
15 | 13.2680 | 530.66915 | 400.066838 | 5.42937 | 12237.361 |
16 | 0.99674 | 970.57846 | #H/ | #H/ | #H/ |
17 | 459.753 | 6 | #H/ | #H/ | #H/ |
18 | 1732393319 | 5652135.3 | #H/ | #H/ | #H/ |
y=ml ∙ xl+m2 ∙ x2+m3 ∙ x3+m4 ∙ x4+b 14:
y=27,64∙x1+12530∙x2+2553∙x3-234,24∙x4+52318 (14)
, 2500 . , , , 25 , :
=27,64 ∙ 2500+12530 ∙ 3+2553 ∙ 2-234,24 ∙ 25+52318=158261 ..
:
= (2:12; A2:D12; {2500;3;2;25}).
2.
蠠
=(E2:E12;A2:D12;;)
4.8:
4.8
0,99835752 | 1,0173792 | 1,0830186 | 1,0001704 | 81510,335 |
0,00014837 | 0,0065041 | 0,0048724 | 6,033E-05 | 0,1365601 |
0,99158875 | 0,0105158 | #/ | #/ | #/ |
176,832548 | 6 | #/ | #/ | #/ |
0,07821851 | 0,0006635 | #/ | #/ | #/ |
#/ | #/ | #/ | #/ | #/ |
, = b mlxl m2x2 m3x3 m4x4 , 4.6 :
= 81510,335 111,08320,01730,9984
0,992 (99,2%), .. , , (14) .
, , , , , , , .
1 , .
2 , ?
3 , ?
4 , ?
5 , ?
6 , , , , ?
7 ?
8 ?
9 ?
. :
1. , , .
2. ( ), ..
3. () . , . , .
4. , ( , , ).
5. ( ) 1 ( ), , .
, , . . .
1
1 . , , , 12 .
12 , .(. ).
2 , , , , ( ), 6 . , : -40%, -40%, -5%, -14%, -1%.
:
1 , % | 2 , % | 3 , % | 4 .,% | 5 . .% | , $. | |
40 | 35 | 10 | 10 | 5 | 410000 | |
... | ... | ... | ... | ... | ... | ... |
38 | 42 | 8 | 15 | 7 | 425000 |
(. ).
2
1 , 10 . , . .
:
1 | 2 | ... | 10 | 11 | |
1 , | 0,5 | 26,7 | ... | 42,8 | |
2 , | 0,45 | 22 | ... | 39,5 | |
, . | 6600 | 7200 | ... | 18250 |
=f (x1) = f(2), 1, 2, ..(. ).
2 : 1- , 2- , 3-
, 4 - , 5- . , 0 1, (% ). , , . 5 .
:
A | B | C | D | E | F | G | |||||
1 | 1-. | 2 - | 3- | 4-. | 5-. | - | |||||
2 | 1 | 0,8 | 0,2 | 0,4 | 0,6 | 1,0 | 76% | ||||
. | ... | ... | ... |
...