×

[PR]この広告は3ヶ月以上更新がないため表示されています。
ホームページを更新後24時間以内に表示されなくなります。

■Excel:重回帰分析(2)
■質的データから量的データを予測する場合■   (参照):統計的データの種類

 「男女,最終学歴,世代から貯蓄残高を予測する場合」「プロ野球で対戦チーム,球場,曜日から観客数を予測する場合」 のように,説明変数(独立変数)がカテゴリーデータ(質的データ,定性的データ)で,目的変数(被説明変数,従属変数)が量的データの場合の回帰分析の方法・・・ここではダミー変数を用いる方法・・・を説明する.

(質的データ→量的データの分析では,「数量化 I 類」が有名であるが,用
いる用語など幾つかの相違点を除けば結果はほとんど同じなので,このペ
ージでは,数量化 I 類をダミー変数による重回帰分析に含めて考える.)

 【要約】 ダミー変数によって数値に直せば,次のいずれの場合も重回帰分析でまとめることができる.
重回帰分析 質的データ,質的データ,・・・→量的データ
量的データ質的データ,・・・→量的データ
量的データ量的データ,・・・→量的データ
○ ダミー変数への変換

 数百人のアンケート調査から右の表1のような結果が得られたとき,これに基づいて男女,最終学歴,年代から貯蓄残高の平均値を予測する場合(ただし右の例は架空データ)

・男女の別のように2つの属性を区別するには,
 一方を0に他方を1にすればよい.表2のように「女」という列を作り、元の男女に応じて0,1を記入する.このとき,男は不要.「女」が0のものは男(もちろん逆でもよい.)

・「高校」「大学」「専門学校」のように3つの属性を区別するには,2つの列を作り該当するものには1,該当しないものには0を記入する.このとき残り1つ(右の例では専門学校)の列は不要.準備した2つの列の値がすべて0のものは残りの属性(専門学校)と分かる.

・一般にn個のカテゴリーデータを区別するにはn−1個の列を準備し,該当するものには1,該当しないものには0を記入すればよい.(n−1個の列が全部0のもので最後の1つの属性を表わす.)

※ 右のようにダミー変数に変換した場合,年代は「もれなく」「重複なく」数え上げただけで,例えば30代が20代と40代の間にあるという意味は失われているまた,年代がすべて0のものは,この分析では50代となり,10代や60代〜は登場しない.
表1 男女 最終学歴 年代 貯蓄残高 表2 貯蓄残高 高校 大学 20代 30代 40代
   高校 20 \200,000   \200,000 0 1 0 1 0 0
   大学 30 \800,000   \800,000 1 0 1 0 1 0
   専門学校 20 \700,000   \700,000 0 0 0 1 0 0
   高校 40 \5,000,000   \5,000,000 1 1 0 0 0 1
   大学 30 \2,000,000   \2,000,000 1 0 1 0 1 0
   大学 50 \8,000,000   \8,000,000 0 0 1 0 0 0
   専門学校 30 \3,000,000   \3,000,000 0 0 0 0 1 0
  高校 40 \7,000,000   \7,000,000 0 1 0 0 0 1
  専門学校 30 \300,000   \300,000 0 0 0 0 1 0
  大学 40 \3,000,000   \3,000,000 1 0 1 0 0 1
○ 予測値の計算方法
ツール→分析ツール→回帰分析で、他の多くの表とともに次の結果が出力される.
  係数
切片 2462500
-3800000
高校 3700000
大学 3375000
20代 -3862500
30代 475000
40代 900000
例えば,女,大学,20代の場合の貯蓄残高の予測値は
貯蓄残高 = 2462500 -3800000×(女)+3700000×(高校) +3375000×(大学) -3862500×(20代)+・・・
に 1 0 1 1 0 0 を代入して\6312,500 となる.
または,
=TREND() に各々範囲を指定する.
注意
1
 ダミー変数への変換において,上の説明において「不要」としたn個目の列(女子以外にさらに男子の列も作ったとき)を作ると分析ツールで分析するときにエラーメッセージが出る.
2
 ダミー変数への変換は,1つの列に0以外の値があるかないかで区別するので,高校=1,大学=2,専門学校=3のような値の割り振りではできない.
3
 ダミー変数への変換は,2進数表記に直しているのではないので,0,1の組合せ3列で8種類のカテゴリーを区別するようなことはできない.
4
 Excelの回帰分析では説明変数は最大16個まで(Excel2002)という制限がある.
○ 量的変数はそのまま使い,質的変数はダミー変数に直すことにより,これらが混在している場合にも重回帰分析による予測が可能となる.

 右の表は,ある駅周辺で検索した木造アパートの家賃の一部である.
 通常「新築」と表示されるものは0年に直した.
 部屋の向きは,「東」「南」「西」の3種類で「北」は実際にはなかった.この場合,ダミー変数に変換するための列は「東」「南」の2つとなる.
 新築アパートの家賃を決める目安をこの表から計算することができる.

※参考
家賃は,「徒歩(分)」「築」と負の相関があるのが自然
部屋は,「西」向きが安いと予想したが実際に分析してみるとそうとはならなかった.(道路との関係や高層マンションとの位置関係などその土地ごとの好みはあるかも.)
家賃(万円) 徒歩(分) 間取り(u) 築(年) 向き
3.4 10 21.4 27
3.4 10 21.4 27
3.9 7 18 16
3.9 7 18 16
4.2 8 20 25
4.2 7 19.5 25 西
4.2 5 18.4 16
4.2 5 16 16
・・・ ・・・ ・・・ ・・・ ・・・
■例と答■
(1)
 右の表は,A,B,Cの3支店での1日当りの食品売上高とする.(架空データ)
 C支店でも「おでん」を取り扱うとすれば,C支店の1日当り「おでん」売り上げ高は幾らになると予測されるか.

(答)
表2のようにダミー変数に変換して予測すればよい.

予測値はのセルに
=TREND(既知のy,既知のx,新しいx)とすれば得られる.

\24,390



 予測値は出るが,その結果が妥当かどうかはまた別の問題・・・そもそも,C支店の「おでん」の売り上げに「おにぎり」や「パン類」の売り上げ高は影響しているのか?むしろ,各支店の総売上高や駅からの距離に関係しないか?説明変数の選び方については別ページで説明する.
表1  売り上げ 支店 食品  表2 売り上げ A支店 B支店 おにぎり 弁当 パン類 飲料
   \56,540 おにぎり    \56,540 1 0 1 0 0 0
   \48,900 弁当    \48,900 1 0 0 1 0 0
   \33,060 パン類    \33,060 1 0 0 0 1 0
   \53,660 飲料    \53,660 1 0 0 0 0 1
   \39,610 おでん    \39,610 1 0 0 0 0 0
   \47,440 おにぎり    \47,440 0 1 1 0 0 0
   \20,660 弁当    \20,660 0 1 0 1 0 0
   \40,430 パン類    \40,430 0 1 0 0 1 0
   \45,210 飲料    \45,210 0 1 0 0 0 1
   \59,750 おでん    \59,750 0 1 0 0 0 0
   \27,970 おにぎり    \27,970 0 0 1 0 0 0
   \21,420 弁当    \21,420 0 0 0 1 0 0
   \16,660 パン類    \16,660 0 0 0 0 1 0
   \5,740 飲料    \5,740 0 0 0 0 0 1
                        
           ?  0 0 0 0 0 0
(2)
 右の表はある駅周辺の木造アパートの家賃である.
 「新築」と表示されているものは0年で表わした.
i) このデータを元に家賃を「徒歩(分)」「間取り(u)」「築(年)」「向き」で表わす回帰式を作れ.
ii) 他の条件が同じのとき,部屋はどちら向きが安いか
iii) 他の条件が同じとき,家賃は1年当り何円下がるか.

(答)
部屋の向きについては右の表のようにダミー変数に変換する.
i) 家賃= 1.895 -0.220×(徒歩)+ 0.199×(間取り) -0.008×(築)-0.206×(南)+ 0.489×(東)
ii) 南向きが安い
iii) 80円
家賃(万円) 徒歩(分) 間取り(u) 築(年) 向き
3.4 10 21.4 27
3.4 10 21.4 27
3.9 7 18 16
3.9 7 18 16
4.2 8 20 25
4.2 7 19.5 25 西
4.2 5 18.4 16
4.2 5 16 16
5.6 9 23.76 0
5.6 9 23.76 0
5.6 5 23.76 100
5.8 9 25.74 0
6.3 9 29.7 0
6.3 5 29.7 100
6.5 6 30.28 9 西
6.5 6 30.28 9 西
7.5 5 32 10
4.2 7 20 25
4.9 12 30 25 西
5.3 5 24.84 0
5.4 5 24.84 0
5.5 5 24.84 0
1 0
1 0
0 1
0 1
0 1
0 0
1 0
1 0
0 1
0 1
0 1
0 1
0 1
0 1
0 0
0 0
0 1
0 1
0 0
1 0
1 0
1 0










  ===>
(3)
 株価については,シーズンストック(季節銘柄)と呼ばれるものがあって,特定季節の前に株価が上がり初め,最盛期を過ぎると株価が下がると言われている.例えば,夏場のビールやエアコンなどがこれに当たるとされている.
 そこで,アサヒビールとダイキン工業の時系列データを分析して,2007年5月と7月の株価を予測してみよう.
(作者の印象)
※ 回帰分析では,元データの範囲(今の場合は年月)から外れた時期を予測するのは元々無理があるが,それは承知の上で・・・

Excelの回帰分析で使える説明変数は16個まで(Excel2002):12か月でダミー変数を11個使うので,年は5個=6年分に絞る.
結果は,
アサヒビール 5月>7月
ダイキン工業 5月<7月
となるが,季節変動といえるほどの差とは思えない.
アサヒビール     ダイキン工業  
日付 終値   日付 終値
2001年1月 1,098   2002年1月 2,030
2001年2月 1,140   2002年2月 2,250
2001年3月 1,195   2002年3月 2,270
2001年4月 1,384   2002年4月 2,330
2001年5月 1,285   2002年5月 2,340
2001年6月 1,399   2002年6月 2,195
・・・ ・・・   ・・・ ・・・
例えば次のようにダミー変数に変換する.
終値 2002 2003 2005 2006 2007 1月 2月 3月 4月 ・・・
1,080 1 0 0 0 0 1 0 0 0 ・・・
1,035 1 0 0 0 0 0 1 0 0 ・・・
1,065 1 0 0 0 0 0 0 1 0 ・・・
1,183 1 0 0 0 0 0 0 0 1 ・・・
1,183 1 0 0 0 0 0 0 0 0 ・・・

○=== メニューに戻る

◇このページの内容について,考え方の間違い,計算間違い,著作権上の
問題点などお気づきの点がございましたら までご連絡ください.