Excel VBA XLOOKUP関数を使う方法

XLOOKUP関数は、VLOOKUPやHLOOKUPなどLOOKUP系関数の完全上位後継として進化した関数です。柔軟性が高くてとても使いやすい関数です。※関数の詳細については前記事をご覧ください。

簡単にXLOOKUP関数のメリットだけ列記すると
・検索範囲と戻り範囲は「配列」なので位置の制限がない（戻り範囲が別シートでもOK！）
・一致モードと検索モードが指定できる
・エラー処理の機能が付いている（IFERRORなどが不要！）
・スピル機能に対応している

など多くのメリットがあり、VLOOKUPを完全に置き換える仕様となっています。

XLOOKUP 関数はすごい！ 使用例も少しだけ解説しています

XLOOKUP 関数とはどんな関数か使用例を使って解説しています。XLOOKUP関数はVLOOKUP関数の強化版と言われています。でもVLOOKUPと同じようにも使えますが単なる強化ではなく全くの別物のすごい機能だということがわかります。

www.kurumico.com

くるみこ

VBAでXLOOKUP関数を使う方法について勉強しましょう。

実は、使ってみたくてググってみたんですけど、XLOOKUP関数をVBAで使う方法の情報って意外と少ないんですよね。よろしくお願いしますm(__)m

XLOOKUP関数をVBAで使う

XLOOKUP関数の解説記事で使った「日本百名山」データをそのまま使って、VBAでXLOOKUP関数を操作できる方法をテストしてみます。

完全一致検索の使用例

XLOOKUP関数は引数を指定しない場合は「完全一致検索」となります。テストでは「見つからない場合」の文字列だけ指定しておきます。

番号入力で情報を一連で取り出す（完全一致検索スピル抽出）

「戻り値範囲」を複数列に指定して「スピル」して取り出す例です。

「検索値」＝G2セル、「検索範囲」＝A2:A101、「戻り値範囲」＝B2:E101 です。
この数式を VBA で計算して「G6」セルに「G2:K2」と同じようスピルして返すことができるのでしょうか。

WorksheetFunction.XLookup

「G2:K2」と同じように返すために「戻り値範囲」を A2:E101 に変更しています。
VBAですから、数式のセル番地部分は「Range(“A2:E101”)」のように記述します。

test_01 戻り値範囲や書き込むセルを変えて動作テスト

Sub test_01()
    'ワークシートの数式をそのまま再現
    Range("G6") = WorksheetFunction.XLookup(Range("G2"), _
                        Range("A2:A101"), Range("A2:E101"), "該当なし")
    
    '戻り範囲をA列だけの単独（スピルしない）に設定
    Range("G6") = WorksheetFunction.XLookup(Range("G2"), _
                        Range("A2:A101"), Range("A2:A101"), "該当なし")
    
    '戻り値を書き込むセル範囲を「戻り値範囲」に合わせて広げてみる
    Range("G6:K6") = WorksheetFunction.XLookup(Range("G2"), _
                        Range("A2:A101"), Range("A2:E101"), "該当なし")
End Sub

１番目の結果は ✖、G6セルには何も表示されません。「該当なし」も表示されません。
２番目の結果は 〇、G6セルに戻り値「1」が書き込まれました。
３番目の結果は ✖、G6:K6セルには何も表示されません。「該当なし」も表示されません。

でも、1番目と3番目が ✖ なのは予想していたとおりです。「戻り値範囲」がスピルする設定の場合は戻り値が「動的配列」となっているため、そのままではセルに書き込まれません。

※次の項の最後にこのVBAの動作をGIF画像にしています。

test_02 動的配列を変数に代入する設定

'動的配列（スピル）を変数で受けてセル範囲に返す
Sub test_02()
    Dim r As Variant
    r = WorksheetFunction.XLookup(Range("G2"), _
                        Range("A2:A101"), Range("A2:E101"), "該当なし")
    Range("G6:K6") = r
End Sub

戻り値を書き込む範囲を「戻り値範囲」に合わせて Range(“G6:K6”) に変更しています。
変数(Variant型)に取得した結果(動的配列)を代入し、同範囲(サイズ)のセルに書き込む設定です。

結果は、G6:K6のセル範囲にスピルと同じデータが書き込まれました。

一連のテスト動作の状況を動画でご確認ください。(クリックで拡大できます)

VBAでは #SPILL! エラーは発生しない

#SPILL! エラーは、スピル範囲にデータが存在していたり結合セルがある場合に表示されます。

VBA の場合は、スピル範囲にデータがあった場合でも上書きして書き込んでしまいます！

ですので、実行時に書き込む範囲内にデータの有無を確認して、データがあった場合上書きするかどうか選択させるなどの処理も必要になるのではないかと思います。

今後、その部分のことは他の関数についても解説を予定しているので、そこで再び取り上げていこうと思います。

スポンサーリンク

Amazonセールで上手に買い物する【必須情報】

Amazonセールで上手に買い物するための「必須情報」です。プライムデーやブラックフライデーなどのビッグセールのほかタイムセールなどをお得に利用できるようにするための情報が満載です。記事内にはAmazonの該当ページへのリンクを豊富に設置

www.kurumico.com

まとめ（おわりに）

以上、VBAでXLOOKUP関数を使う方法について検証してみました。

スピル機能を有する関数を扱う場合は、戻り値は「動的配列」であることを必ず念頭におく必要があることがわかりました。

今回の例では、戻り範囲と同じサイズの書き込み先セル範囲を指定していますが、必要に応じて動的配列をセルに書き込む LOOP 処理が必要になりそうです。

くるみこ

いかがでしたか？
スピル機能を扱えるバージョンのExcelでは、動的配列を扱うケースが多くなるということですね。配列の勉強をしっかり復習しておいた方がいいですね。

VBAよりワークシート関数で扱ったほうが簡単で楽な気がしますけど(^^;

動的配列の復習も頑張ってみます。

【今後の記事について】

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思っています・・・・
・今後の記事にご期待ください(^^)/

スポンサーリンク

サンプルファイルをダウンロードできます

記事で使用したサンプルファイルをダウンロードできるようにページを設置していますので、こちら（このリンク先）からご利用ください

【Excel VBA】サンプルファイル_ダウンロードのページ

【Excel VBA】の記事内で使ったサンプルファイルを（Zipファイルで）ダウンロードできるようにしました。記事内では細かい部分の解説が不十分ですが、実際にファイルをダウンロードして実行することで使えるのかどうか見ていただけたら幸いです。

www.kurumico.com