トップ «前の日記(2007-10-11) 最新 次の日記(2007-10-18)» 編集

十日日記


2007-10-12

Link VLOOKUPのバッドノウハウ

Excelの検索関数VLOOKUPについては以前に書いたことがある。数日前、ちょっとしたバッドノウハウを見つけたので、ここに記しておく。

VLOOKUP関数は、範囲の最左列から特定の語を検索して、マッチした行の任意の列を表示する。リンク先のヘルプを見ればわかるとおり、引数を4つとり、VLOOKUP(検索値, 範囲, 列番号, 検索の型)のように書く。

この関数を用いて想定される事態を考えてみよう。(1)検索値が範囲最左列に存在しない場合。このときには「#N/A!」というエラーが返る。(2)運よく検索値が見つかったものの、列番号が範囲の列数に合わない場合。このときは、「#VALUE!」や「#REF!」のエラーが返る。(3)運よく範囲のm行目n列目まで突き止めたものの、そのセルが空白だった場合。Excelはエラーを返さない。かわりに「0」が返ってくるように見える。

なぜ0なのか。vbEmptyを数値型に変換すると0になるからだと想像しているが、真の理由は私にはわからない。「VLOOKUP()&""」のように文字列型にして、0の返り値を予防する常套手段があることから、先のように推測している。

以上で見たとおり、VLOOKUPの「異常な」返り値としては、エラーと0とがある。ここで、望ましい返り値は「正の実数」と仮定する。つまり、何らかの数値データ表から検索値を引っ張ってくるような状況を想定している。こうしたとき、返り値が(1)エラーかどうか、(2)空文字列がどうかで条件分岐をするのは頭が重い。ベタに書いてみると、

=IF(ISERROR(VLOOKUP()),"",IF(VLOOKUP()="","",VLOOKUP()))

こんなふうに同じVLOOKUP()が3回も出てくる。Excelのワークシート関数には正規表現にあるような文字列展開がないので、このようにしなくはならない。

そこで思いついたバッドノウハウが。

=1/(1/VLOOKUP()))

逆数の逆数にすることで、vbEmptyの場合にゼロ除算(#DIV/0!)のエラーを起こす。こうしておけば、

=IF(ISERROR(1/(1/VLOOKUP())),"",VLOOKUP())

と、ISERRORにまとめることができる。同じ処理をするセルの数が多いときにはIFERROR関数をVBAで定義して、

=IFERROR(1/(1/VLOOKUP()),"")

としている。計算速度には意外と影響を与えない。バリバリに入れても再計算速度は0.01秒〜0.02秒しか変わらなかった。

Tags: Excel
[]


プロフィール

渡辺 慎太郎(na@10days.org)

分野別表示

Admin | Client | Dev | Excel | Linux | PC | PDA | Web | iPad | web | 家電 | 文具 | | 英語 | 言語 | | 音楽

月別表示

1999|07|
2003|05|06|07|08|09|10|11|12|
2004|01|02|03|04|05|06|07|09|10|11|12|
2005|01|02|03|04|05|06|07|08|09|10|11|12|
2006|01|02|03|04|05|06|07|08|09|10|11|12|
2007|01|02|03|04|05|06|07|08|09|10|11|12|
2008|01|02|03|04|05|06|07|08|09|10|11|12|
2009|01|02|03|04|05|06|07|08|09|10|11|12|
2010|01|02|03|04|05|06|07|08|09|10|11|12|
2011|01|02|03|04|05|06|07|08|09|10|11|12|

最近の記事

雨量情報 dictionary.com Yahoo google Yahoo! 路線情報 東京アメッシュ l-mura l-aka l-momo 目次 r-mura r-aka r-daidai r-kiiro asahi.com nogulabo r-sora r-midori r-midori r-momo