如何用数组公式计算每年近10年数据的平均值（无需辅助列）

假设你的年份数据在A2:A21，对应数值在B2:B21（按年份降序排列，2023在最上方），可以用以下两种方式实现：

方式1：下拉填充公式 #

在C2单元格输入以下公式，然后下拉填充至所有年份行：

=AVERAGE(INDEX($B$2:$B$21,ROW()-ROW($B$2)+1):INDEX($B$2:$B$21,MIN(ROW()-ROW($B$2)+10,COUNTA($B$2:$B$21))))

• 公式通过INDEX定位当前年份对应的起始行，以及最多往后10行的结束行（如果剩余数据不足10行，则取到最后一行数据）
• AVERAGE自动忽略空值，直接计算有效数据的平均值

方式2：Excel 365动态数组公式（无需下拉） #

如果使用Excel 365或2021版本，可在C2单元格输入以下公式，自动溢出所有年份的计算结果：

=MAP(SEQUENCE(COUNTA($A$2:$A$21)),LAMBDA(x,AVERAGE(TAKE(DROP($B$2:$B$21,x-1),10))))

• SEQUENCE生成对应数据行数的序列，MAP遍历每个序列值
• DROP从第x行开始截取数值区域，TAKE取前10个数据（不足10个则取全部剩余）
• 最终通过AVERAGE计算平均值

说明 #

不需要反转数据顺序，因为你的数据是按年份降序排列的，当前年份对应的过去10年数据正好是当前行及下方的连续行，直接截取即可。

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