制作过气泡图或者散点图的小伙伴一定碰到过一个头疼的问题:如何才能给气泡图或者散点图添加标签?难道只能逐一添加? 下面介绍如何让你拥有快速添加气泡图标签的技能。
在VBA中插入一个模块,并添加以下宏命令:
Sub AttachLabelsToPoints()
' 定义变量.
Dim Counter As Integer, ChartName As String, xVals As String
' 宏运行时禁止屏幕刷新.
Application.ScreenUpdating = False
' 将第一系列的图表公式存储为xVals.
xVals = ActiveChart.SeriesCollection(1).Formula
' 抽取出气泡图中数据的区域.
xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, Mid(Left(xVals, InStr(xVals, "!") - 1), 9)))
xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1)
Do While Left(xVals, 1) = ","
xVals = Mid(xVals, 2)
Loop
' 将每个数据的标签添加到图表中.
For Counter = 1 To Range(xVals).Cells.Count
ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = True
ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
Next Counter
End Sub
然后运行这个命令即可。
不过要注意,要运行成功对于原数据的排列有所要求。如同上图所示,原数据应该按照【标签】|【x轴数据】|【y轴数据】,这样的列顺序进行排列。