4Excel在工资管理中的应用（课件）《EXCEL在财务中的应用》（航空工业出版社）

Excel在工资管理中的应用 项目四 1 知识目标： ①掌握Excel数据清单的制作。 ②学会SUM、COUNTIF、VLOOKUP、IF 函数的使用。 素养目标： ①恪守职业道德，禁得住诱惑，坚定信念，始终做一名正直、公允、公正的财务人员。 ②通过小组合作，锻炼团结协作能力。 ③通过讲解各种操作技巧，增强善于思考的意识。 学习目标 能力目标： ①能够使用Excel设计工资管理的基本表格。 ②能够运用筛选、数据透视表对工资数据进行统计和分析。 ③能够完成工资条的制作。 ④能够利用Excel制作工资表，并能够对工资表的内容进行汇总筛选，以及批量调整工资表。 冀中职业学院 目录 建立相关数据信息表 任务4.1 冀中职业学院 工资数据的查询与汇总分析 任务4.3 制作公司管理工作表 任务4.2 工资数据的小数问题 任务4.4 批量制作个人薪级工资调整表 任务4.5 4.1.1 建立“工资管理”工作簿文件 冀中职业学院 任务 4.1 建立相关数据信息表 启动 Excel,单击“文件”菜单中的“新建”,单击“空白文档”,然后再单击“文件”中的“另存为”,把新建的文档起名为“工资管理”。 4.1.2 建立“员工基本信息表” 冀中职业学院 任务 4.1 建立相关数据信息表 把“工资管理”工作簿中的“Sheetl”改名为“员工基本信息表”,由于在“项目1”中已经建立“员工基本信息表”工作簿文件，只需要把工资管理中用到的工号、姓名、性别、部门、职务类别、参加工作时间等信息复制到“工资管理”文件中。 4.1.3 建立“基本工资”表 冀中职业学院 任务 4.1 建立相关数据信息表 把“工资管理”工作簿中的“Sheet2”改名为“基本工资”。根据企业规定，基本工资是根据学历发放的，输入相应数值，如图所示。 4.1.4 建立“岗位工资”表 冀中职业学院 任务 4.1 建立相关数据信息表 把“工资管理”工作簿中的“Sheet3”工作表改名为“岗位工资”,本企业规定基本工资是根据员工的职务类别发放的，根据企业制定的标准，输入相应数值，如图所示。 4.1.5 建立“奖金”表 冀中职业学院 任务 4.1 建立相关数据信息表 在“工资管理”工作簿中新建工作表，命名为“奖金”,根据企业制定的标准，输入相应数值，如图所示。 4.1.6 建立“出勤表” 冀中职业学院 任务 4.1 建立相关数据信息表 在“工资管理”工作簿中新建工作表，命名为“出勤表”,输入企业员工的病假和事假天数。根据企业规定，病假一天扣30元，事假一天扣50元。在E2单元格输入公式“=C2*30+D2*50”,然后把鼠标光标放到E2单元格的右下角，当光标变成黑色的填充柄“十”时，拖动鼠标直至这一列的最后一个单元格E90,计算所有员工的“出勤扣款”数值。如图所示。 4.2.1 基本函数的使用 冀中职业学院 任务 4.2 制作工资管理工作表 在计算员工工资表中的各个工资项目时需要用到IF函数、INT函数、NOW函数和VLOOKUP 函数，所以先介绍一下这四个函数的基本用法。 1. IF 函数简介 (1)IF 函数语法格式。 IF(logical_test, value_if_true, value_if_false) Logical_test 表示计算结果为TRUE或FALSE的任意值或表达式。 ① Value_if true 是 logical test 为 TRUE时返回的值。 ② Value_if_false 是 logical_test 为 FALSE 时返回的值。 (2)IF 函数的功能。 IF是条件判断函数，IF(测试条件，结果1,结果2),即如果满足“测试条件”则显示“结果1”,如果不满足“测试条件”则显示“结果2”。 在 Excel中，函数IF可以嵌套七层，在 Excel中可以嵌套更多层，用 value_if_false 及value_if true参数可以构造复杂的检测条件。 4.2.1 基本函数的使用 冀中职业学院 任务 4.2 制作工资管理工作表 2.INT 函数简介 (1)INT 函数语法格式。 INT(number) Number表示需要进行向下舍入取整的实数。 (2)INT 函数的功能。 将数值向下取整为最接近的整数。 举例：INT(8.9),将8.9向下舍入到最接近的整数(8); INT(-8.9),将-8.9向下舍入到最接近的整数(-9)。 4.2.1 基本函数的使用 冀中职业学院 任务 4.2 制作工资管理工作表 3.NOW 函数简介 (1)NOW 函数语法格式。 NOWO 在默认情况下，1900年1月1日的序列号是1,若当前系统日期为2016-4-12,对应的序列号显示为42472。如果在输入函数前，单元格的格式为“常规”,则结果为日期格式，按“Ctrl+Shift+1”可以切换为序列号。 (2)NOW函数的功能。 返回当前的日期和时间所对应的序列号。 4.2.1 基本函数的使用 冀中职业学院 任务 4.2 制作工资管理工作表 4.VLOOKUP 函数 (1)VLOOKUP 函数语法格式。 VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) VLOOKUP(查找目标，查找范围，返回值的列数，精确 OR模糊查找) ① lookup_value 为要查找的数值。 ②table_array为所要查找的目标数组区域。 ③col_index_num 为满足条件的单元格在目标数组区域的列序号。 ④ rang_lookup 为逻辑值。 (2)VLOOKUP 函数的功能。 VLOOKUP在表格或数值数组区域的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。 4.2.1 基本函数的使用 冀中职业学院 任务 4.2 制作工资管理工作表 (3)举例说明。 ①示例：要在一张“表一”中找到部分人员的年龄，填写到“表二”,如图1 和图 2 所示。 图1 图2 4.2.1 基本函数的使用 冀中职业学院 任务 4.2 制作工资管理工作表 ②在“表二”的B2单元格输入公式“=VLOOKUP(A2,表一!$B$1:$D$19,3,0)”。 ③查找目标“A2”:就是指定的查找的内容或单元格引用。本例中表二A列的姓名就是查找目标。我们要根据表二的“姓名”在表一中进行查找。 ④查找范围“表一!$B$1:$D$19”:指定了查找目标，如果没有说从哪里查找，Excel肯定会很为难。所以下一步就要指定从哪个范围中进行查找。VLOOKUP的第二个参数可以从一个单元格区域中查找，也可以从一个常量数组或内存数组中查找。本例中要从表一中进行查找，那么范围我们要怎么指定呢?这里也是极易出错的地方。大家一定要注意，给定的第二个参数查找范围要符合以下条件才不会出错。 4.2.1 基本函数的使用 冀中职业学院 任务 4.2 制作工资管理工作表 a.查找目标一定要在该区域的第一列。本例中查找表二的姓名，那么姓名所对应的表一的姓名列，那么表一的姓名列一定要是查找区域的第一列。在本例中，给定的区域要从第二列开始，即“$B$1:$D$19”,而不能是“$A$1:$D$19”。因为查找的“姓名”不在“$A1:SD$19”区域的第一列。 b.该区域中一定要包含返回值所在的列，本例中要返回的值是年龄。“年龄”列(表一的D列)一定要包括在这个范围内，即“$B$1:$D$19”,如果写成“$B$1:$C$19”就是错的。 4.2.1 基本函数的使用 冀中职业学院 任务 4.2 制作工资管理工作表 ⑤返回值的列数“3”。这是VLOOKUP 函数的第3个参数。它是一个整数值。它怎么得来的呢?它是“返回值”在第二个参数给定的区域中的列数。本例中我们要返回的是“年龄”,它是第二个参数查找范围“$B$1:$D$19”的第3列。这里一定要注意，列数不是在工作表中的列数(不是第4列),而是在查找范围区域的第几列。如果本例中是要查找姓名所对应的性别，第3个参数的值应该设置为多少呢?答案是2。因为性别在查找范围“$B$1:$D$19”的第2列。 ⑥精确 OR模糊查找。VLOOKUP(A2,表一!$B$1:SD$19,3,0),最后一个参数是决定函数精确和模糊查找的关键。一般为0。 4.2.2 建立“员工工资核算表” 冀中职业学院 任务 4.2 制作工资管理工作表 (1)在“工资管理”工作簿中新建工作表，命名为“员工工资核算表”。 (2)建立“员工工资核算表”结构。 在第二行建立“员工工资核算表”结构，包括工号、姓名、部门、性别、学历、职务类别、参加工作时间、基本工资、岗位工资、奖金、工龄工资、卫生费、交通补贴、出勤扣款、税前工资、个人所得税和实发工资。 (3)输入表头。 选中A1:Q1单元格，合并居中，输入“企业员工工资表”。 4.2.2 建立“员工工资核算表” 冀中职业学院 任务 4.2 制作工资管理工作表 (4)基本数据的引入。 “工号”“姓名”“部门”“性别”“学历”“职务类别”“参加工作时间”直接引用“员工基本信息表”中的相关列，如图所示。具体操作如下。 在A3 单元格输入公式“=员工基本信息表!A3”,确定后向右填充至G列。 在“员工工资核算表”的名称框中输入“A3:G91”,确定后，按“CTRL+D”组合键即向下填充至91行。 4.2.3 计算“员工工资核算表”各工资项目 冀中职业学院 任务 4.2 制作工资管理工作表 1.计算“基本工资”项目 在“任务 4.1”中已经建立了“基本工资”表，基本工资是根据学历给出的，“员工工资核算表”也有“学历”列，我们可以使用原来学习过的函数VLOOKUP函数计算基本工资列的数值。具体操作如下。 在H3单元格输入“=VLOOKUP(E3,基本工资!A:B,2,0)”,如图所示，确定后向下填充至91行即可。该公式的作用是根据E3单元格的学历情况，到“基本工资”表A:B列查找该学历，返回第2列(基本工资)对应的数值。 4.2.3 计算“员工工资核算表”各工资项目 冀中职业学院 任务 4.2 制作工资管理工作表 2.计算“岗位工资”项目 在“任务4.1”中已经建立了“岗位工资”表，岗位工资是根据职务类别给出的，“员工工资核算表”中也有“职务类别”列，我们可以使用原来学习过的函数 VLOOKUP 函数计算“岗位工资”列的数值。具体操作如下。 在I3单元格输入“=VLOOKUP(F3,岗位工资!A:B,2,0)”,如图所示，确定后向下填充至91行即可；该公式的作用是根据F3单元格的职务类别，到“岗位工资”表A:B列查找该职务类别，返回第2列(岗位工资)对应的数值。 4.2.3 计算“员工工资核算表”各工资项目 冀中职业学院 任务 4.2 制作工资管理工作表 3.计算“奖金”项目 在“任务4.1”中已经建立了“奖金”表，奖金是根据职务类别给出的，“员工工资核算表”也有“职务类别”列，我们可以使用原来学习过的函数VLOOKUP函数计算奖金列的数值。具体操作如下。 在J3单元格输入公式“=VLOOKUP(F3,奖金!A:B,2,0)”,如图所示，确定后向下填充至91行即可。该公式的作用是根据F3单元格的职务类别，到“资金”表 A:B列查找该职务类别，返回第2列(岗位工资)对应的数值。 4.2.3 计算“员工工资核算表”各工资项目 冀中职业学院 任务 4.2 制作工资管理工作表 4.计算“工龄工资”项目 在K3单元格中输入公式“=(INT(NOW)-G3)/365)*50”,如图4-2-7所示，该公式的作用是计算当前日期的序列号与参加工作日期对应的序列号的差后再除以365,并取整，计算出工龄。根据企业规定的标准，每满一年均可享受50元/月，用工龄乘以50,即计算出工龄工资，该公式能随着系统当前日期自动更新。 4.2.3 计算“员工工资核算表”各工资项目 冀中职业学院 任务 4.2 制作工资管理工作表 5.计算“卫生费”和“交通补贴”项目 在L3单元格中输入计算卫生费公式“=IF(D3="女",30,10)”,如图4-2-8所示，并向下填充至91行。该公式的作用是根据“员工工资核算表”D3单元格的性别进行判断，女员工每月补贴30元，男员工每月补贴10元。 在 M3单元格中输入计算交通补贴公式“=IF(F3="销售人员",300,150)”,如图所示，并向下填充至91行。该公式的作用是：根据“员工工资核算表”F3单元格的职务类别，销售人员每月补贴300元，其余人员每月补贴150元。 4.2.3 计算“员工工资核算表”各工资项目 冀中职业学院 任务 4.2 制作工资管理工作表 6.计算“出勤扣款” 在“任务 4.1”的“出勤表”中已经计算出每个人的出勤扣款的数值，现在只需要把出勤表中的数值引用到“员工工资核算表”的“出勤扣款”列就可以了。在N3单元格输入公式“=出勤表!E2”,如图所示，这样复制数据信息可以自动更新。 4.2.3 计算“员工工资核算表”各工资项目 冀中职业学院 任务 4.2 制作工资管理工作表 7.计算“税前工资”项目 在O3单元格输入公式“=SUM(H3:M3)-N3”,如图4-2-10所示，并向下填充至91行。该公式表示：税前应发工资=(基本工资+岗位工资+奖金+工龄工资+卫生费+交通补贴)-出勤扣款。 4.2.3 计算“员工工资核算表”各工资项目 冀中职业学院 任务 4.2 制作工资管理工作表 8.计算“个人所得税” 常用的个税计算公式是 个税=应纳税额*适用税率-速算扣除数 应纳税额=工资总额一免征额—专项扣除一专项附加扣除一依法确定的其他扣除其中，工资总额是指各单位在一定时期内(一般是一个月)直接支付给本单位全部职工的劳动报酬总额，包括计时工资、计件工资、奖金、津贴和补贴、加班加点工资、特殊情况下支付的工资等项目。个人社保缴交额、住房公积金个人缴交额即为员工当月应缴交的社保、住房公积金个人部分总额，由企业代缴代扣。 4.2.3 计算“员工工资核算表”各工资项目 冀中职业学院 任务 4.2 制作工资管理工作表 目前实施的个人所得税法律制度中规定，居民个人的综合所得，以每纳税年度收入额减除费用6万元(年免征额)以及专项扣除、专项附加扣除和依法确定的其他扣除后的余额，为应纳税所得额。综合所得包括工资、薪金所得、劳务报酬所得、稿酬所得、特许权使用费所得4项。劳务报酬所得、稿酬所得、特许权使用费所得以收入减除 20%费用后的余额为收入额。稿酬所得的收入额减按70%计算。 4.2.3 计算“员工工资核算表”各工资项目 冀中职业学院 任务 4.2 制作工资管理工作表 以2018年10月1日开始实行的个税的计算为例，以便大家更好地理解，假设员工工资总额为10000元，社保个人缴交额为1000元，住房公积金个人缴纳额为500元。则其应纳税额为10000-1000-500-5000=3500元，则参考“2018年10月1日开始实行的个税税率表”,适用税率为10%，速算扣除数为210元，其应缴纳的个人所得税为3500*10%-210=140元。 4.2.3 计算“员工工资核算表”各工资项目 冀中职业学院 任务 4.2 制作工资管理工作表 在“员工工资核算表”中计算“个人所得税”,将P3单元格的公式设置为 “=ROUND(MAX((O3-5000)*1?3,10,20,25,30,35,45}-{0,210,1410,2660,4410,7160,15160},0),2)”,如图所示。 再将 P3单元格的公式填充至 P91单元格即可。 4.2.3 计算“员工工资核算表”各工资项目 冀中职业学院 任务 4.2 制作工资管理工作表 个税 Excel 公式分解说明如下。 (1)“1%*{3,10,20,25,30,35,45}”这部分为“税率”,分别为3%，10%，20%，25%，30%，35%，45%。 (2)“{0,210,1410,2660,4410,7160,15160}”这部分为“速算扣除数”,分别为0,210,1410,2660,4410,7160,15160。 (3)取最大值函数(MAX):“MAX((A1-B1-C1-5000)*1?3,10,20,25,30,35,45}- {0,210,1410,2660,4410,7160,15160},0)”这一部分是个人工资薪金收入减去“五险一金(B1)”“专项附加扣除数(C1)”“起征点”后分别乘以7个税率，再减去对应的速算扣除数，将最后得到的七个数据取最大值。 A1:“个人工资薪金收入”。 B1:“五险一金”个人承担部分。 C1:专项附加扣除，包括子女教育、继续教育、大病医疗、住房贷款利息或者住房租金、赡养老人和婴幼儿照护七项专项附加扣除。前六项专项附件扣除自2019年1月1日实施。2022年3月28日国务院决定设立3岁以下婴幼儿照护专项附加扣除，自2022年1月1日起实施。注：目前暂设为0。 4.2.3 计算“员工工资核算表”各工资项目 冀中职业学院 任务 4.2 制作工资管理工作表 (4)最外层的函数：四舍五入保留2位小数。 “=ROUND(MAX((A1-B1-C1-5000)*1?3,10,20,25,30,35,45}-{0,210,1410,2660,4410,7160,15160},0),2)”是将前面得到的最大值四舍五入，保留两位小数(角、分),即分以下的数值四舍五入。 这个公式非常复杂，记起来不容易，实际上大家只需要把这个公式保存下来，理解“ O3”代表的“税前工资”,在工作中实际用到的工资表中只需要看一下“税前工资”是哪列的值，把列标替换了就可以。 4.2.3 计算“员工工资核算表”各工资项目 冀中职业学院 任务 4.2 制作工资管理工作表 9.计算“实发工资”项目 实发工资=应发工资-个人所得税，在 Q3单元格中输入公式“=ROUND((O3-P3),2)”,如图所示，并向下填充至91行。该公式的作用是，对实发工资四舍五入取2位小数。 4.2.3 计算“员工工资核算表”各工资项目 冀中职业学院 任务 4.2 制作工资管理工作表 10.制作工资条 工资条的制作工资条的最终结果就是一行工资细目，一行员工对应的数据，通常做好的工资表都是只有第一行有数据工资细目，下面全部是数据。如果能从第二行开始，每一行的结尾都添加一行工资细目的数据就好了。 方法一：使用排序法制作工资条。操作步骤如下。 (1)复制“员工工资核算表”的数据。按住Ctrl键拖动“员工工资核算表”工作表名称，就会复制一个副本，把此副本命名为“工资条”。 (2)在“工资条”工作表的“实发工资”列的右侧添加“辅助列”,在辅助列(R列)输入1~89,因为有89个员工，选中这一列数字，按“Ctrl+C”复制，到R列连续复制两次。 (3)复制表头。选中第二行，右击然后单击“复制”。 (4)在A181单元格，右击后单击“粘贴”,把复制的表头粘贴到A181:Q181 单元格区域，然后把此行内容填充至A269:Q269单元格区域。 (5)选中辅助列R列的所有数字，单击“数据”选项卡的“排序和筛选”分组中的“升序”,弹出“排序”对话框，主要关键字选择“辅助列”,选择“升序”,点击“确定”,工资条就做好了。 (6)选中辅助列，删除辅助列。这个方法适合职工人数较少时，使用排序法做工资条简单易行，不用记忆复杂的公式。 4.2.3 计算“员工工资核算表”各工资项目 冀中职业学院 任务 4.2 制作工资管理工作表 方法二：使用公式法制作工资条。 当一个企业人数比较多时，使用排序法制作工资条就不方便了，这个时候可以使用公式来制作工资条。操作步骤如下。 (1)单击“插入工作表”插入一个新工作表，命名为“工资条”。 (2)在“工资条”的A1单元格中输入公式“A1=IF(MOD(ROW0,3)=0,"",IF(MOD(ROW0,3)=1,员工工表!A$2,INDEX(员工工资核算表!$A$2:SQ$91,(ROW0+4)/3,COLUMNO)))”。 解读公式： “=IF(MOD(ROW0,3)=0,"","")”,即显示空行； “=IF(MOD(ROW),3)=1,员工工资核算表!A$2,"")”,即显示标题行； “=IF(MOD(ROW(,3)=2,INDEX(员 工 工 资 核 算 表!$A$2:$Q$91,(ROW)+4)/3, COLUMNO,"")“,即显示数据行。 (3)把此公式向右填充至Q列，向下填充至267行，工资条就做好了。 4.3.1 工资数据的查询(筛选) 冀中职业学院 任务 4.3 工资数据的查询与汇总分析 如果要利用筛选功能进行工资数据查询，首先要进入筛选状态。因为“员工工资核算表”工作表的第一行标题是合并的单元格，所以要使用筛选功能时，需要选中工号、姓名等所在的字段行，才可以单击“数据”选项卡中“排序和筛选”组的“筛选”命令，如图所示。 4.3.1 工资数据的查询(筛选) 冀中职业学院 任务 4.3 工资数据的查询与汇总分析 1.以“职工姓名”为依据进行查询 例如，查找职工中姓“王”的职工的工资情况。 第一步：单击“姓名”列按钮，单击“文本筛选”中的“等于”,如图所示。 4.3.1 工资数据的查询(筛选) 冀中职业学院 任务 4.3 工资数据的查询与汇总分析 第二步：输入要查找的姓，如图1所示，输入“王*”,“*”代表任意多个字符，即找出所有姓“王”的职工的记录，查询结果如图 2所示。 图1 图2 4.3.1 工资数据的查询(筛选) 冀中职业学院 任务 4.3 工资数据的查询与汇总分析 2.以“部门”为依据进行查询 例如，查询“生产部”所有职工的工资情况。 操作步骤是：单击“部门”列按钮，先取消“全选”,然后单击“生产部”,如图1所示，查询结果如图2所示。 图1 图2 4.3.1 工资数据的查询(筛选) 冀中职业学院 任务 4.3 工资数据的查询与汇总分析 3.以“性别”和“实发工资”为依据进行查询 例如，查询男性职工中实发工资小于4000元的职工情况。 第一步：单击“性别”列按钮，先取消“全选”,然后单击“男”,如图1所示，查询结果如图2所示。 图1 图2 4.3.1 工资数据的查询(筛选) 冀中职业学院 任务 4.3 工资数据的查询与汇总分析 第二步：单击“实发工资”列按钮，单击“数字筛选”中“自定义筛选”,如图所示。 4.3.1 工资数据的查询(筛选) 冀中职业学院 任务 4.3 工资数据的查询与汇总分析 第三步：输入“实发工资小于4000元”的筛选条件，如图1所示，查询结果如图2所示。 图1 图2 4.3.2 工资数据的汇总分析(数据透视表) 冀中职业学院 任务 4.3 工资数据的查询与汇总分析 运用 Excel 的数据透视表功能对职工工资的基本数据进行处理，可以方便、快捷地对这些数据进行分析，为管理者提供很大的帮助。 1.计算每一部门每一职务类别“实发工资”的汇总数操作步骤如下。 第一步：打开“工资管理”工作簿文件中的“员工工资核算表”工作表，选中数据列表中的任一有数据的单元格，单击“插入”选项卡中“表格”分组里的“数据透视表”按钮，进入“创建数据透视表”对话框。被选中的数据区域地址显示在“选择一个表或区域”框内。如图所示。 4.3.2 工资数据的汇总分析(数据透视表) 冀中职业学院 任务 4.3 工资数据的查询与汇总分析 第二步：确认数据区域。由于此工作表第一行是合并的单元格，将“表/区域”文本框中默认值“员工工资核算表!SA$1:$A$91”更改为“员工工资核算表!$A$2:SQ$91”。确认数据区域正确后，选择“选择放置数据透视表的位置”,在这里选择“新工作表”,单击对话框的“确定”按钮即可。 完成透视表的创建过程后，自动在当前工作表的左侧添加新的工作表标签，同时显示“数据透视表字段列表”工具栏，如图所示。 4.3.2 工资数据的汇总分析(数据透视表) 冀中职业学院 任务 4.3 工资数据的查询与汇总分析 第三步：将“部门”拖至“行标签”,将“职务类别”拖至“列标签”,将“实发工资”拖至“数值”,如图所示。 4.3.2 工资数据的汇总分析(数据透视表) 冀中职业学院 任务 4.3 工资数据的查询与汇总分析 将产生“实发工资”按部门与职务类别的数据汇总表，如图1所示。 图1 第四步：移动鼠标光标到数据透视表的任一单元格，在功能选项卡区域就会出现“数据透视表工具”选项卡，单击此选项卡下面的“选项”选项卡中“工具”分组中的“数据透视图”按钮，选择“柱形图”中的第一种图形，则在当前工作表中生成一张数据透视图，如图2所示。 图2 4.3.2 工资数据的汇总分析(数据透视表) 冀中职业学院 任务 4.3 工资数据的查询与汇总分析 2.计算每一部门每一职务类别“实发工资”的平均数 在“数据透视表字段列表”工具栏的“求和项”处，右击，在弹出的快捷菜单中单击选择“值字段设置”选项，打开“值字段设置”对话框，在“值汇总方式”选择“平均值”,如图1所示。汇总结果如图2所示。 图1 图2 4.3.2 工资数据的汇总分析(数据透视表) 冀中职业学院 任务 4.3 工资数据的查询与汇总分析 3.计算每一部门每一职务类别“实发工资”的汇总数占“实发工资”总和的百分比 打开“值字段设置”对话框，“值汇总方式”选择“求和”,设置“值显示方式”为“全部汇总百分比”选项，如图1所示，最终结果如图2所示。 图1 图2 4.3.2 工资数据的汇总分析(数据透视表) 冀中职业学院 任务 4.3 工资数据的查询与汇总分析 如果选择“列汇总百分比”或“行汇总百分比”,还可以计算同一职务类别在不同部门“实发工资”占此类别“实发工资”总和的百分比，如图1所示.或同一部门的不同职务类别“实发工资”占此部门“实发工资”总和的百分比，如图2所示。 图1 图2 子任务 4.3.3 数据透视表的分组 冀中职业学院 任务 4.3 工资数据的查询与汇总分析 需要统计实发工资在3000~4000、4000~5000、5000~6000、6000~7000的人数，如下图所示。 子任务 4.3.3 数据透视表的分组 冀中职业学院 任务 4.3 工资数据的查询与汇总分析 第一步：定位光标，单击选中数据列表中的任一有数据的单元格。 第二步：单击“插入”选项卡中“表格”分组里的“数据透视表”按钮。 第三步：打开“创建数据透视表”对话框。确认数据区域为“员工工资核算表!$A$2:SQ$91”,如右图所示，在“选择放置数据透视表的位置”,选择产生在“新工作表”上，单击对话框的“确定”按钮即可。 子任务 4.3.3 数据透视表的分组 冀中职业学院 任务 4.3 工资数据的查询与汇总分析 第四步：将“实发工资”拖至行标签，再将“实发工资”拖至数值区。在数值区更改“值字段设置”为“计数”,如图1所示。 第五步：在数据透视表的布局区域“行标签”列的任一位置右击，在弹出的菜单中单击“组合”,起始值输入“3000”,终止值输入“7000”,步长值输入“1000”,如图2所示，单击“确定”。最终结果如图3所示。 图1 图2 图3 子任务 4.4.1 任务导入 冀中职业学院 任务 4.4 工资数据的小数问题 小张已经习惯使用 Excel 电子表格记账，并且使用 Excel 统计数据，这样处理数据比原来的手工做账效率高、准确率高、不容易出错。可是有一天她在处理“2014—2015年绩效工资第一次补发表”数据时，出现了偏差。263个人的“2014—2015年绩效工资应发小计”合计在电子表格中显示的是“3024344”,可是一个老会计一直信不准电脑，他用计算器计算出的数值是“3024287”,这两个数值之间相差57。两个会计都认为自己做的没有错误，领导说老会计算的正确，银行按照公司给的每个人的数据发下去的钱确实是“3024287”,也就是说小张做错了，可是直接用SUM函数求和没有错误，这到底是怎么回事呢?经过仔细观察，小张发现了问题，原来在显示上虽然每个人的工资数都是整数，但其实每个人的工资都有小数位，这些小数加在一起就多出来了“57”元。问题找到了，如何来解决呢？ 子任务 4.4.2 任务操作步骤 冀中职业学院 任务 4.4 工资数据的小数问题 情况一 步骤一：打开“绩效工资补发未修改”工作簿文件，选中C列，右击选中的区域，在弹出的快捷菜单中选择“插入”,在C列的左侧插入一个辅助列。 步骤二：单击选中C4单元格，单击“插入函数”,弹出“插入函数”对话框，在“选择类别”的下拉列表选择“数学与三角函数”,在“选择函数”框中选择“ROUND”函数，如右图所示。 子任务 4.4.2 任务操作步骤 冀中职业学院 任务 4.4 工资数据的小数问题 情况一 步骤三：单击“确定”按钮。ROUND函数参数输入如下图所示。C4单元格的编辑框显示公式“=ROUND(B4,0)”,这个函数的含义是指对B4单元格的值进行四舍五入，保留0位小数。 子任务 4.4.2 任务操作步骤 冀中职业学院 任务 4.4 工资数据的小数问题 情况一 步骤四：由于人数比较多，对C4:C262 单元格区域进行填充的时候，可以使用快速填充的方法。在名称框中输入“C4:C262”,然后按回车键，就选中了此区域。选中此区域后，可以使用“Ctrl+D”组合键向下快速填充公式，如右图所示。 步骤五：单击选中C263单元格，输入“=SUM(C4:C262)”,求出去掉小数位以后的合计为“3024287”,与原来的合计“3024344”相差57元。 子任务 4.4.2 任务操作步骤 冀中职业学院 任务 4.4 工资数据的小数问题 情况一 步骤六：把计算好的数值替代原来的数值。在名称框中输入“C4:C263”,按回车键选中此区域，在选中的区域右击，在弹出的快捷菜单中单击“复制”,在B4单元格右击，在弹出的菜单中选择“选择性粘贴”中的“数值”命令，就把做好的数值复制好了。 步骤七：删除辅助列C列。单击C列列标，右击，在弹出的菜单中单击“删除”即可，最终的结果如右图所示。 子任务 4.4.2 任务操作步骤 冀中职业学院 任务 4.4 工资数据的小数问题 情况二 以上案例中是使用ROUND函数对指定的位数进行四舍五入。但在生活中也可能遇到要求舍去所有小数位，直接取整的情况，那应该如何做呢? 在这里，直接取整，只需要把情况一中步骤二的ROUND函数，换成INT函数即可。INT(x)函数的含义是得到一个不大于X的最大整数。即在C4单元格输入公式“=INT(B4)”。 或者使用TRUNC函数也可以。TRUNC函数的功能是将数字截为整数或指定位数的小数，即在 C4单元格输入公式“=TRUNC(B4)”。 情况三 如果遇到要求有小数位，就近位直接取整的情况，应该如何做呢? 在这里，只要有小数位就进位，只需要把步骤二的ROUND函数，换成 ROUNDUP函数即可。ROUNDUP 函数的功能是向上舍入数字，即只要有小数就进位。即在C4单元格输入公式“=ROUNDUP(B4,0)”。 子任务 4.5.1 任务导入 冀中职业学院 任务 4.5 批量制作个人薪级工资调整表 小马是某个单位的会计，这一天他精神饱满地准时来到公司上班。同事微笑着向他问好，大家听说要涨工资了。小马泡好一杯咖啡，正想喝，发现办公桌上放着他的上司钱科长给他留的一张便条，便条上写着“昨天你应该完成的‘个人薪级工资调整表’怎么还没完成，限你半小时内完成”。 小马浮想联翩，耽误单位员工工资薪级调整，会牵出一堆麻烦事：单位员工有意见→工作热情下降→工作效率降低→自己被炒鱿鱼。 如何在短时间内完成200个员工的“个人薪级工资调整表”? 可能答案1:先建1张空白表格，再复制200个，再在表格中逐个输入数据，缺陷是时间来不及，不能保证100输入正确。 可能答案2:与同事合作，共同完成(每个人制作若干份薪级审批个人表，再把文档合并)。缺陷是每个人手头上都有工作，不能体现个人工作能力。 子任务 4.5.2 任务操作步骤 冀中职业学院 任务 4.5 批量制作个人薪级工资调整表 这时可以使用WORD的邮件合并功能来完成此工作，这是一个利用Excel软件和Word 软件共同完成的任务。 邮件合并就是“批量生产”邮件，如寄发录取通知书、成绩单、准考证、薪级工资审批表等。邮件合并的制作过程是：先把固定的内容编制成一份 Word文档，把变化的部分制作成 Excel表，然后将Excel表中的数据融合到Word文档中，执行合并命令后系统就会自动产生一个含所有记录的 Word 文档。 子任务 4.5.2 任务操作步骤 冀中职业学院 任务 4.5 批量制作个人薪级工资调整表 步骤一：事先已经做好“河北省事业单位工作人员调整工资审批表”Word文件(主文档)，在这个 Word文件中先把每个人固定的内容都设置好，这个文件就是主文档，如右图所示。 子任务 4.5.2 任务操作步骤 冀中职业学院 任务 4.5 批量制作个人薪级工资调整表 步骤二：建立含一份“薪级工资审批总表”的Excel表(建立数据源)。在建立 Excel工作表时，必须保证数据文件是数据库格式，即第一行必须是字段名，数据行中间不能有空行等。现有的 Excel文件如图1所示。 从图1可以看出，第一行是合并的单元格，且并非字段行，这样的文件不能直接作为邮件合并的数据源文件，必须修改表头，修改结果如图2所示。 图1 图2 子任务 4.5.2 任务操作步骤 冀中职业学院 任务 4.5 批量制作个人薪级工资调整表 步骤三：在打开的“个人薪级工资调整表”Word文件中，单击“开始邮件合并”后面的下拉三角，在弹出的菜单中单击“信函”,下一步单击“选择收件人”中的“使用现有列表”,打开“薪级工资审批总表”文件，如图1所示。弹出此 Excel文件中的两个工作表，如图2所示。选择要使用的数据源“岗位薪级审批总表修改好”工作表，单击“确定”按钮。 图1 图2 子任务 4.5.2 任务操作步骤 冀中职业学院 任务 4.5 批量制作个人薪级工资调整表 步骤四：将插入光标移到域内容应当出现的位置，单击“插入合并域”命令，如图1所示。在下拉列表框选择合适的域，比如当光标所在的位置应当是姓名，就选择域“姓名”。重复上述过程直至加入所有的域，如图2所示。当前只看到域名称，如何才能看到真实的“姓名”等信息呢? 图1 图2 子任务 4.5.2 任务操作步骤 冀中职业学院 任务 4.5 批量制作个人薪级工资调整表 步骤五：单击“预览结果”,通过预览合并效果，就能逐个显示“个人薪级工资调整表”。 步骤六：单击“完成并合并”下拉三角下边的“编辑单个文档”,弹出“合并到新文档”对话框，如图1所示。根据要求合并所有记录，单击“确定”按钮，就自动生成了所有人的“个人薪级工资调整表”,如图2所示。 步骤七：将制作好的合并文档保存并命名。 图1 图2 $$