Excel(2007版)在人力资源管理中的高级应用（下册）舒雄 编著中国青年出版社图书在版编目（CIP）数据Excel（2007 版）在人力资源管理中高级应用/舒雄等编著．—北京：中国 青年出版社，2009.9ISBN 978-7-80218-208-0Ⅰ．用… Ⅱ．李… Ⅲ．电子表格系统，Excel Ⅳ．TP391.13中国版本图书馆 CIP 数据核字（2007）第 012833 号策划编辑 刘全 封面设计 灵动设计 责任编辑 钟梅娟 责任校对 李珊中国青年出版社出 版 发 行社 址 北京市阜成路 8 号 邮 编 100830 (010)68768548网 址 经 销 新华书店发行部(010)68371900(010)88530478(传真)(010)68768541(010)68767294(传真)零售店读者服务部北京青年文苑(010)68371105(010)62529336编辑部(010)68193075承 印 廊坊时嘉印刷有限公司版 次 2009 年 9月第 1 版 2009 年 9月第 1 次印刷 规 格 880×1230 开 本 1/32印 张 8 字 数 246 千字 书 号 ISBN 978-7-80218-208-0定 价 120.00 元（全套共 5 册）本书如有印装质量问题，可与发行部联系调换前 言Excel 集数据采集、编辑、分析于一身，具有强大的数据处理和图 表分析功能，在人力资源管理工作中得到广泛应用。
本书根据人力资源管理工作的实际需要，由浅入深、循序渐进地讲解了人力资源管理工作必需掌握的 Excel 软件技能，并提供了多种 实用模板通过学习本书，读者能够根据实际需要设计出工作表和数 据图表，解决企业人力资源管理工作中的繁杂问题本书共分 11 章，主要内容如下第 1 章讲解 Excel 2007 的基本操作，包括工作簿、工作表和单元 格的基本操作，数据的输入与修改、批注的使用、工作表与工作簿的保护等内容第 2 章～第 11 章讲解了员工档案管理系统、员工离职程 序表、员工学历分析图、员工出勤统计表、员工在职培训管理系统、员工加班记录表、员工年度考核系统、员工业绩奖金评定系统、员工工资管理系统和人力资源薪资调查系统等制作方法在第 2 章～第 11 章中还安排了与该章内容相似的其他常用模板，读者可以举一反三， 全面掌握 Excel 在人力资源管理工作中的应用本书特点：图解方式、版式新颖，实例经典、内容实用，语言简 练、通俗易懂，步骤详细、一看即会请登录网址 尚 IT 生活秀”文件夹中选择本书实例文件和其他模板文件进行下载 本书由舒雄执笔，另外陈豫龙、郭彦芳、李鹏杰、张斯予、马洪亮、张婷、刘方源、吴平和刘江斌也参与了部分编写工作。
如果您对本书有何意见和建议，请通过 yuanxun.007@ 与 我们联系编 者2009 年 5 月i下册目录第 5 章 员工出勤统计表................................................................................. 1055.1 创建员工出勤统计表 ................................................................................. 1055.1.1 创建并格式化表格 ......................................................................... 1055.1.2 设置数据有效性 ............................................................................. 1065.1.3 计算请假天数 ................................................................................. 1075.1.4 计算应扣工资 ................................................................................. 1085.2 统计月度出勤 ............................................................................................. 1095.2.1 创建数据透视表 ............................................................................. 1095.2.2 利用数据透视表查看员工的出勤...................................................1115.2.3 修改数据透视表布局 ..................................................................... 1125.2.4 编辑数据透视表的统计方式.......................................................... 1135.2.5 设置数据透视表中的值显示格式.................................................. 1145.2.6 更新数据源 ..................................................................................... 1155.2.7 套用表格样式 ................................................................................. 1165.2.8 删除数据透视表 ............................................................................. 1165.3 创建数据透视图 ......................................................................................... 1175.4 其他应用实例 ............................................................................................. 119第 6 章 员工在职培训管理系统 ................................................................... 1206.1 创建在职培训日程表 ................................................................................. 1206.1.1 建立基本表格 ................................................................................. 1206.1.2 设置时间格式 ................................................................................. 1206.1.3 自动填充数据 ................................................................................. 1216.1.4 建立单元格之间的链接 ................................................................. 1226.2 拼音字段的应用 ......................................................................................... 1236.2.1 添加拼音 ......................................................................................... 1236.2.2 显示拼音 ......................................................................................... 1246.2.3 设置拼音字段 ................................................................................. 1256.3 创建在职培训成绩表 ................................................................................. 1266.3.1 建立基本表格 ................................................................................. 1276.3.2 插入图片 ......................................................................................... 1276.3.3 冻结窗口 ......................................................................................... 1286.4 统计培训成绩 ............................................................................................. 1296.4.1 计算平均成绩 ................................................................................. 1296.4.2 统计成绩排名 ................................................................................. 1306.5 公式中单元格的引用 ................................................................................. 1316.5.1 相对引用 ......................................................................................... 1326.5.2 绝对引用 ......................................................................................... 1336.5.3 混合引用 ......................................................................................... 133目 录6.5.4 引用其他工作表的单元格.............................................................. 1346.6 在职培训成绩分析 ..................................................................................... 1356.6.1 建立基本表格 ................................................................................. 1356.6.2 应用自动换行功能 ......................................................................... 1356.6.3 为表格添加边框 ............................................................................. 1366.6.4 统计总人数 ..................................................................................... 1376.6.5 统计男女员工的人数 ..................................................................... 1396.6.6 统计最高分 ..................................................................................... 1406.6.7 统计最低分 ..................................................................................... 1406.6.8 分别统计男、女员工的平均分...................................................... 1416.6.9 统计各分数段的人数 ..................................................................... 1426.6.10 计算各分数段人数占总人数的比例............................................ 1466.7 显示所有公式 ............................................................................................. 1476.8 常见公式错误及纠正办法 ......................................................................... 1486.9 其他应用实例 ............................................................................................. 151第 7 章 员工加班记录表................................................................................. 1527.1 创建员工加班记录表 ................................................................................. 1527.2 使用公式输入加班数据 ............................................................................. 1537.2.1 日期的输入 ..................................................................................... 1537.2.2 加班时间的计算 ............................................................................. 1547.2.3 计算加班费 ..................................................................................... 1557.3 使用加班记录表 ......................................................................................... 1557.3.1 完善加班记录表 ............................................................................. 1567.3.2 审核公式 ......................................................................................... 1567.3.3 错误检查 ......................................................................................... 1587.3.4 验证公式计算结果 ......................................................................... 1597.4 其他应用实例 ............................................................................................. 161第 8 章 员工年度考核系统 ............................................................................ 1628.1 创建季度考核表 ......................................................................................... 1628.1.1 创建出勤统计表 ............................................................................. 1628.1.2 设置小数位数 ................................................................................. 1638.1.3 创建绩效表 ..................................................................................... 163vii8.1.4 使用函数 INDEX 引用数据 ........................................................... 1648.1.5 计算总成绩 ..................................................................................... 1668.2 创建年度考核表 ......................................................................................... 1678.2.1 建立基本表格 ................................................................................. 1678.2.2 利用合并计算功能计算成绩.......................................................... 1678.2.3 计算总成绩 ..................................................................................... 1698.2.4 突出显示特定成绩 ......................................................................... 1708.2.5 计算成绩排名 ................................................................................. 1718.3 利用排序功能查看年度考核表.................................................................. 1728.3.1 简单排序 ......................................................................................... 1728.3.2 复杂排序 ......................................................................................... 1738.3.3 自定义排序 ..................................................................................... 1748.4 利用筛选功能查看年度考核表.................................................................. 1768.4.1 自动筛选 ......................................................................................... 1768.4.2 自定义筛选 ..................................................................................... 1768.4.3 高级筛选 ......................................................................................... 1778.5 其他应用实例 ............................................................................................. 180第 9 章 员工业绩奖金评定系统 ................................................................... 1819.1 创建奖金规范表 ......................................................................................... 1819.2 创建奖金评定表 ......................................................................................... 1819.2.1 建立基本表格 ................................................................................. 1819.2.2 计算奖金比例 ................................................................................. 1839.2.3 计算基本业绩奖金 ......................................................................... 1849.2.4 计算本月累计销售额 ..................................................................... 1859.2.5 计算累计销售奖金 ......................................................................... 1859.2.6 计算员工总奖金 ............................................................................. 1869.3 评估员工业绩 ............................................................................................. 1879.3.1 使用图表查看员工业绩 ................................................................. 1879.3.2 添加趋势线 ..................................................................................... 1889.4 创建奖金查询表 ......................................................................................... 1899.5 其他应用实例 ............................................................................................. 191目 录第 10 章 员工工资管理系统.......................................................................... 19210.1 创建员工工资表 ....................................................................................... 19210.1.1 复制工作表 ................................................................................. 19210.1.2 制作岗位工资表 ......................................................................... 19310.1.3 制作员工奖金表 ......................................................................... 19410.1.4 制作员工补贴表 ......................................................................... 19510.1.5 制作员工工资明细表.................................................................. 19710.1.6 计算应发工资额 ......................................................................... 19910.1.7 计算扣除保险费 ......................................................................... 19910.1.8 计算个人所得税 ......................................................................... 20010.1.9 计算实发工资额 ......................................................................... 20210.2 工资额汇总 ............................................................................................... 20310.2.1 分类汇总工资总额...................................................................... 20310.2.2 分类汇总工资平均值.................................................................. 20510.2.3 删除分类汇总 ............................................................................. 20610.3 制作工资条 ............................................................................................... 20710.4 预览打印效果 ........................................................................................... 20810.5 页面设置 ................................................................................................... 20910.5.1 设置页边距及页眉页脚边距...................................................... 20910.5.2 调整打印比例 ............................................................................. 21010.5.3 设置打印方向 ............................................................................. 21010.5.4 打印网格线 ................................................................................. 21110.5.5 设置打印顺序 ............................................................................. 21210.5.6 设计页眉页脚 ............................................................................. 21210.6 打印范围设置 ........................................................................................... 21410.6.1 调整分页符位置 ......................................................................... 21410.6.2 删除分页符 ................................................................................. 21510.6.3 设置打印页数范围和份数.......................................................... 21510.6.4 一次打印多个工作簿.................................................................. 21610.7 其他应用实例 ........................................................................................... 217第 11 章 人力资源薪资调查系统................................................................. 21811.1 设计调查问卷的内容................................................................................ 218ix11.2 创建薪资调查问卷 ................................................................................... 21811.2.1 制作问卷标题.............................................................................. 21911.2.2 制作说明文字.............................................................................. 22011.2.3 添加分组框和选项按钮.............................................................. 22111.2.4 添加分组框和复选框.................................................................. 22411.2.5 添加标签项和组合框.................................................................. 22511.3 测试调查问卷 ........................................................................................... 22711.4 制作调查结果统计表................................................................................ 22811.4.1 制作基本统计表.......................................................................... 22811.4.2 添加批注 ..................................................................................... 22911.5 创建统计表与调查问卷之间的联系........................................................ 22911.5.1 为单选项按钮创建链接.............................................................. 22911.5.2 为复选框创建链接...................................................................... 23111.5.3 为组合框创建链接...................................................................... 23211.6 实现调查问卷自动记录............................................................................ 23311.6.1 添加自动记录子程序.................................................................. 23411.6.2 添加自动记录按钮...................................................................... 23511.6.3 测试调查问卷.............................................................................. 23611.7 封装工作簿 ............................................................................................... 23811.7.1 保护工作表.................................................................................. 23811.7.2 隐藏工作表.................................................................................. 23811.8 统计调查结果 ........................................................................................... 23911.8.1 创建调查结果统计表.................................................................. 23911.8.2 统计单项选择题.......................................................................... 24011.8.3 统计多项选择题.......................................................................... 24111.8.4 统计下拉列表框选择题.............................................................. 24211.9 其他应用实例 ........................................................................................... 243第 5章 员工出勤统计表人力资源管理者每天都要对员工的出勤情况进行统计，因此创建一份 易于操作的出勤统计表是十分必要的。
本章介绍制作员工出勤统计表的方法5.1 创建员工出勤统计表在创建员工出勤统计表之前，一定要清楚工作表应包含的数据及格 式下面介绍创建员工出勤统计表的过程5.1.1 创建并格式化表格员工出勤统计表应包含请假日期、请假时间、请假类别以及员工基本 信息等内容建立基本表格的具体操作步骤如下1 新建工作簿，以“员工出勤统计表”为名称保存107图 5.12 如图 5.1 所示，重命名工作表 Sheet1为“出勤统计表”，在 工作表中输入统计表的标题和表头，并进行美化，设置表格标题单 元格底色3 选择单元格区域 C3:C23 和 I3:I23，单击【开始】选项卡，在【数字】组中单击按钮4 在弹出【设置单元格格式】对话框中，单击【数字】标签，在【分类】列表 框中选择【货币】选项，在【负数】列表框中选择【￥1.234】选项第 5 章 员工出勤统计表5.1.2 设置数据有效性公司规定员工请假类别只有病假、事假、婚假和产假 4 种，可以利用 数据有效性定义单元格序列，使用下拉列表进行数据输入具体操作步骤 如图 5.2～图 5.7 所示1 在工作表单元格区域 L2:L5 中输入“病假”、“事假”、“婚假”、“产假” 数据。
2 选择 F 列，单击【数据】选项卡，在【数据工具】组中单击【数据有效性】按钮3 弹出【数据有效性】对话 框，单击【设置】标签，在【允 许】下拉列表框中选择【序列】 选项4 在【来源】文本框中输入 “=$L$2:$L$5”图 5.25 单击【输入信息】标签，在【输入信息】文本框中输入“请输入请假类别”6 单击【出错警告】标签， 在【标题】文本框中输入“输 入错误”，在【错误信息】文本 框中输入“请使用下拉列表输 入”，单击【确定】按钮图 5.37 单击选择单元格 F2，出现提示信息“请输入请假类别”，在单元格右侧添加下箭头图 5.4图 5.58 在单元格中，单击其右边的下三角按钮，选择请假类型的选项图 5.69 如果输入了非数据有效性定义的数据，会弹出【输入错误】对话框图 5.75.1.3 计算请假天数10 单击【重试】按钮，“探亲假”选择显示，可进行修改；单击【取消】 按钮，重新输入在计算员工因请假而扣除工资时，需要按照请假天数来计算具体计算的操作步骤如图 5.8～图 5.10 所示1 根据实际出勤情况，在表格中输入出勤统计数据图 5.82 请假时间是以“小时为单位记录的，而每天的工作时间是 8 小时，因此在计 算请假天数时，应以请假时间除以 8。
在 H3 单元格中 输入公式“=G3/8”图 5.93 按 Enter 键，得到员工“江思情”请假天数为 3，向下复制公式到单元格 H23，得到其余员工的请假 天数图 5.105.1.4 计算应扣工资公司规定员工请事假扣除当天全额工资，请病假扣除当天工资的一 半，请婚嫁和产假不扣工资计算应扣工资的具体操作步骤如图 5.11 和图5.12 所示1 在单元格 I3 中输入函数“=IF(F3="病假"，C3/30*H3*0.5 ， IF(F3=" 事假"，C3/30*H3，0))图 5.11109第 5 章 员工出勤统计表函数 IF 的功能是根据对指定的条件计算结果为 TRUE（真）或 FALSE（假），返回不同的结果格式：IF(logical_test,value_if_true,value_if_false) 参数：logical_test（逻辑表达式）表示计算结果为 TRUE（真）或 FALSE（假）的任意值或表达式，此参数可使用任何比较运算符；value_if_true（为真时的值）表示 logical_test 为 TRUE 时返回的值，value_if_true 可以 是其他公式；value_if_false（为假时的值）是 logical_test 为 FALSE 时返 回的值，value_if_false 可以是其他公式。
2 按 Enter 键。
