数据透视表的编辑

本页目录

创建或获取数据透视表以后，可以对数据透视表中的元素进行编辑，如添加或修改字段、设置表中数据的数字格式、设置表中单元格区域的格式等。[大谦Excel，dqexcel点com]

添加字段

对于已有数据透视表，可以添加字段。添加页字段、列字段和行字段使用PivotTable对象的AddFields方法，添加值字段使用PivotTable对象的AddDataField方法。

【Excel VBA】

示例文件的存放路径为Samples\ch19\Excel VBA\添加字段.xlsm。CreatePVT过程创建一个简单的数据透视表，将“产品”设置为列字段，将“产地”设置为行字段，如下面代码中所示。

code.vba 
Sub CreatePVT()
  '前面代码省略
  '…

  '设置字段
  With pvt
    .PivotFields("产品").Orientation = xlColumnField  '列字段
    .PivotFields("产品").Position = 1
    .PivotFields("产地").Orientation = xlRowField  '行字段
    .PivotFields("产地").Position = 1
  End With
End Sub

运行过程，生成数据透视表如图7-4所示。

Document Image

图7-4 创建简单的数据透视表

AddField过程向刚刚创建的数据透视表中添加字段，用PivotTable对象的AddFields方法将“类别”添加为页字段，用AddDataField方法将“金额”添加为值字段。

code.vba 
Sub AddFields()
  '省略，获取数据透视表pvt
  '…
  With pvt
    .AddFields PageFields:=”类别”, AddToTable:=True   ‘页字段
    .AddDataField .PivotFields(“金额”), “求和项:金额”, xlSum  ‘值字段
  End With
End Sub

运行过程，生成图7-2所示的数据透视表。

【Python】

编写Python脚本，脚本文件的存放路径为Samples\ch19\Python\添加字段.py。

code.vba 
#前面代码省略，请参见Python文件
#......
先创建一个简单的数据透视表，将“产品”设置为列字段，将“产地”设置为行字段。
#创建透视表
pvt=sht_pvt.api.PivotTableWizard(\
    SourceType=xw.constants.PivotTableSourceType.xlDatabase,\
    SourceData=rng_data)
pvt.Name='透视表'
#设置字段
pvt.PivotFields('产品').Orientation=\
    xw.constants.PivotFieldOrientation.xlColumnField  #列字段
pvt.PivotFields('产品').Position=1
pvt.PivotFields('产地').Orientation=\
    xw.constants.PivotFieldOrientation.xlRowField  #行字段
pvt.PivotFields('产地').Position=1

用PivotTable对象的AddFields方法将“类别”添加为页字段，用AddDataField方法将“金额”添加为值字段。

code.vba 
#添加页字段
pvt.AddFields(PageFields='类别',AddToTable=True)  #页字段
#添加值字段
pvt.AddDataField(pvt.PivotFields('金额'),'求和项：金额',\
    xw.constants.ConsolidationFunction.xlSum)  #值字段

运行脚本，生成图7-2所示的数据透视表。

修改字段

可以修改已有数据透视表中字段对象的属性。例如创建数据透视表时将“金额”指定为值字段，则默认时会生成名为“求和项:金额”的活动字段，如图7-2所示工作表中A3单元格所示。现用PivotField对象的Name属性修改该字段名称为“ 金额 ”。注意，“金额”是已有字段名称，所以新名称在”金额”前后添加了一个空格。

【Excel VBA】

示例文件的存放路径为Samples\ch19\Excel VBA\修改字段.xlsm。CreatePVT过程创建数据透视表，RenameField过程修改字段名称。CreatePVT过程的代码省略，先运行该过程生成数据透视表。

Document Image

图7-5 修改数据透视表中的字段名称

RenameField过程的代码如下所示。用PivotField对象的Name属性修改字段“求和项:金额”的名称为“ 金额 ”。

code.vba 
Sub RenameField()
  '省略，获取数据透视表pvt
  '…
  pvt.PivotFields("求和项:金额").Name = " 金额 "  '修改字段名称
End Sub

运行过程，生成数据透视表如图7-5所示。注意单元格A3中的字段名称已经改变。

【Python】

编写Python脚本，脚本文件的存放路径为Samples\ch19\Python\修改字段.py。脚本首先创建数据透视表，将“金额”作为值字段，自动生成活动字段“求和项:金额”，用PivotField对象的Name属性将该名称修改为“ 金额 ”。

code.vba 
#前面代码省略，请参见Python文件
#......
#设置字段
…
pvt.PivotFields(‘金额’).Orientation=\
    xw.constants.PivotFieldOrientation.xlDataField  #值字段
#修改字段名称
pvt.PivotFields(‘求和项：金额’).Name=’ 金额 ‘

运行脚本，完成字段名称的修改。

设置字段的数字格式

使用PivotField对象的NumberFormat属性可以修改指定字段的数字格式。例如，下面创建数据透视表，将其中活动字段“求和项:金额”的数字格式设置为保留两位小数。

【Excel VBA】

示例文件的存放路径为Samples\ch19\Excel VBA\数字格式.xlsm。先运行CreatePVT过程生成数据透视表。

Document Image

图7-6 设置字段的数字格式

NumberFormat过程设置活动字段“求和项:金额”的数字格式为保留两位小数。

code.vba 
Sub NumberFormat()
  '省略，获取数据透视表pvt
  '…
  pvt.PivotFields("求和项:金额").NumberFormat = "0.00"  '保留2位小数
End Sub

运行过程，生成数据透视表如图7-6所示。

【Python】

编写Python脚本，脚本文件的存放路径为Samples\ch19\Python\数字格式.py。

code.vba 
#前面代码省略，请参见Python文件
#......
#设置数字格式
pvt.PivotFields('求和项:金额').NumberFormat='0.00'

运行过程，生成数据透视表如图7-6所示。

数据透视表的单元格区域属性设置

使用PivotTable对象的DataBodyRange属性可以设置数据区单元格区域的属性，包括单元格区域背景色、字体属性等；使用PivotField对象的DataRange属性可以设置字段对应单元格区域的属性。

下面创建数据透视表，将数据区设置为灰色，字体设置为"Times New Roman"，将“产品”字段单元格区域设置为绿色，将“产地”字段单元格区域设置为黄色。

【Excel VBA】

示例文件的存放路径为Samples\ch19\Excel VBA\单元格区域属性.xlsm。先运行CreatePVT过程生成数据透视表。

RangeFormat过程设置数据区背景色为灰色，设置数据区字体为"Times New Roman"。

code.vba 
Sub RangeFormat()
  '省略，获取数据透视表pvt
  '…
  '设置数据区背景色为灰色
  pvt.DataBodyRange.Interior.Color = RGB(200, 200, 200)
  '设置数据区字体为"Times New Roman"
  pvt.DataBodyRange.Font.Name = "Times New Roman"
End Sub

RangeFormat2过程设置“产品”字段单元格区域背景色为绿色，设置“产地”字段单元格区域背景色为黄色。

code.vba 
Sub RangeFormat2()
  '省略，获取数据透视表pvt
  '…
  '设置“产品”字段单元格区域背景色为绿色
  pvt.PivotFields("产品").DataRange.Interior.Color = RGB(0, 255, 0)
  '设置“产地”字段单元格区域背景色为黄色
  pvt.PivotFields("产地").DataRange.Interior.Color = RGB(255, 255, 0)
End Sub

运行上面两个过程，生成数据透视表如图7-7所示。

Document Image

图7-7 设置数据透视表单元格区域的属性

【Python】

编写Python脚本，脚本文件的存放路径为Samples\ch19\Python\单元格区域属性.py。

code.python 
#前面代码省略，请参见Python文件
#......
#设置数据区单元格区域的属性
pvt.DataBodyRange.Interior.Color = xw.utils.rgb_to_int((200,200,200))
pvt.DataBodyRange.Font.Name = "Times New Roman"
#设置“产品”字段单元格区域背景色为绿色
pvt.PivotFields("产品").DataRange.Interior.Color=\
                      xw.utils.rgb_to_int((0,255,0))
#设置“产地”字段单元格区域背景色为黄色
pvt.PivotFields("产地").DataRange.Interior.Color=\
                      xw.utils.rgb_to_int((255,255,0))

运行脚本，生成数据透视表如图7-7所示。[大谦Excel，dqexcel点com]