[已解决]高手帮修改下全勤公式。拜谢！没问题给最佳！呵呵

奋斗的小皮孩

职称为小时工的可以不限次数请假，只要工作满40小时视为全勤。现在的问题小时工只要出现请假就判断为非全勤。高手请帮修改这一点。其他的条件都是正常判断的。

具体详见附件。附件在2楼

最佳答案

月排行榜 / 总排行榜

tgydslr

2014-1-14 22:48

再简化合并，数组公式，三键结束

1. =IF(ISBLANK(C5),"",IF(D5="小时工",IF(SUM($E5:$AI5)>=40,"全勤",""),IF((SUM(COUNTIF(E5:AI5,{"旷","早退","旷半"})*ROW(1:3)^0)=0)*(AL5<=15)*IF(D5="兼职",((COUNTIF($E5:$AI5,"请")+SUM(COUNTIF(E5:AI5,{"请半","忘请半","迟请半"})/2*ROW(1:3)^0)<=2)*(SUM(COUNTIF($E5:$AI5,{"√","忘","忘迟","迟","忘2","迟2","请"})*ROW(1:7)^0)+SUM(COUNTIF(E5:AI5,{"请半","忘请半","迟请半"})/2*ROW(1:3)^0)>=$C$2-2)),SUM(COUNTIF(E5:AI5,{"请","请半","忘请半","迟请半"})*ROW(1:4)^0)=0),"全勤","")))
   

复制代码

附件见也随着更新

跳转到最佳答案楼层

奋斗的小皮孩

职称为小时工的可以不限次数请假，只要工作满40小时视为全勤。现在的问题小时工只要出现请假就判断为非全勤。高手请帮修改这一点。其他的条件都是正常判断的。

tgydslr

把非小时工的全勤条件晒一下，看着你的表格眼晕啊

不信这样还重名

=IF(ISBLANK(C5),"",IF((COUNTIF($E5:$AI5,"旷")+COUNTIF($E5:$AI5,"早退")+COUNTIF($E5:$AI5,"旷半")=0)*(AL5<=15)*IF(D5="小时工",SUM(E5:AI5)>=40,TRUE)*IF(D5="兼职",((COUNTIF($E5:$AI5,"请")+COUNTIF($E5:$AI5,"请半")/2+COUNTIF($E5:$AI5,"忘请半")/2+COUNTIF($E5:$AI5,"迟请半")/2)<=2)*((COUNTIF($E5:$AI5,"√")+COUNTIF($E5:$AI5,"忘")+COUNTIF($E5:$AI5,"忘迟")+COUNTIF($E5:$AI5,"迟")+COUNTIF($E5:$AI5,"忘2")+COUNTIF($E5:$AI5,"迟2")*COUNTIF($E5:$AI5,"请")+COUNTIF($E5:$AI5,"请半")/2+COUNTIF($E5:$AI5,"忘请半")/2+COUNTIF($E5:$AI5,"迟请半")/2)>=($C$2-2)),(COUNTIF($E5:$AI5,"请")+COUNTIF($E5:$AI5,"请半")+COUNTIF($E5:$AI5,"忘请半")+COUNTIF($E5:$AI5,"迟请半")=0)),"全勤",IF((D5="小时工")*(SUM(E5:AI5))>=40,"全勤","")))

不信这样还重名

楼主即既说别的条件都正常，那我就只改最后一个条件为否时的参数，即把以前的“”，多嵌套了一个判断IF((D5="小时工")*(SUM(E5:AI5))>=40,"全勤","")，测试了一下，好像可以得到正确结果。前面的条件实在是眼花了，楼主就没问题就将就着使吧，有问题再回贴吧

tgydslr

1. =IF(ISBLANK(C5),"",IF(D5="小时工",IF(SUM($E5:$AI5)>=40,"全勤",""),IF((COUNTIF($E5:$AI5,"旷")+COUNTIF($E5:$AI5,"早退")+COUNTIF($E5:$AI5,"旷半")=0)*(AL5<=15)*IF(D5="兼职",((COUNTIF($E5:$AI5,"请")+COUNTIF($E5:$AI5,"请半")/2+COUNTIF($E5:$AI5,"忘请半")/2+COUNTIF($E5:$AI5,"迟请半")/2)<=2)*((COUNTIF($E5:$AI5,"√")+COUNTIF($E5:$AI5,"忘")+COUNTIF($E5:$AI5,"忘迟")+COUNTIF($E5:$AI5,"迟")+COUNTIF($E5:$AI5,"忘2")+COUNTIF($E5:$AI5,"迟2")*COUNTIF($E5:$AI5,"请")+COUNTIF($E5:$AI5,"请半")/2+COUNTIF($E5:$AI5,"忘请半")/2+COUNTIF($E5:$AI5,"迟请半")/2)>=($C$2-2)),(COUNTIF($E5:$AI5,"请")+COUNTIF($E5:$AI5,"请半")+COUNTIF($E5:$AI5,"忘请半")+COUNTIF($E5:$AI5,"迟请半")=0)),"全勤","")

复制代码

gdw831001

(COUNTIF($E5:$AI5,"旷")+COUNTIF($E5:$AI5,"早退")+COUNTIF($E5:$AI5,"旷半")=0)可以换成(SUM(COUNTIF($E5:$AI5,{"旷","早退","旷半"}))=0)当然下面的哪些还有好多了都可以照这样换了，换完了看的眼就不会花了，

tgydslr

2.rar (10.73 KB, 下载次数: 0)

2014-1-14 22:07 上传

点击文件名下载附件

tgydslr

再简化合并，数组公式，三键结束

1. =IF(ISBLANK(C5),"",IF(D5="小时工",IF(SUM($E5:$AI5)>=40,"全勤",""),IF((SUM(COUNTIF(E5:AI5,{"旷","早退","旷半"})*ROW(1:3)^0)=0)*(AL5<=15)*IF(D5="兼职",((COUNTIF($E5:$AI5,"请")+SUM(COUNTIF(E5:AI5,{"请半","忘请半","迟请半"})/2*ROW(1:3)^0)<=2)*(SUM(COUNTIF($E5:$AI5,{"√","忘","忘迟","迟","忘2","迟2","请"})*ROW(1:7)^0)+SUM(COUNTIF(E5:AI5,{"请半","忘请半","迟请半"})/2*ROW(1:3)^0)>=$C$2-2)),SUM(COUNTIF(E5:AI5,{"请","请半","忘请半","迟请半"})*ROW(1:4)^0)=0),"全勤","")))
   

复制代码

附件见也随着更新

奋斗的小皮孩

豪杰少林，数组公式可以下拉吗？   为什么*ROW（1:7）会自动变，最后变成（62:68）.这个没有影响吗？