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

当数据列表中的某些字段存在空白单元格或文本型数值时，如果将该字段布局到数据透视表的数值区域中，默认的汇总方式便为“计数”。如果需要将字段的汇总方式更改为“求和”，通常需要对每个字段逐一进行设置，非常烦琐，此时可以借助其他方法来快速实现这样的更改。

示例8.2　更改数据透视表默认的字段汇总方式

图8-9所示的数据列表中包含许多空白单元格，并且M列中的数值是以文本方式保存的（单元格左上角有绿色三角标志），如果以此数据列表为数据源创建数据透视表，并且需要数据透视表数值区域中字段的汇总方式默认为“求和”而非“计数”，请参照以下步骤。

步　骤1img01

在图8-9所示的数据列表区域中第一行的空白单元格F2、J2中输入数值0。

img149b

图8-9　存在空白单元格或文本型数值的数据列表

步　骤2img01

单击M列列标，选中M列整列，在【数据】选项卡中单击【分列】按钮，弹出【文本分列向导-第1步，共3步】对话框，如图8-10所示。

img150a

图8-10　选择分列命令

步　骤3img01

单击【下一步】按钮，在【文本分列向导-第2步，共3步】对话框中单击【下一步】按钮，在【在文本分列向导-第3步，共3步】对话框中的【列数据格式】中选中【常规】单选钮，单击【完成】按钮，如图8-11所示。

img150b

图8-11　改变数据列表的列数据格式

现在，数据列表中的第2行数据中不再包含空白单元格和文本型数值。

步　骤4img01

选定单元格区域A1：M2，创建一张空白数据透视表，如图8-12所示。

img151a

图8-12　以数据列表A1：M2区域创建数据透视表

步　骤5img01

勾选【数据透视表字段列表】对话框内【选择要添加到报表的字段】中的所有字段的复选框，添加字段后的数据透视表如图8-13所示。

img151b

图8-13　向数据透视表中添加字段

步　骤6img01

单击数据透视表中的任意单元格（如B4），在【数据透视表工具】项下的【选项】选项卡中单击【更改数据源】下拉按钮，在弹出的下拉菜单中选择【更改数据源】命令，弹出【更改数据透视表数据源】对话框，如图8-14所示。

步　骤7img01

使用鼠标拖动重新选定完整的数据源区域A1：M50，单击【确定】按钮完成设置，如图8-15所示。

img152a

图8-14　准备重新选定数据透视表的数据源区域

img152b

图8-15　更改默认字段汇总方式的数据透视表

除此以外，也可以使用VBA代码自动生成默认的字段汇总方式为“求和”的数据透视表。

示例8.3　借助VBA来更改数据透视表默认字段的汇总方式

步　骤1img01

重复示例7.2中的步骤2和步骤3，利用“分列”功能将M列的数据格式由文本变为常规，结果如图8-16所示。

步　骤2img01

在当前工作表中的空白区域插入一个矩形，编辑文字为“生成数据透视表”并设定矩形的形状样式，如图8-17所示。

img153a

图8-16　改变数据源的数据类型

img153b

图8-17　在数据源表中插入矩形

步　骤3img01

在矩形上单击鼠标右键，在弹出的快捷菜单中单击【指定宏】命令，弹出【指定宏】对话框，如图8-18所示。

img153c

图8-18　在数据源表中插入矩形

步　骤4img01

单击【新建】按钮，在弹出的VBE代码窗口中插入以下VBA代码：

Dim ws As Worksheet

Dim ptcache As PivotCache

Dim pt As PivotTable

Dim prange As Range

Set ws = Sheet1

For Each pt In Sheet2.PivotTables

pt.TableRange2.Clear

Next pt

Set ptcache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=Sheet1.Range(“a1”).CurrentRegion.Address)

Set pt = ptcache.CreatePivotTable(tabledestination:=Sheet2.Range(“a3”), tablename:=“透视表1”)

pt.ManualUpdate = True

pt.AddFields RowFields:=“项目”，ColumnFields:=“Data”

For Each prange In ws.Range(ws.Cells(1, 2), ws.Cells(1, 16384).End(xlToLeft))

With pt.PivotFields(prange.Value)

.Orientation = xlDataField

.Name = “ ” & prange

.Function = xlSum

End With

Next prange

pt.ManualUpdate = False

如图8-19所示。

img154a

图8-19　插入VBA代码

步　骤5img01

按<Alt+F11>组合键切换到工作簿窗口，将当前工作表另存为“Excel启用宏的工作簿”。此时，单击矩形即可自动生成一张所有数据字段值汇总方式均为“求和项”的数据透视表，如图8-20所示。

img154b

图8-20　自动生成的数据透视表

注意img01

用户在VBA代码的使用过程中要注意代码中指定生成数据透视表的系统表名称“Sheet2”一定要与【工程资源管理器】窗口中存放数据透视表的工作表“Sheet2（数据透视表）”中的代码名称“Sheet2”保持一致，如图8-21所示，否则代码运行过程中会出现错误。

img155

图8-21　注意工作表代码名称的代码对应