求助：可自动计算4周移动平均且忽略零值的Excel公式

需求回顾 #

• 每周向结构化表格的指定列添加数据
• 在列标题上方的单元格，自动计算最近4个非零、非空周数的移动平均
• 原公式无法实现需求：

  IFERROR(AVERAGEIF(OFFSET(Table32[[#Headers],[LAMINATED PANEL (SQFT/HR)]],COUNT(Table32[LAMINATED PANEL (SQFT/HR)]),0,-4),"<>0"),0)
  

原公式问题分析 #

1. COUNT(Table32[LAMINATED PANEL (SQFT/HR)])会统计零值行，导致OFFSET定位的最后4行可能包含无效数据
2. 即使通过AVERAGEIF排除零值，也只能计算最后4行中的有效值平均，无法跳过无效行去取最近的4个有效数据

解决方案 #

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

使用FILTER+TAKE组合精准筛选最近4个有效值：

=IFERROR(AVERAGE(TAKE(FILTER(Table32[LAMINATED PANEL (SQFT/HR)],Table32[LAMINATED PANEL (SQFT/HR)]<>0),-4)),0)

• FILTER：筛选出列中所有非零数值（自动忽略空值）
• TAKE(..., -4)：提取筛选结果的最后4个值（即最近的4个有效周数）
• AVERAGE：计算这4个值的平均值
• IFERROR：当有效数据不足4个时，返回0

方案2：旧版Excel（无动态数组） #

用INDEX+SMALL组合定位有效数据范围：

=IFERROR(AVERAGE(INDEX(Table32[LAMINATED PANEL (SQFT/HR)],SMALL(IF(Table32[LAMINATED PANEL (SQFT/HR)]<>0,ROW(Table32[LAMINATED PANEL (SQFT/HR)])-ROW(Table32[[#Headers],[LAMINATED PANEL (SQFT/HR)]]),""),MAX(1,COUNTIF(Table32[LAMINATED PANEL (SQFT/HR)],"<>0")-3))):INDEX(Table32[LAMINATED PANEL (SQFT/HR)],SUMPRODUCT(MAX((Table32[LAMINATED PANEL (SQFT/HR)]<>0)*ROW(Table32[LAMINATED PANEL (SQFT/HR)])))-ROW(Table32[[#Headers],[LAMINATED PANEL (SQFT/HR)]])))),0)

注：输入后需按Ctrl+Shift+Enter作为数组公式执行（旧版Excel要求）

• 先通过IF获取所有非零值的行偏移量
• SMALL定位到第N-3个有效值的位置（N为总有效数据量），确保取最近4个
• INDEX组合定位有效数据范围后计算平均
• IFERROR处理数据不足的情况

注意事项 #

• 确保使用的是Excel结构化表格（Table），新增行时公式会自动识别扩展范围
• 条件<>0已同时排除空值（空值与0不相等），无需额外添加空值判断

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