Excel在财务中的应用-课件

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