今天继续介绍移动平均的问题,之前的内容参见:
分组移动平均 - 计算移动平均的函数(数组版):Excel函数式编程
注:今天介绍的方法目前只能在Excel 365的测试频道中才能使用。当前频道中需要过一段时间。
前面我们介绍了移动平均的自定义函数,
RollingAverage = LAMBDA(data, n,
MAKEARRAY(
ROWS(data),
1,
LAMBDA(r, c,
IF(
r < n,
'',
AVERAGE(CHOOSEROWS(data, SEQUENCE(n, , r - n + 1)))
)
)
)
);
这个函数可以帮助我们计算过去n期的移动平均值,
现在,我们还想统计过去n期的合计,当然可以用同样的方法创建一个移动求和的函数,只需要将上面自定义函数中的AVERAGE改为SUM即可。
如果还想统计过去n期的最大值,也可以用同样的方法创建一个移动求最大值的函数,只需要将上面的自定义函数中的AVERAGE改为MAX即可。
类似的还可以计算移动最小值,移动中位数,标准差,等等等等。
问题在于,这样的话,我们就需要创建很多自定义函数。
而且,这些自定义函数逻辑都是一样的。
感觉很浪费。
目前,Excel中已经推出了用函数名称作为参数的功能,我们可以利用这个功能,创建一个万能的移动聚合计算的函数。
Rolling = LAMBDA(data, n,
LAMBDA(function, MAKEARRAY(
ROWS(data),
1,
LAMBDA(r, c,
IF(
r < n,
'',
function(CHOOSEROWS(data, SEQUENCE(n, , r - n + 1)))
)
)
))
);
这个函数可以看作是一个嵌套,从最外层(第1行)看,形式如下:
LAMBDA(data, n,移动计算函数)
这个自定义函数的返回值是一个函数:“移动计算函数”,
其定义是另一个LAMBDA定义的自定义函数:
LAMBDA(function, MAKEARRAY(
ROWS(data),
1,
LAMBDA(r, c,
IF(
r < n,
'',
function(CHOOSEROWS(data, SEQUENCE(n, , r - n + 1)))
)
)
))
)
;
它以“function”为参数,对过去n期的数据进行计算(第8行)。
如果像普通的自定义函数一样调用的话,
= Rolling(B2:B32, 3)
将返回一个错误,
这是因为这次调用返回的是内层的自定义函数(即上面说的“移动计算函数”)。
这个函数是需要一个函数名作为参数的,
=Rolling(B2:B32, 3)(AVERAGE)
结果就是移动平均,
可以计算移动求和,
移动求最大值,
有些聚合计算的需求不能使用这种方式满足。比如计算过去n期中的排名第二的数。显然需要使用LARGE函数,但是LARGE函数需要第二个参数,没有办法直接作为这里的参数。
此时,可以使用自定义函数作为参数,
=Rolling(B2:B32,3)(LAMBDA(x, LARGE(x, 2)))
联系客服