☰

Excel 2013-使用 VBA 从数据透视图图例中删除 LegendEntry 对象

tag-icon • tag-icon microsoft-excel vba microsoft-excel-2013 charts pivot-chart
Excel 2013-使用 VBA 从数据透视图图例中删除 LegendEntry 对象

我有一个数据透视图，它通常显示 15 个系列中的 6-8 个，其中很多系列往往为零。因此，每次刷新时，它都有 15 个系列，而图例只需列出数字不为零的系列，以保持图表整洁且易于阅读。

我正在尝试使用虚拟专用网络删除系列全为零（最大值 = 0）的特定项目，遇到的问题比预期的要多：

  1. 我正在循环遍历图表中的每个项目全系列收藏品，逐一浏览系列列表。但是，由于图例是与系列分开的对象，你不能使用我的循环中的当前系列来引用它，因此需要一个循环计数器。

  2. 无法找到 LegendEntries 项的名称/值，因此您必须将 Series 与 Legend 进行匹配，而无法确认哪个 LegendEntries 项是哪个。

（显然以下功能在 Excel 2007 及更高版本中存在？）

  1. 看起来 FullSeriesCollection 对象在循环时会按 1 到 13 的顺序排列项目（合理），但是LegendEntries 对象按相反顺序排列项目，从13 到 1。

  2. LegendEntries 对象的索引从 0 开始，因此它的物品实际上是从 12 到 0 排序的。

  3. 当你从 LegendEntries 对象中删除项目时，它会重新编号。因此，当您删除项目 1 时，列表中的所有项目都会从 11 重新编号为 0。

  4. 每次数据透视表更新时我都需要刷新图表，重新捕获所有已删除的 LegendEntries 项（这些项现在可能为非零）。

鉴于这些复杂情况，我真的很难删除需要从图表中删除的特定项目。

如何有效地循环遍历数据透视表的项目并删除所有项目为零的相应 LegendEntries 系列？

答案1

从 Worksheet_PivotTableUpdate 事件开始

包含数据透视表的工作表需要以下代码，因此每当此特定情况发生时，它都会调用模块 1 中的新函数“RefreshAbsenceLabels”命名数据透视表已刷新：

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

If Target.Name = "This Specific Table" Then

    Module1.RefreshAbsenceLabels
    MsgBox "The PivotTable has been updated."

End If

End Sub

与正确的图表对话，并刷新其图例

Public Function RefreshAbsenceLabels()

Set DaysAbsent = ActiveWorkbook.Worksheets("Dashboard").ChartObjects("Department Absences")

With DaysAbsent.Chart

    If .HasLegend Then
        .HasLegend = False
    End If
    .HasLegend = True

设置计数器，并将其设置为系列计数减一

减一是为了说明 FullSeriesCollection 的编号为 1 到 X，而 LegendEntries 项的编号为 X-1 到 0

    Dim x As Integer
    x = .FullSeriesCollection.Count - 1

循环遍历系列，删除相应的 LegendEntries 项

计数器循环下降至零。LegendEntries 反向编号的优点是，您不必在每次删除内容时都抵消缺失的项目：任何重新编号的项目都已处理，它们的数字不再重要。

应用程序.工作表函数.Max从序列中找出最高值

    For Each ser In .FullSeriesCollection

        If Application.WorksheetFunction.Max(ser.Values) = 0 Then
            'MsgBox "Deleting" & ser.Name & " - " & x
            .Legend.LegendEntries(x).Delete
        End If

        x = x - 1
    Next
End With
End Function

相关内容