自社サービス開発保守者 兼 情シス 兼 何でも屋さんとしてよく相談を受けるケースとして、SpreadSheet(Excel) で思うような結果が出ないというものがある。 あるあるとしてVLOOKUPがうまくいかないケースがある。 経験則になるけども、これで困っている人はだいたい3パターンのいずれかをチェックすれば解決する。
構文:
VLOOKUP(検索キー, 範囲, 番号, [並べ替え済み])
パターン1: 検索キーのある列を範囲の先頭にしていない
参考のヘルプの言う通り、範囲の先頭列に検索キーのある列を置かなければならない。
検索キー - 検索する値です（例: 42、"ネコ"、I24）。
範囲 - 検索対象の範囲です。範囲の先頭列で検索キーとして指定したキーを検索します。
ID / ユニーク名 / ... とデータが並んでいるケースで、ユニーク名で調べたいのにID値を先頭にして範囲指定をしてしまうとうまくデータが引けない。
パターン2: 番号の指定がずれている
番号の指定は範囲に含まれる列の左から何番目か、という数値になる。これの使い方をシート全体の左から数えて何番目と覚えていると、範囲がシートの左端から始まっていない場合に誤認するケースがある。
パターン3: [並べ替え済み] にfalseに指定していない
並べ替え済みを TRUE に指定するか省略し、範囲の先頭列が並べ替え順でない場合、間違った値が返されることがあります。
と、ヘルプにある通り、[並べ替え済み]がfalseでない場合、検索キー列を並べ替えしておかないと想定通りのデータが引けない。 省略時、[並べ替え済み] は既定値のtrueなので、並べ替えを意識していない表に対してVLOOKUPを行うときはfalse指定は必須といえる。 指定していなくても結果は表示されてしまうので、あんまり意識していない人は意図しない結果が出ていても気づかないケースもあるのではと思う。
なぜtrueだとうまくいかないのか を覚えておく
ヘルプを参照しながら説明した時、そういえばなぜ未並び替えでtrueだと違った値が引けてしまうのかを知らないことに気づいた。ヘルプには流石にアルゴリズムの解説までは載っていない。軽くググったところどうやら二分探索を行っているらしい。
次、VLOOKUPの説明*1をするときのために具体例を検討してみたい。 10件のデータから一致するものを探すことを考えるとする。そして、欲しいデータは8番目に存在するとする。
[並べ替え済み]をfalseにすると、上から順に地道に調べていく(線形検索)。 そのため欲しいデータを見つけるためには8回確認処理をしなければならない。
[並べ替え済み]をtrueにすると、データを半分に割りながら調べていく(二分探索)。 並べ替え済みならば、途中をすっ飛ばして中央を調べても、目的のデータが中央より前方/後方のどちらにあるかを絞り込める。 割ったところの数が探している値より大きければ発見したとみなす。この方法だと探すための処理がぐっと少ない回数(例では2回)で済む。なので速い。
代わりに、並び替えていない場合は値がより大きいものを見つけたときに処理を終えてしまうので、意図通りの結果が得られない。
使いどころが肝心
大量のデータに対してVLOOKUPを使う場合、処理速度に問題があるようであれば検索範囲をちゃんと並び替えして、[並べ替え済み]にtrueを割り当てるのがよりよい方法と言える。単純にfalseを指定しないと、と決めてかかるよりはこういう使い分けが思い浮かぶほうがいざという時いいだろう。 今後なにかレクチャーする機会があったら補足していこう。
サマリ
ヘルプ最高なのでしっかり読もう！
