今時 Excelでvlookupなんて使ってないよね？
テストで大量のEXCELデータを捌く必要がったので、ちょっと研究してみました。
vlookup の問題
ある表上にある項目をキーにして別の表から項目を引っ張ってきたいシーンってのは意外によくあります。
今回私が必要だったのは、数万人の会員の一覧があり、別途会費決済のデータがあり、つじつまが合ってることの確認でした。普通ならプログラムを書くのですが、そのプログラムの検証であったことと時間がなかったのでEXCELで実施しました。
普通に会員一覧の会員IDをキーに決済データの支払いステータスを全員分引っ張ってきました。もちろん vlookupを使って。
ここで問題が発生です。
- 決済データは支払ステータスが会員IDより前の列だったために、そのままでは使えない。(vlookupでは検索キーが一番先頭列でなければならない）
- 遅い
前者は列を並び替えてしまえばよいのですが、動作の遅さは耐えられないものでした。
index と match を使って見る
まず、検索列の位置にとらわれないように index と matchの組み合わせを使います。面倒なので決済データは「決済」というテーブルにしてしまいます。
すると会員一覧に埋め込む式は以下のようになります。A列に会員IDが入っていると想定しています。
=index(決済[ステータス],match(A2,決済[会員ID],0))
これで、検索キーと取得したい項目の位置関係がどうであっても、自由に検索して値を取り出すことができるようになりました。
速度の改善
しかし、vlookupを index/matchに変えたところでほとんど速度は変わりません。
ここで matchの3つ目のパラメータに注目です。0 を指定していますが、この場合は値が一致しなければEXCELは答えを返しません。値がない場合は #N/Aが返ってきます。
このパラメータに1か-1をセットすると近似値が返ってくるのですが、この近似値を返す方が圧倒的にパフォーマンスがよいらしいのです。問題は、「近似値じゃ困る」ってところです。
しからば、以下のようにするとと考えました。
=if(index(決済[会員ID],match(A2,決済[会員ID],1))=A2,index(決済[ステータス],match(A2,決済[会員ID],1)))
一度、検索キー自体を取り出し検索キーと比べることで、近似値の検索であっても一致していることを確認しています。確認できた場合のみ、値をと出すという形にしています。
実際に数万件のデータで試しましたが、２回検索がかかることになりますがそれでも圧倒的に普通にvlookup や matchをかけるより速いです。
なんか、EXCELerになっていく予感。