Excel VBA：图表制作宏，将循环通过唯一的名称组并创建相应的图表？ [英] Excel VBA: Chart-making macro that will loop through unique name groups and create corresponding charts?

问题描述

好吧，我一直在欺骗我的大脑，阅读excel编程的假人，并查看所有的地方，但我强调这个小问题，我在这里。我是vba编程的新手，或者真正的任何编程语言，但我尽力得到一个处理。

Alright, I've been racking my brain, reading up excel programming for dummies, and looking all over the place but I'm stressing over this little problem I have here. I'm completely new to vba programming, or really any programming language but I'm trying my best to get a handle on it.

场景和我的目标是：

下面的图片是我从不同流站的一个庞大的长数据列表的例子。样本只有两个（niobrara和蛇）来说明我的问题，但在现实中，我有一个超过80站的数据，每个变化的压力时期的数量（COLUMN B）。

The picture below is a sample of a huge long list of data I have from different stream stations. The sample only holds two (niobrara and snake) to illustrate my problem, but in reality I have a little over 80 stations worth of data, each varying in the amount of stress periods (COLUMN B).

COLUMN A，是站名称列。
COLUMN B，压力期号码
COLUMN C，建模率
COLUMN D，估计费率

COLUMN A, is the station name column. COLUMN B, stress period number COLUMN C, modeled rate COLUMN D, estimated rate

out是如何使一个宏循环通过车站名称（COLUMN A）和每个UNIQUE组的车站名称，使一个图表将弹出在组的权利，例如在COLUMN E区域。

What I have been TRYING to figure out is how to make a macro that will loop through the station names (COLUMN A) and for each UNIQUE Group of station names, make a chart that will pop out to the right of the group, say in the COLUMN E area.

图表是完全简单的，它只需要两个系列的散点图/折线图;一个系列，COLUMN B为x值，COLUMN C为y值;而其他系列需要COLUMN B作为x值，COLUMN D作为y值。

The chart is completely simple, it just needs two series scatterplot/line chart; one series with COLUMN B as x-value and COLUMN C as y-value; and the other series needs COLUMN B as x-value and COLUMN D as y-value.

现在我的主要考验是，我不知道如何使宏区分车站名称，使用所有与该名称相关的数据来制作图表，然后循环到下一个站组，并创建一个对应的图表，并继续循环通过COLUMN A中的所有80+站名，并使相应的图表在它的右边，如COLUMN E 。

Now my main ordeal, is that I don't know how to make the macro distinguish between station names, use all the data relating to that name to make the chart, then looping on to the next Station group and creating a chart that corresponds for that, and to continue looping through all 80+ station names in COLUMN A and to make the corresponding 80+ charts to the right of it all in somewhere like the COLUMN E.

如果我有足够的分数来赏金，我会在心跳。但是，既然我没有，谁能解决我的困境将收到我真诚的感谢，帮助我理解这个问题顺利运行，希望更好地了解这样的场景在未来。如果有任何更多的信息，我需要澄清，使我的问题更容易理解，请评论您的查询，我很乐意解释更详细的主题。

If I had enough points to "bounty" this, I would in a heartbeat. But since I do not, whoever can solve my dilemma would receive my sincere gratitude in helping me understand run this problem smoothly and hopefully better my understanding of scenarios like this in the future. If there is anymore information that I need to clarify to make my question more understandable please comment your query and I'd be happy to explain in more detail the subject.

干杯。

哦，额外的信用;现在我想到了，我手动输入COLUMN B中的数字。由于循环将需要使用该列作为x值，它将是重要的，如果它可以循环通过自己和填充该列自己在它之前图表（我想象它会与任何简单的事情有关，计数对应的车站名称的行。但是，我不知道对应车站名称的正确的术语，因此，但是如果经验丰富的程序员能够很好地回答这个问题，我可以想象这样的一段代码将足够简单，但对于我所寻求的宏的成功至关重要。

Oh, and for extra credit; now that I think about it, I manually entered the numbers in COLUMN B. Since the loop would need to use that column as the x-value it would be important if it could loop through itself and fill that column on its own before it made the chart (I would imagine it would have something to do with anything as simple as "counting out the rows that correspond to the station name". But again, I know not the proper terminology to correspond the station name, hence the pickle I'm in; however if the veteran programmer who is savvy enough to answer this question could, I'd imagine such a piece of code would be simple enough yet crucial to the success of such a macro I seek.

推荐答案

尝试此

Sub MakeCharts()
    Dim sh As Worksheet
    Dim rAllData As Range
    Dim rChartData As Range
    Dim cl As Range
    Dim rwStart As Long, rwCnt As Long
    Dim chrt As Chart

    Set sh = ActiveSheet

    With sh
        ' Get reference to all data
        Set rAllData = .Range(.[A1], .[A1].End(xlDown)).Resize(, 4)
        ' Get reference to first cell in data range
        rwStart = 1
        Set cl = rAllData.Cells(rwStart, 1)
        Do While cl <> ""
            ' cl points to first cell in a station data set
            ' Count rows in current data set
            rwCnt = Application.WorksheetFunction. _
               CountIfs(rAllData.Columns(1), cl.Value)
            ' Get reference to current data set range
            Set rChartData = rAllData.Cells(rwStart, 1).Resize(rwCnt, 4)
            With rChartData
                ' Auto fill sequence number
                .Cells(1, 2) = 1
                .Cells(2, 2) = 2
                .Cells(1, 2).Resize(2, 1).AutoFill _
                   Destination:=.Columns(2), Type:=xlFillSeries
            End With
            ' Create Chart next to data set
            Set chrt = .Shapes.AddChart(xlXYScatterLines, _
               rChartData.Width, .Range(.[A1], cl).Height).Chart
            With chrt
                .SetSourceData Source:=rChartData.Offset(0, 1).Resize(, 3)
                ' --> Set any chart properties here

                ' Add Title
                .SetElement msoElementChartTitleCenteredOverlay
                .ChartTitle.Caption = cl.Value

                ' Adjust plot size to allow for title
                .PlotArea.Height = .PlotArea.Height - .ChartTitle.Height
                .PlotArea.Top = .PlotArea.Top + .ChartTitle.Height

                ' Name series'
                .SeriesCollection(1).Name = "=""Modeled"""
                .SeriesCollection(2).Name = "=""Estimated"""

                ' turn off markers
                .SeriesCollection(1).MarkerStyle = -4142 
                .SeriesCollection(2).MarkerStyle = -4142 

            End With

            ' Get next data set
            rwStart = rwStart + rwCnt
            Set cl = rAllData.Cells(rwStart, 1)
        Loop

    End With

End Sub

这篇关于Excel VBA：图表制作宏，将循环通过唯一的名称组并创建相应的图表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！