一簿中多表创建透视表的练习

OKJSJSF

这样快多了。

OKJSJSF

工作簿连接的位置是这么回事：

OKJSJSF

OKJSJSF 发表于 2021-4-17 22:38
联合查询有时要用  表名、双引号及逗号，有时确不要。？

            SQL = SQL & "select """ & sh.Name & """ , * from [" & sh.Name & "$]"
如前，有双引号与逗号时，透视表中多了以工作表名称作选项组成的字段 expr1000，方便筛选某表进行透视。
            SQL = SQL & "select * from [" & sh.Name & "$]"
如前，透视表少了一个表名字段。
但实际情况不是这么简单。很多时候，采用下一句SQL语句时，无法生成多字段透视表，只会生成一个 expr1000 字段的透视表，不知什么原因。

OKJSJSF

Excel 开发人员参考
PivotCache.CreatePivotTable 方法
全部显示全部显示
全部隐藏全部隐藏
创建一个基于 PivotCache 对象的数据透视表。返回一个 PivotTable 对象。
语法

表达式.CreatePivotTable(TableDestination, TableName, ReadData, DefaultVersion)

表达式   一个代表 PivotCache 对象的变量。

参数

名称 必选/可选 数据类型 描述
TableDestination 必选 Variant 数据透视表目标区域（工作表中用于放置所生成的数据透视表的区域）左上角的单元格。目标区域必须位于工作簿（此工作簿包含由 expression 指定的 PivotCache 对象）的某个工作表中。
TableName 可选 Variant 新的数据透视表的名称。
ReadData 可选 Variant 如果该值为 True，则创建一个包含外部数据库中所有记录的数据透视表高速缓存；此高速缓存可以很大。如果为 False，则允许在实际读取数据之前将某些字段设置为基于服务器的页字段。
DefaultVersion 可选 Variant 数据透视表的默认版本。

返回值
PivotTable

说明


有关创建基于数据透视表高速缓存的数据透视表的另一种方法，请参阅 PivotTables 对象的 Add 方法。


示例


本示例在活动工作表的 A3 单元格上新建一个基于 OLAP 提供程序 （OLAP 提供程序：对特定类型的 OLAP 数据库提供访问功能的一组软件。该软件包括数据源驱动程序以及与数据库连接所必需的其他客户端软件。）的数据透视表高速缓存，然后基于该高速缓存新建一个数据透视表。

Visual Basic for Applications
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
    .Connection = _
        "OLEDB;Provider=MSOLAP;Location=srvdata;Initial Catalog=National"
   .CommandType = xlCmdCube
   .CommandText = Array("Sales")
   .MaintainConnection = True
    .CreatePivotTable TableDestination:=Range("A3"), _
        TableName:= "PivotTable1"
End With
With ActiveSheet.PivotTables("PivotTable1")
    .SmallGrid = False
    .PivotCache.RefreshPeriod = 0
    With .CubeFields("[state]")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With .CubeFields("[Measures].[Count Of au_id]")
        .Orientation = xlDataField
        .Position = 1
    End With
End With

OKJSJSF

先熟悉一下用一张工作表创建透视表。

OKJSJSF

Sub add()
        ActiveSheet.PivotTableWizard xlDatabase, Worksheets("贷款明细").Range("a1:x10")
End Sub
上面过程是在活动工作表的活动单元格中，凭指定工作表的一个指定单元格区域，创建一个新的透视表。可以改变活动单元格地址后，不断创建新的透视表，透视表会自动按创建顺序命名，象在工具箱中不断拖入控件至窗体中一样，不会冲突出错。

OKJSJSF

上楼的代码省略了参数标签，加上去后如下，结果一样，速度是否一样不清楚：

Sub add()
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=Worksheets("贷款明细").Range("a1:x10")
End Sub
SourceType:=xlDatabase表示透视表的数据源类型，即凭单元格区域创建透视表，不是凭连接创建透视表。
SourceData:=后面接具体的数据源的单元格区域。

OKJSJSF

Excel 开发人员参考
Worksheet.PivotTableWizard 方法
创建一个新的数据透视表。此方法不显示“数据透视表向导”，它不适用于 OLE DB 数据源。请使用 Add 方法添加数据透视表缓存，然后创建基于该缓存的数据透视表。
语法

表达式.PivotTableWizard(SourceType, SourceData, TableDestination, TableName, RowGrand, ColumnGrand, SaveData, HasAutoFormat, AutoPage, Reserved, BackgroundQuery, OptimizeCache, PageFieldOrder, PageFieldWrapCount, ReadData, Connection)

表达式   一个代表 Worksheet 对象的变量。

参数

名称 必选/可选 数据类型 描述
SourceType 可选 Variant 一个代表报表数据源的 XlPivotTableSourceType 值。如果指定了此参数，那么必须同时指定 SourceData。如果忽略 SourceType 和 SourceData，Microsoft Excel 将假定源类型为 xlDatabase，并假定源数据来自命名区域“Database”。如果该命名区域不存在，那么当选定区域所在的当前区域中包含数据的单元格超过 10 个时，Microsoft Excel 就使用当前区域。否则，此方法将失败。
SourceData 可选 Variant 新报表的数据。它可以是一个 Range 对象、一个区域数组或是代表其他报表名称的一个文本常量。对于外部数据库而言，SourceData 是一个包含 SQL 查询字符串的字符串数组，其中的每个元素最长为 255 个字符。您应该使用 Connection 参数指定 ODBC 连接字符串。为了能与旧版本的 Excel 兼容，SourceData 可以是一个二元数组。第一个元素是用于指定数据的 ODBC 源的连接字符串，第二个元素是用于获取数据的 SQL 查询字符串。如果指定 SourceData，则必须同时指定 SourceType。如果活动单元格位于 SourceData 区域内，则必须同时指定 TableDestination。
TableDestination 可选 Variant 一个 Range 对象，它指定报表在工作表中的位置。如果省略此参数，则将报表置于活动单元格中。
TableName 可选 Variant 用于指定新报表名称的字符串。
RowGrand 可选 Variant 如果为 True，则显示报表中的行总计。
ColumnGrand 可选 Variant 如果为 True，则显示报表中的列总计。
SaveData 可选 Variant 如果为 True，则保存报表中的数据。如果为 False，则仅保存报表的定义。
HasAutoFormat 可选 Variant 如果为 True，当刷新报表或移动字段时，Microsoft Excel 将自动设置其格式。
AutoPage 可选 Variant 仅当 SourceType 为 xlConsolidation 时有效。如果值为 True，Microsoft Excel 将为合并创建页字段。如果 AutoPage 为 False，则必须创建一个或多个页字段。
Reserved 可选 Variant 不在 Microsoft Excel 中使用。
BackgroundQuery 可选 Variant 如果为 True，则 Excel 将异步执行（后台执行）报表查询。默认值为 False。
OptimizeCache 可选 Variant 如果为 True，则在构造数据透视表的缓存时对其进行优化。默认值为 False。
PageFieldOrder 可选 Variant 在数据透视表布局中添加页字段的顺序。可为以下 XlOrder 常量之一：xlDownThenOver 或 xlOverThenDown。默认值为 xlDownThenOver。
PageFieldWrapCount 可选 Variant 数据透视表中每列或每行的页字段数。默认值为 0（零）。
ReadData 可选 Variant 如果为 True，则创建数据透视表缓存以包含外部数据库中的所有记录；此时缓存可能会很大。如果 ReadData 为 False，则可在实际读取数据之前，将某些字段设为基于服务器的页字段。
Connection 可选 Variant 包含 ODBC 设置的字符串，这些设置使得 Excel 可以连接 ODBC 数据源。连接字符串的格式为“ODBC;<连接字符串>”。该参数取代以前为 PivotCache 对象的 Connection 属性所做的任何设置。

返回值
一个代表新数据透视表的 PivotTable 对象。

示例


本示例通过 Microsoft Excel 数据库（包含在单元格区域 A1:C100 中）创建数据透视表。

Visual Basic for Applications
ActiveSheet.PivotTableWizard xlDatabase, Range("A1:C100")

OKJSJSF

上图是按上楼方法在A1、D2、G3单元格中创建的三个透视表的空表。透视表最初只会显示一个由四个单元格组成的田字框。拖入一个字段至各个区域，可以知道，田字框各单元格的作用。拖入筛选字段时，透视表会自动移动位置满足放置的需要。只有拖入数值字段时，行字段、列字段才会显示数据。该方法创建的透视表的单元格边框是实线的，不是无边框的。

OKJSJSF

下面过程凭活动工作簿的缓存集合的一个缓存创建一个透视表，缓存的数据源为指定单元格区域，不是连接。代码省略了参数标签。
Sub add()
    Dim i As Byte
    i = ActiveSheet.PivotTables.Count
    ActiveWorkbook.PivotCaches.Create(xlDatabase, Worksheets("贷款明细").Range("a1:x10")).CreatePivotTable ActiveCell.Offset(0, 4 * i), "PivotTable" & i + 1
End Sub