如何在Google Sheets中自动选取最后填充单元格往前12个单元格范围

针对你的需求——新增行后自动计算最近12个月的金额平均值，不用手动调整公式范围，以下是几种实用方案：

方案1：非易失函数组合（兼容所有Excel版本，推荐） #

如果你的金额数据从B5开始（对应示例里的2022/10那条），把公式放在B2的平均值单元格里：

=AVERAGE(INDEX(B:B,MAX(5, COUNTA(B$5:B)-7)):INDEX(B:B,COUNTA(B$5:B)+4))

如果需要排除0值，结合FILTER修改为：

=AVERAGE(FILTER(INDEX(B:B,MAX(5, COUNTA(B$5:B)-7)):INDEX(B:B,COUNTA(B$5:B)+4), INDEX(B:B,MAX(5, COUNTA(B$5:B)-7)):INDEX(B:B,COUNTA(B$5:B)+4)<>0))

公式说明： #

• COUNTA(B$5:B)：统计B5到列尾的非空数据条数
• COUNTA(B$5:B)+4：算出最后一条数据的行号（因为从第5行开始，5+数据条数-1=条数+4）
• MAX(5, COUNTA(B$5:B)-7)：保证起始行不会早于数据首行B5（如果数据不足12条，就从第一条开始计算）
• INDEX(B:B,行号)：精准定位单元格，形成自动更新的动态范围

方案2：OFFSET函数（易失函数，谨慎使用） #

同样针对B5开始的数据，公式如下：

=AVERAGE(OFFSET(B5,MAX(COUNTA(B$5:B)-12,0),0,MIN(COUNTA(B$5:B),12),1))

排除0值版本：

=AVERAGE(FILTER(OFFSET(B5,MAX(COUNTA(B$5:B)-12,0),0,MIN(COUNTA(B$5:B),12),1), OFFSET(B5,MAX(COUNTA(B$5:B)-12,0),0,MIN(COUNTA(B$5:B),12),1)<>0))

公式说明： #

• OFFSET(B5, 偏移行数, 0, 取行数, 1列)：以B5为基准，动态生成范围
• MAX(COUNTA(B$5:B)-12,0)：数据≥12条时，偏移条数-12行取最后12个；不足12条时从B5开始
• MIN(COUNTA(B$5:B),12)：最多取12行，不足则取全部实际数据
• 注意：OFFSET是易失函数，工作表任何变动都会触发重算，数据量大时可能卡顿

方案3：Excel 365/2021专属（最简洁） #

如果用的是支持动态数组的Excel版本，直接用TAKE函数一步到位：

=AVERAGE(TAKE(FILTER(B$5:B,B$5:B<>0),-12))

公式说明： #

• FILTER(B$5:B,B$5:B<>0)：先过滤掉B列的0值和空单元格
• TAKE(数组,-12)：提取过滤后数组的最后12个元素（不足12个则取全部）
• 新增行后公式会自动识别并更新范围，完全无需手动调整

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