【Excel VBA】【マクロ】【Tips】Vlookupよりバイナリ検索が断然早い！

こんにちは！Lenocoです。本日も見てくださりありがとうございます。

今回は検索についてのTips回です！！！

Contents

データの検索について
バイナリ検索がおすすめ！

コード
コードの説明

さいごに

データの検索について

みなさん、データの検索ってどのようにしていますか？
例えば以下のような、A列に検索先、B列に取得したい値があったとき、
1行目から一つずつ見ていくでしょうか、それともVlookupの機能を使うでしょうか。

どの方法でも正しくデータをとってこれると思いますが、
検索先データの件数が膨大だった場合、上記の方法ではものすごく時間がかかってしまうかもしれません。

バイナリ検索がおすすめ！

検索先のデータが数値で、降順または昇順で並び替えられている場合、
バイナリ検索という方法が使えます。

二分探索や、バイナリーサーチともいうみたいです。
こちらのページが分かりやすかったので載せておきますね。

www.codereading.com

外部リンク

バイナリーサーチ／二分探索 : アルゴリズム

こちらのページには、「ソート済みの配列において、検索する間隔を半分に分割しながらデータを探し出すアルゴリズムです。」と説明がありました。
データを全部一つずつ見ていくのではなく、
上下半分にデータを分けたとき、検索値が上と下どちらにあるかを見て、
データが入っている方をまた半分に分け、検索値がどちらにあるかを見て・・・と繰り返していき、最終的に検索値を見つける方法です。

対象の値がない部分は一切見ない分とても速いです。
検索値を渡して、A列を検索し、B列の値を返してくれるマクロをご紹介します。
以下のような簡単な例で見ていきます。

コード

Function SearchItem(id As Long)     '「id」：検索値
    Dim startRow As Long
    Dim tempRow As Long
    Dim ws As Worksheet
    Dim endRow As Long

    Set ws = ThisWorkbook.Sheets("Sheet10")         '検索先のシートを設定
    startRow = 2                            '先頭行を設定
    endRow = ws.Range("A1").End(xlDown).Row '最終行を設定
    
    While (startRow <= endRow And id >= ws.Cells(startRow, 1).Value2 And id <= ws.Cells(endRow, 1).Value2)
        tempRow = (startRow + endRow) / 2
        If ws.Cells(tempRow, 1).Value2 = id Then
            SearchItem = ws.Cells(tempRow, 2).Value2
            Exit Function
        ElseIf id > ws.Cells(tempRow, 1).Value2 Then
            startRow = tempRow + 1
        Else
            endRow = tempRow - 1
        End If
    Wend
    SearchItem = ""
End Function

コードの説明

検索値は「555」として見ていきます。
まず、検索先のデータの先頭行と最終行を変数に格納しています。

While Wendで、以下の条件すべてに一致する間処理を繰り返します。
・先頭行（startRow）が最終行（endRow）以下である
・検索値（id）が先頭行のA列の値以上である
・検索値（id）が最終行のA列の値以下である
すでにお分かりの方もいると思いますが、このループの中で、先頭行と最終行はどんどん変わっていきます。

●1週目のループ

検索先のデータのちょうど半分の行番号をtempRowに入れています。
※今回のように先頭行が2、最終行が7行目だった場合、計算すると9÷2=4.5となりますがtempRowはLong型で宣言しているため、
小数点以下は切り捨てられ、4となります。
A列のtempRowの行番号の値が検索値と一致するか確認し、
一致していればその行番号のB列のデータを戻り値として返しています。
1回目のループではA列4行目は「333」となるので一致しないのでElseIfを見に行きます。
今度は検索値がA列tempRow行目より大きい場合はElseIfに入ります。
今回検索値は「555」で、A列4行目の「333」より大きいためElseIfに入ります。
startRowをtempRowにプラス1したものを入れます。tempRowは4なので5がstartRowに入ります。

●2週目のループ

startRow「5」とendRow「7」を足したものを2で割ります。tempRowに6が入ります。
A列のtempRow「6」の行番号の値が検索値と一致するか確認します。
A列6行目は「555」なので、検索値と一致するためIf文に入ります。
プロシージャ名「SearchItem」に、B列6行目のデータを戻り値として返します。