1. ホーム
  2. データ活用塾
  3. ビッグデータ活用講座
  4. 第3回 単純集計とカテゴライズ

第3回 単純集計とカテゴライズ
Excelを使った単純集計とカテゴライズ

単純集計とカテゴライズ

今回は、クレンジングされたデータを用いて、仮説検証するための分析を行います。 最初にデータの傾向を見るために「単純集計」を行います。
クレンジングしたデータをExcelに展開し、最後の列を「売上高」とし、「単価」×「数量」の式を作り表中の「列」にコピーして「売上高」の入った表を作ります。 以降、この表を使用して分析を行います。

実際に皆さんにも試してもらえるようにサンプルデータを用意しました。
サンプルデータは<こちら>からダウンロード
※単純集計を行う前にデータクレンジングを行ってください。
※データクレンジングの方法は、「第2回 データクレンジング」を参照してください。
Excelを使った単純集計とカテゴライズの仕方を動画で紹介しています。
項目ごとにご覧いただく方法と、まとめてご覧いただく方法がございます。
【項目ごとにご覧いただく場合】
ご覧になりたい項目をクリックすると、別ウィンドウで動画が再生されます。

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

担当者別売上高

「担当者」に関する施策と仮説
施策:「売上高」の低い「担当者」の売上向上
仮説:「担当者」毎の「売上高」に大きな違いがある
これを検証するために「担当者別」の「売上高」を調べます。
Excelのピボットテーブルで、次のような操作を行います。
  • [担当者]を[行]ボックスにドラッグ
  • [売上高]を[値]ボックスにドラッグ

「担当者別」の「売上高」が表示されます。
単純集計で「担当者別売上高」の傾向をつかみ、次のステップへ移ります。 「青島さん」の売上が低いことが分かります。 売れていない原因と対策については、次回のクロス集計で行います。

得意先別売上高

「得意先」に関する施策と仮説
施策:「取引高」の低い「得意先」の取引高向上
仮説:「取引先」毎に「売上高」に大きな違いがある
これを検証するために「得意先別」の「売上高」を調べます。
Excelのピボットテーブルで、次のような操作を行います。
  • [得意先名]を[行]ボックスにドラッグ
  • [売上高]を[値]ボックスにドラッグ

「得意先別」の「売上高」が表示されます。
「売上高」の行を昇順に並び替え、傾向をつかみます。




  • [合計/売上高]の列で数値の入っているセルをクリック
  • [データ]タブの[並べ替えとフィルター]グループの[並べ替え]の<昇順>ボタンをクリック
グラフ化すると次のようになります。
上位3社と下位3社が明確に分かります。 上位3社と下位3社の「取引高分析」を行い、「売れる原因・売れない原因」を調べ、得意先対策を強化します。

月別売上高

「売上年月」に関する施策と仮説
施策:「売上高」の低い月の拡販
仮説:「売上高」の月変動が大きい
これを検証するために「月別」の「売上高」を調べます。
Excelのピボットテーブルで、次のような操作を行います。
  • [売上年月]を[行]ボックスにドラッグ
  • [売上高]を[値]ボックスにドラッグ

「月別」の「売上高」が表示されます。 3月の売上が他の月と比べて明らかに少ないことが分かります。 なぜ3月の売上が少なくなったかが分かれば、売上向上につながる可能性があります。






商品別売上高(カテゴリー化)

「商品」に関する施策と仮説
施策:「売れない商品」の拡販
仮説:「売れる商品」と「売れない商品」がある
これを検証するために「商品別」の「売上高」を調べます。
Excelのピボットテーブルで、次のような操作を行います。
  • [商品名]を[行]ボックスにドラッグ
  • [売上高]を[値]ボックスにドラッグ
  • [合計/売上高]の列で数値の入っているセルをクリック
  • [データ]タブの[並べ替えとフィルター]グループの[並べ替え]の<降順>ボタンをクリック

「商品別」の「売上高」が表示されます。 「パソコン」関係が上位を占めています。 種類が同じ商品群の比較を行い、「売れ筋の商品群」を見極めることも重要です。 このように、ある意味をもったグループに集約することを「カテゴライズ(カテゴリー化)」といいます。

「商品CD(コード)」を、次の内容でカテゴリー化します。

商品コード内容
10000番台パソコン本体
20000番台周辺機器
30000 ~ 40000番台業務システム
50000 ~ 60000番台アプリケーション
70000 ~ 90000番台消耗品他

カテゴリー化を行う主な方法としては次の2つの方法があります。

① ピボットテーブルを使ってカテゴリー化を行う

  • 商品CD]を[行]ボックスにドラッグ
  • [行ラベル]の「10001」から「10099」までを範囲指定
  • [ピボットテーブルツール]の[分析]タブにある[グループ]の[グループの選択]をクリック
  • [グループ1]に「PC本体」と入力します。
    以下同様の操作で、「周辺機器」から「消耗品他」までカテゴリー化を行います。

② Excelの関数を用いてカテゴリー化を行う

  • 元データに「商品カテゴリー」という列を作ります。
  • 「商品カテゴリー」の列に次の式を入力し、最終行までコピー
  • =IF(C2<20000,"PC本体",IF(C2<30000,"周辺機器",IF(C2<50000,"業務システム",IF(C2<70000,"アプリケーション","消耗品他"))))
    
「商品CD」をカテゴリー化した後、カテゴリー別の「売上高」を調べます。
ピボットテーブルで、次のような操作を行います。
  • [商品名]を[行]ボックス(最下位)にドラッグ
  • [売上高]を[値]ボックスにドラッグ


右上図のような表が表示されますので、カテゴリー名の<->ボタンをクリックします。




右下図の表になりカテゴリー別の合計値が表示されます。




「PC本体」の売上が全体の半分以上を占め、「PC本体」以外の売上強化が望まれます。
「PC本体」以外の商品でも、売れているものがある可能性があるため、カテゴリー名の<+>ボタンをクリックして「商品名」を表示し、「商品別の売上高」を調べます。
グラフ化すると次のようになります。



周辺機器や業務システムの中にも売れている商品があるので、それ以外の商品の売上強化が望まれます。 「売上高」に注目してきましたが、「アプリケーション」や「消耗品他」の商品については、安価な量販品として大量に売る必要があります。 そこで、どれくらいの「数量」を販売したのかを調べてみます。
ピボットテーブルで、次のような操作を行います。
  • フィールドの[売上高]のチェックを外す。
  • フィールドの[数量]を[値]ボックスにドラッグ

ほとんどが800台の数値になっていることが分かります。 この結果、安価な量販品販売に対する戦略の見直しが必要と思われます。
以上、「担当者別」「得意先別」「月別」「商品別」の売上高を単純集計してみましたが、課題と思われることが見いだされました。(次表参照)

項目課題と思われる事象
担当者別売上青島さんの売上が他と比べて低い
得意先別売上上位3社と下位3社の差が大きい
月別売上3月の売上が低い
商品別売上PC本体以外の売上が低い(売上高の高いものもある)
商品別販売個数安価な量販品の販売個数がPC本体等の高価な物と同じ

課題と思われる事象は、クロス集計を行い、課題かどうかを判断します。

まとめ:単純集計とカテゴライズのポイント

以上、クレンジングしたデータを用いて単純集計を行ってきましたが、ポイントをまとめると次のようになります。

  • 目的(今回は売上向上)にあった施策を考え、施策を実現できそうな項目を選定する。
  • その項目の傾向を見る。
  • 傾向を見るため、グラフを利用することも考える。
  • 複雑な場合は、平均からどれだけ違うのかも考慮する。



< 第2回 データクレンジング
第4回 Excelを使ったクロス集計 >