如何在Excel数据透视表中计算行内最大值与最小值的差值？

需求说明 #

需要在透视表中为每个Name对应的行添加一列，计算其所有Value的最大值减最小值的差值：

• 当该Name下只有单个Value时，返回0
• 其余情况返回max(Value)-min(Value)

示例数据 #

Name           State    Value
Albert          MA      1
Albert          MA      2
Albert          TX      3
Brittney        CA      5
Brittney        CA      3
Brittney        CA      2
Brittney        CA      1
Franklin        AL      3
Franklin        AL      2
Franklin        AL      3
Franklin        AL      4
Franklin        NV      4
Franklin        AK      9
Franklin        MO      2

当前问题 #

尝试创建计算字段，公式为=max(Value)-min(Value)，但无论选择求和、平均、计数等汇总方式，结果始终显示为0。

解决方案 #

方法1：使用Power Pivot（推荐） #

1. 选中数据源，点击数据选项卡 → 从表格/范围，将数据导入Power Pivot
2. 在Power Pivot的建模选项卡中，点击新建列，输入公式：

   Largest Distance = 
   VAR CurrentName = 'Table'[Name]
   VAR NameValues = CALCULATETABLE(VALUES('Table'[Value]), 'Table'[Name] = CurrentName)
   VAR ValueCount = COUNTROWS(NameValues)
   RETURN
       IF(ValueCount <= 1, 0, MAXX(NameValues, [Value]) - MINX(NameValues, [Value]))
   

3. 回到Excel，插入数据透视表时选择使用此工作簿的数据模型，将Name拖到行区域，Largest Distance拖到值区域，汇总方式选择求和（DAX列已完成计算，求和不影响结果）

方法2：添加辅助列后创建透视表 #

1. 在原始数据中新增一列，命名为Largest Distance，输入公式（假设数据从A2单元格开始）：

   =IF(COUNTIF($A:$A,A2)=1,0,MAXIFS($C:$C,$A:$A,A2)-MINIFS($C:$C,$A:$A,A2))
   

2. 下拉填充公式到所有行
3. 基于包含辅助列的数据创建透视表，将Name拖到行区域，Largest Distance拖到值区域，汇总方式选择求和或最大值（同一Name的辅助列值一致）

为什么直接用计算字段不行？ #

Excel透视表的计算字段是基于汇总后的值进行计算，而非原始行数据。max(Value)和min(Value)在计算字段中会被解析为当前行的汇总值（比如求和后的Value），因此两者相减结果为0。

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