: Mac I 1 ( 5 Windows (Mac Excel : Excel 2007 9 10 1 4 http://asakura.co.jp/ books/isbn/978-4-254-12172-8/ (1 1 9 1/29 (,,... (,,,... (,,, (3 3/29 (, (F7, Ctrl + i, (Shift +, Shift + Ctrl (, a i (, Enter, Tab (, 14 2 + 3 =2+3 (2 + 3 2 /2 =(2+3ˆ2/2 5/29 log 10 5 =log10(5 =log(5 log 5 =ln(5 =exp(5 2 =sqrt(2 e 5 1 Excel,,,, (2 2/29, (, Kana ( + 4/29 9 14 (,,, 1/2 6/29,, 1/2 7/29 8/29
, ( EX1 9/29 (, 1 Excel 10/29 2 Excel : 11/29 A1 B5 (A1:B5 1 6 2 7 3 8 4 9 5 10 C1 =A1+B1 13/29 ( 1. A6 ( A6 =sum(a1:a5 2. A6 B6:C6 12/29 Ctrl (, a C1, c Ctrl + C C2:C5, Enter Ctrl + C C2 =A2+B2 14/29 (1 (C6= 55 A11:C16 A11: =A1/$C$6 A11 A11:C16 %, $ 15/29 16/29
(2 ( C E1:G6 E1: =A1/$C1 E1 E1:G6 ( 6 E11:G16 E11: =A1/A$6 E11 E11:G16 17/29 H1: 2 A2:A16 H2:H16 B2:F2 I2:M2 I3: =B3/B$3 I3 I3:M16 19/29 (2 A31: 3 A32:A46 B32:F32 1950, 1990, 2000, 2025,2050 B33: =(C3/B3ˆ(1/(C$32-B$32-1 B33 B33:E46 B33:E46 B32:F32 1950-1990 ( 21/29 A1:C5 1994 267 72 2000 283 85 2003 282 89 2006 288 91 23/29 (A11:C16 (A21:C26 A11:C16 A21 ( EX2 18/29 (1 P 0 : P t : t r : (, P t = P 0 (1 + r t r r = (P t /P 0 1/t 1 20/29 3 (X-Y 22/29 A1:B5 2-D,,, ( 24/29
A1:C5 (A1, Shift C5, ( 2 ( 3 ( 25/29 (X-Y A1:C5 A11 A12:A15 1994, 2000, 2003, 2006 A11:B15 (,, ( EX3 27/29 A1:A5,C1:C5 (A1:A5, Ctrl C1 C5 ( 3.21, ( 4 26/29 4 2 Table4.1 H2: I2: H3:H80: 2000 I3:I80: 2000 2050 ( pop2 28/29 5 6 29/29
I 2 ( 5 : Mac Windows (Mac Excel : 5 6 Analysis ToolPak OK Analysis Data Analysis 1/26 5 2/26 1 36 2 4 pop2 Table4.1 H, I 3/26 :, : (, 4/26 2000 Table4.1 ( (A2:I80 1 2000 5/26 2000 5000 ( 2000 50000 6/26 (1, OR, 7/26 (2 2000 1, ( 8/26
(1 2000 5000 1 K2:M4 2000 2000 >=50000 <100000 >=50000 <100000 AND (, OR (, (2 A2:I80 K2:M4 ( K6 9/26 2000 K17:K18 : 1 (A2:I80 (Ctrl + 1 1 K20: =DSUM( 1, 2000, 1 (=DSUM(A2:I80,F2,K17:K18 11/26 6,,, 10/26 : (,, DAVERAGE DMIN DCOUNT DPRODUCT DCOUNTA DSTDEV DGET DSUM DMAX DVAR 12/26 (1 ( ( p.48, Sheet1 Table4.1 A1:I80 Sheet1 A1 Sheet1 F3:F80 1 (, 13/26 (2 2000 K1: 2000 K2: K3: L2: =MIN( 1 223 L3: =MAX( 1 12 7398 6 ( 14/26 (3 K6:K11 10000 50000 100000 500000 1000000 1500000 15/26 16/26
(4 Analysis Data Analysis ( Histogram OK Input Range 1 ( Bin Range K6:K11 ( Output Range K13 ( 17/26 (6 L14:L19 Q3 ( Q9: =SUM(Q3:Q8 R3: =Q3/$Q$9 R3:R8 S3: =Q3 S4: =Q4+S3 S4:S8 S3:S8 T3:T8,, 19/26 (2 (5 N1: 1 2000 N2:T2,,,,, N3:N8 1000, 10000, 50000, 100000, 500000, 1000000 O3:O8 10000, 50000, 100000, 500000, 1000000, 1500000 P3: =(N3+O3/2 P3:P8 18/26 (1 Q2:Q8 ( 2-D ( P3:P8 ( OK OK 20/26 3, N11: 1 2000, O11: (, N12: =LOG10(N3 N13: =LOG10(O3 N13:N18 O12: 0,, S3:S8 ( O13 N11:O18,, (, 2 (,, 21/26 22/26 (1 ( D2:D80 ( GDP A85 F2:F80 (2000 B85 (A85:B163 GDP ( GDP (10 C85: GDP C86: =A86*B86/1E6 C86:C163 23/26 (2 D85: D86: =B86/1000 D87: =D86+B87/1000 D87:D163 E85: E86: =C86 E87: =E86+C87 E87:E163 F85: G85: F86: =D86/D$163 F86:G163 24/26
(3 F85:G163 ( 7 8 3, 25/26 26/26
7 I 3 ( 5 : : 7 8 :,, 1/28 :,, (1 3/28 ( 50% p x p% : { x(m x p% = (m x (m +x (m +1 (m 2 m := n p/100 m : m m (3 5/28 (percentile, quantile, Excel PERCENTILE(, QUARTILE(, 2/28 x 1,x 2,...,x n : n x (1,x (2,...,x (n : (x (1 x (2 x (n, x M : { x((n+1/2 (n x M = x (n/2 +x (n/2+1 (n 2 n = 5 x M = x (3 n = 4 x M = (x (2 + x (3 /2 (2 n = 5 x 25% = x (2 (m = 1.25 x (1 x (2 x (3 x (4 x (5 0% 100% 25% n = 4 x 25% = (x (1 + x (2 /2 x (1 x (2 x (3 x (4 0% 100% 25% 4/28 {x i } 75% (4 6/28 n = 5 PERCENTILE x (1 x (2 x (3 x (4 x (5 0% 100% 25% 60% x 0% = x (1 x 100% = x (5 10 : 15 x 25% = x (2 x 60% = x (3 + (x (4 x (3 60 50 75 50 7/28 8/28
(1 x p% = x L + (x U x L p R 0 R 1 R 0 R 1 : x p% (% R 0 : (% x L : x U : x 50% 9/28 (1 100 (2 80 60 50% 40 20 0 % 3 4 x 50% 4.7 5.7 6.2 x 50% 2 x 50 17.9 50% = 4 + (4.7 4 70.5 17.9 5 6 10/28 (2 Excel ( 2000 (Sheet2 A1:A79 A2:A79 2 ( E1: 11/28 E2:E9, 25%,, 75%,,, F2: =AVERAGE( 2 (=SUM( 2/COUNT( 2 F3: =A21 20 F4: =(A40+A41/2 F5: =A60 59 ( 20 F6: =A79-A2 F7: =(F5-F3/2 12/28 (3 B1: C1: B2: =A2-$F$2 B2:B79 C2: =B2ˆ2 C2:C79 C80: =SUM(C2:C79 F8: =C80/COUNT( 2 (COUNT F9: =SQRT(F8 (=F8ˆ0.5 13/28 (1 (Sheet1!N1:T8 E11 : L12: * L13: =G13*I13 L13:L18 L19: =SUM(L13:L18 : M12: * M13: =(G13-$L$19ˆ2*I13 M13:M18 M19: =SUM(M13:M18 15/28 (4 Excel G3: =QUARTILE( 2,1 G4: =MEDIAN( 2 G5: =QUARTILE( 2,3 G6: =MAX( 2-MIN( G7: =(G5-G3/2 G8: =VARP( 2 G9: =STDEVP( 2 14/28 (2 E21:E24, 25%, 50%, 75% F22: =E14+(F14-E14*(0.25-K13/(K14-K13 F23: =E14+(F14-E14*(0.5-K13/(K14-K13 F24: =E15+(F15-E15*(0.75-K14/(K15-K14 H1: H2:H9 ( ( 3 16/28
8 17/28 X ( Y ( ( ( p.105 p.101 ( ( 1 11 2 2 12 2 18/28 (1 1 GDP 1 GDP (Sheet3 A1:A79 B1: GDP B2: =LOG10(A2 B2:B79 C1:C79 19/28 (2 B1:C79 ( ( 1 GDP, X GDP, Y 20/28 (1 D1: D2: =IF(A2<7500,, (IF(,X,Y X, Y E1: E2: =IF(C2<0.005,,IF(C2<0.014,, F1: F2: 1 D2:F2 D3:F79 21/28 (2 (A1:F79 / A1:F79 A86 OK 22/28 (3 Σ (A86 ( A85: 1 23/28 A102 OK Σ Σ /... ( 24/28
(1 GDP : Excel B1:C79 A111 A190: =AVERAGE(A112:A189 B190 C111: x D111: y E111: C112: =A112-A$190 C112:D189 E112: =C112*D112 E112:E189 E190: =SUM(E112:E189 25/28 (3 Excel (2 A195:A198 x, y,, B195: =STDEVP(A112:A189 ( 7 Excel B196: =STDEVP(B112:B189 B197: =E190/78 B198: =B197/(B195*B196 26/28 11 B201: =COVAR(A112:A189,B112:B189 B202: =CORREL(A112:A189,B112:B189 A201:A202 27/28 28/28
: Mac I 4 ( 5 Windows (Mac Excel : 11 12 Analysis ToolPak OK Analysis Data Analysis 1/37 :,, : ( µ, σ 2,, 3/37 1., 2., (Sheet4 A1:B79 A2:A79 5/37, (2 F9: F10: *1/2 F11: F12: G10: =G2*G6/SQRT(G3 G11: =G1-G10 G12: =G1+G10 11 2 ( 2/37 0 χ 2 α (n 1 f(x ( α ( χ 2 α (n 1, n 1 α ( 100 x 4/37, (1 F1:F6,, (n, (n-1,, G1: =AVERAGE( G2: =STDEV( G3: =COUNT( G4: =G3-1 G5: 95% (= 1 α G6: =TINV(1-G5,G4 ( xxxinv (Excel 2010 xxx.inv 6/37, (1 F15: F16: F17: F18: G16: =CHIINV(1-(1-G5/2,G4 G17: =CHIINV((1-G5/2,G4 G18: =DEVSQ( 7/37 8/37
, (2 F21: ( G21: =VAR( F24: F25: F26: G25: =G18/G17 G26: =G18/G16 9/37 (1 0.7%? (100 2 H 0 : µ = 0.7% H 1 : µ 0.7% I1: I3:I8 1 (,,,, t, : : Maintained hypothesis: ( : ( : ( 1 : 2 : 10/37 (2 J4: 0.7% J5: 0.7% ( <>0.7% J6: 5% J7: =SQRT(G3*(G1-J4/G2 J8: =TINV(J6,G4 J7 < J8 I9: t < I10: J10: 11/37 (1 1.78%? (1950 2000 H 0 : µ = 1.78% H 1 : µ < 1.78% I12:I17 2 (,,,, t, 12/37 (2 J13: 1.78% J14: <1.78% J15: 5% J16: =SQRT(G3*(G1-J13/G2 J17: =TINV(2*J15,G4 J16< J17 I18: t< I19: J19: 13/37 (1 (1% 2 = 0.0001? H 0 : σ 2 = (1% 2 H 1 : σ 2 (1% 2 I22: I24:I30 3 (,,,, chi2,, 15/37 14/37 (2 J25: 0.0001 J26: 0.0001 J27: 5% J28: =G18/J25 J29: =CHIINV(1-J27/2,G4 J30: =CHIINV(J27/2,G4 I31: <chi2< I32: J32: 16/37
2 1 : X 1,X 2,...,X m N(µ 1,σ1 2 2 : Y 1,Y 2,...,Y n N(µ 2,σ2 2 2, µ 1 = µ 2, σ1 2 = σ2 2 17/37 (1 ( 1 ( 2? 1. (a σ 2 = σ 2 1 = σ2 2 H 0 : µ 1 = µ 2 H 1 : µ 1 > µ 2 19/37 (3 Analysis Data Analysis ( t-test (Equal Variances OK Variable 1 Range A91:A142 Variable 2 Range C91:C116 Alpha 0.01 Output Range F90 ( 21/37 (1 2. H 0 : σ1 2 = σ2 2 H 1 : σ1 2 σ2 2 Analysis Data Analysis F-Test (for Variances OK Variable 1 Range A91:A142 Variable 2 Range C91:C116 Alpha 0.05 Output Range F129 23/37 2 1. H 0 : µ 1 = µ 2 vs. H 1 : µ 1 µ 2 ( H 0 : µ 1 = µ 2 vs. H 1 : µ 1 > µ 2 (a σ 2 = σ 2 1 = σ2 2 (b σ 2 1 σ2 2 2. H 0 : σ1 2 = σ2 2 vs. H 1: σ1 2 σ2 2 ( H 0 : σ1 2 = σ2 2 vs. H 1: σ1 2 > σ2 2 18/37 (2 5 A85: A86: (A1:B79 A1:B79 A85:A86 ( A90 A86: C90 20/37 (4 1. (b σ1 2 σ2 2 H 0 : µ 1 = µ 2 H 1 : µ 1 > µ 2 ( Analysis Data Analysis t-test (Unequal Variances OK Variable 1 Range A91:A142 Variable 2 Range C91:C116 Alpha 0.01 Output Range F110 ( 22/37 (2 F141: F142: G141: =FINV(97.5%,51,25 G142: =FINV(2.5%,51,25 11 24/37
12 2 11 1 (, 2 (, 12 ( 1 2 ( 3 ( 2 ( 25/37 k: H 0 : p 1,...,p k (p i : ( k i=1 p i = 1 H 0 k (f i e i 2 i=1 e i (f i :, e i : ν 1 ν 0 (ν 0, ν 1 ( (Rao, 1973, Ch. 6 1 7 18 26 18 22 12 52 19 29 30 78 27/37 (f ij e ij 2 i j e ij χ 2 ((3 1(2 1 29/37 2 : (i = 1,2,...,n 2 (2 2 / 2 26/37 (1? (? H 0 : H 1 : 8 (Sheet3!A85:E90 (Sheet5 A1:E6 28/37 (2 A11: 2 C12: A13: A14: A15: B13: C13: D13: B14: =$E4*B$6/$E$6 B14:D15 30/37 (3 (1 A20: 3 A12:D15 A21:D24 ( ( 11 2, B23: =(B4-B14ˆ2/B14 B23:D24 1. (a σ 2 = σ1 2 = σ2 2 A27:A30 chi2,,,? ( B27: =SUM(B23:D24 H B28: 2 0 : µ 1 = µ 2 = = µ s = µ B29: 5% H 1 : 1 B30: =CHIINV(B29,B28 (Sheet 1 B31: chi2> Sheet5 H1:I79 B32:( 31/37 32/37
(2 K1: K2: M1: N1:O1 H1:I79 K1:K2 M1 M1: ( 33/37 (1 1 GDP ρ? (? H 0 : ρ = 0 H 1 : ρ < 0 R1: GDP R2: r 8 Sheet3!B198 S2 ( Sheet3 B202: =CORREL(B2:B79,C2:C79 35/37 (3 K2 N1 O1 Analysis Data Analysis ( Anova: Single Factor OK Input Range M2:O23 Alpha 0.05 Output Range M30 F>F crit ( 34/37 (2 R3:R7 (n,, (n-2, t, S3: 78 S4: 1% S5: =S3-2 S6: =SQRT(S3-2*S2/SQRT(1-S2ˆ2 S7: =TINV(2*S4,S5 S6< S7 ( 36/37 13 37/37
: Mac I 5 ( 5 Windows (Mac Excel : 13 Analysis ToolPak OK Analysis Data Analysis 1/36 Y i = β 1 + β 2 X 2i + + β k X ki + u i Y X 2,...,X k (i = 1,...,n ( p.122: 6.5. X 1i,...,X ki ( 6.6. E(u i = 0, i = 1,...,n 6.7. i j, Cov(u i,u j = E(u i u j = 0 6.8. V (u i = E(u 2 i = σ 2, i = 1,...,n 6.9. ( E, V, Cov,, ( 3/36 p = 0.2 p = 0.8 L(Y 1,...,Y 5,0.2 = 0.2 4 0.8 = 0.00128, L(Y 1,...,Y 5,0.8 = 0.8 4 0.2 = 0.08192 p = 0.8 L(p Y 1,...,Y 5 = L(Y 1,...,Y 5,p ( 2, p (, Y 1,...,Y 5 5/36 (2 (k = 2 u i i u i = Y i (β 1 + β 2 X i N(0,σ 2 Y i i Y i N(β 1 + β 2 X i,σ 2 X i β 1, β 2, σ 2 Y i f(y i X i, β 1,β 2,σ 2 ( { 6.5 } 1 = exp (Yi β 1 β 2 X i 2 2πσ 2 2σ 2 ( 7/36 6 t, F ( (AIC ( 2/36 p, q = 1 p p (p 5 (,,,, (Y 1 = 1, Y 2 = 1, Y 3 = 0, Y 4 = 1, Y 5 = 1 p, 5 L(Y 1,...,Y 5,p = p Y i (1 p 1 Y i i=1 = p 4 (1 p 4/36 (1 : Y i = β 1 + β 2 X 2i + + β k X ki + u i, i = 1,2,...,n ( p.122, 125: 6.5. X 1i,...,X ki ( 6.6. E(u i = 0, i = 1,...,n 6.7. i j, Cov(u i,u j = E(u i u j = 0 6.8. V (u i = E(u 2 i = σ 2, i = 1,...,n 6.9. ( 6.2.3.b. u i ( 6/36 (3 L(β 1,β 2,σ 2 X 1,...,X n, Y 1,...,Y n n { 1 (Yi β 1 β 2 X i 2 } = exp i=1 2πσ 2σ 2 σ > 0 ( 2πσ 2 logl(β 1,β 2,σ 2 X 1,...,X n, Y 1,...,Y n = n(log 2π + logσ n { } (Yi β 1 β 2 X i 2 2σ 2 i=1 8/36
(4 ( β σ 2 (Xi X(Y i Ȳ β 2 = (Xi X 2 = b 2 β 1 = Ȳ β 2 X = b 1 e σ 2 2 = i β 1, β 2, σ 2 n logl( β 1, β 2, σ 2 Y 1,...,Y n 9/36 (2 F,,, 11/36 (1 2 1. : Y i = β 1 + β 2 X 2i + β 3 X 3i + u i : Y i = γ 1 + γ 2 X 2i + v i γ 1, γ 2 ( 2. : Y i = β 1 + β 2 X 2i + u i : Y i = γ 1 + γ 2 X 2i + γ 3 X 3i + v i γ i V ( γ i V (b i (i = 1,2 13/36 ( (1 : Ŷ i e i (Yi Ȳ 2 = (Ŷi Ȳ 2 + e 2 i TSS (Total = ESS (Explained + RSS (Residual R 2 e R 2 2 = 1 i ( (Yi Ȳ = Ŷ Ȳ 2 2 (Yi Ȳ 2 (0 R 2 1 R 2, 15/36 (1 t, H 0 : β j = a t = b j a s.e.(b j (n k t ( 7 β j : j s.e.(b j : β j b j (= s 2 (X X 1 j 10/36 (3 (F H 0 F = (S 0 S 1 /p S 1 /(n k (p,n k F ( S 0 : H 0 S 1 : H 1 n: k: p: (, 12/36 (2 (2. θ ( θ θ (bias: E( θ θ = E( θ θ θ (Mean Squared Error: MSE = E(( θ θ 2 MSE = (E( θ θ 2 + V ( θ MSE 1 2 14/36 ( (2 : R 2 e R 2 2 = 1 i /(n k (Yi Ȳ 2 /(n 1 (, 2 R 2 s 2 (σ 2? 16/36
AIC, BIC AIC ( BIC (Schwartz Bayes AIC := 2logL + 2v BIC := 2logL + (lognv logl : v : AIC BIC 1 (2 17/36 Y i = β 1 + β 2 X i + β 3 D i + u i D i :, Y i = β 1 + β 2 X i + u i ( Y i = β 1 + β 2X i + u i ( H 0 : β 3 = 0 3 (4 19/36 Y i = β 1 + β 2 X i + β 3 D i + β 4 D i X i + u i,, Y i = β 1 + β 2 X i + u i ( Y i = β 1 + β 2 X i + u i (, H 0 : β 3 = β 4 = 0 F 21/36 : (1 Y i = β 1 + β 2 X i + u i Y i : i X i : log e ( GDP i X i = E(Y i GDP i GDP i X i E(Y i GDP i / GDP i β 2 = E(Y i = GDP 1% Sheet1, GDP,, (Sheet6 A1:D79 ( 23/36 (1 : 0 1 { 0, 1 0 ( i D i = 1 ( i Y i = β 1 + β 2 X i + u i Y i : X i : 3 18/36 2 (3 Y i = β 1 + β 2 X i + β 3 D i X i + u i, Y i = β 1 + β 2 X i + u i ( Y i = β 1 + β 2 X i + u i ( Z i = D i X i H 0 : β 3 = 0 20/36 (5, 4 6.9 ( (X X ( 7 22/36 (2 E1: GDP E2: =LN(B2 E2:E79 Analysis Data Analysis ( Regression OK Input Y Range A1:A79 Input X Range E1:E79 Labels Output Range A85 24/36
: (3 ( Y i = 0.03010 0.002818X i (0.003431 (0.000423 1 GDP 1% 0.28%, H 0 : β 2 = 0 H 1 : β 2 < 0 α: 1% =TINV(2%,78-2 6.666 < t α (n k = 2.376 25/36 : (2 Analysis Data Analysis Regression OK Input Y Range A1:A79 Input X Range E1:G79 Labels Output Range A105 ( Y i = 0.02503 0.001979X 2i (0.005201 (0.000514 + 0.000717X 3i 0.00516X 4i (0.000579 (0.00214 s = 0.006474 (=B111=SQRT(C117/B117 27/36 : (4 2. Y ( α 1% H 0 : β 2 = β 3 = β 4 = 0 F = 19.112 (=E116 F α (k 1,n k = 4.058 (=FINV(1%,4-1,78-4 ( Y 29/36 AIC Kullback-Leibler (1 A 0.7, B 0.5 0.4, B 0.6? 31/36 : (1 Y i = β 1 + β 2 X 2i + β 3 X 3i + β 4 X 4i + u i Y = X 2 = log e ( GDP X 3 = log e ( X 4 = 1, F1: F2: =LN(C2 F2:F79 G1: G2: =IF(D2=,1,0 G2:G79 (IF(,a,b: a, b 26/36 : (3 1. β 2, β 3, β 4 H 0 : β 2 = 0, H 1 : β 2 < 0 H 0 : β 3 = 0, H 1 : β 3 < 0 H 0 : β 4 = 0, H 1 : β 4 > 0 α 5% t 2 = 3.851, t 3 = 1.238, t 4 = 2.408, t α (n k = 1.666 (=TINV(10%,78-4,, 28/36 : (5 3. H 0 : β 3 = β 4 = 0 H 1 : 1 α 5% C97 C117, GDP, F = (S 0 S 1 /p = (0.003474 0.003102/2 S 1 = 4.441, /(n k 0.003102/74 F > F α (p,n k = 3.120 (=FINV(5%,2,78-4 (S 0, S 1, p 30/36 AIC Kullback-Leibler (2 20 :, 20 v.s., ( p i : logp i n ( : E( logp i = p i logp i i=1 32/36
AIC Kullback-Leibler (3 (KL KL: KL = E g log g f = E g logg E g logf g : f :, E g g E g logf ( (i KL 0 (ii KL = 0 f g KL 33/36 AIC Kullback-Leibler (5 KL E g logg KL E g logf g,? n f v E g logf (logl v/n = AIC/( 2 ( AIC KL 35/36 AIC Kullback-Leibler (4 KL, 0.6 KL A = 0.6log 0.6 0.4 + 0.4log 0.7 0.3 0.0226 KL B = 0.6log 0.6 0.4 + 0.4log 0.5 0.5 0.0201, B 34/36 (1983. (2009 EViews. 36/36