excel数据透视表分页,数据透视表分页显示
我们经常用Excel生成指定类型的报表,但是很多报表的原始数据是混在一起的,或者分布在各个子表中。现在有了Excel pivot report的功能,我们可以快速的将混合的数据拆分成分页的报表,或者将独立的子表整合成汇总报表。
简单分页,混杂报表变身分页报表
访问:
微软office 2016正式版KMS工具激活工具
为了便于统计,工作中的很多报表数据都收集在一起。比如公司HR编制的员工考勤报表,所有员工按照月度统计综合(图1)。
图1原始数据
现在老板需要查看每个月、每个部门的考勤,所以需要把上面的汇总数据分别按照月份、部门类别进行整理。这种操作可以借助透视报表轻松完成。以次月排序为例。
点击“插入透视表”,在表区选择原始数据中的数据区,设置透视表的位置,选择“新建工作表”,新的工作表中就会生成一个透视表(图2)。
图2创建数据透视表
在数据透视表窗口中,选中数据透视表字段中的员工、缺勤天数和月份。然后用鼠标拖动“月”到下面的过滤器,拖动“员工”和“旷工天数”到行中(图3)。
图3数据透视表字段设置
切换到“透视表选项显示报表过滤页”,在打开的窗口中选择“月”进行过滤。过滤是根据上述过滤字段设置的,所以如果是其他报表,如果要过滤部门,只需在上述过滤字段中添加“部门”字段即可(图4)。
图4显示了报告过滤器页面。
以上操作完成后,点击【确定】,11月和12月的两个子表将被添加到当前工作簿中。切换到各自的子表后,员工缺勤数据一目了然。显然,通过使用自定义字段的方法,我们可以选择所需的数据进行显示,并自动生成所需的报告(图5)。
图5子表的自动生成
快速整合,独立子表融合为汇总表
上面的介绍是把总表的数据拆分成具体数据需要的子表。实际工作中有很多逆向操作,就是汇总多个子表的内容。汇总操作也可以通过透视表完成。例如,原始数据的副本如下。销售部的销售业绩已经按月统计了。现在需要分别统计第一、第二、第三销售部门的销售额(图6)。
图6销售样本数据
按Alt D快捷键,然后继续按P键弹出透视表和数据透视图向导,选择多个合并计算区域。继续下一步,选择“创建单页字段”。在所选区域,根据提示选择一月工作表的“$C$1:$D$4”(即只需选择C列和D列的内容),点击“添加”将其添加到下面的列表中。重复上述操作,依次将二月工作表和三月工作表对应的数据区添加到列表中,最后点击Finish(图7)。
图7选定区域数据
在打开的新透视表中选择“第1页”,并将其更改为“销售部门统计”,这样每个销售部门的统计数据就会自动列在新的透视表中。透视表可以非常方便的列出各个子表的具体数据,并按照指定的要求进行自动统计(图8)。
图8统计多词表的数据透视表
当然,根据我们的实际需要,这个透视表也可以利用行标签的筛选来灵活显示数据。例如,只要显示销售一部分的数据,只要在行标签的下拉列表中勾选销售一部分,这里就会显示销售一部分的统计数据。另外,在数据透视表中,我们可以根据自己的实际需要,通过组合函数来展现更多的数据需求。
例如,原始数据透视表数据也包含收集的金额。现在,需要在透视表中加入收款率(即收款额/销售额)这一指标。切换到数据透视表工具字段、项目和集合计算字段。在打开的新增字段窗口中,点击将默认字段名称改为“收款率”,在公式栏中输入“=”,然后选择“收款金额”,点击“插入字段”。在插入的收款金额字段后,输入除数(即/),然后选择“销售”并单击插入字段。
图9插入字段
这样就在原来的透视表中增加了一个收款率字段,这个字段会根据上面的公式自动计算出来。为了方便显示,显示单元数据可以设置为百分比显示(图10)。
图10插入一个新字段
当然,我们可以根据自己的实际需要插入更多的计算字段。即使原始数据中没有相应的统计。通过自己插入字段,可以在透视表中显示更多的数据。