今回はこれまでのまとめから始めましょう

①実用的なエクセル力とは

ビジネスに修正はつきものです。エクセル機能を組み合わせて、修正にも即時に対応できるような能力を身に付けましょう！

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

②分析にも修正はつきものです

分析の肝の一つは区分です。ABC分析は区分を最少単位で行います

但し、試行錯誤はつきものです。どこからどこまでを１つの区分とするかを変える度に、エクセルの処理を最初からやり直していたら効率が悪すぎます。VLOOKUP関数とPERCENTILE.INC関数をうまく組み合わせて修正作業を自動化しましょう

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

③表への追加にも対応できるようにしておきましょう

従来のABC分析の場合にはデータを追加した場合には、並べ替え作業からやり直しです。②とあわせてテーブル機能も組み合わせて自動化しておきましょう

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

④ABC分析は質と量の公式であらわせます

ABC分析において重要なのはビジネス上優先すべき質の高い区分（グループ）を特定する観点です

ところが、従来の方法だと質と量の公式の観点が分かりにくいのです

②で述べたVLOOKUP関数とPERCENTILEINC関数をうまく組み合わせて、質と量の双方向からランク付けを行っていきましょう

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

さて、今回からこれまでとは違う方向の内容に取り組んでいきます

まず、下の画像を見てみてください

Aランクがどの位の質と量のなのか、理解できましたか？

それに質の７８％と９８％は手で入力しています

これはとても非効率です

次の回からはピボットテーブルと連動した双方向グラフについて解説します

にほんブログ村

前回は再び、ABC分析の肝に触れました

今回はこの点をもう少し詳細に解説するところからはじめましょう

１．分析の公式の一つに”質ｘ量”があります

上記を質ｘ量の公式にあてはめると

上位２０％の商品⇒量は全体の２０％（量）だが、売上の８０％を占める（質）

となります

従来のABC分析の手法ですと、この質と量の関係が見えてこないのです

では、ここからVLOOKUP関数とPERCENTILEINC関数とテーブル機能を組み合わせると、この量と質の関係が見えてくるのかを実際の使用例でみてましょう

２．VLOOKUP関数とPERCENTILEINC関数とテーブル機能を実践的に組み合わせる

まず、”Data”のシートに加えて”ABC設定”の状況を管理するシートを用意しておきます

そして、前もってDataシートにある表はテーブル化しておきましょう

テーブル化しておくことで、表に値を追加しても数式が自動的にセットされます

（詳細は以下のページなどを参照してください）

テーブルを作成しよう～数式を自動入力～

ABC設定のシートは以下の様な構成になっています

そして、DataシートのVLOOKUP関数（近似値で検索）と連携させておきます

ここまで設定しておけば”量x質”の公式による観点でのランク設定が自由自在に行えます

（下の表はDataシートの内容ですが、上から2行目/販売個数：2,213と6行目：1,876のランクが上の操作と連動して変わっています）

データを追加した時もランク付けが自動的に行われます

もし、ABC、３つのランクでなく、ABCD、４つのランクにしたい時もCランクの設定個数を変えれば自動的に作成できます

これで大分、実践的になってきました。次回はこれまでの話しを少しまとめた上で、次の段階にいきたいと思います

ちなみに実際にサンプルを動かしてみたいという方は以下をご利用ください

ABC分析

1 ファイル 258.23 KB

ダウンロード

にほんブログ村

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

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

１．並び変えは時間の浪費を生む

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

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

を行っています

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

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

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

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

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

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

そしてBランクも・・・

これでは時間の浪費です

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

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

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

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

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

”何故VLOOKUP関数”なのか

”PERCENTILE.INC関数”とは？

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

ABC分析

1 ファイル 258.23 KB

ダウンロード

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

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

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

では参照方法を０から１に指定し直したらどうでしょう？

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

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

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

数式を変更しなくても、

並び変えを行わくても、

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

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

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

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

次にPercentile関数です

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

例えば５０％と指定すれば１から１０の間で上位５０％に位置する値を拾います

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

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

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

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

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

ABC分析の肝・・・例：上位２０％の商品で売上の８０％を稼いでいる

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

次回は再びこの肝から解説をはじめて、より詳細に解説を行っていきます

にほんブログ村