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

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

How-to

ABC分析表

ABC分析(パレート分析)は、クラス別管理や重点指向といった、既存リソースの管理効率の向上を目的とした場面で利用される手法です。この成果物となるABC分析表は、パレート図 を作成するにあたっての元表となります。

ABC分析においては、累積の構成比(以下cpr)をもとに通常3つのクラス(セグメント)を作成します。実務におけるこのクラス分けについての基準には、分析の主体においてたいてい任意のものがあてられます。たとえば、事務しごとにおいてこの手法がチョイスされる背景には、ほとんどの場合、ユーザーの念頭に 80 : 20 の法則 があることと思います。この点を鑑みれば,

「cpr ≦ 80% を A クラス, それ以外を B, C クラスで分かつ」…といった基準がまずは想起されるところでしょうか。しかし環境においてそれがフィットすることが当然約束されるものでなく、いろいろと検証を重ねた結果、たとえば
「cpr ≦ 60% を A クラス, それ以外を B, C クラスで分かつ」…といった基準をあてた方がパフォーマンスがよかった…なんてこともありえます。

そのような意味でABC分析の難しさは、一意でない “自由な” 基準を敷かなければならないことと、KPIとの対比においてその適合が検証されつづけなければならない点にあると言えるでしょうか。この手法をカタにはめて利用するだけでなく、上手な活用のあり方を追求するなら、目的に照らし有効かつ効率的なクラス分けを実現できそうなラインを探っていくこと…すなわち、A/Bテストのような補正を加えていけるしくみづくりが、あわせて不可欠のものになってくるような気もします。

以下、エクセルの「ピボットテーブル」機能を使ったABC分析表の作り方です。ここでは Excel 2010 で手続きを追っています。これは Excel 2007 でも変わりません。また Excel 2013, Excel 2016 についても基本同じ流れとなりますが、一部ボタン等の名称が異なる箇所があります(この場合、当該箇所に明記しています)。

元データ

晴花

1

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

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

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

元データ・ABC分析

晴花

2

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

※ 以下しばらく集計作業がつづきます。集計や並べ替えをした後の手順 をお探しの場合、リンクからどうぞ。

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

晴花

3

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

挿入→ピボットテーブルの挿入

晴花

4

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

「ピボットテーブルの作成」ダイアログ

晴花

5

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

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

晴花

6

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

このとき、行ラベル枠内には「社員コード」が表示されています。

2013

2016

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

晴花

7

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

晴花

8

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

このとき、枠内には「合計/社員コード」が表示されています。

晴花

9

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


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

晴花

10

では、ピボットテーブルレイアウト用のウインドウ・右下方の枠には、ここまでの操作で社員コードが入っていることと思います。まずは、これをクリックします。

晴花

11

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

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

晴花

12

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

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

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

2013

2016

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

「値フィールドの設定」ダイアログ…選択したフィールドのデータから「データの個数」を選択

晴花

13

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

説明のみ

集計されたポイント

Extension

ABC分析のおり、とりわけデータが「なかった」ことにも関心をむけるアプローチがあります(「ABCZ分析」。この場合、「なかった」クラスを Z とする)。Z 区分を作る場合は、あわせて左傍線部の一連の手続きが必要となります。

晴花

*1

これまでの集計に関することの補足ですが、実は、この月にポイントが発生しなかった(=ゼロ)人が 1 名存在しています(ABC分析表に記載されない)。

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

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

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

ここでのケースで具体的にこれをおこなうとき…

晴花

*2

第 1 に、(ここではあたらしい別シートに)全販売担当部員のIDを列挙し…

晴花

*3

第 2 に、下式 Vlookup関数をつかって元表(ここではピボット表)からポイントの値を転記します。元表に当該 ID が存在しない場合は 0(ゼロ)を返します。

  • =IFERROR(VLOOKUP(A2, pivot!$A$4:$B$26, 2, FALSE), 0)

以降はABCZ分析表であれ、通常のABC分析表の要点を適宜つぶしていくことになります(並べ替え, 構成比等の計算)。


―――以下、通常のABC分析表作成の手続きにもどります。

晴花

14

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

シート上の「データの個数/社員コード」の列の、データが表示されている任意のセルをアクティブにします(下の図ではセルB4を選択しています)。

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

2013

2016

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

オプション→降順

晴花

15

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

説明のみ

表の体裁をととのえる

晴花

16

では、ここからは集計したデータを分析表として加工していきます。

まずは下のようにデータ範囲を選択し、コピーします(ここではセルA4~B27)。

晴花

17

コピーしたデータを、あいている別のシートに 1 行ほど余裕を取ってから貼り付けます。

その空白行を使って、下のように「社員コード」「ポイント」「構成比」「累積構成比」と見出しを作ります。

集計・並べ替えしたピボット表の値を別のワークシートに貼り付け

晴花

18

構成比」列を計算します。この列の最上段のセル(ここではセルC2)に、= 個人のポイント ÷ 総ポイントとなる式を入力します。ただし、後でコピーすることを考慮して、式中の総計値(ここではセルB25)は絶対参照にしておきます。

  • =B2/$B$25

入力できたら全員分、この式をコピーします。

[セルC2]=B2/$B$25。この式をセルC25までコピー。

晴花

19

累積構成比」列を計算します。ここでは 2 ステップで式を入力する方法をとりたいと思います(累積構成比を 1 度で計算する場合 はリンク先のやり方で)。

まず、この列の最上段のセル(ここではセルD2)に、同じ行の構成比の値をそのまま参照する式を入力します。

  • =C2

累積構成比の計算…[セルD2]=C2

晴花

20

次に、そのすぐ下のセル(ここではセルD3)に、= 上のセルの値 + 左のセルの値となる式を入力します。そしてこの式を全員分コピーします。

  • =D2+C3

[セルD3]=D2+C3。この式をセルD24までコピー。

晴花

21

値にパーセントスタイルを設定します。下のように「構成比」「累積構成比」両列のデータ範囲を選択してから…

晴花

22

リボンのホームタブ数値グループにあるパーセントスタイルボタンをクリックします。また、[小数点以下の表示桁数を増やす | 減らす]ボタンで小数点以下桁数を適当なものに調整します。

この分析手法はあくまでグループ分けの目安を提供するものなので、小数点以下桁数を精度の観点から慮る必要性はあまりないと思います。ですので小数点以下 1 桁程度か、ぶっちゃけ ナシでも問題ないと言って差支えない面もあるので、ユーザーの任意な判断に依ればいいかと思います。

晴花

23

以上でABC分析表の完成です。
…とはいえ、クラスの区別を明確に表記していないです。これはストーリーパートで行いますので触れませんが、お急ぎの方向けに参考として次のように…

FINISHED

ABC分析表の完成

晴花

24

手描きなどでオーバーレイを加える方法と、表に「クラス」列を加える方法(If関数を使ってクラスを判定します)が主に利用されるところじゃないでしょうか。

私は、前者の方がインパクトと可読性の点から好きですが、ABC分析のIF関数を使ったクラス判定 の具体的な方法も、姉妹サイト「BDAstyle」で触れています。必要があればリンク先をご覧ください。

Tips) ABC分析表にクラス分けのオーバーレイを加えた例と、IF関数でクラス判定を行う「クラス」列を加えた例


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

LastUpdate

2016.7.12

.
このページの先頭へ