求EXCEL条件函数公式

显示全部楼层 · 2011-11-4 11:52:56

姓名季度1季度2季度3表现张1408090普通员工
公式：=if(countif(B2:D2,">85")>2,"优秀员工",if(countif(B2:D2,">85")>1,"上进员工",if(countif(B2:D2,">85")>0,"普通员工","工作待定")))张2908896优秀员工张3205030工作待定公式：=if(countif(B2:D2,">85")>2,"优秀员工",if(countif(B2:D2,">85")>1,"上进员工",if(countif(B2:D2,">85")>0,"普通员工","工作待定")))...

千问 · 2011-11-4 11:52:56

假如三个季度的分数在B/C/D三列，在E列显示评比结果：E2格输入公式：=if(and(b2>=85,c2>=85,d2>=85),"优秀员工",if(or(and(b2>=85,c2>=85),and(c2>=85,d2>=85),and(b2>=85,d2>=85)),"上进员工",if(or(b2>=85,c2>=85,d2>=85),"普通员工"...

千问 · 2011-11-4 11:52:56

假如，季度的分数在B、C、D列，E2=VLOOKUP(COUNTIF(B2:D2,">=85"),{3,"优秀员工";2,"上进员工";1,"普通员工";0,"工作待定"},2,0)然后向下拖公式就可以了...

千问 · 2011-11-4 11:52:56

假如三个季度的分数在B/C/D三列，在E列显示评比结果：E列输入公式：=LOOKUP((A1<85)+(B1<85)+(C1<85),{0,1,2,3;"优秀员工","上进员工","普通员工","工作待定"})...