数据透视表应用案例

同时使用计算字段和计算项
例：比较分析费用控制的占比和比较差异 7.22的数据源是2006、2007年制造费 用的明细账，计算出2006、2007年发生费 用的差额；可控费用和不可控费用占总费用 的百分比。
7.23 多表合并
利用“多重合并计算数据区域”实现 注意（1）需要自定义页字段 （2）多重合并计算数据区域作为其数 据源的数据透视表只能有 3 个基本字段： 行，列和值。除了这些基本字段之外，最多 还可以创建 4 个页字段。
例子
合理进行目标完成率指标统计 7.16
注意：“完成”项目中有空白数据，如何使 空白数据不参与计算？ 建立数据透视表—“完成”加入页字段—隐 藏空白数据
修改计算字段
对于已经定义的计算字段可以根据要求进行 修改。
计算项
例7.18 点击某字段—数据透视表—公式—计算项
例7.19统计各商店不同时期的销售增长率
3、计算字段和计算项
数据透视表创建完成，允许用户自定义进行计算。自定义计 算，必须使用“计算字段”或“计算项”功能。在创建了自定 义的字段或项之后，Excel就允许在数据透视表中使用它们， 它们就像是在数据源中真实存在的一样。

计算字段是通过对数据透视表中现有的字段进行计算后得到 的新字段。 计算项是指通过对数据透视表现有某一字段内的项进行计算 后得到的新的数据项。 计算字段和计算项可以对数据透视表中的现有数据(包括其 他的计算字段和计算项生成的数据)进行运算，但无法引用数据 透视表之外的工作表数据。
2.2多个页字段的排列
当我们需要多个页字段同时出现在同一 数据透视表中的时候，可以直接将字段拖入 “页字段”区域。 注意：根据“表格选项”中的“页面布局” 搭配使用。
2.3多页显示
在一个页字段中有若干个不同的数据项，如 何将这些数据项分别显示在不同的工作表中？ 利用“数据透视表工具栏”中的“分页显示” 功能。它会自动建立以一个页字段中不同数 据项的工作表名的数据透视表。
2.1多个页字段项的显示
当页字段的项目有多个时，只想显示其 中的某几项，能做到吗？怎么做？
方法：点击页字段---右键---字段设置—将 要隐藏的选项选中
按某一关键字排列。 例如：按销售收入排序。
操作：选中任何一个“行字段”的项，然后 到数据透视表的悬浮条中找到“排序并显示 出前10个”
2、数据的显示方式
“差异”显示方式 一个会计年度结束之后，公司都想知道预 算额与实际发生额差距到底有多大，以便于 来年在费用预算中做出相应的调整。
注意：“基本项”选择“实际发生额”，差异计算就会在“预算额” 字段显示“预算额”-“实际发生额”的差额，体现预算编制水平； 反之，体现实际支出水平。

说明
1、 行：“行”字段始终由数据源中的第一列组成。 2、 列：“列”字段包含数据源中的其他列。使用多重计算数据 区域的数据透视表将原始数据集中的所有字段（减去第一列， 用于作为“行”字段）合并到一个名为“列”字段的一种高 级字段中。原始数据集中的字段成为“列”字段的数据项。
注意，开始时透视数据表将“计数”应用于“列”字段，如果 将 “列”字段设置更改为“求和”，则“列”字段下的所有数据项都 会收到影响。 3、值：“值”字段包含“列字段下所有数据项的值。注意，甚 至原来数据集中的文本字段也被视为数字值。 4、页：“页字段”是多重合并计算数据区域数据透视表中可以 直接控制的惟一字段。最多可以定义 4 个页字段。
综合案例
3、不等距步长自动组合 当需要组合的数据项比较多时，手动组 合的操作比较繁琐，而且对于新增数据不能 自动进行组合。如果要实现不等距步长数据 的自动组合，则可借助向数据源中添加辅助 列的方法来实现。
步骤：第一、在数据源中增加辅助列 利用IF函数实现 第二、建立数据透视表，以辅助列替 代原有的字段

含有合并单元格的数据源 含有合并单元格的数据源表在创建数据 透视表时必须对数据源进行修改（否则就会 出现空白数据项），先对合并的单元格取消 合并，找出空白格，选择中区域按F5定位-定位条件-空值--确定，就选中了所有的空 格，在编辑栏中输入“=”加上空格上方单元 格中的值，按“Ctrl+Enter”对空格进行填 充
2.5.1文本型数据项组合
例1：按一级科目创建科目汇总表 2.5 将某一项目所在的所有区域选中， 右键—组及显示明细数据—组合 选中新字段---字段设置—求和 例2：会计数据整理 2.5.1 若需要对合并后的数据汇总，则将原有字 段删除即可。
2.5.2数值型数据项的组合
1、按等距步长组合（例）
注意：时间的表示 1、若将00:03:34表示为03’34，可以通过自定义，将格式定义为mm’ss即可。 2、=TEXT（某单元区域，“mm’ss”） 热身赛\利用数据透视表进行账龄分析.xls 2.5.2
百分比显示方式 差异百分比 按某一字段汇总：可用来设置累计数。 占同行数据总和百分比 占同列数据总和百分比 指数
指数
利用“指数”数据显示方式，可以对数据透 视表内的一系列数据的相对重要性进行跟踪 例7.11
杭州地区的微波炉销售指数1．57为最高， 说明微波炉产品的销售在杭州地区的重要性 很高，如果该产品在杭州地区发生短缺，将 会影响到整个微波炉市场的销售。 杭州地区微波炉指数1．57=((杭州地区 微波炉销售金额68500)X(总体汇总之和 7201700))／((行汇总247000)X(列汇总 1269500)
例7.23 将郑州、南阳、新乡、洛阳四张表做成一张数据透视表
注意：
数据透视表要求的数据源必须符合相应的要 求，若遇到不符合要求的数据源，我们需要 对其进行修改，使之符合建立数据透视表的 要求。 例：热身赛\根据凭证流水文件得到科目汇总 表.xls
修改方法：
彻底按照要求对数据源进行修改。可以加入 一列，用MONTH函数直接计算出月份；再 加入一列用LEFT函数取出总账科目代码。
4、日期型数据组合
对于日期型数据，数据透视表提供了更多的组合选 项，可以按秒、分、小时、月、季度、年等多种时 间单位进行组合。
如何按周组合？
组合—日—输入对应数字
5、选定区域不能分组的解决方案
导致分组失败的主要原因： 一是组合字段的数据类型不一致（有空白数 据项、即同一列有数字、有文本）； 二是日期数据格式不正确；（用TYPE（value）=2进行检
①EXCEL数据列表或数据库（普通的EXCEL表格） ②外部数据源（可以文本文件、Microsoft SQL Server\Access\dBASE数据库文件） ③多重合并计算数据区域（可以对二个或二个以上 的多重数据单元格,多个独立 的Excel数据列表， 数据透视表在创建过程中可以将各个独立表格中的 数据信息汇总到一起） ④另一个数据透视表或数据透视图（创建完成的数 据透视表也可以作为数据源来创建另外一个数据透 视表）
2、不等距步长手工组合（例） 对于数值型数据项，除了可以使用等距步长进行 组合，还可利用手动组合实现不等距步长组合。
步骤：第一、建立数据透视表 第二、以“应税收入净额”降序排列 第三、将区间范围内的“姓名”和“应税收入净额” 区 域一同选中，组合 第四、更改字段及项目名称，并通过“字段设置” 增 加分类汇总方式
测，即该单元格是文本，不是日期格式） 如：某一字段汇总数据源 解决方法：可以用分列功能，选择“日期”，将文本转换为日期。
三是数据源引用失效
2.6、数据透视表中进行计算
1、同一字段使用多种汇总方式 Excel数据透视表对数据区域中的数值字 段使用求和方式汇总，对非数值字段则使用 计数方式汇总。事实上，除了“求和”和 “计数”以外，数据透视表还提供了其他多 种汇总方式，包括“平均值”、“最大值”、 “最小值”和“乘积”等。
2.4数据透视表中设置自动筛选
将行字段与列字段都变为自动筛选的功能： 选中数据透视表行字段或列字段同一行，却 在数据透视表以外的右侧相邻的第一个单元格， “数据”—“筛选”—“自动筛选”，即可。取消 自动筛选方法类同。
2.5数据透视表的“组合”功能
虽然数据透视表提供有强大的分类汇总功能， 但由于数据分析需求的多样性。使得数据透 视表的常规分类方式不能应付所有的应用场 景。为了应对这种情况，数据透视表还提供 了另一项非常有用的功能，即项目组合。它 通过对数字、日期、文本等不同数据类型的 数据项采取多种组合方式，大大增强了数据 透视表分类汇总的适应性。
数据透视表 应用案例
一、函数与数据透视表的比较
对于一些复杂的表格，可以使用函数实 现；但从简单、灵活的角度来看，数据透视 表更符合要求。
函数法：运算速度慢，特别是在数据量特别 大的时候，运算速度更加慢，并且会导致电 脑死机，并且限制于固定的数据源框架。 数据透视表的优点，最大的优点是可以随着 数据源的框架的改变而随之更新。可以根据 不同的需要，进行不同的字段的变的换，来 达到不同的数据分析要求；数据的运算效率 特别快；
了解数据透视表的结构
数据源：从中创建数据透视表的数据列表或多维数 据集； 分页符：数据透视表中进行分页的字段； 行字段：在数据透视表中具有行方向的字段； 列字段：信息的种类，等价于数据列表中列； 数据区:中只能统计数值型的数据，如果是文本型 的数据，则默认统计为计数，如改为统计求和，则 显示为0；
计算字段
方法：数据透视表面板---公式---计算字段 注意： 1、只能利用已有的字段、常量和有限的函 数进行公式的定义。 2、函数只能用IF、AND、NOT、OR、 COUNT、AVERAGE、TEXT等函数
例7.12-7.15
小于30天 =IF(AND(TEXT("2008-6-1","#")-欠款日期>0,TEXT("20086-1","#")-欠款日期<=30),应收账款余额,0)

通俗上讲，数据透视表是顾名思议是对 数据源进行透视，并且进行分类汇总，比较 大量的数据、进行筛选，达到快速查看源数 据的不同的统计结果；有机的综合了数据的 排序、筛选、分类汇总等常用数据分析方法 的优点，并且可以调整分类汇总的方式，灵 活的以多种不同的方式展示数据源的特征。
什么样的数据源可以创建数据透视表