ABC分析をエクセル力で改善する4

前回はABC分析のデメリットに触れました

5

今回から何故、この2つのプロセスが問題かについての解説と、克服方法について解説します

1.並び変えは時間の浪費を生む

下の図は”販売個数”に従って

⇒構成率を計算⇒累計・構成率を計算⇒ランク付け(IF関数でランク付け)

を行っています

この表に販売個数/5,000を付け加えたとします

(商品別に行が構成されているという前提です)

そうするとどうなるでしょう?

6

再び同じ作業を繰り返した後に、Aランクが増えます

Aランクが微妙に多すぎたりします

すると、Aランクの基準を変えたくなります、つまり、IF関数の条件を変更するということです、

そしてBランクも・・・

これでは時間の浪費です

もっといい方法がないものでしょうか?

ここがエクセル力の見せどころです

<エクセルの基本機能を組み合わせて実務的な活用をする力>

2.VLOOKUP関数とPERCENTILE.INC関数、とテーブル機能を組み合わせる

ここで違和感を感じる人がいるかもしれません

”何故VLOOKUP関数”なのか

”PERCENTILE.INC関数”とは?

では次のファイルで上記2つの関数のエクササイズをしてみましょう!疑問が解けると思います

目次から”VLOOKUP関数&Percentile関数”のシートに飛んでください

7

まず、VLOOKUP関数ですが、70を検索値としてEからF列にある表を参照していますが、エラーになっています

何故なら、表に70が無いからです

では参照方法を0から1に指定し直したらどうでしょう?

Gif3

検索値を超えない範囲(今回で言えば70を超えない範囲)で最大の値を参照します(今回は10)

つまり、近似値で検索をするのです

このVLOOKUP関数の近似値検索を利用すれば、ランク付けの基準を変える時に、

数式を変更しなくても、

並び変えを行わくても、

表を変えさえすれば、ランク付けは変更されます

他の言い方をすると、累計構成率ではなく、直接的に値(今回の事例では”販売個数”)を参照してランク付けが行えます

VLOOKUP関数の詳細な使い方は以下をぜひ参照してください

VLOOKUP関数とIF関数だけを使い倒す#1

次にPercentile関数です

下のGIF画像の右側にある値の一覧の中から、指定した基準の値を拾ってくくれます

例えば50%と指定すれば1から10の間で上位50%に位置する値を拾います

Gif4

数式の指定の仕方自体はとてもシンプルです

参照する値の範囲を指定し、加えて基準を指定します

(ここで80%を20%に置き換えていることにご注意ください)

8

PERCENTILEINC関数の概要が明確になってきたと思います

VLOOKUP関数とPERCENTILEINC関数を組み合わせることで、

ABC分析の肝・・・例:上位20%の商品で売上の80%を稼いでいる

この肝に準じて表作成を自動的に行っていけるのです

 

次回は再びこの肝から解説をはじめて、より詳細に解説を行っていきます
にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です