excel大神您好，可以帮我看看以下三个公式怎么可以设成一个？

Question

公式1=IF(L56="试用",0,IF(COUNTIF(C56,"*张娟2*"),"中级",IF(COUNTIF(G56,"*店长*"),"初级",IF(AND(OR(G56="正式导购员",G56="正式收银员",L56="正式"),IF(AC56<30,"初级",IF(AD56<85000,"初级",IF(AND(AD56>=85000,AD56<105000),"中级",IF(AND(AD56>=105000,AD56<150000),"高级",IF(AD56>=150000,"最高级")))))))))公式2==IF(L57="试用",0,IF(COUNTIF(C57,"*张娟2*"),"中级",IF(COUNTIF(G57,"*店长*"),"初级",IF(AND(OR(G57="正式收银员"),L57="正式"),IF(AC57<30,"初级",IF(AD57<45000,"初级",IF(AND(AD57>=55000,AD57<65000),"中级",IF(AD57>=65000,"高级"))))))))公式3==IF(L59="试用",0,IF(COUNTIF(C59,"*张娟2*"),"中级",IF(COUNTIF(G59,"*店长*"),"初级",IF(AND(OR(G59="正式洗浴师"),L59="正式"),IF(AC59<10,"初级",IF(AD59<35000,"初级",IF(AND(AD59>=35000,AD59<45000),"中级",IF(AD59>=45000,"高级"))))))))

Answer 1

用函数向导分析公式，编辑公式是个很好的方法。

我用这个向导试验、简化后的公式,请实际应用试试，欢迎追问:

=IF(L58="试用",0,IF(COUNTIF(C58,"*张娟2*"),"中级",IF(COUNTIF(G58,"*店长*"),"初级",IF(AND(G58="正式导购员",L58="正式"),IF(AC58<30,"初级",IF(AD58<85000,"初级",IF(AD58<105000,"中级",IF(AD58<150000,"高级","最高级")))),IF(AND(G58="正式收银员",L58="正式"),IF(AC58<30,"初级",IF(AD58<45000,"初级",IF(AND(AD58>=55000,AD58<65000),"中级",IF(AD58>=65000,"高级")))),IF(AND(G58="正式洗浴师",L58="正式"),IF(AC58<10,"初级",IF(AD58<35000,"初级",IF(AD58<45000,"中级","高级"))),0))))))

Answer 2

最新版Excel有switch()函数，比if嵌套看着清爽简单得多。

Answer 3

D(AD56>=85000,AD56<105000),"中级",IF(AND(AD56>=105000,AD56<150000),"高级",IF(AD56

Answer 4

把OR(G56="正式导购员",G56="正式收银员")改成left(G56,2)="正式"
应该就统一了吧
把IF(AND(AD56>=85000,AD56<105000),"中级",IF(AND(AD56>=105000,AD56<150000),"高级",IF(AD56>=150000,"最高级"))))
改成LOOKUP(AD56,{0,85000,105000,150000},{"初级","中级","高级","最高级"})可以简化公式。