求助函数公式

dbzcxy

说明
一、平时下班打卡时间：16:45——17:45
                     2019-09-12临时下班打卡时间为15:10--16:10

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

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

cutecpu

黃色公式統計部份:

O3 陣列公式:
=SUM(
($A$3:$A$68=$M3)*($F$3:$F$68<>"开会")*($F$3:$F$68<>"公差")*
IFERROR(
IF($D$3:$D$68<>"2019-09-12",
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)
)

---

紅色異常標色部份:

F3:F68 範圍公式: =(F3<>"开会")*(F3<>"公差")*(IF(D3<>"2019-09-12",ABS("17:45"-F3)+ABS(F3-"16:45"),ABS("16:10"-F3)+ABS(F3-"15:10"))<>1/24)

祝順心，南無阿彌陀佛！

dbzcxy

怎么看不见公式

cutecpu

dbzcxy 发表于 2019-9-20 08:53
怎么看不见公式

親愛的 dbzcxy 您好:

我在二樓的回復帖子中
已經把公式詳細列出了

不知道您哪邊看不見呢

cabcyvr

O3=SUMPRODUCT((INT($D$3:$D$68)<>DATE(2019,9,12))*($A$3:$A$68&""=M3&"")*(((($F$3:$F$68<TIME(16,45,0))+($F$3:$F$68>TIME(17,45,0)))*($F$3:$F$68<>"")*(ISERROR(FIND($F$3:$F$68,"开会公差出差"))))+($F$3:$F$68=""))*1)+SUMPRODUCT((INT($D$3:$D$68)=DATE(2019,9,12))*($A$3:$A$68&""=M3&"")*(((($F$3:$F$68<TIME(15,10,0))+($F$3:$F$68>TIME(16,10,0)))*($F$3:$F$68<>"")*(ISERROR(FIND($F$3:$F$68,"开会公差出差"))))+($F$3:$F$68=""))*1)

比较粗鲁的思维，分别用两个sumproduct计算12号和非12号异常数量，之后直接相加

cutecpu

cabcyvr 发表于 2019-9-20 11:07
O3=SUMPRODUCT((INT($D$3:$D$68)DATE(2019,9,12))*($A$3:$A$68&""=M3&"")*(((($F$3:$F$68TIME(17,45,0)))*( ...

學習了！
代碼雖長但很直觀
容易讓人理解接受
{:9_305:}

dbzcxy

如果9月11的情况与9月12一样呢，公式怎么变化？

cutecpu

dbzcxy 发表于 2019-9-21 08:59
如果9月11的情况与9月12一样呢，公式怎么变化？

只有 9/11 和9/12嗎

dbzcxy

对，意思就是一个月中有那么临时两个日子，暂时就是9月11,9月12.  公式出来后给你设置最佳

cutecpu

dbzcxy 发表于 2019-9-21 15:21
对，意思就是一个月中有那么临时两个日子，暂时就是9月11,9月12.  公式出来后给你设置最佳

黃色公式統計部份: O3 陣列公式: =SUM( ($A$3:$A$68=$M3)*($F$3:$F$68<>"开会")*($F$3:$F$68<>"公差")* IFERROR( IF(($D$3:$D$68<>"2019-09-11")*(D$3:$D$68<>"2019-09-12"), 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) ) 紅色異常標色部份: F3:F68 範圍公式: =(F3<>"开会")*(F3<>"公差")*(IF((D3<>"2019-09-11")*(D3<>"2019-09-12"), ABS("17:45"-F3)+ABS(F3-"16:45"),ABS("16:10"-F3)+ABS(F3-"15:10"))<>1/24)