excel教程__人力资源篇

第第 2篇篇 人力资源篇人力资源篇 项目 4 制作员工人事档案管理表2.1 项目 5 制作员工培训管理表2.2 项目 6 制作员工工资管理表2.3 2.1 项目 4 制作员工人事档案管理表 2.1.1 项目背景 员工人事档案的整理分类、汇总、分 析、查询等是一件比较复杂的工作。 使用 Excel可以实现人事管理的自动化 ，提高人事档案管理的条理化。 本项目通过制作 “员工人事档案管理 表 ”，来介绍 Excel软件在人力资源管理中 的应用。 2.1.2 项目效果 图 2-1 员工人事档案筛选效果图 图 2-2 公司各部门人员分配效果图 2.1.3 知识与技能 工作簿的创建 工作表重命名 导入外部数据 数据链接 数据的筛选 COUNTIF函数的使用 创建图表 2.1.4 解决方案 任务 1 创建工作簿，重命名工作表 （ 1）启动 Excel 2003，新建一个空白 工作簿。 （ 2）将创建的工作薄以 “员工人事档 案管理表 ”为名保存。 （ 3）将 “员工人事档案管理表 ”工作 簿中的 Sheet1工作表重命名为 “员工 档案信息 ”。 任务 2 导入 “员工信息 ” 将行政篇中导出的 “员工信息 ”数据导 入到当前的文件中，轻松获取外部人事档 案信息。 （ 1）选中 “员工人事档案管理表 ”工 作簿中的 “员工档案信息 ”工作表。 （ 2）选择 【 数据 】 菜单中 【 导入外 部数据 】 命令的子命令 【 导入数据 】 ，打开 【 选取数据源 】 对话框。 （ 3）选择数据源文件 “员工信息 .prn”， （ 4）从对话框的 “原始数据类型 ”中 选择 “固定长度 ”作为最合适的文件类 型，在 “导入起始行 ”文本框中保持默 认值 “1”，在文件原始格式下拉列表 中选择 “936 ：简体中文（ GB2312） ” 。 （ 5）按文本导入向导进行操作。在 “ 数据预览 ”区内设置字段宽度，也就 是列间隔；设置每列的数据类型。 （ 6）单击 【 确定 】 按钮，完成数据 导入。 任务 3 创建 “员工个人详细资料 ”链接 （ 1）链接到已有文件。 （ 2）链接到新建文件。 任务 4 查阅 “员工个人详细资料 ” 当需要查阅某个员工的个人详细资料 时，我们可通过激活超链接进行访问。 任务 5 复制 “员工档案信息 ”工作表 （ 1）选中 “员工档案信息 ”工作表。 （ 2）将该工作表复制三份，并分别 重命名为 “女员工档案信息 ”、 “入职 时间最早的 5名员工信息 ”、 “2005年 - 2007年入职的员工 ”。 任务 6 通过数据筛选查询女员工档案 信息 任务 7 通过数据筛选查询入职时间最 早的 5名员工信息 任务 8 通过数据筛选查询 2005年 -2007 年入职的员工 任务 9 统计公司各部门员工人数 （ 1）将 Sheet2工作表重命名为 “公司 各部门员工人数 ”。 （ 2）建立表格框架。 （ 3）选中 “公司各部门员工人数 ”工 作表的 B3单元格。 （ 4）输入公式 “=COUNTIF(员工档 案信息 !$D$2:$D$26,“行政部 “)”，按 【 Enter】 键，在 B3中显示 “行政部 ” 人数。 （ 5）同样地，统计出其他部门的人 数。 任务 10 创建公司各部门人员比例图 （ 1）选中 “公司各部门员工人数 ”工 作表中的 A2:B9单元格区域。 （ 2）创建 “三位饼图 ”。 （ 3）设置图表的标题格式。 2.1.5 项目小结 本项目通过制作 “员工人事档案管理 表 ”，主要介绍了工作簿的创建、工作表重 命名、导入外部数据创建员工档案信息表 、利用超链接查阅员工个人详细资料、使 用筛选查询员工档案信息、使用 COUNTIF 函数进行各部门人数统计。 在此基础上，利用统计数据创建各部 门人员比例图表。 2.1.6 拓展项目 1.通过数据筛选查询 80后的员工 档案信息。 2.通过数据筛选查询 “ 市场部 ” 的男员工档案信息。 3.统计公司员工学历情况并制 作比例图。 2.2 项目 5 制作员工培训管理表 2.2.1 项目背景 企业在对员工进行培训后，通常要根 据培训项目进行相应的考核。 人力资源部需要针对考核成绩制定相 应的培训成绩管理表。 本项目通过制作 “员工培训管理表 ”， 来介绍 Excel软件在培训管理方面的应用。 2.2.2 项目效果 图 2-3 员工培训管理表 图 2-4 员工培训考核数据透视图 2.2.3 知识与技能 工作簿的创建 工作表重命名 数据有效性 函数 AVERAGE、 SUM、 COUNTIF和 RANK的应用 美化工作表 数据透视表 数据透视图 2.2.4 解决方案 任务 1 创建工作簿，重命名工作表 （ 1）启动 Excel 2003，新建一个空白 工作簿。 （ 2）将创建的工作薄以 “员工培训管 理表 ”为名保存。 （ 3）将 “员工人事档案管理表 ”工作 簿中的 Sheet1工作表重命名为 “员工 培训成绩表 ”。 任务 2 创建 “员工培训成绩表 ”框架 （ 1）在 “员工培训成绩表 ”中输入工 作表标题。 （ 2）输入表格标题字段。 任务 3 输入 “序号 ” 利用填充句柄，输入序号。 任务 4 输入 “姓名 ”和 “部门 ” （ 1）输入员工 “姓名 ”。 （ 2）输入员工 “部门 ”。 任务 5 设置各培训项目成绩的数据有 效性 在输入数据的过程中，为尽量减小数 据输入的错误，提高数据的有效性，可对 要输入数据的单元格区域设置有效性规则 ，使输入的数据在规定的范围之内。 即输入的成绩范围在 0 100之间。 任务 6 统计 “平均成绩 ”和总成绩 任务 7 利用 RANK函数排列员工培训 成绩的名次 （ 1）选中 I3单元格。 （ 2）输入公式 “=RANK(H3,$H$3:$H$21)”按 【 Enter】 键确认。 （ 3）选中 I3单元格，拖动填充句柄 至 I21单元格，将公式复制到 I4:H21 单元格区域中，排列出所有名次。 任务 8 统计各培训项目的平均成绩 （ 1）在 A22单元格中输入 “各培训项 目平均成绩 ”。 （ 2）选中 D22单元格，输入公式 “=AVERAGE(D3:D21)”，按 【 Enter 】 键确认。 （ 5）选中 D22单元格，拖动填充句柄 至 F22单元格，将公式复制到 E22:F22 单元格区域中。 任务 9 利用 COUNTIF函数统计考核 不合格的员工人数 这里，假定员工的所有项目平均成绩 低于 80分，则认定为不合格。 （ 1）在 G22单元格中输入 “不合格人 数 ”。 （ 2）选中 I22单元格，输入公式 “=COUNTIF(G3:G21,“2000,IF(M3-2000)500,(M3- 2000)=2000),(M3-2000)*0.1-25),0) 按 【 Enter】 键确认 ,可计算出相应 的个人所得税。 选中 N3单元格，用鼠标拖动其填充 句柄至 N26单元格，将公式复制到 N3:N26 单元格区域中，可计算出所有员工的个人 所得税。 （ 13）计算 “实发工资 ”。 任务 7 格式化 “员工工资明细表 ” （ 1）设置工作表的标题格式。 （ 2）将表中所有的数据项格式设置 为 “会计专用 ”格式，保留 2位小数， 货币符号为 “无 ”。 （ 3）为表格设置内细外粗的蓝色边 框。 （ 4）为 “应发工资 ”和 “税前工资 ”两 列的数据区域添加 “淡蓝 ”色底纹，为 “实发工资 ”列数据区域添加 “天蓝 ”色 底纹。 任务 8 制作 “工资条 ” （ 1）将 Sheet3工作表重命名为 “工资 条 ”。 （ 2）选中 “工资条 ”工作表的 A1单元 格。 （ 3）在 “编辑栏 ”中输入下面的公式 ，然后按 【 Enter】 键确认。 =IF(MOD(ROW(),3)=0,“,IF(MOD( ROW(),3)=1, 员工工资明细表 !A$2,INDEX(员工工资明细表 !$A:$O,INT(ROW()- 1)/3)+3,COLUMN() （ 4）选中 A1单元格，用鼠标拖动其 填充句柄至 O1单元格，将公式复制 到 B1:O1单元格区域中。 （ 5）选中 A1:O1单元格区域，用鼠 标拖动其填充句柄至 O74单元格，将 公式复制到 A2:O74单元格区域中。 （ 6）适当调整单元格的列宽，使单 元格的内容能完整的显示出来。 （ 7）美化 “工资条 ”工作表。 2.3.5 项目小结 本项目通过制作 “员工工资管理表 ”， 主要介绍了工作簿的创建、工作表重命名 、外部数据的导入，使用函数 Year、 Datedif、 Today、 Round、 Sum等