Excel中如何根据变量自动更改公式的单元格引用范围

动态调整公式引用范围 #

你之前公式失效的核心原因是Excel无法直接将函数返回的文本字符串、半段拼接的引用识别为合法的单元格范围对象，WkSht!G:(VLOOKUP(...))属于语法错误，FILTER函数要求第一参数必须是完整的可识别引用/数组。

优先使用非易失性的INDEX方案实现动态列范围，性能远好于INDIRECT，适合数据量较大的表格：

=UNIQUE(FILTER(WkSht!G:INDEX(WkSht!A:XFD,0,VLOOKUP(F1,DH2:DI54,2,FALSE)),WkSht!A:A=B1))

公式逻辑说明：

• VLOOKUP最后必须加FALSE参数开启精确匹配，避免周数匹配错误
• INDEX(WkSht!A:XFD,0,列号)中0参数代表返回整列，最终返回VLOOKUP匹配到的目标整列引用，和前面的G列拼接为完整的G:目标列范围，可直接被FILTER识别
• 该方案要求你的查找表DI列存储的是目标列的数字序号（比如AE列对应列号是31，F1=25时DI列对应值填31即可）

如果你的查找表DI列已经存了"AE"这类列标文本，可以用INDIRECT方案实现：

=UNIQUE(FILTER(INDIRECT("WkSht!G:"&VLOOKUP(F1,DH2:DI54,2,FALSE)),WkSht!A:A=B1))

注意：INDIRECT是易失性函数，每次表格编辑都会触发全表重算，数据量大时会明显拖慢运行速度，优先选择INDEX方案。

FORECAST.ETS尾部无效0值过滤 #

不能直接过滤全序列所有0值，否则会误删业务场景下前期的有效0，正确逻辑是定位数值序列最后一个非0值的位置，仅截取序列起始到该位置的分段作为预测输入，自动剔除末尾连续的未发生周0值。

如果你的周度数据是按行存储（每行对应一周），假设周序号存在B列、业务数值存在C列，从第2行开始是有效数据，先通过以下公式定位最后一个有效周的行号：

LOOKUP(2,1/(C2:C1000<>0),ROW(C2:C1000))

将该逻辑嵌入FORECAST.ETS即可，示例（预测下一周数值）：

=FORECAST.ETS(
  MAX(B2:B1000)+1,
  C2:INDEX(C:C,LOOKUP(2,1/(C2:C1000<>0),ROW(C2:C1000))),
  B2:INDEX(B:B,LOOKUP(2,1/(B2:B1000<>0),ROW(B2:B1000)))
)

如果你的周度数据是按列存储（每列对应一周，从G列开始向后排列），只需要把上述行号查找逻辑改为列号查找，核心逻辑不变，即可自动剔除末尾的未发生周0值，完全保留前期的有效0。

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