Note: The other languages of the website are Google-translated. Back to English
登入 立即註冊

如何基於Excel中的特定單元格值篩選數據透視表？

通常，我們通過在下拉列表中選擇項目來過濾數據透視表中的數據，如下面的屏幕截圖所示。 實際上，您可以根據特定單元格中的值過濾數據透視表。 本文中的 VBA 方法將幫助您解決問題。

使用VBA代碼根據特定單元格值過濾數據透視表

使用VBA代碼根據特定單元格值過濾數據透視表

下面的VBA代碼可以幫助您基於Excel中的特定單元格值過濾數據透視表。 請執行以下操作。

1.請輸入一個值，您將根據該值預先將數據透視表過濾到一個單元格中（這裡我選擇單元格H6）。

2.打開包含要根據單元格值過濾的數據透視表的工作表。 然後右鍵單擊工作表選項卡，然後從上下文菜單中選擇“查看代碼”。 看截圖：

3.在開幕 Microsoft Visual Basic for Applications 窗口，將下面的VBA代碼複製到“代碼”窗口中。

VBA代碼：根據單元格值過濾數據透視表

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20180702
    Dim xPTable As PivotTable
    Dim xPFile As PivotField
    Dim xStr As String
    On Error Resume Next
    If Intersect(Target, Range("H6:H7")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Set xPTable = Worksheets("Sheet1").PivotTables("PivotTable2")
    Set xPFile = xPTable.PivotFields("Category")
    xStr = Target.Text
    xPFile.ClearAllFilters
    xPFile.CurrentPage = xStr
    Application.ScreenUpdating = True
End Sub

筆記：在代碼中，

1）“Sheet1”是工作表的名稱。
2）“數據透視表2”是數據透視表的名稱。
3）數據透視表中的過濾字段稱為“類別".
4）您要過濾數據透視表的值放在單元格中 H6.
您可以根據需要更改上述變量值。

4。 按 其他 + Q 關閉鍵 Microsoft Visual Basic for Applications 窗口。

然後，數據透視表將根據單元格H6中的值進行過濾，如下圖所示：

您可以根據需要將單元格值更改為其他值。

備註：您在單元格H6中鍵入的值應與數據透視表的“類別”下拉列表中的值完全匹配。

相關文章：

最佳辦公效率工具

Kutools for Excel 解決了你的大部分問題，並將你的生產力提高了 80%

  • 重用: 快速插入 複雜的公式，圖表 以及您以前使用過的任何東西； 加密單元 帶密碼 創建郵件列表 並發送電子郵件...
  • 超級公式欄 （輕鬆編輯多行文本和公式）； 閱讀版式 （輕鬆讀取和編輯大量單元格）； 粘貼到過濾範圍...
  • 合併單元格/行/列 不會丟失數據； 拆分單元格內容； 合併重複的行/列...防止細胞重複； 比較範圍...
  • 選擇重複或唯一 行; 選擇空白行 （所有單元格都是空的）； 超級查找和模糊查找 在許多工作簿中； 隨機選擇...
  • 確切的副本 多個單元格，無需更改公式參考； 自動創建參考 到多張紙； 插入項目符號，複選框等...
  • 提取文字，添加文本，按位置刪除， 刪除空間; 創建和打印分頁小計； 在單元格內容和註釋之間轉換...
  • 超級濾鏡 （將過濾方案保存並應用於其他工作表）； 高級排序 按月/週/日，頻率及更多； 特殊過濾器 用粗體，斜體...
  • 結合工作簿和工作表; 根據關鍵列合併表； 將數據分割成多個工作表; 批量轉換xls，xlsx和PDF...
  • 超過 300 項強大的功能. 支持 Office / Excel 2007-2021 和 365。支持所有語言。 在您的企業或組織中輕鬆部署。 完整功能 30 天免費試用。 60 天退款保證。
kte選項卡201905

Office選項卡為Office帶來了選項卡式界面，使您的工作更加輕鬆

  • 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！
officetab底部
按評論排序
最舊的
最新的
留言 (23)
還沒有評分。 成為第一位評論！
但以理書
但以理書
網站主持人對此評論進行了最小化
使用此代碼（當然為我的變量更新），當更改字段時，過濾器會立即更改為正確的，然後幾乎立即清除。 試圖弄清楚它為什麼這樣做（想知道它是否與子末尾的 ClearAllFilters 有關？）
回复
0
0
賈斯汀
賈斯汀
網站主持人對此評論進行了最小化
您將如何使用具有層次結構的報表過濾器來執行此操作？
回复
0
0
伊尼亞西亞
伊尼亞西亞
網站主持人對此評論進行了最小化
嘿！ 謝謝你的宏。

我試圖將它用於同一頁面中的多個數據透視表，但它不起作用。 我是這樣寫的：

Private Sub Worksheet_Change（ByVal Target As Range）
將 xPTable1 調暗為數據透視表
將 xPFile1 調暗為 PivotField
將 xStr1 調暗為字符串
在錯誤恢復下一頁
如果 Intersect(Target, Range("D7")) 什麼都不是，則退出 Sub
Application.ScreenUpdating = False
設置 xPTable1 = Worksheets("BUSCADOR").PivotTables("PV_ETAPA1")
設置 xPFile1 = xPTable1.PivotFields("ETAPA1")
xStr1 = 目標文本
xPFile1.ClearAllFilters
xPFile1.CurrentPage = xStr1
Application.ScreenUpdating = True

將 xPTable2 調暗為數據透視表
將 xPFile2 調暗為 PivotField
將 xStr2 調暗為字符串
在錯誤恢復下一頁
如果 Intersect(Target, Range("G7")) 什麼都不是，則退出 Sub
Application.ScreenUpdating = False
設置 xPTable2 = Worksheets("BUSCADOR").PivotTables("PV_ETAPA2")
設置 xPFile2 = xPTable2.PivotFields("ETAPA2")
xStr2 = 目標文本
xPFile2.ClearAllFilters
xPFile2.CurrentPage = xStr2
Application.ScreenUpdating = True

END SUB

也許你能幫幫我！

提前感謝！
回复
0
0
邁克爾
邁克爾     伊尼亞西亞
網站主持人對此評論進行了最小化
Hi


感謝宏


我正在嘗試同樣的事情，但無法讓它在 2 張桌子上工作。 他們都在看同一個單元格，只是 2 個不同的數據透視表


謝謝
回复
0
0
奧克薩娜
奧克薩娜     邁克爾
網站主持人對此評論進行了最小化
您必須更改數據透視表名稱。 每個數據透視表都有不同的名稱。 為此，右鍵單擊數據透視表並選擇數據透視表設置，名稱將位於頂部
回复
0
0
安布羅斯
安布羅斯     奧克薩娜
網站主持人對此評論進行了最小化
你好，

Je ne comprends pas comment ajouter le nom du second TCD dans la macro pour que cela fonctionne sur les deux。
Pourriez-vous m'aider？

謝謝
回复
0
0
阿拉汶
阿拉汶
網站主持人對此評論進行了最小化
您好，不知為何，這個宏進入visual basic頁面後，根本沒有出現。 我無法啟用/運行此宏，我檢查了所有信任中心設置，但沒有任何反應，請幫助我
回复
0
0
希瑟
希瑟
網站主持人對此評論進行了最小化
嗨，我似乎無法讓它工作。 我要引用的單元格是從公式中提取出來的 - 這就是為什麼過濾器在查看公式而不是公式返回的值時找不到它的原因嗎？提前謝謝希瑟麥克唐納
回复
0
0
薩布麗娜
薩布麗娜     希瑟
網站主持人對此評論進行了最小化
嗨，Heather，你找到解決方案了嗎？ 我也有同樣的問題。
回复
0
0
AK泰勒
AK泰勒
網站主持人對此評論進行了最小化
我能夠修改/過濾位於同一選項卡上的 3 個不同的樞軸。 我還在我的數據集“未找到數據”中添加了一行，否則它將過濾器留給我不想要的“全部”。 以上內容對我贏得管理層的讚譽有很大幫助，所以我想分享。 請注意 (All) 是區分大小寫的，這讓我有點想明白。
Private Sub Worksheet_Change（ByVal Target As Range）
'測試
將 xPTable 調暗為數據透視表
將 xPFile 調暗為 PivotField
將 xStr 調暗為字符串

將 x2PTable 調暗為數據透視表
將 x2PFile 調暗為 PivotField
將 x2Str 調暗為字符串

將 x3PTable 調暗為數據透視表
將 x3PFile 調暗為 PivotField
將 x3Str 調暗為字符串

在錯誤恢復下一頁
如果 Intersect(Target, Range("a2:e2")) 沒有，則退出 Sub

Application.ScreenUpdating = False

'tbl-1
設置 xPTable = Worksheets("Graphical").PivotTables("PivotTable1")
Set xPFile = xPTable.PivotFields("MR 部門 - 部門")
xStr = 目標文本
xPFile.ClearAllFilters
xPFile.CurrentPage = xStr
If xPFile.CurrentPage = "(All)" Then xPFile.CurrentPage = "No Data Found"

'tbl-2
設置 x2PTable = Worksheets("Graphical").PivotTables("PivotTable2")
Set x2PFile = x2PTable.PivotFields("MR 部門 - 部門")
x2Str = 目標文本
x2PFile.ClearAllFilters
x2PFile.CurrentPage = x2Str
If x2PFile.CurrentPage = "(All)" Then x2PFile.CurrentPage = "No Data Found"

'tbl-3
設置 x3PTable = Worksheets("Graphical").PivotTables("PivotTable3")
Set x3PFile = x3PTable.PivotFields("MR 部門 - 部門")
x3Str = 目標文本
x3PFile.ClearAllFilters
x3PFile.CurrentPage = x3Str
If x3PFile.CurrentPage = "(All)" Then x3PFile.CurrentPage = "No Data Found"

Application.ScreenUpdating = True

END SUB
回复
0
0
德魯
德魯
網站主持人對此評論進行了最小化
谷歌表格可以做到這一點嗎？ 如果是這樣，怎麼做？
回复
0
0
維寶
維寶     德魯
網站主持人對此評論進行了最小化
Google 表格不需要任何數據透視表。 可以直接通過Filter Function來執行
回复
0
0
sdas34
sdas34
網站主持人對此評論進行了最小化
我想在同一個工作表中使用多個工作表更改代碼。 怎麼做？ 我的代碼如下：
Private Sub Worksheet_Change（ByVal Target As Range）
'基於單元格值的數據透視表過濾器
將 xPTable 調暗為數據透視表
將 xPFile 調暗為 PivotField
將 xStr 調暗為字符串
在錯誤恢復下一頁
如果 Intersect(Target, Range("D20:D21")) 什麼都不是，則退出 Sub
Application.ScreenUpdating = False
設置 xPTable = Worksheets("Sheet1").PivotTables("PivotTable2")
設置 xPFile = xPTable.PivotFields("Designation")
xStr = 目標文本
xPFile.ClearAllFilters
xPFile.CurrentPage = xStr
Application.ScreenUpdating = True
END SUB

Private Sub Worksheet_Change2（ByVal Target As Range）
'基於單元格值2的數據透視表過濾器
將 xPTable 調暗為數據透視表
將 xPFile 調暗為 PivotField
將 xStr 調暗為字符串
在錯誤恢復下一頁
如果 Intersect(Target, Range("H20:H21")) 什麼都不是，則退出 Sub
Application.ScreenUpdating = False
設置 xPTable = Worksheets("Sheet1").PivotTables("PivotTable2")
設置 xPFile = xPTable.PivotFields("Offering")
xStr = 目標文本
xPFile.ClearAllFilters
xPFile.CurrentPage = xStr
Application.ScreenUpdating = True
END SUB
回复
即時報告
0
0
費爾南多
費爾南多
網站主持人對此評論進行了最小化
Olá, gostaria de saber se quisesse filtrar mais de uma categoria como poderia ser?
回复
0
0
奧克薩娜
奧克薩娜
網站主持人對此評論進行了最小化
如果我想將選擇單元格鏈接到不同的選項卡怎麼辦？ 到目前為止，這是我的代碼
Private Sub Worksheet_Change（ByVal Target As Range）
將 xPTable1 調暗為數據透視表
將 xPFile1 調暗為 PivotField
將 xStr1 調暗為字符串
在錯誤恢復下一頁
如果 Intersect(Target, Range("B1")) 什麼都不是，則退出 Sub
Application.ScreenUpdating = False
設置 xPTable1 = Worksheets("SM_SKU PIVOTS").PivotTables("PivotTable1")
設置 xPFile1 = xPTable1.PivotFields("Geography")
xStr1 = 目標文本
xPFile1.ClearAllFilters
xPFile1.CurrentPage = xStr1
Application.ScreenUpdating = True

將 xPTable2 調暗為數據透視表
將 xPFile2 調暗為 PivotField
將 xStr2 調暗為字符串
在錯誤恢復下一頁
如果 Intersect(Target, Range("B1")) 什麼都不是，則退出 Sub
Application.ScreenUpdating = False
設置 xPTable2 = Worksheets("SM_SKU PIVOTS").PivotTables("PivotTable4")
設置 xPFile2 = xPTable2.PivotFields("Geography")
xStr2 = 目標文本
xPFile2.ClearAllFilters
xPFile2.CurrentPage = xStr2
Application.ScreenUpdating = True

將 xPTable3 調暗為數據透視表
將 xPFile3 調暗為 PivotField
將 xStr3 調暗為字符串
在錯誤恢復下一頁
如果 Intersect(Target, Range("B1")) 什麼都不是，則退出 Sub
Application.ScreenUpdating = False
設置 xPTable3 = Worksheets("SM_SKU PIVOTS").PivotTables("PivotTable8")
設置 xPFile3 = xPTable3.PivotFields("Geography")
xStr3 = 目標文本
xPFile3.ClearAllFilters
xPFile3.CurrentPage = xStr3
Application.ScreenUpdating = True

END SUB
回复
0
0
亞歷克斯
亞歷克斯
網站主持人對此評論進行了最小化
您好！

我是 VBA 新手，我想要一個代碼來根據單元格範圍選擇一個數據透視過濾器。
如何將“CurrentPage”更改為範圍值？
謝謝！！
-------------------------------------------------- -----------------------------------------
子 PrintTour()

ActiveSheet.PivotTables("PivotTable1").PivotFields(_
“[Bereich 1].[Tour].[Tour]”）。 _
清除所有過濾器
ActiveSheet.PivotTables("PivotTable1").PivotFields(_
“[Bereich 1].[Tour].[Tour]”）。 _
CurrentPage = "[Bereich 1].[Tour lt. Anlieferungstag].&[4001-01]"
END SUB
回复
0
0
槊
槊
網站主持人對此評論進行了最小化
非常感謝您提供此代碼！ 在調整以滿足我的領域後，我讓它工作了，但是在我的工作表上格式化了一些更改之後，它現在不起作用了！ 我將它從 A1 移到 B1，更改了一些單元格格式以使其脫穎而出，等等。沒什麼太瘋狂的，但現在當我更改 B1 中的文本時它不會更新。 有人有想法麼？

Private Sub Worksheet_Change（ByVal Target As Range）
'測試
將 xPTable 調暗為數據透視表
將 xPFile 調暗為 PivotField
將 xStr 調暗為字符串

將 x2PTable 調暗為數據透視表
將 x2PFile 調暗為 PivotField
將 x2Str 調暗為字符串

將 x3PTable 調暗為數據透視表
將 x3PFile 調暗為 PivotField
將 x3Str 調暗為字符串

在錯誤恢復下一頁
如果 Intersect(Target, Range("b1")) 什麼都不是，則退出 Sub

Application.ScreenUpdating = False

'tbl-1
設置 xPTable = Worksheets("Line Report").PivotTables("PivotTable7")
設置 xPFile = xPTable.PivotFields("Utopia Source")
xStr = 目標文本
xPFile.ClearAllFilters
xPFile.CurrentPage = xStr

'tbl-2
設置 x2PTable = Worksheets("Line Report").PivotTables("PivotTable2")
設置 x2PFile = x2PTable.PivotFields("Utopia Source")
x2Str = 目標文本
x2PFile.ClearAllFilters
x2PFile.CurrentPage = x2Str

'tbl-3
設置 x3PTable = Worksheets("Line Report").PivotTables("PivotTable3")
設置 x3PFile = x3PTable.PivotFields("Utopia Source")
x3Str = 目標文本
x3PFile.ClearAllFilters
x3PFile.CurrentPage = x3Str

Application.ScreenUpdating = True

END SUB
回复
0
0
水晶
水晶     槊
網站主持人對此評論進行了最小化
嗨蘭斯，
我測試了你的代碼，它在我的情況下運行良好。 更改單元格格式不會影響代碼的運行。
回复
即時報告
0
0
DK
DK
網站主持人對此評論進行了最小化
使用多個表時，它如何與 Power Pivot 一起使用？ 我錄製了宏更改過濾器中的值。 進行了一些更改以使上述代碼正常工作。 但它會引發類型不匹配錯誤。 不管我做什麼。
回复
0
0
水晶
水晶     DK
網站主持人對此評論進行了最小化
嗨，DK，
該方法不適用於 Power Pivot。 帶來不便敬請諒解。
回复
即時報告
0
0
安布羅斯
安布羅斯
網站主持人對此評論進行了最小化
你好，
非常感謝您的解釋。

J'aimerai utiliser un filtre (1 cellule) en F4 par example qui filtrerait deux TCD qui sont sur la même feuille.

Cela fonctionne très bien avec un TCD mais dès que j'essaye de combiner le second, ça ne marche pas.
Pourriez-vous m'aider？

非常感謝
安布羅斯
回复
0
0
安布羅斯
安布羅斯
網站主持人對此評論進行了最小化
你好，

Merci beaucoup pour cette explication qui marche parfaitement。
En revanche, j'aimerais pouvoir utiliser ce code pour pouvoir filtrer deux tableaux croisés dynamiques en même temps qui sont sur la même feuille。 La seule petite différence entre les deux，c'est qu'ils n'utilisent pas les mêmes 資源。 En revanche，le filtre sur lequel se base ces TDC est le même。

Pourriez-vous m'aider à faire évoluer ce code afin que cela fonctionne？

Voici le code utilisé quand il marche avec un TCD :

Private Sub Worksheet_Change（ByVal Target As Range）
'更新 Extendoffice 20180702
將 xPTable 調暗為數據透視表
將 xPFile 調暗為 PivotField
將 xStr 調暗為字符串
在錯誤恢復下一頁
如果 Intersect(Target, Range("G4")) 什麼都不是，則退出 Sub
Application.ScreenUpdating = False
設置 xPTable = Worksheets("Cadrage").PivotTables("Tableau croisé dynamique7")
設置 xPFile = xPTable.PivotFields("N°PROJET")
xStr = 目標文本
xPFile.ClearAllFilters
xPFile.CurrentPage = xStr
Application.ScreenUpdating = True
END SUB

非常感謝
回复
0
0
水晶
水晶     安布羅斯
網站主持人對此評論進行了最小化
嗨，安布羅斯，

抱歉，很難修改此代碼以滿足您的需求。 如果您想使用一個過濾器來過濾多個數據透視表，下面這篇文章中的方法可能會對您有所幫助：
如何將單個切片器連接到 Excel 中的多個數據透視表？
回复
即時報告
0
0
這裡還沒有評論
留下你的意見
客人
以訪客身份發帖
登入
×
評價此帖子：
重設
0   字符
推薦地點

關注我們

版權所有©2009 - 萬維網。extendoffice.com。 | 版權所有。 供電 ExtendOffice。 |
Microsoft和Office徽標是Microsoft Corporation在美國和/或其他國家的商標或註冊商標。
受Sectigo SSL保護