人力资源知识_人力资源管理篇

Excel人力资源篇 2 1项目4制作员工人事档案管理表 2 1 1项目背景员工人事档案的整理分类 汇总 分析 查询等是一件比较复杂的工作 使用Excel可以实现人事管理的自动化 提高人事档案管理的条理化 本项目通过制作 员工人事档案管理表 来介绍Excel软件在人力资源管理中的应用 2 1 2项目效果 图2 1员工人事档案筛选效果图 图2 2公司各部门人员分配效果图 2 1 3知识与技能 工作簿的创建 工作表重命名 导入外部数据 数据链接 数据的筛选 COUNTIF函数的使用 创建图表 2 1 4解决方案任务1创建工作簿 重命名工作表 1 启动Excel2003 新建一个空白工作簿 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 启动Excel2003 新建一个空白工作簿 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 80 3 按 Enter 键确认 任务10设置单元格数据格式将 平均成绩 和 各培训项目平均成绩 的数据保留为1位小数 任务11美化员工培训成绩表 1 设置工作表标题格式 2 合并单元格 3 将表格中所有数据区域的内容设置为居中对齐 4 适当调整表格的行高和列宽 能显示表格内容 5 为表格设置边框 任务12制作员工培训考核数据透视图 1 选中 员工培训成绩表 2 选择 数据 菜单中的 数据透视表和数据透视图 命令 打开 数据透视表和数据透视图向导对话框 按提示进行操作 3 将 姓名 作为 行 字段 将 计算机技能 作为 数据 项 4 选择 汇总方式 为 最大值 5 单击 完成 按钮 系统将在工作簿中插入一张工作表Sheet4和一张工作表Chart1 在Sheet4中生成数据透视表 将Sheet4工作表重命名为 员工培训成绩数据透视表 在Chart1中生成数据透视图 将Chart1工作命名为 员工培训考核数据透视图 2 2 5项目小结本项目通过制作 员工培训管理表 主要介绍了工作簿的创建 工作表重命名 数据有效性设置 函数AVERAGE SUM COUNTIF和RANK的应用 美化工作表等 在此基础上 利用培训成绩表中的数据 通过数据透视表和数据透视图对员工培训成绩进行汇总 分析 使表格中的数据更为直观 生动 2 2 6拓展项目1 按培训成绩名次进行升序排列 2 制作各培训项目平均成绩比较图 3 制作公司各部门培训成绩数据透视图 2 3项目6制作员工工资管理表 2 3 1项目背景制作工资表示通常需要综合大量的数据 如基本工资 绩效工资 补贴 扣款项等 如果用手工做账 不仅工作量非常大 而且还往往容易出错 本项目通过制作 员工工资管理表 来介绍Excel软件在工资管理方面的应用 2 3 2项目效果 图2 5员工工资明细表效果图 图2 6员工工资条效果图 2 3 3知识与技能 工作簿的创建 工作表重命名 导入外部数据 函数Year Round Vlookup IF INDEX ROW COLUMN INT的使用 公式的使用 数据透视表 数据透视图 2 3 4解决方案任务1创建工作簿 重命名工作表 1 启动Excel2003 新建一个空白工作簿 2 将创建的工作薄以 员工工资管理表 为名保存 3 将 员工人事档案管理表 工作簿中的Sheet1工作表重命名为 工资基础信息表 任务2导入 员工信息 将行政篇中导出的 员工信息 数据导入到当前工作表中 作为员工 工资基础信息表 的数据 任务3创建 工资基础信息表 1 选中 工资基础信息表 工作表 2 删除 身份证号码 学历 职称 性别 和 出生日期 列的数据 图2 60删除数据后的工作表 3 分别在E1 F1 G1单元格中输入标题字段名称 基本工资 绩效工资 和 工龄工资 4 输入 基本工资数据 5 计算 绩效工资 这里 绩效工资 基本工资 50 6 计算 工龄工资 这里 如果 工龄 超过10年 则工龄工资为500元 否则 按每年50元计算 任务4创建 加班费结算表 1 复制 工资基础信息表 将复制后的工作表重命名为 加班费结算表 2 删除 入职时间 绩效工资 和 工龄工资 列 3 在E1 F1单元格中分别输入标题 加班时间 和 加班费 4 输入加班时间 5 计算机加班费 加班费的计算方法为 加班费 基本工资 30 8 1 5 加班时间 任务5创建 考勤扣款结算表 1 复制 工资基础信息表 将复制后的工作表重命名为 考勤扣款结算表 2 删除 入职时间 绩效工资 和 工龄工资 列 3 在E1 K1单元格中分别输入标题 迟到 迟到扣款 病假 病假扣款 事假 事假扣款 和 扣款合计 4 输入 迟到 病假 事假 的数据 5 计算 迟到扣款 这里 假设每迟到一次扣款为50元 6 计算 病假扣款 这里 假设每病假一天扣款为当日工资收入的50 即 病假扣款 基本工资 30 0 5 病假天数 7 计算 事假扣款 这里 假设每事假一天扣款为当日工资收入 即 事假扣款 基本工资 30 事假天数 8 计算 扣款合计 任务6创建 员工工资明细表 1 将Sheet2工作表重命名为 员工工资明细表 2 参创建员工工资明细表的框架 3 填充 编号 姓名 和 部门 数据 4 导入 基本工资 绩效工资 工龄工资 和 加班费 数据 选中D3单元格 选择 插入 菜单中的 函数 命令 从函数列表中选择 VLOOKUP 函数 打开 函数参数 对话框 设置参数 单击 确定 按钮 导入相应的 基本工资 数据 选中D3单元格 用鼠标拖动其填充句柄至D26单元格 将公式复制到D3 D26单元格区域中 可导入所有员工的基本工资 5 同样地方式 分别导入 绩效工资 工龄工资 和 加班费 数据 6 导入 加班费 数据 7 计算 应发工资 8 填充 养老保险 数据 这里 养老保险的数据为个人缴纳部分 计算方法为 养老保险 基本工资 绩效工资 8 9 填充 医疗保险 数据 这里 医疗保险的数据为个人缴纳部分 计算方法为 医疗保险 基本工资 绩效工资 2 10 填充 失业保险 数据 这里 失业保险的数据为个人缴纳部分 计算方法为 失业保险 基本工资 绩效工资 1 11 导入 考勤扣款 数据 12 计算 税前工资 13 计算 个人所得税 应交个人所得税额 应纳税所得额 适用税率 速算扣除数 个人所得税税率见表2 1所示 表2 1个人所得税税率表 选中N3单元格 输入公式 IF M3 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 适当调整单元格的列宽 使单元格