マスター Excel ルックアップ関数:最適な戦略を選択するための実証済みのフレームワーク

 

ルックアップ関数を使用すると、データを効率的に取得できます。 Excel には複数の検索オプションが用意されているため、適切な戦略を選択するのは難しい場合があります。最適なアプローチを選択すると、最も信頼性の高い結果が得られます。このチュートリアルでは、適切な戦略を選択するための Excel ルックアップ関数フレームワークを作成します。このフレームワークは、経験豊富なユーザーがデータの形状、規模、意図に基づいてどのアプローチが最適であるかを判断するのに役立ちます。

上記のフローチャートに従って、適切な検索戦略を選択できます。

XLOOKUP:1 対 1 のルックアップに最適 (モダン Excel)

まず、何を探しているかを決めます。一致するレコードを 1 つだけ取得しますか、それとも条件に一致するすべてのレコードが必要ですか?

単一の結果、読みやすい数式、およびオプションの「見つからない」処理が必要な場合は、XLOOKUP を使用します。

注文、製品の詳細、顧客情報が別のテーブルに存在するサンプル販売データセットを考えてみましょう。さまざまなテーブルから販売情報を取得するための検索戦略を検討します。

注文に対する単価のルックアップ:

=XLOOKUP(D2, Products!$A$2:$A$7, Products!$D$2:$D$7, "Not found")

表形式の同様の数式:

=XLOOKUP(Orders[@SKU], Products[SKU], Products[UnitPrice], "SKU not found")

注文内の CustomerName の検索:

=XLOOKUP(Orders[@CustomerID], Customers[CustomerID], Customers[CustomerName], "Customer not found")

これらの数式は、さまざまなテーブル内の値を検索し、一致する 1 つのレコードを返します。 XLOOKUP は、最新の Excel での 1 対 1 検索のデフォルトの選択肢になります。それは以下を提供します:

• if_not_found によるエラー処理が組み込まれた、クリーンで読みやすい構文 引数
• 左方向および双方向ルックアップのネイティブ サポート
• 難解な一致タイプ パラメータを使用しない完全一致および近似一致モード

Microsoft 365 または Excel 2021 以降を使用していて、単一値を簡単に取得する必要がある場合は、XLOOKUP を使用してください。

INDEX/MATCH:互換性に最適でありながら強力

ファイルが古い Excel バージョンで動作する必要がある場合、またはチームの標準が INDEX/MATCH である場合は、INDEX/MATCH を選択します。

INDEX/MATCH による単価:

=INDEX(Products[UnitPrice], MATCH(Orders[@SKU], Products[SKU], 0))

この式は古いバージョンでは引き続き確実な選択肢ですが、多くのチームにとって XLOOKUP よりも誤読しやすく、大規模に維持するのが困難です。

フィルタ:1 対多の結果 (こぼれたリスト) に最適

自動的に更新されるレコードの動的なリストが必要な場合は、「フィルター」を選択します。基準に基づいてデータのサブセットを抽出し、それらの結果を自動的に更新し続けます。 FILTER は 1 対多の取得専用に構築されています。

• 一致するすべての行を動的スピル配列として返します
• ブール AND/OR ロジックを使用して複数の条件を処理します
• ソース データの変更に応じて自動的に拡大および縮小します
• 他の動的配列関数とシームレスに統合

CustomerID のすべての注文を表示:

=FILTER(Orders, Orders[CustomerID]=H2, "No orders found")

SKU P-101 のすべての西部地域の注文:

=FILTER(Orders, (Orders[Region]=H9)*(Orders[SKU]=H7), "No matches")

これらの数式は、FILTER が単一の値を返さず、こぼれた表形式の回答を返すため、XLOOKUP とは異なります。これは、XLOOKUP/INDEX-MATCH とは異なるジョブです。

Power Query:ルックアップが実際に「結合」である場合に最適 (データ モデリング)

フィルター処理された結果に対して変換を実行する必要がある場合は、Power Query がより良い選択肢になります。次の場合に使用します。

• データが大きい
• データは外部ソースから取得されるため、更新する必要があります。
• 反復可能な変換パイプラインが必要である
• ピボットテーブル/ダッシュボード用にクリーンな結合テーブルを構築したいと考えています。

Product 列と Customer 列 (UnitPrice、Product、CustomerName、Segment) を含むテーブルを作成しましょう。

ステップ (マージ/結合):

• 範囲をテーブルに変換します (CTRL+T を押します) ）。データはすでにテーブル形式になっています。
• データ に移動します タブ>> データの取得 を選択します>> テーブル/範囲からを選択します (注文から始めます ).

• Power Query エディター:ホーム >> [クエリの結合] を選択します。
  • 注文を結合します 製品 について テーブル
  • 注文[SKU]を選択します および商品[SKU]
  • 結合の種類: 左外側を選択します。 (すべての注文を保持)
  • [OK] をクリックします。

• • [展開] をクリックします。 アイコン>> 製品、カテゴリ、単価を選択します
  • [OK] をクリックします。

• 注文 を結合します 顧客とともに テーブル
  • 注文[CustomerID]と一致します 顧客[CustomerID] 宛

• • [展開] をクリックします。 アイコン>> 顧客名を選択 、セグメント
  • [OK] をクリックします。

• [閉じてロード] をクリックします。 Excel にデータをロードします。

これは、製品と顧客の詳細を含む、注文が強化された新しいテーブルです。

一度マージすれば、いつでも更新できるので、数千の行と複数の列にわたって検索を繰り返す必要がなくなります。

1 対多の場合、FILTER と Power Query のどちらを選択する

FILTER と Power Query はどちらも、高度な自動化された結果を提供します。場合によっては、1 対多の場合にユーザーが選択する際にジレンマに直面することがあります。

• インタラクティブなシート エクスペリエンスが必要な場合は、[フィルター] を選択します。条件セルを変更すると、リストが即座に更新されます。インタラクティブなダッシュボードに役立ちます。
• レポート (ピボットテーブル、グラフ、モデル テーブル) をフィードするために更新可能なデータセットが必要な場合、特に大きなデータの場合は、Power Query を選択します。

ヒントとコツ

• 出力が 1 つのセルの場合は、XLOOKUP (または互換性のため INDEX/MATCH) を選択します。
• 出力が自動的に拡張されるリスト/テーブルの場合は、[フィルター] を選択します。
• タスクが基本的にデータセットの結合であり、更新可能またはスケーラブルである必要がある場合は、Power Query を使用します。
• 多くの列 (価格、カテゴリ、名前、セグメントなど) にわたって同じ検索を実行する場合は、数式を繰り返すのではなく、Power Query の結合を強くお勧めします。

結論

このチュートリアルのフローチャートは、適切な検索戦略を選択するのに役立ちます。まず、データに必要な検索の種類を決定します。普遍的な「最適な」検索関数はありません。最適なアプローチはコンテキストによって決まります。関数だけでなくフレームワークをマスターすれば、検索戦略はニーズに合わせて拡張できます。適切な検索方法を選択するための戦略的感覚を養えば、ソリューションの構築がより速くなり、保守が容易になり、要件の進化に応じてより堅牢になります。

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