在 VBA 中用平滑线编码散点图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44361927/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Coding a Scatter plot with smooth lines in VBA
提问by user3126632
I am trying to code for a Scatter Plot using smooth lines with VBA. I am trying to take data off of a worksheet and create a scatter plot with lines and no markers in the same workbook different sheet using VBA.
我正在尝试使用带有 VBA 的平滑线为散点图编码。我正在尝试从工作表中取出数据，并使用 VBA 在同一工作簿的不同工作表中创建一个带有线条且没有标记的散点图。
This is part snapshot of my worksheet
这是我的工作表的一部分快照
The values below 247 and between 263 to 455 in column A will have corresponding -1.75 in column B.
A 列中低于 247 和 263 到 455 之间的值将在 B 列中具有相应的 -1.75。
The x values are in range A1:A401
x 值在范围内 A1:A401
y-values are in range B1:B401
y 值在范围内 B1:B401
Also I want to have title to my graph and X and Y axis labelled. I am having trouble figuring how to get the y-values to plot with the x-values instead of excel making two seperate lines on the chart.
另外我想给我的图表加上标题，并标记 X 和 Y 轴。我无法弄清楚如何使用 x 值绘制 y 值而不是 excel 在图表上制作两条单独的线。
This is graph I need
这是我需要的图表
This is the code I have used
这是我使用的代码
Set xData = ThisWorkbook.Worksheets(2).Range("A1:A" & LastRow_this)
Set yData = ThisWorkbook.Worksheets(2).Range("B1:B" & LastRow_this)
Set GraphRange = Union(xData, yData)
'Create a chart
Set cht = ThisWorkbook.Worksheets(1).Shapes.AddChart2
'Give chart some data
cht.Chart.SetSourceData Source:=GraphRange
'Determine the chart type
cht.Chart.ChartType = xlXYScatterLines
This is what it gives me in Excel.
这就是它在 Excel 中给我的。
How can I get the desired result ?
我怎样才能得到想要的结果？
Also what can I do if the range is dynamic ?
如果范围是动态的，我该怎么办？
回答by teylyn
With a scatter chart you don't want to use "GraphRange" for the whole chart. Depending on the data and the phase of the moon, Excel will try to plot the X and the Y data as individual series, which is not what you want.
对于散点图，您不想对整个图表使用“GraphRange”。根据数据和月相，Excel 会尝试将 X 和 Y 数据绘制为单独的系列，这不是您想要的。
Instead, edit or insert each series separately and set the range for the X and the Y values. Also you need xlXYScatterSmoothNoMarkersas the chart type.
相反，单独编辑或插入每个系列并设置 X 和 Y 值的范围。您还需要xlXYScatterSmoothNoMarkers作为图表类型。
Try using the macro recorder to select the range, add a scatter chart with smoothed lines. Then inspect the code. This will give you valuable pointers about the changes you need to make to your code.
尝试使用宏记录器选择范围，添加带有平滑线的散点图。然后检查代码。这将为您提供有关您需要对代码进行的更改的宝贵指示。
回答by Subodh Tiwari sktneer
You may try something like this...
你可以试试这样的...
Sub CreateChart()
Dim wsData As Worksheet, wsChart As Worksheet
Dim LastRow As Long
Dim xData As Range, yData As Range, GraphRange As Range
Dim cht As Shape
Application.ScreenUpdating = False
Set wsChart = Sheets(1)
Set wsData = Sheets(2)
LastRow = wsData.Cells(Rows.Count, 1).End(xlUp).Row
Set xData = ThisWorkbook.Worksheets(2).Range("A1:A" & LastRow)
Set yData = ThisWorkbook.Worksheets(2).Range("B1:B" & LastRow)
Set GraphRange = Union(xData, yData)
'Create a chart
Set cht = ThisWorkbook.Worksheets(1).Shapes.AddChart2(, xlXYScatterLinesNoMarkers)
'Give chart some data
cht.Chart.SetSourceData Source:=GraphRange
cht.Chart.FullSeriesCollection(1).Format.Line.Weight = 5
Application.ScreenUpdating = True
End Sub
回答by Dy.Lee
My code is
我的代码是
Sub setChart()
Dim LastRow_this As Long
Dim Ws As Worksheet, chtWs As Worksheet
Dim xData As Range, yData As Range
Dim Cht As Chart
Set Ws = ThisWorkbook.Worksheets(2)
Set chtWs = ThisWorkbook.Worksheets(1)
With Ws
LastRow_this = .Range("a" & Rows.Count).End(xlUp).Row
Set xData = .Range("A1:A" & LastRow_this)
Set yData = .Range("B1:B" & LastRow_this)
End With
Set Cht = chtWs.Shapes.AddChart.Chart
With Cht
.ChartType = xlXYScatterLinesNoMarkers
.HasLegend = False
.SeriesCollection.NewSeries
With .SeriesCollection(1)
.XValues = xData
.Values = yData
End With
.Axes(xlCategory).MajorUnit = 50
.Axes(xlCategory).HasMajorGridlines = True
.Axes(xlValue).HasMajorGridlines = True
.Axes(xlCategory).MaximumScale = 460
.Axes(xlCategory).MinimumScale = 50
End With
End Sub
