Excel柱形图：重叠双系列，将系列1柱形与X轴置于顶层

问题描述 #

我通过VBA创建包含两个系列的簇状柱形图，需要实现两个需求：

• Series 1 的柱形显示在 Series 2 上方
• X轴采用 Series 1 的分类

目前使用以下代码可以将Series 1的柱形置于顶层，但X轴仍然采用Series 2的分类，请问如何修改才能让X轴使用Series 1的分类？

With .SeriesCollection(1)
    .AxisGroup = 2
End With

附完整原始代码：

Option Explicit

Sub CreateChartClustCol2Series_01_()
  Dim ws As Worksheet
  Dim objChart As ChartObject
  Dim myDataRange As Range
  Dim myDataRange2a As Range
  Dim myDataRange2b As Range
  Dim myChtRange As Range
  Set ws = ActiveSheet
  With ws
  On Error Resume Next
    Set myDataRange = Application.InputBox _
        (Title:="Series 1 Cat Row + Val Row", Prompt:="", Type:=8)
    Set myDataRange2a = Application.InputBox _
        (Title:="Series 2 Cat Row", Prompt:="", Type:=8)
    Set myDataRange2b = Application.InputBox _
       (Title:="Series 2 Val Row", Prompt:="", Type:=8)
    Set myChtRange = Application.InputBox _
        (Title:="Chart Area", Prompt:="", Type:=8)
    Set objChart = .ChartObjects.Add( _
        Left:=myChtRange.Left, Top:=myChtRange.Top, _
        Width:=myChtRange.Width, Height:=myChtRange.Height)
    With objChart.Chart
        .SetSourceData Source:=myDataRange
        With .SeriesCollection.NewSeries
            .XValues = myDataRange2a  ' Category
            .Values = myDataRange2b   ' Value
        End With
        With .SeriesCollection(1)
            .ChartType = xlColumnClustered
            .AxisGroup = 2
        End With
        With .SeriesCollection(2)
            .ChartType = xlColumnClustered
'            .AxisGroup = 2
        End With
        .ChartGroups(1).GapWidth = 50
        .HasLegend = False
        .HasTitle = False
        With .ChartArea
            .AutoScaleFont = False
        End With
        .SetElement (msoElementPrimaryValueGridLinesNone)
        ' X-Axis Series 1
        .HasAxis(xlCategory) = True
            With .Axes(xlCategory)
                .Format.Line.Visible = True
                .MajorTickMark = xlNone
                .TickLabelPosition = xlLow
                .TickLabels.Font.Name = "Calibri"
                .TickLabels.Font.Size = 10
            End With
        ' Y-Axis Series 1
        .HasAxis(xlValue) = True
            With .Axes(xlValue)
                .Format.Line.Visible = True
                .Format.Fill.Visible = False
                .MaximumScale = 1000
                .MinimumScale = -1000
                .MajorUnit = 250
                .MinorUnit = 50
                .CrossesAt = 0
                .MajorTickMark = xlInside
                .MinorTickMark = xlNone
                .TickLabelPosition = xlNone
            End With
        ' X-Axis Series 2
        .HasAxis(xlCategory, xlSecondary) = False
            With .Axes(xlCategory, xlSecondary)
                .Format.Line.Visible = True
                .MajorTickMark = xlNone
                .TickLabelPosition = xlLow
                .TickLabels.Font.Name = "Calibri"
                .TickLabels.Font.Size = 10
            End With
        ' Y-Axis Series 2
        .HasAxis(xlValue, xlSecondary) = True
            With .Axes(xlValue, xlSecondary)
                .Format.Line.Visible = False
                .MaximumScale = 1000
                .MinimumScale = -1000
                .CrossesAt = 0
                .TickLabelPosition = xlNone
            End With
    End With
  On Error GoTo 0
  End With
  If myDataRange Is Nothing Then Exit Sub
  If myDataRange2a Is Nothing Then Exit Sub
  If myDataRange2b Is Nothing Then Exit Sub
  If myChtRange Is Nothing Then Exit Sub
End Sub

图表效果示例：

解决方案 #

要同时满足两个需求，需要调整轴组的分配逻辑，隐藏主X轴并启用次X轴（对应Series 1的分类），具体修改如下：

核心修改点 #

1. 确认Series 2处于主坐标轴组（AxisGroup=1），Series 1处于次坐标轴组（AxisGroup=2），保证Series 1柱形在顶层。
2. 关闭主X轴的显示，启用次X轴，并将次X轴配置为所需样式。

修改后的完整代码 #

Option Explicit

Sub CreateChartClustCol2Series_01_()
  Dim ws As Worksheet
  Dim objChart As ChartObject
  Dim myDataRange As Range
  Dim myDataRange2a As Range
  Dim myDataRange2b As Range
  Dim myChtRange As Range
  Set ws = ActiveSheet
  With ws
  On Error Resume Next
    Set myDataRange = Application.InputBox _
        (Title:="Series 1 Cat Row + Val Row", Prompt:="", Type:=8)
    Set myDataRange2a = Application.InputBox _
        (Title:="Series 2 Cat Row", Prompt:="", Type:=8)
    Set myDataRange2b = Application.InputBox _
       (Title:="Series 2 Val Row", Prompt:="", Type:=8)
    Set myChtRange = Application.InputBox _
        (Title:="Chart Area", Prompt:="", Type:=8)
    Set objChart = .ChartObjects.Add( _
        Left:=myChtRange.Left, Top:=myChtRange.Top, _
        Width:=myChtRange.Width, Height:=myChtRange.Height)
    With objChart.Chart
        .SetSourceData Source:=myDataRange
        With .SeriesCollection.NewSeries
            .XValues = myDataRange2a  ' Series 2分类
            .Values = myDataRange2b   ' Series 2值
        End With
        ' Series 1放到次坐标轴组，保证柱形在顶层
        With .SeriesCollection(1)
            .ChartType = xlColumnClustered
            .AxisGroup = 2
        End With
        ' Series 2放到主坐标轴组
        With .SeriesCollection(2)
            .ChartType = xlColumnClustered
            .AxisGroup = 1
        End With
        .ChartGroups(1).GapWidth = 50
        .HasLegend = False
        .HasTitle = False
        With .ChartArea
            .AutoScaleFont = False
        End With
        .SetElement (msoElementPrimaryValueGridLinesNone)
        
        ' 隐藏主X轴（对应Series 2的分类）
        .HasAxis(xlCategory, xlPrimary) = False
        ' 启用次X轴（对应Series 1的分类）并配置样式
        .HasAxis(xlCategory, xlSecondary) = True
        With .Axes(xlCategory, xlSecondary)
            .Format.Line.Visible = True
            .MajorTickMark = xlNone
            .TickLabelPosition = xlLow
            .TickLabels.Font.Name = "Calibri"
            .TickLabels.Font.Size = 10
        End With
        
        ' Y轴配置保持不变
        ' 主Y轴（Series 2）
        .HasAxis(xlValue, xlPrimary) = True
            With .Axes(xlValue, xlPrimary)
                .Format.Line.Visible = True
                .Format.Fill.Visible = False
                .MaximumScale = 1000
                .MinimumScale = -1000
                .MajorUnit = 250
                .MinorUnit = 50
                .CrossesAt = 0
                .MajorTickMark = xlInside
                .MinorTickMark = xlNone
                .TickLabelPosition = xlNone
            End With
        ' 次Y轴（Series 1）
        .HasAxis(xlValue, xlSecondary) = True
            With .Axes(xlValue, xlSecondary)
                .Format.Line.Visible = False
                .MaximumScale = 1000
                .MinimumScale = -1000
                .CrossesAt = 0
                .TickLabelPosition = xlNone
            End With
    End With
  On Error GoTo 0
  End With
  If myDataRange Is Nothing Then Exit Sub
  If myDataRange2a Is Nothing Then Exit Sub
  If myDataRange2b Is Nothing Then Exit Sub
  If myChtRange Is Nothing Then Exit Sub
End Sub

原理说明 #

• 次坐标轴组的元素会默认显示在主坐标轴组元素上方，因此Series 1（次轴组）的柱形会覆盖在Series 2（主轴组）之上。
• 关闭主X轴显示，启用次X轴后，图表会使用Series 1的数据源中的分类作为X轴标签，满足需求。

内容的提问来源于stack exchange，提问作者user23636411