7/30/2017

Excel。上位30%をA。上位70%をBというようにパーセントで判断する方法【Ratio branch】

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ボタンをクリックしましょう。

425.2と算出されましたね。
この数値が上位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分析にも使えますよ。