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

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

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

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

4、E3=4500,E3*0.1-105,IF(E3=9000,E3*0.2-555,IF(E3=35000,E3*0.25-1005,IF(E3=55000,E3*0.3-2755,IF(E3=80000,E3*0.35-5505,E3*0.45-13505)”计算结果如图3-3所示。3.1 3.1 薪资表的建立薪资表的建立v 3.1.2 3.1.2 制作职工工资条制作职工工资条 职工工资条便于职工了解工资项目及工资明细，这里我们介绍一种利用VLOOKUP函数制作工资条的方法。1.VLOOKUP函数，功能：是一个查找和引用函数，可以在表格或区域中按行查找内容。3.1 3.1 薪资表的建立薪资表的

5、建立v 3.1.2 3.1.2 制作职工工资条制作职工工资条 2.制作工资条的步骤(1)建立“职工工资条”表，复制“薪资明细表”表头部分。(2)在“薪资明细表”中，选A2:J22,定义名称为“工资条”。(3)在“职工工资条”中，在A3单元格中输入编号“001”，在B3单元格中输入公式“=VLOOKUP(A3,工资条,2,0)”。(4)选中B3填充柄向右复制公式，并依次修改首列号和所在列号。(5)选中A1:J3区域，按住填充柄，向下复制，就得出职工工资条。3.23.2数据排序数据排序v 3.2.13.2.1数据排序概述数据排序概述 1.对文本、数字、日期类型的数据进行排序 2.按多列或多行进行排

6、序3.23.2数据排序数据排序 在工作表中，为了醒目显示，一些数据可能设置了单元格颜色、字体颜色或单元格图标等格式，同样我们也可以用这些格式来进行排序。3.2.23.2.2按特殊格式排序按特殊格式排序 在工作表中，为了醒目显示，一些数据可能设置了单元格颜色、字体颜色或单元格图标等格式，同样我们也可以用这些格式来进行排序。下面我们以“职工考勤表”为例，假如，表中“”事假（天数）大于或等天5天的已经设置了“浅红填充深红文本”，要求按这种格式进行排序，步骤如下：3.23.2数据排序数据排序v 3.2.33.2.3按自定义序列排序按自定义序列排序 除上述排序外，用户可以使用自定义列表的顺序进行排序。例

7、如，“职称”列可能包含要用来排序依据的值（如“高级”、“中级”和“初级”）。如何排序才能使包含“初级”的行先显示，然后是“中级”，最后是“无”？如果按字母顺序排序，“升序”排序会“初级、高级、无、中级”排列，显然不符合要求。通过创建“自定义序列”，可以避免这一问题。3.3 3.3 筛选筛选 筛选就是根据用户要求将满足条件的数据记录通过筛选过滤出来，提高工作效率。而且还不容易发生遗漏，减少差错。筛选一般分为自动筛选和高级筛选。v 3.3.13.3.1自动筛选自动筛选 1.自动筛选的类型 2.以“职工考勤表”为例，“自动筛选”的操作步骤v 3.3.23.3.2高级筛选高级筛选 如果筛选的数据需要复

8、杂条件(例如，类型=“农产品”OR 销售人员=“李小明”)，可以使用“高级筛选”对话框。下面以“薪资明细表”为例，详细说明高级筛选的操作及条件区域的定义规则。3.43.4分类汇总分类汇总 分类汇总是Excel的一项重要功能，它能快速以某个字段为分类项，对数据列表中其他字段的数值进行统计计算。v 3.4.13.4.1单个字段分类汇总单个字段分类汇总 以“薪资管理表”为例介绍Excel表格中按“部门”进行分类汇总的操作方法。v 3.4.13.4.1多个字段分类汇总多个字段分类汇总 Excel也可以对数据进行多字段的分类汇总。例如，我们可以按“部门”和“职称”同时对实发工资求和，这要分两步，首先按“

9、部门”进行分类汇总，在此基础上再按“职称”进行分类汇总。3.53.5数据透视表和数据透视图数据透视表和数据透视图v 3.5.13.5.1数据透视表数据透视表 数据透视表是一种交互式的表，可以进行求和、计数等一系列的计算，所进行的计算与数据在数据透视表中的排列有关。数据透视表，可以动态地改变它们的版面布置，以便按照不同方式分析数据，也可以重新安排行号、列标和页字段。每一次改变版面布置时，数据透视表会立即按照新的布置重新计算数据。另外，如果原始数据发生更改，则可以更新数据透视表。3.53.5数据透视表和数据透视图数据透视表和数据透视图v 3.5.23.5.2数据透视图数据透视图 数据透视图是与之相关联的数据透视表数据的图形表示形式，与数据透视表一样，数据透视图报告也是交互式的。创建数据透视图时，数据透视图筛选将显示在图表区中，以便于排序和筛选数据透视图报表的基本数据。与标准图表一样，数据透视图显示数据系列、类别、数据标记和坐标轴，可以更改图表类型及其他选项，如标题、图例位置、数据标签和图表位置。THANK YOU