Excelによるデータ分析

Transcription

1 Excel による データ分析 多変量解析編 矢野佑樹 2013/07/27

2 Excel で学ぶデータ分析 ( 多変量解析編 ) 多変量解析では, 気温とアイスの売上個数の関係や, 最寄り駅からの距離と来店者数の 関係など,2 つ以上の変数を一度に分析します. では, 早速 2 つのデータ間の関係を Excel によって分析しましょう. < 散布図と相関 > 例 1. あるアイスクリーム販売店では,1 日あたりの適性在庫量を確保するために, アイ スクリーム販売量と最高気温の関係を知りたいと考えています. 実際にデータを集 めてみると, 以下の表のようになりました. 日付 最高気温 ( 度 ) 販売量 ( 個 ) 8 月 1 日 月 2 日 月 3 日 月 4 日 月 5 日 月 6 日 月 7 日 月 8 日 月 9 日 月 10 日 月 11 日 月 12 日 月 13 日 月 14 日 散布図を作成する 1. ice_cream.xlsx というファイルを開く. 2. C2~D16 までの範囲を選択し, 挿入タブの散布図の中にある 散布図 ( マーカーのみ ) を選択する. グラフを選択したまま右クリックをして, グラフの移動 を押し, 新しいシート を選択する. 3. グラフのレイアウトから, グラフタイトルと軸ラベルを追加する. グラフタイトルに 最高気温とアイスクリーム販売量の散布図, 縦軸に 販売量 ( 個 ), 横軸に 最高気温 ( 度 ) と入力する. 4. 軸の目盛の最小値を指定する場合は, 軸を選択し, 軸の書式設定 軸のオプション で, 最小値に固定値 ( 例えば横軸であれば 20) を指定する. 1

3 フォントサイズなどを調整すると, 以下のような散布図が出来上がったと思います. 散布図から, 最高気温が高ければ販売量も多くなるという関係があることがわかります ( 正 の相関 ).2 変数間の関係を分析したいときは, まず散布図を描いて視覚的にデータの特徴 を捉えることをお勧めします. 相関係数を計算する 1. 分析ツール の 相関 をクリックする. 2. C2~D16 までの範囲を選択し, 新規ワークシートに相関係数を出力する. データ方向は 列 を選択し, 先頭行をラベルとして使用 にチェックを入れる. 以下の表が, 新しいワークシートにできたと思います. 最高気温 ( 度 ) 販売量 ( 個 ) 最高気温 ( 度 ) 1 販売量 ( 個 ) 相関係数は約 0.97 ですので, 最高気温とアイスクリームの販売量の間には 強い正の相関 2

4 があると判断できます. 一般に, 相関係数と関連性の強さは以下の表のようになっていま すので, 参考にしてください. 相関係数 関連性の強さ ( 正の相関 ) 相関係数 関連性の強さ ( 負の相関 ) 0~0.2 ほとんど関連なし ( 無相関 ) -0.2~0 ほとんど関連なし 0.2~0.4 やや関連あり -0.4~-0.2 やや関連あり 0.4~0.7 かなり関連あり -0.7~-0.4 かなり関連あり 0.7~1.0 強い関連あり -1.0~-0.7 強い関連あり 演習問題 1 以下の表は, あるドラッグストアチェーンの店舗の売上高, 店舗面積, 駐車場面積のデータを示しています. 新たな店舗を出店する場合, 店舗面積と駐車場面積のどちらを広くした方がよいでしょうか. 店舗面積と売上高, 駐車場面積と売上高 の散布図を作成し, それぞれの相関係数を求めて分析してください.drug_store.xlsx 店舗 店舗面積 (m 2 ) 駐車場面積 (m 2 ) 売上高 ( 万円 )

5 < 単回帰分析 > 散布図及び相関係数の値から,2 種類のデータ間の関係の強さがわかりましたが, それらの関係を数式で表し, 予測などに用いることはできないでしょうか. そこで, データの集まりについて, できるだけ真ん中を通る直線を描くことを考えます ( データを代表する一本の直線 ). この直線のことを回帰直線と呼びます. この手法は実証分析などでもよく用いられます. 直線の引き方 x と y という 2 つの変数間の関係を考えます. ここで, x の値が y の値に影響を与えるという因果関係を想定します. 例えば, 最高気温とアイスクリーム販売量の例では, 最高気温 ( x ) がアイスクリームの販売量 ( y ) に影響を与えると考えられます. 下の x と y の散布図の中に, データのできるだけ真ん中を通る直線が描かれているのがわかると思います. この回帰直線は, 直線と観測値の y 軸方向のずれ ( つまり, 観測値から直線までの縦のずれ ) を全体として最小にするように描かれています. より専門的には, それぞれの観測値と直線の y 軸方向のずれの二乗をすべて足し合わせたもの ( 平方和 ) を最小にするように直線の式が決められますが, ここでの詳しい解説は省略します ( 最小二乗法 ). 4

6 この直線の式を一般的に表すと, y a bxと書くことができ, データから a と b の値を最小二乗法によって求めることで, 回帰直線の式を導くことができます. 計算は Excel が自動的に行ってくれます. では実際に,ice_cream.xlsx のデータを用いて回帰分析を行ってみましょう. 回帰直線を描く 1. 散布図でデータ系列を選択する. 2. 右クリックで 近似曲線の追加, もしくは グラフツール の レイアウト から その他の近似曲線オプション を選ぶ. 3. 近似曲線のオプションで, 線形近似 を選択し, グラフに数式を表示する と グラフに R-2 乗値を表示する にチェックを入れる. 以下のようにデータを代表する一本の線形の近似曲線 ( 回帰直線 ) が描かれたと思います. 数式は, 販売量 ( 個 )= 最高気温 ( 度 ) であることを表していま す. また, R 2 ( 決定係数 ) は直線の当てはまりの良さを表しており,0~1 の間の値を取り ます.1 に近ければ近いほど, 直線がよく当てはまっていることがわかります. 逆に 0 に近 ければ, 直線の当てはまりがよくないと言えます. この場合 なので, 直線はよく当 てはまっているといえるでしょう. 5

7 では, この回帰式を用いて予測をしてみましょう. 例えば, 気温 35 度のときの販売量を 予測したいのであれば, 上の式に 35 を代入するだけで予測値が得られます. 販売量 ( 個 )= = = ( 約 140 個 ) このように回帰式を予測に用いることができますが, 分析に用いたデータの範囲からあま りにも離れている値を予測に用いることはお勧めできません. 回帰分析を実行して信頼性を確かめる ( より詳しい分析 ) 1. 分析ツール の 回帰分析 をクリックする. 2. 入力 Y 範囲で D2~D16 までを選択, 入力 X 範囲で C2~C16 までを選択する. 3. ラベル にチェックを入れ, 出力オプションでは 新規ワークシート を選択し, 残差 と 標準化された残差, 残差グラフの作成 にチェックを入れる. 以下のように結果が出力されたと思います. 回帰統計 重相関 R 重決定 R 補正 R 標準誤差 観測数 14 2 回帰統計 では, 重決定 R2 が重要です. 重決定 R2 は, 前に計算された決定係数 R と同じものです. 次に解説する重回帰分析の場合, 補正 R2( 自由度修正済み決定係数 ) の方がよいのですが, 単回帰分析の場合は重決定 R2 でもよいです. 重決定 R2 の値が 0.4 以上であれば, モデルの当てはまりに問題はないでしょう. 分散分析表 自由度 変動 分散 観測された分散比 有意 F 回帰 E-09 残差 合計 分散分析表 では, 一番右の 有意 F のところの値に着目してください. この値が,0.05 以下であれば, 係数の推定値の少なくとも一つは意味があるものであることを示していま す. この例では, E-09( ) で,0.05 よりも圧倒的に小さいので, モ デルが意味のあるものであることがわかります. 単回帰の場合は係数の P 値と一致する. 6

8 係数 標準誤差 t P- 値 切片 E-06 最高気温 ( 度 ) E-09 下限 95% 上限 95% 下限 95.0% 上限 95.0% 次の表では, 係数 とその P- 値 に着目してください. 切片の係数は, y a bx の式の a, 最高気温の係数は b の推定値になっています ( 前と同じ ). ここで重要なのが, 最高気温の P- 値です.P 値は, 推定された係数の値が 0 である確率を示しています. つまり, 最高気温とアイスクリームの販売量の間には関連性がない確率を表しているわけです. こ の例では,P 値が E-09( ) というものすごく小さい値ですので, 関 連性がない確率はものすごく小さいと考えることができます. よって, データ間には関連性があると結論付けます. 一般に,P 値が 0.05 以下であれば, 推定された係数は意味のあるものであると考えてよいでしょう. 逆に 0.05 より大きな値であれば, データ間に関連性がない確率が高いと考えられます. 切片は 0 であってもよいので P 値は気にしなくて大丈夫です. 最後に, 直線 ( 予測値 ) と観測値の y 軸方向のずれを 残差 と呼びますが, その残差と最高気温 ( 度 ) のプロットを確認しましょう. 点の集まりにあまり規則性がないような感じであれば ( 無相関であれば ), 分析の信頼性には問題ないと考えてよいでしょう. 何かしらの規則性がある感じであれば ( 例えば, 最高気温が上がるにしたがって, 点の散らばりが大きくなる ), 不均一分散や系列相関といった問題が考えられます. より高度な分析手法が必要になりますので注意してください. 7

9 演習問題 2 以下の表は, ある商品を売る 20 人のセールスマンの営業活動の適性テストの得点と, テス ト後一定期間における販売実績 ( 個数 ) を示したものです. 適性テストの得点が販売実績 に結び付いているかを確かめてみましょう. 散布図の中に回帰直線を描き, 分析ツールで 回帰分析を行ってください. 残差グラフも作成しましょう. また, 新たに採用されたセー ルスマンの適性テストの得点が 48 点であった場合, 商品の販売個数を予測してみましょう. sale.xlsx No 適性テスト得点 販売実績 ( 個 ) < 重回帰分析 > 単回帰分析では, ある 1 つの変数 x の値が, もう 1 つの変数 y の値に影響を与えるという ことを考えました. では,2 つ以上の変数が y の値に影響を与えるケースを考えるときはど のようにすればよいでしょうか.y の動きを説明する変数が 2 つ以上ある場合は, 単回帰分 析の式を拡張して重回帰分析を行います. 例えば, あるドラッグストアチェーンの店舗面 積 x1 と駐車場面積 x2 が, 売上高 y ( 演習問題 1 のデータ ) にどのような影響を与えている かどうかを分析してみます. 拡張された式は, y a0 a1x1 a2x2 となり, a0, a1, a2 は推定 するパラメータです. とにかく,Excel で重回帰分析を行ってみましょう. 手順は単回帰分 析のときと同じですが, 入力 X 範囲を拡張します. 8

10 重回帰分析を実行する 1. drug_store.xlsx を開き, 分析ツール の 回帰分析 をクリックする. 2. 入力 Y 範囲で E2~E22 までを選択, 入力 X 範囲で C2~D22 までを選択する. 3. ラベル にチェックを入れ, 出力オプションでは 新規ワークシート を選択し, 残差 と 標準化された残差, 残差グラフの作成 にチェックを入れる. 以下のように結果が出力されたと思います. 回帰統計 重相関 R 重決定 R 補正 R 標準誤差 観測数 20 重回帰分析の場合, 補正 R2( 自由度修正済み決定係数 ) に着目してください. 補正 R2 の値が 0.4 以上であれば, モデルの当てはまりに問題はないでしょう. この場合,0.59 なので, 当てはまりの良さはまあまあです. 補正 R2 の方がよい理由の一つとしては, 重回帰分析では変数の数が増えると決定係数がどんどん大きくなっていってしまうという計算上の問題があるからです. 分散分析表自由度 変動 分散 観測された分散比 有意 F 回帰 残差 合計 分散分析表 では, 有意 F の値が 0.05 以下であれば, 推定された係数の少なくとも一 つは意味があるものであることを示しています. この例では ですので, 推定され たモデルが意味のあるものであることがわかります. 係数 標準誤差 t P- 値 切片 店舗面積 (m2) E-05 駐車場面積 (m2) この表では単回帰のときと同様に, 係数 とその P- 値 に着目してください. 切片の係数は, y a0 a1x1 a2x2 の式の a 0, 店舗面積の係数は a 1, 駐車場面積の係数は a 2 の推定値になっています. 上述の通り,P 値は推定された係数の値が 0 である確率を示しています. 9

11 P 値を見ると, 店舗面積の P- 値は非常に低く, 駐車場面積の P- 値は 0.73 と 0.05 よりだいぶ大きいことがわかります. このことと係数の符号から, 店舗面積が広くなれば売上高が上がるという関係があり, 駐車場面積は売上高には影響を及ぼさないということが言えます. よって, 駐車場面積はモデルから除外してしまってよいでしょう. つまり, 店舗面積から売上高を予測する単回帰分析でよいということになります. 重回帰分析 (2) 例 2. 次の表は,C 社が発売している既存の缶コーヒーブランドの, 初年度の販売ケース 数, それにかけた広告費と販売促進費のデータを示しています.marketing.xlsx ブランド 広告費 ( 万円 ) 販促費 ( 万円 ) 販売ケース数 ( 万ケース ) A B C D E F G H I J 上と同じ手順で分析すると, 以下のような結果が得られます. 回帰統計 重相関 R 重決定 R 補正 R 標準誤差 観測数 10 分散分析表 自由度 変動 分散 観測された分散比 有意 F 回帰 E-05 残差 合計

12 係数 標準誤差 t P- 値 切片 広告費 ( 万円 ) 販促費 ( 万円 ) 補正 R2 の値を見るとモデルの精度には問題がないことがわかります. 広告費と販促費の P 値は共に 0.05 より小さく, 係数の符号はプラスですので, どちらかもしくは両方増えれば販売ケース数は多くなるということがいえます. 販売ケース数の予測には以下の式を使います. 販売ケース数 = 0.27 広告費 販促費 もし, 新たな商品 ( ブランド ) を追加するときに, 広告費 8200 万円, 販促費 5800 万円を 設定するならば, 販売ケース数は, 販売ケース数 = = 個と予測できます. ちなみに, 残差分析では 残差と予測値 の散布図 ( e Yˆ プロット ) を用います ( 説明変数が 2 つ以上あるため ). 点の集まりに規則性がなければ特に問題はないでしょう. 重回帰分析における注意点 予測に用いる変数間に高い強い相関があると, 多重共線性という問題が発生します. 説明 変数間に 0.7~1 の強い相関がある場合は注意してください. 11

13 < 数量化理論 Ⅰ 類 > 数量化理論 Ⅰ 類は, 0, 1 データを用いた回帰分析です. よって分析手法はこれまで と同じですが, データの質が異なります. 例を用いて解説します. 例 3. 次の表は, あるチョコレート菓子の様々なブランドが売り出されたときの, テレビ CM の有無と新聞広告の有無, そのとき対象となったお菓子の認知率のデータを示 しています.ad.xlsx ブランドテレビ CM 新聞広告認知率 (%) A B C D E F G H I J K L M N O は あり,2 は なし を示している まず, このデータを次のような 0, 1 データ ( 質的データ ) に変換します. ブランドテレビ CM ありテレビ CM なし新聞広告あり新聞広告なし認知率 (%) A B C D E F G H I J K L M N O 該当するなら 1, 該当しないなら 0 12

14 分析には, あり と なし のどちらかのデータを用います. なぜなら, どちらか一方が決まればもう一方の値も決まるので,2 つも必要ないからです. 今回は, テレビ CM あり と 新聞広告あり の列を使って分析を行います. 今回のデータを用いて重回帰分析を行うと以下の結果が得られます. 回帰統計 重相関 R 重決定 R 補正 R 標準誤差 観測数 15 分散分析表 自由度 変動 分散 観測された分散比 有意 F 回帰 E-05 残差 合計 係数標準誤差 t P- 値切片 E-05 テレビCMあり E-05 新聞広告あり 補正 R2 も有意 F の値も問題なく, テレビ CM ありと新聞広告ありの P 値も小さい値なの で問題ありません. 係数の値から, 認知率の予測式がわかります. 認知率 = テレビ CM あり 新聞広告あり ここで, テレビ CM ありと新聞広告ありの値は 0 か 1 ですので, 例えばテレビ CM ありで 新聞広告なしの場合の認知率の予測値は, 認知率 = = 31.35(%) と予想されます. ところで, 数量化理論 Ⅰでは係数の値を正規化します. 調整は以下のように行います. テレビ CM なし の 1 の数は 6 で, 分析から除外しているので係数は 0 とします. テレビ CM あり の 1 の数は 9 で, 係数は です. その平均値を求めると, ( ) /

15 重回帰分析で求めた係数からこの値を差し引いた値を, 各ダミー変数への重みとします. テレビ CM なしは , テレビ CM ありは となります. 同様 に新聞広告なしは 8. 57, 新聞広告ありは 4. 29となります. 定数項を調整してあげると, 認知率 = 6.09 テレビ CM あり 新聞広告あり となります. もう一つの式は, 認知率 = テレビ CM なし 新聞広告なし となります. 認知率が前と同じになるかを計算してみましょう. < コンジョイント分析 > 例 4. 以下の表は来年発売可能なノートパソコンの 属性 と 水準 を示しています. 属性 水準 バッテリー持続時間 3 時間 6 時間 ハードディスク容量 100GB 160GB メモリ容量 512MB 1GB 価格 5 万円 10 万円 どのような組み合わせの商品がよいでしょうか? 新商品開発のヒントを得るためには消費者アンケートが有効です. この場合,2 水準に対して属性が 4 つあるので, すべての組み合わせについて満足度を聞くとすれば,16 通りの商品案を提示しなければなりません. しかし, あまり案が多いとアンケートに回答する側が大変になってしまいます. そこで, 商品案を絞りつつバランスの良い商品案リストを作ります. コンジョイント分析では 直交表 というものを使って商品案を作成します. 商品案 持続時間 ハードディスク容量 メモリ容量 価格 1 3 時間 100GB 512MB 10 万 2 3 時間 100GB 512MB 20 万 3 3 時間 160GB 1GB 10 万 4 3 時間 160GB 1GB 20 万 5 6 時間 100GB 1GB 10 万 6 6 時間 100GB 1GB 20 万 7 6 時間 160GB 512MB 10 万 8 6 時間 160GB 512MB 20 万 14

16 上の表は,L8 直交表を用いて作成した商品案です. 今回はそれぞれの商品案について, 消 費者に 7 段階 (1 点 ~7 点 ) で評価してもらうことにしました. 以下の表は, その結果をま とめたものです. 満足度 商品案持続時間 ハードディメモリスク容量容量 価格 回答者 1 回答者 2 回答者 3 回答者 時間 100GB 512MB 10 万 時間 100GB 512MB 20 万 時間 160GB 1GB 10 万 時間 160GB 1GB 20 万 時間 100GB 1GB 10 万 時間 100GB 1GB 20 万 時間 160GB 512MB 10 万 時間 160GB 512MB 20 万 分析をするためには, それぞれの回答者について商品 1~8 までのデータを, 以下のような 0, 1 データに変換します. 持続時間 ハードディスク容量 メモリ容量 価格 回答者商品案 3 時間 6 時間 100GB 160GB 512MB 1GB 10 万 20 万 満足度 回答者 2,3,4, についても同様にデータを加工します. 加工が終わったら, 再度回帰分析を用いて分析をします. 数量化理論 Ⅰ 類のときと同様に,0,1 データなので, 一方の列を削除して分析を行います. 今回は,3 時間の列,100GB の列,512MB の列,20 万の列を削除します. データが加工できたら, 回帰分析 の 入力 Y 範囲 に満足度の列を指定し, 入力 X 範囲 には 6 時間,160GB,1GB,10 万の列を指定して重回帰分析を行ってください (laptop.xlsx). 結果を見ると, 補正 R2 も有意 F の値も問題なく, すべての説明変数の P 値は 0.05 よりも小さくなっています. 15

17 回帰統計 重相関 R 重決定 R 補正 R 標準誤差 観測数 32 分散分析表 自由度 変動 分散 観測された分散比 有意 F 回帰 E-11 残差 合計 係数 標準誤差 t P- 値 切片 時間 GB GB 万 E-12 係数の値から, 満足度の予測式は以下のように書けます. 満足度 = 持続時間 HDD+1 メモリ容量 価格 機能高水準で 20 万の商品と, 機能は低水準だが 10 万円の商品のどちらの満足度が高いで しょうか?(3.76,3.88) 属性の重要度それぞれの係数の値は, 説明変数が 0 から 1 へ変化したときの満足度の変動幅を表しています. よって, すべての係数の値を合計し, それに対するそれぞれの係数の大きさの割合を求めることで, 属性の重要度がわかります. この例では価格が満足度の変動幅の 50% を占めていますので, 消費者が価格を非常に重要視していると判断できます. コンジョイント分析の注意点あまりにも 属性 もしくは 水準 の数が多くなると, 直交表を用いて絞り込んでも商品案が多くなってしまいます. 分析対象とする属性と水準をなるべく絞り込んでから直交表を用いるようにしてください. 16

18 付録 Ⅰ: 残差分析 回帰分析において, 残差の特徴を見ることは非常に重要です. 単回帰の場合は, 散布図を描くことで特徴がすぐわかりますが, 重回帰分析ではグラフを描くのが大変なため, 残差の特徴で分析の正確さを判断します. 以下の 4 つのデータセットで回帰分析を行うと, すべてほぼ同じ回帰式となります. しかし, データの特徴 ( 残差の特徴 ) は全然違いますので, 確かめてみてください. パターン1 パターン2 パターン3 パターン4 no x1 y1 x2 y2 x3 y3 x4 y 出所 :F.J.Anscombe, Graphs in Statistical Analysis, The American Statistician, Vol.27, No.1,1973, pp

19 パターン 1 は特に問題はありません. パターン 2 は, 回帰式が線形でないことを示しています. また, このようなパターンは, 系列相関がある場合によく見られます. パターン 3 は,1 つの外れ値が回帰直線を曲げてしまう ( 大きな影響を与えてしまう ) ことを示しています. このような場合には, 外れ値を除くことで違った回帰直線が得られます. パターン 4 は,1 つの外れ値が全く異なった結果を与えることを示しています. 外れ値を除いたら垂直な回帰線が得られることがわかります. とにかく, 規則性の見られる残差がプロットされる場合には注意が必要です. 付録 Ⅱ: 分析ツールが表示されていない場合 1. ファイル オプション をクリック 2. アドインの中の 分析ツール を選択し, 設定を押す. 3. 分析ツール にチェックを入れて OK を押す. データタブの中に データ分析 ア イコンができる. 18