Excel。ABC分析の表をスピル機能で、手早くつくってみよう。
＜SUM関数・IFS関数＞
パレート図のもとになる、「ABC分析」。
色々な関数をつかってABC分析の表をつくるのですが、Excelに加わったスピル機能をつかってつくってみると、今までよりも、手早く、つくることができます。
C列の構成比を算出します。
C2に設定した数式は、新宿の売上高を全体の合計で除算するわけですが、合計値はありませんね。
また、合計を絶対参照にして、除算しなければいけません。
ところが、スピル機能の登場によって、C2の数式は、次のように設定することができます。
=B2:B11/SUM(B2:B11)
オートフィルで数式をコピーすることもなく、スピル機能によって、C3:C11に数式が反映（ゴースト）されます。
D列の構成比の累計値は、始点のセルを絶対参照にした、数式をつくります。
D2の数式は、
=SUM($C$2:C2)
オートフィルで数式をコピーします。
C列とD列は、パーセント表示の小数点第一位という設定をすることで、視覚的にわかりやすくします。
E列は、構成比の累計からABCのランク分けを行います。
今回は、80%未満は、A。90%未満はBで、それ以外はCとします。
三分岐なので、色々な関数で算出することができますが、今回はIFS関数を採用しました。
E2のIFS関数をつかった数式は、
=IFS(D2:D11<80%,"A",D2:D11<90%,"B",TRUE,"C")
スピル機能が使えるので、セル番地を指定するのではなく、範囲選択をD2:D11とすることで、オートフィルで数式をコピーする必要がありません。
IFS関数では、「TRUE」を論理式でつかうことで、「それ以外」として設定することができます。
これで、ABCとランク分けすることができました。
今までつかっていた数式もスピル機能に対応した数式に変更することによって、可読性が向上するなど、メリットもあるかもしれませんので、見直してみるのもいいかもしれませんね。
