ExcelのXLOOKUP関数の使い方

ExcelのXlookup関数とは何ですか? Excel で Xlookup を使用するにはどうすればよいですか? LuckyTemplates.com で調べてみましょう!

Excel を定期的に使用している場合は、ルックアップ関数についてよく知っているでしょう。分からない場合は、スプレッドシート内の行または列の特定のデータまたは値を検索するために使用される Excel 関数として単純に理解する必要があります。

この関数は、Excel スプレッドシートで大量のデータを処理する必要がある場合に特に便利です。Excel には、いくつかの種類の LOOKUP 関数があります。XLOOKUP もその 1 つです。では、XLOOKUP 関数とは何ですか、XLOOKUP の数式とその使用方法は何ですか? LuckyTemplates.com で調べてみましょう!

Excel の XLOOKUP 関数について知っておくべきこと

• XLOKUPとは何ですか?
  • XLOOKUP関数へのアクセス方法
  • XLOOKUP 関数の構文
• ExcelのXLOOKUP関数のメリットとデメリット
  • XLOOKUP関数のメリット
  • XLOOKUP関数のデメリット
• XLOOKUP関数の使い方
  • 追加の列インデックス番号はありません
  • デフォルトで完全一致
  • XLOOKUPは左側にあります
  • XLOOKUP を使用して範囲を検索する
  • XLOOKUP は HLOOKUP 関数を置き換えます
  • XLOOKUPは下から上に見ることができます

ExcelのXLOOKUP関数とは何ですか?

基本的に、XLOOKUP は、HLOOKUP、VLOOKUP、LOOKUP などの古い関数を置き換えます。データの垂直方向と水平方向の両方の検索をサポートします。

つまり、Excel の XLOOKUP 関数は、提供されたデータ セット内の値を水平方向と垂直方向の両方ですばやく検索するのに役立ちます。次に、さまざまな行と列に従って対応する値を返します。

XLOOKUP 関数を最大限に活用する方法はたくさんあります。

XLOOKUP関数へのアクセス方法

現在、XLOOKUP は Insiders プログラム ユーザーのみが利用できます。Insider プログラムに参加すると、誰でも最新の Excel 機能にアクセスできるようになります。Microsoft は間もなくこの機能をすべての Office 365 ユーザーに展開する予定ですが、残念ながら、Office 2010、2013、2016、および 2019 バージョンを実行しているユーザーは XLOOKUP を利用できません。

コンピューターで Microsoft 365 スイートを使用していない場合は、XLOOKUP 機能にアクセスするために Microsoft 365 にアップグレードする必要がある場合があります。Microsoft 365 を使用している場合は、Excel でこのオプションが有効になっていることがわかります。XLOOKUP は Office 365 Online でも利用できます。

XLOOKUP 関数の構文

XLOOKUP 関数の構文は、VLOOKUP 関数および HLOOKUP 関数の構文と似ています。以前にこれらを使用したことがある場合は、XLOOKUP を使用する方が便利であることがわかります。Excel の XLOOKUP 関数の構文は次のとおりです。

= XLOOKUP ( lookup_value , lookup_array , return_array , [if_not_found], [match_mode], [search_mode])

XLOOKUP 関数は最大 6 つの引数をサポートしており、その値は次のとおりです。

1. lookup_value (必須): 検索する値。

2. lookup_array (必須): ルックアップ値を検索する配列。

3. return_array (必須): ルックアップ値が見つかったときに値をフェッチして返したい配列。

4. [if_not_found] (オプション): 一致するものが見つからなかった場合に返される値です。

5. [match_mode] (オプション): この引数を使用すると、探している一致タイプを指定できます。それを指定するにはさまざまな値があります。

• 0 - 完全一致を検索します。値はlookup_array の値と正確に一致する必要があります。言及されていない場合、この値もデフォルトとして設定されます。
• -1 - 完全に一致するものを探し、見つかった場合は、次に小さい値に戻ります。
• 1 - 完全一致を検索し、見つかった場合は次に大きい値を返します。
• 2 - *、?のワイルドカードを使用して部分一致を実行します。～には特別な意味があります。

6. [search_mode] (オプション): lookup_array 内の XLOOKUP の検索モードを指定するために使用されます。同じものを指定するには、さまざまな値があります。

• 1 - 最初の項目から検索を実行します。何も指定しない場合は、この値がデフォルトとして設定されます。
• -1 - 最後の項目から逆方向検索を実行します。
• 2 -データを昇順に並べ替える必要がある場合、lookup_arrayでバイナリ検索を実行します。データが並べ替えられていない場合、エラーや間違った結果が生じる可能性があります。
• -2 -データを降順に並べ替える必要がある場合、lookup_arrayで二分検索を実行します。データが並べ替えられていない場合、エラーや間違った結果が生じる可能性があります。

ExcelのXLOOKUP関数のメリットとデメリット

XLOOKUP は、VLOOKUP や INDEX/ATCH に比べて依然として有利な関数です。しかし、それ自体の欠点もあります。

XLOOKUP関数のメリット

• 顎は垂直にも水平にも働きます。
• VLOOKUP や INDEX MATCH のように 4 つの引数ではなく、3 つの引数だけが必要です。
• デフォルトでは常に完全一致になります。
• 部分的な検索はワイルドカードを使用して実行できます。
• ルックアップは降順で実行できます。
• INDEX MATCH では 2 つの関数ではなく 1 つの関数を使用します。

XLOOKUP関数のデメリット

• オプションの引数は初心者にとっては複雑に見えるかもしれません。
• 2 つの範囲を選択し、スプレッドシート内のセルが多すぎる場合は、時間がかかる可能性があります。
• 同じ長さではない配列を検索して返すと、エラーが返されます。
• 検索範囲と戻り範囲の両方を覚えておいてください。

XLOOKUP Excel 関数を使用する利点

• XLOOKUP を使用すると、Excel で最もよく使用される数式がシンプルになり、エラーが発生しにくくなります。=XLOOKUP (検索するデータ、リスト、結果リスト) と記述するだけで、答えが得られます。値が見つからない場合は #N/A が得られます。
• デフォルトの検索結果は正確です。VLOOKUP の欠点の 1 つは、正しい結果を得るために最後のパラメータとして FALSE を指定する必要があることです。XLOOKUP は、デフォルトで完全一致を実行することでこの問題を修正します。必要に応じて、一致モード パラメータを使用して検索動作を変更できます。
• 4 番目のパラメータは、値が見つからない場合をサポートします。ほとんどのビジネス状況では、エラーを排除するために検索式を IFERROR または IFNA で「ラップ」する必要があります。XLOOKUP は 4 番目のパラメーターを提供するため、値が見つからない場合に必要なデフォルトの結果を知ることができます。
• XLOOKUP には、特殊な状況を見つけるための選択パラメータが用意されています。上または下から検索したり、特殊文字を使用したり、フィルタリングされたデータをより高速に検索したりできます。

XLOOKUP関数の使い方

XLOOKUP がどのように機能するかを理解するには、以下の例を見てください。この例では、列 A の ID ごとに列 F の部分を返す必要があります。

これは正確な結果を探す例ですが、XLOOKUP 関数に必要なのは 3 種類の情報だけです。

以下の画像は 5 つの引数を持つ XLOOKUP を示していますが、正しい結果を得るには最初の 3 つの引数が必要です。それでは、それらに焦点を当ててみましょう。

• Lookup_value : ルックアップ値
• Lookup_array : 検索範囲
• Return_array : 返す値を含む範囲

この例では、次の式を使用します。

=XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

次に、VLOOKUP に対する XLOOKUP の利点をいくつか見てみましょう。

追加の列インデックス番号はありません

VLOOKUP の 3 番目の引数は、テーブル範囲から返す情報の列数を指定します。XLOOKUP ではユーザーが戻り値の範囲を選択できるため、これは問題になりません。(この例では列 F)。

また、VLOOKUP とは異なり、XLOOKUP は選択したセルの残りのデータを確認できることを忘れないでください。

また、新しい列を追加するときに数式エラーが発生することもありません。スプレッドシートでこれが発生した場合、戻り範囲は自動的に調整されます。

デフォルトで完全一致

VLOOKUP を使用する場合、ユーザーは必要に応じて完全一致を指定する必要があります。ただし、XLOOKUP の場合、デフォルトは完全一致です。これにより 4 番目の引数が減り、新規ユーザーの間違いが確実に減ります。つまり、XLOOKUP は VLOOKUP よりも必要な質問が少なく、ユーザーフレンドリーです。

XLOOKUPは左側にあります

ルックアップ範囲を選択できるため、XLOOKUP は VLOOKUP よりも柔軟性が高くなります。XLOOKUP では、テーブル内の列の順序は関係ありません。

VLOOKUP は、テーブルの左端の列を検索し、右側の指定された数の列から返すことによって制限されます。

以下の例では、ID (列 E) を検索し、その人の名前 (列 D) を返す必要があります。

以下の式を使用してください。

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

XLOOKUP を使用して範囲を検索する

完全一致ほど一般的ではありませんが、検索式を使用して範囲内の値を見つけることができます。たとえば、使用金額に応じて割引を返したいとします。

今回は特定の値を探しているのではなく、割引を決定するために列 B の値が列 E のどの範囲に該当するかを知る必要があります。

XLOOKUP には、match_mode と呼ばれるオプションの 4 番目の引数 (デフォルトは絶対一致であることを覚えておいてください) があります。

XLOOKUP は VLOOKUP よりも適切に近似一致を見つけることができることがわかります。

検索値より (-1) 未満の最も近い一致、または (1) より大きい最も近い一致を検索するオプションがあります。? などのワイルドカード (2) を使用するオプションもあります。または *。VLOOKUP と同様、この設定はデフォルトでは有効になっていません。

この例の数式は、完全に一致するものが見つからない場合、検索値よりも小さい最も近い値を返します。

=XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,-1)

ただし、セル C7 にエラーがあり、64 ドルしか費やしていないため、0% 割引が返されるはずでしたが、これは割引を受ける基準を満たしていませんでした。

XLOOKUP 関数のもう 1 つの利点は、VLOOKUP のように昇順で検索範囲を必要としないことです。

ルックアップ テーブルの下部に新しい行を入力し、数式を開きます。角をクリックしてドラッグすると、使用範囲が広がります。

数式はエラーを即座に修正します。範囲テーブルの末尾に 0 があっても問題ありません。

XLOOKUP は HLOOKUP 関数を置き換えます

前述したように、XLOOKUP 関数は HLOOKUP 関数の代わりに使用することもできます。1 つの機能で 2 つの機能を置き換えることができます。これより優れたものは何でしょうか?

HLOOKUP 関数は水平方向に検索し、行ごとに検索します。

この関数はVLOOKUPほど有名ではありませんが、以下の例のようにA列にタイトルがあり、データが4行目と5行目にある場合に便利です。

XLOOKUP は、列方向と行方向の両方向に検索できます。

この例では、数式を使用して、列 A2 の名前に関連付けられた売上高を返します。行 4 で名前を調べ、行 5 の値を返します。

=XLOOKUP(A2,B4:E4,B5:E5)

XLOOKUPは下から上に見ることができます

通常、最初に出現する値を見つけるには、リストを上から下に探す必要があります。XLOOKUP には、search_mode という名前の 5 番目の引数があります。この引数を使用すると、リストの最後から検索を開始して、最後に存在する値を検索するようにリダイレクトできます。

以下の例では、列 A の各製品の在庫レベルを見つける必要があります。

ルックアップ テーブルは日付順に配置されます。前回のチェック (最後に発生した製品 ID) の在庫レベルを返したいと考えています。

XLOOKUP 関数の 5 番目の引数には 4 つのオプションがあります。ここでは、 「最後から最初に検索」オプションを使用します。

この例で使用される式は次のとおりです。

=XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,-1)

この式では、第 4 引数が省略されています。これはオプションであり、デフォルトが完全に一致するようにします。

XLOOKUP 関数は、VLOOKUP 関数と HLOOKUP 関数の両方の後継となることが期待されています。この記事では、XLOOKUP 関数の利点を示すために一連の例を使用しました。

XLOOKUP は、ルックアップ関数に関して大幅に改善されました。XLOOKUP 関数の唯一の制限は、下位互換性がないことです。スプレッドシートを古いバージョンの Microsoft Office Suite と共有すると、エラーが発生する可能性があります。

ただし、Office 365 Online から XLOOKUP にアクセスできます。XLOOKUP はまだ開発中であるため、Excel ユーザーがそれを採用するまでにはしばらく時間がかかるでしょう。

成功を祈っています！

XLOOKUPとVLOOKUPの違い

	VLOOKUP	XLOKUP
デフォルトの完全一致	N	Y
ルックアップの右側の値を返します	Y	Y
ルックアップの左側の値を返します	N	Y
行ごとに検索	N	Y
複数の値を返します	N	Y
ルックアップ列を分類する必要がある	おおよその一致のみ	二分探索のみ
上から下へ検索	Y	Y
下から上へ検索	N	Y
二分探索	N	Y
ルックアップ値が見つからない場合のメッセージをカスタマイズする	N	Y
ワイルドカードによる検索	Y	Y
完全一致検索	Y	Y
近似検索では次に小さい値が返されます	Y	Y
近似検索では次に大きい値が返されます	N	Y
ルックアップ値がソートされていない場合、間違った値が返される可能性があります	Y - 近似値	N - 近似値、 Y - バイナリ
テーブルを検索するために新しい列を追加すると、式が壊れる可能性があります	Y	N

XLOOKUP が VLOOKUP よりも優れているのはなぜですか?

VLOOKUP には独自の用途がありますが、XLOOKUP よりも機能が制限されています。このため、大量のデータを処理する必要がある場合には、XLOOKUP が最も一般的な選択肢になります。その結果、実行が速くなり、エラーが少なくなります。

• XLOOKUP はデータ範囲を制限しません。XLOOKUP は VLOOKUP よりも柔軟です。VLOOKUP は、テーブルの左端の列にのみある値を検索し、右側の対応する列から値を返すことができます。一方、XLOOKUP はより簡単な手順のみを必要とし、どちらの方向でも任意の列の値を返すことができます。
• XLOOKUP のデフォルトの結果が最も正確な値です。
• デフォルトでは、Excel の VLOOKUP 関数は常に近似結果を検索します。ただし、正確な結果が必要な場合は、range_lookup 引数に FALSE を入力する必要があります。
• XLOOKUP 関数は、特定の範囲でルックアップ値を検索し、常に正確な値をデフォルトとします。見つからない場合は、引数を編集して、検索値より小さいまたは大きい近似一致を見つけることができます。

その他の理由：

• XLOOKUP は列の挿入または削除を処理できます。
• XLOOKUP は垂直ルックアップと水平ルックアップを実装します。
• XLOOKUP は、よりスマートな近似一致を提供します。
• XLOOKUP に必要なセル参照は少なくなります。