Excelにおける回帰分析（最小二乗法）の手順と出力

Size: px

Start display at page:

Download "Excelにおける回帰分析（最小二乗法）の手順と出力"

のぶみつらぶり
7 years ago
Views:

Microsoft Excel Excel 1 1 x y x y y = a + bx a b a x 1 3 x 0 1 30 31 y b log x α x α x

1 Microsoft Excel Excel 1 1 x y x y y = a + bx a b a x 1 3 x y b log x α x α x β 4 version Website: [email protected] 1

[Excel ] Excel y x Excel (http://keijisaito.info/arc/excel/ols data.

2 Excel Excel.1 Excel Excel 003 [ ] [ ] 1 [ ] [OK] Microsoft Office Excel CD. [Excel ] Excel y x Excel ( data.xls) y x α x β 1 [ ] y x F9 [ ] [ ] [ ] [ ].3 [ ] [ ] [ ] [OK] [ ] 1 Excel 007 Microsoft Office [Excel ] Excel [ ] [ ] Excel 007 [ ] [ ]

[Excel ] Y 1 X 3 Y $B$10:$B$60 X $C$10:$D$60 $K$10 4 5 6 [OK] 概要回帰統計重相関 R 0.951759 重決定 R 0.905845 補正 R 0.901838 標準誤差 8.616 観測数 50 分散分析表自由度変動分散観測された分散比有意 F 回帰 33538.4 16769.1 6.088953 7.

3 [Excel ] Y 1 X 3 Y $B$10:$B$60 X $C$10:$D$60 $K$ [OK] 概要回帰統計重相関 R 重決定 R 補正 R 標準誤差観測数 50 分散分析表自由度変動分散観測された分散比有意 F 回帰 E-5 残差合計係数標準誤差 t P- 値下限 95% 上限 95% 下限 95.0% 上限 95.0% 切片 x α E x β E [Excel ] 3 Excel 16 4 [ ] 5 $ Excel $ $ : 6 $K$10 3

4 3 Excel [ ] y n i y i x y = a + bx y = a 1 + bx a i j x ij b b 1 j b j y ŷ y k i ŷ i (1) ŷ i = b 1 + b x i + b 3 x i3 + + b k x ik (1) (1) ŷ i y i e i 7 e i i e i ŷ i () y i y i = ŷ i + e i () j x j e n n x ij e i = 0 (3) [ ] V Y 6 F9 0 0 n 0 7 e ϵ e ϵ 8 e i = (y i ŷ i ) = {y i (b 1 + b x i + + b k x ik )} e i = 0 e i b j b j =0 {x ij e i } = 0 = {x ij (y i b 1 + b x i + + b k x ik )} = {x ij e i } 4

(1) b 1 1 (3) 1 e i = e i = 0 (4) e 0 e ē e e i ē 0 (1) e i ŷ i e i = [b 1 + b x i + + b k x ik ]e i = [b 1 e i + b x i e i + + b k x ik e i ] n = b 1 e i + b n x i e i + + b k n x ik e i = b 1 0 + b

5 (1) b 1 1 (3) 1 e i = e i = 0 (4) e 0 e ē e e i ē 0 (1) e i ŷ i e i = [b 1 + b x i + + b k x ik ]e i = [b 1 e i + b x i e i + + b k x ik e i ] n = b 1 e i + b n x i e i + + b k n x ik e i = b b b k 0 = 0 (5) ŷ e 0 0 n 0 [ x e 0] 3. () y i = ŷ i + e i n y i = ŷ i + e i (6) (4) (6) e i 0 (6) y i = ŷ i (7) y ŷ n y y y y = ŷ ŷ y = ŷ = b 1 + b x + b 3 x b k x k (8) (8) [ y ] [ ŷ ] [ x ] y x [ ] y B6 x B67 5

6 [ x y ] (8) y = ŷ = b 1 (9) b 1 ȳ (9) b 1 (y i b 1 ) b 1 = y (10) 9 (10) e i b 1 y 3.3 (x, y) = (0, y 1 ) (1, y ) (1, y 3 ) (1, y 4 ) 4 x = 0 ŷ x=0 x = 1 ŷ x=1 x = 0 (x, y) = (0, y 1 ) 1 ŷ x=1 (0, y 1 ) (1, ŷ x=1 ) ŷ x=0 y 1 ŷ x=1 (10) b 1 ŷ x=1 ŷ x=1 x = 1 y ŷ x=1 = y +y 3 +y 4 3 (0, y 1 ) (1, y +y 3 +y 4 3 ) ŷ = y 1 + ( y + y 3 + y 4 3 y 1 )x (11) (11) x 1 y ( y +y 3 +y 4 3 y 1 ) (y y 1 ), (y 3 y 1 ), (y 4 y 1 ) (11) 9 (y i b 1 ) b 1 = ( y i nb 1 ) =0 b 1 = y 6

7 4 Excel Excel R ŷ y () y i = ŷ i + e i () y ŷ () (4) e e i e 0 (7) y = ŷ (4) ē = 0 () y y i y = ŷ i y + e i [y i y] = [ŷ i y + e i ] = [ŷ i y] + [ŷ i y]e i + e i (1) (1) [ŷ i y]e i (4) (5) 0 (1) [y i y] = [ŷ i y] + e i (13) (13) [y ]=[ŷ ]+[e ] 11 y ŷ e 1 (13) (13) 1 = [yi y] [yi y] = [ŷi y] [yi y] + e i [yi y i ] (14) (14) y ŷ 13 (14) R y ŷ 10 R R R 11 e = 0 e i e e i 1 ŷ e y 13 R 14 (9) [ŷ i y] = [y y] = 0 0 7

8 R = [ŷi y] [yi y] = 1 e i [yi y] = = 1 (15) 4. R R R R 1 e R = 1 [yi i y] = 1 = (16) 4.3 R 0 e i 0 e i 0 e i (15) e i [y i y] k (17) R = 1 e i (n 1) [yi y i ] (n k) = 1 (1 ) ( 1) ( ) 1 18 (17) (17) 15 Excel 0.5 Excel = ˆ 0.5 Excel =sqrt( ) Adjusted-R Adj-R 18 n k (17) 1 8

9 R 4.4 (4) ē = 0 0 ±1 ± ± e i e 1 e i n 1 e i n 19 0 e i 0 0 e i = 0 n k (n k) 1 e i (n k) = = e i (n k) = ( ) e i (n k) = ( ) (18) (19) 4.5 F 0 19 n (n 1) ( F9 0 1 (17) [ ] [ ] 3 (standard deviation) (standard error) 4 s s 9

10 5 e i (10) b 1 = y [y i y] y [y i y] e i (13) [ŷ i y] [ ] [ŷ i y] (k 1) 1 (18) 6 (0) (ŷi ȳ) = (k 1) e i (n k) = (0) = = (ŷi ȳ) (k 1) e i (n k) = (1 ) (ŷi ȳ) (yi ȳ) (yi ȳ) (n k) e i (k 1) ( ) ( 1) (1) (1) (0) (1) 0 F 7 F 0 [ ] P1 F [7.68E-5] 7.68 (0.1) (0.1) 5 5 Excel F ANOVA(analysis of variance) F F F P 6 [ ] N1 [ ] N [ ] 7 F F F (k 1, n k) F 8 Excel F [=FDIST(, (k 1), (n k)]=[fdist(6.0883,,47)] 7.68E-5 10

11 5 Excel Excel [ ] 係数標準誤差 t P- 値下限 95% 上限 95% 下限 95.0% 上限 95.0% 切片 x α E x β E [ Excel ] [3.3 ] 0 5. [1] [] 30 [3] [4] 31 [] [4] [ ] F9 [] F4, F5 [3] B7 [4] B6 [-1 1] x α x β 0 ± ±1 Excel 0 Excel 11

12 5.3 t () t t = () t () t 3 t 0 t t t 0 t 0 t.5% 5% 標準正規分布とt 分布 0.5 標準正規分布 t 分布 : 自由度 0 t 分布 : 自由度 5 t 分布 : 自由度 % t 33 t 95% t 34 t 5% t 5% t 0 t % t t.5 1% t 35 t 5%.5% 36 t t t 33 ( normal.xls) F9 95% ± 34 t 0 35 t t Excel [=TINV(, )] 5% 47 [=TINV(0.05, 47)] % (significance level) 1

13 95% t t 5% t t 0 37 t 5.4 P t 5% t t t t t P 38 P 0 t 100 t.13 5% t 1% t 5% 1% t P % 39 5% t P ,, 95% % 37 [5. ] [1] 0 t 38 p P 39 Excel [=TDIST(t,, =)] P [=TDIST(.13, 100, )] % 41 95% [ ] R3 13

14 6 (3) i = b 1 + b i + b 3 i + b 4 i + e i (3) 6.1 (3) 0mm 1mm 100mm 101mm (3) 4 43 [ ] y b j x j 1 y x j b j x j 1% y 46 (3) b 4 1 (4) b 4 1% b 1 + b i + b 3 i + b 4 log( i ) (4) [ ] A B (5) b 1 + b i + b 3 i + b 4 i + b 5 i (5) d log(y) = 1 y dy y y b y j x 45 Excel natural logarithm ln( ) Excel log( ) x j y 14

15 A 0 B 1 Excel (5) A b 1 B b 1 + b 5 (6) b 1 + {b + b 6 i } i + b 3 i + b 4 i (6) (6) (5) i A b B b + b 6 (6) Excel Excel (7) 0 b 1 + b ia + b 6 ib + b 3 i + b 4 i (7) (7) A b B b 6 b 6 = b + b 6 (6) (7) A B C 3 47 [ ] j x j y ŷ e ŷ e ŷ e ( i ) ( i i ) ( i ) ( 傘の販売本数 ) 切片と一乗項のみでの回帰分析グループ A の標本グループ B の標本グループ横断の回帰線 (1 日の降水量 ) ( 傘の販売本数 ) 二乗項と定数項ダミーを追加した回帰分析グループ A の標本グループ B の標本グループ A の回帰線グループ B の回帰線 (1 日の降水量 ) 47 ( wage data.htm) 15

16 6. (3) (3) 48 (3) (3) (3) b 4 b [6.6 ] Excel 16

17 6.4 Excel e ϵ (8) 0 σ ϵ N(0, σ ) (8) (8) [ ] [ ] ϵ 55 e ϵ e x j ŷ 56 [6.1 ] [6. ] 57 ( 推定エラー ) 均一分散 ( 推定エラー ) 不均一分散 ( 推定エラー ) 回帰分析の式に問題 ( 説明変数当てはめ値 ) ( 説明変数当てはめ値 ) ( 説明変数当てはめ値 ) (19) σ (19) ϵ (e 56 t e t 1 ) 57 Excel e t 17

18 Heckit 58 ( 傘の販売本数 ) 60 潜在的な標本を含めた回帰分析 ( 傘の販売本数 ) 60 観測できる標本での回帰分析観測できる標本潜在的な標本全標本での回帰線 (1 日の降水量 ) 観測できる標本観測できる標本での回帰線 (1 日の降水量 ) y x 59 ( 傘の販売本数 ) 60 正確な説明変数での回帰分析 ( 傘の販売本数 ) 60 不正確な説明変数での回帰分析正確な説明変数正確な説明変数での回帰線 (1 日の降水量 ) 30 0 不正確な説明変数不正確な説明変数での回帰線 (1 日の降水量 ) 58 Excel 59 18

スライド 1

スライド 1 データ解析特論重回帰分析編 2017 年 7 月 10 日 ( 月 )~ 情報エレクトロニクスコース横田孝義 1 ( 単 ) 回帰分析単回帰分析では一つの従属変数 ( 目的変数 ) を一つの独立変数 ( 説明変数 ) で予測する事を考える具体的には y = a + bx という回帰直線 ( モデル ) でデータを代表させるこのためにデータからこの回帰直線の切片 (a) と傾き (b) を最小