Registered at Dec.30 2000
Excel孫の手−21
皆さんが頻繁に使う「Excel」について、いろいろなテクニックを紹介していきます。
「シミュレーション手法」-1
Q.今日持ってきた質問は、切実で極めて現実的な物なんです。
A.ということは、売上絡みですか。
Q.さすが鋭い。実は600人程の営業マンの1年間の売り上げ実績を元に、
表彰することになったのです。
しかも、実績集計は年度末の最終日まで含めるようにということなんです。
表彰の仕組み
全国は12のブロックに分けられている。
全営業マンの売上金額に順位付けし、上位100位までを入選とする。
入選者の一番多いブロックを「優秀ブロック」として表彰する。
もし、最上位が複数だった場合、上位101位までで再度カウントし直す。
それでも決まらなければ、102・103・・・・として、差が付くまで
カウントし直す作業を続ける。
A.本来なら、全営業マンの実績を並べ替え、そのまた100位までを並べ替え
だけで、ブロックのカウントをすればよいわけですが、並べ替えをしないで
も要求に応えられる形式にしましょう。
 |
今回は、RANK関数とCOUNTIF関数を組み合わせて使います。
まず、「順位A」の列には、以下のような式が入ります。
=RANK(D29,$D$29:$D$628) ←番地は、表の位置によって変わる。
ここでのポイントは、式の後半部分を絶対番地にすることです。
絶対番地は、範囲を選択した直後にF4キーを押します。
次に、「入選」の列には、「順位A」で付けられた順位が100位以内で
あれば、そのブロック名を表示します。
=IF(E29<=100,B29,"") ←番地は、表の位置によって変わる。
「入選者数」のカウントが、今回のキーです。
=COUNTIF(H29,$F$29:$F$628) ←番地は、表の位置によって変わる。
ここでのポイントは、式の前半部分を絶対番地にすることです。
そして、「順位B」の列で、ブロック毎の入選者数を順位付けします。
=RANK(I29,$I$29:$I$40) ←番地は、表の位置によって変わる。
Q.長い説明でしたね〜。とにかくやってみましょう。
そして・・・・
Q.いや〜、出来ました。並べ替えずに済むから、最新データに差し替えるだけで、
すぐに順位も入選者数もブロックの順位も出てしまう−−−−便利だ〜。
A.もう一つの問題がありましたね。
ブロックの最上位が複数だったら、単独になるまで入選者の枠を広げるという
あれです。
Q.そう、そうです。
どうやるんですか。また、全部式を入れ替えるんですか。
A.面倒くさいと思っているんでしょう。どっこい、簡単です。
予め「入選者枠」というセルを作っておきます。←ここでは、I27
そして、この例なら、100 と入力します。
次に、「入選」の式を改造します。
=if(E29<=100,B29,"") ←番地は、表の位置によって変わる。
この式を、
=if(E29<=$I$27,B29,"") ←番地は、表の位置によって変わる。
に改造します。
Q.おおっっ、凄い。連動して順位が変わる。
今月のポイント
複雑そうな式でも、関係を整理して簡単な式を組み合わせましょう。