実際のルックアップ問題の解決
メニューを表示するにはスワイプしてください
厳密一致はリレーショナルモデルに有用ですが、多くのビジネスシステムでは固定値ではなく範囲や閾値に基づいています。本章では、近似一致を利用して階層別価格設定ロジックや動的割引計算を構築します。
XLOOKUPによる近似一致
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], -1)
lookup_value: 評価対象の値lookup_array: 閾値となる値return_array: 返される結果-1: 検索値以下で最大の値を返す
近似一致を行う場合、検索配列は昇順に並べる必要があります。
階層別ロジック
階層テーブルは厳密一致ではなく最小閾値を定義します。
例：
| Min_Units | Discount_Rate |
|---|---|
| 1 | 0% |
| 5 | 5% |
| 10 | 10% |
| 20 | 15% |
ExcelはUnits値がどの閾値構造に該当するかを判定し、対応する割引率を返します。
ステップ1 階層別価格テーブルの作成
新しいワークシートを作成し、名前を次のように設定します：
Pricing_Tiers
次のデータセットを追加します：
Min_Units;Discount_Rate
1;0%
5;5%
10;10%
20;15%
このデータセットをExcelテーブルに変換します。
Min_Unitsが昇順に並んでいることを確認します。
ステップ2 割引率の検索式作成
Sales_Data内で新しい列を作成し、名前を次のように設定します：
Discount_Rate
最初の行に次の式を入力します：
=XLOOKUP([@Units],Pricing_Tiers[Min_Units],Pricing_Tiers[Discount_Rate],0,-1)
[@Units]: 現在行のUnits値Pricing_Tiers[Min_Units]: 閾値列Pricing_Tiers[Discount_Rate]: 返される割引値0: フォールバック値-1: 近似一致モード
結果をパーセンテージ形式で表示します。
ステップ3 割引後売上の計算
新しい列を作成し、名前を次のように設定します：
Discounted_Revenue
最初の行に次の式を入力します：
=[@Revenue]*(1-[@Discount_Rate])
[@Revenue]: 元のRevenue値[@Discount_Rate]: 適用される割引率
この計算により、ユニット数に応じて動的に調整されます。
ステップ4 階層の動的更新テスト
Pricing_Tiers内の値を変更します。
すべての関連計算が自動的に更新されることを確認します。
ステップ5 トップ製品ロジックの更新
以前のトップ製品の式を次のものに置き換えます：
=XLOOKUP(MAX(Sales_Data[Discounted_Revenue]),Sales_Data[Discounted_Revenue],Sales_Data[Product],"Not found")
検索は生売上ではなく割引後のパフォーマンスを評価します。
ステップ6 逆引きセクションの作成
Summaryシート内で、次の入力セクションを作成します：
Order_ID
ステップ7 顧客情報の取得
検索結果セルに次の式を入力します：
=XLOOKUP(M2,Sales_Data[Order_ID],Sales_Data[Customer_ID],"Not found")
M2: Order_ID入力Sales_Data[Order_ID]: 検索列Sales_Data[Customer_ID]: 返される値
ステップ8 製品情報の取得
別の結果セルに次の式を入力します：
=XLOOKUP(M2,Sales_Data[Order_ID],Sales_Data[Product],"Not found")
これらの式により、選択したOrder_IDから取引詳細を動的に取得できます。
ステップ9 動的検索のテスト
M2内の値を変更します。
以下を確認します：
- 顧客値が自動的に更新される
- 製品値が自動的に更新される
- 式が常に一致する取引を返す
1. match_mode = -1における XLOOKUP の動作は何ですか？
2. 近似一致検索を使用する場合、なぜ検索配列を並べ替える必要があるのか？
3. XLOOKUP が逆引き検索に適している理由は何か？
すべて明確でしたか？
フィードバックありがとうございます！
セクション 3. 章 3
AIに質問する
AIに質問する
何でも質問するか、提案された質問の1つを試してチャットを始めてください
セクション 3. 章 3
