2004年09月14日

重複する値に騙されない

エクセルの関数は重複する値に関して致命的な欠点があります。

例えば、セルB2:B21に20のに入力した値を最大値から最小値までに整理したいです。この数式を使用しました。

=LARGE( B2:B21,ROW(INDIRECT("1:20")))

配列数式ですので「{」と「}」を入力せずにレンジを選択して、Ctrl、Shift とEnterを一緒に押して、自動的現れます。



重複する値が無ければ上記の数式は大丈夫ですが重複する値がある場合困ります。今、下記の図をよく見れば最大値は重なっています。



どうすれば良いでしょうか?

色々な方法があります。まず、一番簡単の方法はLaurent Longreが作成しましたMorefunc.xllをダウンロードしてUNIQUEVALUESの関数を使用する事です。



しかし、他のユーザーは同じMorefunc.xllをインストールしてない場合この方法は効きません。じゃ、私はもう一つの方法を考えました。最初は「重複する値を無視する」数式を隣のセルに入力してドラッグします。

=IF(OR(B2="",COUNTIF(INDIRECT(ADDRESS(ROW($B$2),COLUMN($B$2))&":"&ADDRESS(ROW(),COLUMN()-1)),B2)>1),"",B2)

このように重複する値を「無視」します。



そして、最初の配列数式をもう一度入力します。



さて、その「#NUM!」のエラーは気に入らないので上記の数式をちょっと書き直しましょう。

=IF(ISERROR(LARGE(C2:C21,ROW(INDIRECT("1:20")))),"",LARGE(C2:C21,ROW(INDIRECT("1:20"))))

はい、出来上がりました!;-)



Longreさんと他のエクセルのエキスパートを見たかったらColoのサイトに行って、「セルマスタ」を読んで下さい。

お休み!

この記事へのトラックバックURL

http://trackback.blogsys.jp/livedoor/andrewj/6799356
この記事へのコメント
う〜ん・・・すごい。なかなか面白いですね。
どこからこのような知恵がわきでるのでしょう。
Posted by 栗下 at 2004年09月14日 21:46
え、それほどでも。。。(笑)

いつか私も「セルマスタ」に成りたいからもっと頑張らなくちゃ!

Posted by Andrew at 2004年09月14日 22:47
おみごと(^_^)

でも困ったな…
日経PC21で毎年開催している「表計算腕自慢大会」で
次の問題にこの「重複しない順位」を出そうと思ってたんだ(^^;

しかたないから、別の問題を考えなくちゃ。
Posted by 田中亨 at 2004年09月24日 18:31
わあああ!田中さん、わざわざ私のサイトまで来てありがとうございました!

あれだったら、「重複しない順位」を削除しましょうか?^^

そうですね、エクセルなら色々な問題を解決できます。この程度の私はまだまだ学ぶことは山ほどたくさんありますが解決するの楽しい。

また来て下さい!心から楽しみにしています!
Posted by Andrew at 2004年09月24日 23:27