VBA

【EXCEL VBA】XLOOKUPの使い方とXLOOKUPの自作『徹底解説!!』

 

XLOOKUP関数は、VLOOKUP関数の進化版として2020年にリリースされ、徐々に利用できる環境が広がっている関数です。
この記事では、VLOOKUP関数を普段から利用している方に向けて、XLOOKUP関数の使い方を解説いたします。

また、XLOOKUPは比較的新しい関数なので、対応していない環境の方に向けてVBA で自作する方法も併せて解説します。
大企業ではOffice365の導入が進んでいて使えないことは少ないと思いますが、中小企業や個人の方が利用するPCでは、まだまだ古いOfficeを入れている環境も多いと思いますので、是非、この記事の内容を実践してみて下さい。

基本的な構文【XLOOKUP】

XLOOKUP(検索値, 検索範囲または配列, 返す範囲または配列, [見つからない場合の返り値], [比較方法], [検索モード])
  • 検索値: 検索したい値。
  • 検索範囲または配列: 検索を行う範囲や配列。
  • 返す範囲または配列: 検索が成功した場合に返す範囲や配列。
  • [見つからない場合の返り値]: 検索値が見つからなかった場合に返す値。省略可能。
  • [比較方法]: 検索値と検索範囲の値を比較する方法。省略可能で、デフォルトは 1（精確な一致）。
  • [検索モード]: 検索のモード。省略可能で、デフォルトは 1（精確な一致）。

例えば、

=XLOOKUP(A2, B2:B10, C2:C10, "Not Found")

この式は、セル A2 の値を B2:B10 で検索し、対応する値があればその値を、なければ “Not Found” を返します。

XLOOKUP 関数は非常に柔軟であり、範囲や配列の指定、見つからなかった場合の処理、検索モードの指定などが可能です。

XLOOKUP関数のメリット

  1. 検索する列と返す列の順番を気にしなくていい
  2. 返す列の指定がカンタン
  3. エラーの処理がしやすい
  4. スピルに対応している

VLOOKUP関数では、「検索する列（上記の例で言えばG列）」が「返す列（I列）」より左側にある必要がありました。さらに、返す列が「左側にある検索する列から、何列先にあるのか」という列番号を指定する必要がありました。XLOOKUP関数では列番号指定は不要で、シンプルに「返す列そのもの」を指定すればOKです。

さらに、[任意]の引数を活用することでエラーの処理もカンタンにできます。

スピル（spil）は「こぼれる」「あふれる」という意味です。
従来では、配列数式（※）を利用した場合を除いて、数式を入れた一つのセルに対して一つの結果が表示されるのが常識でした。
ですがスピルは数式を入力したセルだけでなく、隣接するセルにも結果が表示されます。このようなこぼれる、あふれるような挙動にちなんでスピルと呼ばれています。

※これまでのExcelでは、複数のセルに値を表示したい場合、結果を表示したい全てのセルを選択し、Ctrl+Shift+Enter（CSE）を押すことで数式が一括入力されていました。これを「配列数式」と言います。これに対してスピルは、一つのセルに数式を入力すれば隣接するセルにも自動的に結果が表示されるため「動的配列数式」と呼ばれます。

XLOOKUP関数の自作

これほど便利なXLOOKUP関数ですが、EXCEL2021以降のEXCELにしか搭載されていません。そこで、VBAを利用してWorkSheetで利用できる、「MyXLOOKUP」関数を作成したいと思います。これでバージョンに依存せずにXLOOKUP関数を利用できる様になります。

基本的な実装


XLOOKUP 関数を VBA で自作するには、VBA のユーザー定義関数（User-Defined Function、UDF）を使います。以下は、単純な XLOOKUP 関数を模倣した VBA コードの例です。

  • Excel ファイルを開き、ALT + F11 キーを押して VBA エディタを開きます。
  • Insert > Module を選択して新しいモジュールを作成します。
Function MyXLOOKUP(lookup_value As Variant, lookup_array As Range, return_array As Range, Optional if_not_found As Variant, Optional match_type As Integer = 1, Optional search_mode As Integer = 1) As Variant
    Dim i As Long
    Dim found_value As Variant

    If Not IsArray(lookup_array.Value) Or Not IsArray(return_array.Value) Then
        MyXLOOKUP = CVErr(xlErrValue)
        Exit Function
    End If

    For i = 1 To UBound(lookup_array.Value, 1)
        If match_type = 1 Then
            If lookup_array.Value(i, 1) = lookup_value Then
                found_value = return_array.Value(i, 1)
                MyXLOOKUP = IIf(IsError(found_value), if_not_found, found_value)
                Exit Function
            End If
        ElseIf match_type = -1 Then
            If lookup_array.Value(i, 1) = lookup_value Then
                found_value = return_array.Value(i, 1)
                MyXLOOKUP = IIf(IsError(found_value), if_not_found, found_value)
                Exit Function
            End If
        ElseIf match_type = 0 Then
            If lookup_array.Value(i, 1) Like lookup_value Then
                found_value = return_array.Value(i, 1)
                MyXLOOKUP = IIf(IsError(found_value), if_not_found, found_value)
                Exit Function
            End If
        End If
    Next i

    MyXLOOKUP = if_not_found
End Function

自作関数MyXLOOKUP を定義しました。基本的な XLOOKUP の機能のみ実装しました。パラメータとして検索値、検索範囲、返す範囲、見つからなかった場合の値、比較方法、検索モードを受け取ります。match_type は、1 は精確な一致、-1 はより小さい値、0 は部分一致です。search_mode は、検索のモードを指定します。

自作関数MyXLOOKUPの解説

先にも記述しましたが引数は下記になります。

  • lookup_value: 検索する値。
  • lookup_array: 検索対象の範囲（1列のデータ）。
  • return_array: 戻り値を持つ範囲（1列のデータ）。
  • if_not_found (オプション): 検索値が見つからなかった場合のデフォルト値。
  • match_type (オプション): マッチングの種類。1が完全一致、0が部分一致、-1が部分一致（逆向き）。
  • search_mode (オプション): 検索モード。1が通常モード。

動作としては次の様になります。

  1. lookup_array および return_array が配列でない場合、xlErrValue をエラーとして返します。
  2. lookup_array の各セルを lookup_value と比較し、マッチングするものを検索します。
  3. match_type に基づいて、完全一致、部分一致、逆向き部分一致を判定します。
  4. マッチが見つかった場合、対応する return_array のセルの値を取得します。
  5. 取得した値がエラーの場合、if_not_found を返します。
  6. マッチが見つからなかった場合、if_not_found を返します。

注意点として、

  • 関数内で Exit Function が使用されており、マッチが見つかった場合にすぐに関数が終了します。
  • 部分一致の場合は Like 演算子を使用しています。この演算子はパターンマッチングに使用され、ワイルドカードを扱います。
  • if_not_found のデフォルト値は Variant 型であり、検索値が見つからなかった場合に返されます。

VBA内で利用する方法

Dim result As Variant
result = MyXLOOKUP("Alice", Range("A1:A10"), Range("B1:B10"), "Not Found", 1, 1)

If Not IsError(result) Then
    MsgBox "Result: " & result
Else
    MsgBox "Error: " & result
End If

このコードでは、”Alice” を検索して、見つかった場合は対応する値を、見つからなかった場合は “Not Found” を返します。

WorkSheetで利用する方法

普通の数式入力と同じように利用できます。

=MyXLOOKUP("Alice", A1:A10, B1:B10, "Not Found", 1, 1)

これにより、MyXLOOKUP 関数がセルに結果を返します。

WorkSheetで利用する際に関数の説明を出す方法

VBA プロジェクトのモジュールにある関数に説明を追加します。MyXLOOKUP 関数の例で言えば、以下のようにコメントとして説明を追加します。

Function MyXLOOKUP(lookup_value As Variant, lookup_array As Range, return_array As Range, if_not_found As Variant, Optional match_type As Integer = 1, Optional search_mode As Integer = 1) As Variant
    ' MyXLOOKUP 関数は、指定された条件に基づいて値を検索し、結果を返します。
    ' lookup_value: 検索する値
    ' lookup_array: 検索対象の範囲
    ' return_array: 返す値がある範囲
    ' if_not_found: 検索値が見つからなかった場合のデフォルト値
    ' match_type: 検索方法 (デフォルトは 1)
    ' search_mode: 検索モード (デフォルトは 1)
    
    ' 関数の本体
    ' ...
End Function

ヘルプ テキストの設定

VBA エディタ内で、ヘルプ テキストを設定します。

  • VBA エディタでモジュールを開き、View > Properties Window を選択します。
  • モジュール内の関数に移動し、(Name) のプロパティに関数の名前を入力します。例えば、MyXLOOKUP とします。
  • ヘルプ テキストに関数の説明を入力します。

Excel でヘルプ テキストを表示

  • Excel に戻り、ワークシート上でセルに関数を入力します。
  • 関数名の後に引数を入力すると、Excel が関数のヘルプ テキストを表示します。

これで、ワークシートで関数を使用する際に、関数の説明が表示されるようになります。

注意事項

  • 関数を使用する際に、VBA プロジェクトが有効である必要があります。Excel ファイルを保存する際、マクロが有効になっていることを確認してください。
  • MyXLOOKUP 関数を変更した場合は、変更を保存してから Excel を再起動するか、手動で VBA エディタから再コンパイルする必要があります。

MyXLOOKUPの拡張

先程作成した自作関数は基本的な検索機能を実装しただけでした。本家に負けないくらいの機能を実装していきましょう！

例えば次の様な機能を実装してみます。

  1. 複数条件での検索のサポート: 現在のコードでは一つの条件での検索を行っていますが、複数の条件を指定できるように拡張できます。例えば、AND条件やOR条件を組み合わせて検索する機能を追加できます。
  2. 範囲指定のサポート: 現在は行ごとの検索しかサポートしていませんが、列も指定して検索できるように拡張できます。これにより、範囲内の特定の行・列での検索が可能になります。
  3. エラー処理の改善: 現在のコードは、検索結果が見つからなかった場合に CVErr(xlErrNA) を返しています。エラーに対するより具体的な情報や、エラーハンドリングの改善を考えることができます。
  4. 性能向上の検討: 大規模なデータセットに対しても高速に検索できるよう、アルゴリズムやデータ構造を最適化することが考えられます。例えば、データセットをソートしておくことで二分探索を使用できるようにするなどです。
  5. パラメータの追加: 現在のコードでは検索モードや結果モードなどが引数として与えられていますが、必要に応じて追加のパラメータを受け入れるようにしても良いでしょう。
Function myxlookup(lookup_values() As Variant, lookup_arrays() As Range, _
                   result_arrays() As Range, Optional lookup_mode As String = "exact", _
                   Optional result_mode As String = "first") As Variant

    '-----------------------------------------------------------------------
    ' MyXLOOKUP 関数
    '
    ' 概要:
    '     与えられた条件に基づいて範囲を検索し、一致する値を返す関数です。
    '
    ' 引数:
    '     lookup_value: 検索する値
    '     lookup_arrays: 検索対象の範囲
    '     result_arrays: 結果を返す範囲
    '     lookup_mode: 検索方法 ("exact"、"partial"、"criteria" のいずれか)
    '     result_mode: 結果の取得方法 ("first"、"last"、"all" のいずれか)
    '     criteria: "criteria" 検索モードで使用する条件
    '     search_range: 検索対象の範囲 (省略可能)
    '
    ' 戻り値:
    '     一致する値または結果の配列
    '
    ' 注意:
    '     MyXLOOKUP("条件", A1:A10, B1:B10) のように利用します。
    '-----------------------------------------------------------------------

    On Error GoTo ErrorHandler

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim i As Long, j As Long
    Dim found As Boolean
    Dim result() As Variant

    ' ワークシートを設定
    Set ws = lookup_array.Worksheet

    ' lookup_arrayの最終行および最終列を決定
    lastRow = lookup_array.Rows.Count
    lastCol = lookup_array.Columns.Count

    ' 検索範囲を設定
    If search_range Is Nothing Then
        Set search_range = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))
    End If

    ' 結果の配列を初期化
    ReDim result(1 To lastRow, 1 To lastCol)

    ' 各セルを検索範囲内でループ
    For i = 1 To lastRow
        For j = 1 To lastCol
            ' 現在のセルがlookup条件と一致するか確認
            If IsMatch(lookup_value, lookup_array.Cells(i, j).Value, lookup_mode, criteria) Then
                ' 指定されたモードに基づいて結果を記録
                If result_mode = "first" And Not found Then
                    ' 最初の一致を返す
                    result = result_array.Cells(i, j).Value
                    found = True
                ElseIf result_mode = "last" Then
                    ' 最後の一致を継続的に更新
                    result = result_array.Cells(i, j).Value
                ElseIf result_mode = "all" Then
                    ' すべての一致を2D配列に保存
                    result(i, j) = result_array.Cells(i, j).Value
                End If
            End If
        Next j
    Next i

    ' 指定されたモードに基づいて結果を返す
    If result_mode = "all" Then
        MyXLOOKUP = result
    Else
        MyXLOOKUP = Application.WorksheetFunction.IfError(result, CVErr(xlErrNA))
    End If

    Exit Function

ErrorHandler:
    ' エラーが発生した場合の処理
    MyXLOOKUP = CVErr(xlErrValue)

End Function

Function IsMatch(lookup_value As Variant, cell_value As Variant, _
                 lookup_mode As String, Optional criteria As Variant) As Boolean

    ' 現在のセルがlookup条件と一致するか確認
    On Error Resume Next

    Select Case lookup_mode
        Case "exact"
            IsMatch = (cell_value = lookup_value)
        Case "partial"
            IsMatch = (InStr(1, cell_value, lookup_value) > 0)
        Case "criteria"
            If Not IsMissing(criteria) Then
                IsMatch = Evaluate(criteria)
            End If
    End Select

    On Error GoTo 0

End Function


主な変更点とエラー処理:

  1. エラー処理の改善:
    • On Error GoTo ErrorHandler を使用してエラーをハンドルしています。
    • エラーが発生した場合、関数は CVErr(xlErrValue) を返します。
  2. IsMatch 関数:
    • IsMatch 関数が条件に一致するかどうかを確認します。
    • On Error Resume Next を使用してエラーを無視し、条件に基づいて一致を確認します。
  3. 結果の返し方の改善:
    • result_mode に基づいて、最初の一致、最後の一致、またはすべての一致を返すようになりました。
  4. 複数条件対応:
    • lookup_values パラメータを Variant の配列に変更し、複数の検索値を受け入れるようになりました。
    • lookup_arrays と result_arrays パラメータも同様に配列となり、複数の検索対象および結果の範囲を指定できます。
  5. 複数条件に対する検索:
    • 各条件ごとに IsMatch 関数を呼び出し、全条件が一致した場合に一致とみなします。

使い方

Dim lookup_values() As Variant
Dim lookup_arrays() As Range
Dim result_arrays() As Range
Dim result As Variant

' 検索値
lookup_values = Array("Alice", "30")

' 検索対象の範囲
Set lookup_arrays(0) = Range("A1:A10")
Set lookup_arrays(1) = Range("C1:C10")

' 結果の範囲
Set result_arrays(0) = Range("B1:B10")
Set result_arrays(1) = Range("D1:D10")

' 複数条件での検索
result = myxlookup(lookup_values, lookup_arrays, result_arrays, "exact", "first")

使用する方はあまり変更なく簡単だと思います。

WorkSheetでの使用方法


ワークシートに戻り、セルに以下のような式を入力します。

=myxlookup(A1:A10, B1:B10, C1:C10, "exact", "first")

これは、A1:A10 の範囲で A1 の値を、B1:B10 の範囲で検索し、見つかった場合は対応する C1:C10 の値を返します。このようにして関数を呼び出すことができます。

WorkSheetでの使用『複数条件で使用する場合』

複数条件を使用する場合は、例えば以下のようになります。

=myxlookup({"Alice", "30"}, {A1:A10, C1:C10}, {B1:B10, D1:D10}, "exact", "first")

これは、A1:A10 で “Alice” を、C1:C10 で “30” を検索し、一致した場合は B1:B10 および D1:D10 の値を返します。

MyXLOOKUPをスピルに対応

MyXLOOKUP 関数にスピル対応を追加するためには、配列を返すように関数を変更する必要があります。具体的には、Application.Caller プロパティを使用して呼び出し元のセルの範囲を取得し、その範囲に結果を出力します。

※コードが長いので間を省略します。

Function MyXLOOKUP( _
    ByRef lookup_value As Variant, _
    ByRef lookup_array As Variant, _
    ByRef return_array As Variant, _
    Optional match_type As String = "exact", _
    Optional search_mode As String = "first") As Variant

    ' 関数の引数を処理

    ' ここに検索処理を実装

    ' 結果を出力
    MyXLOOKUP = result ' result は関数が返す結果の配列
End Function

これにより、MyXLOOKUP 関数をワークシート上で使用すると、結果が呼び出し元のセル範囲にスピルされるようになります。利用する際には、セル範囲を選択し、通常の関数のようにエンターキーを押すだけで複数の結果が表示されます。

XLOOKUP関数の問題点

EXCEL本体のXLOOKUP関数も、自作したMyXLOOKUP関数も共通なのですが、非常に処理が重いので大量データを処理することには向いていません。（通常のEXCELで利用する範囲なら十分です。C++でDLLを定義してまでやる場合、別の方法で処理を考えた方が良いと思います。）

もし、大量データをXLOOKUPしたい場合、C++でMyXLOOKUPを定義しDLLにして利用する事をお勧めします。

VBA-C++
【VBA】C++を利用して処理の高速化 近年、VBA（Visual Basic for Applications）はエクセルなどのMicrosoft Office製品で広く使...

C++コード作成

まず、MyXLOOKUP 関数を含む C++ ファイルを作成します。以下はサンプルの C++ コードです。

※記事が長くなったので基本的な実装のみです。

#include <windows.h>

extern "C" __declspec(dllexport) double __stdcall MyXLOOKUP(double lookup_value, double* lookup_array, double* return_array, double if_not_found, int match_type, int search_mode, int array_length) {
    for (int i = 0; i < array_length; ++i) {
        if (lookup_array[i] == lookup_value) {
            return return_array[i];
        }
    }

    return if_not_found;
}

このサンプルでは、lookup_value を lookup_array で検索し、対応する return_array の値を返します。見つからなかった場合は if_not_found を返します。

DLL のビルド

C++ コードをビルドして DLL を生成します。以下はコンパイルコマンドの一例です。

cl /LD myxlookup.cpp

これにより myxlookup.dll が生成されます。

Excel で使用する VBA コード

VBA コードを使用して DLL を呼び出します。

Declare Function MyXLOOKUP Lib "path\to\myxlookup.dll" Alias "MyXLOOKUP" (ByVal lookup_value As Double, lookup_array As Double, return_array As Double, ByVal if_not_found As Double, ByVal match_type As Long, ByVal search_mode As Long, ByVal array_length As Long) As Double

path\to\myxlookup.dll を実際の DLL のパスに変更してください。

Excel で関数を使用

Excel のセルで次のように関数を使用できます。

=MyXLOOKUP(A1, B1:B10, C1:C10, -1, 1, 1, COUNT(B1:B10))

この例では、A1 の値を B1:B10 で検索し、対応する C1:C10 の値を返します。見つからなかった場合は -1 を返します。

今回は型が数値のみ対応で実装しましたが、C++側でテンプレート関数を利用する事でどんな型にも対応した関数を作成可能です。

#include <windows.h>
#include <string>
#include <vector>

template <typename T>
extern "C" __declspec(dllexport) T __stdcall MyXLOOKUP(T lookup_value, T* lookup_array, T* return_array, T if_not_found, int match_type, int search_mode, int array_length) {
    // 文字列の場合は std::wstring を使用
    if constexpr (std::is_same<T, std::wstring>::value) {
        for (int i = 0; i < array_length; ++i) {
            if (lookup_array[i] == lookup_value) {
                return return_array[i];
            }
        }
    } else {
        // それ以外の型は通常の比較
        for (int i = 0; i < array_length; ++i) {
            if (lookup_array[i] == lookup_value) {
                return return_array[i];
            }
        }
    }

    return if_not_found;
}

// 明示的なインスタンス化
template double __stdcall MyXLOOKUP<double>(double, double*, double*, double, int, int, int);
template int __stdcall MyXLOOKUP<int>(int, int*, int*, int, int, int, int);
template std::wstring __stdcall MyXLOOKUP<std::wstring>(std::wstring, std::wstring*, std::wstring*, std::wstring, int, int, int);

VBA側は型に合わせて３つ宣言が必要です。

' Double
Declare PtrSafe Function MyXLOOKUP_Double Lib "path\to\myxlookup.dll" Alias "MyXLOOKUP" _
    (ByVal lookup_value As Double, lookup_array As Double, return_array As Double, _
     ByVal if_not_found As Double, ByVal match_type As Long, ByVal search_mode As Long, _
     ByVal array_length As Long) As Double

' Integer
Declare PtrSafe Function MyXLOOKUP_Integer Lib "path\to\myxlookup.dll" Alias "MyXLOOKUP" _
    (ByVal lookup_value As Integer, lookup_array As Integer, return_array As Integer, _
     ByVal if_not_found As Integer, ByVal match_type As Long, ByVal search_mode As Long, _
     ByVal array_length As Long) As Integer

' String
Declare PtrSafe Function MyXLOOKUP_String Lib "path\to\myxlookup.dll" Alias "MyXLOOKUP" _
    (ByVal lookup_value As String, lookup_array As String, return_array As String, _
     ByVal if_not_found As String, ByVal match_type As Long, ByVal search_mode As Long, _
     ByVal array_length As Long) As String

まとめ

XLOOKUP関数は柔軟性があり、多様な検索条件や抽出の要件に対応できます。そのため、Excelユーザーが効果的にデータを取得し業務を効率化する有効な手段となっています。

今回は、VBAでの実装方法も紹介しました。是非皆さんも工夫して業務を効率化して下さい。

C++ EXCEL VBA
こちらもおすすめ！！