在 Excel 中计算移动平均线 [英] Calculate Moving Average in Excel

问题描述

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

I want to calculate a moving average of the last, say 20, numbers of a column. A problem is that some of the cells of the column may be empty, they should be ignored. Example:

 A
175
154

188
145
155

167
201

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

A moving average of the last three would be (155+167+201)/3. I've tried to implement this using average, offset, index, but I simply don't know how. I'm a little bit familiar with macros, so such a solution would work fine: =MovingAverage(A1;3)

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

Thanks for any tips or solutions!

推荐答案

{=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"的两个实例更改为您想要的任何内容.

Enter this with control+shift+enter to make it an array formula. This will find the latest three values. If you want more or less, change the two instances of '3' in the formula to whatever you want.

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

这部分返回具有值的所有单元格的第 4 大行号，在您的示例中为 5，因为第 6、8 和 9 行是具有值的第 1 到第 3 行.

This part returns the 4th highest row number of all the cells that have a value, or 5 in your example because rows 6, 8, and 9 are the 1st through 3rd highest rows with a value.

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

这部分根据行数是否大于第 4 个最大的行数返回 9 个 TRUE 或 FALSE.

This part returns 9 TRUEs or FALSEs based on whether the row number is larger than the 4th largest.

($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 函数

This multiplies the values in A1:A9 by those 9 TRUEs or FALSEs. TRUEs are converted to 1 and FALSEs to zero. This leaves a SUM function like this

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

因为所有大于 155 的值都不满足行数标准，所以得到的结果乘以零.

Because all the values above 155 don't satisfy the row number criterion, the get multiplied by zero.

这篇关于在 Excel 中计算移动平均线的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！