Question

求大神帮助一下，Excel 如何实现函数IF的嵌套超过七层，怎么优化一下 或者怎么办，跪谢

=IF(AND(D2>=0.7,B2="K4"),"不提供",IF(AND(D2>=0.7,B2="KX3"),"不提供",IF(AND(D2>=0.7,B2="K3S"),"不提供",IF(AND(D2>=0.7,B2="K3"),"不提供",IF(AND(D2>=0.7,B2="福瑞迪"),"不提供",IF(AND(D2>=0.7,B2="智跑"),"不提供",IF(AND(D2>=0.7,B2="K5"),"不提供",IF(AND(D2>=0.7,B2="K2"),"不提供",IF(AND(D2>=0.7,B2="狮跑"),"不提供",IF(OR(B2={"K3S","K4","K3","K2","K5","智跑","KX3","狮跑","福瑞迪"}),E4/12,E4*G35)))))))

Answer 1

大致看了下，OR的条件是共同的，那提出来呗，OR不成立的时候，没看到怎么处理，暂时为空，见以下函数：

=if(OR(B2={"K3S","K4","K3","K2","K5","智跑","KX3","狮跑","福瑞迪"}),if(D2>=0.7,"不提供",E4/12,E4*G35),"")

追问

这是什么意思？求解/

追答

OR(B2={"K3S","K4","K3","K2","K5","智跑","KX3","狮跑","福瑞迪"})

这是你原函数中公式，我看了下，与你前面的if嵌套完全一样，意思是，当B2与{"K3S","K4","K3","K2","K5","智跑","KX3","狮跑","福瑞迪"}其中的一个相同时，就返回true。
你公式的总要求是，当B2等于某个值时，如果D2>=7，那么结果就是"不提供"，否则就是E4/12.
如果B2不满足要求，则返回E4*G35。
你的公式太长了，没看清楚，所以第一次给的公式有错。
=if(OR(B2={"K3S","K4","K3","K2","K5","智跑","KX3","狮跑","福瑞迪"}),if(D2>=0.7,"不提供",E4/12),E4*G35)

Answer 2

用OR函数将那几个AND公式包起来，只用一个IF返回 不提供