(巧克的VBA分享) 如何利用VBA寫出EXCEL樞紐分析Pivot table-如何關閉掉subtotal合計欄位?

首先，先簡單說明一下，為什麼一個非資工、資管背景的新鮮人會想要學習VBA?

由於工作上的需求，巧克需要定期產生許多報表供不同團隊使用，一開始耗費了許多時間來解決不同團隊的需求。後來，在主管的建議下，開始研究VBA的撰寫以提升工作效率。
當然，對於剛出社會的新鮮人，一堂課需要花上千甚至上萬塊，是龐大的負擔。



因此，在剛開始工作的第一年，巧克先從最簡單的「錄製巨集」開始，搭配一些VBA的書籍，研究那些程式碼，再去進行修改。另外在Youtube上看好心人的教學課程。(台灣的吳老師、阿拉伯人、印度人...等。)

到了工作的第二年後，自己有了一些積蓄，想要將VBA的概念弄得更清楚，因此，報名了東吳推廣部吳老師的課程。學了更札實的概念，也讓自己的程式更加簡潔有力。但是，老師的工作是引我們入門，如何運用在工作上，還是要靠自己。

這邊先簡單分享一下，樞紐分析的VBA語法。基本上，在台灣各大網站都能找到這些語法，因此我不再贅述寫出VBA的部分，而將重點放在如何關閉掉subtotal欄位?

有打開Subtotal合計欄位

有關閉Subtotal合計欄位

程式碼如下:


Public Sub 樞紐分析表()
Dim PTCache As PivotCache
Dim PT As PivotTable

    Set PTCache = ThisWorkbook.PivotCaches.Add _
        (SourceType:=xlDatabase, _
         SourceData:=Sheets("VBA").Range("A1").CurrentRegion.Address)

    Set PT = PTCache.CreatePivotTable _
        (TableDestination:="", _
         TableName:="PivotTable1")

'在新的PivotTable上設置欄位
      With PT

'將 [Format] 作為分頁
        .PivotFields("Format").Orientation = xlPageField
 
'將 [Location] 作為欄
        .PivotFields("Location").Orientation = xlColumnField

 
'依序將 [PGA] [Material No.][Description2][Unit(F)][Expiration date(YYYYMM)] 作為列
        .PivotFields("PGA").Orientation = xlRowField
        .PivotFields("Material No.").Orientation = xlRowField
        .PivotFields("Description2").Orientation = xlRowField
        .PivotFields("Unit(F)").Orientation = xlRowField
        .PivotFields("Expiration date(YYYYMM)").Orientation = xlRowField
   
 '將 [Quantity(F)] 作為資料
        .PivotFields("Quantity(F)").Orientation = xlDataField

    End With
    '建立樞紐分析表會顯示出其命令列, 關掉它
    Application.CommandBars("PivotTable").Visible = False

'如何關閉掉subtotal欄位?
 ActiveSheet.PivotTables("PivotTable1").PivotFields("PGA").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
 ActiveSheet.PivotTables("PivotTable1").PivotFields("Material No.").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Description2").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Unit(F)").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Expiration date(YYYYMM)").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)

End Sub

大家可以發現，每一個要被我們關閉的Subtotal會出現12次的false在Array。
這些False為什麼會是12次呢?後來經國外的文獻查證，這12次代表的是下圖裡面的值:

欄位設定

Array(True, False, False, False, False, False, False, False, False, False, False, False)

如果你讓第一個False等於True的話，合計(subtotal)這個值就會出現囉!
12個依序為:合計、加總、項目個數、平均值、最大值、最小值、乘積、數字項個數、標準差、母體標準差、變異值、母體變異值。

請大家在依據自己需求進行修改吧! :）