☰

将次轴原点与主轴原点对齐

tag-icon • tag-icon microsoft-excel vba microsoft-excel-2010
将次轴原点与主轴原点对齐

我感兴趣的是对齐我的次轴，使其与我的主轴完全对齐。

为了更清楚起见，我希望原点都在同一位置的 0 处排列，并且各自保留它们的相对比例。

我希望这是一个 VBA 解决方案或者尽量减少手动工作。

图表

答案1

我使用了这个链接：http://peltiertech.com/Excel/Charts/AlignXon2Ys.html

' Helper Macros
Sub AlignY_PrimaryMinimum()
  AlignY 1
End Sub

Sub AlignY_PrimaryMaximum()
  AlignY 2
End Sub

Sub AlignY_SecondaryMinimum()
  AlignY 3
End Sub

Sub AlignY_SecondaryMaximum()
  AlignY 4
End Sub

' The Main Event
Sub AlignY(FreeParam As Integer)
  '' FreeParam: AXIS ALLOWED TO VARY
  '' 1: Y1 (PRI) MIN
  '' 2: Y1 (PRI) MAX
  '' 3: Y2 (SEC) MIN
  '' 4: Y2 (SEC) MAX

  Dim Y1min As Double
  Dim Y1max As Double
  Dim Y2min As Double
  Dim Y2max As Double

  With ActiveChart
    With .Axes(2, 1)
      Y1min = .MinimumScale
      Y1max = .MaximumScale
      .MinimumScaleIsAuto = False
      .MaximumScaleIsAuto = False
    End With
    With .Axes(2, 2)
      Y2min = .MinimumScale
      Y2max = .MaximumScale
      .MinimumScaleIsAuto = False
      .MaximumScaleIsAuto = False
    End With
    Select Case FreeParam
      Case 1
        If Y2max <> 0 Then _
          .Axes(2, 1).MinimumScale = Y2min * Y1max / Y2max
      Case 2
        If Y2min <> 0 Then _
          .Axes(2, 1).MaximumScale = Y1min * Y2max / Y2min
      Case 3
        If Y1max <> 0 Then _
          .Axes(2, 2).MinimumScale = Y1min * Y2max / Y1max
      Case 4
        If Y1min <> 0 Then _
          .Axes(2, 2).MaximumScale = Y2min * Y1max / Y1min
    End Select
  End With
End Sub

答案2

这有用吗？

Sub RescaleSecondaryAxis()

    Dim PriMax, PriMin
    Dim SecMax, SecMin

    ActiveSheet.ChartObjects("myChart").Activate
    ActiveChart.Axes(xlValue, xlPrimary).Select

    PriMax = ActiveChart.Axes(xlValue, xlPrimary).MaximumScale
    PriMin = ActiveChart.Axes(xlValue, xlPrimary).MinimumScale

    SecMax = PriMax 
    SecMin = PriMin 

    ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = SecMax
    ActiveChart.Axes(xlValue, xlSecondary).MinimumScale = SecMin

End Sub

相关内容