Excel数据透视表中实现投资评级的加权标准差计算

投资数据集 #

问题背景 #

已通过Helper = Investment Rating * Commitment辅助列和数据透视表计算字段实现按Commitment加权的Investment Rating平均值，需添加加权标准差字段，要求随数据筛选、切片器自动更新，但直接修改加权平均字段汇总方式无效（计算字段为求和类型）。

解决方案：Power Pivot自定义度量值（最优方案） #

普通数据透视表的计算字段仅支持行内计算后求和，无法实现组内二次汇总逻辑（如先算组内加权平均，再计算加权平方偏差），使用Power Pivot的DAX度量值可完美解决动态计算问题：

1. 导入数据到Power Pivot：选中数据区域，点击「Power Pivot」选项卡 → 「添加到数据模型」。
2. 创建加权平均值度量值：在Power Pivot窗口的「度量值」选项卡点击「新建度量值」，输入：

   加权平均评级 = DIVIDE(SUM('数据表'[Investment Rating] * '数据表'[Commitment]), SUM('数据表'[Commitment]))
   

   （替换'数据表'为你实际的表名）
3. 创建加权标准差度量值：再次新建度量值，输入（以下为总体标准差，若需样本标准差，将分母改为SUM('数据表'[Commitment]) - 1）：

   加权评级标准差 = 
   VAR 组内加权平均 = [加权平均评级]
   VAR 加权平方偏差和 = SUM('数据表'[Commitment] * ('数据表'[Investment Rating] - 组内加权平均)^2)
   VAR 总权重 = SUM('数据表'[Commitment])
   RETURN
       SQRT(DIVIDE(加权平方偏差和, 总权重))
   

4. 生成动态数据透视表：返回Excel，插入基于数据模型的数据透视表，将Region等维度拖至行区域，把「加权平均评级」和「加权评级标准差」拖至值区域。此时筛选器、切片器生效时，两个指标会自动根据当前筛选上下文更新。

替代方案：普通数据透视表辅助列法（局限性大） #

若无法使用Power Pivot，需新增两个辅助列，但仅能实现静态分组计算，无法支持切片器/筛选器的动态更新：

1. 新增列加权平方偏差：公式为=Commitment*(Investment Rating - $H$1)^2（$H$1为全数据集的加权平均值），但此方法仅能计算全局标准差，无法按区域等分组动态更新，不推荐使用。

内容的提问来源于stack exchange，提问作者Chris Dartsmith