MicrosoftExcelでXLOOKUP関数を使用する方法

Excelの新しいXLOOKUPはVLOOKUPに置き換わり、Excelで最も人気のある機能の1つに強力な代替手段を提供します。この新しい関数は、VLOOKUPの制限のいくつかを解決し、追加の機能を備えています。知っておくべきことは次のとおりです。

XLOOKUPとは何ですか？

新しいXLOOKUP関数には、VLOOKUPの最大の制限のいくつかに対するソリューションがあります。さらに、HLOOKUPの代わりにもなります。たとえば、XLOOKUPは左側を見ることができ、デフォルトでは完全一致になり、列番号の代わりにセルの範囲を指定できます。VLOOKUPは、これほど使いやすく、用途が広いわけではありません。すべてがどのように機能するかをお見せします。

現時点では、XLOOKUPはInsiderプログラムのユーザーのみが利用できます。誰でもインサイダープログラムに参加して、最新のExcel機能が利用可能になり次第アクセスできます。マイクロソフトはまもなくすべての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には、このようなエラーを処理するための独自の「ifnotfound」引数が組み込まれています。前の例で動作を確認しましょう。ただし、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はここにあり、まもなく事実上のルックアップ式になります。