高度な Excel ルックアップをマスターする:XLOOKUP と INDEX-MATCH-MATCH で VLOOKUP を超えてください

Excel は、データ管理とデータ分析のための広範な検索テクニックを提供します。 VLOOKUP はデータの取得によく使用されますが、左側にルックアップ列が必要なことや柔軟性に欠けるエラー処理などの制限があります。これらを克服するために、XLOOKUP や INDEX-MATCH-MATCH などの高度な関数により、柔軟性、制御性、効率性が向上します。 XLOOKUP と INDEX-MATCH-MATCH を使用して効果的に活用する、VLOOKUP を超える高度な検索テクニックを紹介しましょう。

実際の例を使用して、高度な検索テクニックを適用する販売データセットを考えてみましょう。

XLOOKUP 高度な検索テクニック

XLOOKUP は、単一列または複数列の検索を行うための Excel の多用途関数です。任意の方向 (左から右、右から左、垂直、水平) で検索でき、カスタム エラー メッセージが表示され、検索のために並べ替えられたデータは必要ありません。 XlOOKUP 関数は、Excel 2021 および MS Office 365 ユーザーのみが使用できます。 XLOOKUP 関数は、ルックアップ値のデータ変更を自動更新します。

構文:

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

• ルックアップ値: 検索する値。
• ルックアップ配列: 検索する範囲または配列。
• 戻り配列: 結果を返す範囲または配列。
• [見つからない場合]; [オプション]: 一致するものが見つからなかった場合に返される値。
• [マッチモード]; [オプション]: 一致のタイプを指定します。完全一致、ワイルドカード一致、または近似一致を選択できます。
  • 0   – 完全一致 (デフォルト)。
  • 1   – 正確、またはその次の大きさ。
  • -1 – 正確、またはその次に小さい。
  • 2   – ワイルドカードの一致。
• [検索モード]; [オプション]: 最初から最後または最後から最初
  などの検索方向を決定します。
  • 1 – 最初から最後まで検索します。
  • -1 – 最後から最初に検索します。
  • 2 – 二分検索 (昇順)。
  • -2 – 二分検索 (降順)。

1.単一の基準に XLOOKUP を使用する

販売データセットから最も近い 100 ドルの売上額を見つけて、あらゆる注文に 100 ドル近くを費やす顧客を見てみましょう。次の数式を挿入します。

式:

=XLOOKUP(100, G2:G71, A2:G71, “見つかりません”, 1)

この数式は、G2:G71 の範囲で 100 を検索し、A2:G71 から対応する行を返します。 100 に最も近い値が見つからない場合は、「Not Found」と表示されます。

出力:

1007 2024 年 1 月 4 日 ダニエル・マルティネス 東 39.99 3 119.97

2.複数の条件で XLOOKUP を使用する

XLOOKUP を使用して複数の条件を連結することにより、より複雑な検索を実行できます。式を見てみましょう。

式:

=XLOOKUP(「メリッサ ロペス」 &「ウェスト」、C2:C71 &D2:D71、A2:G71)

この数式は、顧客の名前と地域を連結し、連結された検索配列内で結合された値を検索します。選択した範囲から対応するものを取得します。

出力:

1012 2024 年 1 月 6 日 メリッサ ロペス ウェスト 79.99 2 159.98

高度な検索手法のための INDEX-MATCH-MATCH

INDEX-MATCH-MATCH は、行と列の両方の基準に基づいて値を検索する必要がある場合に使用され、2D データ テーブルに最適です。

構文:

=INDEX(配列, MATCH(行ルックアップ値, 行ルックアップ配列, 0), =MATCH(列ルックアップ値, 列ルックアップ配列, 0))

• 配列: セル範囲には、取得する値が含まれています。
• MATCH(row_lookup_value, row_lookup_array, 0): 行番号を返します。
  • row_lookup_value: 行内で検索する値。
  • 行検索配列: 検索する行範囲。
  • 0 – 完全一致

• MATCH(column_lookup_value, column_lookup_array, 0): 列番号を返します。
  • column_lookup_value: 列内で検索する値。
  • column_lookup_array: 検索する列の範囲。
  • 0 – 完全一致

1.行と列の 2D ルックアップ

INDEX-MATCH-MATCH 式を使用して、特定の売上高の顧客の売上高を見てみましょう。次の数式を挿入します。

=INDEX(A2:G71, MATCH(“ジョン・スミス”, C2:C71, 0), MATCH(“売上金額”, A1:G1, 0))

この数式は、C2:C71 の範囲で John Smith を検索し、A1:G1 で「売上金額」を検索します。その後、A2:G71 の交差する値に戻ります。

出力:

99.98

2. 3D ルックアップのための高度な INDEX-MATCH-MATCH 式

INDEX-MATCH-MATCH は通常 2 次元ルックアップ用であるため、複数の MATCH 関数を含む配列数式を使用して 3 次元ルックアップに拡張できます。大規模なデータセットや複雑な条件の一致に最適で、柔軟性と機能の点で VLOOKUP を上回ります。

式:

=INDEX(G2:G71, MATCH(1, (C2:C71=”ジョン・スミス”) * (D2:D71=”サウス”) * (F2:F71=4), 0))

この数式は、列 C の顧客名、列 D の地域、列 F の数量の 3 つの条件に一致することによって売上金額を検索します。これらの条件は、乗算を AND 関数として使用して結合され、MATCH ですべての条件が満たされる行を見つけることができ、INDEX は G2:G71 から対応する値を返します。

出力:

129.99

XLOOKUP と INDEX-MATCH を使用する利点

VLOOKUP ではなく XLOOKUP を選択する理由

• 双方向検索 :XLOOKUP は両方向に値を検索できますが、VLOOKUP は左から右へのみ検索できます。
• 列インデックス番号なし :列の変更が影響しないように、列のインデックス番号を指定する必要はありません。
• デフォルトの完全一致 :デフォルトでは、XLOOKUP は完全一致を検索し、エラーを最小限に抑えます。
• XLOOKUP でのワイルドカード検索 :* や? などのワイルドカードを使用できます。テキスト検索を実行する場合は XLOOKUP を使用します。
• エラー処理 :一致するものが見つからなかった場合に何を返すかを指定できます。

VLOOKUP に対する INDEX-MATCH の利点

• 柔軟なテーブル構造 :INDEX-MATCH は列の挿入または削除の影響を受けません。
• 左検索 :VLOOKUP とは異なり、INDEX-MATCH は左側を参照できます。
• パフォーマンス :VLOOKUP を再計算するよりも MATCH の方が高速な場合があるため、大規模なデータセットではより効率的です。

結論

Excel での高度なデータ処理には、XLOOKUP や INDEX-MATCH-MATCH などの高度な検索手法が不可欠です。これらの関数は、データの変更を自動的に更新します。これらの関数は、従来の VLOOKUP を超える柔軟性、精度、パフォーマンスを提供するため、Excel スキルのレベルアップを目指す人にとって非常に重要です。

ソリューション付きの高度な Excel 演習を無料で入手しましょう!