寻求新版Excel数组函数简化库存前置覆盖周数计算的更优方法

核心需求 #

已知当前库存（单元格A1）和未来每周预估销量（区域A2:AX），计算库存可覆盖的完整周数加剩余库存对应周的比例——比如库存100、销量为25/30/10/40时，结果为3.875周。

最优公式实现（基于LET+SCAN+XLOOKUP） #

利用新版Excel的动态数组函数，通过LET封装中间变量，大幅简化逻辑并提升可读性：

=LET(
    累计销量, SCAN(0, A2:AX, LAMBDA(累计, 当前周销量, 累计 + 当前周销量)),
    首个超量周, XLOOKUP(TRUE, 累计销量 > A1, SEQUENCE(ROWS(A2:AX)),, 1),
    完整覆盖周数, 首个超量周 - 1,
    剩余库存, A1 - INDEX(累计销量, 完整覆盖周数),
    下一周销量, INDEX(A2:AX, 完整覆盖周数 + 1),
    完整覆盖周数 + 剩余库存 / 下一周销量
)

公式拆解 #

• SCAN函数：逐周计算累计销量，生成数组如[25, 55, 65, 105, ...]
• XLOOKUP函数：定位第一个累计销量超过库存的周数，通过参数1返回最后一个不满足条件位置的下一个值
• 剩余库存 = 当前库存 - 完整覆盖周数的累计销量，再除以对应下一周的销量得到比例部分
• 最终结果为完整周数加比例值

示例验证 #

当A1=100，A2:A5={25,30,10,40}时：

1. 累计销量数组为[25,55,65,105]
2. 首个超量周为4，完整覆盖周数=3
3. 剩余库存=100-65=35，下一周销量=40
4. 计算得3 + 35/40 = 3.875，与预期一致

极简版（可读性稍弱） #

若追求公式长度最短，可合并部分逻辑：

=XLOOKUP(TRUE,SCAN(0,A2:AX,LAMBDA(x,y,x+y))>A1,SEQUENCE(ROWS(A2:AX)),,1)-1+(A1-SUM(A2:INDEX(A2:AX,XLOOKUP(TRUE,SCAN(0,A2:AX,LAMBDA(x,y,x+y))>A1,SEQUENCE(ROWS(A2:AX)),,1)-1)))/INDEX(A2:AX,XLOOKUP(TRUE,SCAN(0,A2:AX,LAMBDA(x,y,x+y))>A1,SEQUENCE(ROWS(A2:AX)),,1))

但推荐优先使用LET版本，便于后续维护和修改。

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