☰

Excel 資料透視表 - 如何在資料刷新時保留篩選和排序

tag-icon • tag-icon microsoft-excel-2010 vba pivot-table
Excel 資料透視表 - 如何在資料刷新時保留篩選和排序

我有一個資料透視表（Excel 2010）。每次更新資料並刷新時，資料透視表的排序和篩選器都會分別遺失，我需要明確修改篩選器（選擇全部，並刪除不需要的篩選器）。

有什麼解決辦法嗎？

  • 當資料透視表刷新時，如何透過 VBA 更改過濾器？
  • 當資料透視表刷新時，如何透過 VBA 更改排序？
  • 當資料發生變化時如何觸發資料透視表的刷新？

預先感謝您的問候馬克

答案1

我可以實現我想要的。我首先簡單地記錄了一個宏，以便查看必須如何存取哪些物件的詳細資訊。後來我透過一些優化完成了這個事情。請參考下面的程式碼摘錄，也許對其他人有幫助。

關於排序，我在論壇中發現，您應該在原始資料表中分配另一列“排序順序”，並將其用作資料透視表中的隱藏列，以便您可以使用該欄位定義排序順序。當您以後更改資料時，這一點也不會丟失。

Sub Macro1()
On Error GoTo Fehler
Application.ScreenUpdating = False
With ActiveWorkbook.Sheets("Rating").PivotTables(1)
.PivotFields("OWASP 2010").ClearAllFilters
With .PivotFields("OWASP 2010")

    If .PivotItems("(blank)").Visible = True Then
        .PivotItems("(blank)").Visible = False
    End If
    ' uncomment this sectin in case you liek to change the sorting via vba
    ' 1. sort first the stuff automatically A-Z
    '.AutoSort xlAscending, "OWASP 2010"
    ' 2. move empty values to the end
    'If .PivotItems(" ").Position <> .PivotItems.Count - 1 Then
    '    .PivotItems(" ").Position = .PivotItems.Count - 1
    'End If
 End With
.PivotFields("Categories").ClearAllFilters
With .PivotFields("Fortify Categories")
     If .PivotItems("0").Visible = True Then
        .PivotItems("0").Visible = False
     End If
     If .PivotItems("(blank)").Visible = True Then
        .PivotItems("(blank)").Visible = False
    End If
End With
End With
Application.ScreenUpdating = True
Exit Sub
Fehler:
Application.ScreenUpdating = True
End Sub

問候馬克

相關內容