具有多个系列但相同 X 值的 Excel VBA 图表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20860338/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA Chart With Multiple Series But Same X Value
提问by IRHM
I'm trying to put together a VB script in Excel which creates a 'Stacked Bar Chart'.
我正在尝试在 Excel 中组合一个 VB 脚本来创建一个“堆叠条形图”。
From examples found in this site, I did the following code:
从本站点中找到的示例中，我执行了以下代码：
Sub AddNewSeries()
ActiveSheet.Shapes.AddChart2(297, xlBarStacked).Select
With ActiveChart.SeriesCollection.NewSeries
.Name = ActiveSheet.Range("$E")
.Values = ActiveSheet.Range("IFPStart")
.XValues = ActiveSheet.Range("IFPPN")
.Name = ActiveSheet.Range("$G")
.Values = ActiveSheet.Range("IFPBC")
.XValues = ActiveSheet.Range("IFPPN")
.Name = ActiveSheet.Range("$H")
.Values = ActiveSheet.Range("IFPBT")
.XValues = ActiveSheet.Range("IFPPN")
.Name = ActiveSheet.Range("$I")
.Values = ActiveSheet.Range("IFPEC")
.XValues = ActiveSheet.Range("IFPPN")
.Name = ActiveSheet.Range("$J")
.Values = ActiveSheet.Range("IFPFin")
.XValues = ActiveSheet.Range("IFPPN")
.Name = ActiveSheet.Range("$K")
.Values = ActiveSheet.Range("IFPIMS")
.XValues = ActiveSheet.Range("IFPPN")
.Name = ActiveSheet.Range("$L")
.Values = ActiveSheet.Range("IFPInf")
.XValues = ActiveSheet.Range("IFPPN")
.Name = ActiveSheet.Range("$M")
.Values = ActiveSheet.Range("IFPPT")
.XValues = ActiveSheet.Range("IFPPN")
.Name = ActiveSheet.Range("$N")
.Values = ActiveSheet.Range("IFPSols")
.XValues = ActiveSheet.Range("IFPPN")
End With
End Sub
You'll see that rather than using cell references, I use named ranges because the data will be dynamic.
您会看到，我没有使用单元格引用，而是使用命名范围，因为数据将是动态的。
The code does run without any errors displayed, but the last series used i.e.
代码运行时没有显示任何错误，但使用的最后一个系列即
.Name = ActiveSheet.Range("$N")
overwrites the previously plotted data.
覆盖之前绘制的数据。
What am I doing wrong?
我究竟做错了什么？
回答by Floris
You are not adding the series one at at time. You are adding one series, and keep changing (overwriting) the properties of that series. Thus you end up with just one series, with the properties that you specified.
您不会一次添加系列一。您正在添加一个系列，并不断更改（覆盖）该系列的属性。因此，您最终只有一个系列，具有您指定的属性。
The following might work better:
以下可能效果更好：
Sub AddNewSeries()
ActiveSheet.Shapes.AddChart2(297, xlBarStacked).Select
With ActiveChart.SeriesCollection.NewSeries
.XValues = ActiveSheet.Range("IFPPN")
.Name = ActiveSheet.Range("$E")
.Values = ActiveSheet.Range("IFPStart")
End With
With ActiveChart.SeriesCollection.NewSeries
.XValues = ActiveSheet.Range("IFPPN")
.Name = ActiveSheet.Range("$G")
.Values = ActiveSheet.Range("IFPBC")
End With
With ActiveChart.SeriesCollection.NewSeries
.XValues = ActiveSheet.Range("IFPPN")
.Name = ActiveSheet.Range("$H")
.Values = ActiveSheet.Range("IFPBT")
End With
With ActiveChart.SeriesCollection.NewSeries
.XValues = ActiveSheet.Range("IFPPN")
.Name = ActiveSheet.Range("$I")
.Values = ActiveSheet.Range("IFPEC")
End With
With ActiveChart.SeriesCollection.NewSeries
.XValues = ActiveSheet.Range("IFPPN")
.Name = ActiveSheet.Range("$J")
.Values = ActiveSheet.Range("IFPFin")
End With
With ActiveChart.SeriesCollection.NewSeries
.XValues = ActiveSheet.Range("IFPPN")
.Name = ActiveSheet.Range("$K")
.Values = ActiveSheet.Range("IFPIMS")
End With
With ActiveChart.SeriesCollection.NewSeries
.XValues = ActiveSheet.Range("IFPPN")
.Name = ActiveSheet.Range("$L")
.Values = ActiveSheet.Range("IFPInf")
End With
With ActiveChart.SeriesCollection.NewSeries
.XValues = ActiveSheet.Range("IFPPN")
.Name = ActiveSheet.Range("$M")
.Values = ActiveSheet.Range("IFPPT")
End With
With ActiveChart.SeriesCollection.NewSeries
.XValues = ActiveSheet.Range("IFPPN")
.Name = ActiveSheet.Range("$N")
.Values = ActiveSheet.Range("IFPSols")
End With
End Sub
It is possible that you need a
您可能需要一个
ActiveChart.SeriesCollection(1).Delete
Right after the AddChart2statement - on my machine, it tried to "guess" a series and got it very very wrong… but the above removed it and left me just the "good" series.
紧接着AddChart2声明 - 在我的机器上，它试图“猜测”一个系列，结果非常非常错误……但上面删除了它，只剩下“好”系列。
Also - I found that on the Mac, I could not use AddChart2but had to use AddChartinstead. Not sure if that matters for you.
另外 - 我发现在 Mac 上，我无法使用AddChart2但必须使用AddChart。不确定这对你来说是否重要。
