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

问题描述

我正在尝试创建一个在图表中循环的宏，只显示最大值/最小值。一些系列只会有负值，在这些情况下，我只希望显示最小的数据点标签，反之亦然。对于0或更高的数值，系列中的数据将被显示。

代码我到目前为止：

 表格（曲线）ChartObjects（Chart 14）。激活
对于每个系列在ActiveChart.SeriesCollection 
 Dim pointCount As Integer 
 Dim pointValues As Variant 
 pointCount = serie.Points.Count 
 pointValues = serie.Values 
对于pointIndex = 1 To pointCount 
如果pointValues（pointIndex）< 1000然后
 serie.Points（pointIndex）.HasDataLabel = True 
结束If 
下一个pointIndex 
下一个系列
 End Sub 
  

当我手动输入阈值时，哪个工作正常，但是我要用Max（系列）值替换'1000'，这样每个系列在图表中只有一个标签可见。

解决方案

我有一个替代方法，不需要VBA。它增加了一个额外的系列与一个不可见的数据点在最大，这一点有一个数据标签。如果数据发生变化，并且不同点是最大值，则它也会动态更改，而无需重新运行VBA程序。

对于图表中的每个系列，您需要使用一个范围与系列的Y值相同。

假设给定系列的原始Y值在D2：D10中，我们将使用G2： G10为我们额外的数据。在G2中输入
= IF（$ D2 = MAX（$ D $ 2：$ D $ 10），$ D2，NA（））
并将其填充到G10。修改此公式，如果所有内容都为负数，则可以查找最小值。

复制G2：G10并按住CTLR，同时为此选择X值系列。复制。选择图表，使用选择性粘贴，然后选择添加新数据作为新的系列，列，第一列中的分类。

选择添加的系列，最多有两个点），格式没有行，没有标记。将数据标签添加到本系列中。

对图表中的其他系列重复。

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.

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.

The code I have so far is:

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

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.

解决方案

I have an alternative approach, which does not require VBA. It adds an extra series with an invisible data point at the max and this point has a data label. It also changes dynamically if the data changes and a different point is maximum, without having to rerun the VBA procedure.

For each series in the chart you'll need to use a range the same size as the Y values of the series.

Assume the original Y values for a given series is in D2:D10, and we'll use G2:G10 for our extra data. In G2 enter =IF($D2=MAX($D$2:$D$10),$D2,NA()) and fill this down to G10. Modify this formula for the case where you might instead look for the minimum value if everything is negative.

Copy G2:G10 and hold CTLR while you select the X values for this series. Copy. Select the chart, use Paste Special, and select Add data as new Series, in Columns, Categories in First Column.

Select the added series, which is one point (unless there are two points at the maximum), format to have no lines and no markers. Add your data labels to this series.

Repeat for the other series in the chart.

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