动态地，有条件地将数据集分为多个数组或存储有关拆分位置的信息 [英] Dynamically and condtionally divide a dataset into multiple arrays or store information regarding split locations

问题描述

我有一组在年份"列上排序的数据.我从一小部分开始构建代码，因此大约只有500行.年"列中大约有15个不同的值，我想分别为每个列制作一个图形.为此，我需要知道一年开始于哪一行，结束于哪一行.因此，我要么需要每年将大量数据分为多个单独的数组，要么需要一个包含每年开始和结束行的数组，以便可以在生成图形的代码中引用它们.

I have a set of data that is sorted on the column "Year". I started with a small set to build my code, so there are only around 500 rows. There are about 15 different values in the column "Year" and I want to make a graph for each column separately. To do this I need to know at which row one year starts and on which row it ends. Therefore I either need the large set of data divided into separate arrays per year, or an array that contains the start and end rows per year, so that I can reference them in my code that produces the graphs.

我当时正在考虑编写一个循环，使用以下方法检查年份"列中的行的值是否与上面的值不同:

I was thinking of writing a loop that checks whether or not the value for a row in column "Year" is different from the value above, using something like this:

For row = 3 to TotalRows
    IfNot Cells(row,1).Value = Cells(row-1,1).Value Then
        'Row = startrow current year
        'Cells(row,1).Value = current year
        'Row -1 = endrow previous year
        'Cells(row-1,1).Value = previous year
    End If
Next row

什么是存储这些值的最佳方法，以便以后我可以引用它们?

What would be the best way to store these values, so that I can reference them later?

推荐答案

我刚刚获得了风滚草徽章，回到了这个问题.但是，我最近一直在解决这个问题，因此我想与可能遇到此问题的任何人分享我的答案:

I just got drawn back to this question by earning the tumbleweed badge. However I recently worked my way around this problem, so I thought I might just as well share my answer with anyone that might come across this:

Public MyRange As Range
Public Years() As Variant
Global TotalRows As String

Sub DevideData()

Dim i As Integer

Set MyRange = Range("AL35:AN35") 'output data from Years here for manual control.

ReDim Years(1, 2)

Years(0, 0) = Cells(2, 1).Value
Years(0, 1) = 2

i = 1
ThisWorkbook.Worksheets("Simple Boundary").Activate
TotalRows = ThisWorkbook.Worksheets("Simple Boundary").Range("A100000").End(xlUp).row

For row = 3 To TotalRows 'my data starts in row 2, as row 1 is a header
    Years = ReDimPreserve(Years, i, 2) 'as data starts in row 2 this is the first entry

    If Not Cells(row, 1).Value = Cells(row - 1, 1).Value Then 
'check if cell value differs from row above if so fill a line in the array
        Years(i - 1, 2) = row - 1
        Years(i, 0) = Cells(row, 1).Value
        Years(i, 1) = row
        i = i + 1 'increase the row of the array
    End If
Next row

Years(i - 1, 2) = TotalRows
MyRange.Resize(i, 3) = Years

End Sub

在我的另一个问题这里

这篇关于动态地，有条件地将数据集分为多个数组或存储有关拆分位置的信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！