HR超实用Excel技巧
kuaidi.ping-jia.net 作者：佚名 更新日期：2024-06-28
对于公司的人力资源或行政人员来说，员工的每月考勤统计一直是很头痛的一个问题。从各类考勤机、网上考勤与移动考勤系统导出电子考勤数据并借助Excel进行处理是最基本的统计方法。
如果对Excel了解不够深入的话，也许十人以下规模的公司考勤还能靠眼睛人工应付，几百人的就变得不太现实。
本篇技巧就将向大家介绍如何借助Excel的自带高级功能快速统计考勤数据、减少人工工作量提高工作效率。
让我们先来看下借助Excel可以实现的考勤统计最终效果对比：
初始考勤导出表：
最终完成处理的考勤统计表：
是否有考勤数据一目了然豁然的感觉？借助Excel达到最终统计效果仅仅只需要几个步骤几分钟而已！
还等什么？赶快来看下面的教程一起学习吧！当然，你也可以直接使用 半米人才管理云 来进行数据自动统计汇总，也就无需再看此文啦。
步骤1：导出原始考勤电子表格
首先，我们通过考勤机所连接服务器将考勤数据导入到Excel中，通常我们都会导出以下几项考勤数据内容：员工工号，员工姓名，刷卡日期，刷卡时间。
有时每天每个员工的刷卡时间与次数都是不同的，但不用怕，这些不同的数据会在后续的处理中被过滤掉，不会为最终的统计造成影响。
步骤2：添加上下午标签
现在我们需要为打卡时间设置上下午标签，以便为后续统计提供帮助。
在数据表格中插入“上下午”列，并撰写函数：
IF(D2<--"12:00:00","上午","下午")
然后拖拽整列即可。
步骤3：考勤表格数据分类汇总
接下来我们就要做最关的一步，对原始考勤数据表格进行分类汇总。
此时我们要用到的Excel功能就是“数据透视表”。
依次选择菜单：“插入”>“数据透视表”>“数据透视表”，在弹出的选择窗口中，点选“选择一个表或区域”选项，并如示例图中所示，拖拽选择以下数据列：“姓名”“刷卡日期”“刷卡时间”“上下午”。
点击“确定”按钮，在新工作数据表中，右边侧栏会出现“数据透视表字段列表”栏，根据示例中所示，将对应字段拖拽至对应区域内。
然后我们需要对“刷卡时间”数值进行设置，我们将“刷卡时间”设置为“最小值”。
表格会自动根据“刷卡日期”过滤多余非最小“刷卡时间”的数据，多次重复的打卡时间会被自动过滤。
同时将数字格式设置为“时间”格式。
步骤4：在刚插入的表格旁边再次插入数据透视表
插入位置如图中所示，唯一不同之处就在于“刷新时间”数值选择最大值。
下班时间以最后一次打卡时间为准，其余时间均被被过滤。
步骤5：隐藏数据透视表多余字段
现在的表格已经有我们所需要的全部统计数据了，此时我们还隐藏多余字段列表。
隐藏后的表格就是我们下图中所见
步骤6：上下班打卡统计
接下来我们要对上班时间和下班时间来进行处理。假定公司规定早上上班时间不能晚于7点30分，下午下班时间不能早与17点。那么我们新建两列，分别为：上午迟到情况和下午早退情况，分别编写两个统计函数：
=IF(B6<--"07:30:01",0,1)和=IF(B6<--"07:30:01",0,1)
然后拖拽对应姓名所属范围列即可。
为何迟到是1，不知道是0？这是为了后续总和统计方便所设数值。就如示例图中所示，使用sum函数累加后即可得出当月迟到或早退总数。
步骤7：缺勤统计
如果上下班都未能打卡，记为缺勤。因为我们已经有上下班迟到的标识数值，那么利用这个数值即可很方便的统计当天是否有缺勤及当月缺勤总数。
缺勤函数：
=IF(K7=0,IF(L7=0,1,0),0)
步骤8：剩余考勤数据统计函数
工作日标识函数：=IF(OR(WEEKDAY(A7,2)=6,WEEKDAY(A7,2)=7),0,1)
工作时长函数：=INT((HOUR(H7)*60+MINUTE(H7)-HOUR(B7)*60-MINUTE(B7))/60)&"小时"&MOD((HOUR(H7)*60+MINUTE(H7)-HOUR(B7)*60-MINUTE(B7)),60)&"分钟"
EXCEL函数在HR管理的六大应用
答：想让C1单元格显示: 在合同即将到期30天内提醒:"合同即将到期", 且每日提醒 最好弹出提醒对话框,点击确定 当日不再提醒,点击取消当日在一定时间内继续提醒 =IF(ISERROR(DATEDIF(TODAY(),B2,"D")),"已过期",IF(DATEDIF(TODAY(),B2,"D")>30,"未到期",IF(DATEDIF(TODAY(),B2,"D")=30,"今...
[ 干货] 数据处理必须掌握的40个EXCEL神技巧!(建议收藏)
答：1、快速数据批量求和 ：选中需要求和的数据，按住Alt+= 2、文本数字快速进行拆分：选择数据，找到界面的“数据-分列-固定宽度”作如下的操作即可。3、多单元格批量输入同一个内容 ：选中单元格输入内容，最后按住Ctrl+enter即可。4、多表格数据快速查找：查找替换的时候，选择工作范围按钮进行操作，如下...
Excel 2019表格操作技巧汇总
答：除了数据输入外，其他内容都是由Excel自动计算，智能化很高。同时它还利用数据透视表生成了月度销售报告，以方便大家根据自身需求对表格进行二次修改。4、差旅费报销单 步骤：文件新建，搜索差旅费 出纳姐姐的最爱，月末各项事务繁重，报销又是每天都发生的事。有了这张表格后，所有内容都可以交给报销人自行...
Excel合同日期到期自动提醒函数公式剖析HR必会技能
答：在C2输入公式，并向下复制。=DATE(YEAR(B2),MONTH(B2)+3,DAY(B2)-1) 问题2 根据试用期到期时间，设置试用期7天内自动提醒。 在D2输入公式，并向下复制。=IFERROR(IF(DATEDIF(TODAY(),C2, "d")<=7,"本周试用期到期",""),"") 问题3根据入职时间获取合同到期时间，合同期为1年。
Excel高手常用35个函数解析解决HR工作中80难题
答：1SUM求和函数 可以对单个单元格或某个区域里的数值进行求和，使用语法：SUM(区域1，区域2，……)。 2 2AVERAGE求平均数函数 可以对单个单元格或某个区域求平均数，使用语法：AVERAGE(区域1，区域2，……)。 3 3ROUND四舍五入函数 使用语法：ROUND(目标值，保留小数的位数)。例如=round...
Excel实用技巧:一分钟核对两处金额!会计人必备!
答：右侧，在M2处输入：=SUMIFS(G2:G12,E2:E12,I2,F2:F12,J2)下拉填充：第四步 计算L列、M列的金额差异：在N2处输入：=L2-M2 下拉填充：可以看到，差值不为0的地方即为左右两侧对不上，需要重回两侧对应项目核实是否错误。这个核对小技巧在实际中也用得上，例如财务核对科目余额、HR核对工资等等。
HR超实用Excel技巧_快速统计公司员工考勤记录
答：步骤1：导出原始考勤电子表格 首先，我们通过考勤机所连接服务器将考勤数据导入到Excel中，通常我们都会导出以下几项考勤数据内容：员工工号，员工姓名，刷卡日期，刷卡时间。有时每天每个员工的刷卡时间与次数都是不同的，但不用怕，这些不同的数据会在后续的处理中被过滤掉，不会为最终的统计造成影响。...
HR如何好好利用Excel时间函数
答：日常统计有姓名、性别、身份号码、出生年月日等，其实这几个在Excel输入里面是有技巧的。例如姓名是只能手工输入的，性别可以利用数据有效性进行设置；身份证号码需要设置为文本格式，而且有时候可以利用数据有效性将单元格的文本长度设置为18位，避免人工输入的差错；出生年月日可以利用函数提取身份证号码的...
Excel表格操作技巧汇总教你一秒钟制作表格
答：考勤往往会占据HR很大一部分工作量，这组表格除了最基础的数据统计外。一大亮点是支持自定义假期情况、人事清单、假期类型等等，方便HR们根据自家公司结构调整内容。6、库管缺货报警 步骤：“文件”→“新建”，搜索“库存清单”。这个表格的最大亮点，是能够根据库存商品设置最低阈值，如果一个商品的当前...
HR工作中必用的9个Excel函数值得收藏
答：大家都知道二八原则，小百用九个函数涵盖80%的工作场景，你只需要用心掌握这十个Excel函数，工作效率可能会有质的飞跃 01 IF函数 用途：根据逻辑真假返回不同结果。作为表格逻辑判断函数，处处用得到。函数公式：=IF（测试条件，真值，[假值]）函数解释：当第1个参数“测试条件”成立时，返回第2个参数...
如果对Excel了解不够深入的话，也许十人以下规模的公司考勤还能靠眼睛人工应付，几百人的就变得不太现实。
本篇技巧就将向大家介绍如何借助Excel的自带高级功能快速统计考勤数据、减少人工工作量提高工作效率。
让我们先来看下借助Excel可以实现的考勤统计最终效果对比：
初始考勤导出表：
最终完成处理的考勤统计表：
是否有考勤数据一目了然豁然的感觉？借助Excel达到最终统计效果仅仅只需要几个步骤几分钟而已！
还等什么？赶快来看下面的教程一起学习吧！当然，你也可以直接使用 半米人才管理云 来进行数据自动统计汇总，也就无需再看此文啦。
步骤1：导出原始考勤电子表格
首先，我们通过考勤机所连接服务器将考勤数据导入到Excel中，通常我们都会导出以下几项考勤数据内容：员工工号，员工姓名，刷卡日期，刷卡时间。
有时每天每个员工的刷卡时间与次数都是不同的，但不用怕，这些不同的数据会在后续的处理中被过滤掉，不会为最终的统计造成影响。
步骤2：添加上下午标签
现在我们需要为打卡时间设置上下午标签，以便为后续统计提供帮助。
在数据表格中插入“上下午”列，并撰写函数：
IF(D2<--"12:00:00","上午","下午")
然后拖拽整列即可。
步骤3：考勤表格数据分类汇总
接下来我们就要做最关的一步，对原始考勤数据表格进行分类汇总。
此时我们要用到的Excel功能就是“数据透视表”。
依次选择菜单：“插入”>“数据透视表”>“数据透视表”，在弹出的选择窗口中，点选“选择一个表或区域”选项，并如示例图中所示，拖拽选择以下数据列：“姓名”“刷卡日期”“刷卡时间”“上下午”。
点击“确定”按钮，在新工作数据表中，右边侧栏会出现“数据透视表字段列表”栏，根据示例中所示，将对应字段拖拽至对应区域内。
然后我们需要对“刷卡时间”数值进行设置，我们将“刷卡时间”设置为“最小值”。
表格会自动根据“刷卡日期”过滤多余非最小“刷卡时间”的数据，多次重复的打卡时间会被自动过滤。
同时将数字格式设置为“时间”格式。
步骤4：在刚插入的表格旁边再次插入数据透视表
插入位置如图中所示，唯一不同之处就在于“刷新时间”数值选择最大值。
下班时间以最后一次打卡时间为准，其余时间均被被过滤。
步骤5：隐藏数据透视表多余字段
现在的表格已经有我们所需要的全部统计数据了，此时我们还隐藏多余字段列表。
隐藏后的表格就是我们下图中所见
步骤6：上下班打卡统计
接下来我们要对上班时间和下班时间来进行处理。假定公司规定早上上班时间不能晚于7点30分，下午下班时间不能早与17点。那么我们新建两列，分别为：上午迟到情况和下午早退情况，分别编写两个统计函数：
=IF(B6<--"07:30:01",0,1)和=IF(B6<--"07:30:01",0,1)
然后拖拽对应姓名所属范围列即可。
为何迟到是1，不知道是0？这是为了后续总和统计方便所设数值。就如示例图中所示，使用sum函数累加后即可得出当月迟到或早退总数。
步骤7：缺勤统计
如果上下班都未能打卡，记为缺勤。因为我们已经有上下班迟到的标识数值，那么利用这个数值即可很方便的统计当天是否有缺勤及当月缺勤总数。
缺勤函数：
=IF(K7=0,IF(L7=0,1,0),0)
步骤8：剩余考勤数据统计函数
工作日标识函数：=IF(OR(WEEKDAY(A7,2)=6,WEEKDAY(A7,2)=7),0,1)
工作时长函数：=INT((HOUR(H7)*60+MINUTE(H7)-HOUR(B7)*60-MINUTE(B7))/60)&"小时"&MOD((HOUR(H7)*60+MINUTE(H7)-HOUR(B7)*60-MINUTE(B7)),60)&"分钟"
答：想让C1单元格显示: 在合同即将到期30天内提醒:"合同即将到期", 且每日提醒 最好弹出提醒对话框,点击确定 当日不再提醒,点击取消当日在一定时间内继续提醒 =IF(ISERROR(DATEDIF(TODAY(),B2,"D")),"已过期",IF(DATEDIF(TODAY(),B2,"D")>30,"未到期",IF(DATEDIF(TODAY(),B2,"D")=30,"今...
答：1、快速数据批量求和 ：选中需要求和的数据，按住Alt+= 2、文本数字快速进行拆分：选择数据，找到界面的“数据-分列-固定宽度”作如下的操作即可。3、多单元格批量输入同一个内容 ：选中单元格输入内容，最后按住Ctrl+enter即可。4、多表格数据快速查找：查找替换的时候，选择工作范围按钮进行操作，如下...
答：除了数据输入外，其他内容都是由Excel自动计算，智能化很高。同时它还利用数据透视表生成了月度销售报告，以方便大家根据自身需求对表格进行二次修改。4、差旅费报销单 步骤：文件新建，搜索差旅费 出纳姐姐的最爱，月末各项事务繁重，报销又是每天都发生的事。有了这张表格后，所有内容都可以交给报销人自行...
答：在C2输入公式，并向下复制。=DATE(YEAR(B2),MONTH(B2)+3,DAY(B2)-1) 问题2 根据试用期到期时间，设置试用期7天内自动提醒。 在D2输入公式，并向下复制。=IFERROR(IF(DATEDIF(TODAY(),C2, "d")<=7,"本周试用期到期",""),"") 问题3根据入职时间获取合同到期时间，合同期为1年。
答：1SUM求和函数 可以对单个单元格或某个区域里的数值进行求和，使用语法：SUM(区域1，区域2，……)。 2 2AVERAGE求平均数函数 可以对单个单元格或某个区域求平均数，使用语法：AVERAGE(区域1，区域2，……)。 3 3ROUND四舍五入函数 使用语法：ROUND(目标值，保留小数的位数)。例如=round...
答：右侧，在M2处输入：=SUMIFS(G2:G12,E2:E12,I2,F2:F12,J2)下拉填充：第四步 计算L列、M列的金额差异：在N2处输入：=L2-M2 下拉填充：可以看到，差值不为0的地方即为左右两侧对不上，需要重回两侧对应项目核实是否错误。这个核对小技巧在实际中也用得上，例如财务核对科目余额、HR核对工资等等。
答：步骤1：导出原始考勤电子表格 首先，我们通过考勤机所连接服务器将考勤数据导入到Excel中，通常我们都会导出以下几项考勤数据内容：员工工号，员工姓名，刷卡日期，刷卡时间。有时每天每个员工的刷卡时间与次数都是不同的，但不用怕，这些不同的数据会在后续的处理中被过滤掉，不会为最终的统计造成影响。...
答：日常统计有姓名、性别、身份号码、出生年月日等，其实这几个在Excel输入里面是有技巧的。例如姓名是只能手工输入的，性别可以利用数据有效性进行设置；身份证号码需要设置为文本格式，而且有时候可以利用数据有效性将单元格的文本长度设置为18位，避免人工输入的差错；出生年月日可以利用函数提取身份证号码的...
答：考勤往往会占据HR很大一部分工作量，这组表格除了最基础的数据统计外。一大亮点是支持自定义假期情况、人事清单、假期类型等等，方便HR们根据自家公司结构调整内容。6、库管缺货报警 步骤：“文件”→“新建”，搜索“库存清单”。这个表格的最大亮点，是能够根据库存商品设置最低阈值，如果一个商品的当前...
答：大家都知道二八原则，小百用九个函数涵盖80%的工作场景，你只需要用心掌握这十个Excel函数，工作效率可能会有质的飞跃 01 IF函数 用途：根据逻辑真假返回不同结果。作为表格逻辑判断函数，处处用得到。函数公式：=IF（测试条件，真值，[假值]）函数解释：当第1个参数“测试条件”成立时，返回第2个参数...
