简化Excel SUMIFS嵌套IF函数及工作簿性能优化咨询

原函数与问题说明 #

原函数用于计算指定PO下对应物料的已发货数量，逻辑是取「符合条件的总发货量减去当前行以上同物料同PO的累计值」与「订单数量」的较小值，避免超出订单量：

IF(SUMIFS('SAP TRACKER'!N:N,'SAP TRACKER'!K:K,[@FPC],'SAP TRACKER'!B:B,"Shipped",'SAP TRACKER'!J:J,[@PO])-IF(ROW(F40)=3,0,SUMIFS($N$3:N39,$F$3:F39,F40,$D$3:D39,D40))>[@[QTY (IT)]],[@[QTY (IT)]],SUMIFS('SAP TRACKER'!N:N,'SAP TRACKER'!K:K,[@FPC],'SAP TRACKER'!B:B,"Shipped",'SAP TRACKER'!J:J,[@PO])-IF(ROW(F40)=3,0,SUMIFS($N$3:N39,$F$3:F39,F40,$D$3:D39,D40)))

但该函数重复执行了两次完全相同的复杂计算，大量使用时会显著增加工作簿计算负载，导致响应缓慢。

简化方案（适配Excel 365/2021及以上版本） #

使用LET函数将重复计算的结果赋值为变量，避免重复运算，大幅降低计算量：

=LET(
    TotalShipped, SUMIFS('SAP TRACKER'!N:N,'SAP TRACKER'!K:K,[@FPC],'SAP TRACKER'!B:B,"Shipped",'SAP TRACKER'!J:J,[@PO]),
    CumulativePrev, IF(ROW()=3,0,SUMIFS($N$3:INDIRECT("N"&ROW()-1),$F$3:INDIRECT("F"&ROW()-1),[@FPC],$D$3:INDIRECT("D"&ROW()-1),[@PO])),
    CurrentCalc, TotalShipped - CumulativePrev,
    MIN(CurrentCalc, [@[QTY (IT)]])
)

简化逻辑说明 #

• 用TotalShipped存储「对应物料、PO且已发货的总数量」，仅计算一次
• 用CumulativePrev存储「当前行以上同物料同PO的累计已计算值」，替换原固定行号为动态行引用，适配整列下拉
• 用CurrentCalc存储核心计算结果，最后直接用MIN替代原IF判断，逻辑更清晰

兼容旧版Excel方案（无LET函数支持） #

1. 新增辅助列（比如列O），在O3单元格输入以下公式并下拉：

=SUMIFS('SAP TRACKER'!N:N,'SAP TRACKER'!K:K,[@FPC],'SAP TRACKER'!B:B,"Shipped",'SAP TRACKER'!J:J,[@PO])-IF(ROW()=3,0,SUMIFS($O$3:INDIRECT("O"&ROW()-1),$F$3:INDIRECT("F"&ROW()-1),[@FPC],$D$3:INDIRECT("D"&ROW()-1),[@PO]))

2. 原计算列只需引用辅助列并取最小值：

=MIN(O3, [@[QTY (IT)]])

这种方式将重复计算集中在辅助列，避免每一行都重复执行两次SUMIFS，同样能降低计算负载。

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