excel考勤表怎么做

哎呀，一提到 Excel考勤表 怎么做，我的老腰都快疼了。这玩意儿，说简单也简单，不就是敲几个数、拉几个公式嘛。可真要自己上手，从零开始搭建一个既能用、又好用、还能让老板和同事都满意，不出幺蛾子的考勤表，那简直就是一场修行，一场和数据、和时间、和各种“特殊情况”的搏斗啊！我告诉你，我当年为了这事儿，没少在键盘前挠头，咖啡一杯接一杯，最后才琢磨出一套自以为还算顺手的心得。今天，就跟大家伙儿掰扯掰扯，咱们这个考勤表，到底该怎么“玩”起来。

你是不是也遇到过这种情况？月初，考勤数据堆成山，手写记录七零八落，打卡机导出的数据格式乱七八糟，老板那边催着要月报，你这边对着一堆原始数据，脑壳都开始隐隐作痛。别慌，Excel 就是咱们的救星，是那把能把一堆乱麻理顺的“瑞士军刀”。但记住，它只是工具，关键在于你怎么用它，怎么驯服它。

一、打地基：考勤表的骨架和肉

首先，咱们得把考勤表的“样子”搭起来。一个月的考勤，总得有个地方放吧？我的经验是，一个工作簿（也就是一个Excel文件）对应一个季度或者半年，这样不至于文件太多太碎，也方便年度总结。而一个工作表（Sheet），就对应一个月。比如说，一个工作簿里放“2023年Q1考勤”，里面就有“一月考勤”、“二月考勤”、“三月考勤”三个工作表。

每个工作表里，最最基础的几列是啥？

• A列：序号。这个是给谁看的？是给自己看的，方便快速定位。
• B列：日期。例如：1月1日、1月2日……一直到1月31日。这可不是手动敲的，而是要用公式自动生成。
• C列：星期。这也很重要，毕竟周末和工作日，考勤规则可不一样。同样，也是公式自动搞定。
• D列：姓名/工号。咱们得知道是哪位神仙的考勤吧？
• E列起：考勤状态。这才是重头戏！比如“上班打卡时间”、“下班打卡时间”、“考勤状态（正常/迟到/早退/旷工/请假等）”。

对了，别忘了在表格顶部，预留几行放一些关键参数，比如公司的标准上班时间（例如，9:00），标准下班时间（例如，18:00），以及午休时长、迟到多久算迟到（比如，9:01就算，还是9:15才算）。这些参数可不是摆设，它们是后面所有考勤计算的“指挥棒”，方便你随时调整，免得改一次规则，整个表都要重做一遍。

二、玩转公式：让Excel“动”起来

搭好架子，接下来就是给它注入灵魂——公式。我跟你说，Excel的魅力，全在这儿了！

1. 日期和星期：懒人必备！

• 日期：在B2单元格输入该月的第一天，比如2023/1/1。然后在B3单元格输入=B2+1，选中B3，拉着右下角的小方块（填充柄），一路往下拉，拉到这个月的最后一天。看吧，日期哗啦啦就自动填充好了。简单吧？
• 星期：在C2单元格输入=TEXT(B2,"aaaa")。这个TEXT函数可好使了，能把日期变成你想要的任何文本格式。"aaaa"就是显示“星期一”、“星期二”这样的中文星期。一样，拉动填充柄，整个月的星期就都出来了。

2. 考勤状态判断：灵魂深处的呼唤！

这部分才是真正的硬核，也是最容易让人头秃的地方。假设咱们的考勤规则是：早上9:00上班，晚上18:00下班。

• 迟到判断： 在员工的“考勤状态”列，比如E列，对于某一天的打卡时间，咱们得这么写： =IF(D2="","",IF(D2>$H$1,"迟到","正常")) 这里D2是该员工的上班打卡时间，$H$1是我前面说的预留的标准上班时间（记得用绝对引用$，这样下拉公式的时候才不会变）。D2=""是判断该单元格是否为空，如果为空，说明没打卡，先不判断迟到。如果打卡了，再拿打卡时间和标准时间比较。

  这只是最简单粗暴的“迟到”判断。实际情况呢？是不是还有“旷工”？“请假”？“早退”？

  别着急，咱们一层层地来加料。 我通常会引入一个“原始打卡数据”列（比如F列和G列，分别存上班打卡时间和下班打卡时间），然后根据这些原始数据，在“考勤状态”列（比如H列）进行综合判断。

  假设F2是上班打卡时间，G2是下班打卡时间，$H$1是标准上班时间（9:00），$I$1是标准下班时间（18:00）。

  =IF(AND(F2="",G2=""),"旷工", IF(F2="", "上午旷工", IF(G2="", "下午旷工", IF(F2>$H$1,"迟到","") & IF(G2<$I$1,"早退","") & "正常")))

  是不是有点复杂了？这只是个思路。你可以拆分成几个小公式，或者引入数据验证和VLOOKUP。

  我的小技巧是： 先用 数据验证 （在“数据”选项卡里）给“考勤状态”列设置一个下拉菜单，里面包含“正常”、“迟到”、“早退”、“旷工”、“事假”、“病假”、“年假”等等。这样，你手动录入的时候，就不会出现“迟到”打成“迟刀”的笑话了，避免了统计时的麻烦。

  然后，对于自动判断的部分，可以这样： =IF(F2="",IF(OR(H2="事假",H2="病假",H2="年假"),H2,"旷工"), IF(F2>$H$1,"迟到","正常") ) 这个公式的意思是：如果上班打卡时间为空（F2=""），那么再检查你是不是已经请假了（H2="事假"等），如果是，就显示请假类型；如果没请假，那就算旷工。如果打卡了，再判断是否迟到。

  你看，这里面有嵌套IF，有AND/OR，学问大着呢。我强烈建议你，把“请假”这种特殊情况，放在判断链条的最前端，因为它优先级最高。如果员工已经明确请假了，那他打不打卡、几点打卡，都应该算请假，而不是迟到或旷工。

  比如一个更完善的逻辑可能是这样： =IF(OR(ISNUMBER(MATCH(H2,{"事假","病假","年假","调休"},0))),H2, '先判断是否为各类假别，如果是，直接显示假别 IF(AND(F2="",G2=""),"旷工", '如果上下班都未打卡且非假别，则为旷工 IF(F2="", "上午旷工", '如果只未打上班卡且非假别，则为上午旷工 IF(G2="", "下午旷工", '如果只未打下班卡且非假别，则为下午旷工 TRIM(IF(F2>$H$1,"迟到","") & IF(G2<$I$1,"早退","") & IF(AND(F2<=$H$1,G2>=$I$1),"正常","")))))) 这个公式有点长，MATCH和ISNUMBER用来判断单元格内容是否在请假类型列表里。TRIM是为了去除可能出现的空白。这里的逻辑是：如果已经定义了请假，就直接显示请假类型。否则，再根据打卡情况判断旷工、迟到、早退、正常。

  你瞧，一个简单的考勤状态，背后就能牵扯出这么多逻辑判断。一开始会觉得头大，但一旦理清了思路，你会发现这些函数就像搭积木一样，一块一块拼起来，就能解决大问题。

3. 统计汇总：算算这个月到底上几天班！

考勤表的最终目的，不就是为了统计嘛！比如这个月正常出勤天数、迟到次数、请假天数等等。这就要用到COUNTIF和SUMPRODUCT了。

• 统计某种状态的天数：=COUNTIF(H:H,"迟到")。这会统计H列中“迟到”的次数。很简单，把“迟到”换成“正常”、“旷工”、“事假”就行。

• 统计月出勤天数（排除周末和法定节假日）：这个稍微复杂点。你需要一个节假日列表。 假设你在另一个Sheet叫“假日设置”里，A列是日期。 那么，统计正常出勤天数，可以这样： =SUMPRODUCT((TEXT(B2:B32,"aaaa")<>"星期六")*(TEXT(B2:B32,"aaaa")<>"星期日")*(ISNA(MATCH(B2:B32,假日设置!$A:$A,0)))*(H2:H32="正常")) 这个公式有点长，但功能强大。它会依次判断：是不是周六？是不是周日？是不是节假日？考勤状态是不是“正常”？只有这几个条件都满足，才会计入正常出勤天数。ISNA(MATCH(...))就是判断该日期是否在节假日列表里。SUMPRODUCT的强大之处就在于，它能对多个条件进行乘积运算（True视为1，False视为0），然后求和。

  我告诉你，最开始我做统计的时候，总是忘了排除节假日，结果老板一看报表，说“怎么这个月出勤天数比工作日还多？” 那时候我才知道，细致入微有多重要。

三、锦上添花：让考勤表更智能、更美观

光能算可不行，还得让它好看、好用，最好能一眼看出问题。

1. 数据验证：杜绝手误，规范录入！

我在前面提过，这真是个“神器”！选中你需要输入考勤状态的区域，比如H列。去“数据”选项卡，找到“数据验证”。在“允许”里选择“序列”，然后在“来源”里输入你允许的那些状态，比如正常,迟到,早退,旷工,事假,病假,年假,调休，中间用逗号隔开。 这样，再有人想输入“迟刀”，电脑就会跳出警告，只能从你预设的列表里选。这一下，数据录入的规范性和准确性直接就上去了好几个台阶，统计的时候也省心多了。别小看这小小的下拉菜单，它能帮你省掉多少后续的数据清洗工作！

2. 条件格式：问题一目了然！

“一眼望过去，红红绿绿，谁迟到谁正常，清清楚楚！” 这就是条件格式的魔力。 选中“考勤状态”列，在“开始”选项卡里找到“条件格式”。 * 新建规则：如果单元格值等于“迟到”，格式设为红色字体或红色填充。 * 新建规则：如果单元格值等于“旷工”，格式设为醒目的橙色。 * 新建规则：如果单元格值等于“正常”，格式设为绿色。 * 你甚至可以设置如果打卡时间超过了标准时间，单元格背景变黄。

这个功能，简直是为HR和考勤管理员量身定制的。密密麻麻的考勤表，一下子就有了“生命”，有了“表情”。哪里不对劲，一眼就能看出来，再也不用瞪大眼睛一行行去核对了。

3. 保护工作表：防止手贱误操作！

辛辛苦苦搭好的表格，万一不小心被谁删掉了公式，或者改动了重要参数，那可就前功尽弃了。所以，一定要保护工作表！ 选中你希望别人能编辑的单元格（比如考勤员录入打卡数据的单元格，或者手动录入请假类型的单元格），右键，选择“设置单元格格式”，在“保护”选项卡里把“锁定”前面的勾去掉。 然后，在工作表名称上右键，选择“保护工作表”，设置一个密码。这样，除了你允许编辑的单元格，其他地方，比如公式和标题，就都不能随意改动了。安全感瞬间拉满！

四、我经历的“坑”：血泪教训告诉你！

做考勤表这么多年，踩过的坑比走过的路都多，我可不是夸张。

• 时间格式的坑！ 有时候打卡机导出的是8:30，有时候是830，有时候甚至是2023/1/1 8:30:15。你知道吗？Excel对时间格式非常敏感！"8:30"和"08:30"在某些公式里都能让你痛不欲生。我的建议是，统一格式，统一转换。如果导出的是纯数字，用公式TEXT(A1,"00\:00")把它变成标准时间格式，或者用TIMEVALUE把它转换成数值，再进行比较。否则，你设置的迟到判断，很可能根本不会生效！
• 节假日和周末的坑！ 别以为周末就没人请假、没人加班。如果你的公司有加班或调休机制，这些都要考虑进去。一个单独的节假日对照表非常必要，否则你每个月都要手动修改，不仅麻烦，还容易漏掉。
• 排班制的坑！ 我跟你说，如果你公司实行的是非固定作息的排班制，比如三班倒、大小周、轮班这种，那Excel考勤表会变得非常复杂。因为每个人的标准上班时间、下班时间都不固定，你需要一个更强大的逻辑来匹配。这时候，我个人会更倾向于考虑专业的考勤软件，或者至少是一个更高级的Excel宏（VBA）解决方案。单纯的公式，真的会让你抓狂。
• 数据源不统一的坑！ 有些员工用打卡机，有些用APP打卡，有些是外出手动填写。数据来源一多，格式不一，你就等着哭吧！提前规范好数据导入流程，或者想办法统一转换，是重中之重。不然，再好的考勤表，也架不住“垃圾数据进，垃圾结果出”啊。

五、尾声：考勤表，更是管理的哲学

你瞧，一个看似简单的Excel考勤表，背后却牵扯着日期、时间、文本、逻辑判断、数据统计，甚至还有人性化的管理考量。它不仅仅是冷冰冰的数字和公式，更是公司规章制度的体现，是员工工作状态的缩影。

我做考勤表的这些年，最大的感悟是，它没有“完美”的版本，只有“更适合”的版本。随着公司发展，人员增减，制度变化，你的考勤表也需要不断地迭代和优化。不要害怕修改，不要害怕尝试新的函数和方法。每次的调整，都是你对业务理解的加深，也是你Excel技能的精进。

所以，我的朋友，别再对着一堆杂乱无章的打卡记录犯愁了。拿起你的鼠标，打开Excel，跟着我说的这些点，一步一步地去搭建你自己的考勤表吧。从最简单的开始，慢慢地加入复杂的逻辑，你会发现，它不再是一个负担，而是一个能够为你省心省力的好帮手。

而且，当月底你把一份清晰明了、数据准确的考勤报表提交上去的时候，那种成就感，你懂的，那是属于我们这些在数据里摸爬滚打的“幕后英雄”独有的骄傲！去吧，去征服你的考勤表！