考勤统计（问题已经解决，没想到这么复杂，感谢老师指导）

花子颢

具体要求见附件，对于我这新手来讲，太难了！
其中的逻辑关系有点复杂，需要仔细读一读要求，不懂的可以再问我。

[ 本帖最后由 花子颢 于 2010-7-29 16:29 编辑 ]

貌似高手

要求是看懂了，下班之前估计无法完成答案，回家再思考。
假设函数的难度最高级为10级的话，这题的难度达到6级，高手可以看看这个啦！

872062000

站位  站位  站位

piny

AH5=IF(SUM(IF($C$4:$AG$4="休",0,IF((C5:AG5="公")+(C5:AG5="Φ")+(C5:AG5="调休"),8,IF(ISERR(-C5:AG5),0,IF(C5:AG5>8,8,C5:AG5)))))<=SUM(--ISNUMBER($C$4:$AG$4))*8,IF(SUM(IF($C$4:$AG$4="休",0,IF((C5:AG5="公")+(C5:AG5="Φ")+(C5:AG5="调休"),8,IF(ISERR(-C5:AG5),0,IF(C5:AG5>8,8,C5:AG5)))))+SUM(IF($C$4:$AG$4<>"休",0,IF(C5:AG5="休",0,C5:AG5)))<=SUM(--ISNUMBER($C$4:$AG$4))*8,IF(SUM(IF($C$4:$AG$4="休",0,IF((C5:AG5="公")+(C5:AG5="Φ")+(C5:AG5="调休"),8,IF(ISERR(-C5:AG5),0,IF(C5:AG5>8,8,C5:AG5)))))+SUM(IF($C$4:$AG$4<>"休",0,IF(C5:AG5="休",0,C5:AG5)))+SUM(IF($C$4:$AG$4="休",0,IF(ISERR(-C5:AG5),0,IF(C5:AG5-8<0,0,C5:AG5-8))))<=SUM(--ISNUMBER($C$4:$AG$4))*8,SUM(IF($C$4:$AG$4="休",0,IF((C5:AG5="公")+(C5:AG5="Φ")+(C5:AG5="调休"),8,IF(ISERR(-C5:AG5),0,IF(C5:AG5>8,8,C5:AG5)))))+SUM(IF($C$4:$AG$4<>"休",0,IF(C5:AG5="休",0,C5:AG5)))+SUM(IF($C$4:$AG$4="休",0,IF(ISERR(-C5:AG5),0,IF(C5:AG5-8<0,0,C5:AG5-8)))),SUM(--ISNUMBER($C$4:$AG$4))*8),SUM(--ISNUMBER($C$4:$AG$4))*8),SUM(--ISNUMBER($C$4:$AG$4))*8)

AJ5=IF(SUM(IF($C$4:$AG$4="休",0,IF((C5:AG5="公")+(C5:AG5="Φ")+(C5:AG5="调休"),8,IF(ISERR(-C5:AG5),0,IF(C5:AG5>8,8,C5:AG5)))))=SUM(--ISNUMBER($C$4:$AG$4))*8,SUM(IF($C$4:$AG$4<>"休",0,IF(C5:AG5="休",0,C5:AG5))),IF(SUM(IF($C$4:$AG$4="休",0,IF((C5:AG5="公")+(C5:AG5="Φ")+(C5:AG5="调休"),8,IF(ISERR(-C5:AG5),0,IF(C5:AG5>8,8,C5:AG5)))))+SUM(IF($C$4:$AG$4<>"休",0,IF(C5:AG5="休",0,C5:AG5)))<SUM(--ISNUMBER($C$4:$AG$4))*8,0,SUM(IF($C$4:$AG$4="休",0,IF((C5:AG5="公")+(C5:AG5="Φ")+(C5:AG5="调休"),8,IF(ISERR(-C5:AG5),0,IF(C5:AG5>8,8,C5:AG5)))))+SUM(IF($C$4:$AG$4<>"休",0,IF(C5:AG5="休",0,C5:AG5)))-SUM(--ISNUMBER($C$4:$AG$4))*8))

AI5=IF(SUM(IF($C$4:$AG$4="休",0,IF((C5:AG5="公")+(C5:AG5="Φ")+(C5:AG5="调休"),8,IF(ISERR(-C5:AG5),0,IF(C5:AG5>8,8,C5:AG5)))))+SUM(IF($C$4:$AG$4<>"休",0,IF(C5:AG5="休",0,C5:AG5)))>SUM(--ISNUMBER($C$4:$AG$4))*8,SUM(IF($C$4:$AG$4="休",0,IF(ISERR(-C5:AG5),0,IF(C5:AG5-8<0,0,C5:AG5-8)))),IF(SUM(IF($C$4:$AG$4="休",0,IF((C5:AG5="公")+(C5:AG5="Φ")+(C5:AG5="调休"),8,IF(ISERR(-C5:AG5),0,IF(C5:AG5>8,8,C5:AG5)))))+SUM(IF($C$4:$AG$4="休",0,IF(ISERR(-C5:AG5),0,IF(C5:AG5-8<0,0,C5:AG5-8))))+SUM(IF($C$4:$AG$4<>"休",0,IF(C5:AG5="休",0,C5:AG5)))<SUM(--ISNUMBER($C$4:$AG$4))*8,0,SUM(IF($C$4:$AG$4="休",0,IF((C5:AG5="公")+(C5:AG5="Φ")+(C5:AG5="调休"),8,IF(ISERR(-C5:AG5),0,IF(C5:AG5>8,8,C5:AG5)))))+SUM(IF($C$4:$AG$4="休",0,IF(ISERR(-C5:AG5),0,IF(C5:AG5-8<0,0,C5:AG5-8))))+SUM(IF($C$4:$AG$4<>"休",0,IF(C5:AG5="休",0,C5:AG5)))-SUM(--ISNUMBER($C$4:$AG$4))*8))

數組

附件有兩活頁，無輔助欄公式如上，可下拉，並自動判斷每月上班總天數
有輔助欄在另一活頁，公式看來較和藹，請參考 ^^

[ 本帖最后由 piny 于 2010-7-28 19:42 编辑 ]

wangg913

原帖由 piny 于 2010-7-28 19:16 发表
AH5=IF(SUM(IF($C$4:$AG$4="休",0,IF((C5:AG5="公")+(C5:AG5="Φ")+(C5:AG5="调休"),8,IF(ISERR(-C5:AG5),0,IF(C5:AG5>8,8,C5:AG5)))))8,8,C5:AG5)))))+SUM(IF($C$4:$AG$4"休",0,IF(C5:AG5="休",0,C5:AG5)))8,8,C5:A ...

仁兄好勤奋，鼓掌予奖励。

貌似高手

这是我见过最牛叉的公式！故掌后致敬！！！

貌似高手

原帖由 piny 于 2010-7-28 19:16 发表
AH5=IF(SUM(IF($C$4:$AG$4="休",0,IF((C5:AG5="公")+(C5:AG5="Φ")+(C5:AG5="调休"),8,IF(ISERR(-C5:AG5),0,IF(C5:AG5>8,8,C5:AG5)))))8,8,C5:AG5)))))+SUM(IF($C$4:$AG$4"休",0,IF(C5:AG5="休",0,C5:AG5)))8,8,C5:A ...

乖乖隆地咚！对仁兄的敬仰犹如黄河长江之水，一发不可收拾！
答案正确！
我也做了一答案，相对来说，简单一些，供参考！期待更为简洁的答案！

[ 本帖最后由 貌似高手 于 2010-7-29 07:47 编辑 ]

花子颢

piny 兄，是我见过为数不多的牛人！
貌似高手，原来真是高手！太厉害了，这么牛的公式，能拜读都不够格！

花子颢

感谢各位的帮助，现有答案已经为我解决了难题！

孙芳

好好，受用了！