第5回　多重クロス集計1

前回は、2万3千件弱の販売管理のデータを使って分析を行いました。今回は、ID-POSのデータを使って分析を行います。

Excelで取り扱えるデータ量ですが、最大104万8,576行の制限があるため、ID-POSの全データのうち、4店舗、6ケ月分の菓子の売上データ、83万件のデータを使います。

全商品を対象とした2年分のデータを分析する場合、ほかのツールを使用することになりますが、本講座で解説している手順と手法は、その場合でも適用することができます。

はじめに、｢おさらい｣もかねて、次の手順を行ったあとに、商品と年齢、店舗による3次元のクロス集計について解説します。

ID-POSのデータを使ったExcelによる多重クロス集計の仕方を動画で紹介しています。項目ごとにご覧いただく方法と、まとめてご覧いただく方法がございます。

※動画で紹介している操作などの詳細については、以下をお読みください。

分析をはじめる前に、どのような項目があるかを示します。

これらの項目から、｢売れ筋商品の選定｣を目的に分析を行います。

最初に、データの抜けや異常値がないかを確認し、データのクレンジングを行います。今回は、Excelのフィルター機能を使用します。

1. ID-POSデータをExcel上に展開し、[データ]タブをクリック
2. 表内のセルをクリック(場所はどこでも構いません)
3. [並べ替えとフィルター]グループの＜フィルタ＞ボタンをクリック
4. 調べたい項目の表頭の＜▼＞ボタンをクリック

各項目でフィルター機能を使うと、項目に存在する値がすべて表示されます。データの抜けがあった場合、値の最終セルに｢空白セル｣という名前で表示されます。

各項目の信頼度の結果は次の通りです。

以上で今回のデータには問題がないことが分かりました。

項目｢生年月日｣のデータから｢年齢｣を求め、年齢別のデータ分析に利用できるようにします。また、｢金額｣を｢点数｣で割って｢単価｣を求めデータ分析で利用できるようにします。このように、分析しやすいように計算を行って作成したデータを｢加工データ｣といいます。

年齢

年齢は、次のような操作を行うことで求められます。

1. M列[生年月日]とN列[商品名]の間に列を挿入し、項目名を｢年齢｣とします。(｢年齢｣がN列となります)
2. セル｢N2｣に次の数式を挿入します。
   =IF((D2-M2)/10000>200," ",INT((D2-M2)/10000))
   (D列は、｢利用日(購入日)｣)
3. セル｢N2｣をコピーし、最後の行まで貼り付けます。

※数式で作成したデータは、そのまま分析に使用すると誤った集計を行う可能性があります。作成したデータ全てをコピーし、[貼り付け]オプションの[値]で貼り付けましょう。

単価

単価は、次のような操作を行うことで求められます。

1. L列[利益]とK列[点数]の間に列を挿入し、項目名を｢単価｣とします。(｢単価｣がL列となります)
2. セル｢L2｣に次の数式を挿入します。
   =ROUND(I2/ABS(K2),1)
3. セル｢L2｣をコピーし、最後の行まで貼り付けます。

データ加工を行って加工データを作成し、データ分析用に出来上がったデータを｢確定データ｣といいます。

Excelのピボットテーブルを使い、｢個数｣と｢売上金額｣の上位商品を調べ､｢売れ筋商品｣を見つけます。全体の結果はもちろん、実際に販売している店舗ごとの｢売れ筋商品｣の方が重要です。

全体の上位20品目

商品が売れた個数を調べる場合は｢商品名(T列)｣を｢点数(K列)｣で集計し、売れた金額を調べる場合は｢商品名(T列)｣を｢税抜金額(I列)｣で集計します。

1. [挿入]タブの[テーブル]グループの[ピボットテーブル]をクリック
2. [ピボットテーブルの作成]ダイアログボックスの＜OK＞ボタンをクリック
3. [ピボットテーブルのフィールド]で (1)[商品名]を[行]ボックスにドラッグ (2)[点数]を[Σ値]ボックスにドラッグ
4. [Σ値]ボックスの[データの個数]を[合計]に変更し、購入された｢商品数｣にします。
5. ｢点数｣を降順に並べ替えます。

各店舗の上位20品目

全体の上位20品目で求めたピボットテーブルを使います。

1. [ピボットテーブルのフィールド]を表示
2. [店舗CD]を[列]ボックスへドラッグ

全体の上位20品目の月別の推移

全体の上位20品目で作成したピボットテーブルを利用し、次の操作を行います。

1. ピボットテーブルの[利用日]を[列]ボックスへドラッグ
2. [20130701]のセルから[20130731]のセルまでを範囲指定
3. [ピボットテーブルツール]の[分析]タブ - [グループ]の[グループの選択]をクリック
4. [行ラベル]の[グループ１]をクリックし、｢7月｣と入力して[Enter]キーを押します
5. 同様の操作を12月まで繰り返す

「アイスクリーム系」に関しては、7月・8月の「販売個数」や「売上」が多く、それ以降は少なくなる傾向があります。

｢販売価格｣は、｢売上｣や｢販売個数｣とも密接に関連します。今回は傾向を見るだけにします。

1. [ピボットテーブルのフィールド]の[商品名]を[行]ボックスへドラッグ
2. [単価]を[行]ボックスにある[商品名]の下にドラッグ
3. [ピボットテーブルのフィールド]の[点数]を[Σ値]ボックスにドラッグ
4. [値フィールドの設定]ダイアログボックスから[集計方法]タブの[合計]を選択し、＜OK＞ボタンをクリック

また、｢曜日｣による販売の違いを見るため、今の表に｢曜日｣を加えたクロス集計を行います。

総計からは、｢曜日１｣が一番多く、次に｢曜日３｣、｢曜日７｣と続きます。｢曜日１｣は｢日曜日｣で、｢曜日７｣は｢土曜日｣なので、｢土曜日曜｣に多く売れていると思われます。

次回は、ID-POSデータから会員データを抽出して年齢別の再カテゴリー化を行い、店舗別・年齢別・商品別の売上高や、さらに月別を加えた季節ごとの売上高を分析します。