XLOOKUPとは
XLOOKUPとは、2019年にエクセルに新しく追加された関数で、
スプレッドシートにも導入されています。
◯LOOKUPといえば
VLOOKUPやHLOOKUPが真っ先に思い浮かびますが
「使い方調べたけれど、イマイチよくわからない」
「他の関数とどう違うかが理解できていない」
という方も多いのではないでしょうか。
今回は、これらの関数とXLOOKUPの違いや、
詳しい使用方法について解説いたします。
まずはサクッと使いたい！
「細かいことはいいから、とりあえず使えるようにしたい！」
という方に向けて、一番シンプルな使用方法を解説します。
XLOOKUPの基本的な使い方はこちら。
=XLOOKUP( [探したい値※1] , [※1を探す範囲] , [結果として出力する範囲] )上記以外にも細かい設定が可能ですが、
まずはこの3点を抑えましょう。
【XLOOKUPの参照の流れ】
VLOOKUPやHLOOKUPと何が違うの？
ここまで確認してみると、
「従来の関数と何が違うの？」
と、疑問に思われる方もいるかもしれません。
しかし、過去の関数と比べると
XLOOKUPは遥かに柔軟性が増しています！
具体的な違いは、以下の通りです。
- 縦横どちらにも使える
- 左(上)も参照可能
- 複数列(行)返せる
- オプションの指定が豊富
縦横どちらにも使える
まず一番大きい変化はこの点でしょう。
従来は
横方向の参照：VLOOKUP
縦方向の参照：HLOOKUP
このように、横向きに参照したい場合と
横向きに参照したい場合で、関数が分かれていました。
しかしXLOOKUPでは、
縦横いずれも参照可能なため、
これさえ覚えればOKになります！
左(上)も参照可能
この点は、LOOKUP系を多用する人には朗報でしょう。
いままでVLOOKUPでは、
検索対象より右側にある値しか、
結果として返すことはできませんでした。
しかしXLOOKUPでは、「 [結果として出力する範囲]」に
検索値よりも左側にある列を出力することができます。
複数列の結果を返せる
XLOOKUPでは、結果の範囲を複数列指定できるため
まとめて結果を出せるようになったのも大きなポイントです。
オプションの指定が豊富
従来の関数では、
・検索値が無い場合エラーになる
・検索値重複の場合、上の結果が優先される
といった制約が多くありました。
しかしXLOOKUPでは、
オプションの指定により、
これらの挙動を任意で変更することが可能となりました。
オプションの使い方については、次の項で詳しく説明します。
オプションでさらに便利に使おう！
上述のとおり、XLOOKUPには複数のオプションが追加され、
従来のLOOKUP系と比べて
より柔軟に結果のコントロールができるようになりました。
以下の赤字部分がオプション設定です。
=XLOOKUP( [探したい値※1] , [※1を探す範囲] , [結果として出力する範囲], [見つからない場合に返す値] , [一致モード] , [検索モード])※オプションの指定は全て任意です。
指定が無い場合はデフォルト値として実行されます。
それぞれ見ていきましょう。
見つからない場合に返す値
例えばこのような表に対して以下の関数を実行したとします。
=XLOOKUP( “西園寺” , B3:B6 , C3:D6 )しかし表中に「西園寺」という名字は存在しないため
XLOOKUPでは値を返すことができません。
そのような場合に、表示する結果を指定できるのが
このオプションです。
上記の関数を
=XLOOKUP( “西園寺” , B3:B6 , C3:D6 , “見つかりません”)このように記述すると、
結果は「見つかりません」
が返ってきます。
このオプションのデフォルト値は
「#N/A」つまりエラー値となります。
ですので、[見つからない場合に返す値] を指定せずに
検索が無い場合は、エラー値が結果として出力されます。
一致モード
一致モードの指定方法は以下の通りとなります。
- 0（デフォルト）：完全一致
- -1：完全一致がなければ「次に小さい値」
- 1：完全一致がなければ「次に大きい値」
- 2：ワイルドカード一致（*, ? が使える）
-1,1の指定は、文字列でも使用は可能です。
ひらがな、カタカナ、ローマ字の場合、
返り値の順序は辞書順になります
ただし、漢字の場合、
返り値が「ユニコード順」となるため
意図した結果が得られない場合があるので、
使用する際は注意が必要です。
※公式ドキュメントには記載がありませんが、
-1,1を指定する場合、
「元の表がソートされている必要がある」
という情報を一部で見かけました。
実際の挙動は確認がとれていませんが、
利用する際は、事前に並び替えを行ってからの方が、
より意図に沿った結果を得られる可能性が高いようです。
検索モード
「上から検索するか、下から検索するか」を指定できるオプションです。
各指定値は下記の通りとなります。
- 1（デフォルト）：先頭から検索
- -1：末尾から検索（後方検索）
- 2：昇順バイナリ検索（要昇順ソート）
- -2：降順バイナリ検索（要降順ソート）
このオプションは、
VLOOKUPと比較すると特に分かりやすいです。
たとえばこのような表から
「山田」の ”名前” を参照しようとした場合、
VLOOKUPでは「先頭から検索」しかできなかったため、
結果は「太郎」しか返すことができませんでした。
しかしXLOOKUPで「末尾から検索」を指定すれば
「花子」を結果として出力することができます。
表、データベースの特性や、
取得したい結果に応じて使い分けると良さそうです。
オプションの2,-2は、簡潔に説明すると
「事前に並べ替えておく必要がある代わりに、
より高速な処理を可能にする」
といったオプションです。
ただ、一般作業レベルだと
活用シーンはあまり多くないかと思われるため、
こんかい詳細の説明は省略させて頂きます。
まとめとチェックポイント
最後に、XLOOKUPを使用する際の
チェックポイントを確認しましょう。
「探す範囲」は必ず1行(列)
=XLOOKUP( [探したい値※1] , [※1を探す範囲] , [結果として出力する範囲] )※オプションは省略しています
このように記述するわけですが、
第2引数の [※1を探す範囲] は、
必ず1行(横方向に使う場合は1列)である必要があります。
うまく実行できない場合は、
このパラメーターが複数行・複数列で指定されていないか確認しましょう。
手前のオプションは省略できない
オプションの説明で、指定は任意の旨お伝えしましたが、
「前のオプションを省略すると、意図した動作にならない」
場合がありますので、注意してください。
例えば
「一致モードは指定したいけれど、見つからなかった場合の値は指定不要」
といった場合
=XLOOKUP([検索値] , [検索範囲] , [結果範囲] , -1 )このような記述はできません。
(正確に言うと実行はできますが、この「-1」は「見つからなかった場合の結果値」として判定されます)
以上、今回は「XLOOKUP」の詳しい使い方と
使用時の注意点についてお伝えいたしました。
VLOOKUPやHLOOKUPと比べると、
格段に柔軟性がアップした「XLOOKUP」
使い勝手が良い故に、少々使い方に戸惑う場合もあるかと思います。
コメント