求助：Excel无法创建动态滚动n值平均值公式的问题

问题分析 #

你当前的公式逻辑存在问题：用COUNT($H$6:H7)判断是否等于$E$2，只会在累计数据量刚好等于n时计算一次，后续行的累计数据量会超过n，自然无法生成结果。需要改为动态取当前行向上n行的连续区域来计算滚动平均值之比。

正确公式实现 #

适用于Excel 365/2021（支持动态数组） #

在J5单元格输入以下公式，下拉填充即可：

=IF(ROW()-ROW($H$5)+1<$E$2,"",AVERAGE(OFFSET(H5,0,0,-$E$2,1))/AVERAGE(OFFSET(I5,0,0,-$E$2,1)))

逻辑说明：

• ROW()-ROW($H$5)+1：计算当前行相对于H5的偏移行数，判断是否有足够的n行数据（不足时显示空）
• OFFSET(H5,0,0,-$E$2,1)：以当前H列单元格为起点，向上取n行的连续区域
• 分别计算H列和I列该区域的平均值，再做除法得到比值

适用于旧版Excel（不支持动态数组） #

同样在J5单元格输入公式后下拉：

=IF(ROW()<$E$2+ROW($H$5)-1,"",AVERAGE(INDEX(H:H,ROW()-$E$2+1):H5)/AVERAGE(INDEX(I:I,ROW()-$E$2+1):I5))

逻辑说明：

• ROW()<$E$2+ROW($H$5)-1：判断当前行是否有足够的n行历史数据（比如H5是起始行，n=2时，J6才会开始计算）
• INDEX(H:H,ROW()-$E$2+1):H5：定位到当前行向上n行的起始单元格，与当前行组成连续区域
• 计算两列区域的平均值之比

示例验证（以n=2为例） #

• J6单元格：计算H5:H6的平均值 ÷ I5:I6的平均值
• J7单元格：计算H6:H7的平均值 ÷ I6:I7的平均值
• 以此类推，实现滚动计算

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