Excel VBA

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

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

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

データの検索について

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

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

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

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

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

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

対象の値がない部分は一切見ない分とても速いです。
検索値を渡して、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行目のデータを戻り値として返します。

さいごに

ちょっと長くなってしまいました。
イメージつかめましたでしょうか。

検索データが多い場合は、ぜひバイナリ検索を試してみてください!

Lenoco

COMMENT

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です