売上分析をエクセルで行う！ピボットテーブルやABC分析を解説

エクセル上で大量の売上データを管理しすぎて、「ただ数字が並んでいるだけ」の状態になっていませんか？
エクセルに搭載されている「ピボットテーブル」を用いたり、「ABC分析」を行うことで、煩雑に見えるデータも簡単に整理し、解釈しやすくなります。

今回はピボットテーブルの作成方法やABC分析の実施方法をご紹介します。

ピボットテーブルを活用してデータの整理を行う

ピボットテーブルとは表形式で入力されているデータを種類ごとに整理・集計することができるツールです。使うデータを自由に選択でき、柔軟なデータ整理を行えることが長所です。

例えば、日付や商品、1回の決裁ごとに販売した個数、購入金額をまとめたデータを元にピボットテーブルを作成することで、売上分析を行うことができます。また、集計するデータに「営業担当者」や「商品カテゴリ」などを追加し、データ数を増やしても対応ができます。

ピボットテーブルの作り方

今回の例では以下のようなケースを想定します。

そして、自社にて以下のようなデータを管理しているとします。

 

1. まず、ページ上部の「挿入」タブから「ピボットテーブル」を選択します。

   

2. 売上分析に使用したいデータが「テーブル/範囲」に含まれているか、別のワークシートに作成するかどうかを決め、「OK」を選択します。

   

3. すると、以下のような「ピボットテーブル1」と「ピボットテーブルのフィールド」が表示されます 。「ピボットテーブルのフィールド」に各要素をドラッグすることで、ピボットテーブルのフィルター、列、行、値にどのデータを用いるかを選択できます。
   
4. 「ピボットテーブルのフィールド」上部で、集計に考慮したいデータを選択し、「フィルター」「列」「行」「値」の内、使用したい場所にドラッグします。
   今回の場合、顧客IDは数値として考慮はしたくないので、チェックを入れません。例えば、「フィルター」に「販売場所」をドラッグすると、以下のようなウィンドウが表示され、どこで販売されたものを集計に考慮するか選択できます。

   

5. 作成できるピボットテーブルの一例として、フィルターの欄に「販売場所」、列の欄に「値」、行の欄に「日付」、値の欄に「合計/個数」「合計/金額」を選択します。すると、以下のような「販売場所ごとに、どの商品が何日に何個・いくら売れたか」を表すテーブルを作成することができます。

   

ピボットテーブルを活用する

フィールドリストを活用してデータ整理を自在に行う

「ピボットテーブルのフィールド」で値をドラッグすることで、集計に使用するデータを自在に選択することができます。

ピボットテーブル上で右クリックし、「値の集計方法」を選択することで「データの個数」や「最大値」などのカスタマイズができます。その他にも、「ピボットテーブルオプション」から「表示形式」や「レイアウト」が調整できます。また、特定のデータのセルを選択して「詳細の表示」を選ぶことで、別シートに各データの内訳が表示されます。

今回は、フィールドリストを活用し、データ整理を自在に行うことができる例として、作成できるピボットテーブルをいくつかご紹介します。

例：日付ごとに各商品の売上個数、売上金額を整理するピボットテーブル

例：どの金額の決裁が何回行われているのかを示すピボットテーブル
（「値の集計方法」より「データの個数」を選択）

ピボットテーブルを元にグラフを作成する

ピボットテーブルを選択し、ページ上部のタブバーから「ピボットグラフの分析」を選びます。そこで「ピボットグラフ」を選択することで、ピボットテーブルを元にグラフを作成できます。例えば、次のようなピボットテーブルをグラフ化します。

すると、次のようなグラフを得ます。

グラフを作成することで、各商品の日付ごとの売上の増減が確認しやすくなります。
また、ページ上部の「デザイン」タブや「表示形式」タブからグラフのカスタマイズを行うことができます。

可視化された売上情報を解釈する

売上データでピボットテーブルを作成することで分析した後に、ピボットグラフを作成して可視化することで、一見煩雑に見えるデータも解釈しやすくなります。また、ピボットテーブルは柔軟に変更でき、簡単にグラフを作成することができるので、目的に応じた分析・解釈が可能です。

このように、ピボットテーブルを活用することで、商品・カテゴリ・担当者・日付ごとのデータの解釈が可能になり、リアルタイムな売上分析を実施できます。

ABC分析を行い、優先する商品を決める

ABC分析とは、商品を3つの指標で分類する分析手法

ABC分析とは、重視する指標に応じて、データを3つに分類する方法です。売上分析を行う場合、評価の指標として売上高や販売個数が考えられます。ランクの順番が高い順にA→B→Cとなることから、ABC分析という名がついています。

エクセルでABC分析を行う方法

商品V、W、X、Y、Zについて、以下のような販売データを持っているとします。

1. まず、ABC分析を行うには商品ごとの売上や累積売上割合を出す必要があります。
   同じシート内の別のセルに、

   =SUMIF(商品名が書いてある列番号:商品名が書いてある列番号,”商品名“,金額が書いてある列番号:金額が書いてある列番号)

   今回の場合、「=SUMIF(C:C,”V”,E:E)」とすると、Vの販売金額の合計が得られます。
   それぞれの商品についての販売金額とすべての商品の売上金額を計算し、以下のようなデータとして大きい順に整理します。

   

2. 次に、累積売上高と累積売上割合を計算します。
   各商品の売上高を求めたセルの隣に、下図のような式を入力し、オートフィル機能でZまでの商品の累積売上高を求めます。

   

3. 次に、累積売上割合を求めます。
   下図のような式を入力し、オートフィル機能を使用します。

   

4. ABC分析では、累積売上割合の大きさに応じてランク付けを行います。
   今回の場合、以下のような結果を得ます。

   	売上	累積売上高	累積売上割合
   Vの販売金額の合計	709000	709000	47.61584956
   Wの販売金額の合計	254000	963000	64.67427804
   Xの販売金額の合計	230000	1193000	80.1208865
   Yの販売金額の合計	162000	1355000	91.00067159
   Zの販売金額の合計	134000	1489000	100

   この結果から、売上割合の多くを占める商品Vを「ランクA」、あまり売上に貢献していない商品YとZを「ランクC」、その間のWとXを「ランクB」とすることができます。

より詳しいABC分析の説明やグラフを活用したABC分析の行い方は、以下の記事を参考にしてください。

合わせて読みたい記事

ABC分析とは？効率的に在庫管理を行う方法を図解付きで解説！在庫管理を行いやすくするために、商品にランクをつけるABC分析をご存知でしょうか？ 今回はExcelを用いた具体的なABC分析の方...