【VBA】15.2 XLOOKUP関数を使ってみよう！

XLOOKUP関数の概要

XLOOKUP関数は、VLOOKUP関数の不便な点を改良した、現時点で最新のLOOKUP系の関数です。
この機会に、VBAでもVLOOKUP関数からは卒業し、XLOOKUP関数を使えるようになりましょう。

参考情報 ワークシート上の数式で使うXLOOKUP関数はこちら→【関数】 1.XLOOKUP関数を動画で視覚的にマスターしよう！

[overdoserの経験則] XLOOKUP関数を実際の業務で使用するケース

XLOOKUP関数は様々な場面での使用が想定される関数ですが、以下のようなケースはどの企業も日々の業務で発生することが想定され、その際にはXLOOKUP関数が力を発揮します。

• 全社員が期日までに受講する必要があるe-ラーニングがあるが、なかなか受講率が向上せず、度々全社員向けの周知メールを出すものの、それほど受講率が向上しないのが一般的と思われる。
  そのような場合に、e-ラーニングシステムで取得できる未受講者の社員番号を出力し、その社員番号で社内システムから取得した社員一覧データに対してXLOOKUP関数でメールアドレスや氏名や部署などの情報を取得し、メールの先頭に未受講者の部署名と氏名を動的に出力するメールを作成し、社員個別のメールを送信することにより、e-ラーニングの受講率を向上される。
  
  
• Excelマクロで、入力ファイルを読み込んで、各項目のフォーマット変換を行い、出力ファイルを作成する、ということは割と頻繁に発生すると思われる。
  入力ファイルの中で、ある列のデータが値ではなく、コード値だったりする場合がある。
  （例：値が男性→コード値が1、値が女性→コード値が2）
  このような場合、値とコード値の対応表を作業用シート上に展開し、そのコード値が入っている列に対しては、XLOOKUP関数でコード値→値に変換して、出力ファイルの所定の列にセットする、という処理を行う。

XLOOKUP関数の構文

XLOOKUP関数の構文は、以下の通りです。

上記の引数で、「2.検索範囲」と「3.戻り範囲」で列の一部を指定した場合、両方の行数が一致していないと、「#VALUE!」エラーとなります。
これを避けるため、上記の2つの引数は、列全体を指定することを推奨します。

XLOOKUP関数の戻り値は、以下の通りです。

• 検索値が検索範囲から見つかった場合：検索範囲で見つかった行の戻り範囲の値を返す。
• 検索値が検索範囲から見つからなかった場合：上記の「4.見つからない場合」の値を返します。

VLOOKUP関数からの改良点

XLOOKUP関数では、VLOOKUP関数の以下の点が改良されました。

この改良により、VLOOKUP関数での以下のような実装は不要となります。

• 検索値が検索範囲から見つからなかった場合に、戻り値が「#N/A」エラーと帰ってくる場合のエラー処理
• 「3.戻り範囲」が「2.検索範囲」の同一シートの右側に無かった場合の、このためだけの列のコピー処理

[emalabo式] XLOOKUP関数をExcelVBAで使用する際のコツ

XLOOKUP関数をExcelVBAで使用する場合、ExcelVBAの特性を生かし、各引数に以下のような変数を設定することにより、更に有効活用が可能です。

[emalabo式] XLOOKUP関数の使用例

XLOOKUP関数を実際に使用した例は、以下の通りです。

    ws_work.Cells(2, 2) = WorksheetFunction.XLookup(ws_work.Cells(2, 1), ws_employeelist.colomns(4), ws_employeelist.colomns(2), vbNullString)

1. 作業用シート（ws_work）のA2セルの値を取得し、その値で社員一覧シートの4列目のメールアドレスを検索する。
2. メールアドレスがヒットした場合、その行の氏名を戻り値とする。
3. メールアドレスがヒットしない場合、戻り値を空欄とする。
4. 上記で得られた戻り値を、workシートのB2セルにセットする。

[応用編] XLOOKUP関数を用いたコーディング例

今回のコーディング例は、以下の要件を実現するものです。

• 以下のような、A列にメールアドレス、B列に氏名の列のある表が、作業用シート（ws_work）にセットされているものとします。

• XLOOKUPの引数に以下の値をセットし、氏名を取得します。

• 作業用シート（ws_work）のA列の先頭行から最終行までをループし、B列にXLOOKUPからの戻り値をセットします。

上記のみを実行する、emalabo式のソースコードは、以下の通りです。

    For num_row = 2 To ws_work.Cells(Rows.Count, 1).End(xlUp).Row
        ws_work.Cells(num_row, 2) = WorksheetFunction.XLookup(ws_work.Cells(num_row, 1), ws_employeelist.Columns(4), ws_employeelist.Columns(2), vbNullString)
    Next num_row

以下のソースコードは、上記の使用例をサンプルプログラムに組み込み、実際に動作するようにしたものです。

Option Explicit

Sub extract_employeelist()

    Dim ws_macro As Worksheet           '社員データ抽出マクロファイル内の社員データ抽出マクロシートのワークシート変数
    Dim ws_work As Worksheet            '社員データ抽出マクロファイル内のworkシートのワークシート変数
    Dim wb_inputfile As Workbook        '入力ファイルのワークブック変数
    Dim ws_employeelist As Worksheet    '入力ファイルの社員一覧シートのワークシート変数
    Dim path_inputfile As String        '入力ファイルのフルパスを設定する変数
    Dim num_row As Long                 'Forループ分のカウンター
    
    Application.ScreenUpdating = False

    Set ws_macro = ThisWorkbook.Worksheets("社員データ抽出マクロ")
    Set ws_work = ThisWorkbook.Worksheets("work")
    
    If Right(ws_macro.Range("D3"), 1) <> "\" Then
        path_inputfile = ws_macro.Range("D3") & "\" & ws_macro.Range("C3")
    Else
        path_inputfile = ws_macro.Range("D3") & ws_macro.Range("C3")
    End If
    
    Workbooks.Open Filename:=path_inputfile
    Set wb_inputfile = ActiveWorkbook
    Set ws_employeelist = ActiveWorkbook.Worksheets("社員一覧")
    
    For num_row = 2 To ws_work.Cells(Rows.Count, 1).End(xlUp).Row
        ws_work.Cells(num_row, 2) = WorksheetFunction.XLookup(ws_work.Cells(num_row, 1), ws_employeelist.Columns(4), ws_employeelist.Columns(2), vbNullString)
    Next num_row
    
    wb_inputfile.Close SaveChanges:=True
    
    MsgBox "社員データの抽出が完了しました。"
    
    ws_macro.Activate
    
    ThisWorkbook.Save
    
    Application.ScreenUpdating = True
    
End Sub

上記のマクロを実行すると、以下の結果となります。

A3セルのメールアドレス「hanako@emalabo.com」は社員一覧シートのメールアドレスに存在しないため、B3セルは空欄となります。

[応用編] ソースコードの解説

エラーが出力された場合の対応

「型が一致しません」のエラーが発生するケース1

「1.検索値」で「2.検索範囲」を検索する場合、両方のデータ型が一致していないと、「型が一致しません」のエラーが発生します。
その場合は、予め「1.検索値」を「2.検索範囲」のデータ型に型変換をしておく必要があります。

「型が一致しません」のエラーが発生するケース2

以下の場合に、「型が一致しません」のエラーが発生します。

• XLOOKUP関数の戻り値をセルに出力するのではなく、一般的な変数に代入する場合。
• 「1.検索値」で「2.検索範囲」を検索し、「3.戻り範囲」で取得した値が「#DIV/0!」などのエラー値だったりした場合

この場合、戻り値を代入する変数にExcelVBAはエラー値を代入できないため、上記のエラーが発生します。
これを回避する方法としては、戻り値を代入する変数はVariant型とするという方法で私は回避するようにしています。

[次の記事]

emalabo.com

【VBA】15.3 Subtotal関数を使ってみよう！ VBAのSubtotal関数を使った集計方法を詳しく解説しています。AutoFilterとの組み合わせで表示セルの数などを求め、業務での使用例も紹介しています。実例コード付きで分か…