☰

如何创建采用滚动平均值的溢出数组

tag-icon • tag-icon microsoft-excel worksheet-function array
如何创建采用滚动平均值的溢出数组

反转日期顺序是可行的，但我更想知道如何以这种特定方式进行操作。我试图取滚动平均值，理想情况下，使用偏移量更改单元格的深度会很好。因此，它不会显示 20，而是显示 T1 或 SMALength。

=STOCKHISTORY("AAPL",6/18/1981,7/13/2022,0,1,0,1,2,3,4,5)我正在A1、Sheet1 上加载的Close 上测试它

目前的公式是：

=IF(ROW(OFFSET($B$2,,,COUNTA(B:B)))-1>20,AGGREGATE(1,6,OFFSET(B2,,,-20)),NA())


IF ( X , Y, NA())

X 在创建溢出数组时检查数字行深度是否有 20。

Y 应该取当前行高度 -20 的平均值，并且在拖动时有效，但不能作为动态溢出数组的一部分。

一个非常不优雅的方法是使用

=AVERAGE(OFFSET(INDEX($A$1#,,2),,)+OFFSET(INDEX($A$1#,,2),1,)+OFFSET(INDEX($A$1#,,2),2,)+OFFSET(INDEX($A$1#,,2),3,)+etc....+OFFSET(INDEX($A$1#,,2),20,))

但这不允许我动态改变 SMA 的长度。

这个已经完成了一部分，但如果为 True，则值不会更新，只返回 B11:B31

=IF(ROW(OFFSET($B$2,,,COUNT($B$11:$B$100000)))>20,AVERAGE($B11:$B31),NA())

答案1

根据您提供的数据，信息存储在范围内A1:F10356。您想要实现的是逐行计算过去 20 天的移动平均值。实现此目的的一种方法是使用以下函数BYROW()：

=BYROW(array, [function])

该函数本身几乎是不言自明的。使用 lambda 语句中指定的函数指定要应用该函数的数组；在您的例子中，是过去 20 天的平均值。

由于前 20 行应该为空，我添加了一条COUNTIF语句来说明如果数据包含文本，则您不需要平均值，在一种情况下（第 20 行）将导致平均值仅为 19 天；其次，如果出现错误，您需要一个空单元格#REF!，由于 -20 ，前 19 行会发生错误OFFSET。在单元格中使用以下公式G2：

=BYROW(B2:B10356,
LAMBDA(row,IFERROR(IF(COUNTIF(OFFSET(row,,,-20),"*")>0,"",AVERAGE(OFFSET(row,,,-20))),"")))

但是，这种规范的最大缺点是，一旦你的数据发生变化，你也必须相应地调整数组，即B2:B10356。

通过额外添加函数可以实现更加动态的规范INDEX，该函数返回从第 2 行到最后一行的相关值范围：

=BYROW(INDEX($B:$B,2,0):INDEX($B:$B,COUNTA($B:$B),0),
LAMBDA(row,IFERROR(IF(COUNTIF(OFFSET(row,,,-20),"*")>0,"",AVERAGE(OFFSET(row,,,-20))),"")))

相关内容