第6回　多重クロス集計2
ビッグデータによる売れ筋商品の分析

店舗ごとの年齢や月別の売れ筋商品の分析

前回は、ID-POSのデータを使って、売れ筋商品の分析を行い、店舗ごとの売れ筋商品の違いを見てきました。

今回は、さらに各店舗の特性を調べるために、ID-POSの売上データから、年齢や人数が確認できる会員データを抽出します。

抽出した会員データを使って､｢会員の基本情報(会員数・年齢構成等)｣や｢店舗別会員情報｣､｢年齢別商品別売上高｣を調べます。

さらに｢店舗別・年齢別・商品別売上高｣の分析を行い､月別による｢季節ごとの商品別売上高｣の分析を行います。

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

【項目ごとにご覧いただく場合】
【まとめてご覧になる場合】 ※動画で紹介している操作などの詳細については、以下をお読みください。

再カテゴリー化

｢店舗｣により売れる商品に違いがあるので、地域特性として｢年齢｣を考え、｢年齢によって好まれるお菓子に違いがある｣と仮説を立て検証します。
また、｢店舗46｣が他の店舗と比べ6割ほど売上が低くなっているのも、地域特性として｢住民人口｣を考え、｢店舗46｣は他店と比べ会員数が6割ほど少ない｣との仮説を立てます。

会員データの抽出

これを検証するためには、｢年齢｣や｢人数｣が確認できる｢会員のデータ｣を利用する必要があります。 元データを利用して分折することも可能ですが、操作が煩雑になることや、処理速度が遅くなることも考えられるため、会員だけのデータを抽出して分折を行います。

会員を抽出するためには、次の操作を行います。

• データを開き[データ]タブの[並べ替えとフィルター]グループの[フィルター]をクリック
• G列[カード番号]の＜▼＞ボタンをクリック
• プルダウンメニューの｢0｣のチェックを外し、＜OK＞ボタンをクリック
  (カード番号が全て表示され、カード番号のないデータは｢0｣が表示されます)
• 抽出された会員データを範囲指定してコピー
  (1) [A1]をクリック
  (2) [Ctrl]キーと[Shift]キーを押しならが[→]キーを押して一番右側に移動
  (3) [Ctrl]キー、[Shift]キー、[↓]キーを押して最下部に移動(全てを範囲指定できます)
• 新しいブックを開き、コピーしたものを貼り付け

作成した会員データをもとに、次の分折を行います。

会員基本情報

会員の中で｢生年月日｣を登録している｢会員数｣と｢年齢別の会員数｣を求めます。

●会員数
｢会員数｣は｢会員カード｣と同じ数なので、G列の｢カード番号｣を使用して求めます。

• 新規のピボットテーブルを作成
• [ピボットテーブルのフィールド]の[カード番号]を[行]ボックスへドラッグ

表示された｢カード番号｣の数が｢カード枚数(＝会員数)｣となります。
｢枚数｣を求めるには｢カード番号｣が入っているセルの｢最終行番号｣から[行ラベル]が表示されている｢行番号(3)｣を引くと求められます。
これ以外にも、｢COUNT関数｣を使用しても求めることができます。
実際に求めると｢23982(カード番号が入っているセルの最終行番号)-3=23979｣となります。

●年齢を登録している会員数
｢年齢別｣に｢カード番号｣を並べ、それを数えることで求めます。
現状のピボットテーブルの機能だけでは難しいので、次の操作を行います。
ピボットテーブルは、｢会員数｣を求めたものを使います。

• [ピボットテーブルのフィールド]の[12月年齢]を[行]ボックスの[カード番号]の上にドラッグ
• セル｢C列(C3)｣に｢仮年齢｣という項目を作り、次の式をセル｢C4｣に入力

  =IF(A4=" ",0,IF(A4<200,A4,C3))

  (この式で、｢カード番号｣がどの年齢に対応しているのかを表示します。たとえば、最初のカード番号｢2900013161027｣は8歳のものなので、｢8｣と表示されます)
• 入力した式をコピーし、データのある最後の行まで貼り付け
  カード番号に対応した年齢が表示されます。
  このカード番号に対応した年齢の個数が、その年齢の人数になります。
• [仮年齢]が表示されているセル｢C3｣をクリックし、ピボットテーブルを作成
  [既存のワークシート]にチェックを入れ、[場所]テキストボックスのボタンをクリック
  [ピボットテーブルの作成]ダイアログボックスで、テーブルを作成する最初のセル(今回はセル｢E3｣)をクリックし、ダイアログボックスを閉じます。
  最初の[ピボットテーブルの作成]ダイアログボックスの＜OK＞ボタンをクリック
• [ピボットテーブルのフィールド]が表示されますので、[仮年齢]を[行]ボックスへドラッグし、[ピボットのフィールド]にある同じ[仮年齢]を[Σ値]ボックスへドラッグ
  ※その際、[合計／仮年齢]と表示されたら、右にある＜▼＞ボタン、あるいは、[合計／仮年齢]の列(F列)内のデータを右クリックし、プルダウンメニューから[値フィールドの設定(N)]を選択し、[合計]を[データの個数]に変更

次図のような表が表示されます。
この表のF列[データの個数／仮年齢]が各数字の個数を表わしています。
数字の個数には、年齢に対応した｢カード番号｣の数以外に、その｢年齢｣を表した数字の個数(1個)が含まれているので、各数字から｢1｣を引いた値が年齢に対応した｢カード番号｣の数、すなわち｢カード枚数＝会員数｣となります。 ただし、｢0｣に関しては、｢総計｣の｢0｣も含まれるので、｢2｣を引く必要があります(A列の最終行を参照)。

各年齢の人数を求め、そのあとに操作を行う必要があるので、別の表を作成します。

• 表は仮の人数が表示されているピボットテーブルをコピーし、別の列へ貼り付け
  [行ラベル]は[年齢]、[データの個数／仮年齢]は[仮人数]とします。
• 次の列を、[人数式]として、その年齢の人数を求めるための式を入力
• 次の列を、[人数]として、式で求められた値を数値化

操作した結果、会員数は｢23,979人｣で、その内生年月日を登録していない会員は｢3,883人｣、生年月日登録会員は｢20,096人｣でした。

●会員の年齢特性
｢生年月日を登録している会員で、どの年代の会員が多いのか｣や｢平均年齢｣を分析します。
分析を行うために、｢生年月日｣の登録のない｢3,883人｣と総計を除いた、｢年齢｣と｢人数｣の表を作成します。この表からピボットテーブルを作成し、グラフ化することにより人数の多い年代を視覚的に確認することができます。

• H列[年齢]とK列[人数]の内、必要部分をコピーし、M列・N列に貼り付け
• [挿入]タブ⇒[グラフ]グループの[ピボットグラフ]⇒[ピボットグラフ]をクリック
• [ピボットグラフの作成]ダイアログボックスの[既存のワークシート]にチェックを入れ、[場所(L)]のボタンをクリック
  ※新規のワークシートで作成したい場合は、[新規のワークシート]をチェック
  ※元の表の範囲が示されますので、範囲が違う場合は修正します
• [ピボットテーブルの作成]ダイアログボックスのセル｢P3｣をクリック後、ウインドウを閉じます(｢×｣をクリック)
• [ピボットテーブルの作成]ダイアログボックスの＜OK＞ボタンをクリック
• [ピボットグラフのフィールド]の[年齢]を[軸(項目)]ボックスにドラッグ
• [人数]を[Σ値]ボックスへドラッグ
• ピボットテーブルとそれをグラフ化したピボットグラフが表示されます。
  ※[Σ値]の[人数]が[合計]になっていない場合は、ピボットテーブルの[人数｣の行の右クリック、あるいは[Σ値]ボックスの[人数]の＜▼＞ボタンをクリックして[値フィールドの設定(N)]を選択し、[合計]に変更します。

次に会員の｢平均年齢｣を求めます。

• 作成した｢年齢｣｢人数｣の表の次の列に、｢年齢｣と｢人数｣の積を求める式を入力
• その式をコピーし、最終行まで貼り付け
• 最終行の下の行に、計算した積の合計を求め、それを人数で割ります

計算結果は、｢51.64545183｣となったので、全体の｢平均年齢｣は｢51. 65歳｣とします。
全体の、年齢特性は、次図の表になり、ピークは40歳半ば位であるということが分かります。
これを確認するため、年齢を5歳ごとにカテゴリー化しグラフ化します。

• グラフのもとになっているピボットテーブルの[行ラベル]のデータを右クリックし、プルダウンメニューから[グループ化]を選択
• [グループ化]ダイアログボックスの[先頭の値]を｢0｣、[単位(B)]を｢5｣に設定し、＜OK＞ボタンをクリック

その結果、次のピボットテーブルとグラフに変わります。

　
グラフから40歳代の人数が多いことが分かりますが、10歳ごとの方がより分かりやすくなります。 そこで、10歳ごとにカテゴリーを変更します。 このようにカテゴリーを変更することを｢再カテゴリー｣と言い、分析を行う中ではよく使われます。

10歳ごとに再カテゴリーした結果を以下に示します。
　
ピボットテーブルの中では、一部で自動カテゴリー化を行うことが可能ですが、自動カテゴリー化ができない場合は、あらかじめ対応する分類を行っておき、それを元に分析を行います。
今回のデータでは、｢商品名｣に対応する分類として、｢部門名1｣～｢部門名4｣までがあり、それを元に分析が行えます。

店舗別・会員情報

｢店舗別｣の｢会員情報｣の分析は、前節で行った全体の｢会員情報｣の分析と同じです。
ただし、最初に店舗を特定する必要があります。

• 会員データよりピボットテーブルを作成し、[年齢]と[カード番号]を[行]ボックスへドラッグ(ここまでは、前節で行った操作)
• [店舗CD]を[フィルター]ボックスへドラッグ
• [行ラベル]の上部に[店舗CD(すべて)]の右にある＜▼＞ボタンをクリック
• プルダウンメニューから分析しようとする店舗をクリックし、＜OK＞ボタンをクリック
  ※選択した店舗の会員年齢とそれに対応する｢カード番号｣が表示されます
  ※その後は、前節で行った全体の｢会員情報｣の分析と同じ操作を行います。

ここでは、操作が同じなので、操作方法は省略し、結果だけを表示します。
(各店舗の売上は、前節の結果を流用し、会員の売上は、同じ操作を行い求めました)

店舗CD	40	41	45	46	合計
会員数	8,095	6,538	5,414	4,010	23,979
生年月日未登録会員数	769	1,300	1,290	533	3,883
生年月日登録会員数	7,326	5,238	4,124	3,477	20,096
平均年齢	51.09	50.93	50.54	55.11	51.65
全体売上(千円)	35,422	32,062	34,879	19,888	122,251
会員の売上(千円)	24,798	23,077	24,063	14,026	85,964

｢店舗46｣は他の店舗と違い、60歳代の会員が多いことが分かりました。
｢会員数｣が少ないので｢売上高｣が低くなる傾向があると思われますが、60歳代向けの商品を取りそろえることにより、売上拡大を図れる可能性があります。 ｢店舗40｣は｢会員数｣が一番多いようです。
｢売上高｣については、｢会員数｣が2,700人近く少ない｢店舗45｣とあまり変わりません。｢店舗40｣も、年代にあった商品を取りそろえることにより、売上拡大に繋がる可能性があります。

このため、｢年齢別｣の｢商品別売上高｣を分析します。

年齢別・商品別売上高

｢年齢別・商品別売上高｣を分析するため、新規のピボットテーブルを作成します。今回は、実際に商品を購入した時の状況を分析したいので、｢購入時の年齢(購入年齢)｣を使用します。

• [購入年齢]を[列]ボックスにドラッグ
• [商品名]を[行]ボックスにドラッグ
• [税抜金額]を[Σ値]ボックスへドラッグ
  ※ドラッグ後、｢税抜金額｣は、｢合計｣に名称を変更

各商品に対する、｢年齢別｣の｢売上高｣が表示されます。
一番｢売上高｣の多い44歳について、売上の多い順に商品を並べ替えます。
｢売上高｣の一番多い年齢は、次の操作で分かります。

• 先程のピボットテーブルから、[商品名]を削除
• ｢年齢別｣の｢売上高｣が表示されますので、[税抜金額]のデータを右クリックし、プルダウンメニューから[並べ替え]⇒[降順]を選択

44歳の売上が一番多いことが分かります(｢44歳｣の左にあるデータは、｢空白｣のデータで、｢生年月日｣を登録していない会員の｢売上高｣になり、今回は対象外となります)。

続いて、44歳が購入している商品で売上の多いものを分析します。

• 商品名]を[行]ボックスへドラッグ
• 商品名ごとの｢売上高｣が表示されますので、｢44歳｣の列(C列)のデータを右クリックし、プルダウンメニューから[並べ替え]⇒[降順]を選択

｢44歳｣の会員が購入した商品が売上順で分かります。
｢年齢｣の順序を元に戻して44歳前後を見ると1位は変わりませんが、2位以下は微妙に変わっています(順序の戻し方は、[年齢]のデータを右クリックし、プルダウンメニューから[並べ替え]⇒[昇順]を選択)。

50歳代後半以降では、1位と2位が逆転しています。若い人には｢洋風半・生菓子｣が人気で、50歳代後半以降には｢和風半・生菓子｣が好まれることが分かります。

この傾向を見極めるため、年齢を10歳ごとにカテゴリー化して分析を行います。
年齢をカテゴリー化するためには、ピボットのグループ化の機能を使います。

最初に10歳代(10歳～ 19歳)のカテゴリー化を行います。

• ピボットテーブルの[列ラベル]の年齢の数字(10 ～ 19まで)を範囲指定
  ※[10]をクリックし、[Shift]キーを押しながら[→]キーで[19]まで移動
• [ピボットテーブルツール]⇒[分析]タブ⇒[グループ]グループ⇒[グループの選択]をクリック
• [グループ1]をクリックし[10代](識別する名前なので意味が分かればどのような名前でも構いません)と入力して[Enter]キーを押す
  ※[10代]の＜－＞ボタンをクリックすると、10歳から19歳までのデータの総計が表示されます。
  ※この操作を20歳代以降に対して最後まで行います。

40歳代のデータで降順に並べたものは次の通りです。


2位に｢グリコジャイアントコーン｣が入っており、3位が｢和風半・生菓子｣となりました。
しかし、｢和風半・生菓子｣は60歳代以降で1位となっており、高齢者に人気があることが分かります。

それでは、商品のグループではどのようなグループが好まれるのかを分析します。
｢商品グループ｣については、すでに、データの｢部門名1｣～｢部門名4｣で分類されているので、これを使用します。 他のビッグデータの分析でも、分析目的に沿ったカテゴライズ(分類)を行ってから分析する方が効率よく行えます。

• 一旦、[行]ボックスの[商品名]を削除
• [部門名3]を[行]ボックスへドラッグ

すべての年代で｢乾菓子｣が1位ですが、種類が2,000以上あり、そのため1位になったと推測されます。

もう少し分類を細かくして分析します。
本来なら、再カテゴリー化を行いますが、今回は既にある[部門名4]を使用します。
[行ボックス]の[部門名3]を削除し、[部門名4]を[行]ボックスにドラッグします。

その表を40歳代で降順に並べ替えたものが次の40代の部分です。


40歳代は｢洋風半・生菓子｣が1位ですが、｢和風半・生菓子｣は7位です。しかし、60代以降は｢和風半・生菓子｣が1位になっています。
このように年代による違いが分かります。

次表は、この違いを分かり易くするため、30歳代・40歳代・50歳代・60歳代・70歳代の売上上位10品目を表示しています。
※表は年代別商品別売上のピボットテーブルを各年代で降順に並べ替えたものの内、｢部門名4｣をコピーして作成したものです。

順位	30代	40代	50代	60代	70代
1	スナック	洋風半・生菓子	洋風半・生菓子	和風半・生菓子	和風半・生菓子
2	チョコレート	ノベルティ アイス	ノベルティ アイス	洋風半・生菓子	洋風半・生菓子
3	洋風半・生菓子	スナック	マルチパック アイス	米菓	ノベルティ アイス
4	ノベルティ アイス	チョコレート	チョコレート	マルチパック アイス	米菓
5	マルチパック アイス	マルチパック アイス	米菓	ノベルティ アイス	マルチパック アイス
6	米菓	米菓	スナック	チョコレート	チョコレート
7	ゼリー	和風半・生菓子	和風半・生菓子	スナック	スナック
8	ビスケット	ゼリー	ゼリー	水産つまみ菓子	キャンディ
9	和風半・生菓子	プリン	プリン	ゼリー	水産つまみ菓子
10	プリン	ビスケット	ビスケット	キャンディ	和風乾菓子

多重クロス集計

前節では、｢年齢別・商品別売上高｣を分析することにより、全体での年代別の｢売れ筋商品｣が分かりました。

しかし、実際に商品を販売しているのは店舗であり、各店舗での｢年齢別｣の｢売れ筋商品｣を調べ、全体との違いを分析することにより、有益な情報を得ることがあります。
たとえば、前節で分析した会員の店舗別・年齢構成と店舗別の｢売れ筋商品｣を比較して、違いがあれば、年齢構成に適した商品があると推測できます。 そのような商品を年代別に調べ、商品プロモーションを行えば売上につながる可能性があります。

ここでは、｢店舗別・年齢別・商品別売上高｣の分析を行いますが、見方を変えると店舗別の｢クロス集計(年齢別・商品別の売上高)｣となります。 通常の｢クロス集計｣は、｢行(商品)｣と｢列(年齢)｣の2次元ですが、そこに｢別の要素(店舗)｣が入るので、3次元の｢クロス集計｣と見ることができます。

このように、2次元以上の｢クロス集計｣を｢多重クロス集計｣と呼び、ビッグデータで項目の多い分析では、よく使用されます。

Excelのピボットテーブルで｢多重クロス集計｣を行う場合、｢行｣と｢列｣以外に、フィルターを利用します。今回は、[フィルター]ボックスに[店舗CD]をドラッグして、各店舗の分析を行います。

※フィルターの使用方法は、前節の会員情報を分析する際に使用したので、説明は省略し、結果だけを示します。

店舗別・年齢別・商品別売上高

●全体

●店舗40

●店舗41

●店舗45

●店舗46

｢店舗40｣と｢店舗41｣の商品売上傾向は、全体のものと似ています。
これと比べると｢店舗46｣は全体のものと違っており、｢店舗45｣はその中間的な傾向にあります。 これは、前節で分析した会員の年齢構成によるものと思われます。

｢店舗46｣は平均年齢も他の店舗と比べ高く、60歳代が40歳代より多いので、高齢者の嗜好傾向が出ており、｢店舗46｣の地域の人口構成も、高齢者が多い可能性があります。

もし地域の人口構成で高齢者が多いとすると、本格的に高齢者向けの商品販売プロモーションを行うことにより、売上向上につながる可能性があります。

｢店舗41｣は、｢洋風半・生菓子｣が全体の半分以上を売り上げています。
年代別にみても、どの年代でも上位を占めており、この店舗だけ｢洋風半・生菓子｣のプロモーションを行った可能性があります。

地域の人口構成については、政府統計の総合窓口｢e-Stat｣などで公開されている国のデータから調べることが可能です。
参考URL
政府統計の総合窓口｢e-Stat｣
http://www.e-stat.go.jp/SG1/estat/eStatTopPortal.do

月別(季節別)・店舗別・年齢別・商品別売上高

今まで行ってきた分析では、月や季節を考慮していませんでした。
夏の暑いときはアイスクリーム系のものが売れるように、月や季節で｢売れ筋商品｣に違いがあると思われます。 この分析を実現するためには、｢店舗別｣の｢年齢別・商品別売上｣に｢利用日(利用月や利用した季節)｣を加えた4次元の｢クロス集計｣を行う必要があります。

Excelのピボットテーブルで行う場合は、[フィルター]ボックスに[利用日]をドラッグして分析を行います。 ｢利用日｣をカテゴリー化してある場合は、｢利用日2｣としてカテゴリー化した分類が｢ピボットテーブルのフィールド｣に表示されますので、これを使用します。

ここでは、暑い季節として｢7月・8月の合計｣と寒い季節として｢11月・12月の合計｣を｢店舗別｣の｢年齢別・商品別売上｣として分析します。
尚、順位は10位までとし、｢商品名｣の下に｢商品の分類｣を表示しました。

●全体(7月・8月)

●店舗40(7月・8月)

●店舗41(7月・8月)

●店舗45(7月・8月)

●店舗46(7月・8月)


7・8月は夏場なので、｢アイスクリーム系｣の売上が多いことが分かります。
特に若い年代が購入するケースが多く、種類も｢マルチパックアイス｣が多くなっています。
高齢になるとアイスでも｢あずきバー｣が上位になり、｢ノベルティアイス｣も多くなります。
また、アイス以外の物も多くなっており、｢和風半・生菓子｣は定番の様です。