如何在VBA生成的Excel图表中仅显示系列最后一个数据点数值？

用户提供的VBA代码如下：

Sub ProductionDistribution()
Dim wb As Workbook
Dim ws As Worksheet
Dim xData As Range
Dim yData As Range
Dim xData2 As Range
Dim yData2 As Range
Dim xData3 As Range
Dim yData3 As Range
Dim xData4 As Range
Dim yData4 As Range


Set xData = Range("Dates")
Set yData = Range("CumFeed")

Set xData2 = Range("Dates")
Set yData2 = Range("CumTails")

Set xData3 = Range("Dates")
Set yData3 = Range("CumSinter")

Set xData4 = Range("Dates")
Set yData4 = Range("CumSuper")

Set wb = ActiveWorkbook
Set ws = Sheets("Report")

Dim chtObj As ChartObject
Set chtObj = ws.ChartObjects.Add(Width:=249, Height:=150.7, Left:=Range("G6").Left, Top:=Range("G6").Top)

Dim cht As Chart
Set cht = chtObj.Chart
cht.HasLegend = True
cht.Legend.Position = xlLegendPositionCorner
cht.ChartStyle = 7
End Sub

解决方案 #

没有现成的图表样式能直接实现仅显示最后一个数据点的(x,y)标签，但可以通过VBA代码逐个配置数据系列的最后一个数据点：

1. 先把所有数据系列添加到图表中（原代码缺少这一步）
2. 遍历每个数据系列，关闭所有数据点的标签，再单独启用最后一个数据点的标签，并设置标签内容同时包含x轴日期和y轴数值。

修改后的完整代码如下：

Sub ProductionDistribution()
Dim wb As Workbook
Dim ws As Worksheet
Dim xData As Range
Dim yData As Range
Dim xData2 As Range
Dim yData2 As Range
Dim xData3 As Range
Dim yData3 As Range
Dim xData4 As Range
Dim yData4 As Range

Set xData = Range("Dates")
Set yData = Range("CumFeed")
Set xData2 = Range("Dates")
Set yData2 = Range("CumTails")
Set xData3 = Range("Dates")
Set yData3 = Range("CumSinter")
Set xData4 = Range("Dates")
Set yData4 = Range("CumSuper")

Set wb = ActiveWorkbook
Set ws = Sheets("Report")

Dim chtObj As ChartObject
Set chtObj = ws.ChartObjects.Add(Width:=249, Height:=150.7, Left:=Range("G6").Left, Top:=Range("G6").Top)

Dim cht As Chart
Set cht = chtObj.Chart
cht.HasLegend = True
cht.Legend.Position = xlLegendPositionCorner
cht.ChartStyle = 7

' 添加所有数据系列到图表
With cht.SeriesCollection.NewSeries
    .Name = "CumFeed"
    .XValues = xData
    .Values = yData
End With
With cht.SeriesCollection.NewSeries
    .Name = "CumTails"
    .XValues = xData2
    .Values = yData2
End With
With cht.SeriesCollection.NewSeries
    .Name = "CumSinter"
    .XValues = xData3
    .Values = yData3
End With
With cht.SeriesCollection.NewSeries
    .Name = "CumSuper"
    .XValues = xData4
    .Values = yData4
End With

' 遍历每个系列，设置仅最后一个数据点显示(x,y)标签
Dim srs As Series
Dim lastPoint As Point
For Each srs In cht.SeriesCollection
    ' 关闭当前系列所有数据点的标签
    srs.HasDataLabels = False
    ' 获取最后一个数据点
    Set lastPoint = srs.Points(srs.Points.Count)
    ' 开启最后一个点的标签
    lastPoint.HasDataLabel = True
    ' 设置标签显示x和y值
    With lastPoint.DataLabel
        .ShowValue = True ' 显示y值
        .ShowCategoryName = True ' 显示x轴日期值
        .Separator = " | " ' 设置x和y值的分隔符
        .ShowSeriesName = False ' 隐藏系列名称
    End With
Next srs
End Sub

关键代码说明 #

• srs.Points(srs.Points.Count)：获取当前数据系列的最后一个数据点
• lastPoint.HasDataLabel = True：单独启用该点的数据标签
• DataLabel属性配置：通过ShowCategoryName显示x轴日期，ShowValue显示y值，用Separator设置两者的分隔符

内容的提问来源于stack exchange，提问作者jesuslovesyou