首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Excel图表学习61: 编写一个给多个数据系列添加趋势线的加载宏

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

作者头像
fanjy
发布2019-12-24 16:48:00
1.6K0
发布2019-12-24 16:48:00
举报
文章被收录于专栏:完美Excel完美Excel

在《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 If
End Sub

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

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

添加趋势线

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

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

图2

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

图3

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

图4

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

本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2019-12-14,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 完美Excel 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档