自動建立樞紐分析表

樞紐分析表(PivotTable)是交叉分析的好幫手，還可以深入分析(drill-down)與彙總(roll-up)各個維度的資料。建立樞紐分析表也很容易，但有時候應用程式仍然有自動建立樞紐分析表的需求，Excel VBA 建立樞紐分析表的方式之一就是用 Worksheet.PivotTableWizard 方法。以Office範本藝廊的銷售員樞紐分析表來源資料為例：

ActiveSheet.PivotTableWizard xlDatabase, Range(“A1:E800”), Range(“G3”)

即可產生樞紐分析表。如果沒有指定位置(TableDestination)的話，會放在 ActiveCell，如果也沒有 ActiveCell 的話，會放在新的工作表。

樞紐分析表會被加到 PivotTables 集合物件中，可經由 Worksheet.PivotTables 方法取得，例如：

For Each oPvtTable In ActiveSheet.PivotTables
  oPvtTable.TableRange2.Clear
Next

會清除工作表內所有的樞紐分析表^[1]。

應用程式也可以設定樞紐分析表的屬性，例如透過欄位清單(PivotFields)設定欄位方向(XlPivotFieldOrientation)：

Set oPvtTable = ActiveSheet.PivotTableWizard(xlDatabase, Range("A1:E800"))
oPvtTable.PivotFields("國家").Orientation = xl xlPageField
oPvtTable.PivotFields("銷售員").Orientation = xlRowField
oPvtTable.PivotFields("訂單金額").Orientation = xlDataField


此外，Worksheet.PivotTableWizard 可以從不同的資料來源(XlPivotTableSourceType)建立樞紐分析表，以Access 範本資料庫 Northwind.mdb 為例，比照其銷售分析表單建立樞紐分析表：

sQuery = "SELECT 員工.行政區, 員工.姓名, 員工.名" _
  &", 訂貨主檔.送貨日期, 訂貨主檔.訂單號碼" _
  &", 訂貨小計.小計 AS 銷售量, 員工.行政區" _
  &" FROM 員工 INNER JOIN (訂貨主檔 INNER JOIN 訂貨小計" _
  &" ON 訂貨主檔.訂單號碼 = 訂貨小計.訂單號碼)" _
  &" ON 員工.員工編號 = 訂貨主檔.員工編號;"


Set oPvtTable = ActiveSheet.PivotTableWizard(SourceType:=xlExternal _
  ,SourceData:=StringToArray(sQuery) _
  ,Connection:="ODBC;Driver={Microsoft Access Driver (*.mdb)}" _
  &";Dbq=Northwind.mdb;Uid=Admin;Pwd=;")


其中 SourceData 須為字串陣列(an array of strings)，且每個字串須少於255個字元，因此用 StringToArray 函數^[2]加以轉換；Connection 為 ODBC 連接字串^[3]，Worksheet.PivotTableWizard 支援 ODBC，但不支援 OLE DB。

[1] http://www.mrexcel.com/forum/excel-questions/518955-visual-basic-applications-delete-pivot-table.html
[2] http://support.microsoft.com/kb/213841/zh-tw
[3] https://www.connectionstrings.com/microsoft-access-odbc-driver/