エクセル業務効率化!【ピボットテーブル】で集計・速攻ABC分析

スポンサーリンク

【Excelピボットテーブルとは】

Pivot Table

ピボットテーブルとは、選択範囲のデータを簡単に集計・グラフ化できるExcelの機能です。

関数を使う事で同様の集計を行う事もできますが、ピボットテーブルの方が簡単かつ応用が利くため、Excel機能の中では”ピボットテーブル最強論”がある程、便利な機能です。

【なぜ難しいと思われているか】

えーと

関数は知っているけど、ピボットテーブルを知らない・使った事がないという方が多々いらっしゃいます。

これは集計・分析の知識や需要がまだまだ認知されていなかったため、学校や社内勉強会といった一般的な使い方を教える場で、関数は教えるけどピボットテーブルは教えていなかったという問題が挙げられます。

しかし、実際に使ってみるとピボットテーブルとは、とても簡単で便利な機能ということがわかります。

つまりピボットテーブルが難しいと思われているのは、「難易度が高い→難しい」ではなく、「単に知らなかった→難しい」ことから、早期の習得・活用が可能です。

【実践ピボットテーブル活用術】

ふむふむ

それでは実際にピボットテーブルで、仮にECの購買データを集計・分析するというケースで、下記の3つの使い方を説明します。

・クロス集計

・ピボットグラフ

・ABC分析(パレード分析)

そして今回使用するサンプル購買データはこちら。

実際のRawデータの場合は何万件という膨大な量となりますが、シンプルな説明にするため、サンプル件数は10件です。実際のRawデータの場合も選択範囲が増えるだけで作業は同じです。

サンプル表

《クロス集計》

11

まずは基本的な使い方。クロス集計。これはデータから簡単に様々な集計ができます。

例えば、サンプル購買データから「商品区分別の件数」と「合計売上」を集計したい、とした場合、下記3つの手順となります。

①対象データを選択、”ピボットテーブル”をクリック。OKをクリック。

ピボットテーブル

ピボットテーブル2

②右上欄で集計したい項目を選択。右下欄で各項目を該当欄にクリック&ドロップ。

ピボットテーブル3

③以上で「商品区分別の件数と合計売上」の集計が完了!

※右の欄で項目を変えると、様々な軸で再集計が可能です。

ピボットテーブル4

《ピボットグラフ》

12

次はピボットグラフです。これは先ほどのクロス集計と同時にグラフ化ができる機能です。

「クロス集計」時と同様に下記3つの手順となります。

①対象データを選択、”ピボットグラフ”をクリック。OKをクリック。

ピボットテーブル5

ピボットグラフ2

②右上欄で集計したい項目を選択。右下欄で各項目を該当欄にクリック&ドロップ。

ビポットグラフ3

③以上で集計&グラフ化が完了!グラフがあると直感的に理解しやすい。

ピボットグラフ4

《ABC分析表(パレード分析)》

13

最後に、応用編となるABC分析表を作ってみましょう。

ABC分析とは、簡単に言うと、重要度の高い範囲・対象を明確にする分析手法です。

例えば、「全売上構成比の80%を占めている注力商品群とそのリスク」や「売上構成比の80%を構成するロイヤルユーザー・ユーザー数」 などを直感的に把握することができます。

今回は「全売上構成比の80%を占めている注力商品群とそのリスク」を分析してみます。

この分析は、ある程度のサンプル数が必要になるので、先ほどのサンプルデータにデータを追加して分析しています。

手順①②③まではピボットグラフと同様。

手順はピボットグラフ①〜③を参照。③後の下記状態から説明します。

パレード分析1

④累積構成比の追加

ABC分析に必要な累積構成比を追加します。

パレード分析2

⑤並び替えとグラフの2軸化・折れ線グラフに変更

降順に並び替えて、累積構成比を折れ線グラフで表示させます。

パレード分析3

⑥パレード分析表がほぼ完成

軸のメモリの最大値などを調整してパレード分析表を完成させましょう。

パレード分析表

⑦分析

パレード分析6

整理されたこのABC分析表は、単なる数値表と違い、視覚的・直感的に状況が把握できます。

たとえば上図グラフであれば、赤と黄色部分で売上構成比約80%。

しかし赤が突出し過ぎているため、これら赤2品番の在庫が無くなれば、極端に言うと売上30%減となるリスクがあり、赤の在庫があるうちに黄色の販促を強化して全体売上の最適化を図るといった施策を検討します。

もちろん、青を売りたいところですが、人気がない青を打ち出すことは全体の売上低迷のリスクが高まり、それらは慎重に判断しなければなりません。

【総括】

横

以上、「業務効率化!購買データをExcelピボットテーブルで集計・速攻ABC分析」でした。

他にも購買データから分析する内容の一例としては下記ケース等があります。

これらは関数でも計算はできますが、ピボットテーブルであれば、集計・グラフ化がより簡単にできることをご理解頂けたかと思います。

※購買データ×ピボットテーブル活用参考ケース

・会員と非会員の割合

・都道府県別の割合

・アイテム別の割合

・購入回数の割合

・購入期間(1年間をアクティブ会員と設定)

・決済方法の割合

集計や分析は、あくまで”打ち手”を決めるための準備なので、極力時間は掛けずに素早く正確に行えるようにピボットテーブルを活用しましょう。

スポンサーリンク
  • このエントリーをはてなブックマークに追加

コメントをどうぞ

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>