如何用Arrayformula计算固定大小动态偏移范围的14行滑动窗口求和

问题原因 #

INDIRECT、OFFSET本身不属于支持数组展开的函数，直接套在外层ARRAYFORMULA中时只会解析第一个输入参数的结果，无法对整个数组的每一个元素单独处理，因此只会返回第一行的计算值。

解法1：BYROW + LAMBDA（易写易维护） #

直接在N110单元格输入以下公式，无需手动下拉，会自动向下溢出所有行的计算结果：

=BYROW(ROW(I110:I), LAMBDA(cur_row, IF(INDEX(I:I, cur_row)="",, SUM(INDIRECT("I"&cur_row-14&":I"&cur_row)))))

说明：

• BYROW会逐行遍历传入的行号数组，对每一行单独执行LAMBDA内的逻辑，规避了INDIRECT不支持数组的问题
• 内置IF判断，对应行I列为空时返回空值，避免无数据的空行返回无效的0值
• 若要调整滑动窗口长度，只需要修改cur_row-14里的数字即可

解法2：ARRAYFORMULA + MMULT（高性能，适合大数据量） #

如果表格行数过万，逐行调用INDIRECT会有明显性能损耗，可以用矩阵运算一次性算出所有窗口的求和结果，直接在N110输入：

=ARRAYFORMULA(IF(I110:I="",, MMULT(N(ROW(I96:I)>=TRANSPOSE(ROW(I96:I)))*N(ROW(I96:I)<=TRANSPOSE(ROW(I96:I))+14), I96:I)))

说明：

• 通过逻辑矩阵标记每个滑动窗口需要纳入计算的行，一次性完成所有求和运算，计算效率远高于逐行处理方案
• 无需调用任何动态范围解析函数，稳定性更高

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