数据透视表控制专题

zw890728

# 一、创建数据透视表

通常会先创建一个数据缓存（`PivotCache`），然后再创建透视表（`PivotTable`）。

## 步骤1：创建透视缓存

```js
Workbook.PivotCaches.Create(SourceType, SourceData, Version)
```

**参数**

| 名称           | 必需/可选 | 数据类型                   | 说明                                                                 |
| ------------ | ----- | ---------------------- | ------------------------------------------------------------------ |
| _SourceType_ | 必选    | XlPivotTableSourceType | _SourceType_ 可以是以下常量之一： <br>xlConsolidation、xlDatabase或xlExternal。 |
| _SourceData_ | 可选    | any                    | 新数据透视表缓存的数据。                                                       |
| _Version_    | 可选    | any                    | 数据透视表的版本。                                                          |

_SourceType_ 的取值如下：

| 名称              | 值     | Description             |
| --------------- | ----- | ----------------------- |
| xlConsolidation | 3     | 多重合并计算数据区域。             |
| xlDatabase      | 1     | Microsoft Excel 列表或数据库。 |
| xlExternal      | 2     | 其他应用程序中的数据。             |

_SourceData_ 的取值如下：

如果 _SourceType_ 不为 xlExternal，则 _SourceData_ 参数为必需。
当 _SourceType_ 为 xlConsolidation 或 xlDatabase 时，该参数可以为 _Range_ 对象。
当 _SourceType_ 为 xlExternal 时，该参数为 ET 工作簿连接对象。

_Version_ 的取值如下：

| 名称                         | 值   | Description |
| -------------------------- | --- | ----------- |
| xlPivotTableVersion2000    | 0   | Excel 2000  |
| xlPivotTableVersion10      | 1   | Excel 2002  |
| xlPivotTableVersion11      | 2   | Excel 2003  |
| xlPivotTableVersion12      | 3   | Excel 2007  |
| xlPivotTableVersion14      | 4   | Excel 2010  |
| xlPivotTableVersion15      | 5   | Excel 2013  |
| xlPivotTableVersionCurrent | -1  | 仅为向后兼容性而提供  |

## 步骤2（方法1）：创建透视表

```js
PivotCache.CreatePivotTable(TableDestination, TableName, ReadData, DefaultVersion)
```

**参数**

| 名称                 | 必需/可选 | 数据类型  | 说明                                                                                           |
| ------------------ | ----- | ----- | -------------------------------------------------------------------------------------------- |
| _TableDestination_ | 必选    | Range | 数据透视表目标区域左上角的单元格 (工作表上生成的数据透视表将放置在) 的区域。目标区域必须位于**PivotCache** 对象所在工作簿的某个工作表中。               |
| _TableName_        | 可选    | any   | 新的数据透视表的名称。                                                                                  |
| _ReadData_         | 可选    | any   | 如果为 **True** ，则创建包含外部数据库中所有记录的数据透视表缓存;此缓存可能非常大。 如果为 **False**，则允许在实际读取数据之前将某些字段设置为基于服务器的页字段。 |
| _DefaultVersion_   | 可选    | any   | 数据透视表的默认版本。                                                                                  |

**示例**

```js
let pvtCache = ActiveWorkbook.PivotCaches().Create(xlDatabase, ActiveSheet.Range("A1:D9"));
pvtCache.CreatePivotTable(ActiveSheet.Range("A11"), "pivot1");
```

## 步骤2（方法2）：创建透视表

```js
PivotTables.Add(PivotCache, TableDestination, TableName, ReadData, DefaultVersion)
```

**参数**

| **名称**             | **必选/可选** | **数据类型**   | **说明**                                                                                       |
| ------------------ | --------- | ---------- | -------------------------------------------------------------------------------------------- |
| _PivotCache_       | 必选        | PivotCache | 表示一个数据透视表缓存，而新的数据透视表将基于此缓存。缓存用于为报表提供数据。                                                      |
| _TableDestination_ | 必选        | any        | 数据透视表目标区域左上角的单元格 (工作表上生成的数据透视表将放置在) 的区域。目标区域必须位于**PivotCache** 对象所在工作簿的某个工作表中。               |
| _TableName_        | 可选        | any        | 新的数据透视表的名称。                                                                                  |
| _ReadData_         | 可选        | any        | 如果为 **True**，则创建数据透视表缓存以包含外部数据库中的所有记录；此时缓存可能会很大。如果为 **False**，则允许在实际读取数据之前将某些字段设置为基于服务器的页字段。 |
| _DefaultVersion_   | 可选        | any        | 以前创建数据透视表所使用的 ET 版本。                                                                         |

**示例**

```js
let pvtCache = ActiveWorkbook.PivotCaches().Create(xlDatabase, ActiveSheet.Range("A1:D9"));
ActiveSheet.PivotTables().Add(pvtCache, ActiveSheet.Range("A13"), "pivot1")
```

# 二、增加透视字段

## AddFields（方法）：增加行列字段

向数据透视表或数据透视图中添加行字段、列字段和页字段（筛选项）。

```js
PivotTable.AddFields(RowFields, ColumnFields, PageFields, AddToTable)
```

**参数**

| 名称         | 必需/可选 | 数据类型 | 说明                                                                                       |
| -------------- | --------- | -------- | -------------------------------------------------------------------------------------------- |
| _RowFields_    | 可选        | any      | 指定要添加为行或添加到类别轴的字段名称(或字段名称数组)                                                                 |
| _ColumnFields_ | 可选        | any      | 指定要添加为列或添加到序列轴的字段名称(或字段名称数组)                                                                 |
| _PageFields_   | 可选        | any      | 指定要添加为页或添加到页面区域的字段名称(或字段名称数组)                                                                |
| _AddToTable_   | 可选        | any      | 仅适用于数据透视表。 如果为 **True**，则将指定的字段添加到报表中（不替换现有字段）。 如果为 **False**，则用新的字段替换现有的字段。 默认值为 **False**。 |

## AddDataField（方法）：增加数据字段

将数据字段添加到数据透视表中。返回一个PivotField对象，该对象表示新的数据字段

```js
PivotTable.AddDataField(Field, Caption, Function)
```

**参数**

| 名称         | 必选/可选 | 数据类型  | 说明                                                                                                                                                                                         |
| ---------- | --------- | --------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| _Field_    | 必选        | IDispatch | 服务器上的唯一字段。<br>如果源数据是联机分析处理 (OLAP，为查询和报表（而不是处理事务）而进行了优化的数据库技术。OLAP 数据是按分级结构组织的，它存储在多维数据集而不是表中。），则唯一字段是多维数据集字段。<br>如果源数据不是 OLAP（数据透视表或数据透视图使用的基本数据，包括关系数据库、 ET 工作表中的清单以及文本文件数据库。），则唯一字段是数据透视表字段。 |
| _Caption_  | 可选        | any       | 数据透视表中使用的标签，用于识别该数据字段。                                                                                                                                                                         |
| _Function_ | 可选        | any       | 在已添加数据字段中执行的函数。                                                                                                                                                                                |

**Function 的选项**

| _Function_  |  方法   |
| :---------: | :---: |
|  xlAverage  |  均值   |
|   xlCount   |  计数   |
| xlCountNums |  计数   |
|    xlMax    |  最大值  |
|    xlMin    |  最小值  |
|  xlProduct  |  乘积   |
|   xlStDev   | 样本标准差 |
|  xlStDevP   | 总体标准差 |
|    xlSum    |  求和   |
|  xlUnknown  |  未指定  |
|    xlVar    | 样本方差  |
|   xlVarP    | 总体方差  |

## 示例

```js
let pvtCache = ActiveWorkbook.PivotCaches().Create(xlDatabase, ActiveSheet.Range("A1:D9"));
let pvtTable = ActiveSheet.PivotTables().Add(pvtCache, ActiveSheet.Range("g1"), "pivot1");
pvtTable.AddFields("合同方");
pvtTable.AddDataField(pvtTable.PivotFields("合同号"), "合同号", xlCount);
pvtTable.AddDataField(pvtTable.PivotFields("合同总额"), "合同总额", xlSum);
pvtTable.AddDataField(pvtTable.PivotFields("已付款"), "已付款", xlSum);
```


# 三、PivotTable 对象补充

PivotTable代表工作表上的数据透视表。

PivotTable对象是PivotTables集合的成员。PivotTables集合包含某一张工作表上的所有PivotTable对象。由于数据透视表编程可能很复杂，因此通常最简单的方法是记录数据透视表操作，然后修改记录的代码。

[PivotTable (对象) \| AirScript文档](https://airsheet.wps.cn/docs/apiV2/excel/workbook/PivotTable/PivotTable%20%E5%AF%B9%E8%B1%A1.html)

## 常用方法和属性

![[数据透视表1.jpeg]]


后续见附件