图表添加特定横轴数据_Excel图表学习61： 编写一个给多个数据系列添加趋势线的加载宏...

学习Excel技术，关注微信公众号：

excelperfect

在《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),

  4)

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

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

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

代码如下：

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 IfEnd Sub

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

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

添加趋势线

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

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

图2

在工作表中选择图表，运行“Multi Scatter Trendline”命令，会弹出一个对话框，可以选取要添加趋势线的图表数据系列前的复选框，如下图3所示。

图3

选择要添加趋势线的数据系列后，单击“ok”按钮，图表中会自动绘制一条趋势线，如下图4所示。

图4

注：本文学习整理自peltiertech.com，有兴趣的朋友可以直接到该网站上学习原文。