善用Excel，制作加班费统计表

Hello，大家好。今天给大家分享由我制作的加班费统计表，该表由EXCEL完成，下面就和我一起来体验吧。

填写节假日和加班时薪信息

首先我们需要查询万年历，在下图的标记区域1中，将节假日及调休信息填写好。

接着修改标记区域2，将 基准 填写为你所在城市的最低工资标准。

平时 指的是1.5倍加班费，双倍 指的是双倍加班费用，三倍 指的是三倍加班费用。

套用的公式为：平时=基准/21.75/8 * 1.5、双倍=基准/21.75/8 * 2、三倍=基准/21.75/8 * 3，最后将结果保留两位小数。

填写年月信息

如上图所示，只要我们点击箭头，年份和月份就会自动变，说说我是如何实现的。先点击开发工具，再点击插入，选择好对应的控件即可。如下图：

然后我们右键控件，设置控件的格式。这里以月份举例，最小值和最大值，表示1-12月，步长为1表示月份是逐个增加的，单元格链接表示月份所在的单元格地址。

实现日期动态化1

上一步完成后，我们将日期列的首格内容设置为如下图所示的内容。公式为 =DATE(年,月,日)，由于上一步我们已经定义好年和月，我们直接将内容往公式里赋值即可。这里的26我说明一下，由于我所在的公司是以当月26日至下月25日作为工资结算期，所以我就用26日当作首个日期。大家可以根据实际情况修改。

首格日期设置好后，第二格的日期则为 首格+1，由于我的首格为 B7，所以第二行为 B7+1，第三行为 B8+1，以此类推。这样日期就实现动态化，且年月受控件支配。

实现日期动态化2

上一步日期虽然实现动态化，但是每个月的天数并不一致，有大小月之分，还有2月这个特殊的存在。我们知道，每月天数最多的是31天，因此我们必须将日期格子设满31行。但这会有个问题，就是日期少的月份，多余的日期会显示。那么该怎么让多出来的日期消失呢，请看下图的公式：

在上图的公式，我们需要做个判断，即月份大于9且日期大于25的日期，格子自动显示为空白。由于需要判断，所以用 IF 函数，又因为得同时满足两个条件，所以用 AND 函数，条件需要取月份，所以用 MONTH 函数，取天数，就用 DAY 函数。空白是 "" 表示，这样超出的日期就会自动显示空白了。又因为二月天数比较特殊，该公式会报错，所以用 IFERROR 函数，让公式报错时显示为空白。

实现星期动态化

日期动态化实现了，我们还需要星期动态化。这个就简单了，将左边显示日期的公式直接复制，然后选中该列，设置单元格格式为 aaaa 即可。

实现日期高亮

如何实现单元格高亮呢？使用 WEEKDAY 函数即可，该函数会自动提取星期的数值。公式后面的2表示一周从周一开始，即周一表示1。如果你认为一周从周日开始，即周日表示1，那么将公式里的2改为1即可。当星期函数大于5，即周六周日时，单元格会自动变成黄色（具体颜色自己调哦）。下图中展示的是星期列，日期列的设置是同样的方法。

日期类型设置

在文章开头，我们就填写好了全年的节假日和调休信息，这时就派上用场了。

日期类型的判定逻辑很简单，多次判断即可。

<> 表示非空，CONTIF 函数表示带条件统计，后面跟区域和条件。在上面的公式中，当日期非空，且该日期在三倍列（N列）中的数量>=1时，那么就表示 三倍。同理，双倍和平时根据相应公式能推出来。当节假日表中的信息匹配好后，我们还要对不在表中的日期进行判断。这时还用 WEEKDAY 函数，值>5 表示周末，那么是双倍，值<6 表示是周一到周五，那么显示平时。

这样日期类型就判断好了。

工时类型设置

如上图所示，工时类型分为加班、调休、正常，加班指加班时长转作加班费，调休指加班时长转作调休时长，正常表示当日未加班。其实正常这个可以不填，不介意美观的话，直接留空就行。

下面说说实现方法，直接 点击数据 → 数据工具→ 数据验证 即可（WPS上叫数据有效性），如下图所示：

注意： 汉字之间的分隔符号必须为英文输入法模式下的逗号。

加班费计算

如上图的函数所示，当日期为空，却填写加班工时的情况下，将显示值非法。

当未设置工时类型就填写了加班时长，将显示设置工时类型。

当工时类型为正常，却填写加班时长，将显示当日未加班。

当工时类型为调休，将显示0

把上面这四种情况排除，下面就简单了。

当工时类型为 平时，那么将显示 加班工时*平时 的结果

当工时类型为 双倍，那么将显示 加班工时*双倍 的结果

当工时类型为 三倍，那么将显示 加班工时*三倍 的结果

餐补计算

如上图所示，10 表示餐补费用，这根据个人情况修改。COUNTIFS 表示多条件统计。

• 条件1：日期类型为双倍

• 条件2：工时类型为加班

• 条件3：加班时长得大于等于5小时（根据个人情况修改）

以上三个条件就可以计算出满足发放餐补的天数。（由于我三倍工资加班情况很少，所以就没有加入三倍，如有需要可自行加入。）

拿 餐补*满足天数 ，即可算出餐补费用。

薪资计算

薪资的话，不多说，就是简单求和。

薪资的公式为 底薪+加班费用+餐补费用-五险一金费用。

五险一金我默认设 1000，请根据个人情况修改。

好，以上就是表格的制作教程，你学会了吗？