Chart with 1:1 scale

问题:

I have some problems plotting charts on Excel 2013.

I want to plot a graph using the same scale for both axes (so the graph should be a square). At the moment step for both x and y are the same but the x axis is about twice the length of y axis. I know it's possible to move the boundaries of the chart. However, I want my chart to be exactly a square, which is quite difficult to achieve manually. Is it possible to do that automatically on Excel 2013? If yes, how? If not, could you please recommend me some other programs which could help me out?

I hope my question makes sense.

I really appreciate your help! Thanks!

回答1:

This may help using VBA:

Sub SizeChart()
Dim cht As ChartObject
Set cht = ActiveSheet.ChartObjects("Chart 1")
    With cht
        .Height = 300 
        .Width = 300 
    End With
End Sub

回答2:

Here is a more general solution to the problem of equal scaling of both axes in xy-charts.

Excel usually autoscales the two axes and does not provide an option to keep x and y-scales equal.

The following VBA-Module accurately sets the scaling to best fit the plotArea of the diagram in the diagramArea (the bounding box). It first blows up the plotArea to the maximum and then looks, which axis is to be reduced for equal scales.

    Option Explicit

    ''
    ' Default borders between plot area and chart area
    '
    Const BORDER_LEFT = 4
    Const BORDER_RIGHT = 4
    Const BORDER_TOP = 29
    Const BORDER_BOTTOM = 4

    ''
    ' test procedure for ScaleChart
    Sub test1()

        Dim ws As Worksheet
        Dim oChart As ChartObject

        Set ws = ActiveSheet
        Set oChart = ws.ChartObjects("Diagramm 4")

        If ScaleChart(oChart) Then
            MsgBox "The axes are now equally scaled", vbInformation, "Success"
        Else
            MsgBox "An error occured", vbCritical, "Failed"
        End If

    End Sub

    ''
    ' ScaleChart - set equal scaling to both axes of a xy-chart
    '
    ' oChart a chartObject
    '
    ' borderLeft (optional)     left border between plot area and chart ares
    ' borderRight (optional)    right border between plot area and chart ares
    ' borderTop (optional)      top border between plot area and chart ares
    ' borderBottom (optional)   bottom border between plot area and chart ares
    '
    ' returns true on success, false on any error
    '
    Function ScaleChart(oChart As ChartObject, _
            Optional borderLeft As Double = BORDER_LEFT, Optional borderRight As Double = BORDER_RIGHT, _
            Optional borderTop As Double = BORDER_TOP, Optional borderBottom As Double = BORDER_BOTTOM) As Boolean

        '
        ' Variables
        '
        Dim xMin As Double
        Dim xMax As Double
        Dim yMin As Double
        Dim yMax As Double

        Dim xWidth As Double
        Dim yHeight As Double

        Dim scaleX As Double
        Dim scaleY As Double

        On Error GoTo mark_err

        With oChart.Chart

    '        ''
    '        ' display currend border settings
    '        Debug.Print "Const BORDER_LEFT = "; .PlotArea.Left
    '        Debug.Print "Const BORDER_RIGHT = "; .ChartArea.Width - .PlotArea.Width - .PlotArea.Left
    '
    '        Debug.Print "Const BORDER_TOP = "; .PlotArea.Top
    '        Debug.Print "Const BORDER_BOTTOM = "; .ChartArea.Height - .PlotArea.Height - .PlotArea.Top
    '
    '        ''


            ''
            ' reset plot area to full size
            '
            .PlotArea.Left = BORDER_LEFT
            .PlotArea.Width = .ChartArea.Width - .PlotArea.Left - BORDER_RIGHT

            .PlotArea.Top = BORDER_TOP
            .PlotArea.Height = .ChartArea.Height - .PlotArea.Top - BORDER_BOTTOM
            '
            ''

            ''
            ' get axis min/max values and current display sizes
            '
            xMin = .axes(xlCategory).MinimumScale
            xMax = .axes(xlCategory).MaximumScale

            xWidth = .PlotArea.Width

            yMin = .axes(xlValue).MinimumScale
            yMax = .axes(xlValue).MaximumScale
            yHeight = .PlotArea.Height

        End With


        scaleX = (xMax - xMin) / xWidth
        scaleY = (yMax - yMin) / yHeight

    '    '
    '    ' scales information
    '    '
    '    Debug.Print "x-axis: M 1:"; 1 / scaleX, "y-axis: M 1:"; 1 / scaleY

        If 1 / scaleX > 1 / scaleY Then
            '
            ' the diagram has to be reduced in x-direction
            '
            oChart.Chart.PlotArea.Width = oChart.Chart.PlotArea.Width * (1 / scaleY) / (1 / scaleX)
        Else
            '
            ' the diagram has to be reduced in y-direction
            '
            oChart.Chart.PlotArea.Height = oChart.Chart.PlotArea.Height * (1 / scaleX) / (1 / scaleY)
        End If

        '
        ' no error - return true
        '
        ScaleChart = True
        On Error GoTo 0
        Exit Function

    mark_err:
        '
        ' error - return false
        '
        ScaleChart = False
        On Error GoTo 0

    End Function