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

,单击此处编辑母版文本样式,第二级,第三级,第四级,Company Logo,*,单击此处编辑母版标题样式,EXCEL,在财务中的应用,于凌云,项目三,EXCEL,在薪资管理中的应用,学习目的：,分析薪资管理中运用的数据,；,掌握数据的数据引用、计算、排序、筛选、分类汇总、数据透视表以及数据透视图等内容操作,；,以薪资管理表为引例，拓展应用，触类旁通。,3.1,薪资表的建立,3.1.1,相关数据分析计算,1.,薪资明细表,一般包括单位人员的基本工资信息，为了便于分析和理解，我们把薪资明细表简化为以下内容，并根据计算，得出结果，如图,3-1,所示。,保险扣款：假定保险扣款按基本工资和岗位津贴的,15%,扣除。,（,1,）应发工资：用,SUM,函数求得。,（,2,）保险扣款：用公式“应发工资,*15%,”求得。,（,3,）考勤扣款：引用职工考勤表中的“扣款合计”。,（,4,）个人所得税扣款：引用职工纳税表的“应纳个人所得税”。,（,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,）事假扣款：根据要求，用公式“,=IF(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-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.2,制作职工工资条,职工工资条便于职工了解工资项目及工资明细，这里我们介绍一种利用,VLOOKUP,函数制作工资条的方法。,1.VLOOKUP,函数,，,功能：是一个,查找和引用函数,，可以在表格或区域中按行查找内容。,3.1,薪资表的建立,3.1.2,制作职工工资条,2.,制作工资条的步骤,(1),建立“职工工资条”表，复制“薪资明细表”表头部分。,(2),在“薪资明细表”中，选,A2:J22,定义名称为“工资条”。,(3),在“职工工资条”中，在,A3,单元格中输入编号“,001,”，在,B3,单元格中输入公式“,=VLOOKUP(A3,工资条,2,0),”。,(4),选中,B3,填充柄向右复制公式，并依次修改首列号和所在列号。,(5),选中,A1:J3,区域，按住填充柄，向下复制，就得出职工工资条。,3.2,数据排序,3.2.1,数据排序概述,1.,对文本、数字、日期类型的数据进行排序,2.,按多列或多行进行排序,3.2,数据排序,在工作表中，为了醒目显示，一些数据可能设置了单元格颜色、字体颜色或单元格图标等格式，同样我们也可以用这些格式来进行排序。,3.2.2,按特殊格式排序,在工作表中，为了醒目显示，一些数据可能设置了单元格颜色、字体颜色或单元格图标等格式，同样我们也可以用这些格式来进行排序。,下面我们以“职工考勤表”为例，假如，表中“”事假（天数）大于或等天,5,天的已经设置了“浅红填充深红文本”，要求按这种格式进行排序，步骤如下：,3.2,数据排序,3.2.3,按自定义序列排序,除上述排序外，用户可以使用自定义列表的顺序进行排序。例如，“职称”列可能包含要用来排序依据的值（如“高级”、“中级”和“初级”）。如何排序才能使包含“初级”的行先显示，然后是“中级”，最后是“无”？如果按字母顺序排序，“升序”排序会“初级、高级、无、中级”排列，显然不符合要求。通过创建“自定义序列”，可以避免这一问题。,3.3,筛选,筛选就是根据用户要求将满足条件的数据记录通过筛选过滤出来，提高工作效率。而且还不容易发生遗漏，减少差错。,筛选一般分为自动筛选和高级筛选。,3.3.1,自动筛选,1.,自动筛选的类型,2.,以“职工考勤表”为例，“自动筛选”的操作步骤,3.3.2,高级筛选,如果筛选的数据需要复杂条件,(,例如，类型,=,“农产品”,OR,销售人员,=,“李小明”,),，可以使用“高级筛选”对话框。下面以“薪资明细表”为例，详细说明高级筛选的操作及条件区域的定义规则。,3.4,分类汇总,分类汇总是,Excel,的一项重要功能，它能快速以某个字段为分类项，对数据列表中其他字段的数值进行统计计算。,3.4.1,单个字段分类汇总,以“薪资管理表”为例介绍,Excel,表格中按“部门”进行分类汇总的操作方法。,3.4.1,多个字段分类汇总,Excel,也可以对数据进行多字段的分类汇总。例如，我们可以按“部门”和“职称”同时对实发工资求和，这要分两步，首先按“部门”进行分类汇总，在此基础上再按“职称”进行分类汇总。,3.5,数据透视表和数据透视图,3.5.1,数据透视表,数据透视表,是一种,交互,式的表，可以进行求和、计数等一系列的计算，所进行的计算与数据在数据透视表中的排列有关。,数据透视表,，可以动态地改变它们的版面布置，以便按照不同方式分析数据，也可以重新安排行号、列标和页字段。每一次改变版面布置时，数据透视表会立即按照新的布置重新计算数据。另外，如果原始数据发生更改，则可以更新数据透视表。,3.5,数据透视表和数据透视图,3.5.2,数据透视图,数据透视图是与之相关联的数据透视表数据的图形表示形式，与数据透视表一样，数据透视图报告也是交互式的。创建数据透视图时，数据透视图筛选将显示在图表区中，以便于排序和筛选数据透视图报表的基本数据。与标准图表一样，数据透视图显示数据系列、类别、数据标记和坐标轴，可以更改图表类型及其他选项，如标题、图例位置、数据标签和图表位置。,THANK YOU,