如何在Excel数据透视表中嵌入自定义计算实现Uplift及自动判定？

问题梳理 #

需要为A-S列的透视表添加T、U列计算，要求透视表增删行时公式自动同步：

• T列（Uplift）公式：=IFERROR(AVERAGE(K3:M3)/AVERAGE(B3:J3)-1,0)
• U列（判定）：修正原公式语法后实现逻辑：当S列≤5时显示-；当G-L列存在大于5的值且M列=0时显示Not OK，否则显示OK

最优实现：结构化表自动同步 #

这是兼容性最好的方案，支持所有Excel版本：

1. 选中透视表内任意单元格，按下Ctrl+T，勾选「表包含标题」并确认，将透视表转为结构化表
2. 在T列表头输入Uplift，在T3单元格输入公式：

   =IFERROR(AVERAGE([@[K]:[M]])/AVERAGE([@[B]:[J]])-1,0)
   

   公式会自动填充至所有行，后续透视表增删行时，T列公式会自动同步更新
3. 在U列表头输入「判定」，在U3单元格输入修正后的公式（修复原公式的语法错误）：

   =IF([@S]<=5,"-",IF(AND(SUM(--([@[G]:[L]]>5))>0,[@M]=0),"Not OK","OK"))
   

   同理，该公式也会随结构化表的行变化自动更新

备选方案：动态数组溢出（Excel 365/2021专属） #

若使用Excel 365或2021，可借助动态数组实现自动溢出，无需转换为结构化表：

• T列（输入到T3单元格）：

  =BYROW(OFFSET($B$3,0,0,COUNTA($A:$A)-2,9),LAMBDA(row_data,AVERAGE(OFFSET(row_data,0,9,1,3))/AVERAGE(row_data)-1))
  

  公式会自动填充所有透视表数据行，无需手动拖拽
• U列（输入到U3单元格）：

  =BYROW(OFFSET($G$3,0,0,COUNTA($A:$A)-2,6),LAMBDA(row_data,IF(OFFSET(row_data,0,5)<=5,"-",IF(AND(SUM(--(row_data>5))>0,OFFSET(row_data,0,6)=0),"Not OK","OK"))))
  

语法修正说明 #

原判定公式中的SUM(G3:L3>5,M3=0)存在逻辑错误：

• G3:L3>5返回布尔值数组，直接用SUM无法正确统计数量，需用--(G3:L3>5)将布尔值转为1（真）或0（假），再求和判断是否大于0，以此确认G-L列是否存在大于5的数值

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