楼主
传递 |
03版if一般只能嵌套7层。若超过7层，可以用多种方法处理。
这是远远超过7层的公式- =IF(E8=1,C4,IF(E8=2,C5,IF(E8=3,C6,IF(E8=4,C7,IF(E8=5,C8,IF(E8=6,C9,IF(E8=7,C10,IF(E8=8,C11,""))))))))&IF(E8=9,C12,IF(E8=10,C13,IF(E8=11,C14,IF(E8=12,C15,IF(E8=13,C16,IF(E8=14,C17,IF(E8=15,C18,IF(E8=16,C19,""))))))))&IF(E8=17,C20,IF(E8=18,C21,IF(E8=19,C22,IF(E8=20,C23,IF(E8=21,C24,IF(E8=22,C25,IF(E8=23,C26,IF(E8=24,C27,""))))))))&IF(E8=25,C28,IF(E8=26,C29,IF(E8=27,C30,IF(E8=28,C31,IF(E8=29,C32,IF(E8=30,C33,IF(E8=31,C34,IF(E8=32,C35,""))))))))&IF(E8=33,C36,IF(E8=34,C37,IF(E8=35,C38,"")))
可以CHOOSE替换- =CHOOSE(MATCH(E4,A4:A32,0),"A1","A2","A3","A4","A5","A6","A7","A8","A9","A10","A11","A12","A13","A14","A15","A16","A17","A18","A19","A20","A21","A22","A23","A24","A25","A26","A27","A28","A29")
可以配合LOOKUP- =IF(H23<0.01,"",IF(H23>120,"",LOOKUP(H23+9.9999,E12:G24)))
举例说明:- =IF(G40<=1.5,F40,IF(G40<=2.5,F41,""))&IF(G40<=2.5,,IF(G40<=3.5,F42,IF(G40<=4.5,F43,"")))
公式说明
第一段IF(G40<=1.5,F40,IF(G40<=2.5,F41,""))这段说明：在G40数字小于等于1.5时返回F40，显示A
在G40数字小于等于1.5时返回F40，显示A，如果大于1.5 ，则返回第二条件，判断是不是小于等于2.5，如果是，就返回F41,显示B。如果超过2.5，就显示空（不显示）
第二段&IF(G40<=2.5,,IF(G40<=3.5,F42,IF(G40<=4.5,F43,"")))
当G40的数值小于等于2.5时，都不显示。因为第一段已经有判断小于2.5时条件，第一第二段条件并行存在的。
当G40的数值小于等于3.5时返回F42，显示C。如果G40数值大于3.5，返回下个条件。
当G40的数值小于等于4.5时，返回F43，显示 D ,当数值大于4.5时，为空。
 详解破IF7层.rar
|
