首页 > Excel专区 > Excel教程 >

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

Excel教程 2022-01-10 19:04:52

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

图 1

在图表中,最开始有 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 所示。

图 2

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

图 3

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

图 4


标签: Excel图表制作Excel常用函数excel数据透视表excel教程

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.