16.6　更改数据透视表中默认的字段汇总方式

在创建数据透视表时，Excel可以根据数据源字段的类型和数据特征来决定数据透视表值字段的汇总方式。但是Excel的这种智能判断并不完美，有些时候这种默认的字段汇总方式并不是用户希望得到的结果。

示例16.8　使用Excel默认的字段汇总方式创建数据透视表

打开示例文件“使用Excel默认的字段汇总方式创建数据透视表.xlsm”，在“数据源”工作表中有如图16-20所示的统计数据，除6月份产量外，其他月份数据中都有空白单元格。

图16-20　包含空白单元格的数据源

运行CreatPvtNoFunction过程创建的数据透视表如图16-21所示。由于数据源中存在空白单元格，Excel创建数据透视表时，对于该部分数据（1月份至5月份）采用“计数”方式进行汇总，只有6月份数据采用“求和”方式进行汇总。

图16-21　使用Excel默认的字段汇总方式

代码解析：

第6行代码到第8行代码使用For…Each循环结构，删除“数据透视表”工作表中已经存在的全部数据透视表。

第9行代码创建一个新的PivotCache对象。

第10行代码创建一个数据透视表。

第12行代码添加行字段“项目”。

第13行代码到第15行代码添加值字段。

第17行代码调整值字段的Orientation属性，使值字段显示在列字段区域。

第18行代码修改值字段标题为“产量”。

示例16.9　修改数据透视表的字段汇总方式

如果数据透视表中值字段非常多，手工调整字段的汇总方式将花费大量时间，使用代码可以很容易地调整相关字段的汇总方式。

运行ModifyFieldFunction过程，数据透视表中值字段的所有“计数”汇总方式都将被更改为“求和”汇总方式，其效果如图16-22所示。

图16-22　修改值字段的汇总方式

代码解析：

第5行代码关闭屏幕更新可加快代码的执行速度。

第7行代码设置数据透视表为手动更新方式，避免在修改透视表设置的过程中，因系统自动更新数据透视表而产生冲突。

第8行代码到第10行代码使用For…Each循环结构遍历数据透视表中的值字段，并修改其Function属性为xlSum。

Function属性用于设置或者返回数据透视表值字段汇总时所使用的函数，其取值可以是XICon solidationFunction常量之一，如表16-3所示。

表16-3　　XIConsolidationFunction常量

第8行代码中使用DataFields集合遍历数据透视表中的值字段对象。在对象模型中除了PivotFileds集合外，还有几个常用的PivotField对象集合，如表16-4所示。正确选择使用对象集合可以提高代码的运行效率。

表16-4　　常用PivotField对象集合

第9行代码修改值字段的汇总方式为“求和”。

第13行代码和第14行代码释放对象变量所占用的系统资源。

第15行代码恢复系统屏幕更新功能。