Excel VBA仅显示每个系列的最大值的标签 [英] Excel VBA only show labels for each series' max value

问题描述

我试图创建一个宏，它在图表中循环遍历，只显示最大/最小标签取决于max / min值。

I am trying to create a macro which loops through series in a chart and only shows the maximum / minimum label dependent on what the max / min value is.

一些系列将只有负值，在这些情况下，我希望只显示最小数据点标签，反之亦然，对于0或更大的值。

Some of the series will only have negative values and in these cases I wish to only show the minimum datapoint label, and vice versa for series with 0 or greater values.

代码我到目前为止：

Sheets("Curve").ChartObjects("Chart 14").Activate
For Each serie In ActiveChart.SeriesCollection
        Dim pointCount As Integer
        Dim pointValues As Variant
        pointCount = serie.Points.Count
        pointValues = serie.Values
        For pointIndex = 1 To pointCount
          If pointValues(pointIndex) < 1000 Then
            serie.Points(pointIndex).HasDataLabel = True
          End If
        Next pointIndex
      Next serie
End Sub

当我手动输入阈值，但我想替换为'1000'与Max（系列）值，

Which works fine when I manually enter the threshold, but I want to replace the '1000' with Max(series) value instead, so that each series in the chart has only one label visible.

推荐答案

以下修改的例程包括MaxPoint，MaxPointIndex，MinPoint和MinPointIndex变量，在 For 循环中计算每个系列的点。

The following modified routine includes MaxPoint, MaxPointIndex, MinPoint, and MinPointIndex variables which are calculated in the For loop on each serie's points. It then sets the label for the maximum point if the series has only positive value and minimum point otherwise.

  Option Explicit

  Sub chart()
  Dim serie As Variant
  Dim Pointindex As Long
  Dim MaxPoint As Long
  Dim MaxPointIndex As Long
  Dim MinPoint As Long
  Dim MinPointIndex As Long
  Sheets("Curve").ChartObjects("Chart 14").Activate
  For Each serie In ActiveChart.SeriesCollection
      Dim pointCount As Integer
      Dim pointValues As Variant
      pointCount = serie.Points.Count
      pointValues = serie.Values
      MinPoint = 10000              'set to value greater than any point in any serie
      MaxPoint = 0
      For Pointindex = 1 To pointCount
          If pointValues(Pointindex) > MaxPoint Then
              MaxPoint = pointValues(Pointindex)
              MaxPointIndex = Pointindex
          ElseIf pointValues(Pointindex) < MinPoint Then
              MinPoint = pointValues(Pointindex)
              MinPointIndex = Pointindex
          End If
      Next Pointindex
      If MinPoint >= 0 Then
          serie.Points(MaxPointIndex).HasDataLabel = True
      Else
          serie.Points(MinPointIndex).HasDataLabel = True
      End If
  Next serie
  End Sub

这篇关于Excel VBA仅显示每个系列的最大值的标签的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！