Excel VBA中具有各种Y值和一个X值的图形 [英] Graphs with various Y values and one X values in Excel VBA

问题描述

这是我用来创建图形的代码，该图形在指定的路径中搜索.csv {使用excel应用程序创建}文件.它将列"B" {Y轴}相对于列"C" {X轴}进行绘制..我想在我的Y轴上再增加一列"A"，同时将列"C"作为X轴.这样做吗?

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???

这是代码...

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

推荐答案

您可以为每个文件添加2个序列(在中j和j + 1内，用于j = 1到2 * numOfFiles步骤2 )并重复j + 1系列的所有内容，除了:

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:

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

这篇关于Excel VBA中具有各种Y值和一个X值的图形的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！