考勤监控是每个公司或单位都非常重视的事情。下面就来讲解一下如何用excel制作专业考勤表。
1。打开一张空白色的EXCEL工作表，先绘制如下图所示。
图中，M1:P1是用于填写“年”的合并单元格，S1:T1是用于填写“月”的合并单元格，设置为浅蓝色底纹以示醒目。
2。为了在第二行自动显示“周”，您需要如下设置公式:
在单元格D2中输入公式= if(weekday(date $ m $ 1，$ s ，D3)，2) = 7，””，工作日(日期($M，$S，D3)，2))
此时，您可以在D2单元格中看到单词“day”(这意味着2011年5月1日是星期天)。
公式含义:首先用DATE函数把M1的年、S1的月、D3的日做成计算机能识别的日期；然后使用WEEKDAY函数将这个“日期”转换成用星期表示的数字。
参数“2”被添加到WEEKDAY函数中，以将星期一显示为“1”，将星期二显示为“2”…让星期天显示为“7”。
由于我们不习惯将周日称为“Sunday 7”，最后使用IF函数进行判断，自动将显示的“7”改为“Sunday”。
提示:函数DATE和WEEKDAY在EXCEL自带的帮助中有详细介绍，想了解的朋友可以参考一下。
为了方便我们在中国的习惯，我们不得不将显示为***小写数字的星期改为中文数字，即“星期一”改为“星期一”格式。这需要通过定义单元格格式来实现。
选择单元格D2，按鼠标右键“单元格格式”，在出现的格式窗口中选择数字选项卡，在左侧类别框中选择“特殊”，在右侧类型框中选择“中文小写数字”，按确定退出。
之后，可以用鼠标选中单元格D2，按住其右下角的“填充柄”，拖动***单元格AH2，效果如下:
在AI单元格中，可以用公式显示当月的总天数，公式=DAY(DATE(M1，S1+1，1)-1)
公式含义:首先用日期函数“DATE(M1，S1+1，1)”得出本月下个月第一天的日期。在这个例子中，这个月是5月，下个月的第一天是6月1日。
然后减1得到本月最后一天的日期，也就是5月31日。最后由day函数取出代表本月天数的“31”。
3.先设置一些考勤符号，放在AR栏，如图:
这些符号并不统一，可以根据习惯和喜好自行设置，也可以用汉字表示。总之自己看习惯就够了。
如何方便快捷地将这些符号输入到考勤表的D4:AH33区域？我们使用下拉框方法。
选择D4:AH33区域，在上面的工具栏中按Data-Validity打开有效性设置对话框，选择Settings选项卡，在Allow中选择Sequence，在Source中点击右边的折叠按钮，然后用鼠标选择表格中的AR1:AR8区域，再次点击折叠按钮，返回有效性设置窗口，按OK退出。
完成后，当选择考勤表D4:AH33区域的任意单元格时，会出现一个下拉框按钮。点击按钮弹出下拉框，可以用鼠标方便地选择要输入的考勤符号。
4.已经可以输入出勤。怎么统计大家的出勤情况？或者通过公式自动做统计。
先画一个区域进行考勤统计，如下图红圈所示:
该区域应设置多个合并单元格。AK4:AK5合并，AL4:AL5合并……AP4:AP5合并。也就是每个名字对应的上下线需要合并，方便在一个网格内统计上午和下午。
AL4:AP5区域合并操作完成后，选中该区域右下角的填充柄，按住鼠标左键，然后松开鼠标左键直到单元格AP33，这样后面的单元格就可以快速合并了。(其实只是抄袭了AL4:AP5的风格)
由于第一个人的考勤记录区域是D4:AH5区域，所以需要统计该区域的考勤符号的次数，这样就可以知道这个人的考勤情况。
在AK3:AP3中输入出勤符号，然后在AK4单元格中输入公式= COUNTIF(＄D4:＄AH5，AK3)。
公式含义:使用COUNTIF函数对D4:AH5区域AK3网格中的符号进行多次计数。
使用拖动和***功能将此公式***到AK4:AP4区域。
选择AK4:AP4区域，按住AP4右下角的填充柄，向下拖动，***到AP33单元格。
现在统计区域的每个单元格都有一个公式。因为公式的某些部分使用了绝对引用符号“$”，所以每个单元格的公式在拖动和***时是不一样的。
提示:在这张考勤表中，多次使用了“拖动***”的方法，可以大大简化输入公式和设置格式的操作。而且，通过在公式中灵活使用绝对参考符号“$”，可以将变化规律的公式快速输入到区域中，避免了一个一个输入的麻烦。
现在我们来看看统计公式的效果。
在统计结果中，会有很多0值，这意味着相应的考勤符号没有出现在考勤区域。0值太多会让你觉得“乱”。我们通过设置它们来“隐藏”这些0值。
点击工具栏中的“工具-选项”，将出现选项窗口。如下图设置，去掉“零值”前的勾号，这样这些零值就不会显示了。
5.这里，时间表基本完成。细心的朋友会发现一个小问题，那就是29、30、31这三个日期总是出现在AF3、AG3和AH3，甚至在2月只有28天的时候。他们感到很不舒服。
我们可以利用条件格式使它们根据月份的变化自动显示或隐藏，即当月份小时，AH3单元格变成空白色，当月份大时，显示31。在二月，如果不是闰月，AF3、AG3和AH3单元格中的数字将不会显示。
选择AF3:AH3地区，按“格式-条件格式”，设置如下图所示:
用这种条件格式的方法，还可以设置D2:AH2区域，让它们在周六日变成不同的颜色，可以更直观的展现一周的情况。设置方法可以自己思考。
以上用excel制作考勤表的步骤基本通用，适用于很多公司。也可以根据自己的情况来设置。
本文来自尔岚投稿，不代表舒华文档立场，如若转载，请注明出处：https://www.chinashuhua.cn/24/571485.html
微信扫一扫 
