2023/7/10

イントロダクション

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

READ MORE

この分析では,累積の構成比(以下cpr)を基準に通常3つのクラス(セグメント)を作成します。実務におけるこのクラス分けの基準は,分析の主体において任意のものがあてられるのが常です。

たとえば,事務しごとでこの手法がチョイスされる背景には80:20の法則があるでしょうが,この点を鑑みれば,

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

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

以下,ExcelによるABC分析表の作り方です。ここでは一連の手続きを Excel 2010 で追っています。一部ボタンの配置や名称などが異なる箇所がありますが(この場合,可能であれば当該箇所に明記します),手続きそのものは,「永続ライセンス版」にいうところの Excel 2019, Excel 2016, あるいは Excel 2013,そして,「Office365版」の Excel (本頁更新時点のver.1905)とも基本的には同じです。

晴花

元データ

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

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

元データ・ABC分析

上のデータをもとに,ここでは「社員コード」の“登場回数”でABC分析表をつくります(以下“登場回数”のことを「ポイント」と呼びます)。

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

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

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

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

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

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

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

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

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

DIFFERENT VERSIONS

2013-: [変更された表記] 行ラベル

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

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

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

シートの左の方を見ると,あらたに「合計/社員コード」という見出しとともに,一見でたらめに見える一連の数字の羅列が表示されています。これは「社員コード」をExcelが“数字”と判断したためです(人間から見れば社員コードは端から文字列以上の意味を持ちえませんが)。別の言い方をすれば,社員コードのポイント倍となっています。


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

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

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

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

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

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


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

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

2013-: [変更された表記] データの個数個数

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

集計されたポイント

Extension

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

READ MORE

Z区分

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

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

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

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

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

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

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

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

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


以下,通常のABC分析表作成の手続きに戻ります。

値の降順に並べ替える

さて,次のステップとして

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

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

オプション→降順
DIFFERENT VERSIONS

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

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

表の体裁をととのえる

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

まずは下のようにデータ範囲を選択し,クリップボードにコピーします(ここではセルA4~B27)。

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

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

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

構成比」列を計算します。この列の最上段のセル(ここではセルC2)に,

個人のポイント÷総ポイント

となる式を入力します。ただし,後でコピーすることを考慮して,式の中の総計部分(ここではセルB25)を絶対参照にしておきます。

  • =B2/$B$25

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

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

累積構成比」列を計算します。ここでは2ステップで式を入力します

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

cf. 累積構成比をスッキリ計算

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

次に,そのすぐ下のセル(ここではセルD3)に,

上のセルの値+左のセルの値

となる式を入力します。そしてこの式を全員分コピーします。

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

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

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

この分析手法で“何ら”の規範に沿ってグループが区別されたように、そもそも“何ら”の時点からアバウトさを悉除しない性質なので,小数点以下桁数を精度の観点から慮る必要も薄いとは思います。ですので小数点以下1桁程度か,ぶっちゃけ0でも差支えないとは思いますが,いずれにしろユーザーが任意の判断で処理すべきでしょう。

ABC分析表の完成です。

とはいえ,クラスの区分については下の成果物には表示していません。これはストーリーパートで行いますので触れませんが,お急ぎの方向けに参考として次のように

ABC分析表の完成

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

私は,前者の方がインパクトと可読性の点から好きですが,ABC分析のIF関数を使ったクラス判定については,「BDAstyle」でも触れています。必要があればリンク先を参照ください。

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