EXCEL:if函数，满足条件1，返回一个值，满足条件2，返回另一值。若返回值小于零，则为0，请高人指点

Question

=IF(AJ3=0,0,IF(AM3<=100%,AJ3,IF(D3=66,R3-(U3-AJ3),IF(D3=11,R3-(U3+U4-AJ3-AJ4),IF(D3=22,R3-(U3+U4+U5-AJ3-AJ4-AJ5),IF(D3=33,R3-(U3+U4+U5+U6-AJ3-AJ4-AJ5-AJ6),IF(D3=44,R3-(U3+U4+U5+U6+U7-AJ3-AJ4-AJ5-AJ6-AJ7),IF(D3=55,0,IF(D3=77,R3-(U3+U2-AJ3-AJ2))))))))))
这是我初步设好的函数，如何再添加条件，使得返回值若小于零，则为零。请各位高手帮助、指教！

Answer 1

如果要实现所在单元格（A1）的数值小于0时，取0值，也就相当于在A1与0之间取最大值，因此可以用Max函数来实现，即：=Max(A1,0)
用你的原始公式替换上面的A1就可以了。

问题是，你所给公式已经超出了IF函数所能容纳的最大层数，因此必须优化后才能运行。
最外面两层是针对不同单元格（AJ3、AM3）进行判断的，不好优化。但内层的几个判断，都是根据D3的值进行判断，通常用Lookup、HLookup或VLookup进行判断。这里以VLookup函数为例列出内层几个IF函数的近似公式：
=VLOOKUP(D3,{11,R3-(U3+U4-AJ3-AJ4);22,R3-(U3+U4+U5-AJ3-AJ4-AJ5);33,R3-(U3+U4+U5+U6-AJ3-AJ4-AJ5-AJ6);44,R3-(U3+U4+U5+U6+U7-AJ3-AJ4-AJ5-AJ6-AJ7);55,0;66,R3-(U3-AJ3);77,R3-(U3+U2-AJ3-AJ2)},2,0)

注：把公式最后面的“,0”去掉也是可以的。
这里只所以说是近似，因为最终效果还是有所不同的。

为了描述方便，把你所给出的内层公式称为公式1，上面的公式称为公式2，不包括“,0”的公式称为公式3。
如果D3的值只能取11、22、33、44、55、66、77中的一个值，不会有其他例外，上面的三个公式效果都是相同的。
但如果D3的值比较自由，比如D3=30，情况就会有明显的不同：
公式1返回FALSE（IF函数实际应有3个参数，在第3参数缺省情况下，在不满足所给定条件时，就会返回FALSE值）。
公式2返回错误值#N/A，这个公式为精确查找，用查找项的顺序无关，这个公式允许不按升序排列，比如就按你原公式中提供的66、11、22、33、44、55、77的顺序，效果也是一样的。
公式3返回小于D3值并与之最近的一个值所对应的返回值，即22对应的数值“R3-(U3+U4+U5-AJ3-AJ4-AJ5)”。
你如果真的想实现在D3不为11...77值时，返回FALSE值，就只好在公式2或3的外围再嵌套一个IF函数
=IF(AND(D3=11,D3=22,D3=33,D3=44,D3=55,D3=66,D3=77),公式3)
最终公式为
=Max(0,IF(AJ3=0,0,IF(AM3<=100%,AJ3,VLOOKUP(D3,{11,R3-(U3+U4-AJ3-AJ4);22,R3-(U3+U4+U5-AJ3-AJ4-AJ5);33,R3-(U3+U4+U5+U6-AJ3-AJ4-AJ5-AJ6);44,R3-(U3+U4+U5+U6+U7-AJ3-AJ4-AJ5-AJ6-AJ7);55,0;66,R3-(U3-AJ3);77,R3-(U3+U2-AJ3-AJ2)},2))))
加上有可能返回FALSE值的可能性的公式
=Max(0,IF(AJ3=0,0,IF(AM3<=100%,AJ3,IF(AND(D3=11,D3=22,D3=33,D3=44,D3=55,D3=66,D3=77),VLOOKUP(D3,{11,R3-(U3+U4-AJ3-AJ4);22,R3-(U3+U4+U5-AJ3-AJ4-AJ5);33,R3-(U3+U4+U5+U6-AJ3-AJ4-AJ5-AJ6);44,R3-(U3+U4+U5+U6+U7-AJ3-AJ4-AJ5-AJ6-AJ7);55,0;66,R3-(U3-AJ3);77,R3-(U3+U2-AJ3-AJ2)},2,0)))))

------------------------------------
欢迎到Excel Home论坛学习、交流

Answer 2

那就再套上一层=IF(结果<0,0,结果）
下面就是套好的。我在2007版本中测试通过。
=IF(IF(AJ3=0,0,IF(AM3<=100%,AJ3,IF(D3=66,R3-(U3-AJ3),IF(D3=11,R3-(U3+U4-AJ3-AJ4),IF(D3=22,R3-(U3+U4+U5-AJ3-AJ4-AJ5),IF(D3=33,R3-(U3+U4+U5+U6-AJ3-AJ4-AJ5-AJ6),IF(D3=44,R3-(U3+U4+U5+U6+U7-AJ3-AJ4-AJ5-AJ6-AJ7),IF(D3=55,0,IF(D3=77,R3-(U3+U2-AJ3-AJ2))))))))))<0,0,IF(AJ3=0,0,IF(AM3<=100%,AJ3,IF(D3=66,R3-(U3-AJ3),IF(D3=11,R3-(U3+U4-AJ3-AJ4),IF(D3=22,R3-(U3+U4+U5-AJ3-AJ4-AJ5),IF(D3=33,R3-(U3+U4+U5+U6-AJ3-AJ4-AJ5-AJ6),IF(D3=44,R3-(U3+U4+U5+U6+U7-AJ3-AJ4-AJ5-AJ6-AJ7),IF(D3=55,0,IF(D3=77,R3-(U3+U2-AJ3-AJ2)))))))))))

Answer 3

=max(0,原公式)

使用choose函数替代多层if函数，把原公式简化一下，完整写法：

=MAX(IF(AJ3=0,0,IF(AM3<=100%,AJ3,R3-CHOOSE(D3/11,(U3+U4-AJ3-AJ4),(U3+U4+U5-AJ3-AJ4-AJ5),(U3+U4+U5+U6-AJ3-AJ4-AJ5-AJ6),(U3+U4+U5+U6+U7-AJ3-AJ4-AJ5-AJ6-AJ7),R3,(U3-AJ3),(U3+U2-AJ3-AJ2)))),0)追问

您给此函数简化了，请给予解释，我对choose函数使用不明白。谢谢您指点！

追答

从你前面的公式中看出：D3/11,可能得到的值是正好是1－7共七种情况，对应七种结果。把可能的结果按序排列，使用choose函数的第1参数D3/11，正好从后面罗列的结果中指定选取对应的那一种。
这种简单的一一对应关系，可以使用choose函数，比用嵌套的if函数要简单。
虽然用if函数多层嵌套的威力更强大，能够处理复杂的逻辑关系！但是，这点小case用不着老大出马，choose小弟能搞定的，对吧！呵呵~~

如果把if函数嵌套和choose函数作比较：
choose先生，能够带若干马仔，直接指定其中的一个，快速直达；
而if先生，虽然只有2个直接下级，但他的这2个手下，也可以带有下级，下级还可以有下级……，所以，if函数更强，但他的命令需要通过多层才能达到最终执行者，在有些情况下，就显得效率低了！

本回答被提问者和网友采纳

Answer 4

=IF(AJ3=0,0,IF(IF(AM3<=100%,AJ3,IF(D3=66,R3-(U3-AJ3),IF(D3=11,R3-(U3+U4-AJ3-AJ4),IF(D3=22,R3-(U3+U4+U5-AJ3-AJ4-AJ5),IF(D3=33,R3-(U3+U4+U5+U6-AJ3-AJ4-AJ5-AJ6),IF(D3=44,R3-(U3+U4+U5+U6+U7-AJ3-AJ4-AJ5-AJ6-AJ7),IF(D3=55,0,IF(D3=77,R3-(U3+U2-AJ3-AJ2)))))))))<0,0,IF(AM3<=100%,AJ3,IF(D3=66,R3-(U3-AJ3),IF(D3=11,R3-(U3+U4-AJ3-AJ4),IF(D3=22,R3-(U3+U4+U5-AJ3-AJ4-AJ5),IF(D3=33,R3-(U3+U4+U5+U6-AJ3-AJ4-AJ5-AJ6),IF(D3=44,R3-(U3+U4+U5+U6+U7-AJ3-AJ4-AJ5-AJ6-AJ7),IF(D3=55,0,IF(D3=77,R3-(U3+U2-AJ3-AJ2)))))))))))