Microsoft Excel で XLOOKUP 関数を使用する方法

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

XLOKUPとは何ですか?

新しい XLOOKUP 関数には、 VLOOKUP の最大の制限のいくつかに対する解決策が含まれています。さらに、HLOOKUP も置き換えられます。たとえば、XLOOKUP は左側を検索でき、デフォルトで完全一致が設定され、列番号の代わりにセルの範囲を指定できます。 VLOOKUP はそれほど使いやすく、多機能ではありません。すべてがどのように機能するかを説明します。

現時点では、XLOOKUP は Insiders プログラムのユーザーのみが利用できます。誰でも Insiders プログラムに参加して、最新の Excel 機能が利用可能になるとすぐにアクセスできます。 Microsoft は間もなく、すべての Office 365 ユーザーへの展開を開始する予定です。

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 が間違って入力された場合の動作を見てみましょう。

次の数式では、エラーメッセージの代わりに「ID が正しくありません」というテキストが表示されます。

 =XLOOKUP(A2, $E $2 : $E $8 , $D $2 : $D $8 , "Incorrect ID" )

XLOOKUP を使用した範囲検索

完全一致ほど一般的ではありませんが、検索式の非常に効果的な使用方法は、範囲内の値を検索することです。次の例を見てみましょう。使用金額に応じて割引を返したいと考えています。

今回は特定の値を探しません。列 B の値が列 E の範囲内のどこに該当するかを知る必要があります。これにより、得られる割引が決まります。

XLOOKUP には、match mode という名前のオプションの 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 関数もに代わるものです。 1 つの機能で 2 つの機能を置き換えます。素晴らしい！

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 が登場し、まもなく事実上の検索式となるでしょう。