第2章
对数据列表进行分类汇总分析
的基本概念与方法
与第1章中介绍的排序、筛选与分类小计等数据列表初级处理相比较，分类汇总分析是对于数据列表的一种高级处理。
如果说掌握数据列表的初级处理方法有助于企业内基层业务人员提高事务处理的效率的话，那么掌握分类汇总分析这种处理方法将会为企业内中层以上管理人员做好管理决策提供有用的参考信息(当然掌握数据列表的初级处理技术对于中层管理人员也是有用的)。
由于企业业务活动的日积月累，一些重要的数据列表会包含数量巨大的明细数据，这些数据在有关业务的发生过程中曾经发挥过不可或缺的重要作用，但在失去时效之后似乎就没有直接的应用价值了。
然而，正如我们在“前言”中指出的，这些数据列表中实际上蕴藏着许多对于管理人员制订各种决策具有重要参考作用的宝贵信息，我们需要采用有效的方法从数据列表中提炼出这些重要信息。
这些重要信息不是数据列表中包含的明细数据本身，而是各种数量较少的汇总数据。
换言之，为了获取这些有用信息，必须对数据列表做适当的“由细到粗”和“由多到少”的汇总处理。
例如，从记录企业在过去某段时间内发生的销售状况的数据列表中，可以提炼出关于销售额在不同类别产品或不同销售地之间的分布，或者销售额随年月变化的趋势的汇总数据；从记录企业在过去某段时间内发生的原材料质量指标的数据列表中，可以提炼出有关原材料平均合格率在其不同供应厂家之间的分布，或者这些合格率随年月变化的趋势的汇总数据；从记录企业人事资料的数据列表中可以提炼出不同 面向决策的Excel高级数据处理 部门中职工的年龄结构、学历结构以及职称结构等等。
很显然，这些表示某个关键变量分布结构或发展趋势的汇总数据对于企业制定有关决策具有重要的参考价值。
本书所要介绍的多维分类汇总分析就是从数据列表保存的成千上万的记录中提炼出这些宝贵信息的有效手段。
本章只介绍对数据列表进行分类汇总分析的基本概念与方法，第4章将进一步阐述分类汇总分析在概念与方法上的拓展。
还要指出一个重要事实，那就是：在适当的查询技术的帮助下，我们可以将数据库中某一个或几个表中的有关数据导入到Excel中以生成一个数据列表，因此，掌握了从数据列表中提炼出有用信息的方法之后，也就可以使用这些方法从数据库中提炼出有用信息。
第5章将说明对数据库中的数据进行查询并在此基础上对其中的数据做多维分类汇总分析的方法。
2.1被汇总字段、汇总参考字段以及分类汇总表 我们将数据列表中那些作为汇总对象的字段称为被汇总字段。
原则上说，一个数据列表中的每个字段都可以在特定的问题中被当作被汇总字段，但其中那些描述业务量的数值型字段(例如，产品产量、销售收益、原材料质量指标或产品合格率等)常常是管理人员关注的中心，因而也就经常成为被汇总字段的首选字段。
在一个数据列表中求出所有记录的被汇总字段的一个单一的汇总值(例如，单一总计值、单一总平均值或单一总数等)没有任何意义。
人们只有在比较中才能认识事物的性质，因此，只有参考着数据列表中的另一个特定字段的各个不同字段值对被汇总字段求出相应的汇总值(也就是对于那个特定参考字段的每个字段值，将那个字段等于该字段值的全部记录挑选出来，并求出它们的被汇总字段之值的总和或平均值或个数)，从而构成一个多行两列的对照表：表中左列列出该特定参考字段的不同字段值，右列则列出对应的被汇总字段汇总值(总计值、平均值或发生次数等)，这个对照表便会给出被汇总字段随该参考字段的不同值变化的结构性或趋势性的特征，因而成为具有决策参考价值的信息。
从数据列表中获取被汇总字段汇总值随另一特定参考字段的不同字段值变化的对照表的处理过程称为分类汇总分析。
在对被汇总字段进行分类汇总分析时，以其各个字段值作为参考或分类依据的那个字段称为汇总参考字段。
被汇总字段与汇总参考字段是在对数据列表进行分类汇总分析时所涉及的两个基本概念
1。
总之，在对一个数据列表进行分类汇总分析时，必须根据需要将其中的一个字段选为被汇总字段，并将另一个(或几个)字段选为汇总参考字段。
原则上说，数据 1在第1.2.2小节说明“分类小计”功能时已经提到过这两个名词。
•26• 第2章对数据列表进行分类汇总分析的基本概念与方法 列表中每个字段都可以被选为汇总参考字段，甚至连已被选为被汇总字段的字段也可以同时被选为汇总参考字段。
决策者要根据面临的决策问题的焦点或侧重点从数据列表的各个字段中选出所需使用的汇总参考字段，而且在一般情况下，可能还需要选定不止一个汇总参考字段。
随着问题焦点的转移，可能要将不同的字段选为汇总参考字段(有时可能还需要将不同的字段选为被汇总字段)并进行新的分类汇总分析。
我们把在数据列表中参考一个汇总参考字段对某个被汇总字段进行的分类汇总分析称为对于数据列表的简单分类汇总分析，在少数情况下，如果数据列表中存在着一个只有两三个字段值的字段，也可以将这个字段作为第二汇总参考字段包括到简单分类汇总分析中来(并将基本的汇总参考字段称为主要汇总参考字段)。
本章将讨论对数据列表进行简单分类汇总分析的方法。
在简单分类汇总分析中生成的、显示被汇总字段的汇总值随汇总参考字段的不同字段值变化的对照表称为被汇总字段关于特定汇总参考字段的(简单)分类汇总表。
如前所说，一个简单分类汇总表可以用Excel工作表中的一个多行两列的范围来表示。
在使用了主要与第二两个汇总参考字段的情况下，分类汇总表扩展为一个包含三至四列的矩形范围，其中左侧一列列出主要汇总参考字段的各个字段值，顶端一行列出第二汇总参考字段的各个(两个或三个)字段值，位于各个行列交叉点的单元格中则列出与左侧与顶端的两个汇总参考字段字段值对应的被汇总字段汇总值。
对于“ABC数据”数据列表来说，“净销售额”(或“毛销售额”)是被汇总字段的首选字段，“类别”、“渠道”、“销售地”、“日期”等字段则都可以用作汇总参考字段。
图2-1左图显示了“净销售额”总计值随“类别”字段的不同字段值变化的分类汇总表，该图中图显示了“净销售额”总计值随作为主要汇总参考字段的“类别”与作为第二汇总参考字段的“渠道”的不同字段值变化的分类汇总表，该图右图则显示了“净销售额”的总计值随“日期”的不同值变化的分类汇总表的顶端部分内容。
图2-
1 尽管描述企业业务量的数值型字段(例如“ABC数据”数据列表中的“净销售•27• 面向决策的Excel高级数据处理 额”或“毛销售额”)经常被当作被汇总字段，但有时也可以反过来将这种字段当作汇总参考字段，而将“日期”或其他各种文本型字段看成被汇总字段(此时的汇总方式只能是计数)，从而生成业务发生次数(例如销售次数)随业务量(例如销售额)数值变化的分类汇总表。
当然，考虑到在一个数据列表出现两个或更多个具有相同业务量的记录的概率很小的事实，这样制成的分类汇总表所包含的(与不同业务量对应的)行数很大的而大部分行中的业务发生次数都等于
1，所以它们没有多大的决策参考价值。
但是如果把业务量的各种取值组合成一系列具有适当宽度的区间(分组)
1，从而生成表示业务发生次数随业务量区间(分组)的变化状况的分类汇总表，那么这种分类汇总表就会具有较高的决策参考价值，图2-2左图显示了针对“ABC数据”数据列表制成的销售次数随销售额分组变化的分类汇总表。
图2-
2 如前所说，必要时也可以将一个业务量字段既看成汇总参考字段又看成被汇总字段，将这种做法与业务量字段值的分组结合起来，就会生成业务量的总计值或平均值随业务量分组变化的分类汇总表。
图2-2中图与右图就给出了显示“ABC数据”数据列表中净销售额平均值与总计值随净销售额取值区间变化状况的分类汇总表。
关于图2-2所示的几种分类汇总表的制作方法的说明可以参见例4-18。
在使用一个汇总参考字段的条件下，(在添上两个适当的字段名之后)简单分类汇总表本身也呈现为一个具有两个字段的数据列表的形式，其中第一字段是原数据列表中被当作汇总参考字段的那个字段，其各个字段值就是原数据列表中的字段的各个不同值；第二字段是原数据列表中被当作被汇总字段的那个字段，它的字段值就是该字段的各个分类汇总值。
这个数据列表的记录个数等于原数据列表中作为汇总参考字段的特定字段的不同字段值的个数。
在使用了主要与第二两个汇总参考字段的情况下，分类汇总表不再具有数据列表的性质。
但是通过改变结构可以将它转变成一个带有三个字段的数据列表(当然也要添上三个适当的字段名)，其中前两个字段是原数据列表中作为两个汇总参考字段 1有关这种组合的概念与操作方法可参见第4.4.1小节。
•28• 第2章对数据列表进行分类汇总分析的基本概念与方法 的字段，它们在新数据列表各个记录中的值是原数据列表中，两个汇总参考字段的不同值的各种可能组合；而第三个字段是原数据列表中作为被汇总字段的那个字段，其字段值为原数据列表被汇总字段的、与两个汇总参考字段的各个字段值组合对应的分类汇总值。
这个数据列表的记录数等于原数据列表中两个汇总参考字段不同值个数的乘积。
在图2-3所示工作表的范围C2:E9中重新显示了“ABC数据”数据列表中“净销售额”总计值随作为主要与第二汇总参考字段的“类别”与“渠道”的不同字段值变化的分类汇总表(但它与图2-1中图所示的分类汇总表金额单位不同)。
工作表范围H2:J16中显示了由该分类汇总表转变而成的数据列表。
图2-
3 可以利用图形(柱形图、折线图或饼图)来显示简单分类汇总表中的信息，从而直观地显示出该分类汇总表中给出的、被汇总字段的汇总值随汇总参考字段的各个字段值变化的状况。
在包含两个汇总参考字段(而第二汇总参考字段只有两三个字段值)的情况下，可以用一个图形(柱形图或折线图)中的两组或三组不同颜色的柱形(两条或三条折线)来显示在第二汇总参考字段取不同字段值时，被汇总字段汇总值随主要汇总参考字段的不同值变化的状况
1。
例如，图2-3所示的分类汇总表2中的数据可以用图2-4~图2-6等插图中显示的柱形图、折线图、条形图与饼图(包括平面饼图与立体饼图)来表现。
饼图中饼角旁的注释既可以是相应数据点的Y数据(即各个饼角表示的汇总值的大小，见图2-6)，也可以是相应数据点的X数据与Y数据的混合(见图2-7左上图)，在第一种情况下， 1如果将字段值多于两三个的字段当作第二汇总参考字段的话，表示分类汇总信息的图形就会因为显示太多柱形组(或太多的折线)而显得混乱，另外，与柱形图和折线图不同，饼图只能显示一组饼角(因此下面图2-6所示饼图的标题与图2-
4、图2-5所示的其他类型图形的标题有一些区别)。
2为了使图形中显示的坐标轴刻度值比较短，所以图2-3所示的分类汇总表使用了以万元为单位的金额数据。
•29• 面向决策的Excel高级数据处理 各个数据点的X数据用图例来说明(值得指出，各种类型图形的图例都是对各个数据系列的说明，唯独饼图的图例是对各个数据点的说明)。
不同类别商品净销售额比较 25 批发零售20 不同类别商品净销售额比较 25 零售批发20 15 15 净销售额总计值(万元) 净销售额总计值(万元) 10 10
5 5
0 艺术品服装自行车儿童用品食品陶瓷用具运动器材
0 艺术品服装自行车儿童用品食品陶瓷用具运动器材 图2-
4 净销售额总计值(万元) 不同类别商品净销售额(万元)比较 25 批发 零售 20 运动器材陶瓷用具 不同类别商品净销售额(万元)比较 批发零售 食品 15 儿童用品 10 自行车 5
0 艺术品服装自行车儿童用品食品陶瓷用具运动器材 服装艺术品
0 图2-
5 销售
5 10 15 20 25额 不同类别商品零售净销售额（万元）比较 6.98 3.799.05 12.25 12.173.31 9.52 艺术品服装自行车儿童用品食品陶瓷用具运动器材 不同类别商品零售净销售额（万元）比较 6.983.799.05 12.25 12.17艺术品服装自行车儿童用品 3.31食品陶瓷用具运动器材 9.52 图2-
6 •30• 第2章对数据列表进行分类汇总分析的基本概念与方法 对于饼图来说还有一种变体，即在各个饼角旁注明各个数据点的Y数据在作为它们之和的总值中所占的百分比(见图2-7除左上图之外的其余三个图)，另外，在饼角的显示方式上还可以使一个饼角“向外伸出”或者使所有饼角都“向外伸出”(见图2-7左下图与右下图)。
有必要说明一点，虽然在绝大部分情况下，在分类汇总分析中只使用一个被汇总字段。
然而，在某些存在着两个意义相近的字段的数据列表中，不排除在必要时可以将这两个字段同时当作被汇总字段，对它们的总计值随其他字段的字段值变化的状况进行联合的分类汇总分析。
“ABC数据”数据列表中的“净销售额”与“毛销售额”就是两个意义相近的字段，可以想见，将它们相对“类别”等字段做联合分类汇总分析有时是有意义的。
1 不同类别商品零售净销售额（万元）比较 运动器材,6.98陶瓷用具,3.79 艺术品,12.17 食品,9.05 服装,3.31 不同类别商品零售净销售额比较运动器材 陶瓷用具12%7% 艺术品21% 食品16% 服装6% 儿童用品,12.25 自行车,9.52 儿童用品21% 自行车17% 不同类别商品零售净销售额比较运动器材 陶瓷用具12%7% 艺术品21% 食品16% 服装6% 儿童用品21% 自行车17% 不同类别商品零售净销售额比较 运动器材陶瓷用具12% 7% 艺术品21% 食品16% 服装6% 儿童用品21% 自行车17% 图2-
7 很显然，用手工方法对包含数千个记录的数据列表来制作分类汇总表是一个十分繁重的任务。
但在Excel中针对各种包含大量记录的数据列表来制作被汇总字段关于一个(或两个)汇总参考字段的分类汇总表(并且制作出相应的图形)却毫无困难。
许多情况下可以(或者需要)将数据列表中的若干个字段同时用作汇总参考字段(将其中一个作为主要汇总参考字段，而将其余各个作为辅助汇总参考字段)。
区别 1下面第4.7节将说明将数据列表中的两个被汇总字段相对一个或几个汇总参考字段做联合分类汇总分析的问题。
•31• 面向决策的Excel高级数据处理 于简单分类汇总分析，我们将一个数据列表中被汇总字段关于多个汇总参考字段的分类汇总分析称为多维分类汇总分析，第4章将对多维分类汇总分析进行深入的讨论。
2.2对数据列表做分类汇总分析的工具 2.2.1应用分类小计功能对数据列表做分类汇总分析 前面讨论过的分类小计是Excel提供的一种与分类汇总分析有关的功能，图1-11显示的就是在将“净销售额”字段作为被汇总字段并将“类别”与“渠道”作为两个汇总参考字段的情况下，利用Excel的分类小计功能对“ABC数据”数据列表做分类汇总分析后获得的两种结果。
分类小计作为一种分类汇总分析的工具有两个特点：第
一，用这种工具处理后的数据列表具有一种大纲结构，因此既可以只显示汇总数据也可以将明细数据与汇总数据同时显示出来；第
二，它保持着数据列表原有的字段结构，并将被汇总字段的分类汇总值插入到汇总参考字段等于相同字段值的一组记录下方、与该字段对应的单元格中
1。
上述第一个特点使分类小计功能在生成与某些企业经营管理业务有关的报表方面有一定的用处，(按类别或销售地统计的销售金额或销售数量表，按部门层次统计的职工工资表或者按零部件类别层次统计的零部件库存数量表等是这种报表的例子)，但是对于向中高层管理人员提供凝炼汇总信息来说，这一特点并无特别重要的价值。
相反的，正是由于有上述两个特点，一方面无法将具有决策参考价值的汇总数据从明细数据中“剥离”出来，另一方面当同时考虑的汇总参考字段增多时大纲结构的层数随之而增加从而使表的结构变得更加复杂，因此分类小计功能不是分类汇总分析的方便工具，特别不适于多维的分类汇总分析。
1必要时也可以将它设置为置于一组相应的记录的上方。
•32• 第2章对数据列表进行分类汇总分析的基本概念与方法 AB
C 1 净销售额
2 总计值
3 艺术品
4 服装
5 自行车
6 儿童用品
7 食品
8 陶瓷用具
9 运动器材 10 11
D E F
G 零售121736.71 33073.7295188.78122490.9890528.55 37933.869827 批发230676.69121916.56 97576.44149743.41109313.04 32344.1756852.12 图2-
8 除了分类小计功能之外，在Excel中可以使用数据透视表和D函数分析法这两种更合适的工具来对数据列表做分类汇总分析(图2-1与图2-2中显示的各个分类汇总表就是利用数据透视表工具制成的)，下面两节将通过在“ABC数据”数据列表的基础上生成图2-8所示形式的(“净销售额”关于“类别”与“渠道”这两个汇总参考字段的)分类汇总表的过程来说明这两个工具的基本使用方法
1。
2.2.2应用数据透视表对数据列表做分类汇总分析 数据透视表(pivottable)是Excel提供的理想的多维分类汇总分析工具。
本小节将只介绍利用数据透视表来做简单分类汇总分析的方法，第4章将会进一步说明利用数据透视表做多维分类汇总分析的各种方法。
在一个数据列表的基础上利用数据透视表工具来生成分类汇总表的手段是选择菜单命令“数据|数据透视表和数据透视图”，然后在以“数据透视表和数据透视图向导”为名的一系列对话框(以下简称“向导”对话框)的指引下，针对数据透视表的数据源(即所要分析的数据)在何处(是来自Excel数据列表还是来自外部数据源，若是来自数据列表则要确定该数据列表所在的范围，若是来自外部数据源则要确定具体的数据源并选中所需要的各个字段2)、它应具有的布局，以及它的显示位置(是放在当前工作表中还是放在一个新建工作表中)完成一系列设置，最终将它制成。
上述各项设置中最核心的是在“向导--布局”对话框中完成的、对分类汇总表布局的设置，该对话框利用一个示意图画出了数据透视表的结构框架(图2-9)，其中包含着位于主体部分的三个区域：中间区域是供放置被汇总字段的“数据域”， 1这个分类汇总表与图2-3所示的分类汇总表是等效的，二者的区别只是金额单位不同而已。
另外，分类小计功能的局限性使它无法直接生成这种形式的分类汇总表。
2关于在外部数据源的基础上制作数据透视表的问题可参见第5.6.2节。
•33• 面向决策的Excel高级数据处理 左侧是供放置一个(或多个)汇总参考字段的“行域”，顶部是供放置一个(或多个)汇总参考字段的“列域”
1。
操作者可以按照自己希望建立的分类汇总表的布局形式，从对话框左侧的字段名列表中将作为被汇总字段与汇总参考字段的字段名分别拖放到数据域与行域(或列域)中。
当一个数值型字段的字段名被拖放到数据域中时，它立即就会变成“求和项:(字段名)”，表示汇总方式为求和，即求总计值，但操作者在需要时可以方便地将它改成计数(即求发生次数)、求平均值、求极小值或求极大值等其他的汇总方式；当一个文本型字段的字段名被拖放到数据域中时，由于其唯一可实施的汇总方式为计数，因此这个字段名会立即变成“计数项:(字段名)”。
图2-
9 我们将拖放到行域与列域中的汇总参考字段分别称为行域字段与列域字段。
在只使用一个汇总参考字段时，通常应将其字段名拖放到行域中；在使用两个汇总参考字段时，应将主要汇总参考字段的字段名拖放到行域中，而将第二汇总参考字段的字段名拖放到列域中。
这样生成的分类汇总表具有图2-1左、中两图所示的布局外观：在只使用行域字段时它由两列组成，左侧一列列出行域字段的各个不同的字段值，右侧一列则列出对应的被汇总字段汇总值；在同时使用行域字段与列域字段时，除了在左侧一列中列出行域字段的各个不同字段值之外，在顶端一行中还列出列域字段的各个不同字段值，数据域各个单元格中列出对应的被汇总字段汇总值。
数据透视表形式的分类汇总表可以根据操作者的设置在垂直方向的最下方添加或者不添加一个总计行，即所谓“列总计”行，其中列出与行域字段全部字段值(和列域字段的各个字段值)对应的被汇总字段汇总值，在汇总方式为求和或计数时，这行中(各个单元格中)的汇总值等于其上方各个汇总值之和。
在有列域汇总参考字段 1在数据透视表主体之外，在其左上方还有一个供放置其他辅助汇总参考字段的“页域”，第4.1节将说明页域的用途。
•34• 第2章对数据列表进行分类汇总分析的基本概念与方法 时，还可以根据操作者的设置在水平方向的最右侧添加或者不添加一个总计列，即所谓“行总计”列，其中列出与列域字段的全部字段值和行域字段的各个字段值对应的被汇总字段汇总值，在汇总方式为求和或计数时，这个汇总值等于其左侧各个汇总值之和。
可以将两个或更多个汇总参考字段设置为行域字段(或列域字段)，但这时所生成的分类汇总表就会在垂直(或水平)方向以复杂的层次结构来列出相应汇总参考字段的字段值与被汇总字段的汇总值。
这种具有多层结构的分类汇总表通常会显得繁琐而使人不易理解其中的信息(而且不易于用适当的图形来显示这些信息)。
因此，较好的做法是只在行域中放置一个汇总参考字段，在另一个字段只具有两三个字段值的条件下，可以将它作为第二汇总参考字段而放置在列域中(在列域中，更不适合放置两个以上的字段)。
数据透视表与作为其基础数据(数据源)的数据列表之间保存着一种联系，在该数据列表中的数据发生变化之后，数据透视表虽然不会自动发生相应的改变，但是只要在其中任一单元格被选中的条件下选择一次菜单命令“数据|刷新数据”(或者选择一次快捷菜单命令“刷新数据”)，数据透视表中的数据就会发生相应的改变。
下面来说明使用数据透视表生成具有图2-8所示形式的分类汇总表的方法。
【例2-1】试应用数据透视表功能对于“ABC数据”数据列表生成一个具有图2-8所示形式的、“净销售额”总计值随“类别”与“渠道”两个汇总参考字段的字段值变化的分类汇总表。
解在将“ABC数据”数据列表所在范围中任一单元格选中的条件下，选择Excel的菜单命令“数据|数据透视表和数据透视图”，于是“向导--3步骤之1”对话框(图2-10)便会弹出，在其中保持“MicrosoftExcel数据列表或数据库”与“数据透视表”两个单选钮被选中的缺省状态不变，单击“下一步”按钮。
图2-10 •35• 面向决策的Excel高级数据处理 接着弹出一个“向导--3步骤之2”对话框(图2-11)，其中的“选定区域”输入框中显示出制作数据透视表所使用的数据列表所在的范围(数据源区域)以供操作者修改，如果在选择菜单命令“数据|数据透视表和数据透视图”前已将所要使用的数据列表中的一个单元格选为当前单元格，那么“选定区域”输入框中显示的数据源范围就正好是所要分析的数据列表所在的范围因而无需改变，单击“下一步”按钮。
图2-11 在接着出现的“向导--3步骤之3”对话框(图2-12)中单击“布局”按钮，这时屏幕上便会显示出图2-9所示的“向导--布局”对话框，按照准备制作的、如图2-8所示的分类汇总表的布局，将“类别”与“渠道”字段名分别拖到行域与列域中，再将“净销售额”字段名拖到数据域中(变成“求和项:净销售额”，见图2-13)，单击“确定”按钮。
图2-12 在重新成为当前对话框的“向导--3步骤之3”对话框中再单击“选项”按钮，在接着弹出的“数据透视表选项”对话框中将“列总计”与“行总计”两个复选框的选中状态加以取消，单击该对话框中的“确定”按钮。
在再次成为当前对话框的“向导--3步骤之3”对话框中，保持“新建工作表”单选钮的选中状态不变(表示要求在一个新工作表中生成数据透视表)，单击“完成”按钮。
于是，在Excel的新工作表中就生成了一个结构与内容完全符合图2-8所示分类汇总表布局要求的、“净销售额”关于“类别”与“渠道”两个汇总参考字段的分类汇总表(图2-14，即图2-1中图所示的那个分类汇总表，可将该工作表改名为“数据透视表”)。
•36• 第2章对数据列表进行分类汇总分析的基本概念与方法 图2-13 图2-14 值得注意的是，这时在屏幕上会以悬浮的形式显示出一个“数据透视表”工具栏，而且在数据透视表中的任一单元格被选中时，工作表右侧还会显示出一个“数据透视表字段列表”。
可以利用单击“数据透视表”工具栏上最右侧的“隐藏(显示)字段列表”来隐藏或显示这个字段名列表，这个“字段列表”既可以“停泊”在Excel应用程序窗口的左侧或右侧(在图2-14所示状况下它就停泊在右侧)，也可以以“悬浮”的方式放在当前工作表中的任意位置(见图2-15，可以用鼠标指针拖动其标题栏的方式将它移至工作表中的任何位置)。
这两个工具可以为使用者对数据透视表做进一步操作提供便利。
•37• 面向决策的Excel高级数据处理 图2-15 数据透视表还允许使用者根据需要将位于行域或列域中的汇总参考字段的某些值(和被汇总字段的相应的汇总值)加以隐藏。
例如，如果使用者希望在图2-14所示的数据透视表中不显示服装类商品的净销售额总计值，那么就可以单击位于行域中的“类别”字段名右侧的下拉三角形，然后在下拉列表框中取消“服装”左侧复选框的选中状态(见图2-16左图)并单击“确定”按钮，这样该数据透视表就会将原来位于第二行中的“类别”字段值“服装”与它所对应的净销售额总计值隐藏起来(见图2-16右图)
1。
图2-16 1在图2-14所示的数据透视表中，右击位于行域中的“服装”字段值所在单元格，然后在接着弹出的快捷菜单中 选择“隐藏”，也可以实现相同的目的。
在需要将隐藏的汇总参考字段值(与相应的被汇总字段汇总值)重新显示 出来时，只需在相应字段名旁的下拉列表框中将该字段值左侧的复选框重新选中(或者将“全部显示”复选框选 中)即可。
•
38• 第2章对数据列表进行分类汇总分析的基本概念与方法 前面曾经提到，当我们将数值型字段的字段名拖放到数据域中时，它会变成“求和项:(字段名)”，在图2-13所示的“向导--布局”对话框中，将“净销售额”字段名拖放到数据域中后，它就变成了“求和项:净销售额”，在需要改为其他汇总方式时可以双击该字段名(或者在所制成的数据透视表中双击显示文字内容“求和项:净销售额”的单元格A3)，从而使一个“数据透视表字段”对话框弹出(图2-17左图)，其中在“汇总方式”列表框中列出了各种可选的汇总方式。
例如，如果希望改为求平均值的汇总方式，可将“平均值”选中从而使“名称”框内出现“平均值项:净销售额”，然后单击“确定”按钮，这样数据透视表就会变成图2-17右图所示的那样，列出净销售额平均值随不同的类别与渠道值变化的分类汇总表。
利用数据透视表功能制成的分类汇总表的一个突出优点就是它在布局结构方面的极大灵活性。
例如，在已经生成了如图2-14所示的、“净销售额”关于“类别”与“渠道”两个汇总参考字段的分类汇总表的条件下，如果用鼠标指针将位于B3中的列域字段名“渠道”拖出“扔掉”
1，它就会立即变成图2-1左图所示的形式。
如果用鼠标指针将位于A4中的行域字段名“类别”拖到列域中去，同时又将位于B3中的列域字段名“渠道”拖到行域中去，就可将分类汇总表的行列互相交换从而使它显示为如图2-18所示的样子(这种形式的分类汇总表向右铺开得太远，因此通常不大使用)。
图2-17 1即将它拖出数据透视表范围并在鼠标指针所带的图标上显示出一个叉时，将鼠标加以释放。
•39• 面向决策的Excel高级数据处理 图2-18 若将“类别”与“渠道”两个字段名都拖放到行域中，这样就会使分类汇总表显示为如图2-19左右两图所示的、具有双层结构的样子(它们与利用分类小计功能制成的、如图1-11左右两图所示的分类汇总表十分接近)。
当然也可以将两个汇总参考字段的字段名都拖到列域中去。
如前所说，这两种具有双层结构的分类汇总表都比较复杂而不便使用者理解其中的意义，因此我们不提倡使用(在列域中形成双层结构时，还会出现分类汇总表在横向铺开得太远的缺点，因此更不便于使用)。
如果未在“向导--布局”对话框中做任何设置就在图2-12所示的“向导-3步骤之3”对话框中单击“完成”的话，在指定工作表中就会出现一个空白的数据透视表结构框架(图2-20)，这时操作者可以通过在“数据透视表”工具栏上选择命令“数据透视表|向导”，以再次通过“向导--3步骤之3”对话框重新打开“向导--布局”对话框来补充完成对各种汇总参考字段与被汇总字段的布局设置，也可通过从工作表右侧的“数据透视表字段列表”中直接将各个有关字段名拖入数据透视表结构框架的适当区域来完成所需的布局设置。
•40• 第2章对数据列表进行分类汇总分析的基本概念与方法 图2-19 图2-20 利用数据透视表工具制成的分类汇总表还可以同时对一个被汇总字段做两种(或更多种)不同形式的汇总。
例如，针对“ABC数据”数据列表可以生成一个同时显示净销售额总计值和平均值随类别变化的分类汇总表。
为此只需在“向导--布局”对话框中将“类别”字段名拖放到行域中后，将“净销售额”字段名两次拖放到数据域中，但在它们都变成“求和项:净销售额”之后将其中一个改为“平均值项:净销售额”即可。
图2-21显示了这样制成的分类汇总表。
•41• 面向决策的Excel高级数据处理 图2-21 正如前一节所指出的，在某些情况下可以将数据列表中的两个字段同时当作被汇总字段来做分类汇总分析。
在目前的问题中，只要在“向导--布局”对话框中将“净销售额”与“毛销售额”这两个字段名逐一拖放到数据域中去，又将“类别”字段名拖到行域中去，便可在“ABC数据”数据列表的基础上制成“净销售额”与“毛销售额”这两个被汇总字段关于“类别”字段的联合分类汇总表(图2-22)。
■ 图2-22 数据透视表除了具有布局结构上的灵活性之外还有许多优点，例如，它允许使用者在需要时针对分类汇总表中的某个汇总值查看生成该汇总值的明细数据。
举例来说，如果使用者希望了解图2-14所示的数据透视表中生成单元格B11中的运动器材类商品的零售净销售额总计值(69827)的明细数据，那么只需在该单元格中选择快捷菜单命令“组及显示明细数据|显示明细数据”(或者双击该单元格)，这样，在一个新工作表中就会显示出生成该汇总值的所有原始数据记录(共有269个，见图2-23)，在将这些记录下方位于“净销售额”字段所在列中的单元格E271选中，并双击“自动求和”工具按钮，便可生成公式“=SUM(E2:E270)”以求出其上方各个单元格中的净销售额之和，所得结果正是数据透视表中显示的总计值69827。
这里 •42• 第2章对数据列表进行分类汇总分析的基本概念与方法SUM()是一个返回作为其参数的范围内所有数值之和的Excel内建函数。
有关Excel内建函数的简要说明可参见第7.2节，还可参阅参考文献[3]第4章或其他参考书籍与Excel的联机帮助资料。
图2-23 如果在行域中的“类别”字段的任一字段值(例如“艺术品”)所在单元格被选中时，打开快捷菜单并选择命令“组及显示明细数据|显示明细数据”，这时就会弹出一个“显示明细数据”对话框，其中列出了“ABC数据”数据列表所包含的除“类别”之外的其他各个字段名(图2-24左图)，从中选择一个字段名(例如“销售地”)并单击“确定”按钮后，在行域中就会增加一个第二层汇总参考字段“销售地”，而艺术品的零售与批发两个渠道净销售额总计值就会进一步分解成在不同销售地发生的净销售额总计值(还带有一个称为“艺术品汇总”的净销售额合计值)，但其他类别商品的零售与批发两个渠道净销售额总计值则未受影响(图2-24右图)。
如果在“类别”字段名所在单元格(A4)中做相同的操作，那么与行域中所有类别值对应的零售与批发两个渠道净销售额总计值就都会进一步分解成在不同销售地发生的净销售额总计值。
图2-24 •43• 面向决策的Excel高级数据处理 2.2.3应用D函数分析法对数据列表做分类汇总分析 当我们面对一个记录企业业务数据的数据列表时，看到的是一堆杂乱无章的数据，根本看不出其中可能存在着任何内在的函数依赖关系。
但是，当我们对它进行分类汇总时，某种依赖关系就会显现出来：当汇总参考字段取不同的字段值时，被汇总字段的汇总值就会随之而改变。
对这个现象加以概括便可认识到，这就是隐藏在数据列表中的一种“函数”关系：可以将汇总参考字段的字段值看成自变量而将被汇总字段的汇总值看成因变量
1，后者随前者的变化而变化。
正因为如此，在对数据列表进行分类汇总分析，就可以像对其他包含着内在依赖关系的事物进行分析一样，通过建立一个适当的模型(即数据分析模型)来实现，进而可以将在对定量性问题进行分析时行之有效的XD建模法引入到分类汇总分析中来。
在有效性被大量实例所证实的XD建模法的指引下，我们发展出了一种把分类汇总看成对被汇总字段的汇总值随汇总参考字段的字段值变化的函数关系进行描述与分析的方法。
由于在基于这种方法而创建的数据分析模型中需要使用Excel的一种内建函数—D函数(又称为“数据库函数”)来扮演关键角色，所以我们约定将它称为D函数分析法
2。
D函数是Excel内建函数的一种类型，其中共有12个函数，这些函数的功能就是对数据列表中的数据进行汇总。
常用的D函数有DSUM()、DAVERAGE()、DMAX()、DMIN()、DCOUNT()与DCOUNTA()等。
所有D函数都需要三个参数：第1参数是作为处理对象的数据列表所在范围；第2参数是该数据列表中被汇总字段的字段名或该字段在数据列表中的序号；第3参数是第1.2.4小节中说明过的一个条件范围。
这些函数的作用是在第1参数规定的数据列表中将符合第3参数所表示的筛选条件的所有记录挑选出来，再求出(返回)这些记录中由第2参数所规定的被汇总字段的字段值的某种汇总值(上面列举的六个D函数返回的汇总值分别是总计值、平均值、极大值、极小值或出现次数等3)。
另外，12个D函数中有一个特殊的DGET()，它虽然也是对数据列表进行处理的，但其功能不是进行汇总而是求出其中的一个特定值，后面在例4-26等几个例子中将说明它的应用。
D函数分析法的要点是：第
一，建立一个数据分析模型，在其主体中将数据列表中的一个(或两个)汇总参 1由于汇总参考字段的字段值很可能是文本型的，因此，这种函数关系突破了数学上的意义。
2D函数分析法作为一种数据列表分类汇总分析方法，是作者在参考文献[2]中首次提出的(在该书中将它称为“
D 函数加灵敏度分析方法”)。
本书附录中第7.1节将对XD建模法做简要的介绍。
关于XD建模法的内容及其应用的更详细的说明可参见参考文献[3]。
顺便说明，D函数作为一种电子表格软件内建函数，发端于上个世纪80年代初期，当时由于数据库的概念还不很严格，因此人们常将数据列表与数据库混为一谈，并将对数据列表进行处理的函数称为数据库函数或简称为D函数，这一名词一直沿用至今。
3对于数值型被汇总字段来说，可以使用DCOUNT()或DCOUNTA()来求出该字段之值的出现次数，对于文本型被汇总字段来说，只能使用DCOUNTA()来求出该字段之值的出现次数。
•44• 第2章对数据列表进行分类汇总分析的基本概念与方法 考字段的字段值当作自变量(如果需要考虑更多汇总参考字段的话，可将它们的字段值视为参数)，再建立一个条件范围，并将其中的各个条件行单元格以适当的方式与表示(各个)自变量值与参数值的相应单元格链接起来，最后在表示被汇总字段汇总值的函数单元格内输入一个引用了适当的D函数的公式(将准备分析的数据列表作为D函数的第1参数，将被汇总字段的字段名与所建立的条件范围作为其第2参数与第3参数)，从而建立起被汇总字段汇总值与各个汇总参考字段之值之间的点对点依赖关系； 第
二，在数据分析模型主体中建立起了函数与自变量之间的点对点依赖关系之后，作为模型的附属部分，在一个列范围内键入作为自变量的主要汇总参考字段的各个不同的字段值(必要时又在一个行范围内键入作为另一自变量的第二汇总参考字段的各个不同的字段值)，在此基础上将函数单元格相对自变量单元格做一个一维(或二维)灵敏度分析操作(关于XD建模法与灵敏度分析操作的说明可参见第7.1节)，这样便可生成所需要的分类汇总表。
在D函数分析法中生成的分类汇总表与作为其基础数据的数据列表之间保存着紧密的链接关系，在Excel自动重计算功能的支配下，在该数据列表中的数据发生变化或者在其他参数值发生变化之后，分类汇总表的内容便会立即随之而变化。
下面利用一个例子来说明利用D函数分析法来做简单分类汇总分析的方法。
【例2-2】试应用D函数分析法针对“ABC数据”数据列表生成一个具有如图2-8所示形式的、“净销售额”总计值随“类别”与“渠道”两个汇总参考字段的字段值变化的、汇总方式为求和的分类汇总表。
解在保存着“ABC数据”数据列表的工作簿文件的空白工作表中建立一个如图2-25所示的数据分析模型，在其中做以下操作： ABC
D 1
2 3 销售渠道
4 商品类别
5 净销售额总计值
6 7
8 渠道
9 零售 10 11 EFGHI 零售艺术品121736.7 类别艺术品 121737艺术品服装自行车儿童用品食品陶瓷用具运动器材
J 零售121736.71 33073.7295188.78122490.9890528.55 37933.869827
K LM 批发230676.69121916.56 97576.44149743.41109313.04 32344.1756852.12 图2-25 在E3与E4中键入汇总参考字段“渠道”与“类别”的两个任意字段值(例如“零售”与“艺术品”)，在D8与E8中键入这两个汇总参考字段的字段名，在D9与E9中输入公式“=E3”与“=E4”，从而在D8:E9中生成一个条件范围，它表示“渠道”字段值必须等于E3中之值而“类别”字段值必须等于E4中之值的，在此基础上在E5 •45• 面向决策的Excel高级数据处理 中输入一个引用了DSUM()函数的公式“=DSUM(ABC数据,"净销售额",D8:E9)”
1。
计算结果表明，在“渠道”与“类别”这两个汇总参考字段之值分别等于“零售”与“艺术品”时，“ABC数据”数据列表中“净销售额”字段的总计值等于121736.71(元)。
在I3:I9各个单元格中键入“类别”字段的七个不同的字段值，在J2与K2中键入“渠道”字段的两个不同的字段值，在范围I2:K9中将E5相对E3与E4做一个二维灵敏度分析操作，这样在此范围内就生成了所需要的分类汇总表。
使用D函数分析法制成的分类汇总表在布局上不具备数据透视表方法的那种灵活性，为了改变其布局必须将原来的分类汇总表删去重做。
例如，若要取消“渠道”的辅助汇总参考字段地位改为生成一个以“类别”为唯一汇总参考字段的分类汇总表的话，这时只能在图2-25所示的数据分析模型中将范围B3:F3删除，又将D7:D8(原来的D8:D9)中的内容删除，将单元格E4(原来的E5)中的公式改为“=DSUM(ABC数据,"净销售额",E7:E8)”，再将
J、K两列删除，然后在范围I2:J9中将E4相对E3重新做一个一维灵敏度分析操作(见图2-26)。
ABC
D 1
2 3 商品类别
4 净销售额总计值
5 6
7 8
9 10 11 EFGHI 艺术品352413.4 类别艺术品 艺术品服装自行车儿童用品食品陶瓷用具运动器材
J KL 352413.4352413.4154990.28192765.22272234.39199841.5970277.97126679.12 图2-26 如果要将所生成的分类汇总表的汇总方式改为求平均值，可在图2-26所示的模型的单元格E4中将输入的公式改为“=DAVERAGE(ABC数据,"净销售额",E7:E8)”(见图2-27)。
LMN
O 1
2 3 商品类别
4 净销售额平均值
5 6
7 8
9 10 11 PQRST 艺术品667.4496 类别艺术品 艺术品服装自行车儿童用品食品陶瓷用具运动器材
U VW 667.44962667.44962397.41097362.34064531.70779375.64209305.55639220.31151 图2-27 1因为“净销售额”字段在“ABC数据”数据列表中的序号为
5，所以这个公式也可以写为“=DSUM(ABC数据,
5,D8:E9)”，但为了使使用者更容易理解其意义，我们在大多数情况下都将被汇总字段的字段名作为D函数的第2参数。
•46• 第2章对数据列表进行分类汇总分析的基本概念与方法 可调图形的使用是XD建模法的重要内容，D函数分析法自然也会对它加以应用，这就是我们今后在许多例子中将会看到的可选式图形。
可选式图形在本例所讨论的简单的分类汇总分析问题的第一个应用可以参看后面的例3-1(参见图3-20~图3-23)与例4-1(参见图4-2)。
■ 正如本小节开始时指出的，在依据D函数分析法建立的数据分析模型中，与表示自变量值与参数值的单元格链接着的条件范围起着关键的作用。
由于D函数与高级筛选使用的表示筛选条件的范围是一样的，因此，对于字段值中一个值有可能出现是其他值的开始子字符串的那些文本型字段来说，在为它们设置条件范围时必须考虑到第1.2.4小节末尾所指出的“开始子字符串”性质，以免发生不必要的错误。
下面通过一个例子来揭示在一个简单数据列表中采用D函数分析法进行汇总分析时会出现的、与条件范围设置有关的问题以及克服该问题的方法。
【例2-3】在一个工作表的范围A1:A11中陈列着一个包含着一个名为“起重设备附件”的字段的数据列表(图2-28)，该字段共有“滑轮装配”、“小滑轮”、“提升焊接式滑轮”、“定滑轮”、“滑轮”、“滑轮片”、“电缆滑轮”、“绳轮”、“滑轮组”、“单滑轮组”等10个字段值，试应用D函数分析法建立一个数据分析模型来表明该数据列表中“起重设备附件”的各个字段值发生次数随字段值变化的分类汇总表(很明显，这些字段值的发生次数应该都等于1)。
A CDE
F 1起重设备附件 2滑轮装配 3小滑轮 起重设备附件 4提升焊接式滑轮个数 5定滑轮 6滑轮 7滑轮片 8电缆滑轮 9绳轮 10滑轮组 11单滑轮组 12 13 14 GHIJK 小滑轮
1 起重设备附件小滑轮 滑轮装配小滑轮提升焊接式定滑轮滑轮滑轮片电缆滑轮绳轮滑轮组单滑轮组 LMN 11111411111 图2-28 解在所考虑的数据列表所在的工作表中做以下操作：在E3中键入与A1中的字段名相同的标题“起重设备附件”，在G3中键入“起重设备附件”字段的一个任意字段值(例如“小滑轮”)，在G7与G8中键入字段名“起重设备附件”与输入公式“=G3”，在此基础上在G4中输入公式“=DCOUNTA(A1:A11,E3,G7:G8)”以求出G3指定的字段值在数据列表中出现的次数。
再将A2:A11 •47• 面向决策的Excel高级数据处理 拷贝到K2:K12中去，在范围K2:L12中将G4相对G3做一个灵敏度分析操作，这样在K3:L12中就生成了一个看似符合要求的分类汇总表。
所生成的分类汇总表中虽然有9个字段值的发生次数正如预期的那样均等于
1，但其中给出的“滑轮”发生次数4却出错了。
发生错误的原因是：在所考虑的数据列表中，“滑轮”不仅是一个特定的字段值，同时也是“滑轮装配”、“滑轮片”、“滑轮组”等三个字段值的开始子字符串，因而这四个记录都满足G7:G8中设置的筛选条件。
为了消除这个由于开始子字符串性质导致的错误，首先对数据列表做一个扩充：在B2中输入公式“=LEN(A2)”并将它拷贝到B2:B11中去(这里LEN()是Excel提供的计算一个文本所包含的字符数的内建函数)，以求出A2:A11中各个字段值包含的字符个数(即它们的长度)，从而将位于A1:A11中的数据列表扩充成位于A1:B11中的、包含“起重设备附件”与“字段值长度”两个字段的数据列表。
在此基础上对图2-28所示的数据分析模型做一点修改，即：在单元格F10与F11中键入字段名“起重设备附件”与输入公式“=G3”，又在单元格G10与G11中键入字段名“字段值长度”与输入公式“VLOOKUP(G3,A2:B11,2,0)”或“=LEN(G3)”，再用F10:G11中的条件范围来代替G7:G8中的条件范围，将G4中的公式改为“=DCOUNTA(A1:B11,E3,F10:G11)”，这时，在K3:L12中就生成了正确的分类汇总表，其中显示的每个字段值的出现次数都等于
1。
函数VLOOKUP()的功能是返回以作为其第1参数的查表值到作为其第2参数的纵向安排多列表格中去查表时，在由第3参数指定的列中求得的对应值，对该函数的进一步说明可参见第7.2.3小节中的表格内容。
■
A B CDE
F GHIJK 1起重设备附件字段值长度 2滑轮装配
4 3小滑轮 3起重设备附件小滑轮 滑轮装配 4提升焊接式滑轮 7个数
1 小滑轮 5定滑轮
3 1 提升焊接式 6滑轮
2 定滑轮 7滑轮片
3 起重设备附件滑轮 8电缆滑轮
4 小滑轮 滑轮片 9绳轮
2 电缆滑轮 10滑轮组
3 起重设备附件字段值长度 绳轮 11单滑轮组
4 小滑轮
3 滑轮组 12 单滑轮组 13 14 LMN 11111111111 图2-29 •48•
如果说掌握数据列表的初级处理方法有助于企业内基层业务人员提高事务处理的效率的话，那么掌握分类汇总分析这种处理方法将会为企业内中层以上管理人员做好管理决策提供有用的参考信息(当然掌握数据列表的初级处理技术对于中层管理人员也是有用的)。
由于企业业务活动的日积月累，一些重要的数据列表会包含数量巨大的明细数据，这些数据在有关业务的发生过程中曾经发挥过不可或缺的重要作用，但在失去时效之后似乎就没有直接的应用价值了。
然而，正如我们在“前言”中指出的，这些数据列表中实际上蕴藏着许多对于管理人员制订各种决策具有重要参考作用的宝贵信息，我们需要采用有效的方法从数据列表中提炼出这些重要信息。
这些重要信息不是数据列表中包含的明细数据本身，而是各种数量较少的汇总数据。
换言之，为了获取这些有用信息，必须对数据列表做适当的“由细到粗”和“由多到少”的汇总处理。
例如，从记录企业在过去某段时间内发生的销售状况的数据列表中，可以提炼出关于销售额在不同类别产品或不同销售地之间的分布，或者销售额随年月变化的趋势的汇总数据；从记录企业在过去某段时间内发生的原材料质量指标的数据列表中，可以提炼出有关原材料平均合格率在其不同供应厂家之间的分布，或者这些合格率随年月变化的趋势的汇总数据；从记录企业人事资料的数据列表中可以提炼出不同 面向决策的Excel高级数据处理 部门中职工的年龄结构、学历结构以及职称结构等等。
很显然，这些表示某个关键变量分布结构或发展趋势的汇总数据对于企业制定有关决策具有重要的参考价值。
本书所要介绍的多维分类汇总分析就是从数据列表保存的成千上万的记录中提炼出这些宝贵信息的有效手段。
本章只介绍对数据列表进行分类汇总分析的基本概念与方法，第4章将进一步阐述分类汇总分析在概念与方法上的拓展。
还要指出一个重要事实，那就是：在适当的查询技术的帮助下，我们可以将数据库中某一个或几个表中的有关数据导入到Excel中以生成一个数据列表，因此，掌握了从数据列表中提炼出有用信息的方法之后，也就可以使用这些方法从数据库中提炼出有用信息。
第5章将说明对数据库中的数据进行查询并在此基础上对其中的数据做多维分类汇总分析的方法。
2.1被汇总字段、汇总参考字段以及分类汇总表 我们将数据列表中那些作为汇总对象的字段称为被汇总字段。
原则上说，一个数据列表中的每个字段都可以在特定的问题中被当作被汇总字段，但其中那些描述业务量的数值型字段(例如，产品产量、销售收益、原材料质量指标或产品合格率等)常常是管理人员关注的中心，因而也就经常成为被汇总字段的首选字段。
在一个数据列表中求出所有记录的被汇总字段的一个单一的汇总值(例如，单一总计值、单一总平均值或单一总数等)没有任何意义。
人们只有在比较中才能认识事物的性质，因此，只有参考着数据列表中的另一个特定字段的各个不同字段值对被汇总字段求出相应的汇总值(也就是对于那个特定参考字段的每个字段值，将那个字段等于该字段值的全部记录挑选出来，并求出它们的被汇总字段之值的总和或平均值或个数)，从而构成一个多行两列的对照表：表中左列列出该特定参考字段的不同字段值，右列则列出对应的被汇总字段汇总值(总计值、平均值或发生次数等)，这个对照表便会给出被汇总字段随该参考字段的不同值变化的结构性或趋势性的特征，因而成为具有决策参考价值的信息。
从数据列表中获取被汇总字段汇总值随另一特定参考字段的不同字段值变化的对照表的处理过程称为分类汇总分析。
在对被汇总字段进行分类汇总分析时，以其各个字段值作为参考或分类依据的那个字段称为汇总参考字段。
被汇总字段与汇总参考字段是在对数据列表进行分类汇总分析时所涉及的两个基本概念
1。
总之，在对一个数据列表进行分类汇总分析时，必须根据需要将其中的一个字段选为被汇总字段，并将另一个(或几个)字段选为汇总参考字段。
原则上说，数据 1在第1.2.2小节说明“分类小计”功能时已经提到过这两个名词。
•26• 第2章对数据列表进行分类汇总分析的基本概念与方法 列表中每个字段都可以被选为汇总参考字段，甚至连已被选为被汇总字段的字段也可以同时被选为汇总参考字段。
决策者要根据面临的决策问题的焦点或侧重点从数据列表的各个字段中选出所需使用的汇总参考字段，而且在一般情况下，可能还需要选定不止一个汇总参考字段。
随着问题焦点的转移，可能要将不同的字段选为汇总参考字段(有时可能还需要将不同的字段选为被汇总字段)并进行新的分类汇总分析。
我们把在数据列表中参考一个汇总参考字段对某个被汇总字段进行的分类汇总分析称为对于数据列表的简单分类汇总分析，在少数情况下，如果数据列表中存在着一个只有两三个字段值的字段，也可以将这个字段作为第二汇总参考字段包括到简单分类汇总分析中来(并将基本的汇总参考字段称为主要汇总参考字段)。
本章将讨论对数据列表进行简单分类汇总分析的方法。
在简单分类汇总分析中生成的、显示被汇总字段的汇总值随汇总参考字段的不同字段值变化的对照表称为被汇总字段关于特定汇总参考字段的(简单)分类汇总表。
如前所说，一个简单分类汇总表可以用Excel工作表中的一个多行两列的范围来表示。
在使用了主要与第二两个汇总参考字段的情况下，分类汇总表扩展为一个包含三至四列的矩形范围，其中左侧一列列出主要汇总参考字段的各个字段值，顶端一行列出第二汇总参考字段的各个(两个或三个)字段值，位于各个行列交叉点的单元格中则列出与左侧与顶端的两个汇总参考字段字段值对应的被汇总字段汇总值。
对于“ABC数据”数据列表来说，“净销售额”(或“毛销售额”)是被汇总字段的首选字段，“类别”、“渠道”、“销售地”、“日期”等字段则都可以用作汇总参考字段。
图2-1左图显示了“净销售额”总计值随“类别”字段的不同字段值变化的分类汇总表，该图中图显示了“净销售额”总计值随作为主要汇总参考字段的“类别”与作为第二汇总参考字段的“渠道”的不同字段值变化的分类汇总表，该图右图则显示了“净销售额”的总计值随“日期”的不同值变化的分类汇总表的顶端部分内容。
图2-
1 尽管描述企业业务量的数值型字段(例如“ABC数据”数据列表中的“净销售•27• 面向决策的Excel高级数据处理 额”或“毛销售额”)经常被当作被汇总字段，但有时也可以反过来将这种字段当作汇总参考字段，而将“日期”或其他各种文本型字段看成被汇总字段(此时的汇总方式只能是计数)，从而生成业务发生次数(例如销售次数)随业务量(例如销售额)数值变化的分类汇总表。
当然，考虑到在一个数据列表出现两个或更多个具有相同业务量的记录的概率很小的事实，这样制成的分类汇总表所包含的(与不同业务量对应的)行数很大的而大部分行中的业务发生次数都等于
1，所以它们没有多大的决策参考价值。
但是如果把业务量的各种取值组合成一系列具有适当宽度的区间(分组)
1，从而生成表示业务发生次数随业务量区间(分组)的变化状况的分类汇总表，那么这种分类汇总表就会具有较高的决策参考价值，图2-2左图显示了针对“ABC数据”数据列表制成的销售次数随销售额分组变化的分类汇总表。
图2-
2 如前所说，必要时也可以将一个业务量字段既看成汇总参考字段又看成被汇总字段，将这种做法与业务量字段值的分组结合起来，就会生成业务量的总计值或平均值随业务量分组变化的分类汇总表。
图2-2中图与右图就给出了显示“ABC数据”数据列表中净销售额平均值与总计值随净销售额取值区间变化状况的分类汇总表。
关于图2-2所示的几种分类汇总表的制作方法的说明可以参见例4-18。
在使用一个汇总参考字段的条件下，(在添上两个适当的字段名之后)简单分类汇总表本身也呈现为一个具有两个字段的数据列表的形式，其中第一字段是原数据列表中被当作汇总参考字段的那个字段，其各个字段值就是原数据列表中的字段的各个不同值；第二字段是原数据列表中被当作被汇总字段的那个字段，它的字段值就是该字段的各个分类汇总值。
这个数据列表的记录个数等于原数据列表中作为汇总参考字段的特定字段的不同字段值的个数。
在使用了主要与第二两个汇总参考字段的情况下，分类汇总表不再具有数据列表的性质。
但是通过改变结构可以将它转变成一个带有三个字段的数据列表(当然也要添上三个适当的字段名)，其中前两个字段是原数据列表中作为两个汇总参考字段 1有关这种组合的概念与操作方法可参见第4.4.1小节。
•28• 第2章对数据列表进行分类汇总分析的基本概念与方法 的字段，它们在新数据列表各个记录中的值是原数据列表中，两个汇总参考字段的不同值的各种可能组合；而第三个字段是原数据列表中作为被汇总字段的那个字段，其字段值为原数据列表被汇总字段的、与两个汇总参考字段的各个字段值组合对应的分类汇总值。
这个数据列表的记录数等于原数据列表中两个汇总参考字段不同值个数的乘积。
在图2-3所示工作表的范围C2:E9中重新显示了“ABC数据”数据列表中“净销售额”总计值随作为主要与第二汇总参考字段的“类别”与“渠道”的不同字段值变化的分类汇总表(但它与图2-1中图所示的分类汇总表金额单位不同)。
工作表范围H2:J16中显示了由该分类汇总表转变而成的数据列表。
图2-
3 可以利用图形(柱形图、折线图或饼图)来显示简单分类汇总表中的信息，从而直观地显示出该分类汇总表中给出的、被汇总字段的汇总值随汇总参考字段的各个字段值变化的状况。
在包含两个汇总参考字段(而第二汇总参考字段只有两三个字段值)的情况下，可以用一个图形(柱形图或折线图)中的两组或三组不同颜色的柱形(两条或三条折线)来显示在第二汇总参考字段取不同字段值时，被汇总字段汇总值随主要汇总参考字段的不同值变化的状况
1。
例如，图2-3所示的分类汇总表2中的数据可以用图2-4~图2-6等插图中显示的柱形图、折线图、条形图与饼图(包括平面饼图与立体饼图)来表现。
饼图中饼角旁的注释既可以是相应数据点的Y数据(即各个饼角表示的汇总值的大小，见图2-6)，也可以是相应数据点的X数据与Y数据的混合(见图2-7左上图)，在第一种情况下， 1如果将字段值多于两三个的字段当作第二汇总参考字段的话，表示分类汇总信息的图形就会因为显示太多柱形组(或太多的折线)而显得混乱，另外，与柱形图和折线图不同，饼图只能显示一组饼角(因此下面图2-6所示饼图的标题与图2-
4、图2-5所示的其他类型图形的标题有一些区别)。
2为了使图形中显示的坐标轴刻度值比较短，所以图2-3所示的分类汇总表使用了以万元为单位的金额数据。
•29• 面向决策的Excel高级数据处理 各个数据点的X数据用图例来说明(值得指出，各种类型图形的图例都是对各个数据系列的说明，唯独饼图的图例是对各个数据点的说明)。
不同类别商品净销售额比较 25 批发零售20 不同类别商品净销售额比较 25 零售批发20 15 15 净销售额总计值(万元) 净销售额总计值(万元) 10 10
5 5
0 艺术品服装自行车儿童用品食品陶瓷用具运动器材
0 艺术品服装自行车儿童用品食品陶瓷用具运动器材 图2-
4 净销售额总计值(万元) 不同类别商品净销售额(万元)比较 25 批发 零售 20 运动器材陶瓷用具 不同类别商品净销售额(万元)比较 批发零售 食品 15 儿童用品 10 自行车 5
0 艺术品服装自行车儿童用品食品陶瓷用具运动器材 服装艺术品
0 图2-
5 销售
5 10 15 20 25额 不同类别商品零售净销售额（万元）比较 6.98 3.799.05 12.25 12.173.31 9.52 艺术品服装自行车儿童用品食品陶瓷用具运动器材 不同类别商品零售净销售额（万元）比较 6.983.799.05 12.25 12.17艺术品服装自行车儿童用品 3.31食品陶瓷用具运动器材 9.52 图2-
6 •30• 第2章对数据列表进行分类汇总分析的基本概念与方法 对于饼图来说还有一种变体，即在各个饼角旁注明各个数据点的Y数据在作为它们之和的总值中所占的百分比(见图2-7除左上图之外的其余三个图)，另外，在饼角的显示方式上还可以使一个饼角“向外伸出”或者使所有饼角都“向外伸出”(见图2-7左下图与右下图)。
有必要说明一点，虽然在绝大部分情况下，在分类汇总分析中只使用一个被汇总字段。
然而，在某些存在着两个意义相近的字段的数据列表中，不排除在必要时可以将这两个字段同时当作被汇总字段，对它们的总计值随其他字段的字段值变化的状况进行联合的分类汇总分析。
“ABC数据”数据列表中的“净销售额”与“毛销售额”就是两个意义相近的字段，可以想见，将它们相对“类别”等字段做联合分类汇总分析有时是有意义的。
1 不同类别商品零售净销售额（万元）比较 运动器材,6.98陶瓷用具,3.79 艺术品,12.17 食品,9.05 服装,3.31 不同类别商品零售净销售额比较运动器材 陶瓷用具12%7% 艺术品21% 食品16% 服装6% 儿童用品,12.25 自行车,9.52 儿童用品21% 自行车17% 不同类别商品零售净销售额比较运动器材 陶瓷用具12%7% 艺术品21% 食品16% 服装6% 儿童用品21% 自行车17% 不同类别商品零售净销售额比较 运动器材陶瓷用具12% 7% 艺术品21% 食品16% 服装6% 儿童用品21% 自行车17% 图2-
7 很显然，用手工方法对包含数千个记录的数据列表来制作分类汇总表是一个十分繁重的任务。
但在Excel中针对各种包含大量记录的数据列表来制作被汇总字段关于一个(或两个)汇总参考字段的分类汇总表(并且制作出相应的图形)却毫无困难。
许多情况下可以(或者需要)将数据列表中的若干个字段同时用作汇总参考字段(将其中一个作为主要汇总参考字段，而将其余各个作为辅助汇总参考字段)。
区别 1下面第4.7节将说明将数据列表中的两个被汇总字段相对一个或几个汇总参考字段做联合分类汇总分析的问题。
•31• 面向决策的Excel高级数据处理 于简单分类汇总分析，我们将一个数据列表中被汇总字段关于多个汇总参考字段的分类汇总分析称为多维分类汇总分析，第4章将对多维分类汇总分析进行深入的讨论。
2.2对数据列表做分类汇总分析的工具 2.2.1应用分类小计功能对数据列表做分类汇总分析 前面讨论过的分类小计是Excel提供的一种与分类汇总分析有关的功能，图1-11显示的就是在将“净销售额”字段作为被汇总字段并将“类别”与“渠道”作为两个汇总参考字段的情况下，利用Excel的分类小计功能对“ABC数据”数据列表做分类汇总分析后获得的两种结果。
分类小计作为一种分类汇总分析的工具有两个特点：第
一，用这种工具处理后的数据列表具有一种大纲结构，因此既可以只显示汇总数据也可以将明细数据与汇总数据同时显示出来；第
二，它保持着数据列表原有的字段结构，并将被汇总字段的分类汇总值插入到汇总参考字段等于相同字段值的一组记录下方、与该字段对应的单元格中
1。
上述第一个特点使分类小计功能在生成与某些企业经营管理业务有关的报表方面有一定的用处，(按类别或销售地统计的销售金额或销售数量表，按部门层次统计的职工工资表或者按零部件类别层次统计的零部件库存数量表等是这种报表的例子)，但是对于向中高层管理人员提供凝炼汇总信息来说，这一特点并无特别重要的价值。
相反的，正是由于有上述两个特点，一方面无法将具有决策参考价值的汇总数据从明细数据中“剥离”出来，另一方面当同时考虑的汇总参考字段增多时大纲结构的层数随之而增加从而使表的结构变得更加复杂，因此分类小计功能不是分类汇总分析的方便工具，特别不适于多维的分类汇总分析。
1必要时也可以将它设置为置于一组相应的记录的上方。
•32• 第2章对数据列表进行分类汇总分析的基本概念与方法 AB
C 1 净销售额
2 总计值
3 艺术品
4 服装
5 自行车
6 儿童用品
7 食品
8 陶瓷用具
9 运动器材 10 11
D E F
G 零售121736.71 33073.7295188.78122490.9890528.55 37933.869827 批发230676.69121916.56 97576.44149743.41109313.04 32344.1756852.12 图2-
8 除了分类小计功能之外，在Excel中可以使用数据透视表和D函数分析法这两种更合适的工具来对数据列表做分类汇总分析(图2-1与图2-2中显示的各个分类汇总表就是利用数据透视表工具制成的)，下面两节将通过在“ABC数据”数据列表的基础上生成图2-8所示形式的(“净销售额”关于“类别”与“渠道”这两个汇总参考字段的)分类汇总表的过程来说明这两个工具的基本使用方法
1。
2.2.2应用数据透视表对数据列表做分类汇总分析 数据透视表(pivottable)是Excel提供的理想的多维分类汇总分析工具。
本小节将只介绍利用数据透视表来做简单分类汇总分析的方法，第4章将会进一步说明利用数据透视表做多维分类汇总分析的各种方法。
在一个数据列表的基础上利用数据透视表工具来生成分类汇总表的手段是选择菜单命令“数据|数据透视表和数据透视图”，然后在以“数据透视表和数据透视图向导”为名的一系列对话框(以下简称“向导”对话框)的指引下，针对数据透视表的数据源(即所要分析的数据)在何处(是来自Excel数据列表还是来自外部数据源，若是来自数据列表则要确定该数据列表所在的范围，若是来自外部数据源则要确定具体的数据源并选中所需要的各个字段2)、它应具有的布局，以及它的显示位置(是放在当前工作表中还是放在一个新建工作表中)完成一系列设置，最终将它制成。
上述各项设置中最核心的是在“向导--布局”对话框中完成的、对分类汇总表布局的设置，该对话框利用一个示意图画出了数据透视表的结构框架(图2-9)，其中包含着位于主体部分的三个区域：中间区域是供放置被汇总字段的“数据域”， 1这个分类汇总表与图2-3所示的分类汇总表是等效的，二者的区别只是金额单位不同而已。
另外，分类小计功能的局限性使它无法直接生成这种形式的分类汇总表。
2关于在外部数据源的基础上制作数据透视表的问题可参见第5.6.2节。
•33• 面向决策的Excel高级数据处理 左侧是供放置一个(或多个)汇总参考字段的“行域”，顶部是供放置一个(或多个)汇总参考字段的“列域”
1。
操作者可以按照自己希望建立的分类汇总表的布局形式，从对话框左侧的字段名列表中将作为被汇总字段与汇总参考字段的字段名分别拖放到数据域与行域(或列域)中。
当一个数值型字段的字段名被拖放到数据域中时，它立即就会变成“求和项:(字段名)”，表示汇总方式为求和，即求总计值，但操作者在需要时可以方便地将它改成计数(即求发生次数)、求平均值、求极小值或求极大值等其他的汇总方式；当一个文本型字段的字段名被拖放到数据域中时，由于其唯一可实施的汇总方式为计数，因此这个字段名会立即变成“计数项:(字段名)”。
图2-
9 我们将拖放到行域与列域中的汇总参考字段分别称为行域字段与列域字段。
在只使用一个汇总参考字段时，通常应将其字段名拖放到行域中；在使用两个汇总参考字段时，应将主要汇总参考字段的字段名拖放到行域中，而将第二汇总参考字段的字段名拖放到列域中。
这样生成的分类汇总表具有图2-1左、中两图所示的布局外观：在只使用行域字段时它由两列组成，左侧一列列出行域字段的各个不同的字段值，右侧一列则列出对应的被汇总字段汇总值；在同时使用行域字段与列域字段时，除了在左侧一列中列出行域字段的各个不同字段值之外，在顶端一行中还列出列域字段的各个不同字段值，数据域各个单元格中列出对应的被汇总字段汇总值。
数据透视表形式的分类汇总表可以根据操作者的设置在垂直方向的最下方添加或者不添加一个总计行，即所谓“列总计”行，其中列出与行域字段全部字段值(和列域字段的各个字段值)对应的被汇总字段汇总值，在汇总方式为求和或计数时，这行中(各个单元格中)的汇总值等于其上方各个汇总值之和。
在有列域汇总参考字段 1在数据透视表主体之外，在其左上方还有一个供放置其他辅助汇总参考字段的“页域”，第4.1节将说明页域的用途。
•34• 第2章对数据列表进行分类汇总分析的基本概念与方法 时，还可以根据操作者的设置在水平方向的最右侧添加或者不添加一个总计列，即所谓“行总计”列，其中列出与列域字段的全部字段值和行域字段的各个字段值对应的被汇总字段汇总值，在汇总方式为求和或计数时，这个汇总值等于其左侧各个汇总值之和。
可以将两个或更多个汇总参考字段设置为行域字段(或列域字段)，但这时所生成的分类汇总表就会在垂直(或水平)方向以复杂的层次结构来列出相应汇总参考字段的字段值与被汇总字段的汇总值。
这种具有多层结构的分类汇总表通常会显得繁琐而使人不易理解其中的信息(而且不易于用适当的图形来显示这些信息)。
因此，较好的做法是只在行域中放置一个汇总参考字段，在另一个字段只具有两三个字段值的条件下，可以将它作为第二汇总参考字段而放置在列域中(在列域中，更不适合放置两个以上的字段)。
数据透视表与作为其基础数据(数据源)的数据列表之间保存着一种联系，在该数据列表中的数据发生变化之后，数据透视表虽然不会自动发生相应的改变，但是只要在其中任一单元格被选中的条件下选择一次菜单命令“数据|刷新数据”(或者选择一次快捷菜单命令“刷新数据”)，数据透视表中的数据就会发生相应的改变。
下面来说明使用数据透视表生成具有图2-8所示形式的分类汇总表的方法。
【例2-1】试应用数据透视表功能对于“ABC数据”数据列表生成一个具有图2-8所示形式的、“净销售额”总计值随“类别”与“渠道”两个汇总参考字段的字段值变化的分类汇总表。
解在将“ABC数据”数据列表所在范围中任一单元格选中的条件下，选择Excel的菜单命令“数据|数据透视表和数据透视图”，于是“向导--3步骤之1”对话框(图2-10)便会弹出，在其中保持“MicrosoftExcel数据列表或数据库”与“数据透视表”两个单选钮被选中的缺省状态不变，单击“下一步”按钮。
图2-10 •35• 面向决策的Excel高级数据处理 接着弹出一个“向导--3步骤之2”对话框(图2-11)，其中的“选定区域”输入框中显示出制作数据透视表所使用的数据列表所在的范围(数据源区域)以供操作者修改，如果在选择菜单命令“数据|数据透视表和数据透视图”前已将所要使用的数据列表中的一个单元格选为当前单元格，那么“选定区域”输入框中显示的数据源范围就正好是所要分析的数据列表所在的范围因而无需改变，单击“下一步”按钮。
图2-11 在接着出现的“向导--3步骤之3”对话框(图2-12)中单击“布局”按钮，这时屏幕上便会显示出图2-9所示的“向导--布局”对话框，按照准备制作的、如图2-8所示的分类汇总表的布局，将“类别”与“渠道”字段名分别拖到行域与列域中，再将“净销售额”字段名拖到数据域中(变成“求和项:净销售额”，见图2-13)，单击“确定”按钮。
图2-12 在重新成为当前对话框的“向导--3步骤之3”对话框中再单击“选项”按钮，在接着弹出的“数据透视表选项”对话框中将“列总计”与“行总计”两个复选框的选中状态加以取消，单击该对话框中的“确定”按钮。
在再次成为当前对话框的“向导--3步骤之3”对话框中，保持“新建工作表”单选钮的选中状态不变(表示要求在一个新工作表中生成数据透视表)，单击“完成”按钮。
于是，在Excel的新工作表中就生成了一个结构与内容完全符合图2-8所示分类汇总表布局要求的、“净销售额”关于“类别”与“渠道”两个汇总参考字段的分类汇总表(图2-14，即图2-1中图所示的那个分类汇总表，可将该工作表改名为“数据透视表”)。
•36• 第2章对数据列表进行分类汇总分析的基本概念与方法 图2-13 图2-14 值得注意的是，这时在屏幕上会以悬浮的形式显示出一个“数据透视表”工具栏，而且在数据透视表中的任一单元格被选中时，工作表右侧还会显示出一个“数据透视表字段列表”。
可以利用单击“数据透视表”工具栏上最右侧的“隐藏(显示)字段列表”来隐藏或显示这个字段名列表，这个“字段列表”既可以“停泊”在Excel应用程序窗口的左侧或右侧(在图2-14所示状况下它就停泊在右侧)，也可以以“悬浮”的方式放在当前工作表中的任意位置(见图2-15，可以用鼠标指针拖动其标题栏的方式将它移至工作表中的任何位置)。
这两个工具可以为使用者对数据透视表做进一步操作提供便利。
•37• 面向决策的Excel高级数据处理 图2-15 数据透视表还允许使用者根据需要将位于行域或列域中的汇总参考字段的某些值(和被汇总字段的相应的汇总值)加以隐藏。
例如，如果使用者希望在图2-14所示的数据透视表中不显示服装类商品的净销售额总计值，那么就可以单击位于行域中的“类别”字段名右侧的下拉三角形，然后在下拉列表框中取消“服装”左侧复选框的选中状态(见图2-16左图)并单击“确定”按钮，这样该数据透视表就会将原来位于第二行中的“类别”字段值“服装”与它所对应的净销售额总计值隐藏起来(见图2-16右图)
1。
图2-16 1在图2-14所示的数据透视表中，右击位于行域中的“服装”字段值所在单元格，然后在接着弹出的快捷菜单中 选择“隐藏”，也可以实现相同的目的。
在需要将隐藏的汇总参考字段值(与相应的被汇总字段汇总值)重新显示 出来时，只需在相应字段名旁的下拉列表框中将该字段值左侧的复选框重新选中(或者将“全部显示”复选框选 中)即可。
•
38• 第2章对数据列表进行分类汇总分析的基本概念与方法 前面曾经提到，当我们将数值型字段的字段名拖放到数据域中时，它会变成“求和项:(字段名)”，在图2-13所示的“向导--布局”对话框中，将“净销售额”字段名拖放到数据域中后，它就变成了“求和项:净销售额”，在需要改为其他汇总方式时可以双击该字段名(或者在所制成的数据透视表中双击显示文字内容“求和项:净销售额”的单元格A3)，从而使一个“数据透视表字段”对话框弹出(图2-17左图)，其中在“汇总方式”列表框中列出了各种可选的汇总方式。
例如，如果希望改为求平均值的汇总方式，可将“平均值”选中从而使“名称”框内出现“平均值项:净销售额”，然后单击“确定”按钮，这样数据透视表就会变成图2-17右图所示的那样，列出净销售额平均值随不同的类别与渠道值变化的分类汇总表。
利用数据透视表功能制成的分类汇总表的一个突出优点就是它在布局结构方面的极大灵活性。
例如，在已经生成了如图2-14所示的、“净销售额”关于“类别”与“渠道”两个汇总参考字段的分类汇总表的条件下，如果用鼠标指针将位于B3中的列域字段名“渠道”拖出“扔掉”
1，它就会立即变成图2-1左图所示的形式。
如果用鼠标指针将位于A4中的行域字段名“类别”拖到列域中去，同时又将位于B3中的列域字段名“渠道”拖到行域中去，就可将分类汇总表的行列互相交换从而使它显示为如图2-18所示的样子(这种形式的分类汇总表向右铺开得太远，因此通常不大使用)。
图2-17 1即将它拖出数据透视表范围并在鼠标指针所带的图标上显示出一个叉时，将鼠标加以释放。
•39• 面向决策的Excel高级数据处理 图2-18 若将“类别”与“渠道”两个字段名都拖放到行域中，这样就会使分类汇总表显示为如图2-19左右两图所示的、具有双层结构的样子(它们与利用分类小计功能制成的、如图1-11左右两图所示的分类汇总表十分接近)。
当然也可以将两个汇总参考字段的字段名都拖到列域中去。
如前所说，这两种具有双层结构的分类汇总表都比较复杂而不便使用者理解其中的意义，因此我们不提倡使用(在列域中形成双层结构时，还会出现分类汇总表在横向铺开得太远的缺点，因此更不便于使用)。
如果未在“向导--布局”对话框中做任何设置就在图2-12所示的“向导-3步骤之3”对话框中单击“完成”的话，在指定工作表中就会出现一个空白的数据透视表结构框架(图2-20)，这时操作者可以通过在“数据透视表”工具栏上选择命令“数据透视表|向导”，以再次通过“向导--3步骤之3”对话框重新打开“向导--布局”对话框来补充完成对各种汇总参考字段与被汇总字段的布局设置，也可通过从工作表右侧的“数据透视表字段列表”中直接将各个有关字段名拖入数据透视表结构框架的适当区域来完成所需的布局设置。
•40• 第2章对数据列表进行分类汇总分析的基本概念与方法 图2-19 图2-20 利用数据透视表工具制成的分类汇总表还可以同时对一个被汇总字段做两种(或更多种)不同形式的汇总。
例如，针对“ABC数据”数据列表可以生成一个同时显示净销售额总计值和平均值随类别变化的分类汇总表。
为此只需在“向导--布局”对话框中将“类别”字段名拖放到行域中后，将“净销售额”字段名两次拖放到数据域中，但在它们都变成“求和项:净销售额”之后将其中一个改为“平均值项:净销售额”即可。
图2-21显示了这样制成的分类汇总表。
•41• 面向决策的Excel高级数据处理 图2-21 正如前一节所指出的，在某些情况下可以将数据列表中的两个字段同时当作被汇总字段来做分类汇总分析。
在目前的问题中，只要在“向导--布局”对话框中将“净销售额”与“毛销售额”这两个字段名逐一拖放到数据域中去，又将“类别”字段名拖到行域中去，便可在“ABC数据”数据列表的基础上制成“净销售额”与“毛销售额”这两个被汇总字段关于“类别”字段的联合分类汇总表(图2-22)。
■ 图2-22 数据透视表除了具有布局结构上的灵活性之外还有许多优点，例如，它允许使用者在需要时针对分类汇总表中的某个汇总值查看生成该汇总值的明细数据。
举例来说，如果使用者希望了解图2-14所示的数据透视表中生成单元格B11中的运动器材类商品的零售净销售额总计值(69827)的明细数据，那么只需在该单元格中选择快捷菜单命令“组及显示明细数据|显示明细数据”(或者双击该单元格)，这样，在一个新工作表中就会显示出生成该汇总值的所有原始数据记录(共有269个，见图2-23)，在将这些记录下方位于“净销售额”字段所在列中的单元格E271选中，并双击“自动求和”工具按钮，便可生成公式“=SUM(E2:E270)”以求出其上方各个单元格中的净销售额之和，所得结果正是数据透视表中显示的总计值69827。
这里 •42• 第2章对数据列表进行分类汇总分析的基本概念与方法SUM()是一个返回作为其参数的范围内所有数值之和的Excel内建函数。
有关Excel内建函数的简要说明可参见第7.2节，还可参阅参考文献[3]第4章或其他参考书籍与Excel的联机帮助资料。
图2-23 如果在行域中的“类别”字段的任一字段值(例如“艺术品”)所在单元格被选中时，打开快捷菜单并选择命令“组及显示明细数据|显示明细数据”，这时就会弹出一个“显示明细数据”对话框，其中列出了“ABC数据”数据列表所包含的除“类别”之外的其他各个字段名(图2-24左图)，从中选择一个字段名(例如“销售地”)并单击“确定”按钮后，在行域中就会增加一个第二层汇总参考字段“销售地”，而艺术品的零售与批发两个渠道净销售额总计值就会进一步分解成在不同销售地发生的净销售额总计值(还带有一个称为“艺术品汇总”的净销售额合计值)，但其他类别商品的零售与批发两个渠道净销售额总计值则未受影响(图2-24右图)。
如果在“类别”字段名所在单元格(A4)中做相同的操作，那么与行域中所有类别值对应的零售与批发两个渠道净销售额总计值就都会进一步分解成在不同销售地发生的净销售额总计值。
图2-24 •43• 面向决策的Excel高级数据处理 2.2.3应用D函数分析法对数据列表做分类汇总分析 当我们面对一个记录企业业务数据的数据列表时，看到的是一堆杂乱无章的数据，根本看不出其中可能存在着任何内在的函数依赖关系。
但是，当我们对它进行分类汇总时，某种依赖关系就会显现出来：当汇总参考字段取不同的字段值时，被汇总字段的汇总值就会随之而改变。
对这个现象加以概括便可认识到，这就是隐藏在数据列表中的一种“函数”关系：可以将汇总参考字段的字段值看成自变量而将被汇总字段的汇总值看成因变量
1，后者随前者的变化而变化。
正因为如此，在对数据列表进行分类汇总分析，就可以像对其他包含着内在依赖关系的事物进行分析一样，通过建立一个适当的模型(即数据分析模型)来实现，进而可以将在对定量性问题进行分析时行之有效的XD建模法引入到分类汇总分析中来。
在有效性被大量实例所证实的XD建模法的指引下，我们发展出了一种把分类汇总看成对被汇总字段的汇总值随汇总参考字段的字段值变化的函数关系进行描述与分析的方法。
由于在基于这种方法而创建的数据分析模型中需要使用Excel的一种内建函数—D函数(又称为“数据库函数”)来扮演关键角色，所以我们约定将它称为D函数分析法
2。
D函数是Excel内建函数的一种类型，其中共有12个函数，这些函数的功能就是对数据列表中的数据进行汇总。
常用的D函数有DSUM()、DAVERAGE()、DMAX()、DMIN()、DCOUNT()与DCOUNTA()等。
所有D函数都需要三个参数：第1参数是作为处理对象的数据列表所在范围；第2参数是该数据列表中被汇总字段的字段名或该字段在数据列表中的序号；第3参数是第1.2.4小节中说明过的一个条件范围。
这些函数的作用是在第1参数规定的数据列表中将符合第3参数所表示的筛选条件的所有记录挑选出来，再求出(返回)这些记录中由第2参数所规定的被汇总字段的字段值的某种汇总值(上面列举的六个D函数返回的汇总值分别是总计值、平均值、极大值、极小值或出现次数等3)。
另外，12个D函数中有一个特殊的DGET()，它虽然也是对数据列表进行处理的，但其功能不是进行汇总而是求出其中的一个特定值，后面在例4-26等几个例子中将说明它的应用。
D函数分析法的要点是：第
一，建立一个数据分析模型，在其主体中将数据列表中的一个(或两个)汇总参 1由于汇总参考字段的字段值很可能是文本型的，因此，这种函数关系突破了数学上的意义。
2D函数分析法作为一种数据列表分类汇总分析方法，是作者在参考文献[2]中首次提出的(在该书中将它称为“
D 函数加灵敏度分析方法”)。
本书附录中第7.1节将对XD建模法做简要的介绍。
关于XD建模法的内容及其应用的更详细的说明可参见参考文献[3]。
顺便说明，D函数作为一种电子表格软件内建函数，发端于上个世纪80年代初期，当时由于数据库的概念还不很严格，因此人们常将数据列表与数据库混为一谈，并将对数据列表进行处理的函数称为数据库函数或简称为D函数，这一名词一直沿用至今。
3对于数值型被汇总字段来说，可以使用DCOUNT()或DCOUNTA()来求出该字段之值的出现次数，对于文本型被汇总字段来说，只能使用DCOUNTA()来求出该字段之值的出现次数。
•44• 第2章对数据列表进行分类汇总分析的基本概念与方法 考字段的字段值当作自变量(如果需要考虑更多汇总参考字段的话，可将它们的字段值视为参数)，再建立一个条件范围，并将其中的各个条件行单元格以适当的方式与表示(各个)自变量值与参数值的相应单元格链接起来，最后在表示被汇总字段汇总值的函数单元格内输入一个引用了适当的D函数的公式(将准备分析的数据列表作为D函数的第1参数，将被汇总字段的字段名与所建立的条件范围作为其第2参数与第3参数)，从而建立起被汇总字段汇总值与各个汇总参考字段之值之间的点对点依赖关系； 第
二，在数据分析模型主体中建立起了函数与自变量之间的点对点依赖关系之后，作为模型的附属部分，在一个列范围内键入作为自变量的主要汇总参考字段的各个不同的字段值(必要时又在一个行范围内键入作为另一自变量的第二汇总参考字段的各个不同的字段值)，在此基础上将函数单元格相对自变量单元格做一个一维(或二维)灵敏度分析操作(关于XD建模法与灵敏度分析操作的说明可参见第7.1节)，这样便可生成所需要的分类汇总表。
在D函数分析法中生成的分类汇总表与作为其基础数据的数据列表之间保存着紧密的链接关系，在Excel自动重计算功能的支配下，在该数据列表中的数据发生变化或者在其他参数值发生变化之后，分类汇总表的内容便会立即随之而变化。
下面利用一个例子来说明利用D函数分析法来做简单分类汇总分析的方法。
【例2-2】试应用D函数分析法针对“ABC数据”数据列表生成一个具有如图2-8所示形式的、“净销售额”总计值随“类别”与“渠道”两个汇总参考字段的字段值变化的、汇总方式为求和的分类汇总表。
解在保存着“ABC数据”数据列表的工作簿文件的空白工作表中建立一个如图2-25所示的数据分析模型，在其中做以下操作： ABC
D 1
2 3 销售渠道
4 商品类别
5 净销售额总计值
6 7
8 渠道
9 零售 10 11 EFGHI 零售艺术品121736.7 类别艺术品 121737艺术品服装自行车儿童用品食品陶瓷用具运动器材
J 零售121736.71 33073.7295188.78122490.9890528.55 37933.869827
K LM 批发230676.69121916.56 97576.44149743.41109313.04 32344.1756852.12 图2-25 在E3与E4中键入汇总参考字段“渠道”与“类别”的两个任意字段值(例如“零售”与“艺术品”)，在D8与E8中键入这两个汇总参考字段的字段名，在D9与E9中输入公式“=E3”与“=E4”，从而在D8:E9中生成一个条件范围，它表示“渠道”字段值必须等于E3中之值而“类别”字段值必须等于E4中之值的，在此基础上在E5 •45• 面向决策的Excel高级数据处理 中输入一个引用了DSUM()函数的公式“=DSUM(ABC数据,"净销售额",D8:E9)”
1。
计算结果表明，在“渠道”与“类别”这两个汇总参考字段之值分别等于“零售”与“艺术品”时，“ABC数据”数据列表中“净销售额”字段的总计值等于121736.71(元)。
在I3:I9各个单元格中键入“类别”字段的七个不同的字段值，在J2与K2中键入“渠道”字段的两个不同的字段值，在范围I2:K9中将E5相对E3与E4做一个二维灵敏度分析操作，这样在此范围内就生成了所需要的分类汇总表。
使用D函数分析法制成的分类汇总表在布局上不具备数据透视表方法的那种灵活性，为了改变其布局必须将原来的分类汇总表删去重做。
例如，若要取消“渠道”的辅助汇总参考字段地位改为生成一个以“类别”为唯一汇总参考字段的分类汇总表的话，这时只能在图2-25所示的数据分析模型中将范围B3:F3删除，又将D7:D8(原来的D8:D9)中的内容删除，将单元格E4(原来的E5)中的公式改为“=DSUM(ABC数据,"净销售额",E7:E8)”，再将
J、K两列删除，然后在范围I2:J9中将E4相对E3重新做一个一维灵敏度分析操作(见图2-26)。
ABC
D 1
2 3 商品类别
4 净销售额总计值
5 6
7 8
9 10 11 EFGHI 艺术品352413.4 类别艺术品 艺术品服装自行车儿童用品食品陶瓷用具运动器材
J KL 352413.4352413.4154990.28192765.22272234.39199841.5970277.97126679.12 图2-26 如果要将所生成的分类汇总表的汇总方式改为求平均值，可在图2-26所示的模型的单元格E4中将输入的公式改为“=DAVERAGE(ABC数据,"净销售额",E7:E8)”(见图2-27)。
LMN
O 1
2 3 商品类别
4 净销售额平均值
5 6
7 8
9 10 11 PQRST 艺术品667.4496 类别艺术品 艺术品服装自行车儿童用品食品陶瓷用具运动器材
U VW 667.44962667.44962397.41097362.34064531.70779375.64209305.55639220.31151 图2-27 1因为“净销售额”字段在“ABC数据”数据列表中的序号为
5，所以这个公式也可以写为“=DSUM(ABC数据,
5,D8:E9)”，但为了使使用者更容易理解其意义，我们在大多数情况下都将被汇总字段的字段名作为D函数的第2参数。
•46• 第2章对数据列表进行分类汇总分析的基本概念与方法 可调图形的使用是XD建模法的重要内容，D函数分析法自然也会对它加以应用，这就是我们今后在许多例子中将会看到的可选式图形。
可选式图形在本例所讨论的简单的分类汇总分析问题的第一个应用可以参看后面的例3-1(参见图3-20~图3-23)与例4-1(参见图4-2)。
■ 正如本小节开始时指出的，在依据D函数分析法建立的数据分析模型中，与表示自变量值与参数值的单元格链接着的条件范围起着关键的作用。
由于D函数与高级筛选使用的表示筛选条件的范围是一样的，因此，对于字段值中一个值有可能出现是其他值的开始子字符串的那些文本型字段来说，在为它们设置条件范围时必须考虑到第1.2.4小节末尾所指出的“开始子字符串”性质，以免发生不必要的错误。
下面通过一个例子来揭示在一个简单数据列表中采用D函数分析法进行汇总分析时会出现的、与条件范围设置有关的问题以及克服该问题的方法。
【例2-3】在一个工作表的范围A1:A11中陈列着一个包含着一个名为“起重设备附件”的字段的数据列表(图2-28)，该字段共有“滑轮装配”、“小滑轮”、“提升焊接式滑轮”、“定滑轮”、“滑轮”、“滑轮片”、“电缆滑轮”、“绳轮”、“滑轮组”、“单滑轮组”等10个字段值，试应用D函数分析法建立一个数据分析模型来表明该数据列表中“起重设备附件”的各个字段值发生次数随字段值变化的分类汇总表(很明显，这些字段值的发生次数应该都等于1)。
A CDE
F 1起重设备附件 2滑轮装配 3小滑轮 起重设备附件 4提升焊接式滑轮个数 5定滑轮 6滑轮 7滑轮片 8电缆滑轮 9绳轮 10滑轮组 11单滑轮组 12 13 14 GHIJK 小滑轮
1 起重设备附件小滑轮 滑轮装配小滑轮提升焊接式定滑轮滑轮滑轮片电缆滑轮绳轮滑轮组单滑轮组 LMN 11111411111 图2-28 解在所考虑的数据列表所在的工作表中做以下操作：在E3中键入与A1中的字段名相同的标题“起重设备附件”，在G3中键入“起重设备附件”字段的一个任意字段值(例如“小滑轮”)，在G7与G8中键入字段名“起重设备附件”与输入公式“=G3”，在此基础上在G4中输入公式“=DCOUNTA(A1:A11,E3,G7:G8)”以求出G3指定的字段值在数据列表中出现的次数。
再将A2:A11 •47• 面向决策的Excel高级数据处理 拷贝到K2:K12中去，在范围K2:L12中将G4相对G3做一个灵敏度分析操作，这样在K3:L12中就生成了一个看似符合要求的分类汇总表。
所生成的分类汇总表中虽然有9个字段值的发生次数正如预期的那样均等于
1，但其中给出的“滑轮”发生次数4却出错了。
发生错误的原因是：在所考虑的数据列表中，“滑轮”不仅是一个特定的字段值，同时也是“滑轮装配”、“滑轮片”、“滑轮组”等三个字段值的开始子字符串，因而这四个记录都满足G7:G8中设置的筛选条件。
为了消除这个由于开始子字符串性质导致的错误，首先对数据列表做一个扩充：在B2中输入公式“=LEN(A2)”并将它拷贝到B2:B11中去(这里LEN()是Excel提供的计算一个文本所包含的字符数的内建函数)，以求出A2:A11中各个字段值包含的字符个数(即它们的长度)，从而将位于A1:A11中的数据列表扩充成位于A1:B11中的、包含“起重设备附件”与“字段值长度”两个字段的数据列表。
在此基础上对图2-28所示的数据分析模型做一点修改，即：在单元格F10与F11中键入字段名“起重设备附件”与输入公式“=G3”，又在单元格G10与G11中键入字段名“字段值长度”与输入公式“VLOOKUP(G3,A2:B11,2,0)”或“=LEN(G3)”，再用F10:G11中的条件范围来代替G7:G8中的条件范围，将G4中的公式改为“=DCOUNTA(A1:B11,E3,F10:G11)”，这时，在K3:L12中就生成了正确的分类汇总表，其中显示的每个字段值的出现次数都等于
1。
函数VLOOKUP()的功能是返回以作为其第1参数的查表值到作为其第2参数的纵向安排多列表格中去查表时，在由第3参数指定的列中求得的对应值，对该函数的进一步说明可参见第7.2.3小节中的表格内容。
■
A B CDE
F GHIJK 1起重设备附件字段值长度 2滑轮装配
4 3小滑轮 3起重设备附件小滑轮 滑轮装配 4提升焊接式滑轮 7个数
1 小滑轮 5定滑轮
3 1 提升焊接式 6滑轮
2 定滑轮 7滑轮片
3 起重设备附件滑轮 8电缆滑轮
4 小滑轮 滑轮片 9绳轮
2 电缆滑轮 10滑轮组
3 起重设备附件字段值长度 绳轮 11单滑轮组
4 小滑轮
3 滑轮组 12 单滑轮组 13 14 LMN 11111111111 图2-29 •48•
声明：
该资讯来自于互联网网友发布，如有侵犯您的权益请联系我们。
