《Excel在财务中的应用》课件项目三

1、EXCEL在财务中的应用于凌云项目三 EXCEL在薪资管理中的应用学习目的：分析薪资管理中运用的数据；掌握数据的数据引用、计算、排序、筛选、分类汇总、数据透视表以及数据透视图等内容操作；以薪资管理表为引例，拓展应用，触类旁通。3.1 薪资表的建立3.1.1 相关数据分析计算1.薪资明细表一般包括单位人员的基本工资信息，为了便于分析和理解，我们把薪资明细表简化为以下内容，并根据计算，得出结果，如图3-1所示。保险扣款：假定保险扣款按基本工资和岗位津贴的15%扣除。（1）应发工资：用SUM函数求得。（2）保险扣款：用公式“应发工资*15%”求得。（3）考勤扣款：引用职工考勤表中的“扣款合计”。（4

2、）个人所得税扣款：引用职工纳税表的“应纳个人所得税”。（5）实发工资：用公式“应发工资-考勤扣款-保险扣款-个人所得税扣款”求得。3.1 薪资表的建立2.职工考勤表假定企业相关制度规定：病假扣款：病假大于等于10天，销售部扣1000元，非销售部扣900元，病假小于10天，销售部扣500元，非销售部扣400元。事假扣款：事假大于等于7天,扣基本工资的50%,小于7天,按实际天数扣款(每月按22天计算)。（1）病假扣款：根据要求，用公式“=IF(E3=0,0,IF(E3=10,IF(C3=销售部,1000,900),IF(C3=销售部,500,400)”求得。（2）事假扣款：根据要求，用公式“=I

3、F(F3=0,0,IF(F3=7,D3*0.5,F3*D3/22)”（3）扣款合计：用SUM函数求病假扣款、事假扣款之和。计算结果如图3-2所示。3.1 薪资表的建立3.职工纳税表个人所得税按新个税起征点3500税率参考表中扣交，见表3-1。注意：该表仅用于EXCEL中IF语句实践，工作中涉及的个税还应以个人所得税法为准。（1）应纳税工资额：根据公式“=C3+D3-3500”求得。（2）应纳个人所得税：根据以下公式求得：“=IF(E3=1500,E3*0.03,IF(E3=4500,E3*0.1-105,IF(E3=9000,E3*0.2-555,IF(E3=35000,E3*0.25-100

4、5,IF(E3=55000,E3*0.3-2755,IF(E3=80000,E3*0.35-5505,E3*0.45-13505)”计算结果如图3-3所示。3.1 薪资表的建立3.1.2 制作职工工资条职工工资条便于职工了解工资项目及工资明细，这里我们介绍一种利用VLOOKUP函数制作工资条的方法。1.VLOOKUP函数，功能：是一个查找和引用函数，可以在表格或区域中按行查找内容。3.1 薪资表的建立3.1.2 制作职工工资条2.制作工资条的步骤(1)建立“职工工资条”表，复制“薪资明细表”表头部分。(2)在“薪资明细表”中，选A2:J22,定义名称为“工资条”。(3)在“职工工资条”中，在A

5、3单元格中输入编号“001”，在B3单元格中输入公式“=VLOOKUP(A3,工资条,2,0)”。(4)选中B3填充柄向右复制公式，并依次修改首列号和所在列号。(5)选中A1:J3区域，按住填充柄，向下复制，就得出职工工资条。3.2数据排序3.2.1数据排序概述1.对文本、数字、日期类型的数据进行排序2.按多列或多行进行排序3.2数据排序在工作表中，为了醒目显示，一些数据可能设置了单元格颜色、字体颜色或单元格图标等格式，同样我们也可以用这些格式来进行排序。3.2.2按特殊格式排序在工作表中，为了醒目显示，一些数据可能设置了单元格颜色、字体颜色或单元格图标等格式，同样我们也可以用这些格式来进行排

6、序。下面我们以“职工考勤表”为例，假如，表中“”事假（天数）大于或等天5天的已经设置了“浅红填充深红文本”，要求按这种格式进行排序，步骤如下：3.2数据排序3.2.3按自定义序列排序除上述排序外，用户可以使用自定义列表的顺序进行排序。例如，“职称”列可能包含要用来排序依据的值（如“高级”、“中级”和“初级”）。如何排序才能使包含“初级”的行先显示，然后是“中级”，最后是“无”？如果按字母顺序排序，“升序”排序会“初级、高级、无、中级”排列，显然不符合要求。通过创建“自定义序列”，可以避免这一问题。3.3 筛选筛选就是根据用户要求将满足条件的数据记录通过筛选过滤出来，提高工作效率。而且还不容易发

7、生遗漏，减少差错。筛选一般分为自动筛选和高级筛选。3.3.1自动筛选1.自动筛选的类型2.以“职工考勤表”为例，“自动筛选”的操作步骤3.3.2高级筛选如果筛选的数据需要复杂条件 (例如，类型 =“农产品”OR 销售人员 =“李小明”)，可以使用“高级筛选”对话框。下面以“薪资明细表”为例，详细说明高级筛选的操作及条件区域的定义规则。3.4分类汇总分类汇总是Excel的一项重要功能，它能快速以某个字段为分类项，对数据列表中其他字段的数值进行统计计算。3.4.1单个字段分类汇总以“薪资管理表”为例介绍Excel表格中按“部门”进行分类汇总的操作方法。3.4.1多个字段分类汇总Excel也可以对数

8、据进行多字段的分类汇总。例如，我们可以按“部门”和“职称”同时对实发工资求和，这要分两步，首先按“部门”进行分类汇总，在此基础上再按“职称”进行分类汇总。3.5数据透视表和数据透视图3.5.1数据透视表数据透视表是一种交互式的表，可以进行求和、计数等一系列的计算，所进行的计算与数据在数据透视表中的排列有关。数据透视表，可以动态地改变它们的版面布置，以便按照不同方式分析数据，也可以重新安排行号、列标和页字段。每一次改变版面布置时，数据透视表会立即按照新的布置重新计算数据。另外，如果原始数据发生更改，则可以更新数据透视表。3.5数据透视表和数据透视图3.5.2数据透视图数据透视图是与之相关联的数据透视表数据的图形表示形式，与数据透视表一样，数据透视图报告