Excel。上位30%をA。上位70%をBというようにパーセントで判断する方法
＜IF+PERCENTILE.INC関数＞
次のような表があります。
例えば、400以上ならA。300以上ならB。
それ以外はCというように判断分岐をするとしたら、
IF+IF関数のネストで算出することが出来ますよね。
ところが、今回のリクエストは、パーセント。
割合で判断分岐をしたいということなのです。
売上高の上位30%以内なら、A。
上位70%以内なら、B。
それ以外はCというような判断分岐をしたいというのが、
今回の目標になります。
まず厄介なのが、
新宿の売上高は、全体の売上高のうち、
上位30%に入っているのか？を判断指せるとした場合、
上位30%って、いくら？というのがわからないと判断の使用がありませんね。
このような、上位何%というような数値を求める関数があります。
それが、PERCENTILE.INC関数です。
C13に、PERCENTILE.INC関数を使って、
上位30%の数値を算出してみましょう。
C13をクリックして、
PERCENTILE.INC関数ダイアログボックスを表示しましょう。
配列には、C3:C12を入力します。
率には、0.7を入力します。
上位30%だから0.3では？と思われるかもしれませんが、
0から70％の位置の数値が、上位30%の数値になりますので、
0.7と入力します。
ですので、上位70%の位置の数値を求める場合には、
0.3ということになりますね。
では、OKボタンをクリックしましょう。
この数値が上位30%の数値ですので、
コレよりC3の値が大きければ上位30%以内ということを判断することができます。
C13の数式は、
=PERCENTILE.INC(C3:C12,0.7)
ついでに、上位70%以内の数値も算出すると、
364.7
という数値が上位70%という結果が求めることが出来ました。
このC13とC14の数値を使ってIF関数でABCの判断をしてもいいのですが、
今回は、別途セルに算出しないで、数式を作成してみましょう。
D3をクリックして、IF関数ダイアログボックスを表示しましょう。
論理式には、先ほど作成したPERCENTILE.INC関数をネストしていきます。
配列には、$C$3:$C$12
先ほどと範囲自体はかわりませんが、
オートフィルで数式をコピーしますので、
絶対参照を忘れないで設定しておきましょう。
率は、0.7
それでは、IF関数に戻りましょう。
IF関数の論理式を加工します。
PERCENTILE.INC($C$3:$C$12,0.7)<=C3
これで、C3が上位30%以内に該当するのかが、判断できます。
真の場合には、”A”
偽の場合ですが、IF+IF関数と同じですので、
IF関数をネストします。
ネストのIF関数は、次のように設定していきます。
論理式には、
PERCENTILE.INC($C$3:$C$12,0.3)<=C3
真の場合には、”B”
偽の場合には、”C”
として、OKボタンをクリックします。
あとは、オートフィルで数式をコピーして完成ですね。
D3の数式は、
=IF(PERCENTILE.INC($C$3:$C$12,0.7)<=C3,"A",IF(PERCENTILE.INC($C$3:$C$12,0.3)<=C3,"B","C"))
このようにすれば、パレート図を作成するときのABC分析にも使えますよ。
