跳到主要內容

自動建立樞紐分析表


樞紐分析表(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/

留言

這個網誌中的熱門文章

QEMU安裝與測試-x86篇

QEMU安裝與測試-x86篇 以下都是在 WSL(Windows Subsystem for Linux) 第2版(WSL 2)環境下用 Ubuntu 24.04 測試的: qemu-system-x86_64 --version 上面的指令顯示 QEMU x86_64 模擬器的版本,以下用 8.2.2 版測試安裝 Windows 10: Windows 10 安裝光碟映像檔可以從微軟下載 媒體安裝工具 ,然後依媒體安裝工具指示下載 ISO 檔,下載完成後準備一個虛擬磁碟機檔案進行安裝,例如: qemu-img create -f qcow2 winten.qcow2 40G 上面的指令用 QEMU 映像檔工具建立虛擬磁碟機檔案 winten.qcow2,設定最多可以成長至40G,準備好就開始進行安裝: sudo qemu-system-x86_64 -M q35 \ -m 2G \ -hda winten.qcow2 \ -cdrom Windows.iso \ -boot d \ -accel kvm \ -device qemu-xhci \ -device usb-tablet 上面的指令用 qemu-system-x86_64 模擬 q35 晶片組的主機,加上 -m 選項模擬 2G 的記憶體,檔案 winten.qcow2 作為磁碟機,檔案 Windows.iso 作為光碟機,設定光碟開機,因為使用 x86 主機模擬 x86 架構,這裡加上 -accel kvm 選項(Kernel-based Virtual Machine)啟動核心虛擬機支援(WSL 已經可以使用),然後加入 USB 控制器,在上面加入 USB 平板以取代 PS/2 滑鼠,避免鼠標錯亂。 請按照正常程序安裝 Windows,建置好的 Windows 虛擬機也需要正常關機,以避免虛擬磁碟機檔案毀損。 由於 /dev/kvm 權限設定的關係,加上 -accel kvm 選項後 qemu-system-x86_64 需要用 root 執行,可以將執行模擬器的帳號加入 kvm 群組,避免後續問題,例如: sudo usermod -a -G kvm username 提醒:加入群組需要重新登入後生效。Windows 安裝完成後,下次啟動 Windows 虛擬...

QEMU安裝與測試-ARM篇

QEMU安裝與測試-ARM篇 以下都是在 WSL(Windows Subsystem for Linux) 第2版(WSL 2)環境下用 Ubuntu 24.04 測試的: qemu-system-arm --version 上面的指令顯示 QEMU ARM 模擬器的版本,以下都是用 8.2.2 版測試的,如果系統裡面內沒有安裝 QEMU ARM 模擬器,Ubuntu 會提示用套件管理工具(apt)進行安裝(install),例如: sudo apt install qemu-system-arm WSL 使用 NAT(Net Address Translation) 架構存取網路,所以在 WSL 環境下,Ubuntu 安裝好就可以對外連線,如果上面的指令沒有辦法連線安裝 qemu-system-arm 的話,可能是因為需要設定套件管理系統的 proxy,例如: sudo vi /etc/apt/apt.conf.d/proxy.conf 在檔案 proxy.conf 內加入代理伺服器設定,例如: Acquire::http::Proxy "http://proxy.yoyodyne.com:18023/"; Acquire::https::Proxy "http://proxy.yoyodyne.com:18023/"; 參考 QEMU ARM 模擬器說明 ,有些 預先建置好的系統 可以直接拿來用,這裡下載 Squeeze(Debian 6.0) 來測試: wget https://people.debian.org/~aurel32/qemu/armel/README.txt wget https://people.debian.org/~aurel32/qemu/armel/vmlinuz-2.6.32-5-versatile wget https://people.debian.org/~aurel32/qemu/armel/initrd.img-2.6.32-5-versatile wget https://people.debian.org/~aurel32/qemu/armel/debian_squeeze_armel_standard.qcow2 如果沒有辦法連線下載,可能是因為需要設定 ...