求助

dbzcxy

           说明
一、星期一到星期四上班打卡：7:00---8:30   下班打卡：16:45——17:45
                                星期五下班打卡时间为15:10--16:10

二、空白处为没有打卡，“开会”、“公差”视为已打卡
三、以上规定上下班打卡时间以及没有标注“开会”“出差”均为异常统计数之列。异常的单元格自动填充红色
四、27个工号对应27个异常统计表，让黄色部分自动生成
五、“统计表”中对工号为1的给出示范统计其他黄色部分参照

用公式让黄色部分自动生成数据

cutecpu

因六日的規則跟假設不一樣，所以再另外回復一篇
提供給 您對照公式前後差異(紅色部份)，當作學習

---

黃色公式統計部份:

P3 陣列公式:  
=SUM(COUNTIFS($A$3:$A$68,$N3,$E$3:$E$68,{"","<07:00",">08:30"},$E$3:$E$68,"<>开会",$E$3:$E$68,"<>公差"))
-SUM(COUNTIFS($A$3:$A$68,$N3,$G$3:$G$68,"星期"&{"六","日"},$E$3:$E$68,"<07:00"))
-SUM(COUNTIFS($A$3:$A$68,$N3,$G$3:$G$68,"星期"&{"六","日"},$E$3:$E$68,">08:30"))

Q3 陣列公式:
=SUM(
($A$3:$A$68=$N3)*($F$3:$F$68<>"开会")*($F$3:$F$68<>"公差")*
IFERROR(
IF(WEEKDAY($D$3:$D$68,2)<>5,
ABS("17:45"-$F$3:$F$68)+ABS($F$3:$F$68-"16:45"),
ABS("16:10"-$F$3:$F$68)+ABS($F$3:$F$68-"15:10")
)<>1/24,0)
)
-SUM(COUNTIFS($A$3:$A$68,$N3,$G$3:$G$68,"星期"&{"六","日"},$F$3:$F$68,"<16:45"))
-SUM(COUNTIFS($A$3:$A$68,$N3,$G$3:$G$68,"星期"&{"六","日"},$F$3:$F$68,">17:45"))

R3 陣列公式: =SUM(IF(($A$3:$A$68=N3)*MMULT(($E$3:$E$68={"开会","公差"})+($F$3:$F$68={"开会","公差"}),ROW($1:$2)^0),1))

---

紅色異常格式化部份:

E3:E68 格式化公式:  =IF(WEEKDAY(D3,2)<6,(E3<>"开会")*(E3<>"公差")*((E3<"07:00"*1)+(E3>"08:30"*1)),E3="")
F3:F68 格式化公式:  =IF(WEEKDAY(D3,2)<6,(F3<>"开会")*(F3<>"公差")*(IF(WEEKDAY(D3,2)<>5,ABS("17:45"-F3)+ABS(F3-"16:45"),ABS("16:10"-F3)+ABS(F3-"15:10"))<>1/24),F3="")

祝順心，南無阿彌陀佛！

cutecpu

星期六、日的上下班時間怎麼規定呢？

cutecpu

黃色公式統計部份:
P3 陣列公式:  =SUM(COUNTIFS($A$3:$A$68,$N3,$E$3:$E$68,{"","<07:00",">08:30"},$E$3:$E$68,"<>开会",$E$3:$E$68,"<>公差"))
Q3 陣列公式:
=SUM(
($A$3:$A$68=$N3)*($F$3:$F$68<>"开会")*($F$3:$F$68<>"公差")*
IFERROR(
IF(WEEKDAY($D$3:$D$68,2)<>5,
ABS("17:45"-$F$3:$F$68)+ABS($F$3:$F$68-"16:45"),
ABS("16:10"-$F$3:$F$68)+ABS($F$3:$F$68-"15:10")
)<>1/24,0)
)
R3 陣列公式: =SUM(IF(($A$3:$A$68=N3)*MMULT(($E$3:$E$68={"开会","公差"})+($F$3:$F$68={"开会","公差"}),ROW($1:$2)^0),1))

---

紅色異常格式化部份:
E3:E68 格式化公式:  =(E3<>"开会")*(E3<>"公差")*((E3<"07:00"*1)+(E3>"08:30"*1))
F3:F68 格式化公式:  =(F3<>"开会")*(F3<>"公差")*(IF(WEEKDAY(D3,2)<>5,ABS("17:45"-F3)+ABS(F3-"16:45"),ABS("16:10"-F3)+ABS(F3-"15:10"))<>1/24)

---

註:
1. 原檔案E,F二欄的值格式不太一致，我在附件中有做修正
2. 我先假設星期六、日上下班規則跟星期一到星期四一樣
3. 同一天上下班都有開會或公差的話，統計時我只算一筆

祝順心，南無阿彌陀佛！

dbzcxy

cutecpu 发表于 2019-9-17 20:33
星期六、日的上下班時間怎麼規定呢？

星期六星期天不在打卡范围

dbzcxy

dbzcxy 发表于 2019-9-18 08:05
星期六星期天不在打卡范围

星期六星期天只要打卡了，就要算在之类

dbzcxy

cutecpu 发表于 2019-9-18 04:40
黃色公式統計部份:
P3 陣列公式:  =SUM(COUNTIFS($A$3:$A$68,$N3,$E$3:$E$68,{"","08:30"},$E$3:$E$68,"开 ...

下班异常统计公式有问题。每天上下班有正常的打卡时间，唯独星期五下午例外。另外开会公差不算之列

dbzcxy

你红色部分工号为1的下班异常统计为3，但是公式结果显示为1

cutecpu

dbzcxy 发表于 2019-9-18 08:13
你红色部分工号为1的下班异常统计为3，但是公式结果显示为1

Q3 是陣列公式，請按 Ctrl+Shift+Enter
註: 我上傳的附件忘了按，所以結果有錯

dbzcxy

星期5在公司中哪里体现   公司中1/24,0  是什么意思