Excel图表学习61：编写一个给多个数据系列添加趋势线的加载宏

在《Excel图表学习60：给多个数据系列添加趋势线》中，我们手工给多个散点图系列添加了一条趋势线，如下图1所示。

图1

在图表中，最开始有3个数据系列，其公式分别为：

=SERIES(Sheet1!$C$2,Sheet1!$B$3:$B$11,Sheet1!$C$3:$C$11,1)

=SERIES(Sheet1!$E$2,Sheet1!$D$3:$D$11,Sheet1!$E$3:$E$11,2)

=SERIES(Sheet1!$G$2,Sheet1!$F$3:$F$11,Sheet1!$G$3:$G$11,3)

注意，图表的系列公式一般有4个参数：

=SERIES(系列名称, X值, Y值, 绘图顺序值)

后来，我们添加了一个名为“趋势线”的新系列，合并了原来的3个系列的X值和Y值，其公式为：

=SERIES("趋势线",

(Sheet1!$B$3:$B$11,Sheet1!$D$3:$D$11,Sheet1!$F$3:$F$11),

(Sheet1!$C$3:$C$11,Sheet1!$E$3:$E$11,Sheet1!$G$3:$G$11),

组成X值和Y值的多个单元格区域被包含在括号中。

下面，我们使用VBA代码来自动添加趋势线。

代码执行的操作是对图表中的系列进行计数，依次读取每个系列公式，拆分其参数，然后将单独的X和Y值连接为组合的X和Y值。接着，代码将应用系列公式的参数添加新系列、隐藏标记并添加趋势线。

代码如下：

代码语言：javascript复制

Sub ComputeMultipleTrendline()
  If Not ActiveChart Is Nothing Then
    With ActiveChart
      Dim ixSeries As Long
      For ixSeries = 1 To .SeriesCollection.Count
        Dim SeriesFormula As String
       SeriesFormula = ActiveChart.SeriesCollection(ixSeries).Formula
       SeriesFormula = Mid$(SeriesFormula, InStr(SeriesFormula, "(")  1)
       SeriesFormula = Left$(SeriesFormula, Len(SeriesFormula) - 1)
 
        Dim SeriesArgs As Variant
       SeriesArgs = Split(SeriesFormula, ",")
 
        Dim XAddress As String, YAddress As String
       XAddress = XAddress & SeriesArgs(LBound(SeriesArgs)   1) &","
       YAddress = YAddress & SeriesArgs(LBound(SeriesArgs)   2) &","
      Next
 
      XAddress= "=(" & Left$(XAddress, Len(XAddress) - 1) & ")"
      YAddress= "=(" & Left$(YAddress, Len(YAddress) - 1) & ")"
 
      With ActiveChart.SeriesCollection.NewSeries
        .Name ="趋势线"
       .XValues = XAddress
        .Values= YAddress
       .Format.Line.Visible = False
       .MarkerStyle = xlMarkerStyleNone
        With.Trendlines.Add.Format.Line
         .DashStyle = msoLineSolid
         .ForeColor.ObjectThemeColor = msoThemeColorText1
          .ForeColor.Brightness= 0
        EndWith
      End With
    End With
  End If
End Sub

在工作表中选取图表，运行上述代码，可以看到图表中自动添加了一条趋势线。

以上面的代码为基础，创建可以为多个数据系列添加趋势线的加载宏。可以在完美Excel微信公众号底部发送消息：

添加趋势线

下载该加载宏使用和研究。

安装该加载宏后，Excel功能会新增一个名为“Multi Trendline”的选项卡，带有一个名为“Multi ScatterTrendline”的命令按钮，如下图2所示。