如何在Excel中使用切片器链接表和数据透视表? [英] How to link a Table and a Pivot Table using Slicers in Excel?

问题描述

好吧.我在Excel的工作表上有一个名为"ALL_INFO"的表，在其他工作表中做了一个数据透视表，其名称为"PIVOT_INFO".而且我想知道链接表的方式，以及使用切片器过滤数据的数据透视表的方式，并将其反映在两个表中.

Well. I have a table named "ALL_INFO" on a Sheet in Excel and I made a Pivot table in other sheet, its name is "PIVOT_INFO". And I would like to know the way to link a table, and a pivot table using Slicers to filter information and it be reflected in both tables.

有人知道我该怎么做吗?

Anybody knows how I can do it?

先谢谢您.

推荐答案

为数据透视表创建切片器，为表创建一个切片器.确保PT Slicer可见，并且Table Slicer隐藏在用户看不到的地方.然后将此代码放在与您的PT所在的工作表相对应的 Sheet模块中:

Create a Slicer for the PivotTable, and one for the Table. Make sure the PT Slicer is visible, and the Table Slicer is hidden somewhere where users can't see it. Then put this code in the Sheet Module corresponding to the worksheet where your PT is:

Option Explicit

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim sLastUndoStackItem As String
Dim sc_Pivot As SlicerCache
Dim sc_Table As SlicerCache
Dim si_Pivot As SlicerItem
Dim si_Table As SlicerItem

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

If Target.Name = "PivotTable1" Then '<= Change name as appropriate
    On Error Resume Next 'in case the undo stack has been wiped or doesn't exist
    sLastUndoStackItem = Application.CommandBars(14).FindControl(ID:=128).List(1) 'Standard Commandbar, undo stack
    'The above line doesn't seem to work in my version of O365 so we'll use the English language backup
    If sLastUndoStackItem = "" Then sLastUndoStackItem = Application.CommandBars("Standard").Controls("&Undo").List(1)
    On Error GoTo 0

    If sLastUndoStackItem = "Filter" Or sLastUndoStackItem = "Slicer Operation" Then

        Set sc_Pivot = ActiveWorkbook.SlicerCaches("Slicer_Data") '<= Change name as appropriate
        Set sc_Table = ActiveWorkbook.SlicerCaches("Slicer_Data1") '<= Change name as appropriate
        sc_Table.ClearAllFilters

        On Error Resume Next 'In case items differ between Table and PT
        For Each si_Pivot In sc_Pivot.SlicerItems
            With si_Pivot
                sc_Table.SlicerItems(.Name).Selected = .Selected
            End With
        Next si_Pivot
    End If
End If

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

在使用主"切片器之前，情况如下:

Here's how things look before I use the "Master" slicer:

...这是使用"Master"切片器后的外观:

...and here's how things look after I use the "Master" slicer:

请注意，过滤表会隐藏整个工作表中的行.因此，您不想将任何您希望始终保持可见的表放在表的旁边.

Note that filtering a Table hides rows in the entire worksheet. So you don't want to put anything that you want to remain visible at all times alongside the Table.

这篇关于如何在Excel中使用切片器链接表和数据透视表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！