薪酬管理系统课件.ppt

《薪酬管理系统课件.ppt》由会员分享，可在线阅读，更多相关《薪酬管理系统课件.ppt（98页珍藏版）》请在三一办公上搜索。

1、,项目二 薪酬管理系统实务,任务二 计算薪金、生成工资条,任务三 职工薪酬数据统计分析,李娜是山东丰源公司财务人员，现需设计公司的薪酬管理系统。山东丰源公司有5个部门：企划部、设计部、财务部、销售部、生产部，员工12人。每个员工的工资包括基本工资、岗位工资、职务津贴、奖金、事假扣款、病假扣款、住房公积金和个人所得税。员工的基本信息情况、考勤、业绩情况见表。相关说明：该企业请假扣款=50*（病假天数+事假天数），即请假一天扣50；职工个税的应纳税所得额=月工资、薪金所得-3500；应纳税额=每月应纳税所得额*适用税率-速算扣除数,职工基本情况,职工考勤,职工基本工资,职工业绩考核,职工福利,职工

2、社保,薪酬系统结构及创建步骤,职工基本情况表职工基本工资表职工福利表职工考勤表职工社会保险表职工业绩考核表具体操作要求按教材执行,本任务需要确定各类计算比率创建工资结算单，导入工资数据 职工工资结算单是由职工基本工资表、职工福利表、职工社会保险表、职工考勤表、职工业绩考核表中的各项数据组合而成，如果采用逐一填入数据，工作会非常繁琐，而且容易出错。因此可以利用Excel提供的“定义数据名称”功能和“函数”功能从各个表中提取数据，简化操作。生成工资条,任务二 计算薪金、生成工资条,名 称,Excel的名称与普通公式类似，是一种由用户自行设计并能够进行数据处理的算式。其特别之处在于，普通数据存在于单

3、元格中，而名称而在于Excel的内存中。为了便于理解，可以将名称看作是对工作簿中某些特定元素的“文本化”标识，而这些元素包括单元格区域、常量数据、常量数组以及函数公式。将这些元素定义为名称（即命名）后，就可以在函数和公式中进行调用,为什么要使用名称,增强公式的可读性和便于公式修改假设某公司希望将各单位3个月的营业额进行汇总，公式为：SUM（C3：E3）此公式并无问题，但公式意途不明确，如将汇总范围C3：E3定义为一个名称“月营业额” ，则公式变为SUM（月营业额），更易于理解。有利于简化公式如以下公式：IF（SUM（$B2:$F2)=0,0,G2/ SUM（$B2:$F2)如将SUM（$B2:

4、$F2)定义为名称Total 则可简化公式编辑，便于阅读，简化后：IF（Total=0,0,G2/Total)代替工作表区域突破函数嵌套的限制,名称的分类,根据名称的作用范围不同，分为“工作簿级名称”和“工作表级名称”工作簿级名称：能够在同一工作簿的任意一张工作表中使用。工作表级名称：只能在被定义的工作表中可见，在其他工作表中不可见，如要调用其他工作表中定义的名称，则要使用完整名称。格式为：工作表名+半角感叹号+名称,定义名称的限制,名称命名可以是任意字符与数字组合，但不能以数字开头，如1Pic ，如果要以数字开头，可在前面加下划线，如_1Pic名称不能仅以数字作为标识；如3，因为如果输入公式

5、=A1+3 ，Exce不能确定到底是调用名称还是使用真正的数值3名称不能与单元格地址相同，也不能以字母R、C、r、c作为名称，因为R、C在R1C1引用样式中表示工作表的行、列,名称中不能包含空格，可以用下划线或点代替不能使用除下划线、点、和反斜线（）以外的其他符号，允许使用问号，但不能作为名称的开头名称字符数不能超过255 。名称应便于记忆，尽量简短，在名称“引用位置”中的公式字符数也不能超过255 。在定义工作表级名称时，必须使用名称所在工作表的标签名名称不区分大小写,定义名称的方法,使用“定义名称”对话框单击菜单“插入”“名称”“定义”（或用组合键CTRL+F3） 打开“定义名称”对话框进

6、行操作使用“定义名称”对话框是定义名称的方法之一，能够为不同元素定义名称，也是编辑和管理名称的唯一方法。,使用名称框快速定义名称选定指定的单元格区域后，再用鼠标单击左上角的“名称框”，输入名称后按下回车键，即可完成定义注：使用该方法定义“工作表级名称”，同样需要在名称前添加工作表名，否则Excel将其添加为“工作簿级名称”,定义动态名称,在认识“动态名称”之前，首先应理解“动态引用”，动态是相对静态而言的，一个静态的区域引用，如$A$1:$A$10 通常是保持不变的；而动态引用则是随着明细数据的增加或减少，引用区域动态地扩大或缩小，即动态变化。如果希望实现对一个未知大小的区域引用，就可定义一个

7、动态名称来对数据进行动态引用。,利用 “列表功能”定义动态名称,操作步骤如下：1、选择数据列表的区域2、按下CTRL+F3组合键，打开“名称”对话框，输入名称，并设置引用区域3、保持选定的数据区域不变，按下CTRL+L组合键，打开“创建列表”对话框4、单击“确定”完成列表创建。,常用查找和引用函数有：（1）LOOKUP函数（2）VLOOKUP函数（HLOOKUP）（3）MATCH函数,查找和引用函数,（1） LOOKUP函数（向量形式）函数功能：在单行区域或单列区域中查找值，然后返回第2个单行区域或单列区域中相同位置的值。语法格式：LOOKUP（lookup_value,lookup_vect

8、or,result_vector）参数说明： lookup_value是LOOKUP在第一个向量中搜素的值，可以是数字，文本，逻辑值，名称或对值得引用；Lookup_vector 为只包含一行或一列的区域。该参数的数值可以为文本、数字或逻辑值。同时该区域必须按升序排序，否则不能返回正确的结果。Result_vector 只包含一行或一列的区域，其大小必须与 lookup_vector 相同。,注：如果函数 LOOKUP 找不到 lookup_value，则查找 lookup_vector 中小于或等于 lookup_value 的最大数值。 如果 lookup_value 小于 lookup_

9、vector 中的最小值，函数 LOOKUP 返回错误值 #N/A。应用举例：查询申购基金的申购手续费下图模拟了一份基金购买的申购费率表，其中左表为购买基金的费率标准，右表D1：D4区域为根据标准转换得到的数值关系表，下面的公式将根据购买者的申购金额来计算应付的申购基金招待费费。,函数名称：LOOKUP（数组形式）功能：在数组的第一列（或第一行）中查找指定数值，然后返回最后一列（或最后一行）中相同位置处的数值。 语法格式：LOOKUP(lookup_value,array) Lookup_value为要查找的数值。该参数可以为数字、文本、逻辑值或包含数值的名称或引用。Array为一单元格区域。

10、区域中的数值必须按升序排序,（2） VLOOKUP函数函数功能：在指定数据区域的首列中搜索满足条件的数据，并返回当前行中指定列号对应的值。语法格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup),参数一查找值,参数二查找区域,参数三指定列号,参数四查找方式,参数说明：Lookup_value代表需要查找的数值；Table_array代表需要在其中查找数据的单元格区域；Col_index_num为在table_array区域中待返回的匹配值的列序号（当Col_index_num为2时,返回table_array第2列中的数

11、值，为3时，返回第3列的值）；Range_lookup为逻辑值，如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果为FALSE，则返回精确匹配值，如果找不到，则返回错误值#N/A。,HLOOKUP函数功能：在指定数据区域的首行中搜索满足条件的数据，并返回当前列中指定行号所对应的值。语法格式：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)注：HLOOKUP与VLOOKUP函数功能类似，函数结构、参数意义一致，只是方向不一样。,应用举例：利用HLOOKU

12、P函数查询产品月销售量，下图展示了某公式年度产品销售情况表， 下面的公式将查询“产品D”在某月的销售量。B3单元格为查询月份“8”，B4单元格公式为：=HLOOKUP(B3,$D$2:$P$8,5,0),如果在B7单元格输入文本类型的数值10，即使查询公式不变，结果却返回“#N/A”错误，这是因为B7单元格的查找值（文本）与查找范围中首行的数据（数值）的数据类型不一致，因此，可将公式进行修改，以使其返回正确的结果=HLOOKUP(B7*1,$D$2:$P$8,5,0)注：某些情况下，可能忽略了查找值与查找范围的数据类型不一致的情况，从而导致查询失败，因此，在使用些类查询函数进行查询时，查找条件

13、与查找范围的首列或首行的数字格式必须保持一致，如使用了文本函数（LEFT、MID、RIGHT等）来提取查找条件中的字符串，其结果为文本，也需注意数据类型的转换。,LOOKUP函数与HLOOKUP和VLOOKUP的区别:函数 LOOKUP 的数组形式与函数 HLOOKUP 和函数 VLOOKUP 非常相似。不同之处在于函数 HLOOKUP 在第一行查找 lookup_value，函数 VLOOKUP 在第一列查找，而函数 LOOKUP 则按照数组的维数查找。如果数组所包含的区域宽度大，高度小（即列数多于行数），函数 LOOKUP 在第一行查找 lookup_value。如果数组为正方形，或者所包

14、含的区域高度大，宽度小（即行数多于列数），函数 LOOKUP 在第一列查找 lookup_value。函数 HLOOKUP 和函数 VLOOKUP 允许按行或按列索引，而函数 LOOKUP 总是选择行或列的最后一个数值。,函数名称：MATCH函数功能：用于确定查找值在查找范围中的位置序号，该函数主要是对查找数据进行定位，当数据存在时，则返回具体的位置序号，否则返回“#N/A”错误。语法格式：MATCH(lookup_value,lookup_array,match_type),参数一查找值,参数二查找范围,参数三查找方式,说明： Lookup_value代表要在数据表中查找的数值 Lookup

15、_array表示可能包含所要查找的数值的连续单元格区域； Match_type表示查找方式（-1、0或1）-1，则查找大于或等于 lookup_value的最小数值，Lookup_array 必须 按降序排列；1，则查找小于或等于lookup_value 的最大数值，Lookup_array 必须按升序排列；0，帅查找等于lookup_value 的第一个数值，Lookup_array 可以按任何顺序排列；如果省略match_type，则默认为1。,应用举例：查找某员工是否登记在员工信息表中在下图所示的员工信息表中，在B3单元格中录入下面的公式可查找员工“张三”是否已经登记，如果已登记，则返回

16、该员工所在的部门名称，否则提示“无此员工”,函数名称：OFFSET功能：以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。 语法格式：OFFSET(reference,rows,cols,height,width),参数一参照系,参数二偏移行数,参数三偏移列数,参数三高度,参数五宽度,参数说明：Reference作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用；否则，函数 OFFSET 返回错误值 #VALUE!。Rows相对于偏移量参照系的左上角单元格，上（下）偏移的行数。如果使用 5 作

17、为参数 Rows，则说明目标引用区域的左上角单元格比 reference 低 5 行。行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）。Cols相对于偏移量参照系的左上角单元格，左（右）偏移的列数。如果使用 5 作为参数 Cols，则说明目标引用区域的左上角的单元 格比 reference 靠右 5 列。列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。Height高度，即所要返回的引用区域的行数。Height 必须为正数。Width宽度，即所要返回的引用区域的列数。Width 必须为正数。,示例：以下公式将返回对$C$4:$E$8单元格区域的引用,工资总额

18、汇总表，是对工资数据进行分析的表格，需要将相同类型的数据统计出来，即数据的分类和汇总。Excel提供了强大的数据分析功能，一是分类汇总功能，另一种功能是数据透视表功能。工资费用分配表是在每月月末，企业将本月的应付职工薪酬按照发生的地点、部门与产品的关系进行分配，编制工资费用分配表，并根据表中的各个项目分别计入相关账户，各类经费计提比例件前文。,任务三 职工薪酬数据统计分析,数据统计分析,排序筛选分类汇总数据透视表数据透视图,EXCEL数据分析排序,EXCEL允许对字符、数字等数据进行升序或降序排列，要进行排序的数据称为关键字，不同类型的数字作为关键字进行排序时，排序规则如下：数值：按数值大小字

19、母：按字母先后顺序日期：按日期的先后汉字：按汉语拼音的顺序或按笔画顺序逻辑值：FLASETRUE注：Excel一次最多允许满足3个条件的排序,另外，可对数据列表中的某部分进行排序：选择数据列表中的某部分区域，单击“数据”“排序”，再选择相应关键字。对数据列表中的行进行排序：即是按行进行排序，对数据列表中的列进行排序，而数据列表中的行保持不变。自定义排序：用户还可以用一种特殊的非字母顺序进行排序，可以使用自定义排序。数据排序次序下拉列表中自定义排序次序。（此次序即为“自定义序列”中的次序）,对数据列表进行排序，要注意含有公式的单元格，当含公式，排序过后，公式的单元格引用会出现错误。为了避免对含有

20、公式的数据列表中排序错误，可以遵循以下规则： 数据列表单元格的公式中引用了数据列表外的单元格数据，请使用绝对引用；对行进行排序，避免使用其他行的单元格的公式；对列进行排序，避免使用其他列的单元格的公式。,EXCEL数据分析筛选,筛 选就是将不符合特定条件的数据隐藏，只显示符合条件的数据。Excel有两种筛选：自动筛选和高级筛选自动筛选：要使用自动筛选命令，首先单击数据列表中的任意单元格，然后依次单击菜单栏“数据”“筛选”“自动筛选”,对多列使用自动筛选：先以数据列表中的某一列为条件进行筛选，然后在筛选出的记录中以另一列为条件进行筛选，依此类推。使用“自动筛选”查找前10条记录：在数据列表中的数

21、字字段上使用“自动筛选”命令中的“自定筛选前10个”功能，可以显示数据列表中的前N个最大值或最小值，还可以查找那些占某列前百分之几或后百分之几的数据。使用“自动筛选”查找空白单元格使用自定义筛选：可以通过多个条件筛选或查询数据列表中含有某一特定范围的值所在的行，是筛选数据列表获得较大的灵活性。还可以使用通配符：*代表任意系列的字符串；？代表任意一个字符。如要表示*，？本身，需要在前面加上符号。,复制和删除自动筛选后的数据：有时需要将自动筛选出来的结果复制到另一工作表中，单击自动筛选出数据的任意单元格，按，单击鼠标右键，选择“复制”，粘贴到想要的区域中，只会复制筛选后可见的行。同样，选择并删除自

22、动筛选出的结果时，自动筛选中被隐藏的行也将不受影响。,高级筛选：EXCEL高级筛选功能是自动筛选的升级，它可以将自动筛选的定制格式改为自定义设置。利用高级筛选，可以使用较多的条件来对数据列表进行筛选，它的功能更加优于自动筛选。高级筛选的条件可以是“与”条件、“或”条件及其组合，也可以使用计算条件。,高级筛选的功能,可以设置更为复杂的筛选条件；可以将筛选出的结果放在不同的位置；可以指定计算的筛选条件；可以筛选出不重覆的记录项。,使用高级筛选的步骤如下：1、创建条件区域 “高级筛选”要求在数据列表以外的区域内单独设定所需要的筛选条件。 一个“高级筛选”的条件区域至少包含两行，第一行是标题行（一般情

23、况下标题行中的字段名应和数据列表中的字段名相匹配）;第二行是筛选条件。高级筛选的条件类型:与条件、或条件、计算条件 Excel根据以下规则解释条件区域中的条件：同一行中的条件之间的关系是“与”不同行中的条件之间的关系是“或”如为空白单元格，则表示筛选出该列的所有数据2、根据创建的条件区域，对数据列表的数据进行筛选。,示例1、使用 “与”条件要求：运用“高级筛选”功能将“性别”为“男”且“绩效系数”为“1.00”的数据筛选出来，并将筛选结果存放在单元格A26所在的区域中。示例2、使用“或”条件要求：将“性别”为“男”，或“绩效系数”为“1.00”的数据筛选出来，并将筛选结果放在数据列表下方A26

24、所在区域中。示例3、同时使用“关系与”和“关系或”要求：显示“顾客”为“天津大宇”、“宠物垫”产品的“销售额总计”大于500的记录；或者显示“顾客”为“北京福东”、“宠物垫”产品的“销售额总计”大于100的记录；或者显示“顾客”为“上海嘉华”、“雨伞”产品的“销售额总计”小于400的记录；或者显示“顾客”为“南方万通”的所有记录。,在高级筛选中，使用文本条件可以使用通配符：“*”表示可以与任意多的字符相匹配；“？”表示只能与单个的字符相匹配。高级筛选可以选择不重复的记录，对于数据量较大的重复数据时，使用该功能效果最佳。,高级筛选中使用“计算条件”所谓“计算条件”指的是该条件不仅将某列的值与某个

25、常数进行简单比较，而且该条件还必须在数据列表中参与计算测试，筛选出符合条件的数据。示例4、使用计算条件要求：将满足以下条件的数据筛选出来1、“顾客”列中含有“天津”两个字符2、身份证号中的出生年份为“1980”3、“产品”列中第一个字母为G最后一个字母为S,计算条件说明：单元格A2包含的公式：=ISMUNBER(FIND(“天津”,A5)公式通过在“客户”列中寻找“天津” 并做出数值判断单元格B2包含下面的公式：=MID(B5,7,4)=“1980” 公式通过在“身份证”列中第7个字符开始截取4位字符来判断单元格C2包含下面公式=COUNTIF(C5,”G*S”)公式通过在“产品”列中对包含“

26、G*S”即第一个字母为G最后一个字母为S的产品计数，来判断是否第一个字母为G最后一个字母为S,关于条件区域的字段名：条件区域没有使用数据列表中的字段名，而是使用空白标题。因为，Excel高级筛选功能在使用“计算条件”时允许使用空白或创建一个新的字段标题，而不允许使用与数据列表中同名的字段标题。使用计算条件时，要注意以下两点：使用数据列表中首行数据来创建计算条件的公式，单元格引用要使用相对引用而不能使用绝对引用如果计算公式中需要引用到数据列表外的同一单元格的数据，要使用绝对引用而不能使用相对引用,EXCEL数据分析分类汇总,分类汇总：是Excel中最常用的功能之一，它能够快速地以某一个字段为分类

27、项，对数据列表中的数值字段进行各种统计计算，如求和、计数、平均值、最大值、最小值、乘积等。使用分类汇总功能以前，必须对要进行汇总的字段进行排序。,EXCEL列表功能,Excel列表功能是Excel2003中的新增功能，就是将一个数据列表区域创建为列表后，可以自动扩展列表区域；排序、筛选；自动求和、平均值等操作，而不用输入任何公式。另外只需做一下选择，列表还可随时转换为区域。将某一区域指定为列表后，可以方便地管理和分析列表中的数据而不会影响到列表之外的其他数据。可以在数据列表中设置多个不同的列表，从而更加灵活地根据需要将数据划分为易于管理的不同数据集。,创建列表：步骤：单击数据列表中的任意单元格

28、，运行菜单“数据”“列表”“创建列表” 调出“创建列表”对话框，单击“确定”按钮完成列表的创建。在列表中添加数据列表的最后一行包含一个带星号的空行，只要在这个空行中输入数据，就可以向列表中添加数据行。在列表中添加汇总函数 单击“列表”工具栏的“切换汇总行”可以为列表中的“订单金额”添加汇总函数。单击列表中的汇总行，在其下拉列表中可以选择汇总类型，如求和、平均值、计数等。,要将列表转换为标准区域，可以在菜单栏上单击数据-列表-转换为区域。设计列表：默认情况下，标题行为列表中的所有列启用自动筛选功能。自动筛选允许用户快速筛选、排序数据。列表周围的深蓝色边框清晰地区分组成列表的单元格区域。包含星号的

29、行称为扩展行。在此数据行中输入的数据将自动添加到列表中并扩展列表的边框，本行将自动继承上一行的数据格式和公式。可以为列表添加汇总行。单击汇总行中的单元格时，将显示汇总函数下拉列表。通过拖动列表边框右下角的调整手柄，可以调整列表大小。,用列表建立动态名称 因为列表的一个重要功能就是在包含星号的行插入数据后，整个列表将向下自动扩展。利用这个功能，在定义名称时选择整个列表区域，随着列表本身的拓展，定义的名称也随着拓展，从而定义的名称也就变为动态的了。 步骤：选择列表区域，但不包括含有星号的行，单击插入名称鼠标指向“指定”并单击它，出现“指定名称”对话框，取消“最左列”复选框的勾选，单击确定。,用记录

30、单为数据列表命名动态名称将数据列表定义名称为“Database”,引用位置为数据列表区域，确定；单击数据-记录单命令，在出现的对话框中输入新的数据，单击“新建”按钮将数据添加到列表中。注：为数据列表定义的名称必须为Database，为数据列表添加新数据也必须通过记录单，否则将不能为数据列表定义动态名称。,什么是数据透视表数据透视表是用来从Excel数据列表中统计分析信息的工具。它是一种交互式报表，可以快速分类汇总和比较大量数据，并可随时选择其中页、行和列中的不同元素，以达到快速查看源数据的不同统计结果，同时还可以随意显示和打印所需要区域的明细数据。透视表有机地综合了数据排序、筛选、分类汇总等数

31、据分析的优点，可方便地调整分类汇总的方式，灵活地以多种不同方式展示数据的特征。,EXCEL数据分析数据透视表,数据透视表的用途1、数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式动态表格，能分析、组织数据。2、建立好数据透视表后，可以对数据透视表重新安排，从不同的角度查看数据。从大量看似无关的数据中寻找背后的联系，从而将繁乱的数据转化为有价值的信息。3、合理运用数据透视表进行计算与分析，能使许多复杂的问题简单化并极大地提高工作效率。何时使用数据透视表分析数据1、对庞大的数据进行多条件统计。2、需要对得到的数据进行行列变化，把字段移动到统计中的不同位置上，从而得到新的统计数据，以满足不同

32、的统计要求。3、需要在得到的统计数据中找出某一字段的一系列相关数据。4、需要将统计数据与原始数据保持实时更新。5、需要在得到的统计数据中找出数据内部的各种关系并分组。,数据透视表创建步骤,插入数据透视表（调出【创建数据透视表】对话框）选择数据源区域：一般情况Excel能自动识别数据源所在单元格区域，如果Excel识别的数据源区域不正确，则需要重新选定区域。（注：不能选择合并区域）指定数据透视表位置：指定数据透视表的显示位置。可以是当前工作表，也可以是新工作表。,数据透视表结构,页字段：可以翻页显示的字段,行字段：在行方向上显示的字段,列字段：在列方向上显示的数据字段,数据区域：对显示在透视表中

33、行和列的值求和、计数、求平均值、最大、最小值等。可在“值字段设置”对话框中选择不同的“汇总方式。,数据透视表中的相关术语,数据源 从中创建数据透视表的数据列表或多维数据集轴数据透视表中的一维，如行、列、页列字段 信息的种类，等价于数据列表中的列行字段 在数据透视表中具有行方向的字段分页符 数据透视表中进行分页的字段字段标题 描述字段内容的标志。可以通过拖动字段标题对数据透视表进行透视(布局)。项目 组成字段的成员。如实例中，一车间、二车间等就是组成“部门”字段的项组 一组项目的集合，可以自动或手动为项目组合透视 通过改变一个或多个字段的位置来重新安排数据透视表分类汇总 透视表中对行或列单元格的

34、分类汇总刷新 重新计算数据透视表，反映目前目前数据源的状态,数据透视表的布局改变数据透视表的布局：设计报表布局、拖动行列页数据项字段改变行、列字段顺序：拖动字段、排序获取数据透视表源数据信息：选择表中一数据，右键选择详细信息数据透视表的复制和移动,整理数据透视表,通过对数据透视表字段的整理，可以满足用户对数据透视表格式上的需求。改变数据数据透视表的布局整理数据透视表的页字段区域整理数据透视表行字段区域整理数据透视表列字段区域数据透视表的复制和移动,整理数据透视表的页字段区域显示页字段的多个数据项页字段区域下拉列表不是复选框，不能同时选中多个数据项，只能选择“全部”或可视数据项中的某一项。通过下

35、面两种方法可以显示页字段的多个数据项：方法1 利用“数据透视表字段”对话框中的“隐藏数据项”，显示页字段的多个数据项,方法2 通过转移法设置显示数据透 视表的多个页字段数据项恢复页字段中隐藏的数据项,改变页字段的显示方式数据透视表创建完成后，页面区域如果有多个页字段，系统默认页字段的显示方式为垂直并排显示，为了使数据透视表更具可读性和易于操作，可以通过在“数据透视表”对话框中设置“页面布局”方式来改变页字段的显示方式,数据透视表的分页显示功能数据透视表的页字段可以容纳多个页面的数据信息，但它通常只显示一张表格。利用数据透视表的分页功能，可以创建一系列链接在一起的数据透视表，每一张工作表显示页字

36、段中的一页。操作方法：选定透视表中的任意单元格，单击“数据透视表”工具栏中的“数据透视表”按钮，在下拉菜单中选择“分页显示”菜单项，然后进行设置。,整理数据透视表行、列字段区域整理复合字段如果数据透视表的数据区域中垂直显示多个字段，则不符合阅读习惯。为了便于读取、比较数据，可以重新安排数据透视表的字段。方法1：把“数据”按钮拖到“汇总”单元格方法2：在“数据”按钮上单击鼠标右键，在弹出的快捷菜单中选择“顺序”“移至列”，也可以达到将多个数据字段水平排列的效果。,重命名字段当向数据区域添加字段后，它们都将被重命名，例如上例中的“本月数量”变成了“求和项：本月数量”，这样就会加大字段所在列的列宽，

37、影响表格的美观。可以通过两面两种方法对字段进行重命名，让列标题更加简洁。方法一：直接修改数据透视表字段名称方法二：替换数据透视表的默认字段名称,改变行、列字段项的排列顺序透视表创建完成后，行、列字段的排列顺序是按系统默认方式排列的，如下图所示，这样的排列顺序不符合通常情况下的阅读习惯。此时，可以通过“拖动字段”或通过“右键快捷菜单”来调整行、列字段的排列顺序，以满足人们通常的阅读习惯。,改变数据透视表行字段的显示方式透视表行字段数据的显示方式有两种：报表形式显示和大纲形式显示。系统默认的行字段布局为“以报表形式显示项目”，用户可以在“数据透视表字段”对话框中，将系统默认的行字段布局改为“以大纲

38、形式显示项目”，以满足不同的数据分析需求。通过下图所示对话框，还可以将每组数据的汇总行显示在数据的顶部；以及在分类汇总行的后面插入一行空白行，以便更显示地区分不同的数据。,数据透视表的复制和移动数据透视表创建完成后，如果需要对同一个数据源再创建另外一个数据透视表用于特定的数据分析，只需对原有的数据透视表进行复制即可，这样可以免去从头创建数据透视表的一系列操作，提高工作效率。用户还可以将已经创建好的数据透视表在同一个工作簿内的不同工作表中任意移动，以满足数据分析的需要。,获取数据透视表的数据源信息数据透视表完成后，如果不慎将数据源删除，可通过以下方法将原始数据源找回。方法如下：1、在 “数据透视

39、表选项”对话框中勾选“显示明细数据”筛选框。2、鼠标双击数据透视表的最后一个单元格，即可在另外一个工作表中重新生成原始的数据源。,控制数据透视表的行列总计与分类汇总在分析数据时，可根据需要在数据透视表中添加或取消行列总计和分类汇总。如下图就是一张包含行列总计和分类汇总的透视表，控制行列总计或分类汇总的方法如下：,（1）控制行、列总计针对上图中的数据透视表，如要取消行、列总计，可在“数据透视表选项”对话框的“格式选项”中取消勾选的“列总计”和“行总计”复选框。如要添加行列总计，只要重新勾选“列总计”和“行总计”复选框即可。（操作示例）,（2）控制分类汇总如要取消分类汇总，可在透视表中任一分类汇总

40、项上单击鼠标右键，在弹出的快捷菜单中选择“隐藏” ，或者在“数据透视表字段”对话框中将“分类汇总”选项设置为“无”即可。如要添加分类汇总，则应在“数据透视表字段”对话框中设置好分类汇总的计算类型。,控制数据透视表的合并标志在包含分类汇总的透视表中，分类项默认位于分类区域的第一行。但在一般的阅读习惯中，分类标志应该垂直居中，要实现这样的效果，可在“数据透视表选项”对话框中的“格式选项”中勾选“合并标志”复选框即可。,在透视表的每项后面插入空行如果要在透视表的每项后插入空行，以使透视表看上去便于阅读，可在“数据透视表字段”对话框中单击“布局”按钮，打开“数据透视表字段布局”对话框，在其中勾选“每项

41、后面插入空行”复选框，然后确定即可。,刷新数据透视表的数据,手动刷新数据透视表源数据发生变化后，透视表本身不会自动刷新，需要在透视表内单击右键，选择“刷新数据”或单击数据透视表工具栏上的“刷新数据”按钮，手动刷新数据。,打开文件时自动刷新操作方法：在数据透视表的任意位置单击右键，在弹出的快捷菜单中选择“表格选项”。在打开的“数据透视表选项”对话框中勾选“打开时刷新”复选框。,刷新链接在一起的数据透视表当数据透视表用作其他数据透视表的数据源时，对其中任何一张数据透视表进行刷新，都会引起所有链接在一起的数据透视表进行刷新使用VBA代码设置自动刷新在数据透视表所在工作表的标签上单击鼠标右键，选择“查

42、看代码”菜单项，进入VBA代码窗口。在VBA编辑窗口中的代码区域中输入以下代码：,Private Sub Worksheet_Activate()ActiveSheet.PivotTables(“ 数据透视表的名称”).PivotCache.RefreshEnd Sub注：括号中的透视表名称可以根据实际情况修改，如果不清楚目标数据透视表的名称，在 “数据透视表选项”对话框中“名称”框内即可看见数据透视表的名称。保存工作簿。此时，只要激活“透视表”所在工作表，透视表就会自动刷新数据。,数据透视表的刷新注意事项海量数据源对刷新速度的影响一般情况下对透视表的刷新可以瞬间完成，但基于海量数据源创建的数

43、据透视表，受计算机性能及内存的限制刷新会非常缓慢，透视表被刷新时鼠标指针状态会变为“忙”清除已删除数据的标题项如果删除了数据源中一些不需要的数据，刷新后，删除的数据虽然从透视表中清除了，但透视表字段的下拉列表中仍然存在着被删除的数据项。此时可用手动拖动字段，并刷新数据的方式清除已删除项,创建动态数据透视表1、通过“定义名称”创建动态数据透视表步骤：1、按CTRL+F3打开“定义名称”对话框，然后在“在当前工作簿中的名称”框中输入data ，在“引用位置”框中输入=OFFSET(数据源!$A$1,COUNTA(数据源!$A:$A),COUNTA(数据源!$1:$1) ，单击“添加”按钮。2、通过

44、创建数据透视表向导创建数据透视表，在步骤3进行区域选择时，输入之前定义的名额 data ，然后单击完成按钮。3、在新创建的空数据透视表中按需要进行布局。,2、使用列表功能创建动态数据透视表步骤：1、单击工作表中任意一个包含数据的单元格，然后依次选择菜单“数据列表创建列表”，在弹出的对话框中单击“确定”按钮，即可将当前的数据表格转换为列表。2、通过创建数据透视表向导创建数据透视表。3、在新创建的空数据透视表中按需要进行布局。注：利用列表的自动扩展功能创建的动态数据表，只对数据源中新增加的记录（行）有效。如果数据源中增加了新字段（列），则无法被数据透视表识别。,3、借助VBA代码创建动态数据透视表

45、步骤：1、在数据透视表所在的工作表标签上单击鼠右键，在弹出的快捷菜单中选择“查看代码”项，进入VBA编辑器窗口。2、在VBA代码窗口的代码区域中输入代码（代码）3、单击VBA代码窗口菜单“插入模块”，在出现的“模块1”代码窗口中输入以下代码：Public strFld As String4、按ALT+F11组合键切换到EXCEL窗口。至此，在“数据源”中新增数据后，只要激活数据透视表所在的工作表，透视表中就会立即自动显示新增的数据。,Private Sub Worksheet_Activate() Dim pv As PivotTable, rng As Range, dFld As Pivo

46、tField Set pv = Sheet1.b3.PivotTable pv.RefreshTable For Each rng In Worksheets(数据源).Range(Data).Rows(1).Cells If VBA.InStr(1, strFld, , & VBA.Trim(rng) = 0 Then _ pv.AddDataField pv.PivotFields(rng.Value), & rng.Value, xlSum Next rng pv.ManualUpdate = False Application.ScreenUpdating = TrueEnd SubP

47、rivate Sub Worksheet_Deactivate() Dim pv As PivotTable Set pv = Sheet1.b3.PivotTable For Each dFld In pv.PivotFields strFld = strFld & , & dFld.Name NextEnd Sub,返回,数据透视表的格式设置,修改数据透视表的格式 通常情况下，数据透视表创建完成后，还需作进一步的修饰后才能得到更令人满意的效果。除了使用最普通的单元格格式（如字体、字型、颜色等）设置方法外，Excel还提供了其他一些针对透视表的格式设置方法。,数据透视表的自动套用格式“自动套

48、用格式”功能提供了“报表1”至“报表10”共10种缩进格式，以及“表1”至“表10”共10种非缩进格式，另外还提供了“传统数据透视表”和“无”两种特别格式，总共提供了22种数据透视表格式。,特别格式：“无”格式的运用“无”格式实际上也是数据透视表的一种格式，它隐去了传统数据透视表列区域标题中的“求和项”，及其上方的“数据”等文字，加上表格线后，使得数据透视表更接近普通数据表的格式。,刷新后如何保持调整好的列宽使用中经常遇到这样一种现象：调整好数据透视表的列宽后，如果执行了刷新指令，此时列宽会自动发生变化，无法保持手工设置的宽度。出现这种情况，是因为在默认设置下，数据透视表刷新数据之后，列宽就会

49、调整为列字段默认的“最适合宽度”，而之前所设置的的宽度不再有效。为此可采取以下方法实现在数据透视表刷新后仍能保持调整好的列宽。（1）通过设置“数据透视表选项”保持列宽。在“数据透视表选项”对话框中，取消“自动套用格式”的勾选。,（2）在应用了自动套用格式的数据透视表中保持列宽如果在数据透视表中应用了自动套用格式后，又希望在刷新后保持设定的列宽，则不能使用上面介绍的方法。此时，可以采用直接在列字段名称的前面或后面加入适量空格的方式来调整列宽。,（3）使用VBA代码控制数据透视表保持固定列宽如果希望透视表有始终固定的列宽（不管是否使用自动套用格式）可以使用VBA代码来实现。例如：要求始终保持A至E

50、列的列宽为10磅，方法如下：1、按ALT+F11 打开VBA窗口，再按F7键打开代码窗口2、在代码窗口中输入以下VBA代码：Private Sub Worksheet_SelectionChange(ByVal Target As Range) Columns(A:A).ColumnWidth = 10 Columns(B:B).ColumnWidth = 10 Columns(C:C).ColumnWidth = 10 Columns(D:D).ColumnWidth = 10 Columns(E:E).ColumnWidth = 10End Sub,批量设置数据透视表中某类项目的格式借助“