Excel筛选数据逐行计算滚动盈亏平均及滚动胜率的实现问题

结构化引用逐行计算逻辑说明 #

你提到的AVERAGE(IF([Score]>0,[Score]))这类全列结构化引用，默认对整个表的Score列所有行生效，不会自动限制到当前行以上的范围。不管在哪一行计算，这个公式默认都会判断全表所有Score值是否大于0，而非仅取到当前行的内容。要实现逐行滚动的范围限制，必须额外结合行号判断逻辑。

各列计算公式 #

以下公式适配Excel 2019及以上版本，365版本可直接回车输入，旧版版本需按Ctrl+Shift+Enter输入数组公式，所有结果支持筛选后动态更新。
假设你的结构化表名为Table1，表头行为第2行，数据从第3行开始，包含Type、Score两列基础字段。

1. Average Win（平均盈利，C列） #

仅当前行Score>0时返回截至当前行所有可见正得分的平均值，否则返回空值：

=IF([@Score]<=0,"",
SUMPRODUCT(([Score]>0)*(ROW([Score])<=ROW([@Score]))*SUBTOTAL(103,OFFSET([@Score],ROW([Score])-ROW(Table1[#Headers]),0))*[Score])
/
SUMPRODUCT(([Score]>0)*(ROW([Score])<=ROW([@Score]))*SUBTOTAL(103,OFFSET([@Score],ROW([Score])-ROW(Table1[#Headers]),0)))
)

逻辑说明：

• ROW([Score])<=ROW([@Score]) 限制计算范围为表首到当前行，实现滚动效果
• SUBTOTAL(103,OFFSET(...)) 逐个判断范围内的行是否可见，自动排除筛选隐藏的行
• 分子为符合条件的正得分总和，分母为符合条件的正得分数量，相除得平均值

2. Average Loss（平均亏损，D列） #

和平均盈利逻辑对称，仅当前行Score<0时返回截至当前行所有可见负得分的平均值，如需显示正数可在分子前加负号：

=IF([@Score]>=0,"",
SUMPRODUCT(([Score]<0)*(ROW([Score])<=ROW([@Score]))*SUBTOTAL(103,OFFSET([@Score],ROW([Score])-ROW(Table1[#Headers]),0))*[Score])
/
SUMPRODUCT(([Score]<0)*(ROW([Score])<=ROW([@Score]))*SUBTOTAL(103,OFFSET([@Score],ROW([Score])-ROW(Table1[#Headers]),0)))
)

3. 滚动胜率列 #

无需额外辅助列，直接计算截至当前行可见胜场（得分>0）占有效场次的比例，单元格格式设置为百分比即可显示0%、50%、66%这类结果：

=SUMPRODUCT(([Score]>0)*(ROW([Score])<=ROW([@Score]))*SUBTOTAL(103,OFFSET([@Score],ROW([Score])-ROW(Table1[#Headers]),0)))
/
SUMPRODUCT(([Score]<>0)*(ROW([Score])<=ROW([@Score]))*SUBTOTAL(103,OFFSET([@Score],ROW([Score])-ROW(Table1[#Headers]),0)))

如果得0的场次需要计入分母，把分母里的[Score]<>0改成1即可。

注意事项 #

• 如果你的表插入位置不是从首行开始，调整OFFSET里的行偏移参数，确保能正确定位到范围内的每一行
• 数据量过大时可提前增加IsVisible辅助列（公式=SUBTOTAL(103,[@Score])），把公式里的SUBTOTAL部分替换成[IsVisible]提升计算效率

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