excel - 在 Excel 中计算移动平均线

Question

我想计算一列最后(例如 20)个数字的移动平均值。问题是该列的某些单元格可能为空，应忽略它们。示例:

 A
175
154

188
145
155

167
201

最后三个的移动平均值为 (155+167+201)/3。我尝试使用平均值、偏移量、索引来实现此目的，但我根本不知道如何实现。我对宏有点熟悉，所以这样的解决方案可以很好地工作:=MovingAverage(A1;3)

感谢您提供任何提示或解决方案!

Answer 1

{=SUM(($A$1:A9)*(ROW($A$1:A9)>LARGE((ROW($A$1:A9))*(NOT(ISBLANK($A$1:A9))),3+1)))/3}

使用 control+shift+enter 输入此值以使其成为数组公式。这将找到最新的三个值。如果您想要更多或更少，请将公式中的两个“3”实例更改为您想要的任何值。

LARGE((ROW($A$1:A9))*(NOT(ISBLANK($A$1:A9))),3+1)

此部分返回所有具有值的单元格中第四高的行号，或者在示例中返回 5，因为第 6、8 和 9 行是具有值的第一到第三高的行。

(ROW($A$1:A9)>LARGE((ROW($A$1:A9))*(NOT(ISBLANK($A$1:A9))),3+1))

此部分根据行号是否大于第四大行数返回 9 个 TRUE 或 FALSE。

($A$1:A9)*(ROW($A$1:A9)>LARGE((ROW($A$1:A9))*(NOT(ISBLANK($A$1:A9))),3+1))

这会将 A1:A9 中的值乘以这 9 个 TRUE 或 FALSE。 TRUE 转换为 1，FALSE 转换为 0。这留下了一个像这样的 SUM 函数

=SUM({0;0;0;0;0;155;0;167;201})/3

由于所有大于 155 的值都不满足行数条件，因此将 get 乘以零。