樞紐分析表(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/
留言
張貼留言