如何在Excel中將樞紐分析表篩選器連結到特定儲存格？

在Excel中，您可能經常需要建立互動式報告，讓樞紐分析表的篩選器反映出特定儲存格中的值。這樣可以讓使用者在一個地方選擇或輸入篩選值，並根據該輸入動態更新樞紐分析表。這種方法在設計儀表板或用於數據探索的自訂篩選界面時特別有用。

本文提供了幾種實用的解決方案，包括基於VBA的方法和其它內建的Excel方法，幫助您將樞紐分析表篩選器連結到儲存格值，或實現類似的動態報告效果。

• 使用VBA代碼將樞紐分析表篩選器連結到特定儲存格
• Excel公式 - 使用公式（例如GETPIVOTDATA）結合切片器或報表篩選器引用
• 其他內建的Excel方法 - 連接樞紐分析表切片器與儀表板以進行互動式篩選

使用VBA代碼將樞紐分析表篩選器連結到特定儲存格

如果您需要在儲存格和樞紐分析表篩選器之間建立最直接的連結——即更改儲存格的值會自動更新樞紐分析表篩選器——VBA提供了一種實際的方法來實現這一點。此方法適用於交互式儀表板或報告，使用者希望從單一儲存格快速控制數據切片的情況。

要使此技術生效，您的樞紐分析表必須包含一個篩選字段。篩選字段的名稱對於正確配置VBA代碼至關重要。

考慮以下範例：樞紐分析表有一個名為類別(Category)的篩選字段，具有兩個篩選值：“費用(Expenses)” 和 “銷售(Sales)”。通過將儲存格與樞紐分析表篩選器連結，您可以通過在所選儲存格中輸入“費用”或“銷售”來控制顯示的數據。

要實現這個功能：

• 選擇要用作篩選控制器的儲存格（例如，H6），並提前輸入其中一個篩選值。確保該值與樞紐分析表篩選字段中的可用值完全匹配。
• 前往包含樞紐分析表的工作表。右鍵點擊工作表標籤，然後從菜單中選擇檢視程式碼(View Code)。這將打開Visual Basic for Applications窗口。

在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")) 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

注意：

粘貼代碼後，按下 Alt + Q 關閉VBA編輯器窗口並返回Excel。

現在，樞紐分析表的篩選狀態由H6儲存格的內容控制。只需更改H6儲存格中的值（如“銷售”或“費用”），樞紐分析表的顯示就會立即更新。如果遇到任何問題，請仔細檢查參考儲存格值是否與樞紐分析表中的篩選值完全匹配，以及代碼中的名稱是否正確分配。

每當您修改儲存格的內容時，樞紐分析表都會相應地刷新其篩選數據。

提示和故障排除：如果儲存格中的篩選字段值與可用項目不完全匹配（包括大小寫和空格），代碼可能無法按預期應用篩選器。始終驗證VBA代碼中的字段和表格名稱是否拼寫正確。如果您想在多個樞紐分析表中使用此設置，可以進一步適配代碼或使用循環擴展它。

使用 Kutools AI 解鎖 Excel 的魔法

• 智能執行：執行單元格操作、分析數據並創建圖表——所有這些都由簡單的指令驅動。
• 自訂公式：生成量身定制的公式，簡化您的工作流程。
• VBA 編碼：輕鬆編寫和實現 VBA 代碼。
• 公式解釋：輕鬆理解複雜的公式。
• 文本翻譯：打破電子表格中的語言障礙。

通過人工智能工具增強您的 Excel 能力。立即下載，體驗前所未有的效率！

Excel公式 - 使用公式（例如GETPIVOTDATA）結合切片器或報表篩選器引用

雖然Excel沒有提供純粹原生的公式方法來將樞紐分析表的篩選器直接綁定到儲存格，但您可以使用GETPIVOTDATA等公式結合切片器或報表篩選器來實現動態報告和顯示相關值。當您希望構建一個摘要值能根據篩選選擇或其他儲存格輸入即時更新的儀表板時，此解決方案非常有用，使數據分析更具互動性。

適用場景包括動態報告面板、儀表板或比較摘要，在這些場景中，您希望顯示的結果遵循切片器選擇，或反映與儲存格內容相關的數據。主要優勢是此方法能很好地顯示更新的摘要數據。然而，樞紐分析表的實際篩選狀態不能僅靠儲存格公式程序化設置。

示例：根據儲存格值顯示樞紐分析表摘要

假設您有一個樞紐分析表，按類別（Category）（例如，“銷售”，“費用”）總結銷售情況。您可以使用GETPIVOTDATA提取指定在儲存格中的類別的相關值。

1. 假設H6包含您希望顯示的類別（例如，“銷售”）。將以下公式放入您的摘要儲存格（例如，I6）：

=GETPIVOTDATA("Sum of Amount",$B$4,"Category",H6)

2. 在I6中輸入公式後，按下Enter。現在，每當您將H6更改为有效的類別（如“費用”或“銷售”）時，I6將立即更新以顯示該類別的總計，根據當前樞紐分析表的情況。

提示：儘管此方法不會改變樞紐分析表本身的篩選條件，但它有效地顯示了如同被儲存格篩選過的結果數據，提供了一個與目標儲存格輸入相連的動態顯示。您還可以使用此方法來驅動圖表、摘要表格或儀表板。

故障排除：如果公式返回#REF!或#VALUE!錯誤，請檢查您的儲存格引用是否正確，輸入的類別是否存在於您的樞紐分析表中，以及字段/求和名稱是否完全匹配。

其他內建的Excel方法 - 連接樞紐分析表切片器與儀表板以進行互動式篩選

Excel的切片器和報表篩選器工具提供了用戶友好的內建選項，用於無需編寫VBA代碼的互動式篩選。您可以使用這些方法來實現類似儀表板的效果，將多個樞紐分析表或顯示連接到一個或多個切片器。

一種常見的方法是插入一個與樞紐分析表字段（例如，“類別”）相連的切片器。使用者只需在切片器中點擊所需的項目，樞紐分析表就會相應更新。如果您有多個基於相同數據源的樞紐分析表，可以將單一切片器連接到所有表格以進行同步篩選，使您的報告界面更加直觀且一致。

要創建切片器並將其連接：

• 點擊您的樞紐分析表並前往樞紐分析表分析(PivotTable Analyze)（或選項卡，具體取決於Excel版本）> 插入切片器(Insert Slicer)。
• 勾選所需的字段（例如，類別）並點擊確定。切片器會出現在工作表上，允許用戶進行視覺化篩選。
• 要將一個切片器連接到多個樞紐分析表，右鍵點擊切片器，選擇 報表連接(Report Connections) （或 樞紐分析表連接(Pivottable Connections)），並勾選您希望同步的所有樞紐分析表。
  這對於各種可視化共同響應用戶篩選的儀表板場景特別強大。

優勢：非常易於使用，適合大多數互動式篩選需求，不需要宏或自訂代碼。非常適合儀表板或共享報告，簡潔性和可靠性至關重要。限制是絕對的儲存格到篩選器自動化（儲存格到篩選器綁定）不是原生支持的——直接值到篩選器分配需要VBA或外部工具。

故障排除：如果切片器無法連接到多個樞紐分析表，請確保所有表格都是基於相同的緩存/數據源建立的。只有在表格兼容的情況下才會出現報表連接選項。

總結建議：在選擇將樞紐分析表篩選器連結到儲存格值或建立互動式儀表板的最佳方法時，請考慮您所需的自動化水平、Excel版本限制以及您的環境是否允許使用VBA/宏。對於基本需求，切片器和公式（GETPIVOTDATA）提供了快速、穩健的結果。對於高級自動化，VBA解決方案提供了更大的控制能力。始終驗證字段名稱和篩選項目的一致性，以確保準確的結果。如果出現錯誤，請檢查儲存格輸入值，並確保代碼、公式和數據集之間的所有名稱完全匹配。

相關文章：

• 如何在Excel中將多個工作表合併到一個樞紐分析表中？
• 如何在Excel中從文本文件創建樞紐分析表？
• 如何根據Excel中的特定儲存格值篩選樞紐分析表？