高难度啊,不过excel是无敌的!待我做给你看:
需要你在sheet1里原因后面再加两列,D列(请假类型),E列(小时)
D2公式:=LEFT(C2,2)
E2公式:=IF(D2="病假",IF(RIGHT(C2)="天",SUBSTITUTE(SUBSTITUTE(C2,"天",""),"病假","")*8,SUBSTITUTE(SUBSTITUTE(C2,"小时",""),"病假","")*1),IF(RIGHT(C2)="天",SUBSTITUTE(SUBSTITUTE(C2,"天",""),"事假","")*8,SUBSTITUTE(SUBSTITUTE(C2,"小时",""),"事假","")*1))
不容易,然后咱们来到sheet2里,
在B3里输入:=SUMIFS(E:E,D:D,"病假",A:A,">=2012-1-1",A:A,"<2012-2-1")/8
大功告成了。
追问sheet1里面D列的公式成功了,E列里面公式出来的数据不对
sheet2里面B3里的公式不行,出来是#NAME?,在sheet2里面不要从sheet1里面引用数据吗?