Excel... 3 Excel... 3 Excel... 5 Excel... 6 Excel... 8 Excel... 8... 9... 9... 11... 12... 12 Excel... 12... 13... 14 Excel... 15... 17... 18 Excel... 19... 19... 21... 24... 25... 27... 29 X 2... 32... 34... 36... 38... 40... 41... 41... 45 Excel... 49 Excel... 56 2
Excel Excel Excel Microsoft Excel Windows Excel Excel Excel5.0 Excel95 Excel97 Excel2000 Excel Excel Excel Excel 400 Excel Excel Excel Excel97 14 Excel 100 3
FoxPro Access Clipper Sybase Excel Excel Excel Excel Excel Excel Visual Basic Excel Excel Excel Excel 4
Excel Excel Excel Excel Excel 255 Excel Excel97 Excel Excel97 Excel97 0ffice97 window95 Excel windows Excel97 1 CD R0M 0ffice 97 2 Windows SETUP.EXE 3 Office Office Excel Excel Microsoft Excel, 5
-1 Excel Excel Excel Excel Excel -1 Excel Excel -1 Excel -1, Microsoft Excel-Book1 Microsoft Excel Excel Book1.xls Excel 6
Excel Excel 1 Excel 2-1 5 Excel 16 sheet Sheetl 1 65536 A IV 7
256 65536 Excel Excel Sheet1, Sheet2 Excel 3 Excel Excel [Caps Lock CAPS Excel Excel Excel A1 A2 A1 A1 ( 8
) A2 A1 A2 A1 A2 A2 A2 A1 Excel Excel Excel Excel Excel, Excel Excel Excel Excel Tab Excel Excel / / - - true ( ) false ( ) Excel Excel Excel Excel 9
2 10 9 Excel 2E+9 Excel Excel A1 B1 A1 B1 C1 C1 =A1+B1 =SUM(a1:b1), C1 A1+B1 C1 A1 B1 Excel, ( ) ( )*( ) ( ) ( )^( ) SUM( CORREL( ) STDEV( ) Excel Excel 1 1 Excel A 1-2 -2 2 10
3 2 1 2 Excel Excel $ A1 B1 C1 =A1+B1, A1 B1, C2 =A2+B2 F100, =D100+E100 C1 =$A$1+$B$1 C1 =A$1+B$1, F100 =D$1+E$1 $ Excel 11
Excel Excel FOXPRO DBF lotus1-2-3 Excel Excel Excel Excel, Excel Excel Excel Excel 12
Excel Excel Excel SUM PMT TRUE FALSE #N/A Excel 1 2 3 4 Excel / ( ) 13
Excel 95% 10 5% (X-10, X +10 K / k / k,, Excel A1 5 X ( S / n ) (Medium) (Mode), (S) 2 ), (Max-Min), (Min), (Max), n 14
50,, 1 2 3 4 Excel Excel Excel Excel Excel 5 Excel 15
Excel -3-3 x-y x-z 1 A2:C12 2 3 4 Excel 5. x-y x-z 1 X x,y 2 X x,z Excel 5 6. X-Y X-Z X X Y Y/Z 7. -4 16
-4-4 Excel Excel -5-5 1-5 2 Microsoft Excel 3 Excel 4 17
- - -6 5-7 -7-7 18
1 2 3 4 / -1 Excel Excel Excel BINOMDIST BINOMDIST BINOMDIST 10 2 Excel 0.3 1-8 Excel 19
-8 2-9, B7 F7-9 3 B7 F7 F13-10 20
-10 4 B7 B13 5 6. (X) A7 A13 X 7. X Y : -10 Excel BINOMDIST BINOMDIST TRUE BINOMDIST FALSE CRITBINOM 1 CRITBINOM BINOMDIST P(X<=x) 2 CRITBINOM(trials,probability_s,alpha) Trials Probability_s Alpha 3 : CRITBINOM(6,0.5,0.75) 4 0.5, 6 4 0.75 21
HYPGEOMDIST 1 HYPGEOMDIST HYPGEOMDIST HYPGEOMDIST 2. HYPGEOMDIST(sample_s,number_sample,population_s, number_population) Sample_s Number_sample Population_s Number_population 3. 20 8 12 4 HYPGEOMDIST(1,4,8,20)= 0.363261 NEGBINOMDIST 1 NEGBINOMDIST NEGBINOMDIST n 2 : NEGBINOMDIST(number_f,number_s,probability_s) Number_f: Number_s: Probability_s: 3 5 0.3 5 10 NEGBINOMDIST(10,5,0.3)= 0.06871 POISSON: 1 POISSON n 2 POISSON(x,mean,cumulative) 22
X Mean Cumulative cumulative TRUE POISSON 0 x 0 1 FALSE x 3 POISSON(2,5,FALSE)=0.084224 5 2 0.084224 NORMDIST 1 NORMDIST NORMDIST 2. NORMDIST(x,mean,standard_dev,cumulative) X Mean Standard_dev Cumulative cumulative TRUE NORMDIST FALSE 3 NORMDIST(6,5,2,0) 5 2 X=6 NORMDIST 60 50 4 1 50 4 X=60 NORMSDIST 1 NORMSDIST 2 NORMSDIST(z) Z 3 NORMSDIST(0)=0.5 NORMSINV 1 NORMSINV 2 NORMSINV(probability) 23
Probability 3 NORMSINV(0.5)=0 t TDIST: 1 TDIST student t T t 2 TDIST(x,degrees_freedom,tails) X Degrees_freedom Tails tails = 1 TDIST tails = 2 TDIST 3 TDIST(1.96,60,2)=0.054645 Excel Rand() 0 1 Rand() RAND() -11 10 5-11 1 B2 =RAND() 2 B2 B11 B1 RANDOM 3. B2 B11 24
4. A2 B11 5. RANDOM -12 A2 A6 5-12 1. Excel RANDBETWEEN(TOP,BOTTOM) RANDBETWEEN(TOP,BOTTOM) TOP BOTTOM 2. RAND() 0 1 Excel 0-1 3. Excel Excel AVERAGE STDEV T TINV 25
Excel 1-13 2. A4:B6,A8:B8 A10:B15 ( CTRL ), -13 3. 0.95, -13 4. D1:D11, -14 26
-14 Excel Excel Excel Excel Excel NORMSDIST IF Excel 1-15 2. A3:B4,A6:B8 A10:A11,A13:A15 A17:B19, 27
-15 2-16 3 C1:C11, -16 28
-16-16 Ho 5%, 35 35 31.4 0.020303562, Excel 9 18-17 29
-17 1. t- : -18-18 2. 1 $B$1:$B$10 2 $D$1:$D$10 0 0.05, -19-19,Df 2 t 30
-19 P(T<=t) t t P(T<=t) t t t 5% Excel 1 t- student t- t- t- 2 t- t- 3 z - z - 31
X 2 X 2-20 X 2-20 1 A3:C4, 2. -21 D6 D7:D11 D12-21 3. -22 E6 E7:E11 E12 32
-22 4. X 2-23 F6 F7:F11 X 2 E12 X 2, X 2 E13 F12 F13, -23 5. -24 A14 B20 X 2 X 2 = - -1=5-2-1=2-24 33
-25-25 Excel, Excel Excel Excel -26 34
-26 1-27 B10 =B$7*$F4/$F$7 Excel sum -27 2 X 2 A15 B15 CHITEST B4:E6 B10:E12 1.3E-07, -28-28 35
3. X 2-29 B20 =(B4-B10)^2/B10 X 2 A24 B24-29 4. -30 CHIINV =( -1) *( -1)=(3-1)*(4-1)=6-30 B15 B24 1.3E-07 0.00000013 0 t- -31 32 4 36
-31 1. : 2. -32-32 -33 SS DF MS SS DF F F F 1 37
-33 F F crit 1.67761<2.94668, P-value 19.442%, -34 1987 1997-34 38
1 Y X Excel 16 95% anova y r 2 A1 2 X $B$1:$B$12, Y $C$1:$c$12, -35 R 2 F t Y=8.46433*X-18.288 39
-35-3 x,y,z 1 2 3 4-36 40
-36 x,y,z x,y 0.929167, x,y 3-37 1999 4 1 20 1-37 C1 Z C2 = -AVERAGE( ) C2 Excel C3 C22 2 E1 F1 Lag ac, 41
-37 E2 E9 1 8 F2 =SUMPRODUCT(OFFSET(C$2,0,0,20-E2), OFFSET(C3,0,0,20-E2)) /VAR($C$2:$C$21)/19 F2 F3:F9-38 -38 2 Excel Yule-Walker 8 8 Yule-Walker H2:O9-39 H2:O9 Excel 42
8 1i 8i H12 I12:I13, J12:J14, 8 MMULT(MINVERSE(OFFSET(H2,0,0,1,1)),OFFSET(F2,0,0,1)) MMULT(MINVERSE(OFFSET(H2,0,0,2,2)),OFFSET(F2,0,0,2)) MMULT(MINVERSE(OFFSET(H2,0,0,3,3)),OFFSET(F2,0,0,3)) MMULT(MINVERSE(OFFSET(H2,0,0,4,4)),OFFSET(F2,0,0,4)) MMULT(MINVERSE(OFFSET(H2,0,0,5,5)),OFFSET(F2,0,0,5)) MMULT(MINVERSE(OFFSET(H2,0,0,6,6)),OFFSET(F2,0,0,6)) MMULT(MINVERSE(OFFSET(H2,0,0,7,7)),OFFSET(F2,0,0,7)) MMULT(MINVERSE(OFFSET(H2,0,0,8,8)),OFFSET(F2,0,0,8)) 1. Excel =MMULT(MINVERSE(OFFSET(H2,0,0,2,2)), OFFSET(F2,0,0,2)) CTRL+SHIFT+ Excel 2. 1 pac 8-39 H12:O19 F12:F19 8 43
-39 3 1.96 1.96 k>1 (, ) (-0.438,0.438) 20 20 K>1 AR(1) C2:C20 D3:D21 D1 Z(-1) -40 AR(1) 44
-40-41 AR(1) -41 ^ Z =1.06284*Z(-1), - =1.06284( - ) (T) (S) (I) 45
Excel -42 A1 B13 1996 1998 ( ), 1999 1 C3 =AVERAGE(B2:B5) C4:C11 2. D4 =AVERAGE(C3:C4) D5:D11 3 E4 =B4/E4 E5:E11 4. F4 =AVERAGE(E4,E8) F5:F7-42 5. 1 G4 =F4/AVERAGE($F$4:$F$7) G5:G7 G4:G7 G4:G7 4 G2:G13 6. H2 =B2/F2 H3:H13 H 46
7. I 1 12-42 -43-44 B35 B36-43 47
-44 8. G14:G17 B14 =($B$35+I14*$B$36)*G14 B15:B17 B14:B17 1999-45 -45 48
Excel AVEDEV AVERAGE AVERAGEA BETADIST BETAINV BINOMDIST CHIDIST CHIINV CHITEST TRUE FALSE Beta Beta beta probability = BETADIST(x,...) BETAINV(probability,...) = x beta BINOMDIST BINOMDIST X 2 X 2 X 2 X 2 X 2 probability = CHIDIST(x,?) CHIINV(probability,?)= x CHITEST X 2 X 2 49
CONFIDENCE CORREL COUNT COUNTA COVAR CRITBINOM DEVSQ EXPONDIST FDIST FINV array1 array2 COUNT COUNTA CRITBINOM EXPONDIST EXPONDIST F F p = FDIST(x, ) FINV(p, ) = x F F 50
FISHER FISHERINV FORECAST FREQUENCY FTEST GAMMADIST GAMMAINV GAMMALN GEOMEAN GROWTH HARMEAN x Fisher Fisher y = FISHER(x) FISHERINV(y) = x x y x y x FREQUENCY FREQUENCY F F 1 2 P = GAMMADIST(x,...) GAMMAINV(p,...) = x (x) GEOMEAN x y GROWTH x y GROWTH x y 51
HYPGEOMDIST INTERCEPT KURT LARGE LINEST HYPGEOMDIST HYPGEOMDIST x y y known_x known_y y INTERCEPT 0 k LARGE LOGEST LOGINV LOGNORMDIST MAX MAXA x ln(x) mean standard-dev p=lognormdist(x,...) LOGINV p,... =x x ln(x) mean standard_dev TRUE FALSE MAXA 52
MINA MEDIAN MIN MINA MODE TRUE FALSE MEDIAN MODE NEGBINOMDIST probability_s NEGBINOMDIST number_s number_f NORMDIST NORMINV NORMSDIST NORMSINV 0 1 0 1 PEARSON PERCENTILE PERCENTRANK Pearson r -1.0 1.0-1.0 1.0 K 90 PERCENTRANK 53
PERMUT POISSON PROB QUARTILE RANK RSQ SKEW SLOPE SMALL STANDARDIZE STDEV STDEVA upper_limit x _range lower_limit QUARTILE 25% known_y's known_x's Pearson REARSON R y x known_y's known_x's k mean standard-dev mean 54
STDEVP STDEVPA STEYX mean TRUE FALSE mean mean y x y TDIST TINV TREND TRIMMEAN TTEST VAR VARA VARP VARPA WEIBULL ZTEST - t T t - t y known_y's known_x's new_x's y TRIMMEAN - t TTEST TRUE FALSE TRUE FALSE z P Z x 55
Excel F - F - F- t - t - t - t - t - t- t - t - t - t t - t - t - z - z - 56
( www.riskage.com) 57 FFT
( www.riskage.com) 58 20 A B- a Anova (anova) t- Anova anova Anova anova t-
[1] [ ]MICHAEL R.MIDDLETON, Excel 1997 [2],,,1997 [3] 1998 59