Excelピボットテーブルのみで完結させる、ABC分析表。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

すると，下のようなショートカットメニューが表示されます。

ここから値フィールドの設定を選びます。

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

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

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

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

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

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

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

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

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

ここから，残る2つのデータ項目を加工していきます。

具体的には「データの個数/社員コード2」列を構成比に，「データの個数/社員コード3」列を累積の構成比となるよう設定を変えていきます。

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

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

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

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

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

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

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

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

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

たとえばテンポラリーな資料の準備を迫られた場合であれば，ここで作成した表をもとにして，他シートで細部の体裁を整えていった方が効率的かもしれません(コピー＆ペースト)。

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

では，それぞれの列見出しをより相応しいものに変更します。

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

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

ぶっちゃけ読み手で任意のラインも判断できた方が便利だとは思うので、それは最初から描き込んでおく必要があるものなのかい？と強弁するのも度胸が要るので、責任の在りかを明確にする意味ではクラス表記を刻んでおく方が望ましいこともあるでしょう。

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

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

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

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

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

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

• 【Aクラス】 累積構成比 ≦ 70%
• 【Bクラス】 70% ＜ 累積構成比 ≦ 90%
• 【Cクラス】 90% ＜ 累積構成比 ≦ 100%

としました。

この例の場合，上記の条件では同一の構成比を跨いでクラス分けがなされます(次の27 の表を参照)。こうした時には，本来は境界設定が意味を成すよう上記の条件も調整すべきところです。

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

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

以上，ピボットテーブルのみでABC分析表を作成することができました。

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

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

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

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

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

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

あと，ええとこのピボット表からパレート図を用意する場合のヒントです。

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

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

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

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

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