多条件下Excel列最大Quote#计算问题及公式优化需求

需求说明 #

实现以下逻辑的计算公式：

• 当Quote#列存在零或空白，且对应行的Line Total列大于0时，返回N/A
• 其余情况返回Quote#列的最大值

示例：

• 1-3列场景：最大Quote#为2
• 4-6列场景：最大Quote#为N/A

现有公式问题分析 #

• 公式1：=IF(AND(COUNTIF(B7:B15,0)+COUNTBLANK(B7:B15),COUNTIF(F7:F15,"> 0"))," N/A ",MAX(B7:B15))
  问题：未关联同一行的条件，只要区域内存在Quote#为0/空白，且存在任意Line Total>0，就返回N/A——哪怕空白行的Line Total也是0，仍会错误返回N/A，必须填满Quote#才能得到最大值。

• 公式2：=IF(AND((B7:B15=0),(F7:F15> 0))," N/A ",MAX(B7:B15))
  问题：仅处理了Quote#为0且Line Total>0的行，完全忽略了Quote#空白且Line Total>0的情况，本该返回N/A时错误返回最大值。

• 公式3：=MAXIFS(B7:B15,B7:B15, ">0",F7:F15, ">=0")
  问题：与公式2一致，未考虑Quote#空白且Line Total>0的行，不符合需求逻辑。

正确公式实现 #

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

=IF(SUM(--((B7:B15=0)+(B7:B15=""))*(F7:F15>0))>0,"N/A",MAX(IF(B7:B15>0,B7:B15)))

逻辑解释：

1. (B7:B15=0)+(B7:B15="")：标记Quote#为0或空白的行，符合条件返回1，否则返回0
2. *(F7:F15>0)：将上述标记与对应行的Line Total>0条件关联，只有同时满足两个条件的行才会返回1
3. SUM(--(...))>0：判断是否存在满足触发N/A的行，若存在则返回N/A
4. MAX(IF(B7:B15>0,B7:B15))：过滤掉Quote#为0或空白的行，计算剩余值的最大值

旧版Excel（非动态数组，需按Ctrl+Shift+Enter输入） #

=IF(SUM(IF((B7:B15=0)+(B7:B15=""),IF(F7:F15>0,1,0),0))>0,"N/A",MAX(IF(B7:B15>0,B7:B15)))

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