MicrosoftExcelでXLOOKUP関数を使用する方法
公開: 2022-01-29
Excelの新しいXLOOKUPはVLOOKUPに置き換わるものであり、Excelで最も人気のある機能の1つに強力な代替手段を提供します。 この新しい関数は、VLOOKUPの制限のいくつかを解決し、追加の機能を備えています。 知っておくべきことは次のとおりです。
XLOOKUPとは何ですか？
新しいXLOOKUP関数には、VLOOKUPの最大の制限のいくつかに対するソリューションがあります。 さらに、HLOOKUPの代わりにもなります。 たとえば、XLOOKUPは左側を見ることができ、デフォルトでは完全一致になり、列番号の代わりにセルの範囲を指定できます。 VLOOKUPは、これほど使いやすく、用途が広いわけではありません。 すべてがどのように機能するかをお見せします。
現在のところ、XLOOKUPはインサイダープログラムのユーザーのみが利用できます。 誰でもInsiderプログラムに参加して、最新のExcel機能が利用可能になり次第アクセスできます。 Microsoftは、まもなくすべてのOffice365ユーザーに展開を開始します。
XLOOKUP関数の使用方法
XLOOKUPの動作例を見てみましょう。 以下のデータ例を見てください。 列Aの各IDについて、列Fから部門を返します。
これは、古典的な完全一致ルックアップの例です。 XLOOKUP関数に必要な情報は3つだけです。
次の画像は、6つの引数を持つXLOOKUPを示していますが、完全に一致させるために必要なのは最初の3つだけです。 それでは、それらに焦点を当てましょう。
- Lookup_value：探しているもの。
- Lookup_array：どこを見ればよいか。
- Return_array：返す値を含む範囲。
この例では、次の式が機能します。 =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)
ここで、XLOOKUPがVLOOKUPよりも優れているいくつかの利点を調べてみましょう。
列インデックス番号はもうありません
VLOOKUPの悪名高い3番目の引数は、テーブル配列から返す情報の列番号を指定することでした。 XLOOKUPを使用すると、戻る範囲を選択できるため、これは問題ではなくなりました（この例では列F）。
また、VLOOKUPとは異なり、XLOOKUPは選択したセルの左側のデータを表示できることを忘れないでください。 これについては、以下で詳しく説明します。
また、新しい列が挿入されたときに数式が壊れてしまうという問題もなくなりました。 それがスプレッドシートで発生した場合、戻り範囲は自動的に調整されます。
完全一致がデフォルトです
VLOOKUPを学習するとき、完全一致を指定する必要がある理由は常に混乱していました。
幸い、XLOOKUPはデフォルトで完全一致に設定されています。これは、ルックアップ式を使用するはるかに一般的な理由です）。 これにより、その5番目の引数に答える必要性が減り、数式を初めて使用するユーザーによる間違いが少なくなります。
つまり、XLOOKUPはVLOOKUPよりも質問が少なく、ユーザーフレンドリーで、耐久性もあります。
XLOOKUPは左を見ることができます
ルックアップ範囲を選択できるため、XLOOKUPはVLOOKUPよりも用途が広くなります。 XLOOKUPでは、テーブル列の順序は重要ではありません。
VLOOKUPは、テーブルの左端の列を検索してから、指定された数の列から右に戻ることによって制約されていました。
以下の例では、IDを検索し（列E）、個人の名前を返す必要があります（列D）。
次の式でこれを実現できます。 =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)
見つからない場合の対処方法
ルックアップ関数のユーザーは、VLOOKUPまたはMATCH関数が必要なものを見つけられない場合に表示される＃N / Aエラーメッセージに精通しています。 そして、多くの場合、これには論理的な理由があります。
したがって、ユーザーは、このエラーが正しくないか有用ではないため、このエラーを非表示にする方法をすばやく調査します。 そしてもちろん、そうする方法もあります。
XLOOKUPには、このようなエラーを処理するための独自の「見つからない場合」の引数が付属しています。 前の例で実際に動作しているのを見てみましょう。ただし、IDの入力が間違っています。
次の数式では、エラーメッセージの代わりに「IncorrectID」というテキストが表示されます。 =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,"Incorrect ID")
範囲ルックアップにXLOOKUPを使用する
完全一致ほど一般的ではありませんが、ルックアップ式の非常に効果的な使用法は、範囲内の値を検索することです。 次の例を見てください。 使用金額に応じて割引を返したい。
今回は特定の値を探していません。 列Bの値が列Eの範囲内のどこにあるかを知る必要があります。これにより、獲得した割引が決まります。
XLOOKUPには、一致モードという名前のオプションの5番目の引数があります（デフォルトでは完全一致になります）。
XLOOKUPは、VLOOKUPよりも近似一致で優れた機能を備えていることがわかります。
探した値が（-1）より小さい、または（1）より大きい最も近い一致を見つけるオプションがあります。 ？などのワイルドカード文字（2）を使用するオプションもあります。 または *。 この設定は、VLOOKUPの場合のようにデフォルトではオンになっていません。
この例の式は、完全に一致するものが見つからない場合に、検索された値よりも小さい値を返します。 =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)
ただし、セルC7に誤りがあり、＃N / Aエラーが返されます（「見つからない場合」引数は使用されませんでした）。 64の支出は割引の基準に達しないため、これは0％の割引を返すはずです。
XLOOKUP関数のもう1つの利点は、VLOOKUPのようにルックアップ範囲を昇順にする必要がないことです。
ルックアップテーブルの下部に新しい行を入力してから、数式を開きます。 角をクリックしてドラッグし、使用範囲を拡大します。
式はすぐにエラーを修正します。 範囲の一番下に「0」がある場合は問題ありません。
個人的には、ルックアップ列でテーブルを並べ替えます。 一番下に「0」があると、私は夢中になります。 しかし、公式が破られなかったという事実は素晴らしいです。
XLOOKUPはHLOOKUP関数も置き換えます
前述のように、XLOOKUP関数もHLOOKUPを置き換えるためにここにあります。 2つを置き換える1つの機能。 優れた！
HLOOKUP関数は水平ルックアップであり、行に沿って検索するために使用されます。
兄弟のVLOOKUPほどよく知られていませんが、ヘッダーが列Aにあり、データが行4と5に沿っている以下のような例に役立ちます。
XLOOKUPは、列の下と行に沿って、両方向を見ることができます。 2つの異なる関数はもう必要ありません。
この例では、数式を使用して、セルA2の名前に関連する売上値を返します。 行4に沿って名前を見つけ、行5から値を返します。 =XLOOKUP(A2,B4:E4,B5:E5)
XLOOKUPは下から上を見ることができます
通常、値の最初の（多くの場合のみ）出現を見つけるためにリストを探す必要があります。 XLOOKUPには、検索モードという名前の6番目の引数があります。 これにより、ルックアップを一番下から開始するように切り替え、代わりにリストをルックアップして最後に出現する値を見つけることができます。
以下の例では、列Aで各製品の在庫レベルを検索します。
ルックアップテーブルは日付順になっており、製品ごとに複数の在庫チェックがあります。 前回チェックした時（最後に発生した商品ID）の在庫レベルを戻したい。
XLOOKUP関数の6番目の引数には、4つのオプションがあります。 「最後から最初まで検索」オプションの使用に関心があります。
完成した数式を次に示します。 =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)
この式では、4番目と5番目の引数は無視されました。 これはオプションであり、完全一致のデフォルトが必要でした。
切り上げする
XLOOKUP関数は、VLOOKUP関数とHLOOKUP関数の両方の後継として待望されています。
この記事では、XLOOKUPの利点を示すために、さまざまな例を使用しました。 その1つは、XLOOKUPをシート、ワークブック、およびテーブル全体で使用できることです。 この記事では、理解を助けるために例を単純にしています。
動的配列が間もなくExcelに導入されるため、値の範囲を返すこともできます。 これは間違いなくさらに調査する価値のあるものです。
VLOOKUPの日には番号が付けられています。 XLOOKUPはここにあり、まもなく事実上のルックアップ式になります。