如何编写Excel公式计算库存基于月度预测的可支撑时长？

核心逻辑 #

计算库存可支撑时长的关键是：先统计库存能完全覆盖的整月数，再加上剩余库存占下一个月销售预测的比例，最终得到总时长（如示例中的4.5个月）。以下提供两种简化公式，分别适配不同Excel版本。

适配Excel 365/2021（动态数组版本） #

假设：

• 库存数量存于单元格 A1
• 月度销售预测数据存于区域 B1:B12（可根据实际月份数调整）

直接使用以下公式，无需新增辅助列：

=LET(
    累计销量, SCAN(0, B1:B12, LAMBDA(累计, 当月, 累计+当月)),
    整月数, SUM(--(累计销量 <= A1)),
    剩余库存, A1 - INDEX(累计销量, 整月数),
    IF(整月数 = COUNTA(B1:B12), 
       整月数 + 剩余库存/INDEX(B1:B12, 整月数), 
       整月数 + 剩余库存/INDEX(B1:B12, 整月数+1)
    )
)

公式说明：

1. LET 函数用于定义临时变量，简化公式结构
2. SCAN 自动计算每个月的累计销售预测值
3. SUM(--(累计销量 <= A1)) 统计库存能完全覆盖的整月数量
4. 最后通过判断整月数是否等于总预测月数，计算剩余库存对应的时长：
   • 若库存覆盖所有预测月份，剩余库存按最后一个月的预测比例计算时长
   • 若未覆盖所有月份，剩余库存按下一个月的预测比例计算时长

适配旧版Excel（非动态数组版本） #

如果使用Excel 2019及更早版本，使用以下数组公式（输入完成后按 Ctrl+Shift+Enter 确认）：

=SUM(--(SUBTOTAL(9,OFFSET(B1,0,0,ROW(B1:B12)-ROW(B1)+1))<=A1)) + (A1-SUMIF(SUBTOTAL(9,OFFSET(B1,0,0,ROW(B1:B12)-ROW(B1)+1)),"<="&A1,SUBTOTAL(9,OFFSET(B1,0,0,ROW(B1:B12)-ROW(B1)+1))))/INDEX(B1:B12,SUM(--(SUBTOTAL(9,OFFSET(B1,0,0,ROW(B1:B12)-ROW(B1)+1))<=A1))+1)

公式说明：

• SUBTOTAL+OFFSET 生成每个月的累计销售预测数组
• SUM(--(...)) 统计库存能覆盖的整月数
• 后半部分计算剩余库存占下一个月预测的比例，最终相加得到总时长

注意事项 #

• 确保月度预测区域无空值，若有空值可将 COUNTA(B1:B12) 替换为 MAX(IF(B1:B12<>"",ROW(B1:B12)-ROW(B1)+1,0))（旧版需按数组键确认）
• 若库存为0，公式返回0，可根据需求调整为特殊值（如 IF(A1=0,0,...)）
• 若库存足够支撑所有预测月份，公式会继续计算剩余库存对应的额外时长，若不需要此逻辑，可将 IF 分支改为直接返回 整月数

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