1比1的图表 [英] Chart with 1:1 scale
问题描述
我想使用两个轴的相同比例绘制一个图表(所以图形应该是一个正方形) 。在x和y两者的时刻,X轴是y轴长度的两倍。我知道可以移动图表的边界。但是,我希望我的图表是一个正方形,这是很难手动实现的。是否可以在Excel 2013上自动执行?如果是,怎么样?如果没有,你能否推荐一些可以帮助我的其他程序?
我希望我的问题有道理。
$ b $我非常感谢你的帮助!谢谢!
这是一个更通用的解决方案,解决了xy图表中两个轴的等比例缩放问题。 >
Excel通常自动缩放两个轴,并且不提供保持x和y等级相等的选项。
以下VBA-Module准确地将缩放设置为最适合 diagramArea
(边界框)中图的 plotArea
。
它首先将 plotArea
最大化,然后看起来,哪个轴将以相等的比例缩小。
Option Explicit
''
'绘图区域和图表区域之间的默认边框
'
Const BORDER_LEFT = 4
Const BORDER_RIGHT = 4
Const BORDER_TOP = 29
Const BORDER_BOTTOM = 4
''
'ScaleChart的测试程序
Sub test1()
Dim ws As Worksheet
Dim oChart As ChartObject
设置ws = ActiveSheet
设置oChart = ws.ChartObjects(Diagramm 4 )
如果ScaleChart(oChart)然后
MsgBox轴现在相等,vbInformation,成功
Else
MsgBox发生错误 vbCritical,Failed
End If
End Sub
''
'ScaleChart - 设置对xy-图表
'
'o绘制图表对象
'
'borderLeft(可选)绘图区域和图表之间的左边界是
'borderRight(可选)绘图区域之间的右边界和图表ares
'borderTop(可选)绘图区域和图表之间的顶部边框是
'borderBottom(可选)绘图区域和图表之间的底部边框
'
'返回true成功,任何错误的错误
'
函数ScaleChart(oChart As ChartObject,_
可选borderLeft As Double = BORDER_LEFT,可选borderRight As Double = BORDER_RIGHT,_
可选borderTop As Double = BORDER_TOP,可选borderBottom As Double = BORDER_BOTTOM)As Boolean
'
'变量
'
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
'''
''显示currend边框设置
'Debug.PrintConst BORDER_LEFT =; .PlotArea.Left
'Debug.PrintConst BORDER_RIGHT =; .ChartArea.Width - .PlotArea.Width - .PlotArea.Left
'
'Debug.PrintConst BORDER_TOP =; .PlotArea.Top
'Debug.PrintConst BORDER_BOTTOM =; .ChartArea.Height - .PlotArea.Height - .PlotArea.Top
'
'''
''
'将绘图区域重置为全大小
'
.PlotArea.Left = BORDER_LEFT
.PlotArea.Width = .ChartArea.Width - .PlotArea.Left - BORDER_RIGHT
.PlotArea.Top = BORDER_TOP
.PlotArea.Height = .ChartArea.Height - .PlotArea.Top - BORDER_BOTTOM
'
''
''
'获取轴最小/最大值和当前显示大小
'
xMin = .axes(xlCategory).MinimumScale
xMax = .axes(xlCategory).MaximumScale
xWidth = .PlotArea。宽度
yMin = .axes(xlValue).MinimumScale
yMax = .axes(xlValue).MaximumScale
yHeight = .PlotArea.Height
结束用
sc aleX =(xMax - xMin)/ xWidth
scaleY =(yMax - yMin)/ yHeight
''
''比例信息
''
'Debug.Print'x轴:M 1:; 1 / scaleX,y-axis:M 1:; 1 / scaleY
如果1 / scaleX> 1 / scaleY然后
'
'图必须在x方向减少
'
oChart.Chart.PlotArea.Width = oChart.Chart.PlotArea.Width *( 1 / scaleY)/(1 / scaleX)
Else
'
'图必须在y方向
'
oChart.Chart.PlotArea上减少。 Height = oChart.Chart.PlotArea.Height *(1 / scaleX)/(1 / scaleY)
如果
'
'没有错误 - 返回true
'
ScaleChart = True
错误GoTo 0
退出函数
mark_err:
'
'错误 - 返回false
'
ScaleChart = False
错误GoTo 0
结束函数
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!
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
这篇关于1比1的图表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!