我正在尝试创建一个 xy 图,其中 x 值是时间轴(A 列),y 值在其他列中。(在示例中只有 C 和 D)当我从下面尝试我的代码时,我得到了一个带有时间轴(即 A 列)的图表,而 y 值是所有其他列(B、C、D、E、...),这是不想要的。
我可以看到 .seriescollection(1) 和 (2) 覆盖了默认的 y 值(因为我的范围较小),但所有其他值(B、D、E 列……)仍然保留在图中。
有什么想法吗?提前致谢!
Sub grafieken()
'
' grafieken Macro
'
Dim sh As Worksheet
Dim chrt As Chart
Dim naaaam As String
naaam = ActiveWorkbook.ActiveSheet.Name
Set sh = ActiveWorkbook.Worksheets(naaam)
Set chrt = sh.Shapes.AddChart.Chart
With chrt
'Data?
.ChartType = xlXYScatter
.SeriesCollection.NewSeries
.SeriesCollection(1).Name = sh.Range("$C$1")
.SeriesCollection(1).XValues = sh.Range("$A$2:$A$11")
.SeriesCollection(1).Values = sh.Range("$C$2:$C$11")
.SeriesCollection(2).Name = sh.Range("$D$1")
.SeriesCollection(2).XValues = sh.Range("$A$2:$A$11")
.SeriesCollection(2).Values = sh.Range("$D$2:$D$11")
'Titles?
.HasTitle = True
.ChartTitle.Characters.Text = naaam
End With
End Sub
答案1
对我来说,当我添加SetSourceData
:
Sub grafieken()
Dim sh As Worksheet
Dim chrt As Chart
Dim naaaam As String
naaam = ActiveWorkbook.ActiveSheet.Name
Set sh = ActiveWorkbook.Worksheets(naaam)
Set chrt = sh.Shapes.AddChart.Chart
With chrt
'Data?
.SetSourceData Source:=Range("Sheet1!$C$1:$D$11")
.ChartType = xlXYScatter
.SeriesCollection(1).Name = sh.Range("$C$1")
.SeriesCollection(1).XValues = sh.Range("$A$2:$A$11")
.SeriesCollection(1).Values = sh.Range("$C$2:$C$11")
'.SeriesCollection.NewSeries ' removed
.SeriesCollection(2).Name = sh.Range("$D$1")
.SeriesCollection(2).XValues = sh.Range("$A$2:$A$11")
.SeriesCollection(2).Values = sh.Range("$D$2:$D$11")
'Titles?
.HasTitle = True
.ChartTitle.Characters.Text = naaam
End With
End Sub