EXCEL表格的IF函数超过7层,怎么办?在线求大神帮改下公式。。
=IF(C4="通学",IF($B$1-(E4+G4+K4)=0,"学费交满",(E4+G4+K4)- $B$1),IF(C4="通寄①",IF($D$1-(E4+G4+K4)=0,"学费交满",(E4+G4+K4)-$D$1),IF(C4="通寄②",IF($F$1-(E4+G4+K4)=0,"学费交满",(E4+G4+K4)-$F$1),IF(C4="全托",IF($H$1-(E4+G4+K4)=0,"学费交满",(E4+G4+K4)-$H$1),IF(C4="全托自接",IF($J$1-(E4+G4+K4)=0,"学费交满",(E4+G4+K4)-$J$1),IF(C4="公立通学",IF($L$1-(E4+G4+K4)=0,"学费交满",(E4+G4+K4)-$L$1),IF(C4="公立通寄①",IF($N$1-(E4+G4+K4)=0,"学费交满",(E4+G4+K4)- $N$1),IF(C4="公立通寄②",IF($P$1-(E4+G4+K4)=0,"学费交满",(E4+G4+K4)-$P$1),"请输入就读方式"))))))))
(1)$B$1-(E4+G4+K4)和(E4+G4+K4)- $B$1等等表达式,有括号纯属画蛇添足。直接写成:$B$1-E4-G4-K4和E4+G4+K4- $B$1就行。
要改掉这个不良习惯。
(2)IF函数嵌套超过7层,是8层,怎么办?
有两种办法:
1)用2007以上版本的excel。
2)从第7层最后出断开,加上:
"")))))))&
公式最后去掉7个右括号。
再去掉:请输入就读方式
在公式后面加上:&IF(C4="","请输入就读方式","")
至此完毕,如图所示
=IF(C4="通学",IF($B$1-E4-G4-K4=0,"学费交满",E4+G4+K4-$B$1),
IF(C4="通寄①",IF($D$1-E4-G4-K4=0,"学费交满",E4+G4+K4-$D$1),
IF(C4="通寄②",IF($F$1-E4-G4-K4=0,"学费交满",E4+G4+K4-$F$1),
IF(C4="全托",IF($H$1-E4-G4-K4=0,"学费交满",E4+G4+K4-$H$1),
IF(C4="全托自接",IF($J$1-E4-G4-K4=0,"学费交满",E4+G4+K4-$J$1),
IF(C4="公立通学",IF($L$1-E4-G4-K4=0,"学费交满",E4+G4+K4-$L$1),
IF(C4="公立通寄①",IF($N$1-E4-G4-K4=0,"学费交满",E4+G4+K4-$N$1),"")))))))
&IF(C4="公立通寄②",IF($P$1-E4-G4-K4=0,"学费交满",E4+G4+K4-$P$1),"")
&IF(C4="","请输入就读方式","")
=IF(C4="","请输入就读方式",IF(AND(C4="通学",$B$1-(E4+G4+K4)=0),"学费交满",(E4+G4+K4)- $B$1)&
IF(AND(C4="通寄①",$D$1-(E4+G4+K4)=0),"学费交满",(E4+G4+K4)-$D$1)&
IF(AND(C4="通寄②",$F$1-(E4+G4+K4)=0),"学费交满",(E4+G4+K4)-$F$1)&
IF(AND(C4="全托",$H$1-(E4+G4+K4)=0),"学费交满",(E4+G4+K4)-$H$1)&
IF(AND(C4="全托自接",$J$1-(E4+G4+K4)=0),"学费交满",(E4+G4+K4)-$J$1)&
IF(AND(C4="公立通学",$L$1-(E4+G4+K4)=0),"学费交满",(E4+G4+K4)-$L$1)&
IF(AND(C4="公立通寄①",$N$1-(E4+G4+K4)=0),"学费交满",(E4+G4+K4)- $N$1)&
IF(AND(C4="公立通寄②",$P$1-(E4+G4+K4)=0),"学费交满",(E4+G4+K4)-$P$1))
你这个函数改不了,你的判断条件的单元格本身就有8种情况了