今時 Excelでvlookupなんて使ってないよね?

テストで大量のEXCELデータを捌く必要がったので、ちょっと研究してみました。

vlookup の問題

ある表上にある項目をキーにして別の表から項目を引っ張ってきたいシーンってのは意外によくあります。

今回私が必要だったのは、数万人の会員の一覧があり、別途会費決済のデータがあり、つじつまが合ってることの確認でした。普通ならプログラムを書くのですが、そのプログラムの検証であったことと時間がなかったのでEXCELで実施しました。

普通に会員一覧の会員IDをキーに決済データの支払いステータスを全員分引っ張ってきました。もちろん vlookupを使って。

ここで問題が発生です。

  1. 決済データは支払ステータスが会員IDより前の列だったために、そのままでは使えない。(vlookupでは検索キーが一番先頭列でなければならない)
  2. 遅い

前者は列を並び替えてしまえばよいのですが、動作の遅さは耐えられないものでした。

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)))

一度、検索キー自体を取り出し検索キーと比べることで、近似値の検索であっても一致していることを確認しています。確認できた場合のみ、値をと出すという形にしています。

実際に数万件のデータで試しましたが、2回検索がかかることになりますがそれでも圧倒的に普通にvlookup や matchをかけるより速いです。

なんか、EXCELerになっていく予感。


Comments !