excel - 如何设置直方图x轴的最大和最小比例？

Question

我正在尝试将我的直方图设置为具有最大和最小比例，因为对于我的一些直方图，它们在开始和结束时有很多零。我正在使用

    With Activechart.Axes(xlCategory)
        .MaximumScale = Application.WorksheetFunction.RoundUp(Bnum, -1)
        .MinimumScale = Application.WorksheetFunction.RoundDown(Snum, -1)
    End With

尝试将其设置在 Snum 的位置是最小的数， Bnum是所选范围内的最大值。
然而有一个

Run-time error '-2147467259 (80004005)': Method of 'MaximumScale' of object 'Axis' failed

所以我在想，是否可以设置直方图的比例，或者我是否必须更改 bin 和范围来解决这个问题。请指教，谢谢。

如果有帮助，这是我绘制直方图的完整代码

Sub MakeHistogram()

    Dim src_sheet As Worksheet, Graph_sheet As Worksheet
    Dim selected_range As Range
    Dim title As String
    Dim r As Integer
    Dim percent_cell As Range
    Dim num_percent As Integer
    Dim count_range As Range, bin_range As Range
    Dim new_chart As Chart
    Dim lRow As Long, lCol As Long, glRow As Long
    Dim xStr As String
    Dim RngToCover As Range, Chtob As ChartObject
    Dim Snum As Long, Bnum As Long

    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .DisplayStatusBar = False
    End With

    With Sheets("Data")
        lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        lCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
    End With

    For i = 5 To lCol

        With Sheets("Data")
            Set selected_range = .Range(.Cells(6, i), .Cells(lRow, i))
        End With
        Set src_sheet = Sheets("Data")
        Set Graph_sheet = Sheets("Graphs")
        title = src_sheet.Cells(2, i).Value

        With Graph_sheet
            glRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
            If glRow = 2 Then glRow = glRow - 1
            .Cells(glRow + 1, 1) = title
            .Cells(glRow + 1, 1).Font.Bold = True
        End With
        xStr = src_sheet.Cells(5, i).Value
        If xStr = "%" Then xStr = "Percentage %"

        num_percent = selected_range.Count

        ' See how many bins we will have.
        Const BIN_SIZE As Integer = 5
        Dim num_bins As Integer
        num_bins = 150 \ BIN_SIZE

        ' Make the bin separators.
        Graph_sheet.Cells(1, 2) = "Bins"

        For r = 1 To num_bins - 1
            Graph_sheet.Cells(r + 2, 2) = r * BIN_SIZE - 1
        Next r

        ' Make the counts.
        Graph_sheet.Cells(1, 1) = "Counts"
        Set count_range = Graph_sheet.Range("A" & glRow + 2 & ":A" & num_bins + glRow)
        Set bin_range = Graph_sheet.Range("B" & 3 & ":B" & num_bins)

        count_range = WorksheetFunction.Frequency(selected_range, bin_range)

        ' Make the range labels.

        Graph_sheet.Cells(1, 3) = "Ranges"
        For r = 1 To num_bins - 1
            Graph_sheet.Cells(r + 2, 3) = "'" & _
                5 * (r - 1) & "-" & _
                  5 * (r - 1) + 4
            Graph_sheet.Cells(r + 2, 3).HorizontalAlignment = _
                xlRight
        Next r
        r = num_bins
        Graph_sheet.Cells(r + 1, 3) = "'" & _
            5 * (r - 1) & "-150"
        Graph_sheet.Cells(r + 1, 3).HorizontalAlignment = xlRight

        ' Make the chart.
        Set new_chart = Charts.Add()
        With new_chart
            .ChartType = xlColumnClustered
            .SetSourceData Source:=Graph_sheet.Range("A" & glRow + 2 & ":A" & _
                num_bins + glRow + 1), _
                PlotBy:=xlColumns
            .Location where:=xlLocationAsObject, _
                Name:="Graphs"
        End With

        'Get the largest and smallest number
        Snum = 100
        Bnum = 0
        For Each cell In selected_range
            With cell
                If .Value < Snum Then Snum = .Value
                If .Value > Bnum Then Bnum = .Value
            End With
        Next cell

        With Graph_sheet
            Set RngToCover = .Range(.Cells(glRow + 5, 5), .Cells(glRow + 22, 11))
        End With

        With ActiveChart
            .HasTitle = True
            .ChartTitle.Characters.Text = title & " Histogram"
            With .Axes(xlCategory, xlPrimary)
                .HasTitle = True
                .AxisTitle.Characters.Text = xStr
            End With
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Count"

            ' Display percentage ranges on the X axis.
            .SeriesCollection(1).XValues = "='" & _
                "Graphs" & "'!R3C3:R" & _
                num_bins + 1 & "C3"

            Set Chtob = .Parent
            Chtob.Height = RngToCover.Height
            Chtob.Width = RngToCover.Width
            Chtob.Top = RngToCover.Top
            Chtob.Left = RngToCover.Left
            With .Axes(xlCategory)
                'Error starts here
                .MaximumScale = Application.WorksheetFunction.RoundUp(Bnum, -1) 
                .MinimumScale = Application.WorksheetFunction.RoundDown(Snum, -1)
            End With
        End With
        ActiveChart.SeriesCollection(1).Select
        With ActiveChart.ChartGroups(1)
            .Overlap = 0
            .GapWidth = 0
            .HasSeriesLines = False
            .VaryByCategories = False
        End With
    Next i
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
        .DisplayStatusBar = True
    End With
End sub

此代码循环遍历数据表中的数据列并获取每列的频率，其中包含数百个数据，并获取它的计数以在图表中为每列数据制作直方图。

如果您需要更多详细信息，请与我澄清，谢谢。

Answer 1

根据Microsoft Docs MaximumScale和 MinimumScale不适用于 x 轴 (xlCategory)，而仅适用于 y 轴 (xlValue)。所以你需要改变范围。