Excel在财务中的应用

《Excel在财务中的应用》配套课件编者：张颖章莹项目四Excel在薪酬管理中的应用Excel在财务中的应用书名：Excel在财务中的应用书号：978-7-111-57590-0作者：张颖出版社：机械工业出版社原先公司也有工资计算的表格，但是一旦增加或减少了员工，或者奖金等发生了调整，工资表就又要重新编制，这就失去了Excel的作用。因此李燕决定重新编制职工薪资管理系统表格，通过设置多个原始表格，利用Excel表格强大的数据处理能力，让薪资管理变得简单便捷。李燕决定要完成以下设置：一是建立工资管理框架，包括编制员工信息表、编制员工考勤信息表、编制固定薪资信息表等；二是完成工资信息输入；三是完成工资条的查询与打印。工作情境1.建立工资管理系统表格2.设置账户3.输入记账凭证4.生成科目汇总表：根据记账凭证的业务计算出本期借贷方发生额，在试算平衡的基础上，计算出期末借贷方余额。5.建立总账汇总表：利用静态复制和动态数据透视表功能自动生成总账汇总表。工作任务分析一、工资管理系统表格1.利用“套用表格格式”功能来完成2.转换成表格后可以自动生成公式和格式。重点提示：（一）公司职工档案图1公式职工档案表格一、工资管理系统表格（二）职工通讯费补贴通讯费补贴标准：函数语言：重点提示：公式中用到了AND和OR函数，在前面Excel基础操作中也提到过，可以参见逻辑函数那部分的介绍。一般它们都和其他函数结合起来使用为多。总经理500元，学历研究生及以上且职称为高级或者是部门经理都是300元，其他职工150元。=IF(G2=总经理,500,IF(OR(AND(D2=研究生,E2=高级),G2=部门经理),300,150))一、工资管理系统表格（三）薪资奖惩标准表重点提示：将表格的网格线去掉的做法是：选择【视图】选项卡，将网格线前面的小方框的勾去掉，整个工作表就会呈现白纸状态。另外也可以按照自己的习惯来调整行高。一、工资管理系统表格（四）职工考勤奖惩表重点提示：表格之间的取数：因为当时已经把“公司职工档案”设置成表格形式的，所以在编辑栏中显示出的是“=表1[@序号]”，代表的意思就是从表格1中取序号这一列的数据。单元格公式含义B2=VLOOKUP($A2,职工信息,2,0)根据序号从“职工信息”名称范围中找对应的职工姓名C2=VLOOKUP($A2,职工信息,6,0)根据序号从“职工信息”名称范围中找对应的职工部门D2=VLOOKUP($A2,职工信息,7,0)根据序号从“职工信息”名称范围中找对应的职工职务E2=VLOOKUP($A2,职工信息,10,0)根据序号从“职工信息”名称范围中找对应的职工工资（含津贴）“职工考勤奖惩表”取数公式及含义一、工资管理系统表格（四）职工考勤奖惩表重点提示：ROUND/ROUNDUP/ROUNDDOWN函数的用法：三者的语法格式均为：(number,num_digits)奖惩公式一览表单元格公式F2=IF((I2=0)*(J2=0)*(K2=0),薪资奖惩标准表!$C$4,0)G2=ROUND($E2/30*0.1*$I2+$E2/30*0.5*$J2+$E2/30*$K2,2)H2=300*$L2+200*$M2二、计算职工工资汇总表（一）职工工资汇总表表头（二）职工工资汇总表取数单元格公式B2=VLOOKUP($A2,考勤,2,0)C2=VLOOKUP($A2,考勤,3,0)D2=VLOOKUP($A2,考勤,4,0)E2=VLOOKUP($A2,考勤,5,0)F2=VLOOKUP($A2,考勤,6,0)G2=VLOOKUP($A2,考勤,7,0)H2=VLOOKUP($A2,考勤,8,0)I2=VLOOKUP($A2,职工信息,11,0)取数公式二、计算职工工资汇总表（三）职工工资汇总表五险一金的计算图2五险一金的计算结果重点提示：应发工资、社保、公积金、应纳税所得额的计算都比较简单。二、计算职工工资汇总表（三）职工工资汇总表个人所得税的计算重点提示：以上用到了IF嵌套函数的运用1.If函数计算个税单元格公式N2=IF(M20,0,IF(M21500,M2*3%,IF(M24500,M2*10%-105,IF(M29000,M2*20%-555,IF(M235000,M2*25%-1005,IF(M255000,M2*30%-2755,IF(M28000,M2*35%-5505,M2*45%-13505)))))))二、计算职工工资汇总表（三）职工工资汇总表个人所得税的计算重点提示：以上用到了IF结合AND函数的运用1.If函数计算个税单元格公式N2=IF(M20,0,)&IF(AND(M2=0,M2=1500),M2*3%,)&IF(AND(M21500,M2=4500),M2*10%-105,)&IF(AND(M24500,M2=9000),M2*20%-555,)&IF(AND(M29000,M2=35000),M2*25%-1005,)&IF(AND(M235000,M2=55000),M2*30%-755,)&IF(AND(M255000,M2=80000),M2*35%-5505,)&IF(M280000,M2*45%-13505,)二、计算职工工资汇总表（三）职工工资汇总表个人所得税的计算重点提示：以上用到了VLOOKUP函数的模糊查找功能2.VLOOKUP函数计算个税单元格公式N2=IF(M20,0,M2*(VLOOKUP(M2,薪资奖惩标准表!$J$3:$L$9,2,1))-VLOOKUP(M2,薪资奖惩标准表!$J$3:$L$9,3,1))二、计算职工工资汇总表（三）职工工资汇总表个人所得税的计算重点提示：以上用到了MAX函数，相对比较简单3.MAX函数计算个税单元格公式N2=MAX(M2*0.03,M2*0.1-105,M2*0.2-555,M2*0.25-1005,M2*0.3-2755,M2*0.35-5505,M2*0.45-13505,0)二、计算职工工资汇总表（三）职工工资汇总表个人所得税的计算重点提示：1.以上用到了数组函数，这是本项目重点，需要重点掌握。4.数组函数计算个税单元格公式N2=ROUND(MAX(M2*{0.03,0.1,0.2,0.25,0.3,0.35,0.45}-{0,105,555,1005,2755,5505,13505},0),2)2.等号后面的花括号是通过键盘输入的，而等号外面一层花括号是通过执行【Ctrl+Shift+Enter】组合键实现的。二、计算职工工资汇总表（四）职工工资汇总表实发工资的计算图3工资汇总表三、薪资的查询（一）薪资信息的查询图4自动筛选生产车间的薪资水平1.自动筛选1.如果是表格的创建中，会在首行自动创建带有“筛选”按扭重点提示：2.如果是普通单元格区域，可以选择需要筛选的标题行，执行【数据】|【筛选】，也可以直接在【开始】选项卡中执行【排序和筛选】|【筛选】。三、薪资的查询（一）薪资信息的查询2.自定义筛选图5筛选该公司实发工资前五名的员工三、薪资的查询（一）薪资信息的查询3.高级筛选高级筛选需要在筛选的源数据区域外的空白区域中手动设定筛选条件区域，且条件区域的结果需要满足以下条件：1.条件区域的第一行为条件的列标签行，需要与筛选的源数据区域的筛选条件列标签相同；在该列标签下方，至少应包含一行具体的筛选条件。2.如果字段具有两个或以上筛选条件时，可以在条件区域中对应的列标签下方单元格依次列出各个条件，各条件间的逻辑关系为“或”；但若在同一行对应的列标签下输入各个条件，则各条件间的逻辑关系为“与”。因此，若要筛选满足多组条件之一的情况，可将各组条件输入在条件区域的不同行上。三、薪资的查询（一）薪资信息的查询3.高级筛选——“逻辑与”图6生产车间员工中“加班工资”不为零的员工信息三、薪资的查询（一）薪资信息的查询3.高级筛选——“逻辑或”图7实发工资在3500至4500元以及经理级别员工的薪资情况三、薪资的查询（二）职工工资条查询1.利用VLOOKUP函数进行查询图8利用VLOOKUP函数实现职工工资条查询三、薪资的查询（二）职工工资条查询2.利用OFFSET和MATCH函数进行查询图9利用MATCH函数实现职工工资条查询三、薪资的查询（三）工资条的制作1.利用函数制作全员工资条=IF(MOD(ROW(A1),3)0,OFFSET($A$1,IF(MOD(ROW(A1)-1,3),ROUNDUP(ROW(A1)/3,0),0),COLUMN(A1)-1),)2.利用排序功能制作全员工资条3.利用定位功能制作全员工资条