Excel。ABC分析（パレート図）のデータを作ってみよう？
ABC分析（パレート図）
分析関係でお馴染みになってきた、ABC分析（パレート図）。折れ線グラフを縦軸0横軸0の交点で始める方法は、以前ご紹介しましたが、
仕事で使えるExcel講座や企業研修さんにおいて、グラフの作成の前段階。
つまりABCに分ける表の作り方について、ご質問が、ちょこちょこございますので、
今回は、データというか表の作成をご紹介していきましょう。
表自体の作成方法は、初心者の方でもいい練習になりますので、挑戦してみましょう。
さて、下記の表があるとします。
まず、パレート図を作成するにあたり手順を確認しておきましょう。
1.頻度の順（今回は金額）に項目を並べる
2.頻度の合計（金額の合計）を100とした構成比を求めます
3.頻度の順に構成比の累計を算出する
4.構成比率の累計によってグループ分けをしていく
～80%をA
～90%をB
～100%をC
とグループ分けします。
それでは、B列の金額を金額の大きい順すなわち、降順にしましょう。
ここで、注意が必要なのは、データタブの降順ボタンをポンと押すと21行目の総計まで含めて並び変わってしまいますね。
この場合は、どうしたらいいかというと、まずA1:B20までを範囲選択、
つまり総計行を除いて範囲選択をします。
データタブの並び替えボタンをクリックしましょう。
クリックすると、並び替えのダイアログボックスが表示されます。
このダイアログボックスを使用します。
最優先されるキーを”金額”として、並び替えのキーは、値で、順序は”降順”として、
OKボタンをクリックすると、金額の降順でデータが並び変わりますね。
当然範囲に含めていない総計行は除外されていますね。
これで、1.頻度の順（今回は金額）に項目を並べる。が出来ましたね。
次は、2.頻度の合計（金額の合計）を100とした構成比を求めていきましょう。
C1に構成比と入力をして、C2に大阪の構成比を算出していきましょう。
使う値は、大阪の金額と総計の金額ですね。で、比率なので、四則演算は、除算ですね。
問題は、どっちからどっちを割ることになるのですが、とりあえず、割ってみましょう。
100%を越えたら、おかしいわけですから、そのポイントだけ頭においておけばよいでしょう。
今回は、大阪の金額を総計で割ってみることにしましょう。
=B2/$B$21
B21の総計は常に参照。
つまり、絶対参照にしないとオートフィルで数式をコピーした時に#DIV/0というエラーが
表示されてしまいますので、F4キーを押して、絶対参照の設定を忘れないようにしましょうね。
数式をオートフィルでコピーして、％スタイルと小数第2位まで表示するようにすると、
このようになりますね。
これで、2.頻度の合計（金額の合計）を100とした構成比を求めることができましたね。
続いて、3.頻度の順に構成比の累計を算出していきましょう。
D1に累計と入力して、D2をクリックしましょう。
ここで、SUM関数のテクニックを使って、累計を求めていきましょう。
数式を手入力したほうが楽ですので、直接入力で数式を作っていきます。
=SUM($C$2:C2)
この数式はどんな意味なのかというと、最初のC2を絶対参照を設定することにより、
常にC2～指定のセルまでの範囲という意味になります。
絶対参照も使い方をちょこっとアレンジするだけで、累計も簡単に算出することが出来ます。
この数式をオートフィルをしようして、コピーしていきましょう。
そして、4番目の、4.構成比率の累計によってグループ分けをしていく
～80%をA ～90%をB ～100%をC とグループ分けするので、
ここは、IF+IF関数を使うと求めることが出来ますね。
E1にランクと入力をして、E2をクリックしましょう。
IF関数のダイアログボックスを表示しましょう。
論理式に、D2<80%
真の場合には、”A”と入力しましょう。
偽の場合には、再度質問をしていくことになりますので、名前ボックスの▼をクリックしましょう。
もう一度IF関数のダイアログボックスを表示していきましょう。
どうも、このネストの時に、前のダイアログボックスが見えなくなるのが、
関数に慣れてない人からは、わからなくなるということをよく聞きますね。
論理式に、D2<90%
真の場合には、”B”と入力しましょう。
そして、
偽の場合には、”C”と入力しましょう。
これで、OKボタンをクリックすると、Aと算出されましたね。
この数式をオートフィルを使ってコピーしていきましょう。
これで、ABC分析（パレート図）を作成するデータ。表が完成しましたね。
これを基にして、グラフにしていくわけですね。
