《模块三：Excel在工资管理中的应用.pptx》由会员分享，可在线阅读，更多相关《模块三：Excel在工资管理中的应用.pptx（169页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、EXCEL在财务中的应用在财务中的应用模块模块任务任务三三EXCEL在工资管理中在工资管理中 的应用的应用一一 设置工资管理中的表设置工资管理中的表格体系并计算工资格体系并计算工资任务任务引出：引出：利用利用EXCEL系统进行工资业务管理，首先系统进行工资业务管理，首先需要建立工资管理的相关表格，以便收集、录需要建立工资管理的相关表格，以便收集、录入工资管理中所需的基本数据并计算出当月各入工资管理中所需的基本数据并计算出当月各项工资数据。项工资数据。任务分析：任务分析：一般企业在工资管理中，对员工工资的管理会涉及一般企业在工资管理中，对员工工资的管理会涉及到员工的基本档案，在工资计算的内容中，
2、还包括员工到员工的基本档案，在工资计算的内容中，还包括员工的基本工资、奖金、出勤情况、应缴纳的社会保险等多的基本工资、奖金、出勤情况、应缴纳的社会保险等多项内容。因此员工工资的计算实际上可以理解为获取以项内容。因此员工工资的计算实际上可以理解为获取以上基本数据并进行统计、汇总和计算。有效地借助于上基本数据并进行统计、汇总和计算。有效地借助于EXCELEXCEL的公式与函数、数据表单等操作，可以大大提高的公式与函数、数据表单等操作，可以大大提高工作效率，科学地计算与管理工资。工作效率，科学地计算与管理工资。知识讲解：知识讲解：1.1 函数函数VLOOKUP 1.作用：使用作用：使用 VLOOKU
3、P 函数搜索某个单元格区函数搜索某个单元格区域的第一列，然后返回该区域相同行上任何单元格域的第一列，然后返回该区域相同行上任何单元格中的值。中的值。2.语法：语法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)知识讲解：知识讲解：（1 1）lookup_valuelookup_value必需。必需。是要在表格或区域的第一列中搜索的值。lookup_value 参数可以是值或引用。如果为 lookup_value 参数提供的值小于 table_array 参数第一列中的最小值，则 VLOOKUP 将返回错误值#N/A。（2
4、 2）table_arraytable_array必需。必需。是包含数据的单元格区域。可以使用对区域（例如A2:D8）或区域名称的引用。table_array 第一列中的值是由 lookup_value 搜索的值。这些值可以是文本、数字或逻辑值。文本不区分大小写。知识讲解：知识讲解：（3 3）col_index_numcol_index_num必需。必需。table_array 参数中必须返回的匹配值的列号。col_index_num 参数为 1 时，返回 table_array 第一列中的值；col_index_num 为 2 时，返回 table_array 第二列中的值，依此类推。如果
5、col_index_num 参数小于 1，则 VLOOKUP 返回错误值#VALUE!；大于 table_array 的列数，则 VLOOKUP 返回错误值#REF!。知识讲解：知识讲解：（4 4）range_lookuprange_lookup可选。可选。其值是一个逻辑值，指定希望 VLOOKUP 查找精确匹配值还是近似匹配值。如果 range_lookup 为 TRUE 或1或被省略，则返回精确匹配值或近似匹配值。如果找不到精确匹配值，则返回小于 lookup_value 的最大值。如果 range_lookup 参数为 FALSE或0，VLOOKUP 将只查找精确匹配值。如果 table
6、_array 的第一列中有两个或更多值与 lookup_value 匹配，则使用第一个找到的值。如果找不到精确匹配值，则返回错误值#N/A。知识讲解：知识讲解：3.3.使用注意事项：使用注意事项：如果 range_lookup 为 TRUE 或1或被省略，则必须按升序排列 table_array 第一列中的值；否则，VLOOKUP 可能无法返回正确的值。如果 range_lookup 为 FALSE或0，则不需要对 table_array 第一列中的值进行排序。知识讲解：知识讲解：例例1：根据“基本工资”、“员工信息表”中的相关数据，利用VLOOKUP函数，将“工资表”中的“工资级别”和“基本
7、工资”列数据填写完整。如图3-1-1所示。知识讲解：知识讲解：知识讲解：知识讲解：解决方案：解决方案：根据员工姓名在根据员工姓名在“员工信息表员工信息表”中查到对应的工资级别中查到对应的工资级别值填入值填入“工资表工资表”C列，然后根据工资级别在列，然后根据工资级别在“基本工资基本工资”表中查找到对应的基本工资数据填入表中查找到对应的基本工资数据填入“工资表工资表”D列即可。列即可。在本题中，人员姓名和工资级别、工资级别和基本工资均在本题中，人员姓名和工资级别、工资级别和基本工资均是精确匹配。是精确匹配。知识讲解：知识讲解：操作步骤：操作步骤：第一步，选择第一步，选择C3单元，录入公式单元，录
8、入公式“=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所示。所示。知识讲解：知识讲解：知识讲解：知
9、识讲解：例例2：已知某单位销售额对应的奖金提成比例如下表：已知某单位销售额对应的奖金提成比例如下表3-1-1所示所示:销售额销售额提成比例提成比例049990500099990.0310000199990.0620000399990.0840000以上0.12要求：根据销售人员的销售额统计数据计算其销售奖金要求：根据销售人员的销售额统计数据计算其销售奖金 （如表（如表3-1-2销售提成计算表）销售提成计算表）。知识讲解：知识讲解：解决方案：解决方案：此题实质是根据“销售提成计算表”中的销售额在“提成比例表”中查找出对应的提成比例，填入“销售提成计算表”的“提成比例”列，然后用销售额乘以提成比例
10、计算出“提成奖金额”即可。考虑到“销售提成计算表”中的销售额为数值型，而“提成比例表”中的销售额为表示数值区间的字符串，因此，第一步，对提成比例表进行改进，添加“参照销售额”列，如图3-1-4 所示，而且，由于vlookup的查找规则为“找到小于等于被查找值的最大值”，故参照值取提成比例对应区间的最小数；知识讲解：知识讲解：知识讲解：知识讲解：第二步，选定G3单元，录入公式“=VLOOKUP(F3,$B$3:$C$7,2)”，然后，将该公式向下填充至G6；如图3-1-5 所示.知识讲解：知识讲解：任务实施：任务实施：1.1获取员工档案数据 通常，企业的员工档案是由人事部门负责管理的，财务部门在
11、计算员工工资之前，必须先获取人事档案表。如果人事部门使用ACCESS等数据库文件进行员工档案的管理，则可以使用EXCEL所提供的【数据】|【获取外部数据】直接导入员工档案，否则，手工设置员工档案文件并录入相关数据。本企业员工档案表是人事部门提供的EXCEL文件“职工人事档案”。任务实施：任务实施：实施步骤：实施步骤：1.新建一个工作簿“工资管理系统”，在工作表sheet1中选定A1单元，然后单击菜单栏【数据】|【自其他来源】|【来自XML数据导入】，如图3-1-7所示。任务实施：任务实施：任务实施：任务实施：实施步骤：实施步骤：2.选取数据源。选取数据源。在弹出的“选取数据源”对话框中，从“文
12、件类型”下拉列表中选择“所有文件”，将“查找范围”中确定为“职工人事档案”所在的文件夹“企业人事档案”，选定列表中的目标文件“职工人事档案”，单击【打开】按钮或双击目标文件“职工人事档案”。如图3-1-8所示。任务实施：任务实施：任务实施：任务实施：实施步骤：实施步骤：3.选择表格在弹出的“选择表格”对话框中，选择列表中的第一项，单击【确定】按钮。如图3-1-9所示。任务实施：任务实施：实施步骤：实施步骤：4.导入数据导入数据 在弹出的“导入数据”对话框中，选定“该数据在工作簿中的显示方式”为“表”，“数据的放置位置”为“现有工作表”，单元格位置为“$A$1”，然后单击【属性】按钮。任务实施：
13、任务实施：实施步骤：实施步骤：5.连接属性设置连接属性设置 在弹出的“连接属性”对话框中，对导入数据的属性进行设置，设置完毕，单击【确定】按钮，返回“导入数据”对话框。任务实施：任务实施：任务实施：任务实施：任务实施：任务实施：实施步骤：实施步骤：6在“导入数据”对话框中单击【确定】按钮，导入数据后的EXCEL工作表如图所示。任务实施：任务实施：任务实施：任务实施：实施步骤：实施步骤：7.根据需要设置导入数据表的字体、字号等格式，取消排序和筛选设置，将员工编号一列的数据类型设置为字符型，并重新录入长度为4位的员工顺序编码，然后将工作表名称改为“员工档案”，最后将工作簿以“员工工资管理系统”文件
14、名保存。如图所示。任务实施：任务实施：任务实施：任务实施：1.2创建基本工资标准表和岗位工资标准表创建基本工资标准表和岗位工资标准表 一般情况下，员工的基本工资和岗位工资是相对比较固定的，本企业员工的基本工资是根据不同部门制定，即同一部门的员工基本工资相同；岗位工资是和员工的岗位职务挂钩，同一职务的员工岗位工资相同。因此，为了快速填入每位员工的基本工资和岗位工资，应该先建立基本工资标准表和岗位工资标准表。任务实施：任务实施：实施步骤：实施步骤：1.打开“员工工资管理系统”工作簿，选择sheet2,在合适位置分别建立基本工资标准表和岗位工资标准表，并录入基本数据。如图 所示。任务实施：任务实施：
15、注：由于两个表格数据不多，可以放在一个工作表中。注：由于两个表格数据不多，可以放在一个工作表中。任务实施：任务实施：实施步骤：实施步骤：2.修改sheet2工作表名称为“工资标准”，然后保存。如图修改工作表名称任务实施：任务实施：1.3创建绩效考核表创建绩效考核表 一般企业奖金是工资构成中必不可少的一个项目，但员工奖金与企业的奖励制度有密切关系，可能每个月都会发生变动。在本企业，奖金是与绩效挂钩的，绩效考核为“优”的奖2000，“良”的奖1500，“中”的奖800，“合格”的奖500，“不合格”无奖励。因此，可以创建一个绩效考核表，该表中可以包含员工的编号、姓名、所属部门、绩效考核结果及奖金字
16、段，每月通过此表提供员工的奖金数据。任务实施：任务实施：实施步骤：实施步骤：1.打开“员工工资管理系统”工作簿，选择sheet3,设计出“绩效考核表”格式并录入员工编号数据。由于绩效考核按月进行，因此日期的具体数据可以通过在E2单元输入公式“=NOW()”获得，选定E2，输入公式，并将单元格式设置为合适的日期型，如图所示。任务实施：任务实施：任务实施：任务实施：实施步骤：实施步骤：2.利用VLOOKUP函数，从“员工档案”中提取员工“姓名”、“所属部门”数据。选择D4单元，插入公式：“=VLOOKUP(C4,员工档案!$A$2:$K$26,2,0)”，如图3-1-18所示，然后，向下填充该公式
17、至D28;任务实施：任务实施：任务实施：任务实施：实施步骤：实施步骤：选择E3单元，插入公式“=VLOOKUP(C4,员工档案!$A$2:$K$27,4,0)”，如图所示。然后，向下填充该公式至E28。得到结果如图所示。任务实施：任务实施：实施步骤：实施步骤：然后，向下填充该公式至E28。得到结果如图所示。任务实施：任务实施：实施步骤：实施步骤：3.利用数据有效性功能录入员工当月绩效考核结果。选定F4:F28,选择【数据】|【数据有效性】下拉列表中的【数据有效性】，在弹出的“数据有效性”对话框“设置”选项卡“允许”下拉列表中选择“序列”。如图所示。任务实施：任务实施：任务实施：任务实施：实施步
18、骤：实施步骤：在“来源”列表中录入序列值“优，良，中，合格，不合格”，如图所示。然后单击【确定】按钮。任务实施：任务实施：实施步骤：实施步骤：绩效考核表中，F列数据即可通过单击单元格右侧的下拉按钮在列表中选择依次录入，如图3-1-23所示。任务实施：任务实施：实施步骤：实施步骤：4.利用IF函数，根据绩效考核结果计算得出员工奖金。选定G4单元，录入公式“=IF(F4=优,2000,IF(F4=良,1500,IF(F4=中,800,IF(F4=合格,500,0)”，按回车确认后，向下填充至F28，即可计算出员工奖金。如图所示。任务实施：任务实施：F4单元的公式公式计算结果任务实施：任务实施：实施
19、步骤：实施步骤：5.修改sheet3工作表页签为“本月绩效考核表”，然后保存。任务实施：任务实施：1.4 创建员工考勤统计表。创建员工考勤统计表。员工考勤统计表是用来统计员工的出勤情况的。一般在每月的月末进行统计，然后根据公司的考勤制度和员工的出勤情况，计算员工的考勤扣款和加班工资，最后用在员工当月工资的计算中。本公司考勤制度如下：考勤类型分为迟到、病假、事假、旷工、婚假、产假、年假、加班；任务实施：任务实施：迟到扣款制度：月累计迟到10分钟以内不扣款，超过10分钟但不超过半小时，扣款15元，累计超过半小时但在1小时内，扣款30元，超过1小时，扣款60元。请假扣款制度：婚假、产假、年假工资照发
20、；病假期间支付日基本工资的50%，事假期间不支付日基本工资；旷工扣款制度：旷工一天按日基本工资的1.2倍扣款。任务实施：任务实施：每月天数均按30天计算。根据以上资料，在EXCEL中创建员工考勤统计表。任务实施：任务实施：实施步骤：实施步骤：1.打开“员工工资管理系统”工作簿，插入新工作表，命名为“本月考勤统计”，然后在该工作表中录入统计表框架并录入员工编号。如图3-1-25所示。本月考勤表包含的报表项目有员工编号、员工姓名、职务、基本工资、累计迟到（分钟）、病假天数、事假天数、旷工天数、加班时数、迟到扣款、事假扣款、旷工扣款、缺勤扣款、加班工资。任务实施：任务实施：注意：表头日期注意：表头日
21、期H2H2单元应录入公式单元应录入公式“=NOW()=NOW()”并设置其日期格式显示为并设置其日期格式显示为“*年年*月月”任务实施：任务实施：实施步骤：实施步骤：2.利用VLOOKUP函数从“员工档案”表中提取员工姓名、所属部门、职务数据填入本表，从“工资标准表”中提取基本工资填入本表。（1）选定B4:B28,录入公式“=VLOOKUP(A4:A28,员工档案!$A$2:$K$26,2,0)”，同时按CTRL、SHIFT和ENTER键进行确认，得到职工姓名；（2）选定C4:C28,录入公式“=VLOOKUP(A4:A28,员工档案!$A$2:$K$26,4,0)”，同时按CTRL、SHIF
22、T和ENTER键进行确认，得到所属部门；（3）选定D4:D28,录入公式“=VLOOKUP(A4:A28,员工档案!$A$2:$K$26,5,0)”，同时按CTRL、SHIFT和ENTER键进行确认，得到职务；任务实施：任务实施：注意：这里的公式均是以数组公式的方注意：这里的公式均是以数组公式的方式录入的，也可以先录入每列第一个单式录入的，也可以先录入每列第一个单元的公式，然后向下填充。元的公式，然后向下填充。任务实施：任务实施：实施步骤：实施步骤：3.手工录入迟到（分钟）、病假天数、事假天数、旷工天数、加班时数等统计数字。任务实施：任务实施：根据考勤结果手根据考勤结果手工输入数据工输入数据任
23、务实施：任务实施：实施步骤：实施步骤：4.利用IF函数计算迟到扣款、病假扣款、事假扣款和旷工扣款、加班工资，并统计缺勤扣款。（1）选择K4:K28，录入公式“=IF(F4:F2860,60,IF(F4:F2830,30,IF(F4:F2810,15,0)”,同时按CTRL、SHIFT和ENTER键进行确认，计算得到迟到扣款金额。任务实施：任务实施：任务实施：任务实施：实施步骤：实施步骤：（2）选择L4:L28,录入公式“=ROUND(E4:E28/30/2*G4:G28+E4:E28/30*H4:H28,0)”，同时按CTRL、SHIFT和ENTER键进行确认，计算得到病事假扣款。任务实施：任
24、务实施：任务实施：任务实施：实施步骤：实施步骤：（3）选择M4:M28,录入公式“=ROUND(E4:E28/30*1.2*I4:I28,0)”，同时按CTRL、SHIFT和ENTER键进行确认，计算得到旷工扣款。任务实施：任务实施：任务实施：任务实施：实施步骤：实施步骤：（4）选择N4,录入公式“=SUM(K4:M4)”按回车确认，向下填充直到N28，得到缺勤扣款数。任务实施：任务实施：任务实施：任务实施：实施步骤：实施步骤：（5）选择O4:O28,录入公式“=ROUND(IF(D4:D28=经理,0,IF(J4:J282,J4:J28*30,IF(J4:J280,J4:J28*25,0),
25、0)”，同时按CTRL、SHIFT和ENTER键进行确认，计算得到加班工资。然后保存文件。任务实施：任务实施：任务实施：任务实施：1.5 创建工资明细表并计算工资创建工资明细表并计算工资。前面已经将计算工资所需要的一些基本数据和每月需单独提供的数据设计表格计算完成，下来即可按照公司工资制度所规定的工资项目和计算方法创建工资明细表，计算工资。任务实施：任务实施：实施步骤：实施步骤：1.打开“员工工资管理系统”工作簿，添加一张表页，并将其命名为“工资明细表”，在该工作表中设计工资明细表的框架，并录入职工编号。工资明细表中包含的工资项目有：员工编号、姓名、所属部门、基本工资、岗位工资、奖金、加班工资
26、、缺勤扣款、应发工资、代扣社会保险、应税所得、代扣个人所得税、实发工资。任务实施：任务实施：任务实施：任务实施：实施步骤：实施步骤：2.利用LOOKUP函数设计公式，分别从“员工档案”中提取姓名、所属部门、职务数据；从“工资标准”中提取基本工资、岗位工资；从“本月绩效考核表”中提取奖金；从“本月考勤统计”中提取加班工资、缺勤扣款等填入本表，然后计算出应发工资。（1）选定B4:B28,录入公式“=VLOOKUP(A4:A28,员工档案!$A$2:$K$26,2,0)”，同时按CTRL、SHIFT和ENTER键进行确认，得到职工姓名；任务实施：任务实施：实施步骤：实施步骤：（3）选定D4:D28,
27、录入公式“=VLOOKUP(A4:A28,员工档案!$A$2:$K$26,5,0)”，同时按CTRL、SHIFT和ENTER键进行确认，得到职务；（4）选定E4:E28,录入公式“=VLOOKUP(C4:C28,工资标准!$A$3:$B$6,2,0)”，同时按CTRL、SHIFT和ENTER键进行确认，得到基本工资；任务实施：任务实施：实施步骤：实施步骤：（5）选定F4:F28,录入公式“=VLOOKUP(D4:D28,工资标准!$D$3:$E$9,2,0)”，同时按CTRL、SHIFT和ENTER键进行确认，得到岗位工资；（6）选定G4:G28,录入公式“=VLOOKUP(A4:A28,本月
28、绩效考核表!$C$4:$G$28,5,0)”，同时按CTRL、SHIFT和ENTER键进行确认，得到奖金；（7）选定H4:H28,录入公式“=VLOOKUP(A4:A28,本月考勤统计!$A$4:$O$28,15,0)”，同时按CTRL、SHIFT和ENTER键进行确认，得到加班工资；任务实施：任务实施：实施步骤：实施步骤：（8）选定I4:I28,录入公式“=VLOOKUP(A4:A28,本月考勤统计!$A$4:$O$28,15,0)”，同时按CTRL、SHIFT和ENTER键进行确认，得到缺勤扣款。（9）应发工资由前几项数据计算得来，选定J4:J28,录入公式“=E4:E28+F4:F28+
29、G4:G28+H4:H28-I4:I28”，同时按CTRL、SHIFT和ENTER键进行确认，得到应发合计。任务实施：任务实施：任务实施：任务实施：实施步骤：实施步骤：3.根据单位所遵循的住房公积金和社会保险计算管理办法，计算“代扣社会保险”。本公司住房公积金和社会保险计算管理办法如下：应由个人缴纳住房公积金、养老保险、医疗保险和失业保险部分，统一由企业按月代扣代缴。三险一金的缴纳基数为个人当月的基本工资与岗位工资之和，缴纳比例分别为5%、6%、2%、1%。按照以上计算办法设置代扣社会保险的计算公式：选定K4:K28，录入公式“=(E4:E28+F4:F28)*0.14”，同时按CTRL、SH
30、IFT和ENTER键进行确认，得到代扣社会保险。任务实施：任务实施：任务实施：任务实施：实施步骤：实施步骤：4.计算应税所得额及代扣个人所得税公司按照现行税收法律规定按月代扣代缴员工工资、薪金个人所得税，起征点为3500元，即应税所得额超过3500，须缴纳个人所得税，否则，不缴个人所得税。个人所得税计算时使用超额累进税率。如果运用速算扣除法计算，其税率及对应速算扣除数如图3-1-36所示，本单位代扣个人所得税计算方法为：代扣个人所得税=应税所得额*适用税率-速算扣除数其中，应税所得额=应发工资-代扣社会保险-起征点任务实施：任务实施：任务实施：任务实施：实施步骤：实施步骤：为此，可以先计算出应
31、税所得额,选定L4:L28，录入公式“=IF(J4:J28-K4:K28-35000,J4:J28-K4:K28-3500,0)”，同时按CTRL、SHIFT和ENTER键进行确认，得到应税所得额。任务实施：任务实施：任务实施：任务实施：实施步骤：实施步骤：为此，可以先计算出应税所得额,选定L4:L28，录入公式“=IF(J4:J28-K4:K28-35000,J4:J28-K4:K28-3500,0)”，同时按CTRL、SHIFT和ENTER键进行确认，得到应税所得额。任务实施：任务实施：实施步骤：实施步骤：然后，创建税率查询表，用IF函数与VLOOKUP函数相结合来计算代扣个人所得税。（1
32、）在员工工资管理系统中增加一个新工作表，命名为“个税税率查询表”，在该工作表中设计个税税率查询表如图所示。任务实施：任务实施：添添加加“参参考考标标准准”列列，且且数数据据为为该该级级距距范范围围内的最小值！内的最小值！任务实施：任务实施：实施步骤：实施步骤：（2）选中C3:E9,单击【公式】|【定义名称】，为该区域命名为“参考标准”，单击【确定】按钮。任务实施：任务实施：任务实施：任务实施：实施步骤：实施步骤：（3）单击【工资明细表】页签，在“工资明细表”中，选中M4:M28,录入公式“=IF(L4:L280,L4:L28*VLOOKUP(L4:L28,参考标准,2,1)-VLOOKUP(L
33、4:L28,参考标准,3,1),0)”，同时按CTRL、SHIFT和ENTER键进行确认，得到代扣个人所得税。如图所示。任务实施：任务实施：注意：在此处的注意：在此处的VLOOKUPVLOOKUP函数使用中，函数使用中，VLOOKUP(lookup_value,table_array,VLOOKUP(lookup_value,table_array,col_index_num,range_lookup),col_index_num,range_lookup),参数参数range_lookuprange_lookup的值设为的值设为1 1或省略，表示近似匹配。或省略，表示近似匹配。任务实施：任务
34、实施：实施步骤：实施步骤：5.计算实发工资实发工资=应发工资-代扣社会保险-代扣个人所得税。在“工资明细表”中，选中N4:N28,录入公式“=J4:J28-K4:K28-M4:M28”，同时按CTRL、SHIFT和ENTER键进行确认，得到实发工资。保存工作簿。任务实施：任务实施：小结评价：小结评价：熟练掌握工资管理中各类工资基础数据表格的设置；熟练掌握工资管理中基本工资、岗位工资、工龄工资的来源分析与生成；熟练掌握绩效工资、奖金数据的计算、保险与公积金的计算、个人所得税的计算；EXCEL在财务中的应用在财务中的应用模块模块任务任务三三EXCEL工资管理中工资管理中 的应用的应用二二 员工工资
35、的管理员工工资的管理任务任务引出：引出：当工资明细表制作完成后，企业根据工资数据安全、发放工资、当工资明细表制作完成后，企业根据工资数据安全、发放工资、工资汇总分配等的需要在表中数据的基础上进一步进行工资管理。工资汇总分配等的需要在表中数据的基础上进一步进行工资管理。为了保障工资明细表中数据的安全，一般除了每月需要手工录入为了保障工资明细表中数据的安全，一般除了每月需要手工录入的变动数据外，其余根据公式计算产生的数据是不允许随意修改的。的变动数据外，其余根据公式计算产生的数据是不允许随意修改的。但是在系统默认状态下，当选中公式所在单元时，公式会显示在栏中但是在系统默认状态下，当选中公式所在单元
36、时，公式会显示在栏中并允许随意修改，因此为了保证数据安全，需要对公式隐藏并加密；并允许随意修改，因此为了保证数据安全，需要对公式隐藏并加密；每月发放工资时，需要为每位员工提供工资条；会计人员每月需要按每月发放工资时，需要为每位员工提供工资条；会计人员每月需要按部门或人员类别对工资进行分类汇总以便按汇总结果进行费用分配；部门或人员类别对工资进行分类汇总以便按汇总结果进行费用分配；可以随时提供对工资数据的查询。可以随时提供对工资数据的查询。任务分析：任务分析：为了完成上述工资管理任务，可以选择使用为了完成上述工资管理任务，可以选择使用EXCEL系统提供的相应功能来实现：利用数据保护功能实现公系统提
37、供的相应功能来实现：利用数据保护功能实现公式的隐藏及加密；利用式的隐藏及加密；利用VLOOKUP等函数来实现工资条等函数来实现工资条的生成；利用数据筛选、汇总功能或数据透视功能来实的生成；利用数据筛选、汇总功能或数据透视功能来实现工资数据的汇总、分配和查询。现工资数据的汇总、分配和查询。任务实施：任务实施：1.1工资数据的保护。工资数据的保护。保护“工资明细表”中的所有工资数据和公式，可以使用EXCEL所提供的【保护】功能来实现。任务实施：任务实施：实施步骤：实施步骤：1.隐藏公式。打开员工工资管理系统工作簿，将“工资明细表”作为当前工作表，选中A2：N28区域，单击鼠标右键，在弹出的快捷菜单
38、中单击选择【单元格格式】命令，在弹出的对话框中单击【保护】选项卡，单击选中【隐藏】复选框，单击【确定】按钮。任务实施：任务实施：任务实施：任务实施：实施步骤：实施步骤：2.保护工作表。单击【审阅】|【保护工作表】命令，在弹出的“保护工作表”对话框中输入密码，单击【确定】按钮，在弹出的“确认密码”对话框中再次录入相同的密码，然后单击【确定】按钮返回工作表。此时，被保护单元格的公式被隐藏。任务实施：任务实施：任务实施：任务实施：注意：注意：单元锁定和隐藏公式只有在工作表保护状态下才是有效的。若要撤销公式隐藏，只需取消工作表保护。单击【审阅】|【撤销工作表保护】命令，在弹出的“撤销工作表保护”密码框
39、中输入正确密码，单击【确定】按钮即可。任务实施：任务实施：任务实施：任务实施：1.2制作工资条制作工资条 发放工资时一同发放给员工的工资条的内容与工资明细表基本一致，不同的是每一条记录都带有表头项目，因此需根据工资明细表重新制作工资条。任务实施：任务实施：实施步骤：实施步骤：1.打开员工工资管理系统工作簿，在“工资明细表”选中A4:N28区域，单击【公式】|【定义名称】，在弹出的“新建名称”对话框的“名称”框中录入该区域名称“工资数据”，单击【确定】按钮。然后按员工编号的升序对记录进行排序。任务实施：任务实施：任务实施：任务实施：实施步骤：实施步骤：2.在工作簿中插入一张工作表，并修改名称为“
40、工资条”，将该表移至“工资明细表”右边。将“工资明细表”的A3:N3区域复制到“工资条”表页上的B2：O2区域，在“工资条”表中A2单元输入“年月”；合并A1:O1区域，输入“员工工资条”，并设置表头格式。任务实施：任务实施：任务实施：任务实施：实施步骤：实施步骤：3.在A3单元中录入公式“=NOW()”,然后将该单元格格式设置为“*年*月”格式；在B3单元中录入第一个员工的编号“0001”；依次在C3到O3单元中分别录入公式：“=VLOOKUP(B3,工资数据,2,0)”;“=VLOOKUP(B3,工资数据,3,0)”“=VLOOKUP(B3,工资数据,4,0)”“=VLOOKUP(B3,工
41、资数据,5,0)”任务实施：任务实施：实施步骤：实施步骤：“=VLOOKUP(B3,工资数据,7,0)”“=VLOOKUP(B3,工资数据,8,0)”“=VLOOKUP(B3,工资数据,9,0)”“=VLOOKUP(B3,工资数据,10,0)”“=VLOOKUP(B3,工资数据,11,0)”“=VLOOKUP(B3,工资数据,12,0)”“=VLOOKUP(B3,工资数据,13,0)”得到结果如图所示。任务实施：任务实施：任务实施：任务实施：实施步骤：实施步骤：4.选中A1:M3区域，向下填充至第75行（员工记录为25条，每条记录对应的工资条数据占3行，共占75行。）结果如图所示。任务实施：任
42、务实施：任务实施：任务实施：5.对制作好的工资条进行打印设置，需要时即可打印输出。对制作好的工资条进行打印设置，需要时即可打印输出。【问题思考问题思考】是否可用其他方法制作工资条？是否可用其他方法制作工资条？任务实施：任务实施：1.3工资的分类汇总工资的分类汇总 根据工资数据核算与管理的需要，每月要按职工所属部门或人员类别等对工资数据进行分类汇总，以便分配工资费用或进行费用的统计分析。本单位每月按所属部门计算应发工资和实发工资的部门总数和平均数。该任务可以采用系统提供的分类汇总功能实现，也可以使用透视表功能来实现。任务实施：任务实施：1.采用分类汇总功能进行分类汇总。采用分类汇总功能进行分类汇
43、总。实施步骤：实施步骤：（1）打开“员工工资管理”工作簿，插入一张新工作表，更改工作表名为“工资汇总表”。（2）复制“工资明细表”工作表中的数据到“工资汇总表”，对“工资汇总表”中的数据按“所属部门”的升序进行排序，如图3-2-11所示。（注意：如果工资明细表“所属部门”一列的数据由数组公式生成，则不允许按其重新排序，故可将“工资明细表”中的相应的数组公式改为单元公式形式，或在复制数据时采用“选择性粘贴”|“值和数字格式”命令。）任务实施：任务实施：任务实施：任务实施：实施步骤：实施步骤：（3）选定A3:N28单元区域，单击【数据】|【分类汇总】命令，在打开的【分类汇总】对话框中依次选择“分类
44、字段”为“所属部门”，“汇总方式”为“求和”，“选定汇总项”为“应发工资”和“实发工资”，然后单击“确定”按钮。任务实施：任务实施：任务实施：任务实施：任务实施：任务实施：实施步骤：实施步骤：（4）继续单击【数据】|【分类汇总】命令，在打开的【分类汇总】对话框中依次选择“分类字段”为“所属部门”，“汇总方式”为“求平均值”，“选定汇总项”为“应发工资”和“实发工资”，取消“替换当前分类汇总”复选框中的对勾，然后单击“确定”按钮。任务实施：任务实施：任务实施：任务实施：任务实施：任务实施：任务实施：任务实施：说明：说明：在此分类汇总结果中，即可以看到每个部门应发工资和实发工资的汇总数据，也可以看
45、到每个部门应发工资和实发工资的平均数据。如果要取消分类汇总的结果，只需选定分类汇总数据区域，单击【数据】|【分类汇总】命令，在打开的【分类汇总】对话框中单击“全部删除”按钮即可。任务实施：任务实施：2.利用透视表功能实现分类汇总。利用透视表功能实现分类汇总。实施步骤：实施步骤：（1）打开工资明细表，选定A3:A28单元区域，单击【插入】|【数据透视表】命令，如图1-2-16所示，打开“创建数据透视表”对话框。任务实施：任务实施：任务实施：任务实施：任务实施：任务实施：实施步骤：实施步骤：（2）在“创建数据透视表”对话框中，确认“请选择要分析的数据区域”选项正确（如原来所选区域有误，可在此重新选
46、择），然后确定“选择放置数据透视表的位置”为“新工作表”（也可放在现有工作表中），然后单击“确定”按钮。得到结果如图。任务实施：任务实施：任务实施：任务实施：实施步骤：实施步骤：（3）在“数据透视表字段列表”选项卡中，先在“选择要添加到报表的字段”的选项列表中选定“所属部门”，系统将其确认为行标签，如图3-2-19所示；继续依次选定需要汇总的项目“应发工资”和“实发工资”，系统将其确认为列标签，汇总结果则随之显示，如图 任务实施：任务实施：任务实施：任务实施：任务实施：任务实施：实施步骤：实施步骤：（4）在表中数据区域以外空白处单击或单击“数据透视表字段列表”的关闭按钮，“数据透视表字段列表”
47、被关闭；如需对“数据透视表”内容进行修改，可右击任意数据区域在弹出的快捷菜单中选择执行相应命令，如图。任务实施：任务实施：任务实施：任务实施：实施步骤：实施步骤：（5）如要对应发工资按部门平均值进行汇总，可右击应发工资列数据，在弹出的快捷菜单中依次单击【数据汇总依据】|【平均值】，如图。任务实施：任务实施：任务实施：任务实施：任务实施：任务实施：实施步骤：实施步骤：（6）如需将B4及C4的显示名称分别改为“应发工资汇总”、“实发工资汇总”，可先双击B4单元或右击B4单元后选择【值字段设置】，在弹出的“值字段设置”选项卡上设定“自定义名称”的值为“应发工资汇总”，然后单击“确定”。如图。任务实施
48、：任务实施：任务实施：任务实施：任务实施：任务实施：然后再对 C4单元按照上述步骤进行设置即可，结果如图。任务实施：任务实施：实施步骤：实施步骤：（7）如果不需要在表中显示“值”及“行标签”字样，则可单击任一数据单元，依次选择【数据透视表工具】|【选项】|【字段标题】对其进行隐藏，如图.任务实施：任务实施：任务实施：任务实施：任务实施：任务实施：实施步骤：实施步骤：（8）为了符合一般人的阅读习惯，可以选中A1:C2单元区域，加上报表标题“工资部门汇总表”，然后对表页重新命名为“工资部门汇总表”如图所示。保存报表。任务实施：任务实施：任务实施：任务实施：【思考问题】分类汇总功能与数据透视表功能有
49、何异同？任务实施：任务实施：1.4 工资数据的查询工资数据的查询 作为一个完善的工资系统，应该为管理者或职工提供相关工资数据的查询功能。对工资数据的查询可以利用VLOOKUP 函数设计查询系统根据员工的编号或姓名等查询个人工资数据，也可以利用系统提供的筛选功能进行查询。此处仅介绍利用筛选功能进行查询。任务实施：任务实施：1.在工资明细表中查询职工何天明的工资数据。在工资明细表中查询职工何天明的工资数据。此任务属于按单个条件查询，使用自动筛选功能即可完成 实施步骤：实施步骤：（1）打开“职工工资系统”工作簿，选择“工资明细表”工作表，选定第三行，单击【开始】|【排序和筛选】|【筛选】，进入筛选状
50、态，如图.任务实施：任务实施：任务实施：任务实施：任务实施：任务实施：实施步骤：实施步骤：（2）单击B3单元“姓名”右侧下拉按钮，可以在下拉菜单中选择【文本筛选】|【等于】，任务实施：任务实施：任务实施：任务实施：在弹出的对话框中“姓名”值选择为“何天明”，然后单击“确定”按钮.任务实施：任务实施：注意：通过注意：通过“自定义自动筛选方式自定义自动筛选方式”，也可以实现一个字段的多值查询；，也可以实现一个字段的多值查询；若要取消自动筛选，再次单击若要取消自动筛选，再次单击【开始开始】|【排序和筛选排序和筛选】|【筛选筛选】即可。即可。任务实施：任务实施：2.在工资明细表中查询生产部门实发工资低