Excelとデータ分析(6)数字以外も”ダミー変数”として、ガンガンExcel回帰分析

  • ブックマーク
  • Feedly
  • -
    コピー
Excelとデータ分析(6)数字以外も”ダミー変数”として、ガンガンExcel回帰分析

せるワザ

せるワザでは、過去にも単回帰分析重回帰分析について解説しました。

回帰分析を使うと、「実収入と実支出」など相関がつよいトピックばかりではなく、「大学進学率と肥満度(BMI)比較」のように、一見すると関係なさそうな数値同士からも、意外な法則性を発見できる可能性があります(※ 予想から大きくはずれることも、よくあります!?)。

では、回帰分析には、数値しか使用することができないのでしょうか?

答えは「Yes」であり、「No」です。ん?

Excelの[データ分析]ダイアログボックスから選択できる[回帰分析]では、Y軸/X軸ともに、数値を指定する必要があります(数値以外のセルを選択しても、エラーとなり実行できません)。

それならば、変数を数値に変えてしまえばよいのです。

「ずいぶん乱暴なやり方では?」とびっくりするかもしれませんが、これは統計学的にも「数量化理論1類」として知られる、伝統的な手法です。

今回は、この数量化理論1類を活用して、回帰分析でみつけられる規則性の幅を、さらに広げてみましょう。

まず、今あるデータから異常値をみつけましょう

ここでは、以前ご紹介したことのある「アイスクリーム屋さんで学ぶ楽しい統計学」(KOGOLAB(早稲田大学人間科学学術院 向後研究室で実際授業に使われたWeb公開データ)を、サンプルデータとして使ってみます。

アイスクリーム屋さんが来店客数を予測するときに、当日の気温との相関を調べました。

「気温が高いほど、アイスクリームがよく売れそうだ」という仮説は、すぐに思いつきますよね。 その影響がどのくらいか、は実際に散布図を作るとわかります。

[サンプル;某アイスクリーム・チェーンの8月2週間の来店客数と気温の一覧表]

Excelとデータ分析(6)数字以外も”ダミー変数”として、ガンガンExcel回帰分析

(C) 2003 KogoLab

この表から、Excelのグラフ・ウィザードを使って自動的に作成した散布図がこちら。

Excelとデータ分析(6)数字以外も”ダミー変数”として、ガンガンExcel回帰分析

気温とアイスクリーム・ショップの来店客数に、正の相関があることはわかりますが、4つほど、異常値(他のデータにくらべて突出している値)があります。これはおそらく「週末」(土曜日・日曜日)のデータだったのだろう、と予測しています。

それなら(ほかの要素はぬきにして)、何曜日には何人くらい来店するのか?を推測してみてはいかがでしょうか。 14日分の元データは西暦何年のもので、何曜日のものかわかりませんが、おそらく月曜日からはじまって、2週間後の日曜日までのデータでしょう。

Excelとデータ分析(6)数字以外も”ダミー変数”として、ガンガンExcel回帰分析

このように、曜日を代入してみると、曜日ごとの来店客数計は下記グラフのようにまとめられます。

Excelとデータ分析(6)数字以外も”ダミー変数”として、ガンガンExcel回帰分析

このサンプルは、わずか2週間14日分のデータなので、手動計算しても簡単に集計できます。

しかし、実際の需要予測では、数ヶ月分〜数年分の大量データをつかって分析した方が精度があがりますので、Excelの回帰分析ツールを使用します。

曜日データを数値化(ダミー変数化)することで、回帰分析ツールにかけられるようになります

ここからは「(ほかの要因はぬきにして)何曜日に何人の来店客数がみこまれるか」を予測する単回帰分析を、Excelでやってみましょう。

Excelに分析させるために、用意したデータがこちら。

Excelとデータ分析(6)数字以外も”ダミー変数”として、ガンガンExcel回帰分析

(1)該当する曜日と、当日の来店客数との関係をみるため、該当曜日;1/非該当曜日;0とするマトリクスを、来店客数データのとなりに追加します。

(2)月曜日〜日曜日まで7曜日の変化を比較したいのですが、どこか1列、比較対象の軸(基準)となる曜日を削除します(どの列を削除しても、要素間の差分は変わらない結果が返されますが、ここでは「月曜日」をはずします)。

Excelとデータ分析(6)数字以外も”ダミー変数”として、ガンガンExcel回帰分析

(3)[データ分析]→[回帰分析]ダイアログボックスを表示させたら、「来店客数」のデータを[入力Y範囲:]へ、「曜日マトリクス」のデータを[入力X範囲:]へ入力します(表に見出しがあれば、[ラベル]にもチェックを入れてくださいね)。

分析結果が出ました。

Excelとデータ分析(6)数字以外も”ダミー変数”として、ガンガンExcel回帰分析

一般に「補正R2」は0.5以上あると「信頼性のある分析結果」といわれていますので、ギリギリ微妙なところ(0.493)ですが、曜日ごとの来店客数みこみがでました。

「切片」293.5が、比較基準とした月曜日の平均来店客数です。

以下「火」「水」「木」...「日」までの数値が、月曜日よりもふえるとみこまれる客数です。

以上、曜日を0/1の数値データ化して回帰分析を行いましたが、同様のことは、「当日の天候(はれ/雨)」、「広告チラシ(有/無)」、「割引プロモーション(有/無)」など、ほかの要因分析にも応用することができます。

大小ハッキリわかる数値(量的データ)に対して、単純に大きさを比較できない質的データも、このように、分類のため「ダミー変数化」することで、Excel回帰分析ツールにかけることができるようになるのです。

まとめ;質的データを「数量化」することで、分析の幅を大きくひろげることができます

「数量化理論1類」ときくと、なにやら難解そうなイメージをもちますが、実はそれほどむずかしい話ではありません。

「月曜日・火曜日」....、「男性・女性」...、「不満・やや不満・普通・やや満足・満足」...など、数値でない分析要素を説明変数とする場合の、「ダミー変数化」のことをさしているのです。

かりに男;1/女;2、と数値化しても、この1と2の間には、なにも量的なちがいはありません(女性の方が男性より2倍※※※、という意味ではないですよね)。たんなる分類符号にしかすぎない、とかんがえると、気がラクになりますよね。

ビジネス上、将来需要予測を立てることは、1つの重要なミッションです。
とはいえ、肩ヒジはらずに、まずは「数量化理論1類」で遊んでみる!? というのはいかがでしょうか。
「予測がはずれたって結構!」それくらいのおおらかな気分で、Excel回帰分析ツールをいじってみると、けっこうおもしろい分析結果がはじきだされてきますよ!!









小さなお悩みでも、
お気軽にご相談ください!

お急ぎの方はお電話にてお問い合わせください

050-6867-2130
せるワザロゴ

セルワザでは、役に立つEXCELワザをご紹介しております!

  • ブックマーク
  • Feedly
  • -
    コピー