数据透视项目自定义排序（行字段）

I was thinking for this for a while now for the correct loop to be use here.

我正在考虑这个问题一段时间才能在这里使用正确的循环。

I'm trying to move/sort those fruits in the bottom of the pivot, however they should be in that exact particular order in the bottom part (fruit1, fruit2,..., apples, oranges, grapes).

我试图在枢轴底部移动/分类那些水果，但是它们应该在底部（fruit1，fruit2，...，苹果，橙子，葡萄）中按照特定的顺序排列。

However, there's a chance that one of those in bottom 3 fruits may not present in the data so i can't subtract its position from the PivotItems.Count.

但是，有可能底部3个水果中的一个可能不会出现在数据中，因此我无法从PivotItems.Count中减去其位置。

With pvt

   .PivotField("fruits").Orientation = xlRowField
   .RowAxisLayout xlTabularRow

   For Each pi In .PivotFields("fruits").PivotItems
      Select Case LCase(pi.Name)
         Case "apples", "oranges", "grapes"
            .PivotFields("fruits").PivotItems(pi.Name).Position = .PivotFields("fruits").PivotItems.Count
      End Select
   Next pi

End with

1 个解决方案

#1

You will need to adjust this to suit your needs but the following will order your list. It assumes you data is laid out as follows in sheet2:

您需要根据自己的需要进行调整，但以下内容将为您排序。它假设您在sheet2中按如下方式布置数据：

And it creates a pivot in Sheet7

它在Sheet7中创建了一个支点

Option Explicit
Public Sub CreateOrderedFruitPivot()

    Dim pc As PivotCache, pt As PivotTable, pi As PivotItem
    Dim varSubsOff, varItemList, varItem
    varSubsOff = Array(False, False, False, False, False, False, False, False, False, False, False, False)

    With ActiveSheet 'better to used named Sheet

        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
                                          "'Sheet2'!R1C1:R4C2", Version:=6).CreatePivotTable TableDestination:="Sheet7!R3C1", _
                                          TableName:="PivotTable2", DefaultVersion:=6

        Set pt = ThisWorkbook.Worksheets("Sheet7").PivotTables("PivotTable2")

        With pt

            .AddDataField .PivotFields("fruits"), "Count of fruits", xlCount

            With .PivotFields("fruits")
                .Orientation = xlRowField
                .Position = 1
            End With
            ActiveWorkbook.ShowPivotTableFieldList = False
            On Error Resume Next

            With .PivotFields("fruits")
                .PivotItems("(blank)").Visible = False
                varItemList = Array("Apples", "Oranges", "Grapes")

                For Each varItem In varItemList
                    Set pi = .PivotItems(varItem)
                    pi.Visible = True
                    pi.Position = .VisibleItems.Count
                    Set pi = Nothing
                Next varItem
                On Error GoTo 0
            End With

            pt.RowAxisLayout xlTabularRow
        End With

    End With

End Sub

You will need to amend to your sheets and ranges accordingly (also check field names) but the part you are really interested in is the following:

您需要相应地修改工作表和范围（也请检查字段名称），但您真正感兴趣的部分如下：

 On Error Resume Next
 With .PivotFields("fruits")
                .PivotItems("(blank)").Visible = False
                varItemList = Array("Apples", "Oranges", "Grapes")

                For Each varItem In varItemList
                    Set pi = .PivotItems(varItem)
                    pi.Visible = True
                    pi.Position = .VisibleItems.Count
                    Set pi = Nothing
                Next varItem
                On Error GoTo 0
            End With

Here you are looping an array in the order you want to see items and adding them and the error handling deals with the item not being present.

在这里，您按照要查看项目的顺序循环数组并添加它们，错误处理处理项目不存在。

Credits go to Rory here: Sorting pivot items as i used his solution as a framework for approaching your problem.

积分转到Rory：我使用他的解决方案作为解决问题的框架来对枢轴项进行排序。

#1