Excel怎么制作考勤表

说实话，每次一到月底，尤其对于我们这些管着小团队、小店面，甚至家里那几口子的“家庭CEO”来说，考勤表这玩意儿，简直就是一场无形的战役。密密麻麻的打卡记录、请假条、出差申请…… 我的老天，那叫一个头疼！刚开始，我也是手写、用纸质表格，结果呢？涂涂改改、字迹不清，最后算出来的数字自己都半信半疑。直到有一天，我痛定思痛，决定跟Excel这个“磨人小妖精”好好谈谈，这才发现，原来它才是解决考勤难题的真正“救世主”。

别以为Excel只是个简单的计算器，它可不是！它是一个能把你的思绪、规则、甚至是那些零散的数据，统统规整得服服帖帖的“魔法盒子”。 今天，我就来跟大家聊聊，我是怎么用Excel，从零开始，打造一张既实用又赏心悦目的考勤表的。这不是什么高深的编程，就是一点点耐心，一点点巧思，和Excel里那几个“老伙计”——函数、条件格式、数据验证——的默契配合。

第一步：搭框架，这是地基，马虎不得！

你想啊，造房子得先打地基，考勤表也一样。我们得先规划好，这张表要有哪些基本信息？我一般会这样来设计我的“考勤地基”：

• 表头区域： 别搞得花里胡哨，简洁明了最好。公司名称、部门、考勤月份，这些肯定少不了。我通常会在A1单元格放上大大的“[你的公司名/部门名] 月度考勤表”，然后A2、B2单元格放上“考勤月份：”和“[年份]年[月份]月”（这个月份我们后面要让它变得聪明，能自动切换）。

• 员工信息区域： 这是我们考勤的主体。

  • 第一列：序号（纯粹为了看着舒服，好排序）。
  • 第二列：员工编号（这个太重要了，是唯一的身份标识，可别手写，从人事系统导出来最好）。
  • 第三列：姓名（这不用说，得有）。
  • 第四列开始，就是日期了。从“1日”一直排到“31日”。你可以直接打进去，但我更喜欢让它自己蹦出来，显得我们更专业不是？

• 汇总区域： 这才是我们最关心的“成果展示区”！

  • 应出勤天数
  • 实际出勤天数
  • 迟到次数
  • 早退次数
  • 旷工天数
  • 事假天数
  • 病假天数
  • 年假天数
  • 加班时长（如果你的公司有加班制度，这个就得加上，而且计算会稍微复杂一点，但我们先放这儿）。

你看，这张考勤表，在我脑海里已经有了个大致的骨架。我可不喜欢那种乱糟糟的表格，所以，我会提前把表格的边框、底色、字体大小都先设置好，看着就舒服，才有继续填下去的欲望。

第二步：让日期“活”起来，告别手动调整的苦恼！

这是我个人觉得最“解脱”的一个环节。每个月都要改日期，要是遇到2月这种“短命月”，还得删几列，烦不烦？别烦！Excel有办法。

1. 设置月份输入： 在我们前面预留的“考勤月份”旁边，比如B3单元格，我只会输入一个代表月份的数字，例如“8”代表8月。
2. 自动生成日期： 这是核心。在第一行日期那里（比如E4单元格），我输入公式：=DATE(YEAR(TODAY()),$B$3,1)。这个公式会根据当前年份和你在B3单元格输入的月份，自动生成该月1号的日期。然后，在F4单元格，输入=IF(MONTH(E4+1)=$B$3,E4+1,"")，这个公式的意思是：如果下一天还是当前月份，那就显示下一天；如果不是，就显示空。然后，把F4的公式一直往右拖，拖到你觉得最长的月份（31天）足够的位置。这样，每个月你只要改一下B3的数字，所有日期都会自动更新，月末多余的日期也会自动消失。是不是很酷？
3. 显示星期： 在日期行的下面，紧接着一行，我会用=TEXT(E4,"aaa")来显示这个日期是星期几。这样一眼就能看出周末。然后也往右拖。

第三步：给考勤状态一个“规矩”，拒绝乱七八糟的输入！

我见过太多考勤表，有人写“出勤”，有人写“✔”，有人写“到岗”，五花八门，最后统计起来简直要命！所以，数据验证，也就是我们常说的“下拉菜单”，必须用起来。

• 创建考勤状态列表： 我会在一个不常用的工作表（比如专门建一个叫“设置”的Sheet）里，把所有可能的考勤状态都列出来：正常出勤、迟到、早退、事假、病假、年假、旷工、出差、休息。
• 应用到考勤表： 回到考勤表，选中所有需要录入考勤状态的单元格区域（比如从1日到31日，所有员工的格子），然后点击“数据”选项卡，找到“数据验证”，选择“序列”，来源就选择你刚刚在“设置”Sheet里列出的那些考勤状态。
• 结果： 以后录入考勤，只需要点一下单元格旁边的下拉箭头，选择对应的状态就行了，再也不会出现错别字或者不统一的表达。这省了多少事，懂的都懂！

第四步：核心计算，让Excel替你“数豆子”！

这部分是考勤表的灵魂，各种COUNTIF和COUNTIFS函数要隆重登场了。别怕，它们比你想象的要简单得多，就像一个非常听话的小学数学老师，只会帮你数数。

• 应出勤天数： 这个得先明确你的考勤规则。比如，排除周末（周六日），再排除法定节假日。
  • 首先，在一个隐藏的区域或者“设置”Sheet里，列出所有法定节假日的日期。
  • 然后，对于每个员工，在“应出勤天数”的单元格里，我们可以这样写公式：=SUMPRODUCT((WEEKDAY(日期范围,2)<6)*(COUNTIF(节假日范围,日期范围)=0))。这个公式有点复杂，它会检查日期是不是周六日，是不是节假日，然后把非周末非节假日的加起来。初学者可以简化为=30-COUNTIF(日期范围,"休息")，当然这假设你的“休息”只对应周末。更简便直接的，是直接数日期列里非空的格子，然后减去假期。这需要更精细的逻辑。
  • 我通常会用一个辅助行来标记日期是否为工作日（1为工作日，0为休息/节假日），然后用SUMIF去求和。比如，在日期下方增加一行“工作日标识”，用=IF(OR(WEEKDAY(日期,2)>5,COUNTIF(节假日列表,日期)>0),0,1)，然后=SUM(工作日标识行)。这样更直观。
• 实际出勤天数： 这个简单。=COUNTIF(某员工所有日期范围,"正常出勤")。
• 迟到次数、早退次数、旷工天数、各种假的天数： 都用COUNTIF函数。例如，=COUNTIF(某员工所有日期范围,"迟到")。是不是很简单？只要你用下拉菜单规范了录入，这里就几乎是“傻瓜式”操作。
• 加班时长： 这个就得看你加班记录的方式了。如果每天只是记录一个数字（比如2小时），那用SUMIF或者SUM就行了。如果记录的是具体时间段，那就需要更复杂的时间计算，比如用MOD函数处理跨天加班。不过对于初级考勤表，通常只记录一个总时长。

第五步：美化与高亮，让数据“说话”！

一张密密麻麻的表格，看久了眼睛会花。条件格式就是你的“色彩魔法师”。

• 高亮周末/节假日： 选中日期所在的那一行，然后点击“条件格式”，选择“新建规则”，使用公式来确定要设置格式的单元格。例如，=OR(WEEKDAY(E$4,2)>5,COUNTIF(节假日列表,E$4)>0)，然后设置填充色为浅灰色。这样，周末和节假日就一眼明了了。
• 警示异常情况： 选中每个员工的考勤状态区域。
  • 迟到、旷工： 如果单元格内容是“迟到”或“旷工”，就自动填充成醒目的黄色或红色。
  • 请假类型： 不同的请假类型可以设置不同的颜色，比如事假是蓝色，病假是绿色。
  • 未填写： 甚至可以设置如果某个日期单元格是空白的，就用浅粉色高亮，提醒你是不是漏填了。

这样一来，你的考勤表就不再是死板的数据堆砌，而是会“呼吸”的，异常情况一目了然，大大提高了效率。

第六步：保护你的“劳动成果”，防止误操作！

辛辛苦苦搭好的框架和公式，万一不小心被手滑删了怎么办？那可就欲哭无泪了！所以，一定要保护工作表。

• 解除公式单元格锁定： 默认情况下，所有单元格都是被锁定的。在保护工作表之前，你需要选中所有允许手动输入的单元格（也就是那些考勤状态的单元格），右键“设置单元格格式”，在“保护”选项卡里，把“锁定”前面的勾去掉。
• 保护工作表： 然后点击“审阅”选项卡，选择“保护工作表”，可以设置一个密码。这样，只有知道密码的人才能修改被锁定的单元格，而你的公式和固定信息就安全了。

我的小总结和一点点碎碎念：

搞定一张考勤表，远不止是把公式套进去那么简单。它是你对公司考勤规则的一次深度梳理，是你效率思维的一次体现。

想想看，以前每个月底，我都要对着一堆纸质记录，又是查日期，又是数正字，好几次都算得头昏眼花，怀疑人生。现在呢？每月初，我只需要更新一下B3的月份数字，然后每天在表格里点几下鼠标，月底一秒钟，所有汇总数据，迟到早退，清清楚楚，明明白白。那种成就感，那种掌控感，真是“妙不可言”！

当然，这只是一个相对基础的考勤表。如果你有更复杂的考勤需求，比如精确到分钟的打卡时间、多班次排班、与钉钉/企业微信等考勤系统的数据对接，那可能就需要VBA宏或者更专业的考勤软件了。但对于大部分中小企业或者个人管理来说，一张精心制作的Excel考勤表，已经足够解决90%以上的问题了。

所以，别再犹豫了，别再让那些考勤数据“散兵游勇”了。拿起你的Excel，现在就动手，把它变成你管理考勤的“得力助手”吧！相信我，你会爱上那种清晰、高效、一切尽在掌握的感觉的。