excel综合运用函数进行人事管理
综合运用函数进行人事管理：
对员工的有效管理是一个企业成功的关键因素之一，如果企业中的部分员工工作态度不积极，经常迟到、请假，不但会影响其他员工的工作热情，也势必会导致企业业绩的下降。因此作为人事部的管理人员将会制作细致规范的考勤系统，并制定相应的奖惩制度，避免上述现象的发生。由此可见制定员工考勤管理系统是对员工进行有效管理的手段之一。下面通过几个工作表来完成员工考勤管理系统的定制。
本实例所介绍的“员工考勤表”工作薄中包括“员工请假明细表”、“员工年假表”、“员工考勤明细表”、“加班明细表”和“加班时间统计表”5个工作表。
“员工请假明细表”主要用于记录一个月中员工请假的详细情况，效果如下图所示。
“员工年假表”主要用于统计员工年假的使用情况，并将已休完年假的员工记录突出显示，效果如下图所示。
“员工考勤明细表”主要用于体现所有员工每个月的总体出勤情况，并将全勤和没有迟到记录的员工突出显示，效果如下图所示。
在“员工加班明细表”中记录每天员工加班的详细情况，并计算出每个员工加班的时间，效果如下图所示。
在“员工加班时间统计表”中对每个员工当月的加班时间进行汇总，效果如下图所示。
A、员工请假明细表：员工请假明细表的基本框架。
1）在“日期“列中输入员工请假的日期；在“员工编号”列中输入请假员工的员工编号。
2）当输入员工姓名、部门及职位时，可以使用VLOOKUP函数来完成。选择C4单元格，输入公式=“VLOOKUP($B4,员工考勤明细表！$A$3:$D$32,2,0)”，然后按键盘上的<ENTER>键，即可自动输入该员工的姓名，如下图所示。
（公式说明：在“员工考勤明细表”的“$A$3:$D$32”单元格区域中第1列查找$B4单元格中的数值，然后返回第2列中与$B4单元格中所对应的同一行值即“李X亮”。
3）将鼠标指针移到C4单元格区域的右下角，当鼠标指针变成十字形状时，单击鼠标左键并拖曳鼠标至E4单元格，然后释放鼠标，即可自动输入该员工所在的部门和职位。
4）当考勤人员再次输入员工请假明细时，只需要输入请假日期和员工编号，而姓名、部门、职位可以自动填充完成。
说明：当员工请假明细表设置好以后，就可以将员工每天的请假情况录入到表中。通常一个月中的请假记录会比较多，为了方便输入数据，可以将窗格冻结，使工作表的标题部分始终在窗口的上方显示。
说明：将一个月中的所有请假记录都录入完成以后，需要对所有的请假天数进行汇总。
5）选择F4：F41单元格区域，然后单击“公式”选项卡，在“函数库”组中单击“自动求和”图标按钮，即可计算出事假的总天数。
技巧：按键盘上的<Alt>+<=>组合键，可以在单元格中快速输入SUM求和函数公式。
6）将鼠标指针移到F42单元格区域的右下角，当鼠标指针变成十字形状时，单击鼠标左键并拖曳鼠标至I42单元格，然后释放鼠标，即可计算出各种假别的合计数。
说明：为了方便以后的公式引用，可以将“员工请假明细表”中相关数据定义名称，例如，将“员工请假明细表!$B$4:$I$41”单元格区域定义名称为：“请假明细”。
7）在“员工请假明细表”中选择B4:I41单元格区域。
8）单击“公式”选项卡，在“定义的名称”组中选择“定义名称”—“定义名称”命令，打开“新建名称”窗口。
9）在“名称”文本框中输入要定义的名称“请假明细”。
10）单击“确定”按钮即可。
11）可以用同样的方法将“事假”所在的F4:F41单元格区域定义名称为：“事假”；将“年假”所在的G4:G41单元格区域定义名称为：“年假”； 将“病假”所在的H4:H41单元格区域定义名称为：“病假”； 将“其他”所在的I4:I41单元格区域定义名称为：“其他”。
说明：如果要在公式中引用定义名称的单元格区域，可以单击“定义的名称”组中的“用于公式”下拉按钮，然后在弹出的下拉菜单中进行选择。
12）此时，完成“员工请假明细表”的制作。
B、员工年假表：员工请假明细表的基本框架。
提示：当输入完员工编号时，也可以使用VLOOKUP函数在“员工考勤明细表”工作表中查找相应的数据来输入员工的姓名、部门及职位。
1）选择G3单元格，输入公式“=DATEDIF($F3,”2007-12-31”,”Y”)”，然后按键盘上的<Enter>键，即可计算出该员工的入职年限，如下图所示。
公式的说明：计算F3单元格中的日期与2007年12月31日之间的年数。
2）将鼠标指针移到G3单元格区域的右下角，当鼠标指针变成十字形状时，单击鼠标左键并拖曳鼠标至G32单元格，然后释放鼠标，即可计算出所有员工的入职年限。
3）选择H3单元格，输入公式“=IF(G3>=1,IF(G3<=5,3,3+(G3-5)),0)”，然后按键盘上的<Enter>键，即可计算出该员工的年假，如下图所示。
公式的说明：
a）如果G3单元格中的数值大于等于1，则执行IF(G3<=5,3,3+(G3-5))，否则年假为0；
b）IF(G3<=5,3,3+(G3-5))；表示如果G3单元格中的数值小于等于5，则年假为3，否则年假为3+(G3-5)（即每增加一年相应增加一天年假）。
4）将鼠标指针移到H3单元格区域的右下角，当鼠标指针变成十字形状时，单击鼠标左键并拖曳鼠标至G32单元格，然后释放鼠标，即可计算出所有员工的年假。
5）将员工上月的剩余年假复制到年假表中。
6）选择J3单元格，输入公式“=SUMIF(员工请假明细表!$B$4:$B$41,$A3,年假)”， 然后按键盘上的<Enter>键，即可计算出该员工本月所使用的年假，如下图所示。
提示：公式中引用的“年假”是“定义的名称”，在输入“年假”时，可以选择“公式”选项卡，在“定义的名称”组中单击“用于公式”下拉按钮，在弹出的下拉菜单中选择“年假”即可。
公式的说明：首先在“员工请假明细表”中的B4:B41单元格区域中判断哪些数据与A3单元格中的数据相同，然后将“员工请假明细表”中G4:G41单元格区域中符合条件（即年假）的数据相加。
7）将鼠标指针移到J3单元格区域的右下角，当鼠标指针变成十字形状时，单击鼠标左键并拖曳鼠标至J32单元格，然后释放鼠标，即可计算出所有员工本月所使用的年限。
8）选择K3单元格，输入公式“=I3-J3”， 然后按键盘上的<Enter>键，即可计算出员工本月剩余的年假，如下图所示。
9）将鼠标指针移到K3单元格区域的右下角，当鼠标指针变成十字形状时，单击鼠标左键并拖曳鼠标至K32单元格，然后释放鼠标，即可计算出所有员工的本月剩余的年假。
说明：突出显示年假，如果本月剩余年假为0，则该条记录突出显示，可以起到提示的作用。
10）选择A3：K32单元格区域，单击“开始”选项卡，在“样式”组中选择“条件格式”——“新建规则”命令，打开“新建格式规则”窗口。
11）在“选择规则类型”列表框中选择“使用公式确定要设置格式的单元格”项，在“为符合此公式的值设置格式”文本框中输入以下公式“=$K3=0”。
12）单击“格式”按钮，打开“设置单元格格式”窗口。选择“填充”选项卡，在“背景色”区域中选择一种颜色。依次单击“确定”按钮，即可完成设置。
13）此时，完成“员工年假表”的制作。
C、员工考勤明细表：员工考勤明细表的基本框架。
1）选择E3单元格，输入公式”=DATEDIF(员工年假表!$F3,TODAY(),”Y”)，然后按键盘上的<Enter>键，即可计算出该员工的工龄，如下图所示。
公式的说明：计算“员工年假表”中的F3单元格的日期数（即入职时间）与当前日期之间的年数。
2）选择F3单元格，输入公式”=SUMIF(请假明细,A3,事假)”， 然后按键盘上的<Enter>键，即可计算出该员当月所有的事假天数，如下图所示。
注意：公式中的“请假明细”与“事假”是在“员工年假表”中定义的名称。
公式的说明：首先在“员工请假明细表”中的$B$4:$I$41单元格区域中（即请假明细）判断哪些数据与A3单元格中的数据相同，然后将“员工请假明细表”中的$F$4:$F$41单元格区域中（即事假）符合条件的数相加。
3）用同样的方法，分别计算出“年假”、“病假”和“其他”假的天数。
4）选择E3：J3单元格区域，将鼠标指针移到J3单元格区域的右下角，当鼠标指针变成十字形状时，单击鼠标左键并拖曳鼠标至E32:J32单元格区域，然后释放鼠标，即可计算出所有员工的工龄以及各种假别的天数。
5）在“迟到”列中输入员工当月的迟到次数。
6）选择F33单元格，按健盘上的<Alt>+<=>组合键，即可自动输入公式”=SUM(F3:F32)”，确认计算单元格区域是否正确以后，按键盘上的<Enter>键，即可计算出本月事假的总天数。
7）将鼠标指针移到F33单元格区域的右下角，当鼠标指针变成十字形状时，单击鼠标左键并拖曳鼠标至J33单元格，然后释放鼠标，即可计算出各种假别和迟到次数的总天数。
说明：将全勤和没有迟到记录的员工突出显示。
8）选择A3：J32单元格区域，单击“开始”选项卡，在“样式”组中选择“条件格式”——“新建规则”命令，打开“新建格式规则”窗口。
9）在“选择规则类型”列表框中选择“使用公式确定要设置格式的单元格”项，在“为符合此公式的值设置格式”文本框中输入以下公式“=$F3+$G3+$H3+$I3+$J3=0”。
12）单击“格式”按钮，打开“设置单元格格式”窗口。选择“填充”选项卡，在“背景色”区域中选择一种颜色。依次单击“确定”按钮，即可完成设置。
13）此时，完成“员工考勤明细表”的制作。
D、员工加班明细表：“员工加班明细表”的基本框架。
提示：当输入完员工编号时，可以使用VLOOKUP函数来输入员工的姓名、部门及职位，具体方法可以参见“员工请假明细表”。
1）选择H3单元格，输入公式“=(G3-F3)×24”，然后按键盘上的<Enter>键，即可计算出该员工的加班小时数，如下图所示。
公式的说明：用加班结束的时间减去开始时间，然后转换为小时。
2）将鼠标指针移到H3单元格区域的右下角，当鼠标指针变成十字形状时，单击鼠标左键并拖曳鼠标至H41单元格，然后释放鼠标，即可计算出所有员工的加班小时数。
E、员工加班时间统计表：“员工加班时间统计表”的基本框架。
1）选择E3单元格，输入公式”=SUMIF(加班明细表! $B$3:$H$41,$A3,加班明细表! $H$3:$H$41)”， 然后按键盘上的<Enter>键，即可计算出该员工当月的合计加班时间，如下图所示。
公式的说明：首先在“员工加班明细表”中的B3:H41单元格区域中判断哪些数据与A3单元格中的数据相同，然后将“员工加班明细表”中的H3:H41单元格区域中符合条件的数据相加。
2）将鼠标指针移到E3单元格区域的右下角，当鼠标指针变成十字形状时，单击鼠标左键并拖曳鼠标至E32单元格，然后释放鼠标，即可计算出所有员工的合计加班时间。
3）选择E33单元格，然后单击“公式”选项卡，在“函数库”组中单击“自动求和”图标按钮，即可计算出所有员工加班的总天数。
