求助：Excel中计算库存可覆盖最晚月份的公式

场景说明 #

现有数据结构：

• 每行对应一个物料（如Part1、Part2）
• 列包含：FG（成品库存）、WIP（在制品库存）、RM（原材料库存），以及3月至6月的月度需求
• 需在「Coverage until」列计算：总库存（FG+WIP+RM）能覆盖的最晚需求月份

公式方案 #

方案1：Excel 365/2021（支持动态数组）

直接使用SCAN函数生成累计需求，配合INDEX+MATCH定位最晚覆盖月份：

=INDEX($E$1:$H$1,MATCH(1,--(SCAN(0,$E2:$H2,LAMBDA(x,y,x+y))<=SUM($B2:$D2)),1))

公式拆解：

1. SUM($B2:$D2)：计算当前物料的总库存（FG+WIP+RM）
2. SCAN(0,$E2:$H2,LAMBDA(x,y,x+y))：生成从3月开始的累计需求数组（如[10,25,45,70]）
3. --(...)<=SUM(...)：将「累计需求≤总库存」的判断结果转为1/0数组（符合条件为1，否则为0）
4. MATCH(1,...,1)：定位最后一个符合条件的累计需求的列位置
5. INDEX($E$1:$H$1,...)：根据位置提取对应的月份名称（如May、March）

方案2：旧版Excel（不支持动态数组）

使用数组公式（输入后需按Ctrl+Shift+Enter确认）：

=INDEX($E$1:$H$1,MAX(IF(SUBTOTAL(9,OFFSET($E2,0,0,1,COLUMN($E2:$H2)-COLUMN($E2)+1))<=SUM($B2:$D2),COLUMN($E2:$H2)-COLUMN($E2)+1)))

公式拆解：

1. SUBTOTAL(9,OFFSET(...))：生成每个月份的累计需求值（通过偏移量扩展求和范围）
2. IF(...)：筛选出累计需求≤总库存的列位置
3. MAX(...)：取最大的列位置（即最晚覆盖的月份）
4. INDEX(...)：提取对应月份名称

示例验证 #

• Part1总库存45，3-5月累计需求为45（刚好等于总库存），公式返回May
• Part2总库存20，仅3月需求≤20，公式返回March

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