: . , , . , , .
, . , ,
, (4.1)
, (4.2)
. (4.3)
(4.1) (4.3)
. (4.4)
1- , , w, . 2
; (4.5)
; (4.6)
, (4.7)
. (4.8)
(4.8) (4.6) (4.7), ,
(4.9)
, , , (4.10)
(4.9)
(4.11)
(1.24) ,
, . (4.12)
(4.12) (4.8),
. (4.13)
MS Excel ( )
1. (. 1, . 1) , (. . 4.1).
. 4.1. MS Excel
2. (. . 4.1).
4.1
I | J | K | L | M | N | O | |
01.04.2009 | |||||||
02.04.2009 | -0,4060 | 9,4195 | 5,7755 | 7,5093 | 1,0096 | 10,6154 | |
03.04.2009 | 7,7846 | 0,9081 | -1,7251 | 6,6790 | 8,7237 | 4,5858 | |
06.04.2009 | 0,1427 | -2,9406 | 0,0949 | -2,1038 | 5,0657 | -2,9851 | |
07.04.2009 | 0,4775 | -3,6137 | -1,4091 | -2,1361 | -5,4583 | -2,6938 | |
08.04.2009 | 5,3692 | 3,0372 | 3,7286 | 4,3082 | 5,9938 | 4,3310 | |
25.12.2009 | 0,6318 | 0,8298 | 0,7851 | 0,7262 | -0,1234 | 1,1637 | |
28.12.2009 | 1,2496 | -0,2908 | 0,0836 | 0,9878 | -0,7168 | 2,2091 | |
29.12.2009 | 1,0216 | 1,1500 | 2,3236 | 1,8253 | 0,9336 | -1,2423 | |
30.12.2009 | -1,0639 | -0,6310 | -0,7050 | -0,0865 | 1,1717 | -1,4157 | |
31.12.2009 | -0,1536 | 0,3668 | 0,2280 | -0,6854 | 0,5973 | 1,8640 | |
0,1789 | 0,2475 | 0,1739 | 0,4418 | 0,8059 | 0,3418 |
|
|
:
J | O | ||
=(B4/B3-1)*100 | =(G4/G3-1)*100 | ||
=(B5/B4-1)*100 | =(G5/G4-1)*100 | ||
=(B6/B5-1)*100 | =(G6/G5-1)*100 | ||
=(B7/B6-1)*100 | =(G7/G6-1)*100 | ||
=(B8/B7-1)*100 | =(G8/G7-1)*100 | ||
=(B191/B190-1)*100 | =(G191/G190-1)*100 | ||
=(B192/B191-1)*100 | =(G192/G191-1)*100 | ||
=(B193/B192-1)*100 | =(G193/G192-1)*100 | ||
=(B194/B193-1)*100 | =(G194/G193-1)*100 | ||
=(B195/B194-1)*100 | =(G195/G194-1)*100 | ||
=(J4:J195) | =(O4:O195) |
3. (. . 4.2).
4.2
R | S | T | U | V | W | |
-0,5850 | 9,1720 | 5,6015 | 7,0675 | 0,2037 | 10,2736 | |
7,6057 | 0,6605 | -1,8990 | 6,2371 | 7,9178 | 4,2439 | |
-0,0362 | -3,1881 | -0,0790 | -2,5456 | 4,2598 | -3,3269 | |
0,2986 | -3,8612 | -1,5830 | -2,5779 | -6,2643 | -3,0356 | |
5,1903 | 2,7897 | 3,5547 | 3,8664 | 5,1879 | 3,9891 | |
0,4528 | 0,5823 | 0,6112 | 0,2844 | -0,9294 | 0,8219 | |
1,0707 | -0,5383 | -0,0903 | 0,5459 | -1,5228 | 1,8673 | |
0,8427 | 0,9025 | 2,1497 | 1,3835 | 0,1277 | -1,5841 | |
-1,2428 | -0,8785 | -0,8789 | -0,5283 | 0,3657 | -1,7575 | |
-0,3325 | 0,1193 | 0,0540 | -1,1272 | -0,2086 | 1,5222 |
:
R | W | ||
=J4-J$197 | =O4-O$197 | ||
=J5-J$197 | =O5-O$197 | ||
=J6-J$197 | =O6-O$197 | ||
=J7-J$197 | =O7-O$197 | ||
=J8-J$197 | =O8-O$197 | ||
=J191-J$197 | =O191-O$197 | ||
=J192-J$197 | =O192-O$197 | ||
=J193-J$197 | =O193-O$197 | ||
=J194-J$197 | =O194-O$197 | ||
=J195-J$197 | =O195-O$197 |
4. .
(Z4:AE9),
=((R4:W195);R4:W195)/(192-1)
Ctrl+Shift+Enter .
.
5. , .
(Z12:AE17),
=(Z4:AE9)
Ctrl+Shift+Enter .
,
.
6. , , , .
, (Z19:Z24),
=(J197:O197)
Ctrl+Shift+Enter .
(Z26:Z31) (J198:O198) .
. 4.3.
4.3
, , ,
AB | AC | AD | AE | |
0,0486 | 0,0230 | 0,1372 | 0,3 | |
0,0061 | 2,9532 | 6,7934 |
|
|
:
D | |||
=(J197:O197; (Z12:AE17;Z19:Z24)) | =(J197:O197; (Z12:AE17;Z26:Z31)) | =(J198:O198; (Z12:AE17;Z26:Z31)) | |
=AB20*AD20-AC20^2 | =(AD20*AE20-AC20)/AB23 | =(AB20-AC20*AE20)/AB23 |
7. (. 4.4).
4.4
( )
AB | AC | AD | AE | |
+ | ||||
0,5284 | 6,7934 | 7,3218 | 0,3291 | |
0,7309 | 6,7934 | 7,5243 | -0,0428 | |
0,5137 | 6,7934 | 7,3071 | 0,3826 | |
1,3048 | 6,7934 | 8,0982 | 0,0607 | |
2,3801 | 6,7934 | 9,1735 | 0,1420 | |
1,0095 | 6,7934 | 7,8029 | 0,1285 |
:
D | E | |||
=Z19*$AC$23 | =Z26*$AD$23 | =AB26:AB31+AC26:AC31 | =(Z12:AE17; AD26:AD31) | |
=Z20*$AC$23 | =Z27*$AD$23 | =AB26:AB31+AC26:AC31 | =(Z12:AE17; AD26:AD31) | |
=Z21*$AC$23 | =Z28*$AD$23 | =AB26:AB31+AC26:AC31 | =(Z12:AE17; AD26:AD31) | |
=Z22*$AC$23 | =Z29*$AD$23 | =AB26:AB31+AC26:AC31 | =(Z12:AE17; AD26:AD31) | |
=Z23*$AC$23 | =Z30*$AD$23 | =AB26:AB31+AC26:AC31 | =(Z12:AE17; AD26:AD31) | |
=Z24*$AC$23 | =Z31*$AD$23 | =AB26:AB31+AC26:AC31 | =(Z12:AE17; AD26:AD31) |
8. (. 4.5).
(34:AF34),
=((AE26:AE31);Z4:AE9)
Ctrl+Shift+Enter .
.
4.5
2,7712 | =((AA34:AF34;AE26:AE31))^0,5 |
9. . , , , (. 4.6).
4.6
.. | .. | ||||
1. | 2,7011 | 0,150 | 9. | 3,2485 | 0,550 |
2. | 2,7041 | 0,200 | 10. | 3,3856 | 0,600 |
3. | 2,7276 | 0,250 | 11. | 3,5333 | 0,650 |
4. | 2,7712 | 0,300 | 12. | 3,6902 | 0,700 |
5. | 2,8338 | 0,350 | 13. | 3,8552 | 0,750 |
6. | 2,9144 | 0,400 | 14. | 4,0273 | 0,800 |
7. | 3,0113 | 0,450 | 15. | 4,2057 | 0,850 |
8. | 3,1232 | 0,500 | 16. | 4,3895 | 0,900 |
10. ( ), . 4.2.
. 4.2.
MS Excel ( )
:
:
:
.
1. ( , , ), . MS Excel , . 4.3. , $AP$12 , .. , . (AJ13:AO19); , (AJ24:AO24); (AP13:AP19).
|
|
2. AQ12
=(AJ24:AO24;(AJ4:AO9;(AJ24:AO24))).
3. (AQ13:AQ20)
=(AJ13:AO20;(AJ24:AO24)).
4. ( ). , :
1) $AQ$12, (. . 4.4);
. 4.3.
. 4.4. ( )
2) $AJ$24:$AO$24 (. . 4.4);
3) (. . 4.4):
$AQ$13=$AP$13; $AQ$14<=$AP$14; $AQ$15<=$AP$15; $AQ$16<=$AP$16; $AQ$17<=$AP$17; $AQ$18<=$AP$18; $AQ$19<=$AP$19; $AQ$20=$AP$20.
4) . , . 4.5.
. 4.5. ( )