组合图

本页目录

统计图表中常常把不同类型的图表组合在一起组成一个新的图表，这个新图表兼具有组成它的每个图表的用途，可以为使用者提供绘图数据的更多的信息。比如5.3.2小节介绍的小提琴图样式2就组合了简单小提琴图和箱形图，从而让使用者对数据有更多的了解。本节介绍更多组合图，包括云雨图、误差柱状图、散点柱状图和散点箱形图等。[大谦Excel，dqexcel点com]

云雨图

云雨图是核密度估计曲线图、箱形图和抖动散点图等图表类型的组合，根据参与组合的图表类型的不同，可以有不同的样式。下面介绍3种常见样式。

云雨图样式1由核密度估计曲线图和抖动散点图组合而成，如图5-17所示。上有云，下有雨，很形象。抖动散点图直接显示了绘图数据的分布特征。

Document Image

图5-17 云雨图样式1

用draw_kde函数绘制核密度估计曲线图，用draw_rnd_scatter_h函数绘制水平放置的抖动散点图。调用这两个函数绘制云雨图样式1。完整代码见：Samples->ch08 统计图表->15 云雨图->py.py。

code.vba 
... 
           DrawKDE cht, D1, lngCount(1), 1 + 0.2, 0, 0, 255, -4, 4
  DrawRndScatterH cht, D1, lngCount(1), 1, 0.5

  DrawKDE cht, D2, lngCount(2), 3 + 0.2, 0, 0, 255, -4, 4
  DrawRndScatterH cht, D2, lngCount(1), 3, 0.5

  DrawKDE cht, D3, lngCount(3), 5 + 0.2, 0, 0, 255, -4, 4
  DrawRndScatterH cht, D3, lngCount(3), 5, 0.5
...

第2种样式的云雨图由核密度估计曲线图和箱形图组合而成，如图5-18所示。该样式的作用跟上面介绍的小提琴图样式2一样。

Document Image

图5-18 云雨图样式2

用draw_kde函数绘制核密度估计曲线图，用draw_boxplot_h函数绘制水平放置的箱形图。调用这两个函数绘制云雨图样式2。完整代码见：Samples->ch08 统计图表->16 云雨图2->py.py。

code.vba 
... 
            DrawKDE cht, D1, lngCount(1), 1 + 0.2, 0, 0, 255, -4, 4
  DrawBoxplotH cht, D1, lngCount(1), 1, 0, 0, 255, 0.1, False

  DrawKDE cht, D2, lngCount(2), 3 + 0.2, 0, 0, 255, -4, 4
  DrawBoxplotH cht, D2, lngCount(2), 3, 0, 0, 255, 0.1, False

  DrawKDE cht, D3, lngCount(3), 5 + 0.2, 0, 0, 255, -4, 4
  DrawBoxplotH cht, D3, lngCount(3), 5, 0, 0, 255, 0.1, False
...

3种样式的云雨图由核密度估计曲线图、箱形图和抖动散点图组合而成，如图5-19所示。该样式的云雨图兼具了各种图表的优点，可以直接探查原始数据的分布，也可以用统计量和概率密度了解数据的总体特征。

Document Image

图5-19 云雨图样式3

用draw_kde函数绘制核密度估计曲线图，用draw_boxplot_h函数绘制水平放置的箱形图，用draw_rnd_scatter_h函数绘制水平放置的抖动散点图。调用这三个函数绘制云雨图样式3。完整代码见：Samples->ch08 统计图表->17 云雨图3->py.py。

code.vba 
‘省略部分代码
draw_kde(cht,d1,1+0.2,0,0,255,-4,4)
draw_rnd_scatter_h(cht,d1,count1,1-0.2,0.5)
draw_boxplot_h(app2,cht,d1,count1,1,0,0,255,0.1,False)
draw_kde(cht,d2,3+0.2,0,0,255,-4,4)
draw_rnd_scatter_h(cht,d2,count2,3-0.2,0.5)
draw_boxplot_h(app2,cht,d2,count2,3,0,0,255,0.1,False)
draw_kde(cht,d3,5+0.2,0,0,255,-4,4)
draw_rnd_scatter_h(cht,d3,count3,5-0.2,0.5)
draw_boxplot_h(app2,cht,d3,count3,5,0,0,255,0.1,False)
‘省略部分代码

误差柱状图

误差柱状图由柱状图和误差条图组合而成。柱状图一般用来表示各组数据的均值的大小，误差条图表示置信区间或标准差等。误差柱状图如图5-20所示。

Document Image

图5-20 误差柱状图

用Python xlwings编程绘制误差柱状图时，笔者试图先创建柱状图，然后利用序列对象的Hasbars属性、ErrorBars属性和ErrorBar属性等添加误差条图，如下面代码所示。但操作没有成功，误差条始终加不上。

所以，最后考虑自己绘制柱状图和误差条图，如下面代码所示。完整代码见：Samples->ch08 统计图表->18 误差柱状图->py.py。

code.vba 
Sub Test()
  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.3
  cht.Axes(1).MaximumScale = 6.7
  cht.Axes(2).MinimumScale = 0
  cht.Axes(2).MaximumScale = 0.25

  Dim intI As Integer
  If cht.SeriesCollection.Count > 0 Then
    For intI = cht.SeriesCollection.Count To 1 Step -1
      cht.SeriesCollection(intI).Delete
    Next
    cht.HasLegend = False
  End If

  cht.SeriesCollection.NewSeries
  Dim ax1 As Axis
  Dim ax2 As Axis
  Set ax1 = cht.Axes(1)
  Set ax2 = cht.Axes(2)
  ax1.CrossesAt = ax1.MinimumScale
  ax2.CrossesAt = ax2.MinimumScale

  SetStyle cht

  Dim dblMn()
  Dim lx As Double
  Dim ly As Double
  Dim ww As Double
  Dim hh As Double
  dblMn = Range("B2:B7")
  For intI = 1 To 6
    DrawBar cht, CDbl(dblMn(intI, 1)), CDbl(intI), 0, 0, 255, 0.5, False
  Next
  Dim dblUp()
  Dim dblDn()
  Dim shp2 As Shape
  dblUp = Range("C2:C7")
  dblDn = Range("D2:D7")
  For intI = 1 To 6
    bx = ShapeX(cht, CDbl(intI))
    ex = ShapeX(cht, CDbl(intI))
    by = ShapeY(cht, CDbl(dblMn(intI, 1)))
    ey = ShapeY(cht, CDbl(dblUp(intI, 1) + dblMn(intI, 1)))
    Set shp2 = cht.Shapes.AddLine(bx, by, ex, ey)
    shp2.Line.ForeColor.RGB = RGB(0, 0, 0)
    shp2.Line.Weight = 1

    bx = ShapeX(cht, CDbl(intI))
    ex = ShapeX(cht, CDbl(intI))
    by = ShapeY(cht, CDbl(dblMn(intI, 1)))
    ey = ShapeY(cht, CDbl(dblMn(intI, 1) - dblDn(intI, 1)))
    Set shp2 = cht.Shapes.AddLine(bx, by, ex, ey)
    shp2.Line.ForeColor.RGB = RGB(0, 0, 0)
    shp2.Line.Weight = 1

    bx = ShapeX(cht, CDbl(intI - 0.5 / 4))
    ex = ShapeX(cht, CDbl(intI + 0.5 / 4))
    by = ShapeY(cht, CDbl(dblUp(intI, 1) + dblMn(intI, 1)))
    ey = ShapeY(cht, CDbl(dblUp(intI, 1) + dblMn(intI, 1)))
    Set shp2 = cht.Shapes.AddLine(bx, by, ex, ey)
    shp2.Line.ForeColor.RGB = RGB(0, 0, 0)
    shp2.Line.Weight = 1

    bx = ShapeX(cht, CDbl(intI - 0.5 / 4))
    ex = ShapeX(cht, CDbl(intI + 0.5 / 4))
    by = ShapeY(cht, CDbl(dblMn(intI, 1) - dblDn(intI, 1)))
    ey = ShapeY(cht, CDbl(dblMn(intI, 1) - dblDn(intI, 1)))
    Set shp2 = cht.Shapes.AddLine(bx, by, ex, ey)
    shp2.Line.ForeColor.RGB = RGB(0, 0, 0)
    shp2.Line.Weight = 1
  Next
  cht.SeriesCollection.NewSeries
  Dim intN As Integer
  intN = cht.SeriesCollection.Count
  cht.FullSeriesCollection(intN).ChartType = xlXYScatterLinesNoMarkers
  cht.FullSeriesCollection(intN).XValues = Array(0, 7)
  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

运行代码后生成图5-20所示的误差柱状图。

散点柱状图

散点柱状图在柱状图基础上叠加绘制抖动散点图，如图5-21所示。该图给出了各组数据的均值统计量和数据点本身。

Document Image

图5-21 散点柱状图

用draw_bar函数绘制柱状图，用draw_rnd_scatter函数绘制抖动散点图。调用这两个函数绘制散点柱状图。完整代码见：Samples->ch08 统计图表->19 散点柱状图->py.py。

code.vba 
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 = 130

  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

运行代码生成类似图9-21所示的散点柱状图。

散点箱形图

散点箱形图在箱形图基础上叠加绘制抖动散点图，如图5-22和图5-23所示。散点箱形图给出了各组数据的分位数统计量和数据点本身。

Document Image

图5-23 用自定义函数绘制散点箱形图

用draw_boxplot函数绘制箱形图，用draw_rnd_scatter函数绘制抖动散点图。调用这两个函数绘制散点箱形图。完整代码见：Samples->ch08 统计图表->21 散点箱形图-自定义->py.py。

code.vba 
Sub Test()
  Dim intI As Integer
  Dim intJ As Integer
  Dim D1() As Double
  Dim D2() As Double
  Dim D3() As Double
  Dim D4() As Double
  Dim D5() As Double
  Dim D6() As Double
  Dim intCount(1 To 6) As Integer
  For intI = 1 To 6
    intCount(intI) = 0
  Next
  Dim data()
  data = Range("B2:C101").Value
  For intJ = 1 To 100
    If data(intJ, 2) = 1 Then
      intCount(1) = intCount(1) + 1
      ReDim Preserve D1(intCount(1))
      D1(intCount(1)) = data(intJ, 1)
    ElseIf data(intJ, 2) = 2 Then
      intCount(2) = intCount(2) + 1
      ReDim Preserve D2(intCount(2))
      D2(intCount(2)) = data(intJ, 1)
    ElseIf data(intJ, 2) = 3 Then
      intCount(3) = intCount(3) + 1
      ReDim Preserve D3(intCount(3))
      D3(intCount(3)) = data(intJ, 1)
    ElseIf data(intJ, 2) = 4 Then
      intCount(4) = intCount(4) + 1
      ReDim Preserve D4(intCount(4))
      D4(intCount(4)) = data(intJ, 1)
    ElseIf data(intJ, 2) = 5 Then
      intCount(5) = intCount(5) + 1
      ReDim Preserve D5(intCount(5))
      D5(intCount(5)) = data(intJ, 1)
    ElseIf data(intJ, 2) = 6 Then
      intCount(6) = intCount(6) + 1
      ReDim Preserve D6(intCount(6))
      D6(intCount(6)) = data(intJ, 1)
    End If
  Next

  Dim cht As Chart
  Set cht = ActiveChart
  Dim ax1 As Axis
  Set ax1 = cht.Axes(1)
  Dim ax2 As Axis
  Set ax2 = cht.Axes(2)
  ax1.MinimumScale = 0
  ax1.MaximumScale = 7
  ax2.MinimumScale = -0.1
  ax2.MaximumScale = 0.4
  ax2.ReversePlotOrder = True
  If cht.Shapes.Count > 0 Then
    For intI = cht.Shapes.Count To 1 Step -1
      cht.Shapes(intI).Delete
    Next
  End If

  DrawBoxplot D1, intCount(1), cht, 255, 0, 0, 1, 0.5, False
  DrawBoxplot D2, intCount(2), cht, 0, 255, 0, 2, 0.5, False
  DrawBoxplot D3, intCount(3), cht, 0, 0, 255, 3, 0.5, False
  DrawBoxplot D4, intCount(4), cht, 255, 255, 0, 4, 0.5, False
  DrawBoxplot D5, intCount(5), cht, 0, 255, 255, 5, 0.5, False
  DrawBoxplot D6, intCount(6), cht, 255, 0, 255, 6, 0.5, False
End Sub

运行完整代码生成类似图5-23的散点箱形图。[大谦Excel，dqexcel点com]