第6回　多重クロス集計2

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

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

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

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

ID-POSのデータを使ったExcelによる多重クロス集計の仕方を動画で紹介しています。

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

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

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

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

1. データを開き[データ]タブの[並べ替えとフィルター]グループの[フィルター]をクリック
2. G列[カード番号]の＜▼＞ボタンをクリック
3. プルダウンメニューの｢0｣のチェックを外し、＜OK＞ボタンをクリック
4. 抽出された会員データを範囲指定してコピー
5. 新しいブックを開き、コピーしたものを貼り付け

会員数

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

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

実際に求めると「23982（カード番号が入っているセルの最終行番号）-3=23979」となります。

年齢を登録している会員数

｢年齢別｣に｢カード番号｣を並べ、それを数えることで求めます。次の操作を行います。

1. [ピボットテーブルのフィールド]の[12月年齢]を[行]ボックスの[カード番号]の上にドラッグ
2. セル｢C列(C3)｣に｢仮年齢｣という項目を作り、次の式をセル｢C4｣に入力
   =IF(A4=" ",0,IF(A4<200,A4,C3))
3. 入力した式をコピーし、データのある最後の行まで貼り付け
4. [仮年齢]が表示されているセル｢C3｣をクリックし、ピボットテーブルを作成
5. [ピボットテーブルのフィールド]が表示されますので、[仮年齢]を[行]ボックスへドラッグし、同じ[仮年齢]を[Σ値]ボックスへドラッグ

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

会員の年齢特性

｢生年月日を登録している会員で、どの年代の会員が多いのか｣や｢平均年齢｣を分析します。

1. H列[年齢]とK列[人数]の内、必要部分をコピーし、M列・N列に貼り付け
2. [挿入]タブ⇒[グラフ]グループの[ピボットグラフ]をクリック
3. [ピボットグラフの作成]ダイアログボックスの[既存のワークシート]にチェックを入れ、[場所(L)]のボタンをクリック
4. [ピボットテーブルの作成]ダイアログボックスのセル｢P3｣をクリック後、ウインドウを閉じます
5. [ピボットテーブルの作成]ダイアログボックスの＜OK＞ボタンをクリック
6. [ピボットグラフのフィールド]の[年齢]を[軸(項目)]ボックスにドラッグ
7. [人数]を[Σ値]ボックスへドラッグ
ピボットテーブルとそれをグラフ化したピボットグラフが表示されます。

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

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

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

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

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

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

1. 会員データよりピボットテーブルを作成し、[年齢]と[カード番号]を[行]ボックスへドラッグ
2. [店舗CD]を[フィルター]ボックスへドラッグ
3. [行ラベル]の上部に[店舗CD(すべて)]の右にある＜▼＞ボタンをクリック
4. プルダウンメニューから分析しようとする店舗をクリックし、＜OK＞ボタンをクリック

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

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

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

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

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

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

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

｢44歳｣の会員が購入した商品が売上順で分かります。
｢年齢｣の順序を元に戻して44歳前後を見ると1位は変わりませんが、2位以下は微妙に変わっています。

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

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

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

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

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

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

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

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

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

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

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

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

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

前節では、｢年齢別・商品別売上高｣を分析することにより、全体での年代別の｢売れ筋商品｣が分かりました。各店舗での｢年齢別｣の｢売れ筋商品｣を調べ、全体との違いを分析することにより、有益な情報を得ることができます。

通常の｢クロス集計｣は、｢行(商品)｣と｢列(年齢)｣の2次元ですが、そこに｢別の要素(店舗)｣が入るので、3次元の｢クロス集計｣と見ることができます。このように、2次元以上の｢クロス集計｣を｢多重クロス集計｣と呼びます。

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

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

●全体

●店舗40

●店舗41

●店舗45

●店舗46

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

｢店舗46｣は平均年齢も他の店舗と比べ高く、60歳代が40歳代より多いので、高齢者の嗜好傾向が出ており、地域の人口構成も高齢者が多い可能性があります。地域の人口構成については、政府統計の総合窓口｢e-Stat｣などで調べることが可能です。

参考URL：政府統計の総合窓口｢e-Stat｣
http://www.e-stat.go.jp/SG1/estat/eStatTopPortal.do

今まで行ってきた分析では、月や季節を考慮していませんでした。夏の暑いときはアイスクリーム系のものが売れるように、月や季節で｢売れ筋商品｣に違いがあると思われます。

ここでは、暑い季節として｢7月・8月の合計｣と寒い季節として｢11月・12月の合計｣を｢店舗別｣の｢年齢別・商品別売上｣として分析します。

暑い季節（7月・8月）

●全体(7月・8月)

●店舗40(7月・8月)

●店舗41(7月・8月)

●店舗45(7月・8月)

●店舗46(7月・8月)

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

寒い季節（11月・12月）

●全体(11月・12月)

●店舗40(11月・12月)

●店舗41(11月・12月)

●店舗45(11月・12月)

●店舗46(11月・12月)

11・12月は、7・8月と比べるとアイスの売上が少なくなっています。しかし、｢店舗45｣と｢店舗46｣の若い世代では、アイスの売り上げの割合が多くなっています。このように、｢売れ筋商品｣を見極めるためには、年代や季節を考慮する必要があります。また、場合によってはその地域の特性も考慮する必要があります。