今回はExcelの小技ということで、検索でよく使うVLOOKUP関数の高速化についてです。
VLOOKUP関数は、第四引数にTRUEかFALSEかを設定します。
通常はFALSEで使用すると思うのですが、TRUEを指定するとあいまい検索になります。
「あいまい検索」と言われると部分一致検索のようなものを思い浮かべるかもしれませんが、実際は二分検索だそうです。
二分検索については情報処理技術者試験でも出題されるので各自調べてほしいのですが、平たく言うと検索対象のデータが昇順にソートされていることを条件に高速に検索する方法です。
Excelの場合は文字コード(SJIS)の昇順に並べる必要があります。
FALSEの場合は線形検索（上から順次検索）になるので、検索対象のデータ量がN倍になると検索にかかる時間もN倍になります。
しかし、TRUEの場合は二分検索になるので、検索対象のデータ量がN倍になっても検索にかかる時間はlogN(底は2)倍の増加で済みます。
例えば、データ量が2倍になった場合は、FALSEだと2倍の時間がかかるようになりますが、TRUEだと1.414…倍になります。データ量が3倍になった場合は、FALSEだと3倍の時間、TRUEだと1.732…倍になります。
注意点としては、TRUEにした場合は満たす値が無かった場合にも値を返すようになるということがあります。
検索条件を満たす値が無かった場合は、満たす値未満の最も大きな値を返します。
例えば、以下のようにデータ並んでいて11を検索した場合は、けが返ります。
10 け
12 こ
FALSEの場合と同じように一致するデータが無かった場合に#N/Aとしたい場合は、以下のようにする必要があります。
=IF(VLOOKUP(検索値,範囲,1,TRUE)=検索値,VLOOKUP(検索値,範囲,列番号,TRUE),NA())
まずは検索条件と同じデータが存在するかどうかを調べ、存在する場合のみ検索をする、存在しない場合は#N/Aとする、ということをしています。
VLOOKUP関数を２回発行しているのでデータ量が少ないとFALSEの場合よりも時間がかかる場合もありますが、データ量が増えてくると効果を発揮します。
ここで注意点なのですが、漢字をキーにして検索する場合は、フィルタからの並び替えは不可です。
フィルタから並び替えると、文字コードの昇順ではなく、読み仮名の昇順に並んでしまうためです。
漢字を文字コードの昇順に並び替えるためには、以下の手順を踏む必要があります。
１．「データ」タブ→「並び替え」を選択
２．「オプション」を選択
３．「ふりがなを使わない」を選択
４．「列」を当該列、「並び替えのキー」を「値」、「順序」を「昇順」とする
５．「OK」を押下すると文字コードの昇順に並び替えられる
いかがでしたでしょうか。
VLOOKUP関数は普段の業務でも良く使用する関数だと思うのですが、調べてみると意外と奥深いことがわかります。
二分検索を使用した高速検索、漢字の文字コード順の並び替えについては、知っておいて損はないと思います。
業務で役に立つ小ネタがありましたら、また紹介しようと思います！
コメント