excel图表怎么用箭头线连接两个XY散点 _知识经验

本文介绍如何使用箭头线更加可视化地显示变化的方向，如图1所示。

文章插图
图1
在Excel 2007及以后的版本中，通过设置系列3的箭头末端格式，很容易实现图1所示的效果。
下面，我们使用VBA来完成箭头线的绘制。
SubConnectTwoXYSeries()
Dim myCht As Chart
Dim mySrs1 As Series
Dim mySrs2 As Series
Dim Npts As Integer, Ipts As Integer
Dim myBuilder As FreeformBuilder
Dim myShape As Shape
Dim Ishp As Long
Dim Xnode1 As Double, Ynode1 As Double
Dim Xnode2 As Double, Ynode2 As Double
Dim Xmin As Double, Xmax As Double
Dim Ymin As Double, Ymax As Double
Dim Xleft As Double, Ytop As Double
Dim Xwidth As Double, Yheight As Double
‘ 必须先选取具有两个系列的图表
If ActiveChart Is Nothing Then
GoTo ExitSub
End If
If ActiveChart.SeriesCollection.Count < 2Then
GoTo ExitSub
End If
Set myCht = ActiveChart
Set mySrs1 = myCht.SeriesCollection(1)
Set mySrs2 = myCht.SeriesCollection(2)
Npts = mySrs1.Points.Count
‘ 两个系列必须具有相匹配数量的点
If mySrs2.Points.Count <> Npts Then
GoTo ExitSub
End If
‘ 移除任何原先的连接箭头线
For Ishp = myCht.Shapes.Count To 1 Step -1
If Left(myCht.Shapes(Ishp).Name, 12) =”ArrowSegment” Then
myCht.Shapes(Ishp).Delete
End If
Next
Xleft = myCht.PlotArea.InsideLeft
Xwidth = myCht.PlotArea.InsideWidth
Ytop = myCht.PlotArea.InsideTop
Yheight = myCht.PlotArea.InsideHeight
Xmin = myCht.Axes(1).MinimumScale
Xmax = myCht.Axes(1).MaximumScale
Ymin = myCht.Axes(2).MinimumScale
Ymax = myCht.Axes(2).MaximumScale
For Ipts = 1 To Npts
‘ 第1个数据点
Xnode1 = Xleft + (mySrs1.XValues(Ipts) -Xmin) * Xwidth / (Xmax – Xmin)
Ynode1 = Ytop + (Ymax -mySrs1.Values(Ipts)) * Yheight / (Ymax – Ymin)
‘ 第2个数据点
Xnode2 = Xleft + (mySrs2.XValues(Ipts) -Xmin) * Xwidth / (Xmax – Xmin)
Ynode2 = Ytop + (Ymax -mySrs2.Values(Ipts)) * Yheight / (Ymax – Ymin)
‘ 绘制连接线
Set myShape = myCht.Shapes.AddLine(Xnode1,Ynode1, Xnode2, Ynode2)
‘ 命名并格式形状为箭头线
With myShape
.Name = “ArrowSegment” &CStr(Ipts)
With .Line
‘ 在这里可以使用你喜欢的格式
.ForeColor.SchemeColor = 12‘ 蓝色
.EndArrowheadLength = msoArrowheadLong
.EndArrowheadWidth =msoArrowheadWidthMedium
.EndArrowheadStyle =msoArrowheadTriangle
End With
End With
Next
ExitSub:
EndSub
在运行程序前，先要选择图表。
运行程序后的结果如图2所示。