模块三excel在工资管理中的应用.pptx

EXCEL在财务中的应用 模块 任务 三 EXCEL在工资管理中 的应用 一 设置工资管理中的表 格体系并计算工资 任务引出： 利用EXCEL系统进行工资业务管理，首先 需要建立工资管理的相关表格，以便收集、录 入工资管理中所需的基本数据并计算出当月各 项工资数据 任务分析： 一般企业在工资管理中，对员工工资的管理会涉及 到员工的基本档案，在工资计算的内容中，还包括员工 的基本工资、奖金、出勤情况、应缴纳的社会保险等多 项内容因此员工工资的计算实际上可以理解为获取以 上基本数据并进行统计、汇总和计算有效地借助于 EXCEL的公式与函数、数据表单等操作，可以大大提高 工作效率，科学地计算与管理工资 知识讲解： 1.1 函数VLOOKUP 1. 作用：使用 VLOOKUP 函数搜索某个单元格区 域的第一列，然后返回该区域相同行上任何单元格 中的值 2. 语法：VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) 知识讲解： （1）lookup_value必需是要在表格或区域的第一列中搜索 的值 lookup_value 参数可以是值或引用。

如果为 lookup_value 参数提供的值小于 table_array 参数第一列 中的最小值，则 VLOOKUP 将返回错误值 #N/A （2）table_array必需是包含数据的单元格区域 可以使用对区域（例如A2:D8）或区域名称的引用 table_array 第一列中的值是由 lookup_value 搜索的值 这些值可以是文本、数字或逻辑值文本不区分大小写 知识讲解： （3）col_index_num必需 table_array 参数中必须返回的匹配值的列号 col_index_num 参数为 1 时，返回 table_array 第一列中的值；col_index_num 为 2 时，返回 table_array 第二列中的值，依此类推 如果 col_index_num 参数小于 1，则 VLOOKUP 返 回错误值 #VALUE!；大于 table_array 的列数，则 VLOOKUP 返回错误值 #REF! 知识讲解： （4）range_lookup可选其值是一个逻辑值，指定希望 VLOOKUP 查找精确匹配值还是近似匹配值 如果 range_lookup 为 TRUE 或1或被省略，则返回精确 匹配值或近似匹配值。

如果找不到精确匹配值，则返回小于 lookup_value 的最大值 如果 range_lookup 参数为 FALSE或0，VLOOKUP 将只查 找精确匹配值如果 table_array 的第一列中有两个或更 多值与 lookup_value 匹配，则使用第一个找到的值 如果找不到精确匹配值，则返回错误值 #N/A 知识讲解： 3. 使用注意事项： 如果 range_lookup 为 TRUE 或1或被省略，则必须 按升序排列 table_array 第一列中的值；否则， VLOOKUP 可能无法返回正确的值 如果 range_lookup 为 FALSE或0，则不需要对 table_array 第一列中的值进行排序 知识讲解： 例1：根据“基本工资”、“员工信息表”中的相关数据，利 用VLOOKUP函数，将“工资表”中的“工资级别”和“基本 工资 ”列数据填写完整如图3-1-1所示 知识讲解： 知识讲解： 解决方案： 根据员工姓名在“员工信息表”中查到对应的工资级别值 填入“工资表”C列，然后根据工资级别在“基本工资”表中 查找到对应的基本工资数据填入“工资表”D列即可在本 题中，人员姓名和工资级别、工资级别和基本工资均是精 确匹配。

知识讲解： 操作步骤： 第一步，选择C3单元，录入公式 “=VLOOKUP(B3,$F$10:$G$13,2,FALSE)”或 “=VLOOKUP(B3,$F$10:$G$13,2,0)”，确认后，将此公式向 下填充至C6，如图 3-1-2所示； 知识讲解： 知识讲解： 操作步骤： 第二步，选择D3单元，录入公式 “=VLOOKUP(C3,$F$3:$G$7,2, FALSE )”或 “=VLOOKUP(C3,$F$3:$G$7,2,0)”，确认后，将此公式向 下填充至D6，如图3-1-3所示 知识讲解： 知识讲解： 例2：已知某单位销售额对应的奖金提成比例如下表3-1-1 所示: 销售额提成比例 0～49990 5000～99990.03 10000～199990.06 20000～399990.08 40000以上0.12 要求：根据销售人员的销售额统计数据计算其销售奖金 （如表3-1-2销售提成计算表） 知识讲解： 解决方案： 此题实质是根据“销售提成计算表”中的销售额在“提成比例表” 中查找出对应的提成比例，填入“销售提成计算表”的“提成比例” 列，然后用销售额乘以提成比例计算出“提成奖金额”即可。

考虑到“销售提成计算表”中的销售额为数值型，而“提成比例 表”中的销售额为表示数值区间的字符串，因此，第一步，对提 成比例表进行改进，添加 “参照销售额”列，如图3-1-4 所示， 而且，由于vlookup的查找规则为“找到小于等于被查找值的最 大值”，故参照值取提成比例对应区间的最小数； 知识讲解： 知识讲解： 第二步，选定G3单元，录入公式 “=VLOOKUP(F3,$B$3:$C$7,2)”，然后，将该公式向下填 充至G6；如图3-1-5 所示. 知识讲解： 任务实施： 1.1获取员工档案数据 通常，企业的员工档案是由人事部门负责管理的，财务部 门在计算员工工资之前，必须先获取人事档案表如果人事部 门使用ACCESS等数据库文件进行员工档案的管理，则可以使 用EXCEL所提供的【数据】|【获取外部数据】直接导入员工 档案，否则，手工设置员工档案文件并录入相关数据本企业 员工档案表是人事部门提供的EXCEL文件“职工人事档案” 任务实施： 实施步骤： 1.新建一个工作簿“工资管理系统”，在工作表sheet1中选定A1 单元，然后单击菜单栏【数据】|【自其他来源】|【来自XML 数据导入】，如图3-1-7所示。

任务实施： 任务实施： 实施步骤： 2.选取数据源 在弹出的“选取数据源”对话框中，从“文件类型”下拉列表中 选择“所有文件”，将“查找范围”中确定为“职工人事档案”所在的 文件夹“企业人事档案”，选定列表中的目标文件“职工人事档案 ”，单击【打开】按钮或双击目标文件“职工人事档案”如图3- 1-8所示 任务实施： 任务实施： 实施步骤： 3.选择表格 在弹出的“选择表格”对话框中，选择列表中的第一项，单击【 确定】按钮如图3-1-9所示 任务实施： 实施步骤： 4.导入数据 在弹出的“导入数据”对话框 中，选定“该数据在工作簿中的 显示方式”为“表”，“数据的放置 位置”为“现有工作表”，单元格 位置为“$A$1”，然后单击【属 性】按钮 任务实施： 实施步骤： 5.连接属性设置 在弹出的“连接属性”对话框中，对导入数据的属性进行设 置，设置完毕，单击【确定】按钮，返回“导入数据”对话 框 任务实施： 任务实施： 任务实施： 实施步骤： 6．在 “导入数据”对话框中单击【确定】按钮，导入数据后的 EXCEL工作表如图所示 任务实施： 任务实施： 实施步骤： 7.根据需要设置导入数据表的字体、字号等格式，取消排序和 筛选设置，将员工编号一列的数据类型设置为字符型，并重新 录入长度为4位的员工顺序编码，然后将工作表名称改为“员工 档案”，最后将工作簿以“员工工资管理系统”文件名保存。

如图 所示 任务实施： 任务实施： 1.2创建基本工资标准表和岗位工资标准表 一般情况下，员工的基本工资和岗位工资是相对比较固定 的，本企业员工的基本工资是根据不同部门制定，即同一部门 的员工基本工资相同；岗位工资是和员工的岗位职务挂钩，同 一职务的员工岗位工资相同因此，为了快速填入每位员工的 基本工资和岗位工资，应该先建立基本工资标准表和岗位工资 标准表 任务实施： 实施步骤： 1.打开“员工工资管理系统”工作簿，选择sheet2,在合适位置分 别建立基本工资标准表和岗位工资标准表，并录入基本数据 如图 所示 任务实施： 注：由于两个表格数据不多，可以放在一个工作表中 任务实施： 实施步骤： 2.修改sheet2工作表名称为“工资标准”，然后保存如图 修改工作表名称 任务实施： 1.3创建绩效考核表 一般企业奖金是工资构成中必不可少的一个项目，但员工 奖金与企业的奖励制度有密切关系，可能每个月都会发生变 动在本企业，奖金是与绩效挂钩的，绩效考核为“优”的奖 2000，“良”的奖1500，“中”的奖800，“合格”的奖500，“不合格 ”无奖励因此，可以创建一个绩效考核表，该表中可以包含 员工的编号、姓名、所属部门、绩效考核结果及奖金字段，每 月通过此表提供员工的奖金数据。

任务实施： 实施步骤： 1. 打开“员工工资管理系统”工作簿，选择sheet3,设计出“绩效 考核表”格式并录入员工编号数据由于绩效考核按月进行， 因此日期的具体数据可以通过在E2单元输入公式“=NOW()”获 得，选定E2，输入公式，并将单元格式设置为合适的日期型， 如图所示 任务实施： 任务实施： 实施步骤： 2. 利用VLOOKUP函数，从“员工档案”中提取员工“姓名”、“所 属部门”数据 选择D4单元，插入公式：“=VLOOKUP(C4,员工档案 !$A$2:$K$26,2,0)”，如图3-1-18所示，然后，向下填充该公式 至D28; 任务实施： 任务实施： 实施步骤： 选择E3单元，插入公 式“=VLOOKUP(C4,员 工档案 !$A$2:$K$27,4,0)”， 如图所示然后，向 下填充该公式至E28 得到结果如图所 示 任务实施： 实施步骤： 然后，向下 填充该公式 至E28得到 结果如图所 示 任务实施： 实施步骤： 3. 利用数据有效性功能录入员工当月绩效考核结果 选定F4:F28,选择【数据】|【数据有效性】下拉列表中的【数 据有效性】，在弹出的“数据有效性”对话框“设置”选项卡“允许” 下拉列表中选择“序列”。

如图所示 任务实施： 任务实施： 实施步骤： 在“来源”列表中录入序列值“优，良，中，合格，不合格”，如 图所示然后单击【确定】按钮 任务实施： 实施步骤： 绩效考核表 中，F列数据 即可通过单 击单元格右 侧的下拉按 钮在列表中 选择依次录 入，如图3-1 -23所示 任务实施： 实施步骤： 4. 利用IF函数，根据绩效考核结果计算得出员工奖金选定 G4单元，录入公式“=IF(F4=“优“,2000,IF(F4=“良 “,1500,IF(F4=“中“,800,IF(F4=“合格“,500,0))))”，按回车确认后 ，向下填充至F28，即可计算出员工奖金如图所示 任务实施： F4单元的公式 公式计算结果 任务实施： 实施步骤： 5. 修改sheet3工作表页签为“本月绩效考核表”，然后保存 任务实施： 1.4 创建员工考勤统计表 员工考勤统计表是用来统计员工的出勤情况的一般在每月 的月末进行统计，然后根据公司的考勤制度和员工的出勤情况 ，计算员工的考勤扣款和加班工资，最后用在员工当月工资的 计算中 本公司考勤制度如下： ① 考勤类型分为迟到、病假、事假、旷工、婚假、产假、年 假、加班； 任务实施： ② 迟到扣款制度：月累计迟到10分钟以内不扣款，超过10分 钟但不超过半小时，扣款15元，累计超过半小时但在1小时内 ，扣款30元，超过1小时，扣款60元。

③ 请假扣款制度：婚假、产假、年假工资照。