初心者オサムシがExcelのマクロと戦う戦闘ブログである
by オサムシ
VLOOKUPの検索値にワイルドカードをつかってみた/Excel応用

VLOOKUP・ワイルドカード・配列を駆使して最強の関数を作る


関連記事:VLOOKUP&CEILING/Excel応用

VLOOKUPってデータ抽出にはよく使うけど、ちょっと融通が利かないところがあるよね

すごく便利な関数だから、あんまり贅沢いっちゃいけないけど、戻り値が"値"だからその後は表示したり、文字数をカウントするといった文字列関数くらいしか適用できない。(あたりまえか。。)

戻り値がセル参照なら、戻り値に対して適用できる関数が多いですよね。

少し調べてみると、VLOOKUPには検索値として*(ワイルドカード)が使えるそうな

ってことは、ワイルドカードで"全て"を検索するんだけど、対象が一つになるように他の関数を使って絞り込めばなんでもできるVLOOKUPができあがる(ちょっと大げさかなぁ)

まずは画像を。
d0285354_23204818.jpg

画像上選択されているF2セルの演算式は下記になります
{=VLOOKUP("*",IF(COUNTIF(F$1:F1,B$2:B$34)=0,B$2:B$34),1,0)}

この演算式の肝はじつはVLOOKUPじゃなくて配列

一番外側の{}は入力後、確定する時に Ctrl+Shift+Enter で配列を定義すると勝手に付加される(自分で{}を入力した訳ではありません)

それと絶対参照と相対参照を使い分けて、セルが下に行くにつれてその分範囲が大きくなっていく小技(F$1:F1)も大事

部品はともかく、この演算式全体で何をしているかというと、"重複"を検索しないようにしているのだ

ロジックを言葉で説明すると、「F1から現在セルまでに存在していない値を、B1~B34セルから昇順に探し、最初に該当した値を返す」ということになる

F4セルを例にとると、F1からF3までの間にB1~B34の値が存在しないか配列を上から順番に検索していく

F1~F3までに存在しない値をみつけると COUNTIF が"0"をカウントする事になるので、IFによって=0が成立するので TRUE と判断される

この例だと"B$2:B$34"の中でF4より上に存在しない最初の値としてB5セルの"川崎"がヒットするというわけ(新宿・五反田は存在したのでFALSEで通過)

VLOOKUPの「上から検索して最初にヒットした値を戻す」という特性を活かした裏ワザ

それと配列を定義する事によって、IFで列と列(つまりは配列)を対象とすることが実現できている

配列を定義しなければ、単にエラーをはかれてしまいます

そして、上記ロジックで該当する値がなくなると#N/Aを返します

っとここまで書いたのもワタクシ的にはかなりの力作

こういうの何も考えずにサッと書けるようになりたいねぇ。osamushi

オサムシじゃーなる
[PR]
by 03musi | 2013-01-05 00:21 | Excel 応用
<< 変数の型宣言/マクロ基礎 Excel VBAエキスパート/日記 >>