Excel VBA仅显示每个系列的最大值的标签

本文介绍了Excel VBA仅显示每个系列的最大值的标签的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我试图创建一个宏，它在图表中循环遍历，只显示最大/最小标签取决于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