エクセルで回帰分析用のデータを作るための準備作業をざっくり解説

重回帰分析をするためには、ローデータを加工して重回帰分析用のデータに変換する必要があります。そのための手順をざっくり解説します。
今回は、あるECサイトである商品の売上がどんなものに影響を受けるのかを知りたい場合を例とします。

①成果データの確認(目的変数の設定）

商品の売上と簡単に書きましたが、何月から何月までの売上か？指標は売上点数か？金額か？対象となる商品はECサイトで扱う全商品か？一部の商品なのか？など、成果データの条件を整理します。

今回は、誕生祝いのベビー服の直近3年間の月次売上点数とします。

②成果要因の洗い出し( 説明変数の洗い出し）

設定した成果に対して影響を与える変数をブレストベースで洗い出しします。洗い出しは紙とペンでもいいですし、エクセルでもいいです。

以下のイメージのような形で、成果データにどんなものが影響を与えるのか？の因果構造を矢印で作ります。

イメージ:

③説明変数の設計/取得可否の確認

ブレストベースで洗い出しした要因について、実際に数値データとして取得できるものに変換します。
例えば、「赤ちゃんがその月に多く生まれた」であれば、政府のサイトから月次の出生人数を取得できるか調べ、取得できれば「月次出生人数」を変数に加えます。

④データの作成

説明変数の設計と取得可否の確認ができたら、あとは解析用のデータを作るだけです。

基本的には、データは「１つのy　目的変数」と「たくさんのx　説明変数」として、整理して作るとわかりやすいです。

特に、重回帰分析は変数選択を繰り返ししてモデリングするため、データはきれいに作り、yは左端にしたり、xはわかりやすい変数名をつけるなどしておくとスムーズかもしれません。

イメージ：

回帰分析用のデータを作る際は、エクセル作業よりも前段階の考える時間が重要かもしれません。つまり、どんなモデルを作るのか？のお題設定と、どんな変数を作るのか？の変数設定ができると、エクセルでの作業もスムーズに進められるかもしれません。

エクセル作業のミスを減らすには「フィルタ機能」を上手に使う必要があります

エクセルは自由にデータ操作をできるのが便利な反面、意図しない操作を無意識にやってしまい、間違ったデータを作ってしまうことがたまにあるかと思います。

フィルタ機能を活用すれば、このような思いもしないミスを減らすことができます。
特に、データを用意しているときに間違えすいところは

• データのソート（並べ替え）をしたとき
• データをコピーするとき

が挙げられます。

とくに空白セルが多い場合にはミスが起こりやすくなります。

それを防ぐために、ソート機能を使いましょう。

サンプルデータ

名簿データなのですが、途中で空白行が入っていたとします。

E列を選択し、年齢で大きい順にソートした場合、の結果です。

※拡大

空白行を境にして、上のデータまではソートされていますが、下のデータはソートが適用されていません。今回のような10件程度であれば気づくことができますが、1000件以上もあるあようなデータだと、今回のようなミスが起こっても気づくことは難しいのです。

どうするか

データ全体を確認して、全範囲を指定してフィルタを適用することです。

（注意！左上の一部のセルだけを指定するのではなく、データ全体を指定してフィルタを適用ことがコツ！）

そうすることで、空白行があっても囲っているデータ全体でソートをできるようになります。また、フィルタで空白のある行を除くこと（非表示にすること）ができるので、コピーをする際にもミスを防ぐことができます。

①データ全体を指定する

※データ全体を指定するには空白に注意しながら、shift + ctrl + ↓キー⇢キー　を使うと便利です。

②フィルタを適用する

完成！

まとめ

• データを操作する前に、データ全体の範囲を確認する
• 確認ができたら、全範囲を選択して、フィルタを適用する
• フィルタ機能を使い、空白セルの処理（削除する/注意文言を入れる　など）を行う

「分析のプロセス」について

　分析プロセスの代表的なものに「CRISP-DM」や、「Import, Tidy, Transform, Visualize, and Model 」などが挙げられると思いますが、組織でのマーケティング活動における分析プロセスについて、個人的に重要だと思うステップを紹介します。

• 分析のプロセス①　キーマンの把握
• 分析のプロセス②　目的設定
• 分析のプロセス③　データの収集・分析
• 分析のプロセス④　分析結果の共有・展開

分析のプロセス①　キーマンの把握

　組織活動において、分析結果はその組織の状態を把握し、意思決定をするための情報として活用されます。また、その意思決定は重要な人が一人で決める場面が多くあります。分析レポートを書く立場である場合は、誰がその判断を下すのか？を把握すると、そのキーマンに響くレポートを具体的に意識して書けるようになるでしょう。

　キーマン（例えば会社の社長や、マーケティング部部長）はなんの事前説明もなしに重回帰分析の係数情報やp値を提示するだけで理解できる人・さらに、それを知りた人もいれば、統計解析の結果を出しても理解できない人まで様々です。分析リテラシーの低い人に重回帰分析をした結果を提示しても、「？」で終わってしまうことが目に見えています。

　逆に、分析レポートの内容が良ければ、基本的にレポートの内容に沿った形で、会社の意思決定がなされたり、会社の大きな方針決めの要因になったりします。キーマンの関心に沿った良いレポートを作り、説明＆説得できたときの喜びもひとしおです。

分析のプロセス②　目的設定

  目的設定はキーマンの関心ごとに合わせる必要があります。莫大な予算を投下したマーケティングキャンペーンの効果検証をするときにも、単に「キャンペーンの成果」だけを示せればいいのか？「キャンペーンの成果が良くなかったのは既に分かっていて、何が原因で失敗してしまったことを示す」必要があるのか？など、意思決定のキーマンと対話し、どういったことについて分析しなければならないのかを明確にします。

　ここで難しいのは、キーマンは分析のプロではないことが多々であるので、「どんな分析をしたら、キーマンが知りたいことを明らかにできるのか？」は、分析コンサルティング会社に相談したり、自分で考える必要があります。（がんばろー）

分析のプロセス③　データの収集・分析

　キーマンとの目的設定の対話が終了してしまえば、あとは作業です。大規模な組織ではデータの収集に苦労しますが「キーマンがやれっていってるから〜」の一言で、データの収集もスムーズに終了します。分析も目的が明確であればやりやすいです。

分析のプロセス④　分析結果の共有・展開

　分析結果の共有・展開をする上で気をつけなければならないことは、レポートの品質がある一定以上になってからすることです。具体的には集計値にミスがないこと。集計方法についてレポートに記載すること。が前提です。

　前者ですが、明らかにミスと分かる数値が入っている状態で展開した場合、レポートを書いた人の信頼が落ちます。家にゴキブリを一匹見たら、その家には・・・の法則で、数値ミスが一箇所でもあった場合はそのレポートは信用ならないものと思われますし、信頼を取り戻すのにも相当な労力が必要になります。後者は、例えばユニークユーザーを例に取ると、ブラウザベースなのか、本当に一人の人間ベースなのかで意味も変わってきます。カウントする定義や割り算の数式などは注釈として記載しておくと親切です。

　以上が個人的に大事だと思う分析プロセスです。

データサイエンスの勉強に使える無料のデータ

データ分析の実践をする上で欠かせないのがデータ。社会人の人は会社のデータを使えばいいですが学生だと自分でデータ用意するのは難しいですよね。他のデータも扱って色々とやってみたいと思いますよね。kaggleに参加するのも手ですが、初心者はハードルが高いです。

そこで、気軽に勉強できるサンプルのデータをご紹介します。

自然科学系データ

■気象庁　過去の気象データ

http://www.data.jma.go.jp/obd/stats/etrn/index.php

→　気温や降水量など季節性がはっきりしているので、ARIMAとかカルマンフィルターのモデルを作るのに適したデータになっています。

マーケティング系データ

■KSPーSP 食品スーパー新商品売れ筋ランキング

https://www.ksp-sp.com/open_data/

→　週次のPOSの売上ランキングが掲載されています。毎週のランキングをつなげ合わせることで、特定商品の売上をトラッキングすることもできます。

なお、CSVでの提供はないようなので、自分でHTMLファイルを加工してデータを作成する必要があります。

経済系データ

■セントラル短信　FXデータ

https://www.central-tanshifx.com/market/finder/popn-csv-download.html

→　各種類の通貨ペアの日足、週足、月足のデータが掲載されています。単純な値動きでけでなく、移動平均やボラティリティなどでk-meansなどでセグメントをし、各セグメントの特徴を抽出するなどの練習ができると思います。

【サンプルデータ付きの本を購入するのも手】

データ分析で使えるエクセルの機能と関数のまとめ

エクセルの関数は星の数ほどありますが、便利な関数だけ覚えておけば大丈夫です。
では、データ分析でよく使えるエクセルの機能や関数はどういったものがあるでしょうか？

■フィルタ

「フィルタ」を使うと簡単便利にデータを絞り込むことができます。

メニューからデータ→フィルタをクリックすればできますが、

ショートカットキーを覚えて置くと便利です。

ショートカットはAltボタンを押しながら、D＋F＋F（Fボタン２回）です。

■マージ

以前の記事でも紹介しましたが、Vlookup関数はエクセルで分析をする上で必要不可欠な関数です。必ず覚えたい関数ですね。

■トランスフォーム（数値変換）

日付を操作する関数

• year(日付)　   →　年だけ
• month(日付)　→　月だけ
• day(日付)　　 →　日だけ

 日付から、年だけ、月だけ、日だけを取り出す関数です。特にmonth関数は月次の数値を計算するときのキーを作るときによく使う関数です。

さらに

• weekday(日付)　→　曜日に変換
• weeknum(日付) →　その年の何番目の週か？（週数）

曜日別や週次の推移を計算する際にはこうした関数も覚えて置くと便利です。

文字列操作する関数

• left(文字列 , 文字数）　→　指定した文字列の左から指定した文字数だけ抽出
• right(文字列 , 文字数)   →　指定した文字列の右から指定した文字数だけ抽出
• ASC(文字列）　→　全角カタカナを半角カタカナに変換

アンケートデータに入っているフリーアンサー情報などを加工する際によくつかう関数です。

異常値を操作する関数

• IF関数
• IFERROR関数

少し高度なデータ操作をする場合には、IF関数も理解する必要があります。IF関数は覚えなくてもデータの加工はできるので、「効率的にエクセルの関数を組めるようになりたい！」と感じるようになってから勉強すればいいと思います。

【参考になる本】