Excel VBA 中具有各种 Y 值和一个 X 值的图形

Graphs with various Y values and one X values in Excel VBA

提问by user1270123

This is the code i use to create a graph which searches for .csv {created using excel application} file in the path specified. It plots the column 'B' { Y axis } against column 'C' {X-axis}.. I want to one more column 'A' to my Y axis keeping column 'C' as the X axis.. How can i do that???

这是我用来创建图形的代码，该图形在指定的路径中搜索 .csv {created using excel application} 文件。它针对列 'C' {X 轴} 绘制列 'B' { Y 轴} .. 我想在我的 Y 轴上再添加一列 'A'，将列 'C' 作为 X 轴.. 我怎么能去做？？？

here is the code...

这是代码...

Sub Draw_Graph()
    Dim strPath As String
    Dim strFile As String
    Dim strChart As String
    Dim i As Integer
    Dim j As Integer

    strPath = "C:\PortableRvR\report\"
    strFile = Dir(strPath & "*.csv")
    i = 1

    Do While strFile <> ""
        With ActiveWorkbook.Worksheets.Add
            With .QueryTables.Add(Connection:="TEXT;" & strPath & strFile, _
                Destination:=.Range("A1"))
                Parent.Name = Replace(strFile, ".csv", "")
                TextFileParseType = xlDelimited
                TextFileTextQualifier = xlTextQualifierDoubleQuote
                TextFileConsecutiveDelimiter = False
                TextFileTabDelimiter = False
                TextFileSemicolonDelimiter = False
                TextFileCommaDelimiter = True
                TextFileSpaceDelimiter = False
                TextFileColumnDataTypes = Array(1)
                TextFileTrailingMinusNumbers = True
                Refresh BackgroundQuery:=False
                Files(i) = .Parent.Name
                i = i + 1
            End With
        End With
        strFile = Dir
    Loop

    numOfFiles = i - 1
    chartName = "Chart 1"

    For j = 1 To numOfFiles
        strFile = Files(j)
        Sheets(strFile).Select
        Plot_y = Range("B1", Selection.End(xlDown)).Rows.Count
        Plot_x = Range("C1", Selection.End(xlDown)).Rows.Count

        Sheets("GraphDisplay").Select
        If j = 1 Then ActiveSheet.ChartObjects(chartName).Activate
        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.SeriesCollection(j).Name = strFile
        ActiveChart.SeriesCollection(j).XValues = Sheets(strFile).Range("C1:C" & Plot_x)
        ActiveChart.SeriesCollection(j).Values = Sheets(strFile).Range("B1:B" & Plot_y)
        ActiveChart.SeriesCollection(j).MarkerStyle = -4142
        ActiveChart.SeriesCollection(j).Smooth = False
    Next j

    ActiveSheet.ChartObjects(chartName).Activate
    ActiveChart.Axes(xlValue).DisplayUnit = xlMillions
    ActiveChart.Axes(xlValue).HasDisplayUnitLabel = False
End Sub

采纳答案by Aprillion

you can add 2 series for every file (j and j+1 inside for j = 1 to 2*numOfFiles step 2) and repeat everything for j+1 series except:

您可以为每个文件（里面的 j 和 j+1 for j = 1 to 2*numOfFiles step 2）添加 2 个系列，并为 j+1 系列重复所有内容，除了：

ActiveChart.SeriesCollection(j).Values = Sheets(strFile).Range("A1:A" & Plot_y)
ActiveChart.SeriesCollection(j+1).Values = Sheets(strFile).Range("B1:B" & Plot_y)

回答by Siddharth Rout

Not for points

不是为了积分

I was planning to post this as a comment (and hence do not select this as an answer. All credit to @Aprillion) but the comment would not have formatted the code as this post would have done.

我打算将此作为评论发布（因此不要选择它作为答案。所有功劳都归功于 @Aprillion）但评论不会像这篇文章那样格式化代码。

Whenever you add a series as Aprillion mentioned you have to also add one more line. I just tested this with small piece of data and it works.

每当您像 Aprillion 提到的那样添加一个系列时，您还必须再添加一行。我只是用一小段数据测试了它并且它有效。

'<~~ You have to call this everytime you add a new series
ActiveChart.SeriesCollection.NewSeries 
ActiveChart.SeriesCollection(1).Values = "=Sheet1!$B:$B"
'<~~ You have to call this everytime you add a new series
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Values = "=Sheet1!$A:$A"

Also since there is a huge difference between your Series 1 Data and Series 2 data (as per the snapshot), the 2nd series will be very close to X Axis.

此外，由于您的系列 1 数据和系列 2 数据之间存在巨大差异（根据快照），第二系列将非常接近 X 轴。

Hope this is what you wanted?

希望这是你想要的吗？

FOLLOWUP

跟进

Is this what you are trying?

这是你正在尝试的吗？

Dim files(1 To 20) As String
Dim numOfFiles As Integer
Dim chartName As String, shName as String

Sub Time_Graph()
    Dim strPath As String, strFile As String, strChart As String
    Dim i As Long, j As Long, n As Long

    strPath = "C:\PortableRvR\report\"
    strFile = Dir(strPath & "*.csv")

    i = 1

    Do While strFile <> ""
        With ActiveWorkbook.Worksheets.Add
            shName = strFile
            ActiveSheet.Name = Replace(shName, ".csv", "")
            With .QueryTables.Add(Connection:="TEXT;" & strPath & strFile, _
                Destination:=.Range("A1"))
                .Name = Replace(strFile, ".csv", "")
                .TextFileParseType = xlDelimited
                .TextFileTextQualifier = xlTextQualifierDoubleQuote
                .TextFileConsecutiveDelimiter = False
                .TextFileTabDelimiter = False
                .TextFileSemicolonDelimiter = False
                .TextFileCommaDelimiter = True
                .TextFileSpaceDelimiter = False
                .TextFileColumnDataTypes = Array(1)
                .TextFileTrailingMinusNumbers = True
                .Refresh BackgroundQuery:=False
                files(i) = .Parent.Name
                i = i + 1
            End With
        End With
        strFile = Dir
    Loop

    numOfFiles = i - 1
    chartName = "Chart 1"

    For j = 1 To numOfFiles
        If n = 0 Then n = j Else n = n + 2
        strFile = files(j)
        Sheets(strFile).Select
        Plot_y = Range("B1", Selection.End(xlDown)).Rows.Count
        Plot_x = Range("C1", Selection.End(xlDown)).Rows.Count

        Sheets("GraphDisplay").Select

        If j = 1 Then ActiveSheet.ChartObjects(chartName).Activate

        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.SeriesCollection(n).Name = strFile & " - Col B Values"
        ActiveChart.SeriesCollection(n).XValues = "=" & strFile & "!$C:$C$" & Plot_x
        ActiveChart.SeriesCollection(n).Values = "=" & strFile & "!$B:$B$" & Plot_y

        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.SeriesCollection(n + 1).Name = strFile & " - Col A Values"
        ActiveChart.SeriesCollection(n + 1).XValues = "=" & strFile & "!$C:$C$" & Plot_x
        ActiveChart.SeriesCollection(n + 1).Values = "=" & strFile & "!$A:$A$" & Plot_y

        ActiveChart.SeriesCollection(j).MarkerStyle = -4142
        ActiveChart.SeriesCollection(j).Smooth = False
        ActiveChart.SeriesCollection(n + 1).MarkerStyle = -4142
        ActiveChart.SeriesCollection(n + 1).Smooth = False
    Next j

    ActiveSheet.ChartObjects(chartName).Activate
    ActiveChart.Axes(xlValue).DisplayUnit = xlMillions
    ActiveChart.Axes(xlValue).HasDisplayUnitLabel = False
End Sub