加重計算と条件付きロジック
メニューを表示するにはスワイプしてください
SUMIFS は標準的な条件付き合計に適していますが、分析計算では重み付けや埋め込みロジックが必要な場合があります。本章では、SUMPRODUCT を使用して集計システムを拡張します。
SUMPRODUCT の構造
=SUMPRODUCT(array1, [array2], [array3], ...)
array1: 最初の計算配列;[array2]: オプションの第2配列;[array3]: オプションの追加配列。
SUMPRODUCT は対応する値を掛け合わせ、その合計を算出します。
数式内の論理条件は自動的に次のように変換されます:
TRUE = 1;FALSE = 0。
加重平均の構造
=SUMPRODUCT(values * weights) / SUM(weights)
values: 評価対象の指標;weights: 重要度係数。
加重平均は、大きな取引が最終結果により大きな影響を与えます。
ステップ1 注文ごとの平均Revenueを計算
入力:
=SUMPRODUCT(Sales_Data[Revenue]) / COUNTA(Sales_Data[Revenue])
SUMPRODUCT(...): Revenue の合計;COUNTA(...): Revenue 行数。
この数式は、1件あたりの平均 Revenue を返します。
ステップ2 条件付き平均を作成
入力:
=SUMPRODUCT((Sales_Data[Region]="North")*Sales_Data[Revenue]) / SUMPRODUCT((Sales_Data[Region]="North")*1)
(Sales_Data[Region]="North"): 論理条件;*Sales_Data[Revenue]: フィルタリングされた Revenue 値;SUMPRODUCT(...*1): 条件付き行数。
この数式は、North地域のみの平均 Revenue を計算します。
ステップ3 加重平均割引率を計算
入力:
=SUMPRODUCT(Sales_Data[Discount_Rate]*Sales_Data[Revenue]) / SUM(Sales_Data[Revenue])
Sales_Data[Discount_Rate]: 割引率;Sales_Data[Revenue]: 重み付け要素。
高い Revenue の取引が平均割引率により大きな影響を与えます。
ステップ4 ハイティアRevenue分析を作成
Summary テーブル内で入力:
=SUMIFS(Sales_Data[Revenue],Sales_Data[Region],A2,Sales_Data[Performance tier],"High")
Sales_Data[Region],A2: 地域フィルター;Sales_Data[Performance tier],"High": パフォーマンスフィルター。
この数式は、ハイパフォーマンス取引による Revenue のみを返します。
ステップ5 ハイティア注文数を作成
入力:
=COUNTIFS(Sales_Data[Region],A2,Sales_Data[Performance tier],"High")
この数式は、選択した地域のハイティア注文数をカウントします。
ステップ6 ハイティアRevenue比率を計算
入力:
=E2/C2
E2: ハイティア Revenue;C2: 総 Revenue。
この数式は、ハイパフォーマンス取引による Revenue の割合を測定します。
ステップ7 結果を書式設定
適用:
- Revenue 指標に通貨書式;
- 割合や割引計算にパーセント書式。
ステップ8 動的動作をテスト
Sales_Data に追加行を挿入。
確認事項:
- 平均 Revenue が自動で再計算されること;
- 加重割引計算が自動で更新されること;
- ハイティア指標が自動で更新されること。
1. なぜSUMPRODUCTは加重計算に役立つのか？
2. SUMIFSのSUMPRODUCTに比べた主な制限は何か？
3. SUMPRODUCTを用いた加重平均の式で、分母は何を表すか？
すべて明確でしたか？
フィードバックありがとうございます！
セクション 1. 章 5
AIに質問する
AIに質問する
何でも質問するか、提案された質問の1つを試してチャットを始めてください
セクション 1. 章 5
