如何实现插入新日期时自动计算4/8/13周平均值的动态公式？

需要实现：插入新日期列（例如2024年2月10日）后，公式能自动纳入新日期的数据，计算对应周期（4周、8周、13周）的平均值。当前使用的公式：

=ROUND(AVERAGE(OFFSET(D4, 0, COUNTA(D3:P3)-4, 1, 4))*4.33, 0)

但插入新日期列后，公式无法自动包含新数据，每月都会添加新日期，需要公式动态适配。

解决方案 #

方法1：用结构化表格（推荐，最省心） #

1. 选中包含日期行和数据行的整个数据区域，按Ctrl+T转成结构化表格（勾选「我的表格有标题」）。
2. 假设表格名称为Table1，计算4周平均值的公式：

=ROUND(AVERAGE(TAKE(Table1[@],-4))*4.33,0)

• [@]代表当前行的所有数据列
• TAKE(..., -4)会自动抓取当前行最后4列的数据，插入新列后表格范围自动扩展，公式会直接包含新数据

3. 对应8周、13周的公式，只需把-4换成-8、-13即可：

# 8周平均值
=ROUND(AVERAGE(TAKE(Table1[@],-8))*4.33,0)

# 13周平均值
=ROUND(AVERAGE(TAKE(Table1[@],-13))*4.33,0)

方法2：动态范围公式（不用表格的情况） #

如果不想转表格，用INDEX替代易失性的OFFSET，同时让范围自动覆盖新增列：

# 4周平均值
=ROUND(AVERAGE(INDEX(D4:XFD4,1,COUNTA(D3:XFD3)-3):INDEX(D4:XFD4,1,COUNTA(D3:XFD3)))*4.33,0)

• D3:XFD3覆盖了行3所有可能的日期列（XFD是Excel最大列），COUNTA会自动统计已有的日期数量
• 要取8周就把-3改成-7，取13周改成-12（因为n列的起始位置是总列数-(n-1)）

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