Excel 2016中SUMIFS返回0时的后续条件判断方案问询

当前思路点评 #

你用辅助列Custom合并物料与工序编号的思路没问题，能简化匹配逻辑，但SUMIFS和嵌套IF的实现方式不达标：

• SUMIFS只能精准匹配指定的物料+工序组合，没法自动向下查找有效工序的产量；
• 嵌套IF面对最高到100的工序范围会变得异常冗长，跳工序场景下还容易漏判，可靠性差。

Excel 2016可用的函数方案 #

结合你的需求（自动查找有效产量、无需手动操作/VBA/透视表），推荐以下两种简洁方案：

方案1：LOOKUP函数（推荐，操作简单） #

利用LOOKUP在数组中自动定位最后一个符合条件值的特性，公式如下：

=LOOKUP(2,1/(Orders2020[物料编号]=$A3)*(Orders2020[工序编号]<=$C$2)*(Orders2020[Aantal]>0),Orders2020[Aantal])

逻辑说明：

1. 三个条件筛选：匹配当前物料（$A3）、工序不超过指定值（$C$2）、产量大于0；
2. 1/(...)把符合条件的位置转为1，不符合的转为错误值；
3. LOOKUP(2,1/...,Aantal)会跳过错误值，定位到最后一个符合条件的产量（也就是该物料不超过指定工序的最大有效工序产量）。

方案2：INDEX+MATCH数组公式 #

如果需要从指定工序开始向下找第一个有效产量（而非最大工序的），可以用这个数组公式：

=INDEX(Orders2020[Aantal],MATCH(1,(Orders2020[物料编号]=$A3)*(Orders2020[工序编号]<=$C$2)*(Orders2020[Aantal]>0),0))

⚠️ 输入完成后必须按Ctrl+Shift+Enter确认（不是单独回车），公式会自动加上大括号表示生效。

逻辑说明：

1. 同样用三个条件生成1/0数组，1代表符合要求的记录；
2. MATCH(1,...,0)找到第一个符合条件的行号；
3. INDEX返回该行对应的产量值。

额外提示 #

• 确保Orders2020工作表的列标题和公式里的一致（物料编号、工序编号、Aantal）；
• 方案1无需数组确认，对运维人员更友好；
• 如果同物料同工序有多条记录，方案1返回最后一条的产量，方案2返回第一条，若需要求和所有有效产量，把INDEX换成SUM即可。

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