前面两节介绍了用基本图形元素搭建新图表和在Excel图表基础上重建图元创建新图表，除此之外，还可以通过组合已有类型图表来创建新图表。
2.3.1小节介绍了散点柱状图的创建，用多边形绘制柱状图，用点绘制抖动散点图。所以，该图是用基本图形元素搭建而成的。本节还是用散点柱状图为例来演示组合已有类型的图表创建新图表。与前面不同的是，本例中用Shapes对象的AddChart2函数创建Excel图表，并用逐个添加序列的方式添加柱状图序列和散点图序列。所以，实际上是通过组合两种类型的图表来创建散点柱状图。图表效果如图2-40所示。[大谦Excel，dqexcel点com]
\[\]
图2-40 散点柱状图
下面的代码实现图2-40。注意，绘制散点图之前需要用随机数对各组数据的横坐标进行抖动，生成抖动散点图。完整代码见：Samples->ch05 创建新图表->38 散点柱状图的另外一种实现->py.py。
code.vba
Sub DrawRndScatter(cht As Chart, dblX As Double, dblY() As Double, lngN As Long, dblW As Double)
Dim dblRD() As Double
ReDim dblRD(lngN)
Dim intI As Integer
Dim intCount As Integer
cht.SeriesCollection.NewSeries
intCount = cht.SeriesCollection.Count
For intI = 0 To lngN - 1
Randomize
dblRD(intI) = dblX - dblW / 2 + dblW * Rnd
Next
cht.SeriesCollection(intCount).ChartType = xlXYScatter
cht.SeriesCollection(intCount).XValues = dblRD
cht.SeriesCollection(intCount).Values = dblY
'cht.SeriesCollection(intCount).PlotOrder = 2
End Sub
Sub Test()
Dim intI As Integer
Dim Data()
Dim dblDt1(0 To 19) As Double
Dim dblDt2(0 To 19) As Double
Dim dblDt3(0 To 19) As Double
Dim dblDt4(0 To 19) As Double
Dim dblMean(3) As Double
Data = Range("B2:E21").Value
For intI = 0 To 19
dblDt1(intI) = CDbl(Data(intI + 1, 1))
dblDt2(intI) = CDbl(Data(intI + 1, 2))
dblDt3(intI) = CDbl(Data(intI + 1, 3))
dblDt4(intI) = CDbl(Data(intI + 1, 4))
Next
dblMean(0) = Application.WorksheetFunction.Average(dblDt1)
dblMean(1) = Application.WorksheetFunction.Average(dblDt2)
dblMean(2) = Application.WorksheetFunction.Average(dblDt3)
dblMean(3) = Application.WorksheetFunction.Average(dblDt4)
Dim shp As Shape
Dim cht As Chart
Set shp = ActiveSheet.Shapes.AddChart2()
Set cht = shp.Chart
cht.ChartType = xlXYScatter
cht.Axes(1).MinimumScale = 0.5
cht.Axes(1).MaximumScale = 4.5
cht.Axes(2).MinimumScale = 0
cht.Axes(2).MaximumScale = 0.35
SetStyle cht
cht.SeriesCollection.NewSeries
Dim intN As Integer
intN = cht.SeriesCollection.Count
cht.FullSeriesCollection(intN).ChartType = xlColumnClustered
cht.FullSeriesCollection(intN).XValues = Array(1, 2, 3, 4)
cht.FullSeriesCollection(intN).Values = dblMean
cht.FullSeriesCollection(intN).Format.Fill.ForeColor.RGB = RGB(76, 200, 132)
cht.ChartGroups(1).GapWidth = 100
DrawRndScatter cht, 1, dblDt1, 20, 0.5
DrawRndScatter cht, 2, dblDt2, 20, 0.5
DrawRndScatter cht, 3, dblDt3, 20, 0.5
DrawRndScatter cht, 4, dblDt4, 20, 0.5
cht.SeriesCollection.NewSeries
intN = cht.SeriesCollection.Count
cht.FullSeriesCollection(intN).ChartType = xlXYScatterLinesNoMarkers
cht.FullSeriesCollection(intN).XValues = Array(0.5, 4.5)
cht.FullSeriesCollection(intN).Values = Array(0, 0)
cht.FullSeriesCollection(intN).Format.Line.ForeColor.RGB = RGB(0, 0, 0)
cht.FullSeriesCollection(intN).Format.Line.Weight = 1
End Sub运行代码生成图2-40。