当数据列表中的某些字段存在空白单元格或文本型数值时,如果将该字段布局到数据透视表的值区域中,默认的汇总方式便为“计数”。如果需要将字段的汇总方式更改为“求和”,通常需要对每个字段逐一进行设置,非常烦琐,此时可以借助其他方法来快速实现这样的更改。
示例:更改数据透视表默认的字段汇总方式
图10-9所示的数据列表中包含许多空白单元格,并且M列中的数值是以文本方式保存的(单元格左上角有绿色三角形标志)。如果以此数据列表为数据源创建数据透视表,并且需要数据透视表值区域中字段的汇总方式默认为“求和”而非“计数”,具体操作步骤如下。
图10-9存在空白单元格或文本型数值的数据列表
步骤1在图10-9所示的数据列表区域中第一行的空白单元格F2、J2中输入数值“0”。
步骤2选中M列整列单元格区域,在【数据】选项卡中单击【分列】按钮,弹出【文本分列向导–第1步,共3步】对话框,如图10-10所示。
图10-10选择分列命令
步骤3单击【下一步】按钮,在【文本分列向导–第2步,共3步】对话框中单击【下一步】按钮,在【文本分列向导–第3步,共3步】对话框中的【列数据格式】下选中【常规】单选按钮,单击【完成】按钮,如图10-11所示。
图10-11改变数据列表的列数据格式
现在,数据列表中的第2行数据中不再包含空白单元格和文本型数值。
步骤4选中A1:M2单元格区域,创建一张空白数据透视表,如图10-12所示。
图10-12以A1:M2单元格区域创建数据透视表
步骤5在【数据透视表字段】窗格的【选择要添加到报表的字段】下选中所有字段的复选框,添加字段后的数据透视表如图10-13所示。
图10-13向数据透视表中添加字段
步骤6选中数据透视表中的任意一个单元格(如B4),在【数据透视表工具-分析】选项卡中单击【更改数据源】下拉按钮,选择【更改数据源】命令,弹出【更改数据透视表数据源】对话框,如图10-14所示。
图10-14准备重新选中数据透视表的数据源区域
步骤7重新选中完整的A1:M50数据源区域,单击【确定】按钮完成设置,如图10-15所示。
图10-15更改默认字段汇总方式的数据透视表
除此以外,也可以使用VBA代码自动生成默认的字段汇总方式为“求和”的数据透视表。
示例:借助VBA来更改数据透视表默认字段的汇总方式
步骤1重复示例10.2中的步骤2和步骤3的操作,利用“分列”功能将M列的数据格式由文本变为常规,结果如图10-16所示。
图10-16改变数据源的数据类型
步骤2在当前工作表中的空白区域插入一个矩形,编辑文字为“生成数据透视表”并设定矩形的形状样式,如图10-17所示。
图10-17在数据源表中插入矩形
步骤3在矩形上右击,在弹出的快捷菜单中单击【指定宏】命令,弹出【指定宏】对话框,如图10-18所示。
图10-18调出【指定宏】对话框
步骤4单击【新建】按钮,如图10-19所示,在弹出的VBE代码窗口中插入以下VBA代码。
DimwsAsWorksheet
DimptcacheAsPivotCache
DimptAsPivotTable
DimprangeAsRange
Setws=Sheet1
ForEachptInSheet2.PivotTables
pt.TableRange2.Clear
Nextpt
Setptcache=ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,SourceData:=Sheet1.Range('a1').CurrentRegion.Address)
Setpt=ptcache.CreatePivotTable(tabledestination:=Sheet2.Range('a3'),tablename:='透视表1')
pt.ManualUpdate=True
pt.AddFieldsRowFields:='项目',ColumnFields:='Data'
ForEachprangeInws.Range(ws.Cells(1,2),ws.Cells(1,16384).End(xlToLeft))
Withpt.PivotFields(prange.Value)
.Orientation=xlDataField
.Name=' '&prange
.Function=xlSum
EndWith
Nextprange
pt.ManualUpdate=False
图10-19插入VBA代码
步骤5按【Alt+F11】组合键切换到工作簿窗口,将当前工作表另存为“Excel启用宏的工作簿”。此时,单击矩形即可自动生成一张所有数据字段值汇总方式均为“求和项”的数据透视表,如图10-20所示。
图10-20自动生成的数据透视表
注意:用户在使用VBA代码的过程中要注意代码中指定生成数据透视表的系统表名称“Sheet2”一定要与【工程资源管理器】窗口中存放数据透视表的工作表“Sheet2(数据透视表)”中的代码名称“Sheet2”保持一致,如图10-21所示,否则代码运行过程中会出现错误。
图10-21注意工作表代码名称的代码对应
联系客服