Excel无辅助表统计移动平均离群点的数组公式实现方法

架构老林

2026-10-7

解决方案

方案1：Excel 365/2021及以上版本单公式实现（无需任何辅助列/表）

直接在每行的统计结果单元格输入单条公式即可得到离群点总数，无需创建任何辅助表/辅助列，公式量从原来的190万条直接降到1万条，文件体积和运算速度都会大幅优化。
假设你的val1在B列、val200在GU列、数据从第2行开始，第2行的统计结果放在GV2单元格，公式如下：

=SUM(BYCOL(SEQUENCE(,190,11),LAMBDA(k,
  LET(
    w,INDEX(B2:GU2,SEQUENCE(,10,k-10)),
    v,INDEX(B2:GU2,k),
    IF(OR(v<AVERAGE(w)-2*STDEV.S(w),v>AVERAGE(w)+2*STDEV.S(w)),1,0)
  )
)))

适配说明：如果需要和你原有逻辑完全对齐（兼容文本值视为0的规则），可将公式中的AVERAGE替换为AVERAGEA，STDEV.S替换为STDEVA即可。
输入完成后下拉填充到所有行即可，所有计算都在单个单元格内完成，完全不需要额外辅助表。

方案2：低版本Excel兼容方案（仅需数组公式，无辅助表）

如果你使用的是2019及更早版本的Excel，没有动态数组函数，可以使用数组公式实现，同样不需要辅助表，还是以上述单元格位置为例，公式如下：

=SUMPRODUCT(--(
  OFFSET(B2,0,10,1,190)<(SUBTOTAL(1,OFFSET(B2,0,COLUMN(OFFSET(B2,0,0,1,190))-1,1,10))-2*SUBTOTAL(7,OFFSET(B2,0,COLUMN(OFFSET(B2,0,0,1,190))-1,1,10)))
  +
  OFFSET(B2,0,10,1,190)>(SUBTOTAL(1,OFFSET(B2,0,COLUMN(OFFSET(B2,0,0,1,190))-1,1,10))+2*SUBTOTAL(7,OFFSET(B2,0,COLUMN(OFFSET(B2,0,0,1,190))-1,1,10)))
))

输入完成后按Ctrl+Shift+Enter确认为数组公式，再下拉填充即可。