エクセルデータのまとめ方 分析用データを作る方法とは？
KUROCO株式会社代表の齋藤です。今回はデータ分析のコツをお伝えしたいと思います。
VLOOKUP関数でデータをまとめる
データ分析を行う上で、様々なデータを取り扱うと思うのですが、「各種データを一つにまとめるところから」スタートしてください。
販売実績関連データや、商品マスタ、顧客マスタといったマスタデータなどを、まずは一つのデータに結合します。そのデータさえ見れば、全ての情報が入っている、という状態を作るのです。
例えば、次のような３種類のデータがあるとします。
図１．販売実績データ/顧客マスタデータ/商品マスタデータ 例
販売実績データ
顧客マスタデータ
商品マスタデータ
皆さんの事業でもこのように、日々の販売や取引状況が蓄積されているデータと、商品や顧客などのプロファイル情報であるマスタデータがあるかと思います。これらを一つにすることで、抜け漏れのない（少ない）データにすることができます。
様々なツールがあるので、紐づけるための方法はいくつもありますが、ここでは最も多くの人が使っているであろう、エクセルでの紐づけ方法について説明します。紐づけるためには、VLOOKUP関数を用います。（VLOOKUP関数の使い方についてはここでは省きます。）
販売実績データのF列「会員ID」と顧客マスタデータのA列「会員ID」が紐づけられます。また、販売実績データのD列「商品ID」と商品マスタデータのA列「商品ID」が紐づけられます。
販売実績データの会員ID、商品IDをキーとして、顧客マスタデータおよび商品マスタデータの各項目をVLOOKUP関数で紐づけたデータが図２となります。
図２．マスタデータを紐づけた販売実績データ
ビジネスは全て「顧客」に「商品」を販売することで「売上」となります。
上図のように、販売実績データに顧客マスタデータと商品マスタデータを紐づけることで、売上を上げるために何が問題となっているのか、解決すべき課題は何なのかを把握するための精度の高いデータ分析を実現するための「分析用元データ」となるのです。
IF関数を使って元データをアップデート
もう少しだけこの「分析用元データ」をアップデートしてみます。
データ分析は大きな傾向から進めていくことが重要です。データ分析を行う際に、その目的を定めることがもっとも大切ですが、現実にはその目的が漠然としている場合がよくあるのではないでしょうか。
経営者や上長、あるいは自身も売上が減少している要因や収益性が悪化している要因がわからず、解決策を立てられないこともあるでしょう。このようなとき、数値データを大きいところから分析することで、徐々に目的を詳細に定めることができます。
また、いきなり細かい部分から分析してもその結果が売上増加にインパクトあるかは分かりません。大きいところから傾向を分析し、売上増加に影響を与えている部分を少しずつ細かく分析していくことが重要なのです。
例えば、次のようにブレイクダウンしていきます。
- 指標
売上 ⇒ 購入件数×購入単価
⇒ （UU数×購入回数）×（1回当り購入点数×商品単価） - 商品
商品カテゴリ ⇒ 商品 - 顧客
会員/非会員 ⇒ 性別、年代別、新規・既存 等 - 時期
年度/年間 ⇒ 月次 ⇒ 週次、曜日別、時間帯別 等
更に複数のセグメントをクロスで分析していくことも詳細化していく上では必要になってきます。そこで、分析するためのデータ自体を大きい傾向から分析できるようなデータに整理することが重要です。
先ほど各種データを紐づけたデータの各項目をもとに、大きな傾向から分析するための必要な項目を図３のように追加していくことで、分析用元データを完成させます。
図３．必要項目を追加した販売実績データ（分析用元データ）
S列「商品代金」はG列「商品価格」×H列「個数」の計算により、エクセルファイルの１行ごとの商品単位での売上を計算しています。
T列「購入件数」については、A列「注文ID」が同一のものは同じ注文となるため、例えば、セルT2には、「=IF(A2=A1,0,1)」という数式を入れることで、購入件数を表の中でカウントできるようにしています。
U列「会員/非会員」については、例えば、セルU2には、「=IF(F2=””,”非会員”,”会員”)」という数式を入れていますが、F列「会員ID 」の有無により会員か非会員かを分類しています。
V列～X列については、B列「受注日」から注文の年、月、週をそれぞれ計算しています。
それぞれYEAR関数、MONTH関数、WEEKNUM関数を使います。
Y列「年齢」は顧客マスタデータから販売実績データに紐づけたN列「誕生日」から年齢を計算しています。例えば、セルY2には、「=IFERROR(DATEDIF(N2,TODAY(),”Y”),””)」という計算式を入れています。
DATEDIF関数を使うと日付から誕生日を計算できます。IFERROR関数については、非会員の場合は誕生日が記載されていないため、その場合は空白を入れるようにするために使用しています。
そしてZ列「年代」については、年齢からROUND関数を使って年代に計算しています。例えば、Z2には、「=IFERROR(ROUND(Y2,-1),””)」を入れます。
それぞれの関数についてもぜひ覚えておいてください。最後、かなり細かい方法まで記載しましたが、このような複数のデータを一つの「分析用元データ」にすることで、その先のデータ分析の精度やスピードがグッと上がりますので、ぜひ試してみてください。
＼ この記事を読んでいる人におすすめ！ ／
