Excel 2013 VBA数据透视表仅选择带有领带的前5个项目 [英] Excel 2013 VBA Pivot Table Select Only Top 5 Items With Ties

问题描述

我正在尝试找到一种使用Excel 2013 VBA的方法来仅选择数据透视表中的前5个项目.我尝试了以下几行代码，以尝试仅显示前5个项目:

I am trying to find a way to use Excel 2013 VBA to only select the Top 5 items in a pivot table. I have tried the following lines of code in an attempt to show only the Top 5 items:

Dim c
dim i as long

Worksheets("sheet1").PivotTables(1).PivotFields ("field1")
ActiveSheet.PivotTables(1).PivotFields("field1").CurrentPage = "(All)"
With ActiveSheet.PivotTables(1).PivotFields("field1")
c = 5
For i = .PivotItems.Count To 1 Step -1
If (c > 0) Then
.PivotItems(i).Visible = True
Else
.PivotItems(i).Visible = False
End If
c = c - 1
Next
End With

这是宏录制器提供给我的代码，但是不起作用:

This is the code that the macro recorder gives me and does not work:

ActiveSheet.PivotTables("PivotTable5").PivotFilters. _
.PivotFields("field1") Add2 Type:=xlTopCount, _        DataField:=ActiveSheet.PivotTables("PivotTable5"). _
    PivotFields("fied1"), Value1:=5

如果没有在前5名列表中的任何值没有价值纽带，则下面的代码可以正常工作.有了我们的数据集，我们将始终在前5名列表中保持联系.

The code below works fine if there are no value ties for any of the values that are not in the Top 5 list. With our data set we are always going to ties in our Top 5 list.

With Workbooks(cFileName).Worksheets("sheet1")
.PivotTables(1).PivotFields("Field1").PivotFilters.Add2     
xlTopCount, .PivotTables(1).PivotFields("Field1"), 5
End With

数据透视表列表将显示某些项目已被选中，但是没有数据与这些值相关联.基于这些数据透视表的图表为空白，下面的代码仅按照原帖的意图选择该字段中的底部5个值.

The pivot table list will show some items as being checked, but no data is associated with those values. The charts which are based on these pivot tables are blank and the following code selects only the bottom 5 values in the field as it was intended to do in it's original post.

With workbook.Worksheets("sheet1").PivotTables("PivotTable2").PivotFields
("Count of Description")

For Each WS In ActiveWorkbook.Worksheets
For Each pvt In WS.PivotTables

c = 5

For i = .PivotItems.Count To 1 Step -1
If (c > 0) Then
.PivotItems(i).Visible = True
Else
.PivotItems(i).Visible = False
End If
c = c - 1
Next
Next
Next

End With

推荐答案

我终于弄明白了这一点.万一其他人想知道的话，最好的方法是在您录制一个宏:

I finally figured this one out. The best way to do it just in case anyone else is wondering, is to record a macro while you:

1. 选择要在其中开始查看数据的单元格.在我的情况下是"B10".对于其他所有人，这将取决于透视表数据的位置.
2. 按SHIFT + END +向下箭头键可向下移至使用范围的底部.
3. 选择所有要隐藏的行.
4. 右键单击要隐藏的值.
5. 从列表中选择过滤器".
6. 单击隐藏所选项目".

下面是我将来会引用的代码.

Below is the code that I will refer to in the future.

Application.Goto reference:=Workbooks("File").Sheets("Sheet1").Range("B10")
If IsEmpty(Range("B11").value) = False Then
Range("B10").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete
Else
End If

这篇关于Excel 2013 VBA数据透视表仅选择带有领带的前5个项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！