Excel在财务中的应用

1、项目四 Excel在薪酬管理中的应用,Excel在财务中的应用,书名：Excel在财务中的应用 书号：978-7-111-57590-0 作者：张颖 出版社：机械工业出版社,原先公司也有工资计算的表格，但是一旦增加或减少了员工，或者奖金等发生了调整，工资表就又要重新编制，这就失去了Excel的作用。因此李燕决定重新编制职工薪资管理系统表格，通过设置多个原始表格，利用Excel表格强大的数据处理能力，让薪资管理变得简单便捷。 李燕决定要完成以下设置：一是建立工资管理框架，包括编制员工信息表、编制员工考勤信息表、编制固定薪资信息表等；二是完成工资信息输入；三是完成工资条的查询与打印。,工作情境,1

2、. 建立工资管理系统表格 2. 设置账户 3. 输入记账凭证 4. 生成科目汇总表：根据记账凭证的业务计算出本期借贷方发生额，在试算平衡的基础上，计算出期末借贷方余额。 5. 建立总账汇总表：利用静态复制和动态数据透视表功能自动生成总账汇总表。,工作任务分析,一、工资管理系统表格,1.利用“套用表格格式”功能来完成,2.转换成表格后可以自动生成公式和格式。,重点提示：,（一）公司职工档案,图1 公式职工档案表格,一、工资管理系统表格,（二）职工通讯费补贴,通讯费补贴标准：,函数语言：,重点提示：,公式中用到了AND和OR函数，在前面Excel基础操作中也提到过，可以参见逻辑函数那部分的介绍。一

3、般它们都和其他函数结合起来使用为多。,总经理500元，学历研究生及以上且职称为高级或者是部门经理都是300元，其他职工150元。,=IF(G2=总经理,500,IF(OR(AND(D2=研究生,E2=高级),G2=部门经理),300,150),一、工资管理系统表格,（三）薪资奖惩标准表,重点提示：,将表格的网格线去掉的做法是：选择【视图】选项卡，将网格线前面的小方框的勾去掉，整个工作表就会呈现白纸状态。另外也可以按照自己的习惯来调整行高。,一、工资管理系统表格,（四）职工考勤奖惩表,重点提示：,表格之间的取数：因为当时已经把“公司职工档案”设置成表格形式的，所以在编辑栏中显示出的是“=表1序号

4、”，代表的意思就是从表格1中取序号这一列的数据。,“职工考勤奖惩表”取数公式及含义,一、工资管理系统表格,（四）职工考勤奖惩表,重点提示：,ROUND/ROUNDUP/ROUNDDOWN函数的用法： 三者的语法格式均为：(number,num_digits),奖惩公式一览表,二、计算职工工资汇总表,（一）职工工资汇总表表头,（二）职工工资汇总表取数,取数公式,二、计算职工工资汇总表,（三）职工工资汇总表五险一金的计算,图2 五险一金的计算结果,重点提示：,应发工资、社保、公积金、应纳税所得额的计算都比较简单。,二、计算职工工资汇总表,（三）职工工资汇总表个人所得税的计算,重点提示：,以上用到了

5、IF嵌套函数的运用,1.If函数计算个税,二、计算职工工资汇总表,（三）职工工资汇总表个人所得税的计算,重点提示：,以上用到了IF结合AND函数的运用,1.If函数计算个税,二、计算职工工资汇总表,（三）职工工资汇总表个人所得税的计算,重点提示：,以上用到了VLOOKUP函数的模糊查找功能,2.VLOOKUP函数计算个税,二、计算职工工资汇总表,（三）职工工资汇总表个人所得税的计算,重点提示：,以上用到了MAX函数，相对比较简单,3.MAX函数计算个税,二、计算职工工资汇总表,（三）职工工资汇总表个人所得税的计算,重点提示：,1.以上用到了数组函数，这是本项目重点，需要重点掌握。,4.数组函数

6、计算个税,2.等号后面的花括号是通过键盘输入的，而等号外面一层花括号是通过执行【Ctrl+Shift+Enter】组合键实现的。,二、计算职工工资汇总表,（四）职工工资汇总表实发工资的计算,图3 工资汇总表,三、薪资的查询,（一）薪资信息的查询,图4 自动筛选生产车间的薪资水平,1.自动筛选,1.如果是表格的创建中，会在首行自动创建带有“筛选”按扭,重点提示：,2.如果是普通单元格区域，可以选择需要筛选的标题行，执行【数据】|【筛选】，也可以直接在【开始】选项卡中执行【排序和筛选】|【筛选】。,三、薪资的查询,（一）薪资信息的查询,2.自定义筛选,图5 筛选该公司实发工资前五名的员工,三、薪资

7、的查询,（一）薪资信息的查询,3.高级筛选,高级筛选需要在筛选的源数据区域外的空白区域中手动设定筛选条件区域，且条件区域的结果需要满足以下条件： 1. 条件区域的第一行为条件的列标签行，需要与筛选的源数据区域的筛选条件列标签相同；在该列标签下方，至少应包含一行具体的筛选条件。 2. 如果字段具有两个或以上筛选条件时，可以在条件区域中对应的列标签下方单元格依次列出各个条件，各条件间的逻辑关系为“或”；但若在同一行对应的列标签下输入各个条件，则各条件间的逻辑关系为“与”。因此，若要筛选满足多组条件之一的情况，可将各组条件输入在条件区域的不同行上。,三、薪资的查询,（一）薪资信息的查询,3.高级筛选“逻辑与”,图6 生产车间员工中“加班工资”不为零的员工信息,三、薪资的查询,（一）薪资信息的查询,3.高级筛选“逻辑或”,图7 实发工资在3500至4500元以及经理级别员工的薪资情况,三、薪资的查询,（二）职工工资条查询,1.利用VLOOKUP函数进行查询,图8 利用VLOOKUP函数实现职工工资条查询,三、薪资的查询,（二）职工工资条查询,2.利用OFFSET和MATCH函数进行查询,图9 利用MATCH函数实现职工工资条查询,三、薪资的查询,（三）工资条的制作,1.利用函数制作全员工资条,=IF