上班族，加班补贴算清楚！

送人玫瑰，手有余香，请将文章分享给更多朋友

动手操作是熟练掌握EXCEL的最快捷途径！

【置顶公众号】或者【设为星标】及时接收更新不迷路

小伙伴们好，今天要和大家分享一道比较复杂的题目。这个题目是和我们人事工作相关的，题目是这个样子的：

补贴的标准如下：

-       工作日：出勤打卡4H以上，下班时间在晚上20:00后，补贴15元/次

-       休息日：出勤打卡4H以上，补贴30元/次

-       休息日：出勤打卡8H以上，补贴60元/次

由于它的源数据在规范录入上存在一定的问题，因此这道题目注定又要开一列火车了。

在单元格G2中输入公式“=(SUM(AGGREGATE(14,,(INT($D$2:$D$20)=INT(D2))*($A$2:$A$20=A2)*ISERROR(FIND("无效",$F$2:$F$20))*($E$2:$E$20),{1,2})*{1,-1})*24>4)*(15*(IF(INT(D2)=INT(E2),HOUR(E2),HOUR(E2)+24)>=20)*ISERROR(FIND("无效",F2))+IF((IF(INT(D2)=INT(E2),HOUR(E2),HOUR(E2)+24)>=20)*ISERROR(FIND("无效",F2)),IF(SUM(AGGREGATE(14,,(INT($D$2:$D$20)=INT(D2))*($A$2:$A$20=A2)*ISERROR(FIND("无效",$F$2:$F$20))*($E$2:$E$20),{1,2})*{1,-1})*24>8,45,15),IF(SUM(AGGREGATE(14,,(INT($D$2:$D$20)=INT(D2))*($A$2:$A$20=A2)*ISERROR(FIND("无效",$F$2:$F$20))*($E$2:$E$20),{1,2})*{1,-1})*24>8,60,30))*(COUNTIF($C$2:C2,C2)>1)*(WEEKDAY(D2,2)>5))”，三键回车并向下拖曳即可。

公式太复杂了，简单说一说解题的思路吧。

思路：

• 由于它的记录中有两行的，也有三行的记录，因此我们没有办法直接用相减的方法确定打卡的时长。只好另想办法

• (INT($D$2:$D$20)=INT(D2))*($A$2:$A$20=A2)*ISERROR(FIND("无效",$F$2:$F$20))*($E$2:$E$20)这部分，是题目中的各个条件，打卡日期、员工以及排除无效打卡后的打卡记录。这样处理之后，这一串的结果就是某一个员工某一天上下班的两次打卡记录和一些“0”所组成的内存数组

• AGGREGATE(14,,(INT($D$2:$D$20)=INT(D2))*($A$2:$A$20=A2)*ISERROR(FIND("无效",$F$2:$F$20))*($E$2:$E$20),{1,2})部分，利用AGGREGARE函数，我们可以将这个内存数组中最大的两个值（也就是两次打卡的记录）提取出来。这一步是这道题目的关键所在。“14”是代表最大值，{1,2}代表取前两位

• AGGREGARE()*{1,-1}这部分，让最大值乘以，第二大值乘以“-1”，外侧再嵌套SUM函数，实现了让他们相减的目的

• SUM()*24>4部分，将时间转换为小时，并和4作比较。

• (SUM()*24>4)*(15*()+IF())部分的含义是，对于满足大于4小时的加班，都会给它乘以“15再将上休息日的补助”

• 15*()部分，是用来判断下班时间是否超过20点的。如果没有超过，括号内的逻辑值部分的结果就是“FALSE”，和15相乘后就是0，也就是没有补贴；如果逻辑值部分是“TRUE”，这时紧接着后面的IF()部分来判断该如何返回补助标准。如果超过20点，那么补贴标准“30和60”就要分别减去15，因为超过20点后逻辑值是“TRUE”，等于是前面多加了一个15；如果没有超过20点，那么补贴标准不变仍为“30和60”，因为前面的15乘以逻辑值后为零了

• (COUNTIF($C$2:C2,C2)>1)*(WEEKDAY(D2,2)>5)这部分是两个条件，用来解决休息日的问题，以及将补贴费放在下班打卡的那行记录上

我就知道你“在看”