如何在Excel中基于月度变动预测计算库存在手月数(MOH)

核心逻辑 #

放弃平均需求法，改用逐月累减库存、统计可覆盖的完整月份+剩余库存占下月需求的比例，这是最贴合实际业务场景的计算方式，能完美适配季节性、波动型或递增/递减的需求曲线。

Excel公式实现（以H4单元格为例，对应G3的期初库存计算MOH） #

假设预测需求从E4开始，后续月份为E5、E6...（示例按7个月需求范围E4:E10，可按需扩展），H4的公式如下：

通用自适应公式 #

=IF(G3=0,0,
 SUMPRODUCT(--(SUBTOTAL(9,OFFSET(E4,0,0,ROW(E4:E10)-ROW(E4)+1))<=G3))
 +MAX(0,(G3-SUMPRODUCT(--(SUBTOTAL(9,OFFSET(E4,0,0,ROW(E4:E10)-ROW(E4)+1))<=G3)*SUBTOTAL(9,OFFSET(E4,0,0,ROW(E4:E10)-ROW(E4)+1))))
 /INDEX(E4:E10,SUMPRODUCT(--(SUBTOTAL(9,OFFSET(E4,0,0,ROW(E4:E10)-ROW(E4)+1))<=G3))+1))

注：将公式中的E4:E10替换为实际的预测需求单元格范围即可。

分步拆解 #

1. 统计完整覆盖月份数：通过OFFSET生成从E4开始的1个月、2个月...直到全量需求的累加区域，用SUBTOTAL计算每个区域的需求总和，对比是否小于等于当前库存G3，统计符合条件的次数，即完整覆盖的月份数。
2. 计算剩余库存比例：先算出完整覆盖月份的总需求，用库存减去该总和得到剩余库存，再取下一个月的需求值，用剩余库存除以该需求得到比例。
3. 边界处理：如果库存为0，直接返回0，避免除以0错误。

验证用户示例 #

以用户提供的4个无供应产品为例（库存均为100）：

• 产品A：需求[20,20,20,20,20,20,20]
  完整覆盖5个月（总需求100），剩余库存0，MOH=5.00，匹配示例结果
• 产品B：需求[10,10,20,20,50,10,10]
  前4个月总需求60，剩余库存40，第五个月需求50，比例40/50=0.8，MOH=4.80，匹配示例结果
• 产品C：需求[0,0,50,0,0,50,0]
  前6个月总需求100，完整覆盖6个月，剩余库存0，MOH=6.00，匹配示例结果
• 产品D：需求[0,5,10,15,20,25,30]
  前6个月总需求75，剩余库存25，第七个月需求30，比例25/30≈0.83，MOH=6.83，匹配示例结果

固定需求长度简化公式 #

如果预测需求的月份数固定（比如12个月），可使用更简洁的公式：

=IF(G3=0,0,
 MATCH(G3,SUBTOTAL(9,OFFSET(E4,0,0,ROW(INDIRECT("1:"&ROWS(E4:E15))))),1)
 +(G3-INDEX(SUBTOTAL(9,OFFSET(E4,0,0,ROW(INDIRECT("1:"&ROWS(E4:E15))))),MATCH(G3,SUBTOTAL(9,OFFSET(E4,0,0,ROW(INDIRECT("1:"&ROWS(E4:E15))))),1))
 /INDEX(E4:E15,MATCH(G3,SUBTOTAL(9,OFFSET(E4,0,0,ROW(INDIRECT("1:"&ROWS(E4:E15))))),1)+1))

注：将E4:E15替换为固定长度的需求范围即可。

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