Excel。データをABCで振り分ける、パレート図用のABC分析の表を作ってみよう。
＜IFS関数＞
データをABCというステータスで振り分けることで、そのデータが全体の位置の中でどのあたりのポジションにあるのかがわかりやすくなるのが、ABC分析です。ABC分析を元にして、縦棒グラフと折れ線で構成されたグラフがパレート図になるわけですが、今回は、パレート図を作るためのABC分析の表を作っていきましょう。
【並び替えをしておく】
既存の軸となるデータを降順で並び替えをしておきます。
今回は、金額を軸にしております。この金額の合計も算出しておきましょう。
なお、オートSUMボタンの合計やSUM関数を使うのであれば、先に降順で並び替えをしておきませんと、合計行も含めて並び替えを行ってしまい、やり直す必要が発生しますので、手順について注意が必要です。
【構成比を算出】
それぞれのデータがどのぐらいのシェア。つまり構成比をもっているのかを算出する必要がありますので、構成比を算出していきます。
構成比は、そのデータを全体の合算値で除算すれば算出できます。
合算値である、B21を使うので、絶対参照を使う必要があります。
C2の数式は、
=B2/$B$21
と設定し、オートフィルを使って数式をコピーします。
0.192…と小数点で表示されますが、これでは、わかりにくいので、パーセント表示にしています。なお、今回は小数点第一位までの表示としています。
【構成比の累計を算出する】
つづいて、構成比の累計を算出します。ここで算出する値をつかって、ABCに振り分けていきます。累計を算出するには、前のデータまでの合算値とデータとの合算値で求めることができますが、次のような数式を作ることで簡単に算出することができます。
D2に設定した数式は、
=SUM($C$2:C2)
範囲選択の先頭である始点を絶対参照で留めておきます、そして、終点には相対参照のままにします。
これにより、オートフィルを使って数式をコピーすると、始点を固定しておくことができ、データの範囲が拡張することができます。
このやり方だと、この数式だけで累計の算出が出来ます。
【ABCと条件分岐で判定させる】
いよいよ、ABCに振り分けます。ランキングのようなABCなので、RANK.EQ関数を使うように思うかもしれませんが、あくまでも条件分岐です。今回は、累計が80%未満なら、「A」80%～90%未満なら「B」。それ以外は「C」とします。
旧来ならば、IF+IF関数のようなIF関数のネストで算出させるのですが、今回は、Office365から登場したIFS関数を使うと算出が簡単に作成できます。
E2にIFS関数を使った数式を設定します。
=IFS(D2<80%,"A",D2<90%,"B",1,"C")
IFS関数ダイアログボックスで確認してみましょう。
論理式1に、D2<80%というように、条件を設定して、真の場合を”A”と設定します。これを繰り返していきます。今回の最後は論理式3ですが、ここには、「1」。
TRUEを入力します。
Excelは、TRUEを「1」。FALSEが「0」と設定されていますので、「1」と入力しています。
あとは、オートフィルを使って数式をコピーすると、完成です。
このように、簡単な計算式で、ABC分析の表を作ることができます。
