湖南龙网

  1. 首页 > 生活百科 > >

excel图表制作：给多个数据系列添加趋势线的加载宏

小知识生活百科

我们手工给多个散点图系列添加了一条趋势线 ， 如下图 1 所示 。

excel图表制作：给多个数据系列添加趋势线的加载宏

文章插图
图 1
【excel图表制作：给多个数据系列添加趋势线的加载宏】在图表中 ， 最开始有 3 个数据系列 ， 其公式分别为：
=系列（Sheet1！$ C $ 2 ， Sheet1！$ B $ 3：$ B $ 11 ， Sheet1！$ C $ 3：$ C $ 11,1）
=系列（Sheet1！$ E $ 2 ， Sheet1！$ D $ 3：$ D $ 11 ， Sheet1！$ E $ 3：$ E $ 11,2）
=系列（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 功能会添加一个称为“ Multi Trendline” 的选项卡 ， 带有一个称为“ Multi ScatterTrendline ”的命令按钮 ， 如下图 2 所示 。
excel图表制作：给多个数据系列添加趋势线的加载宏

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

推荐阅读


上一篇：使用Windows命令合并CSV文件

下一篇：excel图表制作：创建级联列表框