Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.学ぶ 動的およびパラメータ駆動型のルックアップ | Excelの数式
エクセル数式

動的およびパラメータ駆動型のルックアップ

メニューを表示するにはスワイプしてください

このワークブックはすでにリレーショナルなルックアップと動的レポートに対応しています。本章では、カテゴリ単位の集計を作成し、ユーザーが選択したシナリオに基づいて計算が動的に変化するパラメータ駆動型ロジックを導入します。

SUMPRODUCT の構造

=SUMPRODUCT(array1 * array2 * ...)
  • array1: 最初の計算配列；
  • array2: 2番目の計算配列；
  • TRUE: 1 に変換；
  • FALSE: 0 に変換。

これにより、単一の数式内で論理条件と集計を同時に実行できます。

INDIRECT の構造

=INDIRECT(ref_text, [a1])
  • ref_text: テキストを実際の参照に変換；
  • [a1]: 参照形式のオプション引数。

INDIRECT を使うことで、セルの値に応じて参照先を動的に切り替えることができます。

ステップ1 カテゴリ集計の作成
expand arrow

Summary シート内に、次のヘッダーを追加します:

Category
Total_Revenue
Total_Cost
Total_Profit
ステップ2 カテゴリリストの生成
expand arrow

A10 に次の数式を入力します:

=UNIQUE(Products[Category])

これで、カテゴリリストは新しいカテゴリが追加されるたびに自動で拡張されます。

ステップ3 カテゴリ別売上の計算
expand arrow

B10 に次の数式を入力します:

=SUMPRODUCT((XLOOKUP(Sales_Data[Product],Products[Product],Products[Category],"")=A10)*Sales_Data[Revenue])
  • XLOOKUP(...): 各商品のカテゴリ値を取得；
  • =A10: カテゴリが一致するかを判定；
  • Sales_Data[Revenue]: 集計対象の値。

数式を列全体にコピーします。

ステップ4 カテゴリ別コストの計算
expand arrow

C10 に次の数式を入力します:

=SUMPRODUCT((XLOOKUP(Sales_Data[Product],Products[Product],Products[Category],"")=A10)*XLOOKUP(Sales_Data[Product],Products[Product],Products[Cost],0)*Sales_Data[Units])

この数式はカテゴリごとの総コストを動的に計算します。

ステップ5 利益の計算
expand arrow

D10 に次の数式を入力します:

=B10-C10

数式を下までコピーし、すべての値を適切に書式設定します。

ステップ6 シナリオセレクターの作成
expand arrow

Summary シート内に、次のセルを作成します:

Active Pricing Scenario

次のオプションでデータの入力規則を設定します:

Pricing_Tiers
Pricing_Tiers_Promo
ステップ7 動的割引ルックアップの構築
expand arrow

Sales_Data 内で、以前の割引数式を次のものに置き換えます:

=XLOOKUP([@Units],INDIRECT(Summary!$F$9&"[Min_Units]"),INDIRECT(Summary!$F$9&"[Discount_Rate]"),0,-1)
  • Summary!$F$9: 選択されたシナリオテーブル；
  • INDIRECT(...): テキストを有効なテーブル参照に変換；
  • -1: 近似一致モード。

これにより、価格シナリオ間のルックアップが動的に切り替わります。

ステップ8 シナリオ切り替えのテスト
expand arrow

シナリオのドロップダウンで選択値を変更します。

次のことを確認します:

  • Discount_Rate が自動で更新される；
  • Discounted_Revenue が自動で更新される；
  • すべての関連計算が選択した価格モデルに応じて反映される。

1. このレッスンにおける SUMPRODUCT の役割は何ですか？

2. パラメータ駆動型モデルでINDIRECTが使用される理由は何ですか？

3. サマリーテーブルでUNIQUEとSUMPRODUCTを組み合わせて使用する主な利点は何ですか？

question mark

このレッスンにおける SUMPRODUCT の役割は何ですか？

正しい答えを選んでください

question mark

パラメータ駆動型モデルでINDIRECTが使用される理由は何ですか？

正しい答えを選んでください

question mark

サマリーテーブルでUNIQUEとSUMPRODUCTを組み合わせて使用する主な利点は何ですか？

正しい答えを選んでください

すべて明確でしたか？

どのように改善できますか？

フィードバックありがとうございます！

セクション 1. 章 17

AIに質問する

expand

AIに質問する

ChatGPT

何でも質問するか、提案された質問の1つを試してチャットを始めてください

セクション 1. 章 17
some-alt