excel透视表无添加字段_在Excel数据透视表中添加过滤器标记

excel透视表无添加字段

If you're using Excel 2007 or Excel 2010, you can quickly see which fields in a pivot table have filters applied. For example, in the screenshot below, the ItemSold field has been filtered.

如果您使用的是Excel 2007或Excel 2010，则可以快速查看数据透视表中的哪些字段已应用过滤器。 例如，在下面的屏幕截图中，ItemSold字段已被过滤。

The arrow drop down has changed to a filter symbol, with a tiny arrow.

箭头下拉列表已更改为带有小箭头的过滤器符号。

In Excel 2003 though, there's no indicator that a field has been filtered. Here's the same filtered pivot table in Excel 2003, and the drop down arrows look the same in both of the fields.

但是，在Excel 2003中，没有指示器表明已过滤字段。 这是Excel 2003中相同的过滤后的数据透视表，并且下拉箭头在两个字段中看起来都相同。

There's no marker to show if either field has been filtered. You'd have to click each arrow, to see if any of the check marks have been removed from the pivot items. Who has time for that?

没有标记可显示是否已过滤任何一个字段。 您必须单击每个箭头，以查看是否已从数据透视表项中删除了所有复选标记。 谁有时间？

创建自己的过滤器标记 (Create Your Own Filter Markers)

Several of my clients are still using Excel 2003, and maybe you use it too. If so, you'll appreciate this sample file from AlexJ, which adds a bright blue marker above each filtered field.

我的几个客户仍在使用Excel 2003，也许您也使用它。 如果是这样，您会欣赏来自AlexJ的此样本文件，该文件在每个过滤的字段上方添加了一个亮蓝色标记。

That makes it easy to keep track of what's been changed in the pivot table, and prevents you from overlooking the filters.

这样可以轻松跟踪数据透视表中的更改，并防止您忽略过滤器。

To create the markers, Alex wrote a user defined function, named pvtFilterID.

为了创建标记，Alex编写了一个用户定义的函数，名为pvtFilterID。

In the screenshot below, you can see the pvtFilterID formula in cell D5, which refers to the ItemSold field heading in cell D7.

在下面的屏幕快照中，您可以在单元格D5中看到pvtFilterID公式，该公式引用单元格D7中的ItemSold字段标题。

=pvtFilterID(D$7)

= pvtFilterID(D $ 7)

The formula is used in cells B5:D5, above the row fields, and that range could be adjusted if your pivot table has a different number of row fields.

该公式用于行字段上方的单元格B5：D5中，并且如果您的数据透视表具有不同数量的行字段，则可以调整该范围。

蓝色箭头标记 (The Blue Arrow Marker)

Cell D1 is named Symbol.Filter, and it contains the blue arrow symbol that's used as a marker. If you changed the symbol there, the new symbol would be used as the filter marker.

单元格D1命名为Symbol.Filter，它包含用作标记的蓝色箭头符号。 如果在此处更改符号，则新符号将用作过滤器标记。

In cell G5 there's another formula, that shows a message if any of the pivot table fields are filtered.

在单元格G5中，还有另一个公式，该公式显示是否过滤了任何数据透视表字段的消息。

=IF(COUNTIF($B$5:$D$5,Symbol.Filter)>0, "Pivot Filter On" & Symbol.Filter,"")

= IF(COUNTIF($ B $ 5：$ D $ 5，Symbol.Filter)> 0，“ Pivot Filter On”＆Symbol.Filter，“”)

This formula checks the cells above the pivot table, and shows the message if any of those cells contain the marker symbol.

此公式检查数据透视表上方的单元格，并显示消息，如果其中任何一个单元包含标记符号。

也可以与切片机一起使用 (Works With Slicers Too)

Even though Alex wrote this code for Excel 2003 pivot tables, it works in Excel 2007 and Excel 2010 too.

即使Alex为Excel 2003数据透视表编写了此代码，它也可以在Excel 2007和Excel 2010中使用。

In the screenshot below, you can see and Excel 2010 pivot table with slicers, and the filter markers highlight the row fields where filters have been applied.

在下面的屏幕截图中，您可以看到带有切片器的Excel 2010数据透视表，并且过滤器标记突出显示了已应用过滤器的行字段。

The filter symbol is on the field drop downs too, and the bright blue markers are extra insurance that users notice which fields are filtered.

过滤器符号也在字段下拉列表中，并且明亮的蓝色标记是用户可以注意到哪些字段已过滤的额外保证。

过滤器标记功能代码 (The Filter Marker Function Code)

Here's Alex's code for the pvtFilterID function.

这是pvtFilterID函数的Alex代码。

Function pvtFilterID(rng As Range) As String  'rng As Range)
On Error GoTo XIT ' -not in pivot
If Not rng.Parent Is ActiveSheet Then GoTo XIT
If rng.Cells.Count > 1 Then
    MsgBox "Error: pvtFilterID range selection"
    GoTo XIT
End If
If rng.PivotField.HiddenItems.Count > 0 Then
    pvtFilterID = [Symbol.Filter]
End If
XIT:
End Function

清除数据透视表过滤器 (Clear the Pivot Table Filters)

Another nice feature that was added to Excel 2007 pivot tables is the Clear All Filters command. Alex's workbook contains a button that runs code to remove all the filters from a pivot table.

Excel 2007数据透视表中添加的另一个不错的功能是“清除所有筛选器”命令。 Alex的工作簿包含一个按钮，该按钮运行代码以从数据透视表中删除所有过滤器。

Here's the code for the ClearPivotFilters procedure.

这是ClearPivotFilters过程的代码。

Sub ClearPivotFilters(ws As Worksheet)
    Dim pvt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim lSort As Long
On Error Resume Next
Set pvt = ws.PivotTables("PivotTable1")
For Each pf In pvt.VisibleFields
    If pf.HiddenItems.Count > 0 Then
      lSort = pf.AutoSortOrder
      pf.AutoSort xlManual, pf.SourceName
      For Each pi In pf.PivotItems
        pi.Visible = True
      Next pi
    End If
    pf.AutoSort lSort, pf.SourceName
Next pf
Set pi = Nothing
Set pf = Nothing
Set pvt = Nothing
End Sub

The button code passes the worksheet name to the procedure.

按钮代码将工作表名称传递给过程。

Private Sub cmdClearPvtFilters_Click()
    Call ClearPivotFilters(Me)
End Sub

下载样本文件 (Download the Sample File)

To test the pivot table filter markers, and see the VBA code, you can download Alex's sample file from the Contextures website.

若要测试数据透视表过滤器标记并查看VBA代码，可以从Contextures网站下载Alex的示例文件。

On the AlexJ Sample Files page, go to the Pivot Tables section, and look for: PT0000 - Pivot Table Filter Markers ___________

在“ AlexJ示例文件”页面上，转到“ 数据透视表”部分 ，然后查找： PT0000-数据透视表过滤器标记 ___________

翻译自: https://contexturesblog.com/archives/2010/11/19/add-filter-markers-in-excel-pivot-table/

excel透视表无添加字段