第4回　クロス集計
Excelを使ったクロス集計

クロス集計

前回は「担当者」「得意先」「商品」「売上年月」に対する「売上高」を調べ、課題と思われるものを見つけだすことができました。
本当に課題なのかを調べるには、「クロス集計」が最適です。
まず、「青島さん」は何がネックで売れないのかを調べます。

・「得意先」で売上の少ない所がネックなのか。
・「商品」で売れないものがあることがネックなのか。
・「売れない月」があることがネックなのか。

「得意先」「商品」「売上月」に対しても同様のことを調べます。
調べる組合せとしては、次の6つとなります。

・担当者・得意先
・担当者・商品
・担当者・売上年月
・得意先・商品
・得意先・売上年月
・商品・売上年月

これらに対する「売上高」を調べます。
この様な2つ以上の項目に対する集計のことを「クロス集計」といいます。

※クロス集計を行う前にデータクレンジングを行ってください。
※データクレンジングの方法は、「第2回 データクレンジング」を参照してください。

Excelを使ったクロス集計の仕方を動画で紹介しています。
項目ごとにご覧いただく方法と、まとめてご覧いただく方法がございます。

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

担当者別・得意先別売上高

担当者ごとの得意先別売上高を調べます。

必要に応じて新しいピボットテーブルを作成します。

• 分析するデータが表示されているExcelの表中のセルをクリック
• ［挿入］タブをクリック
• ［テーブル］グループの［ピボットテーブル］をクリック
• ［ピボットテーブルの作成］ダイアログボックスの［新規ワークシート］をチェック
• ＜OK＞ボタンをクリック

ピボットテーブルで、次の操作を行います。

• ［担当者］を［列］ボックスにドラッグ
• ［得意先名］を［行］ボックスにドラッグ
• ［売上高］を［値］ボックスにドラッグ

「青島さん」の売上を調べると、ほとんどの得意先で最下位でした。

分かりやすくするためにグラフ化します。

• ［分析］タブをクリック
• ［ツール］タブの［ピボットグラフ］をクリック
• ［グラフの挿入］ダイアログボックスの［折れ線グラフ］をクリック
• 折れ線グラフの種類を選択する画面で［マーカー付き折れ線］を選択

ほとんどの得意先で「青島さん」が最下位であることが分かります。 他の担当者についてみると、「得意先」により「売上高」の順位が変わっていることが分かります。 「担当者」によって得意な販売先があることが想像できます。「担当者」の得意分野が分かれば、お客様ごとに担当者を決めることで、売上が向上する可能性があります。

担当者別・商品別売上高

「担当者」によって「売りやすい商品」があるのかを調べます。
また、「商品」のカテゴリー別集計も行ってみます。

• ［商品CD2］を［行］ボックスにドラッグ
• ［商品名］を［行］ボックスの［商品CD2］の下にドラッグ
• ［担当者］を［列］ボックスにドラッグ
• ［売上高］を［値］ボックスにドラッグ

カテゴリー名の＜－＞ボタンをクリックすると、カテゴリー別の売上高が表示されます。
1位から3位までの売上はそれほど変わりません。4位も少々低い程度です。
5位の「青島さん」だけ相当低いです。
分かりやすくするために、総計に対する「割合」を示します。

• ［値］ボックスの［合計／売上高］＜▼＞ボタンをクリック
• ポップアップメニューから［値フィールドの設定］をクリック
• ［値フィールドの設定］ダイアログボックスの［計算の種類］タブをクリック
• ［計算の種類］の＜∨＞ボタンをクリック
• ［計算なし］の下の［総計に対する比率］をクリックして＜OK＞ボタンをクリック

カテゴリー別の割合が表示されます。
「青島さん」が最下位で、他はそれほど差がないことが分かります。

グラフ化してみると、「青島さん」が全て最下位。「池本さん」は「業務システム」「アプリケーション」で１位。「福本さん」は「業務システム」「アプリケーション」以外が１位。「松田さん」は総合で２位。「小森さん」は全て４位です。この結果から担当者による売りやすい分野がある可能性が分かってきました。

カテゴリー別にグラフ化します。
該当データをコピーし、ピボットテーブル以外の領域に貼り付け、グラフ化します。

「商品別」の「売上高」の傾向は、カテゴリー別と同じようです。
担当者によって、特定の商品の売り上げが大きくなっていないので、売りやすい商品はないようです。

ここまでは「売上高」で分析してきましたが、「単価」の高いものは「売上高」も大きくなる可能性があります。 そこで、担当ごとにどんな商品を何個売ったかを調べます。

• ［売上高］フィールドのチェックを外します。
• ［数量］フィールドにチェックを入れます（□をクリック）。

「青島さん」は最下位。「福本さん」は1位です。 「単価」の低いものが「数量」も多いようで、それほど差がありません。 この原因について考える必要があります。

担当者別・売上年月別売上高

「月別」の「売上高」は、一般的にはボーナス月や年末・決算月に多くなる傾向があり、個人別においても同様です。 このような傾向がみられない場合、この問題を解決することにより、売上向上につながる可能性があります。
ピボットテーブルを使い「担当者別・月別売上高」の表を作成すると、次のようになります。


3月の「売上高」が低くなっています。分かりやすくするため、総計だけをグラフ化します。


3月以外はほとんど変動していないことが分かります。
「小森さん」の3月のデータが空白になっており、これが原因で3月の売上が落ちています。 休暇だったのかデータの入力漏れなのかを調査し、データの入力漏れだったら、データを入力して今までと同じような分析を行う必要があります。
「担当者別」の「月別売上高」をグラフ化すると次のようになります。


「小森さん」は7月に、「小森さん」以外は10月に売上の落ち込みがあります。また、ボーナス月（6月・12月）にも売上が伸びていないので、それぞれ考える必要があります。

得意先別・商品別売上高

「得意先」によって「売りやすい商品」があるなら、その「商品」を専門に売る、あるいは重点的に売ってもらうようにすると、売上の向上につながります。または、多くの「販売店」で「売上高」が1位の「商品」を重点販売商品として販売戦略をたてる方法もあります。
ピボットテーブルを使って、このような傾向がないかを調べてみます。
結果が表示されたら、「得意先」の「売上高」の総計を昇順に並び替えます。
（総計の数値のあるセルを右クリックし、［並べ替え］＞［昇順］とクリック）


グラフ化すると、次のようになります。


「売上」の半分以上が「PC本体」で占められています。「売れ筋商品」が特定できれば、拡販の施策を立てることが可能です。 「売れ筋商品」を特定するため、「PC本体」の商品ごとの各得意先における「売上高」を調べるためにグラフ化を行います（ピボットテーブル以外のセルにデータをコピーし、グラフ化します）。


「FMGP-END」が14社で売上1位となっています。 売れ筋のように思えますが、集計対象が34社で、半数に満たっていません。 1位ではない「得意先」での評判を調査し、売れ筋かどうかの判断をした方が良いでしょう。 「周辺機器」のMOディスクドライブ「モチャンバド」、「消耗品他」の「インクリボンの騎士」は半数以上の得意先で1位となっておりますが調査が必要です。

得意先別・月別売上高

得意先毎の「月別」の「売上高」を調べ、「売れる月」「売れない月」を分析して売上向上につなげます。ピボットテーブルを使用して分析を行います。


「得意先」によってデータのない月がありました。 売上下位「こあら販売」、「ヤマネコ販売」です。 データの入力漏れ、新規取引、あるいは取引中止なのか調査が必要です。

「こあら販売」が10月からの新規取引だとすると、11月・12月の売上が得意先内で上位を占め、来年からの売上に期待が持てます。

「ヤマネコ販売」はデータの抜けだとすると、6ヶ月分の売上を加味した年間の「売上」は上位になる可能性があります。 取引中止であれば、その理由を解明し、再発防止策を立てることで売上向上に結び付きます。

データのない月がある得意先は、これ以外にも4社あり、その内2社は上位3社です。これらは、売上向上のため調査が必要です。

得意先全般に関して、売れる月・売れない月の傾向があるかを調べるため、1月～ 12月の売上を得意先別にグラフ化してみます。
（横軸を「月」・縦軸を「売上高」とし、各得意先別に折れ線グラフにします）
（横軸が「得意先」になるので、「表頭」と「表側」を入れ替えてグラフ化を行います）

次のような操作を行います。

• 現在のピボットテーブルでグラフを作成
  [ピボットテーブルツール]→[分析]タブ→[ツール]グループの＜ピボットグラフ＞ボタン→＜折れ線＞ボタン→＜マーカー付き折れ線＞ボタン→＜OK＞ボタンをクリック
• 表示されたグラフをクリックすると、［ピボットグラフツール］が表示されますので、その中の［デザイン］タブをクリック
• ［データ］グループの＜行/列の切り替え＞ボタンをクリック
  （テーブルが変更され、グラフも変更されます）

多くの「得意先」で、3月の売上が落ち込んでいますが、 「小森さん」の3月の「売上」が計上されていないためであると思われます。 また、「得意先」により、売上のピークや底はまちまちで、月による増減の変動が大きいです。

グラフからは、情報が読み取りにくいので、グラフの「得意先名」のフィルター機能を使い、5社程度に分けて表示させると読取りやすくなります。

操作方法は、次の通りです。

• グラフ内の「得意先名」横の＜▼＞ボタンをクリック
• ダイアログボックスが表示されますので、「（すべてを選択）」のチェックを外します
• 得意先名に付いていたチェックがすべて外れますので、売上を見たい得意先名をクリックし、＜OK＞ボタンをクリック

任意に6社を選択して表示させると、次のようになります。
3月の売上が落ちており、各社増減の変動が激しいことが分かります。

商品別・月別売上高

時期により売れる商品が分かると、売上向上につながりやすくなります。
特に年末商戦やボーナス商戦と呼ばれる時期に「売れ筋商品」が分ると良いです。
ピボットテーブルを使って、そのような傾向があるのかを調べてみます。

結果は次のようになります。

グラフ化すると、次のようになります。

カテゴリー別では、3月の売上がすべて低くなっていますが、「小森さん」の売上の影響と思われます。それ以外は季節による変動はあまり見られません。 商品別に調べると「PC本体」は次のようになります。 ピボットグラフのフィルター機能を使いグラフ化します。

操作方法は、次の通りです。

• グラフの［商品CD2］横の＜▼＞ボタンをクリック
• ダイアログボックスが表示されますので、［（すべてを選択）］のチェックを外します
• カテゴリー名に付いていたチェックがすべて外れるので、［PC本体］にチェックを入れ、＜OK＞ボタンをクリック
  （グラフとピボットテーブルの表が「PC本体」だけの売上に変わります）
• ピボットテーブルの表頭［PC本体］の左横にある＜＋＞ボタンをクリック
  （テーブルは、「PC本体」の商品別・月別に変わり、同時にグラフも変わります）

結果は、次の通りです。

「PC本体」以外についても、同様に操作してグラフ化してみると、全てのカテゴリーのほとんどの月で売上1位の「商品」があります。
一見売れ筋商品のように見えますが、「売上高」から「個数」に変更すると、他の商品と売れている「個数」はあまり変わらないことが分かります。 このため、「売れ筋商品」とは言えません。 また、季節による変動は「商品」でも見られません。
　
　

あまり傾向が見られないので、ピボットテーブルに「得意先名」と「担当者」をフィルターとして追加し、「得意先別担当者別の月別売上高」を調査します。

操作方法は、次の通りです。

• ［担当者］と［得意先名］を［フィルター］ボックスにドラッグ
• ピボットテーブルの上にある［担当者］と［得意先名］のフィルターから目的のものを選択（ピボットテーブルに結果が反映されます）

「福本さん」の「いろはに通信販売」の「月別・商品別売上高」は、次の通りです。

カテゴリー別に売れていない月がありますが、全体ではその様な月はないので、誰か他の人が売っていることなります。 商品別には、もっと顕著にそれが現れています（次表参照）。


以上、クロス集計による分析を行ってきましたが、各クロス集計で推察されたことをまとめると、次のようになります。

集計方法	推察事項
担当者・得意先別	「青島さん」がほとんどの得意先で最下位（1ヵ所だけ1位がある） 他の担当者も1位の得意先がある 　→担当者により得意な得意先がある可能性有
担当者・商品別	「担当者」により売りやすい商品がある可能性有 　→個数による集計では大差無し（売りやすい商品はない）
担当者・売上年月別	3月の「小森さん」のデータがない 　→総計などへの影響有 上記以外、あまり変化なし
得意先・商品別	「PC本体」の売上が半分以上 売上の高い商品もある 　→個数を考えると売れ筋商品ではない
得意先・売上年月別	データのない月がある 　→総計などへの影響有 月別の増減の変動が大きい
商品・売上年月別	各カテゴリー別商品で1位の物がある 　→個数を考えると売れ筋ではない 「担当者別・得意先別」に集計すると、データのない所がある 　→他の担当者が販売している

この結果を踏まえて、売上拡大を目指すには、次の施策が必要です。

① 売上の低い担当者への対策を行う。
② 担当者毎に担当得意先を決め、得意先戦略をたて販売する。
③ 売りやすい商品を見極め、販売戦略を立てる。
④ 売上が伸びると思われる時期を見極め販売する。