自制考勤打卡机

自制考勤打卡机 功能点与实践意义 1. 进一步学习在Excel中日期数据格式的设置和显示方式。 2. 掌握宏的录制和应用方法。 3. 学会为按钮等对象指定宏。 4. 掌握生成当前时间的方法：NOW（）函数的应用。 5. 掌握利用“条件格式”功能实现特殊数据的特殊显示。 6. 掌握COUNTIF（）函数的使用方法。 建立表格 设计考勤表结构 (1)启动Excel 2007，按Ctrl+N组合键新建一个工作簿。 (2)在工作表的A2、B2、C2单元格中分别输入“姓名”、“考勤”、“日期”，在D2、D3单元格中都输入“2008-1-1”。 (3)拖选A2：A3单元格，在“开始”工具栏的“对齐方式”组中单击“合并后居中”按钮，将两个单元格合并，同时，其中的文字“姓名”居中对齐。 (4)对B2：B3、C2：C3单元格分别进行相同的操作，使其合并且文字居中，如图所示。 (5)选中D2单元格，在“开始”工具栏的“数字”组中单击（数字格式）下拉列表框右侧的下三角按钮，在弹出的菜单中选择最下面的“其他数字格式”命令，弹出“设置单元格格式”对话框。 (6)在“数字”选项卡的“分类”列表中选择“自定义”，在右侧的列表框中选择“m月d日”，此时在上面的“类型”文本框中即可显示选中的项目，这里我们只需要显示“某日”，因此，在其中删除“m月”的内容，只保留“d日”，单击“确定”按钮，此时单元格中的日期变为如图所示的格式。 (7)选中D3单元格，同样打开“设置单元格格式”的对话框，在“数字”选项卡的“分类”列表中选择“日期”，然后在右侧的“类型”列表中选择“星期三”，这将使单元格显示对应数字日期为星期几。 (8)单击“确定”按钮，此时D3单元格中的显示如图所示。 (9)再次选中D2：D3单元格，然后向右侧用填充柄填充，直到Z列为止（具体根据每个月的天数选择填充长度），然后在“自动填充选项”下拉列表中选择“以工作日填充”，如图所示。 (10)在A列中，每隔一行输入一个员工姓名，然后将每个员工姓名与其下面的空白单元格执行“合并后居中”操作，形成如图所示的结果。 (11)在C4、C5单元格分别输入“上班时间”、“下班时间”，然后选中C4：C5，拖动填充柄至最后一个员工，完成为每个员工填充上下班时间的操作。 (12)在A1单元格中输入工作表的总标题“2008年1月考勤记录”。 至此，考勤表的结构已经设计完成，最终的结果如图所示。 美化工作表 (1) 选中C2:Y23区域（具体根据你所输入的数据选择适当的区域），然后在“开始”工具栏的“样式”组中单击“套用表格格式”按钮，在弹出的样式选项列表中选择一种样式。注意，最好选择那种具有镶边行的样式，因为镶边行是所有奇数行和所有偶数行分别采用不同的两种填充色，因此，正好可以使每个员工的“上班时间”和“下班时间”所在的行有不同的颜色，从而更加方便识别和操作。 (2)选择之后，这时，表格的标题行会出现“筛选”按钮（这里因为宏套用表格样式的时候，会自动创建Excel列表，且默认进入自动筛选状态），在“数据”工具栏中单击“筛选”按钮，取消自动筛选状态。 (3)选择A2：Y23，在“开始”工具栏的“样式”组中单击“单元格样式”按钮，在弹出的列表中选择一种“标题”样式。接着，继续通过使用该功能调整“姓名”列中的填充色和文字颜色。 (4)单击D4单元格，作为一个冻结拆分点，然后在“视图”工具栏的“窗口”组中单击“冻结窗格”按钮，在弹出的下拉菜单中选择“冻结拆分窗格”命令，这样，即可将与日期和姓名等相关的行与列冻结，从而使我们在上下、左右滚动表格时，这些内容一直保持不动，方便阅读和操作。最后的表格如图所示。 这时，创建表格的过程基本完成，下面进行生成考勤时间的设定。 (2)录制宏以生成考勤时间 宏是一系列操作的集合，可用于自动执行任务的一项或一组操作。在本例中，为每个员工记录上下班的时间是一种重复性的操作，因此，可以通过将这一系列的记录上下班时间的动作录制为宏，然后将宏分配给一个按钮对象（也可以使其他图形或控件），以便能够通过单击该对象来运行宏。 添加按钮并为按钮创建宏 （1）首先，将光标定位于任意的单元格中，例如D4，然后在Excel 2007的状态栏中单击“录制新宏”按钮，将弹出如图所示的对话框。 注意：定位光标与D4单元格一定要在弹出对话框之前（也就是正式开始录制宏之前）完成，否则，当进入录制宏的过程中时，定位光标的操作将会被记录到宏中。 (2)在“宏名”文本框中输入为宏指定的名称，然后在“说明”文本框中可以输入一些关于该宏操作的描述性文字，在“保存在”下拉列表框中选择宏要保存的位置，这里选择“当前工作簿”，最后单击“确定”按钮，开始录制宏的过程。 说明：此时按钮变为 (停止录制)，说明从当前开始，录制宏的操作正式开始，我们所要执行的每一步操作都要被记录到宏中，直到单击为止。 (3)下面是要录制到名为“打卡时间”的宏中的操作（即可单击上图中的“确定”按钮之后要进行的操作）。a) 输入“=NOW()”。（单击“确认”按键） b) 这时，单元格中就会产生当前时间，但此时时间的格式类似 于“2008-1-8 16：15”。而我们在考勤记录中只需要时间段的内容，不需要日期段。因此，此时要在“开始”工具栏的“数字”组中单击“数字格式”下拉列表右侧的下三角按钮。在弹出的选项中选择“时间”，这时，就会将单元格中的数字格式化为“时分秒”的格式。 (4)执行完上面的录制宏的操作之后，单击按钮结束录制，即可生成一个名为“打卡时间”的新宏。 为按钮指定宏 (1)在“开发工具”工具栏中的“控件”组中单击“插入”按钮，在弹出的选项中单击“表单控件”中的“按钮（窗体控件）”，如图所示。 (2)此时光标变为十字形状，在B4单元格中拖动即可绘制一个按钮，同时弹出如图所示的对话框。 (3)这里要求为这个绘制的按钮指定一个宏，可以看到，在中间的列表框中已经存在一个我们刚才创建的宏“打卡时间”，用鼠标单击该宏，即可将其添加到“宏名”文本框中，然后单击“确定”按钮，即可为按钮指定该宏。 (4)右键单击刚刚绘制的按钮，在快捷菜单中选择“编辑文字”，然后将按钮上的文字改为“打卡”，同时，还可以设置该文字的字体和字号。 (5)在按钮之外的任意处双击鼠标，即可取消对按钮的选择，这时，将光标定位在D4单元格中，单击“打卡”按钮，即可看到，马上会生成当前的时间。 (6)按照我们的思路，如果在下班时，当姓名为“郭可可”的员工离开公司时，只要将光标定位于该员工的“下班时间”与当日的日期交叉单元格内，如D5（表示2008年1月1日星期二的下班时间）中，然后单击“打卡”按钮即可生成下班时间。但是，当我们这样做的时候，发现在D4单元格中的时间也会随之改变，如图所示。 之所以产生这样的问题，是因为每次生成时间以后，该单元格中表面上是一个时间数字，但实际上它是公式“=NOW()”的计算结果。从编辑栏中可以看出，该单元格仍保持着公式的形式，所以，在其他单元格中执行“打卡”按钮中包含的“打卡时间”宏时，会执行宏中内含的公式“=NOW()”，这样，其他保留该公式的单元格中的数字也就会自然随着改变。要解决这个问题，需要在每次生成时间之后，将该单元格中包含的公式属性去掉。 思路是每次生成时间之后，复制该时间，然后通过“选择性粘贴”功能将复制的时候再通过“粘贴值”命令粘贴回去即可，这可以再次通过一个宏来实现。 我们删除原来的“打卡时间”宏，重新录制一个新的宏。 (7)按照前面录制宏的方法再次录制一个名为“打卡时间”的新宏，首先，将光标定位于任意单元格中，然后单击状态栏中的按钮，进入录制宏的过程，宏中包含的操作为： 1 宏“打卡时间”包含的步骤 2按Ctrl+C组合键复制上述单元格 3在“开始”工具栏的“剪贴板”组中单击“粘贴”按钮，在弹出的菜单中选择“粘贴值”按钮 4按“ESC”键取消复制状态 (8)单击状态栏中的按钮结束录制，同时修改“打卡”按钮显示文字为“上班”。 (9)在“开始”工具栏的“编辑”组中单击“查找和选择”按钮，在弹出的菜单中选中“选择对象”命令。此时，可以通过单击选中插入到文档中的按钮控件对象而不运行其中包含的宏，并能够进行编辑，按住CTRL键拖动该按钮到B5单元格中，然后右键单击，在弹出的快捷菜单中选择“指定宏”命令。 (10)在对话框中为按钮指定刚才录制的新宏“打卡时间”，单击“确定”按钮 (11)将按钮上的文字修改改为“下班”，然后在按钮之外双击，推出按钮编辑状态。 (12)拖选B4:B5单元格，然后拖动填充柄至最后一个员工结束，这样，即可把这两个单元格中的按钮填充进来，效果如图所示。 (13)最后，将工作表标签改为“1月”，这样，该月的考勤表就制作完毕了。 创建其他月份的考勤表 (1)右键单击“1月”工作表的标签，在快捷菜单中选择“移动或复制工作表”命令。 (2)在该对话框中选择“（移至最后）”和“建立副本”，单击“确定”按钮，即可复制一个工作表，将该工作表的标签修改为“2月 ”。 (3)按照同样的方法复制工作表，并修改标签名称，直到“12月”。 (4)进入“2月”考勤表，更改标题为“2008年2月考勤记录”，然后再更改D2和D3单元格中的日期为“2008-2-1”，再填充整月的序列即可。按照同样的方法修改其他工作表的标题和日期序列。 到此为止，整个年度的考勤表已经制作完成，接下来就可投入使用了。 执行考勤 执行考勤的操作比较简单，负责考勤的人员只要激活对应员工，对应日期的上下班时间单元格，单击“打卡”按钮，即可生成当时的时间，接着再单击“确认时间”按钮，即可保证已经打过时间不在随其他人单击“打卡”按钮而改变。如果当天某个员工没有执行考勤，可根据实际情况记入“出差”、“请假”或“缺勤”。 出勤统计 相比考勤来说，对每个月结束之后的员工出勤统计才是最终的目的。单纯的考勤只是记录了员工的上下班时间而已而对员工每个月甚至全年出勤情况的统计，将会从中了解到员工的工作状况和态度。 突出显示迟到和早退记录 (1)在公式编辑栏左侧的名称框中输入“D4:Z35”,按ENTER键之后即可快速选择需要编辑的区域。(2)在“开始”工具栏的“样式”组中单击“条件格式”按钮，在弹出的菜单中选择“新建规则”命令。弹出如图所示的对话框。 (3)在“选择规则类型”中单击“只为包含以下内容的单元格设置格式”，然后在“编辑规则说明”中，设置为“单元格值”、“介于”、“9：00”、“17：30”。 说明:设置条件介于9：00到17：00的意义就是，凡是在这个时间段打卡生成的时间记录，一定是大于9：00，小于17：30.那么。大于9：00的时间就是迟到，小于17：30的时间就是早退。 (4)单击“格式”按钮，弹出如图所示的对话框。 (5)在其中选择一种满足条件的单元格颜色，单击“确定”按钮之后回到“新建格式规则”对话框。再次单击“确定”按钮，即可看到。在考勤表中，满足条件的单元格被特别显示出来。 统计考勤结果 (1)对AA1：AE1区域执行“合并后居中”命令，在其中输入“统计”二字。 (2)在名称框中输入“A1”并按ENTER键，然后单击“开始”工具栏“剪贴板”组中的“格式刷”按钮，此时鼠标光标变为#形状，接着在刚刚合并的AA1单元格中单击，即可将格式快速复制过来。 (3)在AA3：AE3单元格中分别输入“迟到”、“早退”、“缺勤”、“请假”、“出差”。 (4)对于每个员工，在其对应的“迟到”、“早退”、“缺勤”、“请假”、“出差”列中均有两个单元格，要通过“合并后居中”功能将它们合并。 (5)将鼠标定位于AA4单元格中，然后在编辑栏中输入“=COUN”，此时会弹出自动补全公式的下拉选项。 (6)在下来选项栏中提供了以输入字符开头的所有函数名称，这一功能对于用户编辑公式来说非常有用。我们在其中双击“COUNTIF”，即可选中该函数，此时，如果单击编辑栏左侧的“插入公式”按钮，则弹出对话框。 说明：COUNTIF（）函数计算选定区域中满足函数内部参数所设置的某个条件的单元格数目。 (7)这个对话框对于不熟悉该函数的用户来说，可以一边阅读参数的提示文字，一边设置函数参数，非常方便。这里，在“Range”中输入“D4:Z4”，在“Criteria”中输入统计条件“9：00”，这里的9：00表示上班时间，如果大于这个时间即为迟到。单击“确定”按钮，即可看到对员工“郭可可”上班时间的统计结果。 同时，在编辑栏中会生成一个完整的公式“=COUNTIF(D4:Z4,9:00)”. (8)在AB5单元格中要统计的是员工“郭可可”得早退次数。按照上面的方法，再次单元格中生成公式“=COUNTIF(D5:Z5,”17:30”)”,按Enter键之后，即可看到统计结果。 （9）在AC4单元格中统计“缺勤”得次数，输入“=COUNTIF(D4:Z4,”)”即可，表示如果没有考勤时间，即视为“缺勤”。 （10）在AD4单元格中统计“请假”得次数，输入“=COUNTIF(D4:Z4,”请假”)”之后按Enter键即可。 （11）在AE4单元格中统计“出差”的次数，输入“=COUNTIF(D4:Z4，“出差”)”之后按Enter键即可，此时，即可完成第一个员工“郭可可”的出勤统计。 （12）对于其他员工的统计方法，可以通过复制公式来完成，这里就不在多讲了。 直观性设计 尽管实现了对所有员工的出勤统计，但是结果却并不是一目了然。 由于统计出勤的 就是掌握每个员工的非正常上下班得情况，因此，如果将统计结果中次数非“0”得数字特别显示出来，将会更加直观。 （1）选中AA4:AE35区域，即统计数字区域。 （2）在“开始”工具栏得“样式”组中单击“条件格式”按钮，在弹出的菜单中选择“突出显示单元格规则”，在其子菜单中选择“其他规则”命令，弹出如图所示的对话框。 （3）在该对话框中，选择“只为包含以下内容的单元格设置格式”，然后设置条件为：单元格值不等于0，并通过单击“格式”按钮设置满足条件的单元格得颜色为红色，单击“确定”按钮后，即可将非“0”得单元格以红色显示，如图所示。 易用性设计 现在得到的表格有一个明显的使用上的问题，就是当我们在考勤区和统计区中移动时，会由于列数很多而不得不来回拖动水平