如何根據Excel中的特定儲存格值來篩選樞紐分析表？

Author Siluvia Last modified 2025-08-23

在Excel中，樞紐分析表被廣泛用於有效率地總結、分析和探索數據。默認情況下，樞紐分析表內的篩選通常是通過從篩選下拉菜單中選擇所需的項目來完成的。雖然這種方法提供了靈活性，但在某些情況下需要更動態的篩選方法——例如，您可能希望樞紐分析表的結果根據特定工作表儲存格中輸入的值自動改變。這在準備儀表板、自動化工作流程或為不熟悉手動篩選的最終用戶構建互動式報告時特別有用。

Excel並未提供將儲存格值與樞紐分析表篩選器原生連接的標準功能（不用代碼的情況下）。然而，有多種實用技術可以實現這一需求，每種技術都有其優點和需要注意的地方。本教程首先介紹了一個簡單的VBA方法，可以直接將儲存格與樞紐分析表篩選器連接，這樣當儲存格值改變時，樞紐分析表會立即更新。此外，我們還將介紹其他替代方法，例如使用Excel公式（例如GETPIVOTDATA、FILTER）來顯示篩選後的結果，以及使用切片器作為圖形化的篩選控制。了解這些選項有助於您為您的Excel工作流和用戶體驗選擇最佳方法。

A screenshot showing a Pivot Table with a drop-down filter in Excel

💡 快速導航：
➤ 使用VBA代碼根據特定儲存格值篩選樞紐分析表
➤ Excel公式 - 根據儲存格值顯示篩選後的樞紐分析表結果
➤ 其他內置的Excel方法 - 使用切片器作為互動式的樞紐分析表篩選器

使用VBA代碼根據特定儲存格值篩選樞紐分析表

如果您想要真正的動態互動性——即當您輸入一個值到儲存格中時，樞紐分析表篩選器會自動響應該變化——VBA提供了一個直接的解決方案。這在儀表板、同事使用的模板或需要快速調整篩選條件的情況下特別有用。然而，此方法需要基本熟悉VBA編輯器，並且如同所有宏一樣，您的工作簿必須保存為啟用宏的格式（.xlsm）。

以下VBA代碼允許您將工作表儲存格動態連結到樞紐分析表篩選器。請仔細按照這些步驟操作，並根據您的工作簿需要修改工作表名稱、樞紐分析表名稱和字段引用：

步驟1：將您要用來篩選樞紐分析表的值輸入到工作表的一個儲存格中（例如，在H6儲存格中輸入或選擇篩選值）

步驟2：打開包含目標樞紐分析表的工作表。右鍵單擊Excel底部的工作表標籤，然後從上下文菜單中選擇「查看代碼」。這將打開工作表的VBA編輯器窗口。

A screenshot showing the View Code option for a worksheet in Excel

步驟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

📝 注意：

"Sheet1" 是包含樞紐分析表的工作表。根據需要進行調整。
"PivotTable2" 是您的樞紐分析表的名稱。您可以在樞紐分析表分析選項卡中找到它。
"Category" 是您要篩選的字段。它必須完全匹配字段名稱。
H6是篩選儲存格。確保該值與篩選列表中的項目匹配。
篩選值必須逐字匹配。多餘的空格或拼寫錯誤可能會導致錯誤或空白結果。

步驟4：按Alt + Q關閉VBA編輯器並返回Excel。

現在，您的樞紐分析表應該自動篩選以僅顯示與H6儲存格中輸入的值相匹配的數據。每次H6中的值發生變化時，這個宏都會運行，讓您可以輕鬆地動態調整數據摘要。

Pivot Table filtered based on a specific cell value

您可以隨時修改篩選儲存格中的值——只要更改或替換儲存格內容，樞紐分析表就會立即更新。

Result of changing the filter cell value for the Pivot Table

故障排除：

確保您的工作簿中啟用了宏。
再次檢查工作表、樞紐分析表和字段名稱是否與您的實際設置匹配。
確保H6中的篩選值與樞紐分析表的值完全匹配。
此VBA方法適用於單字段篩選。對於多字段篩選，需要額外的腳本編寫。

Excel公式 – 根據儲存格值顯示篩選後的樞紐分析表結果

對於不想啟用宏的用戶，Excel提供了基於公式的解決方案，根據特定儲存格值顯示樞紐分析表結果。雖然像GETPIVOTDATA和FILTER這樣的函數不會實際改變樞紐分析表的篩選設置，但它們可以動態引用並呈現響應用戶輸入的匯總結果。

這種解決方案在構建自定義匯總表、儀表板或報告時特別有用，這些報告反映了用戶輸入的變更標準——而無需更改原始樞紐分析表視圖。

使用GETPIVOTDATA：

假設您的樞紐分析表（名為"PivotTable2"）按類別總結銷售數據，並且篩選值輸入在H6儲存格中。您可以使用GETPIVOTDATA來顯示H6指定類別的總銷售額：

1. 選擇您想顯示匯總結果的儲存格（例如I6）：

=GETPIVOTDATA("Sum of Sales", $A$4, "Category", $H$6)

2. 按Enter鍵。當您更改H6中的值時，I6中的結果會自動更新以反映樞紐分析表中對應的匯總結果。

如果您的樞紐分析表使用不同的字段名稱或佈局，請相應調整公式。要自動生成GETPIVOTDATA公式，在儲存格中輸入=，然後點擊樞紐分析表內的值儲存格。Excel將插入適當的公式，您可以根據需要進行編輯。

使用FILTER與輔助表：

如果您想從原始數據集中提取詳細記錄（而不仅仅是樞紐分析表的匯總），並且您正在使用Excel 365或Excel 2019，FILTER函數允許根據儲存格值進行動態篩選：

假設您的源數據在A1:C100範圍內，且類別在A列中。

1. 選擇篩選記錄應該出現的起始儲存格（例如J6）：

=FILTER(A2:C100, A2:A100 = H6, "No data")

2. 按Enter鍵。匹配的行將溢出到相鄰的儲存格中，列出所有類別與H6中的值匹配的記錄。更新H6將立即刷新結果。

要匹配樞紐分析表分組或多條件篩選，考慮結合使用GETPIVOTDATA和FILTER，或使用額外的邏輯條件擴展公式。

📝 提示與警告：

這些公式不會修改實際的樞紐分析表篩選器。它們只根據儲存格值提供一個獨立的、動態的視圖。
要直接更改樞紐分析表篩選器，需要使用VBA。
確保GETPIVOTDATA中使用的字段名稱與樞紐分析表中的字段名稱完全匹配（大小寫和空格）。
如果看到#REF!錯誤，請驗證您的引用是否有效且樞紐分析表結構未發生變化。

其他內置的Excel方法 – 使用切片器作為互動式樞紐分析表篩選器

如果VBA或基於公式的解決方案不能完全滿足您的工作流程，Excel的切片器提供了另一種互動式篩選樞紐分析表的方法。切片器是可視化的篩選控件，允許用戶通過簡單的點擊界面篩選數據。儘管它們不能直接與儲存格值連接——意味著您不能通過更改儲存格來控制切片器——但它們對於非技術用戶使用的儀表板和報告來說直觀且非常有效。

如何添加和使用切片器：

選擇樞紐分析表中的任何儲存格。
轉到樞紐分析表分析選項卡（或舊版本中的分析選項卡），然後點擊插入切片器。
在插入切片器對話框中，勾選您要篩選的字段（如類別），然後點擊確定。
切片器將出現在您的工作表上。點擊按鈕即可按該值篩選樞紐分析表。按住Ctrl以選擇多個項目。

切片器可以進行格式化、調整大小，並連接到多個樞紐分析表以實現不同報告之間的同步篩選。它們在儀表板或共享工作簿中特別有用，因為用戶可能不習慣使用下拉篩選器但仍需要輕鬆篩選數據而不使用VBA或編輯公式。

局限性：切片器不支持與儲存格值的原生連接。如果您的工作流程需要由儲存格輸入控制的動態篩選，切片器應該被視為補充工具而不是VBA或基於公式方法的替代品。

此外，如果您的數據存儲在Excel表格（不是樞紐分析表）中，您仍然可以通過選擇表格並前往表格設計選項卡>插入切片器來使用切片器。

故障排除：如果切片器似乎沒有篩選樞紐分析表，請檢查（在切片器或分析選項卡下）報告連接以確保它已正確連接到預期的樞紐分析表。

上述每種方法都服務於不同的目的：VBA允許直接的儲存格鏈接篩選，公式提供動態結果顯示，切片器則提供用戶友好的圖形篩選。選擇最符合您自動化、靈活性和易用性需求的方法。傳統的樞紐分析表下拉篩選器仍然作為基本備選方案可用。

最佳 Office 生產力工具

🤖	Kutools AI 助手：以智能執行為基礎，革新數據分析｜生成程式碼｜創建自訂公式｜分析數據並生成圖表｜調用 Kutools 增強函數…
	熱門功能：查找、選取項目的背景色或標記重複值｜刪除空行｜合併列或單元格且不遺失數據｜四捨五入（免公式）...
	高級 LOOKUP：多條件 VLookup｜多值 VLookup｜多表查找｜模糊查找...
	高級下拉列表：快速創建下拉列表｜依賴型下拉列表｜多選下拉列表...
	列管理器：添加指定數量的列｜移動列｜切換隱藏列的顯示狀態｜比較區域及列...
	精選功能：網格聚焦｜設計檢視｜增強編輯欄｜工作簿及工作表管理器｜資源庫（快捷文本）｜日期提取器｜合併資料｜加密/解密儲存格｜按列表發送電子郵件｜超級篩選｜特殊篩選（篩選粗體/傾斜/刪除線...）...
	15 大工具集：12 項文本工具（添加文本、刪除特定字符…）｜50+ 儀表類型（甘特圖等）｜40+ 實用公式（基於生日計算年齡等）｜19 項插入工具（插入QR码、根據路徑插入圖片等）｜12 項轉換工具（金額轉大寫、匯率轉換等）｜7 項合併與分割工具（高級合併行、分割儲存格等）｜...及更多