Excel中如何对筛选后的表格按每组n个计算平均值

嗨，这个场景我太熟悉了！筛选后普通的索引类函数确实会“无视”筛选状态，把隐藏行的数据也算进去，给你两个靠谱的解决方案，按需选用：

方案一：适合Excel 365/2021（支持动态数组） #

如果你的Excel版本支持动态数组，用FILTER先提取筛选后的可见数据，再分组计算平均，逻辑清晰又好维护：

1. 先获取所有可见的数值（假设数据在A2:A100区域）：

   =FILTER(A2:A100, SUBTOTAL(103, OFFSET(A2, ROW(A2:A100)-ROW(A2), 0, 1)))
   

   这个公式里，SUBTOTAL(103,...)会判断每一行是否可见（103代表忽略筛选和手动隐藏的行），FILTER只保留可见行的数值。你可以把这个结果定义成一个名称（比如VisibleData），方便后续调用。

2. 按每组n个计算平均（假设n存在C1单元格）：

   =BYCOL(CHOOSECOLS(VisibleData, SEQUENCE(ROUNDUP(ROWS(VisibleData)/C1,0),C1,1)), LAMBDA(col, AVERAGE(col)))
   

   这里SEQUENCE生成每组的列索引，CHOOSECOLS把可见数据按每组n个拆分，最后BYCOL对每一组计算平均值。如果最后一组不足n个，会自动计算现有数据的平均。

方案二：兼容旧版Excel（无动态数组） #

如果用的是旧版Excel，就靠AGGREGATE+OFFSET组合来实现，虽然公式长一点，但实用性拉满：

假设要在E2单元格开始输出每组平均值，n在C1，数据区域A2:A100，输入以下公式后下拉：

=IFERROR(AVERAGE(OFFSET($A$2, AGGREGATE(15,5,ROW($A$2:$A$100)-ROW($A$2)+1, (ROW()-ROW($E$2))*$C$1+1)-1, 0, $C$1)), "")

拆解下逻辑：

• AGGREGATE(15,5,...)：用SMALL函数（15代表SMALL）提取忽略隐藏行的可见行相对位置，5表示忽略筛选/手动隐藏的行
• OFFSET：根据提取到的起始位置，取出连续n个可见单元格的区域
• AVERAGE计算平均，IFERROR处理最后一组不足n个时的报错，返回空值

小提醒 #

• 不管用哪种方法，SUBTOTAL的参数用103而不是3，这样既能忽略筛选隐藏的行，也能忽略手动隐藏的行，兼容性更好
• 如果你的数据是带表头的表格（Excel结构化表格），可以直接用表格列名代替单元格区域，比如Table1[数据列]，公式会更灵活

备注：内容来源于stack exchange，提问作者raphi-486