《Excel操作》-第11章 EXCEL在人力资源管理中的应用

本章主要介绍Excel在人力资源管理中的具体应用，包括人员招聘管理、人事资料管理、员工考勤管理、员工绩效与福利管理等。本章简介本章重点重点、难点在Excel中绘制自选图形的方法；数组公式的使用方法。本章难点数组公式的使用方法。11.1人员招聘管理11.2人事资料管理11.3员工考勤管理11.4员工绩效与福利管理本章目录11.1.2制作面试通知书11.1.1制作招聘流程图11.1人员招聘管理11.1.3统计试用期到期人数11.1.1制作招聘流程图企业在每次招聘新员工时都有一套固定的流程，招聘时必须严格按照流程进行，以便最大限度地发现和录用优秀的人才。制作直观的员工招聘流程图，可以帮助企业人力资源管理者更方便、快捷地猎获到所需人才，并为各个部门的紧密协作提供有力保障。11.1.1制作招聘流程图例11-1新建一个工作薄，在Sheet1工作表中，制作一个如图11-1所示的员工招聘流程图，并以“科博公司员工招聘流程图.xls”为文件名保存起来。制作流程图的具体操作步骤如下：图11-1 员工招聘流程图11.1.1制作招聘流程图例11-1新建一个工作薄，在Sheet1工作表中，制作一个如图11-1所示的员工招聘流程图，并以“科博公司员工招聘流程图.xls”为文件名保存起来。制作流程图的具体操作步骤如下：1新建一个工作簿，并以“科博公司员工招聘流程图.xls”为文件名保存起来。2在Sheet1工作表中，把A1:C1单元格区域合并为一个单元格，输入文字“员工招聘流程：”，并把文本左对齐。3分别选中A2、B2、C2单元格，并在其中分别输入文字“人力资源部”、“用人部门”、“总经理”，然后将这3个单元格中的文本进行“水平居中”对齐。11.1.1制作招聘流程图4将A2、B2、C2单元格添加虚线边框；A3:A32单元格区域、B3:B32单元格区域和C3:C32单元格区域添加虚线外边框。5把A列的宽度设为30磅，B、C两列的宽度设为24磅，第1、2行的行高设为25磅。6单击菜单“工具”→“选项”命令，打开“选项”对话框，在对话框中选择“视图”选项卡，把“窗口选项”栏中的“网格线”复选项取消，如图11-2所示。例11-1新建一个工作薄，在Sheet1工作表中，制作一个如图11-1所示的员工招聘流程图，并以“科博公司员工招聘流程图.xls”为文件名保存起来。制作流程图的具体操作步骤如下：图11-2 取消“网格线”复选框11.1.1制作招聘流程图例11-1新建一个工作薄，在Sheet1工作表中，制作一个如图11-1所示的员工招聘流程图，并以“科博公司员工招聘流程图.xls”为文件名保存起来。制作流程图的具体操作步骤如下：7单击菜单“插入”→“图片”→“自选图形”命令，在弹出的“自选图形”工具栏中单击“流程图”按钮，弹出图形选项框，选中“流程图：文档”图标，如图11-3所示。此时，光标变成十字形状，在工作表的A3单元格位置上单击并拖动鼠标左键，添加一个“流程图：文档”图形。图11-3单击“流程图：文档”图标11.1.1制作招聘流程图例11-1新建一个工作薄，在Sheet1工作表中，制作一个如图11-1所示的员工招聘流程图，并以“科博公司员工招聘流程图.xls”为文件名保存起来。制作流程图的具体操作步骤如下：8右键“流程图：文档”图形，在弹出的快捷菜单中选择“添加文字”选项，使“流程图：文档”图形处于文字编辑状态，在该图形中输入“发布/更新招聘信息”文字信息，如图11-4所示。9右键“流程图：文档”图形，在弹出的快捷菜单中选择“设置自选图形格式”选项，打开“设置自选图形格式”对话框，在对话框中单击“对齐”选项卡，在“文本对齐方式”栏，把“水平”和“垂直”的对齐方式均设为“居中”，如图11-5所示。选择“字体”选项卡，把字体的字号设为“11”，单击“确定”按钮。图11-4单击“流程图：文档”图标图11-5设置文本的对齐方式11.1.1制作招聘流程图10采用类似的方法依次继续添加“流程图：过程”图形、“流程图：决策”图形、“流程图：过程”图形、“流程图：决策”图形等自选图形，并添加相应的文字，如图11-6所示。例11-1新建一个工作薄，在Sheet1工作表中，制作一个如图11-1所示的员工招聘流程图，并以“科博公司员工招聘流程图.xls”为文件名保存起来。制作流程图的具体操作步骤如下：图11-6添加各种自选图形11.1.1制作招聘流程图11在“自选图形”工具栏中单击“线条”按钮，弹出线条选项框，然后选中“箭头”图标，如图11-7所示。当光标变成十字形状时，按住Shift键不放，单击并拖动鼠标左键，在第一个“流程图：文档”自选图形的下面，绘制一条垂直向下的黑色箭头。采用同样的方法绘制流程图中的其他箭头，如图11-8所示。例11-1新建一个工作薄，在Sheet1工作表中，制作一个如图11-1所示的员工招聘流程图，并以“科博公司员工招聘流程图.xls”为文件名保存起来。制作流程图的具体操作步骤如下：图11-7设置文本的对齐方式图11-8添加箭头符号11.1.1制作招聘流程图12单击“视图”→“工具栏”，在出现的选项栏中选中“绘图”选项，把“绘图”工具栏显示在窗口的下方。单击“绘图”工具栏中的“文本框”按钮，此时鼠标变成单击“I”字形状，按住鼠标左键不放并在第一个“流程图：决策”图形的下面位置单击，然后输入文字“是”，采用同样的方法在该图形的左边插入文本框，并输入文字“否”。例11-1新建一个工作薄，在Sheet1工作表中，制作一个如图11-1所示的员工招聘流程图，并以“科博公司员工招聘流程图.xls”为文件名保存起来。制作流程图的具体操作步骤如下：13按住Ctrl键不放，拖动“是”字文本框，把文本框复制到流程图其他的相应位置中，采用同样的方法复制“否”字文本框到相应的位置，得到最终的流程图如图11-1所示。11.1.2制作面试通知书人力资源部门经过层层的筛选，最终确定要面试的人选后，需要通知符合录用要求的人员面试。如果需要面试的人员太多，可以通过使用Excel与Word的邮件合并功能来制作面试通知书，然后再通过电子邮件群发面试通知书的方式及时通知候选人面试。下面以制作科博公司面试通知书为例来进行讲解。11.1.2制作面试通知书例11-2通过Word和Excel制作科博公司的面试通知书。具体操作如下：1启动Excel2003，新建一个名为“科博公司面试人员信息表.xls”的工作簿，在Sheet1工作表中输入如图11-9所示数据。图11-9 科博公司面试人员信息表11.1.2制作面试通知书例11-2通过Word和Excel制作科博公司的面试通知书。具体操作如下：2启动Word2003，新建一个名为“科博公司面试通知.doc”的文档，对照图11-10所示的“面试通知单”，在刚刚创建的文档中输入相应的文字。注意：在第一行的“：”和“部门要求”之前分别预留一个空格，以方便插入邮件合并“域”。图11-10 面试通知书的内容11.1.2制作面试通知书例11-2通过Word和Excel制作科博公司的面试通知书。具体操作如下：图11-11 选择工作表3打开“科博公司面试通知.doc”文档，单击“视图”→“工具栏”菜单命令，在弹出的选项栏中单击“邮件合并”选项，调出“邮件合并”工具栏。4单击“邮件合并”工具栏中的“打开数据源”按钮，打开“选取数据源”对话框，选择前面制作好的“科博公司面试人员信息表.xls”工作簿，然后单击“打开”按钮，打开“选择表格”对话框，在对话框中选择Sheet1工作表，如图11-11所示，单击“确定”按钮。11.1.2制作面试通知书例11-2通过Word和Excel制作科博公司的面试通知书。具体操作如下：图11-12 插入“姓名”域5将光标定位在第一行的“：”的前面空格处，单击“邮件合并”工具栏中的“插入域”按钮，弹出“插入合并域”对话框，在对话框中选中“数据库域”单选项，并在下面的“域”栏中选择“姓名”，如图11-12所示，然后单击“插入”按钮，再单击“关闭”按钮，此时在“：”的前面就自动添加“姓名”域了。6采用同样的方法在文档的相应位置插入“应聘部门”、“面试日期”、“面试时间”、“面试地点”和“联系电话”等相关的域。11.1.2制作面试通知书例11-2通过Word和Excel制作科博公司的面试通知书。具体操作如下：图11-13 选择“If...Then...Else”命令7将光标定位在第一行“：”的前面、“姓名”域的后面，单击“邮件合并”工具栏中的“插入Word域”选项旁边的向下三角形，在出现的选项框中选择“If...Then...Else”命令，如图11-13所示，打开“插入Word域：IF”对话框。在对话框中的“域名”下拉列表中选择“性别”，然后在“比较条件”下拉列表中选择“等于”，在“比较对象”文本框中输入“男”，在“则插入此文字”栏中输入“先生”，在“否则插入此文字”栏中输入“女士”，如图11-14所示，单击“确定”按钮，返回文档窗口。插入域后的效果如图11-15所示。选择通知的正文，把所有文字的字号都设置为“小四”。图11-14 设置“插入Word域：IF”对话框图11-15 插入域后的文档11.1.2制作面试通知书例11-2通过Word和Excel制作科博公司的面试通知书。具体操作如下：图11-16 “主文档类型”对话框8单击“邮件合并”工具栏中的“设置文档类型”按钮，打开“主文档类型”对话框，选择“电子邮件”选项，如图11-16所示，单击“确定”按钮。9单击“邮件合并”工具栏中的“合并到电子邮件”按钮，打开“合并到电子邮件”对话框，在“收件人”一栏里选择“Email”，在“主题行”一栏里输入“科博公司面试通知”，如图11-17所示，单击“确定”按钮。图11-17 “合并到电子邮件”对话框11.1.2制作面试通知书例11-2通过Word和Excel制作科博公司的面试通知书。具体操作如下：图11-13 选择“If...Then...Else”命令10单击“邮件合并”工具栏中的“合并到新文档”按钮，打开“合并到新文档”对话框，在对话框中选择“全部”单选项，如图11-18所示，单击“确定”按钮，Word就会自动在一个新文档中创建多份通知书以供打印或保存备份了，把新建文档以“科博公司面试通知书邮件备份.doc”为文件名保存起来。11.1.3 统计试用期到期人数例11-3打开“科博公司试用期到期人数统计表.xls”工作薄，在“试用期到期人数”工作表中，统计截止到当前日期试用期（公司规定新聘员工的试用期为90天，）已到的员工人数。具体操作如下。图11-19 科博公司试用期到期人数统计表打开“科博公司试用期到期人数统计表.xls”工作簿，在“试用期到期人数”工作表中，单击E20单元格，在编辑栏中输入公式“=COUNTIF(E3:E17，"<"&TODAY()-90)”后按Enter键确认，即可计算出试用期到期的人数，如图11-19所示。11.1.3 统计试用期到期人数公式解析：在公式中，先用TODAY函数获得当前日期，然后减去试用期的天数90，再与E列中的聘用日期进行比较。如果比聘用日期大（即日期更晚），则说明该员工已经超过了试用期的90天，最后使用COUNTIF函数来统计符号条件的个数。11.2.2统计各工龄段的员工人数11.2.3提取员工的生日、年龄和性别11.2.1计算员工工龄11.2人事资料管理11.2.1计算员工工龄例11-4打开“伟全科技公司员工工龄表.xls”工作簿，在Sheet1工作表中，根据员工的工作时间计算他们的工龄。具体操作方法如下。图11-20 计算出所有员工的工龄打开“伟全科技公司员工工龄表.xls”工作簿，在Sheet1工作表中，单击E2单元格，输入公式“=DATEDIF(D2,TODAY(),"Y")”后按Enter键确认，得到结果“8”。2移动鼠标到D2单元格的右下角，拖动填充柄，将公式向下复制到单元格D21，算出其他员工的工龄，如图11-20所示。111.1.3 统计试用期到期人数本例的公式说明：在公式“=DATEDIF(D2,TODAY(),"Y")”中，D2单元格里存放了某位员工的工作时间，该时间作为起始日期，利用TODAY函数计算当前系统日期作为结束日期；因为是计算工龄，只需要得到以“年”为单位的结果即可，所以时间单位参数设为“Y”。至此便可以计算出该员工的工龄。员工的工龄。11.2.2 统计各工龄段的员工人数例11-5打开“伟全科技公司员工各工龄段人数统计表.xls”工作簿，在Sheet1工作表中，统计各工龄段的员工人数。统计各工龄段的人数可以通过使用COUNTIF函数和使用FREQUENCY数组公式两种方法。11.2.2 统计各工龄段的员工人数例11-5打开“伟全科技公司员工各工龄段人数统计表.xls”工作簿，在Sheet1工作表中，统计各工龄段的员工人数。（1）使用COUNTIF函数统计第一步：打开“伟全科技公司员工各工龄段人数统计表.xls”工作簿，在Sheet1工作表中的G1:H8单元格区域中输入如图11-21所示的信息。单击H3单元格，输入公式“=COUNTIF($E$2:$E$21，">20")”后，按Enter键确认。此时，在H3单元格中显示工龄结果。第二步：图11-21 输入相关的信息11.2.2 统计各工龄段的员工人数例11-5打开“伟全科技公司员工各工龄段人数统计表.xls”工作簿，在Sheet1工作表中，统计各工龄段的员工人数。第三步：依次在H4、H5、H6、H7单元格中，分别输入公式“=COUNTIF($E$2:$E$21,">15")-COUNTIF($E$2:$E$21,">20")”、“=COUNTIF($E$2:$E$21,">10")-COUNTIF($E$2:$E$21,">15")”、“=COUNTIF($E$2:$E$21,">5")-COUNTIF($E$2:$E$21,">10")”和“=COUNTIF($E$2:$E$21,"<=5")”，然后按Enter键，得到各个工龄段的员工人数。如图11-22所示。在单元格H8中，输入公式“=SUM(H3:H7)”并按Enter键确认，核对一下各工龄段的总人数是否正确。第四步：图11-21 输入相关的信息11.2.2 统计各工龄段的员工人数例11-5打开“伟全科技公司员工各工龄段人数统计表.xls”工作簿，在Sheet1工作表中，统计各工龄段的员工人数。（2）使用FREQUENCY数组公式统计使用FREQUENCY数组公式统计各工龄段员工人数的方法请参阅第3章的相关介绍。11.2.3提取员工的生日、年龄和性别在对公司的员工信息进行管理时，通过员工提供的身份证号码，可以提取员工的出生日期、年龄和性别等信息。我国的居民身份证号码是一组特征组合码，原为15位，现升级为18位，其编码规则为：身份证为15位的，前面的6位数字是常住户口所在县市的行政区划代码，接着的6位数字为出生日期代码，后面的3位数字为顺序码。身份证为18位的，前面的6位数字是常住户口所在县市的行政区划代码，接着的8位数字为出生日期代码，然后是3位数字顺序码和1位检验码。其中3位数字顺序码，是为同一地址码的同年同月同日出生人员编制的顺序号，偶数的为女性，奇数的为男性。11.2.3提取员工的生日、年龄和性别例11-6打开“力宏公司员工身份证信息.xls”工作簿，在 Sheet1工作表中，B列为公司员工的身份证号码，使用公式从身份证号码中提取员工的性别和出生日期，然后再根据出生日期计算员工的年龄。具体的操作方法如下。在C2单元格中，输入公式“=IF(LEN(B2)=18,IF(MOD((MID(B2,17,1)),2)=1,"男","女"),IF(MOD((MID(B2,15,1)),2)=1,"男","女"))”，按Enter键确认，判断出第一个员工的性别。12向下拖动C2单元格右下角的填充柄，将公式复制单元格C8，自动判断出其他员工的性别，如图11-23所示。图11-23 从身份证号码中判断性别11.2.3提取员工的生日、年龄和性别公式解析：在公式中，先用LEN(B2)=18判断身份证号码是否是18位，如果是18位，那么用MID函数从身份证号码中提取第17位，接着用MOD函数判断该数字是否为奇数，如果是奇数则判断为男性，否则为女性。如果不是18位的身份证号，那么就是15位的，按照类似的方法提取第15位数字并判断是否是奇数，如果是奇数则为男性，否则为女性。在公式中，判断数字是否为奇数，也可以用ISODD函数来实现。此时，公式应为“=IF(LEN(B2)=18,IF(ISODD(MID(B2,17,1)),"男","女"),IF(ISODD(MID(B2,15,1)),"男","女"))”。在Excel2003中，需要安装加载项才能使用ISODD函数。方法为：单击“工具”菜单中的“加载宏”命令，在打开的“加载宏”对话框中选择“分析工具库”复选框，单击“确定”按钮。11.2.3提取员工的生日、年龄和性别在D2单元格中，输入公式“=TEXT(IF(LEN(B2)=18,MID(B2,7,8),"19"&MID(B2,7,6)),"0000年00月00日")”，按Enter键确认，提取出第一个员工的出生日期。3公式解析：与判断性别函数有点类似，在公式中先用LEN(B2)=18判断身份证号码是否是18位，如果是18位，那么用MID函数从身份证号码中的第7位开始连续提取8个数字；如果是15位，那么用MID函数从身份证号码中的第7位开始连续提取6个数字，并在前面添加数字“19”。最后使用TEXT函数把提取的数字格式化为“年”、“月”、“日”的形式。11.2.3提取员工的生日、年龄和性别向下拖动D2单元格右下角的填充柄，将公式复制到单元格D8，自动提取出其他员工的出生日期，如图11-24所示。445在E2单元格中，输入公式“=DATEDIF(D2,TODAY(),"Y")”，按Enter键确认，计算出第一个员工的年龄。向下拖动E2单元格右下角的填充柄，将公式复制到单元格E8，计算出其他员工的年龄，如图11-25所示。图11-24 从身份证号码中提取出生日期图11-24 从身份证号码中提取出生日期11.3.2制作考勤统计表11.3.3批量制作考勤表11.3.1统计员工迟到和早退情况11.3员工考勤管理11.3.1统计员工迟到和早退情况在对员工进行日常考勤管理过程中，需要准确地记录每一天员工的迟到和早退情况。比如，在一个单独的列中根据员工的上、下班打卡时间自动统计出缺勤的次数，用颜色直接对迟到或早退的时间进行标记，让所有缺勤的打卡时间一目了然等。下面举例讲解使用条件格式对数据标记格式以及统计缺勤次数的方法。11.3.1统计员工迟到和早退情况例11-7打开“伟全科技公司员工考勤情况表.xls”工作簿，在Sheet1工作表中，D列和E列分别是每个员工的早上上班打卡时间和下午下班的打卡时间，公司规定：上班打卡时间不能晚于8点，下午打卡时间不能早于17:30；如果员工迟到或早退一次，都记缺勤1次。利用条件把D、E两列中不符合规定的打卡时间用红色底纹进行标记，并统计出每个员工的缺勤次数。具体的操作步骤如下。选择单元格D1:D11，单击“格式”→“条件格式”菜单命令，打开“条件格式”对话框。在“条件格式”对话框中的“条件1”下拉列表中选择“单元格数值”，然后在其右侧的下拉列表中选择“大于”，接着在最右侧的文本框中输入时间“8:00:00”。1211.3.1统计员工迟到和早退情况单击“格式”按钮，选择“图案”选项卡，把单元格的底纹颜色设置为红色，单击“确定”按钮返回“条件格式”对话框，设置好的“条件格式”如图11-26所示，单击“确定”按钮，Excel将对选中单元格中时间超过8点的单元格设置红色的底纹，如图11-27所示。3图11-26设置上班打卡时间的条件格式图11-27对迟到的时间进行标记11.3.1统计员工迟到和早退情况对E列的时间也做类似的条件格式设置，只不过在“条件格式”对话框中，“条件1”的下拉列表中选择“单元格数值”后，在其右侧的下拉列表中选择“小于”，在最右侧的文本框中输入时间“17:30:30”，如图11-28所示，最终的结果如图11-29所示。图11-26设置上班打卡时间的条件格式图11-27对迟到的时间进行标记411.3.1统计员工迟到和早退情况单击单元格F2，输入公式“=SUM(D2>TIMEVALUE("8:00:00"),E2<TIMEVALUE("17:30:00"))”，按Enter键确认，统计出第一个员工上、下班迟到和早退的次数总和。图11-30统计员工的缺勤次数65向下拖动F2单元格右下角的填充柄，将公式复制到单元格F11为止，自动统计出其他员工的缺勤情况，如图11-30所示。11.3.2 制作考勤统计表在公司的实际管理中，有时还要详细地统计员工迟到或早退的情况。比如，有些公司规定：员工迟到或早退的时间超过了30分钟就要扣除一定的出勤奖金，此时，就要统计员工迟到或早退了多少小时和多少分钟，以便进行相应的处罚。下面通过实际的案例来讲解使用函数计算时间差以及从时间中提取小时和分钟的方法。11.3.2 制作考勤统计表例11-8打开“伟全科技公司员工考勤统计表.xls”工作簿，在Sheet1工作表中，单元格B1和E1中的时间为公司规定的上班时间和下班时间，C、D两列中的时间分别为每位员工上、下班的打卡时间，要求根据员工实际的打卡时间，来计算每个员工迟到或早退的具体时间。具体的操作步骤如下。第一步：第二步：单击E4单元格，输入公式“=IF(C4>$B$1,IF(MINUTE(C4)>=MINUTE($B$1),HOUR(C4)-HOUR($B$1),HOUR(C4)-HOUR($B$1)-1),0)”，按Enter键确认，算出第一个员工上班打卡迟到的小时数。向下拖动E4单元格右下角的填充柄，将公式复制到单元格E13为止，算出其他员工迟到的小时数。11.3.2 制作考勤统计表公式解析：在公式中，先判断C4单元格中的打卡时间是否大于B1单元格中公式规定的上班打卡时间，如果大于，那么该员工就迟到了。然后比较员工打卡时间中的分钟是否大于或等于规定时间的分钟，如果是的话，那么可以直接用员工打卡的小时数减去规定打卡的小时数，得到迟到的小时数；否则需要将打卡小时数减去规定打卡的小时数后再减1。如果C4单元格中的打卡时间小于规定的打卡时间，那么就返回0，因为员工提前来上班了。11.3.2 制作考勤统计表第三步：第四步：第五步：单击F4单元格，输入公式“=IF(C4>$B$1,IF(MINUTE(C4)>=MINUTE($B$1),MINUTE(C4)-MINUTE($B$1),MINUTE(C4)+60-MINUTE($B$1)),0)”，按Enter键确认，计算出第一个员工上班打卡迟到的分钟数。单击G4单元格，输入公式“=IF(D4<$E$1,IF(MINUTE(D4)>=MINUTE($E$1),HOUR($E$1)-HOUR(D4)-1,HOUR($E$1)-HOUR(D4)),0)”，按Enter键确认，算出第一个员工下班打卡早退的小时数。向下拖动F4单元格右下角的填充柄，将公式复制到单元格F13为止，算出其他员工迟到的分钟数。11.3.2 制作考勤统计表第六步：第七步：向下拖动G4单元格右下角的填充柄，将公式复制到单元格G13为止，算出其他员工早退的小时数。单击H4单元格，输入公式“=IF(D4<$E$1,IF(MINUTE(D4)<=MINUTE($E$1),MINUTE($E$1)-MINUTE(D4),MINUTE($E$1)+60-MINUTE(D4)),0)”，按Enter键确认，算出第一个员工下班打卡早退的分钟数。11.3.2 制作考勤统计表第八步：向下拖动H4单元格右下角的填充柄，将公式复制到单元格H13为止，算出其他员工早退的分钟数，最终的结果如图11-31所示。图11-31最终的统计结果11.3.3批量制作考勤表在使用Excel管理表格过程中，经常需要制作大量结构和格式相同的表格，如每个月的员工考勤表，表中有每一个员工的姓名和每个月中每一天的日期，区别只是每个月中每个员工的考勤情况不同。如果要制作一年的考勤表，就要制作12张表格，这样做既费力效率又低。此时，可以先制作好第一个月的考勤表，然后将其创建为模板，这样在制作后面的每个月的考勤表时，只需使用模板来创建考勤表，即可在新建的表格中自动保留模板中的姓名和日期。11.3.3批量制作考勤表例11-9制作如图11-32所示的员工考勤表模板，表格中的姓名和日期所在的单元格都是锁定的，用户不能对其进行修改。新建考勤表时，通过修改A1中的月份来使A列日期自动更新为指定月份下的日期。具体的操作步骤如下。第一步：建立一个如图11-32所示的考勤表，在A3单元格中输入公式“=DATE(2013,LEFT($A$1,FIND("月",$A$1)-1),ROW()-2)”，按Enter键确定，得到2013年1月份第一天的日期。如图11-32所示。图11-32通过公式得到考勤表的第一个日期11.3.3批量制作考勤表公式解析：本例的思路是，在考勤表标题输入月份后，通过公式在A列中自动得到本月的日期。因此，在公式中，先用FIND函数查找标题中“月”字的位置，位置的数字减1作为LEFT函数中的第二个参数（要提取的字符个数），这样就可以从标题中提取月份的数字。提取的月份数字最后作为DATE函数的第二个参数（即表示“月”的数字），而DATE函数的第一个参数可以指定任何年份。同时，为了在复制公式的过程中得到正确的日数，在公式中通过ROW函数来引用当前单元格的行号，并减2（因为第一个日期的单元格是从第3行开始），作为DATE函数的第三个参数（日期中的日）。11.3.3批量制作考勤表例11-9制作如图11-32所示的员工考勤表模板，表格中的姓名和日期所在的单元格都是锁定的，用户不能对其进行修改。新建考勤表时，通过修改A1中的月份来使A列日期自动更新为指定月份下的日期。具体的操作步骤如下。第二步：第三步：拖动A3单元格右下角的填充柄，复制公式到A33，得到1月份每天的日期。单击工作表中左上角的全选按钮，选中所有单元格，单击“格式”→“单元格格式”菜单命令，打开“单元格格式”对话框。在对话框中选择“保护”选项卡，取消选择“锁定”复选项，如图11-33所示，单击“确定”按钮。图11-33取消选择“锁定”复选项11.3.3批量制作考勤表例11-9制作如图11-32所示的员工考勤表模板，表格中的姓名和日期所在的单元格都是锁定的，用户不能对其进行修改。新建考勤表时，通过修改A1中的月份来使A列日期自动更新为指定月份下的日期。具体的操作步骤如下。第四步：选择B2:K2单元格区域，然后按住Ctrl键的同时，选择A3:A33单元格区域，单击“工具”→“保护”→“保护工作表”菜单命令，打开“保护工作表”对话框，在对话框中设置好保护的项目后，输入保护的密码，如图11-34所示。图11-34设置工作表密码单击“确定”按钮，在弹出的“确认密码”对话框中，再次输入密码，如图11-35所示，单击“确定”按钮。这样所有的姓名和日期就不能修改。图11-35“确认密码”对话框11.3.3批量制作考勤表例11-9制作如图11-32所示的员工考勤表模板，表格中的姓名和日期所在的单元格都是锁定的，用户不能对其进行修改。新建考勤表时，通过修改A1中的月份来使A列日期自动更新为指定月份下的日期。具体的操作步骤如下。第五步：单击工具栏中的“保存”按钮将考勤表保存。接着单击“文件”→“另存为”菜单命令，打开“另存为”对话框，在对话框中的“保存类型”下拉列表中选择“模板”，然后在“文件名”后面的文本框中输入“员工考勤模板”，如图11-36所示。最后单击“保存”按钮，创建员工考勤模板。图11-36创建工作簿模板11.3.3批量制作考勤表例11-9制作如图11-32所示的员工考勤表模板，表格中的姓名和日期所在的单元格都是锁定的，用户不能对其进行修改。新建考勤表时，通过修改A1中的月份来使A列日期自动更新为指定月份下的日期。具体的操作步骤如下。图11-37选择要使用的模板第六步：如果要制作其他月份的员工考勤表，则选择“文件”→“新建”命令，然后在“新建工作簿”窗格中单击“本机上的模板”，在出现的“模板”对话框中选择“员工考勤模板”，如图11-37所示。11.3.3批量制作考勤表例11-9制作如图11-32所示的员工考勤表模板，表格中的姓名和日期所在的单元格都是锁定的，用户不能对其进行修改。新建考勤表时，通过修改A1中的月份来使A列日期自动更新为指定月份下的日期。具体的操作步骤如下。图11-38使用模板新建工作簿第七步：单击“确定”按钮，Excel将新建一个工作簿，在Sheet1工作表中，把标题中的月份数字更改后，A列的日期就会更新为相应的月份，如图11-38所示。11.4.2计算员工销售提成奖金11.4.1员工业绩评定与排名11.4员工绩效与福利管理11.4.3制作员工工资表11.4.4制作员工工资条11.4.1员工业绩评定与排名例11-10打开“力宏公司2013年上半年销售业绩考核.xls”工作簿，在工作表Sheet1中，计算出每个员工的销售金额（销售金额=销售单价×\u38144X售数量），并根据销售量进行业绩评定，销售量大于或等于150的为“优秀”，在100～149之间的为“良好”，在50～99之间的为“一般”，低于50的为“差”。最后对员工的销售金额进行排名。具体操作步骤如下。打开“力宏公司2013年上半年销售业绩考核.xls”工作簿，在工作表Sheet1中，选中G3单元格，在其中输入计算销售金额的公式“=E3*F3”，表示销售单价乘以销售数量。输入正确的公式之后，按Enter键。此时可以看到在目标单元格中显示了计算的结果。第一步：11.4.1员工业绩评定与排名选中G3单元格，并向下拖动其右下角的填充柄，复制公式到最后一个员工销售金额的单元格G12，得到各员工的销售金额，如图11-39所示。第二步：图11-39算出所有员工的销售金额单击H3单元格，输入公式“=IF(F3>=150，"优秀"，IF(F3>=100，"良好"，IF(F3>=50，"一般"，"差")))”，按Enter键确认，根据销售数量对第一个员工的业绩进行评定。第三步：11.4.1员工业绩评定与排名这里也可以用CHOOSE函数来对第一个员工的销售量进行评定。公式为“=CHOOSE(IF(F3>=150,1,IF(F3>=100,2,IF(F3>=50,3,4))),"优秀","良好","一般","差")”；或者用LOOKUP函数进行，公式为“=LOOKUP(F3,{0,50,100,150},{"差","一般","良好","优秀"})。11.4.1员工业绩评定与排名图11-40评定所有员工的业绩拖动H3右下角的填充柄，将公式向下复制到单元格H12，完成对所有员工业绩的评定，如图11-40所示。第四步：单击I3单元格，输入公式“=RANK(G3,$G$3:$G$12)”，按Enter键确认，得到第一个员工的销售金额的排名名次。第五步：11.4.1员工业绩评定与排名图11-41算出所有员工的名次拖动I3右下角的填充柄，将公式向下复制到单元格I12，得到所有员工的名次，如图11-41所示。第六步：11.4.2 计算员工销售提成奖金例11-11力宏公司对员工实行销售绩效考核，并按照不同的比例计提奖金。规定如下：奖金类别分为6个档次，月销售额低于2万元的无奖金分配；月销售额为2万～4万的计提1.5%的奖金；月销售额为4万～6万的计提2.0%的奖金；月销售额为6万～8万的计提3.0%的奖金；月销售额为8万～10万的计提4.0%的奖金；月销售额为10万元以上的计提5.0%的奖金；当月员工个人奖金最高为3000元；实发奖金额四舍五入到元。在“力宏公司2013年7月份销售人员奖金统计表.xls”工作簿的Sheet1工作表中，计算每位员工的奖金金额。11.4.2 计算员工销售提成奖金打开“力宏公司2013年7月份销售人员奖金统计表.xls”工作簿，在Sheet2工作表中，按照公司的奖金规定方法制作如图11-42所示的奖金评定比例，并把Sheet2工作表重命名为“奖金评定比例”。在Sheet1工作表中，选中F3单元格，输入公式“=VLOOKUP(E3,奖金评定比例!$A$3:$C$8,3)”，按Enter键确认，得到第一个员工的奖金比例。第一步：第三步：第二步：拖动F3单元格右下角的填充柄，复制公式到F12单元格，得到所有员工的奖金比例，如图11-43所示。图11-42 制作“奖金评定比例”表图11-43 算出所有员工的奖金比例11.4.2 计算员工销售提成奖金选中G3单元格，输入公式“=IF(ROUND(E3*F3,0)<=3000,ROUND(E3*F3,0),3000)”，按Enter键确认，得到第一个员工的奖金金额。拖动G3单元格右下角的填充柄，复制公式到G12单元格，得到所有员工的奖金金额，如图11-44所示。第四步：第五步：图11-44 算出所有员工的奖金金额11.4.2 计算员工销售提成奖金公式解析：公式“=IF(ROUND(E3*F3,0)<=3000,ROUND(E3*F3,0),3000)”是指当测试条件“ROUND(E3*F3,0)”为真时。返回“ROUND(E3*F3,0)”的运算结果。否则返回“3000”。“E3*F3”是需要进行四舍五入的数值。“0”意味着四舍五入到最接近的整数。“ROUND(E3*F3,0)”的意思就是对E3单元格里数值乘以F3单元格里数值得到的结果进行四舍五入处理。最后保留最接近该结果的整数。本案例中，G3单元格的值四舍五入后的值为“989”。小于3000，因此逻辑值为真，所以返回“ROUND(E3*F3,0)”的运算结果，即“989”。11.4.3 制作员工工资表核定薪酬是人力资源部每个月必须完成的工作，其中最基本的工作就是制作员工的月工资表。月工资表一般包括基本工资、福利津贴、绩效奖金、加班费、各种扣钱项目及代扣代缴保险个税等内容。11.4.3 制作员工工资表个人所得税是工资表上的一个重要计算项目。根据我国最新的个税计算方法，目前，在计算个税时，使用的是3%～45%的7级超额累进税率，如图11-45所示。而个人所得税的计算公式为：应纳税额=（每月收入额−3500）*适用税率−速算扣除数。每月取得工资收入后，先减去个人承担的基本养老保险金、医疗保险金、失业保险金，以及按省级政府规定标准缴纳的住房公积金，再减去费用扣除额3500元/月，为应纳税所得额，按3%～45%的7级超额累进税率计算缴纳个人所得税。级数应纳税所得额税率(%)速算扣除数1不超过1500元的302超1500元至4500元的部分101053超过4500元至9000元的部分205554超过9000元至35000元的部分2510055超过35000元至55000元的部分3027556超过55000元至80000元的部分3555057超过80000元的部分4513505图11-45 个人所得税的税率表11.4.3 制作员工工资表例11-12打开“力宏公司2013年7月份员工工资表.xls”工作簿，在Sheet1工作表中，计算每位员工的应发工资、应纳税所得额、个税税率、速算扣除数、应缴纳税和实发工资。具体操作如下。1打开“力宏公司2013年7月份员工工资表.xls”工作簿，在Sheet1工作表中，选择I3单元格，输入公式“=SUM(E3:H3)”，按Enter键确认，计算第一名员工的实发工资。拖动I3单元格右下角的填充柄，将公式复制到单元格I12为止，算出所有员工的应发工资。2选择K3单元格，输入公式“=I3-J3-3500”，按Enter键确认，计算第一名员工的应纳税所得额。拖动K3单元格右下角的填充柄，将公式复制到单元格K12为止，算出所有员工的应纳税所得额，如图11-46所示。图11-46计算员工的应纳税所得额11.4.3 制作员工工资表例11-12打开“力宏公司2013年7月份员工工资表.xls”工作簿，在Sheet1工作表中，计算每位员工的应发工资、应纳税所得额、个税税率、速算扣除数、应缴纳税和实发工资。具体操作如下。图11-47制作的个税表3在Sheet2工作表中，制作如图11-47所示的个税表，并把工作表名改为“个税表”。11.4.3 制作员工工资表例11-12打开“力宏公司2013年7月份员工工资表.xls”工作簿，在Sheet1工作表中，计算每位员工的应发工资、应纳税所得额、个税税率、速算扣除数、应缴纳税和实发工资。具体操作如下。4单击Sheet1 中的L3单元格，输入公式“=IF(K3<=0,0,VLOOKUP(K3,个税表!$D$2:$F$8,2,TRUE))”，按Enter键确认，计算第一名员工应缴纳个税的税率。拖动L3单元格右下角的填充柄，将公式复制到单元格L12为止，算出所有员工的应纳税税率。5单击Sheet1 中的M3单元格，输入公式“=IF(K3<=0,0,VLOOKUP(K3,个税表!$D$2:$F$8,3,TRUE))”，按Enter键确认，计算第一名员工的速算扣除数。拖动M3单元格右下角的填充柄，将公式复制到单元格M12为止，算出所有员工的速算扣除数。11.4.3 制作员工工资表例11-12打开“力宏公司2013年7月份员工工资表.xls”工作簿，在Sheet1工作表中，计算每位员工的应发工资、应纳税所得额、个税税率、速算扣除数、应缴纳税和实发工资。具体操作如下。6单击Sheet1中的N3单元格，输入公式“=K3*L3-M3”，按Enter键确认，计算第一名员工的应纳税额，拖动N3单元格右下角的填充柄，将公式复制到单元格N12为止，算出所有员工的应纳税额，如图11-48所示。图11-48算出所有员工的应纳税额11.4.3 制作员工工资表例11-12打开“力宏公司2013年7月份员工工资表.xls”工作簿，在Sheet1工作表中，计算每位员工的应发工资、应纳税所得额、个税税率、速算扣除数、应缴纳税和实发工资。具体操作如下。7单击Sheet1中的O3单元格，输入公式“=I3-J3-N3”，按Enter键确认，计算第一名员工的实发工资，拖动O3单元格右下角的填充柄，将公式复制到单元格O12为止，算出所有员工的实发工资，如图11-49所示。图11-49计算实发工资11.4.4 制作员工工资条例11-13打开“2013年7月白马公司员工工资表.xls”工作薄，根据“工资表”工作表，制作员工的工资条。制作工资条的常用方法主要有两种：（1）通过排序方法制作工资条第一步：在“2013年7月白马公司员工工资表.xls”工作薄中，复制“工资表”工作表到最后面，并命名为“排序法制作工资条”。第二步：在“排序法制作工资条”工作表中，选中A列，单击“插入”→“列”菜单命令，在A列的前面插入新的一列。11.4.4 制作员工工资条第三步：在A2:A11单元格区域，输入数字1～10。在A12单元格中输入1.5，单击并拖动A12单元格右下角的填充柄到A20，此时，A12:A20单元格区域中的数据都是1.5，因为选择一个单元格并拖动其右下角的填充柄时，Excel默认为复制数据，单击数据右下角的下