Excel每月新增数据时，如何计算最后6个大于0单元格的平均值？

问题分析 #

你遇到的核心问题是原公式使用了固定区域（H2:S2），无法自动覆盖新增的数据列，导致FILTER无法纳入最新的有效数据，进而让TAKE(-6)取到的不是预期的最后6个大于0的单元格。

修正后的公式 #

动态覆盖新增列的精准版本 #

这个公式会自动定位第2行从H列到最后一个非空单元格的区域，确保新增数据后能被实时纳入计算：

=AVERAGE(TAKE(FILTER(H2:INDEX(2:2, COUNTA(2:2)), H2:INDEX(2:2, COUNTA(2:2))>0), -6))

简化全列覆盖版本 #

如果第2行只有H列及以后是月度数据、无其他干扰内容，可以用更简洁的写法，直接覆盖到Excel的最后一列（XFD）：

=AVERAGE(TAKE(FILTER(H2:XFD2, H2:XFD2>0), -6))

处理有效数据不足6个的情况 #

如果存在大于0的单元格数量不足6个的场景，用IFERROR避免返回错误值：

=IFERROR(AVERAGE(TAKE(FILTER(H2:XFD2, H2:XFD2>0), -6)), "")

公式说明 #

• INDEX(2:2, COUNTA(2:2))：定位第2行最后一个非空单元格，实现数据区域的动态扩展。
• FILTER(..., ...>0)：筛选出区域内所有数值大于0的单元格。
• TAKE(..., -6)：提取筛选结果中最靠右的6个单元格。
• AVERAGE(...)：计算这6个单元格的平均值。

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