ひとりマーケティングのためのデータ分析

手順解説 | Excel(エクセル)でおこなうビジネスデータの分析

How-to

ピボットテーブル機能に特化してつくる

ABC分析表

このページは ABC分析表の作成 with Excel の補足ページです。

エクセル 2010 でピボットテーブル機能が強化されたことにより、ABC分析表の作成もより効率的にすすめられるようになりました。そこでこちらのページでは「ABC分析表の作成 with Excel」で紹介した手順を Excel 2010, 2013 および 2016 のピボットテーブル機能にフィットするように見直していこうと思います。

以下、「ピボットテーブル」機能のみで完結する Excel での ABC分析表の作り方です。例示は Excel 2010 でおこないます。また Excel 2013, 2016 については、一部操作対象が異なる箇所があります(その旨当該部分に明記しています)。

元データ

晴花

1

元のデータです。未集計(クロス集計していない)の状態で、取引のレコードを単純に並べた表です。

レコード数(データ行の数)は 218 件ですが、下では一部のみ表示しています。

※ すべてのデータはサイドバーのボタンからご利用いただけます。

元データ・ABC分析(エクセル2010ピボットバージョン)

晴花

2

上のデータをもとに、ここでは「社員コード」別の “登場回数” で ABC分析表 をつくります(ちなみにこのサイトでは “登場回数” のことを「ポイント」と呼んでいます)。

IDの別にポイントを集計する

晴花

3

では、データ領域の任意のセル(空白でないセル)をアクティブにしてから、リボンの挿入タブテーブルグループにあるピボットテーブルの挿入ボタンをクリックします。

晴花

4

ピボットテーブルの作成ダイアログが開きます。ここでは正しいデータ領域がテーブル/範囲のテキストボックスに指定されているかを確認し、問題がなければ既定の状態のままOKボタンをクリックします。

※ 状況に応じて既存のワークシートに作成してもよいかと思います。

晴花

5

新しいシートが追加され、画面右端にピボットテーブルのレイアウト用のウインドウが表示されました(設定により異なる場合があります)。

ピボットテーブル・レイアウトウインドウ

晴花

6

ピボットテーブルのフィールドリスト(画面右の 日付・社員コード・サービスコード と表示されている枠)から社員コードをドラッグし、下の行ラベル枠内でドロップします。

すると行ラベル枠に「社員コード」と表示されると思います。

2013

2016

変更された表記:行ラベル

晴花

7

ここでシートの左の方を見ると 「行ラベル」という見出しと社員コードがズラーッと表示されているのがわかります。

晴花

8

さて、前の手続きと同様にして、再び社員コードをドラッグし、今度は下の枠内でドロップします。この操作を計 3 回、繰り返します。

(1 つめのデータは登場回数のカウントに、2 つめのデータは構成比の表示のために、3 つめのデータは累積の構成比の表示のために使用します)

晴花

9

この時点でシートの左の方を見ると、あらたに「合計/社員コード」という見出しとともに、一見でたらめに見える一連の数字の羅列が表示されています。これは「社員コード」を Excel が数字として判断したことによるものです。つまりは、社員コードの登場回数分だけ、数字としてみた社員コードを合計した値となっています。

金額をベースとしたABC分析…ならばそれでよいのですが、今回は回数をベースとした分析です。したがってこのままでは不都合ですので、次の工程で回数をカウントするよう指示を加えます

晴花

10

ピボットテーブルレイアウト用のウインドウ・右下方の枠には、ここまでの操作で合計/社員コードが 3 つ入っていることと思います。このうち最も上のものをクリックします。

すると、下のようなポップアップが表示されます。

ここから値フィールドの設定をクリックします。

晴花

11

値フィールドの設定ダイアログが表示されます。

選択したフィールドのデータからデータの個数を選択し、OKボタンをクリックします。

※ 簡単に言えば、この作業によって「社員コードは量的データじゃなくて個人を識別する ID として使ってー」という指示を Excel に与えます。

2013

2016

変更された表記:データの個数個数

値フィールドの設定

晴花

12

同じようにして、 2 番目 3 番目合計/社員コードについても値フィールドの設定からデータの個数に変更しておきます。

晴花

13

下のように、社員コード別のデータの個数が集計されました。これはつまり、5 月に綾子が RS 部員に対して個別に付加したポイントの数を意味している、ということになります。

説明のみ

集計されたポイント

表の体裁をととのえる

晴花

14

さて、次のステップとして…

シート上に作成した 3 つののうち、最も左のフィールドとなる「データの個数/社員コード」列の、データが表示されている任意のセルをアクティブにしておきます(下の図ではセルB4を選択しています)。

次に、リボンのオプションタブ並べ替えとフィルターグループの降順ボタンをクリックします。

2013

2016

データタブ並べ替えとフィルターグループの降順ボタン

晴花

15

データの個数/社員コード」のデータが降順に並べ替えられました。これによって、綾子がつけたポイントが大きな人からの序列を意味するものとなりました。

説明のみ

晴花

16

ここからは、シート上に作成した残る 2 つののデータを加工していきます。

具体的には「データの個数/社員コード 2」列を構成比に、「データの個数/社員コード 3」列を累積の構成比となるよう設定を変更する作業をおこないます。

説明のみ

晴花

17

再び、ピボットテーブルレイアウト用のウインドウ、右下方の枠に視点を移し、上から 2 番目データの個数/社員コード 2上でクリックします。

ここから値フィールドの設定をクリックします。

晴花

18

値フィールドの設定ダイアログが表示されます。計算の種類タブに切り替えた後、ドロップダウンリストから列集計に対する比率を指定します。

指定した後、OKボタンをクリックします。

晴花

19

またまたピボットテーブルレイアウト用のウインドウ、右下方の枠です。今度は上から 3 番目データの個数/社員コード 3上でクリックします。

ここから値フィールドの設定をクリックします。

晴花

20

値フィールドの設定ダイアログが表示されます。計算の種類タブに切り替えた後、ドロップダウンリストから比率の累計を指定します。

指定の後OKボタンをクリックします。

晴花

21

ABC分析表の基本的な体裁は以上で完成です。

たとえば突発的に要求されたペーパー資料を作成する際などには、ここで作成した表をもとにして、他シートで細部の体裁を整えていった方が効率的かもしれません(コピー&ペースト)。


他方、そのような資料作成ではなく Excel 上での ABC分析のひな型としての活用―――すなわち、分析のツールとしての使い方のほうに重きを置いていく場合には、さらなる手数が必要かなとも思います。たとえば、下の図のような列見出し名などに理解しにくい要素が残ってしまいます。ここからはそういった諸点を解決していきたいと思います。

説明のみ

ABC分析表の基本的な体裁

晴花

22

では、それぞれの列見出しをよりふさわしいものに変更します。

ここでは、A列 の見出しが「社員コード」、B列 の見出しが「ポイント」になります。その他、C列 ・ D列 は ABC分析表 の場合、順次定型的に「構成比」「累積構成比」といった類いの見出しをあてておけばよいかと思います。

見出しの変更

クラスの別を明示する

晴花

23

次にクラス表記(Aクラス, Bクラス, Cクラス)を入れる場合を考えてみたいと思います。

ぶっちゃけそれがなくても累計構成比をみれば一目瞭然…とまでは言いません。不可欠なものではないですが、可読性の点から時にクラス表記が必要とされることもありますね。

しかし、ピボット表で If関数を使って判定をおこなうのもいろいろと面倒な調整が必要になり、労苦に見合った利が得られるかといえば疑問です。そこでここではもっと簡単に考えて、条件書式を使った記号による判定法をとりたいと思います。

説明のみ

ピボットテーブルのABC分析表でクラス表記をいかに表現するかが課題

晴花

24

では「累積構成比」列の任意のデータをアクティブにします(ここではセルD4)。

次に、リボンのホームタブスタイルグループの条件付き書式ボタンをクリックします。

晴花

25

下のように、プルダウンメニューをアイコンセットその他のルールとたどります。

条件付き書式のアイコンセット

晴花

26

新しい書式ルールダイアログが表示されます。下の図の順番および内容で各所を設定します。


なお、ここではクラス分けの基準を便宜的に

  • Aクラス … 累積構成比 ≦ 70%
  • Bクラス … 70% < 累積構成比 ≦ 90%
  • Cクラス … 90% < 累積構成比 ≦ 100%

としました(このケースの場合、上記の条件では同じ構成比をもつ項目をまたいでクラス分けがなされます[次の “FINISHED” の表を参照]。こうした時には、本来は境界設定が意味を成すよう上記の条件も調整すべきところです)。


その他の基準を適用して分析表を作成する場合には、下の図の “4” の値を適宜変更してください。なお、条件付き書式の設定では “5” 部分の仕様上、上から Cクラス、Bクラス、Aクラスと見立てて設定することになります。

すべて設定できたらOKボタンをクリックします。

新しい書式ルールダイアログ

晴花

27

ピボットテーブル上でのABC分析表の完成です。

下の図のように、各クラスは記号によって判別が可能です。

FINISHED

ABC分析表の完成。ピボットテーブルによるABC分析表のクラス表記は条件付き書式のアイコンセットでおこなった。

Tips: 元データを入れ替える

晴花

28

ここからは Tips として活用法・展開について若干の説明を加えたいと思います。


このように作成したピボットテーブル上の ABC分析表のひな形は、データソースの変更が容易で繰り返し利用できることから、複数の ABC分析表をアウトプットしたい場合や、データセットをとり替えながら分析をおこなっていく必要のある場合などには利があると思います。

2013

2016

分析タブ

[Tips]ピボットテーブルを利用する狙い

晴花

29

ただし、セルアドレスで範囲の再指定を繰り返すのも地味に面倒な作業ですので、端からソースを変更することが念頭にある場合には、元表については “テーブル” 化してしまった方がいろいろと便利かもしれません(当該表の任意のセルをアクティブにし、挿入タブテーブルグループのテーブルボタン。下を参照)。

[Tips]元表をテーブルに

晴花

30

この関連として… たとえば、各シートに 1 つのテーブルが作られており、それらが「テーブル1」「テーブル2」といった番号つきの名前で管理されているような環境があるとします。

ここでのデータソースの変更に関しては、ピボットテーブルのデータソースの変更ダイアログで テーブル番号(下の図の赤い囲み部分)を変えるだけですみますので、範囲指定にくらべて いくぶんかは負担も減るかと思います。

Tips: ピボットグラフでパレート図をつくる

晴花

31

あと、ええと… このピボット表からパレート図を作成する場合のヒントです。

この場合「構成比」は不要ですので、下の図のように枠から構成比のみ枠外へドラッグして削除してから、リボンのオプションタブツールグループのピボットグラフボタンをクリックします。

ピボットグラフボタンを押した後に必要な作業は、別頁「パレート図の作成 with Excel」を参照ください。

2013

2016

分析タブツールグループのピボットグラフボタン

晴花

32

しかし、せっかく作ったひな型を崩すと後が面倒ーっ!なこともあると思います。

晴花

33

ズボラな私の力技で申し訳ないのですが…

から「構成比」は削除せずにそのままピボットグラフを作成して、「構成比」棒を不可視にし、凡例からは「構成比」の項目だけを削除する…といった方法でも不都合はないかなーって思います…です。はい。

(1)「構成比」棒の枠線と塗りつぶしの色を「なし」に (2)「構成比」の凡例だけを削除

Extension

ABC分析のおり、とりわけデータが「なかった」ことにも関心をむけるアプローチがあります(「ABCZ分析」。この場合、「なかった」クラスを Z とする)。これをピボット表で完結させる場合、あわせて以下のような手続きが必要になります。

晴花

*1

最後にいましばらくの補足を重ねます。

ここまで無視してきましたが、実はこの月…ポイントが発生しなかった(=ゼロ)人が 1 名存在しています(ABC分析表に記載されない)。

“社員コード 1002” がその人ですが、ここでの元データは「ポイントが発生したら記録する」というルールのもとにあることから、“社員コード 1002” に関するレコードは当然ながら元データにも存在しません。

重点管理のみを目的としたABC分析においては「ない」といった点に関心をはらわなければならない理由もないのですが、こと記録が発生しなかったという点をあえて無視しないアプローチもあります(ABCZ分析:一般には「売上」や「販売数量」が 0(ゼロ)ものを Z 区分とする方法)。

たとえばマーチャンダイジングの局面で、「ない」ことが情報としての価値をもちえる場合などに利用できるかと思いますが、この場合、対象マスタとのすり合わせをおこなって「なかった」ことを別途表面化させる手続きが必要になってきます。

このようなABCZ分析表を作成したいとき…
これまでのような手続きの中で、Z 区分を加えて作成するための容易な path は…やはり、ピボット表から離れて処理をおこなうことかと思います(具体的な方法は ABC分析表の作成 with Excel 頁を参照ください)。

晴花

*2

また ひな形としてのピボット表の体裁を維持したいなら、Excel 2013 以降に限り 元データと対象マスタとの間にリレーションシップを設定してやることで対応は可能です。その場合は、以下のような処理を上に見た手続きの中に組み込んでいくことになります。

―――以下、その要点を示します。

晴花

*3

元データとは別に全対象者(or商品等)を羅列したシートを用意し…

晴花

*4

…いずれもテーブル化します(ここでは、前者のテーブルに「ポイント記録」、後者のそれに「全RS部員」という名前を定義しています)。

晴花

*5

づづいて、元データのテーブルを使って…

晴花

*6

…ピボットテーブルを作成していきますが、このとき、このデータをデータ モデルに追加するにチェックをつけておく必要があります。

晴花

*7

そのまま、元データと対象マスタとの間でリレーションシップを設定するため…

2016

分析タブ計算方法グループのリレーションシップボタン

晴花

*8

…key(ここでは「社員コード」)をもとに 別テーブルを参照できるように指定します。

晴花

*9

ピボットテーブルのフィールドリストすべてのフィールドが表示されるようにしたうえで、対象マスタの集計項目をに、元データのそれをに振り分けていきます。

晴花

*10

つづいてピボットテーブルのオプションからダイアログを呼び出し、いくつかの項目に手続きを加えると…

表示タブ「データのないアイテムを行に表示する」にチェック

レイアウトと書式タブ「空白セルに表示する値」に 0(ゼロ)を入力

晴花

*11

下図赤の囲みのような、「0(ゼロ)」値の項目が表示されます。ABCZ分析表のひな形を完成させるには、これに構成比・累積構成比列を加え(下図上段はこの時点のもの)、さらには降順で並べ替え、条件付き書式を設定(下図下段)するなど上に見たABC分析表で必要とされた手続きを適宜加えていくことになります。

条件付き書式「数式を使用」の適用順序と式→A区分:=AND(D4<0.7,C4<>0)  B区分:=AND(D4<0.9,C4<>0) C区分:=AND(D4<=1,C4<>0) Z区分:=C4=0


  • 本頁で使用したデータはすべて架空のものです。また特定の会社等に実在する人物名、および同場所で実際に観測されたデータ群などを根拠にしたものでもありません。
.

LastUpdate

2016.7.12

.
このページの先頭へ