如何在电子表格中计算每行最后6个月度数据的平均值（不硬编码列）

核心思路 #

通过统计整行非空单元格数量，自动定位最后一个月度数据列，往前截取最多6列（避开末尾固定的5个统计列）计算平均值，新增月度列后无需手动修改公式。

Excel 公式方案 #

假设当前行A列为支出类别，后续列依次为月度数据和末尾5个统计列，在目标单元格输入以下公式（以第2行为例，下拉即可适配所有行）：

方案1：OFFSET 函数（直观易理解）

=AVERAGE(OFFSET(A2, 0, MAX(1, COUNTA($2:$2)-5-6+1), 1, MIN(6, COUNTA($2:$2)-5)))

• COUNTA($2:$2)：统计整行非空单元格总数
• COUNTA($2:$2)-5：减去末尾5个统计列，得到月度数据的总列数
• MAX(1, ...-6+1)：确定截取的起始列位置，避免月度数据不足6个时出现负数偏移
• MIN(6, ...)：最多取6列，若月度数据不足6个则取全部有效数据

方案2：INDEX+SEQUENCE 函数（无易失性，更高效）

=AVERAGE(INDEX(2:2, SEQUENCE(1, MIN(6, COUNTA(2:2)-5), COUNTA(2:2)-5-6+1, 1)))

通过SEQUENCE生成要截取的列位置，INDEX定位对应单元格，再计算平均值，避免OFFSET函数的易失性问题。

Google Sheets 公式方案 #

适配自动新增列场景，以第2行为例：

方案1：OFFSET 函数

=AVERAGE(OFFSET(A2, 0, COUNTA($2:$2)-5-6, 1, MIN(6, COUNTA($2:$2)-5)))

逻辑与Excel一致，在Google Sheets中表现稳定。

方案2：QUERY 函数（灵活过滤有效数据）

=AVERAGE(QUERY(TRANSPOSE(2:2), "select Col1 where Col1 is not null offset "&(COUNTA(2:2)-5-6)&" limit 6", 0))

通过TRANSPOSE将行转列，QUERY跳过前面的旧数据，精准提取最后6个有效月度值计算平均。

注意事项 #

1. 确保末尾5个统计列始终为非空状态（即使是公式生成的空值，建议用""填充），否则COUNTA会错误统计空白列，导致定位偏差。
2. 若数据起始列不是A列，只需调整公式中A2为当前行第一个数据单元格即可。

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