4/26/2014

Excel。ABC分析(パレート図)のデータを作ってみよう?


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分析(パレート図)を作成するデータ。表が完成しましたね。
これを基にして、グラフにしていくわけですね。