过滤二维数组在Excel VBA [英] Filtering 2D Arrays in Excel VBA
问题描述
使用Excel和VBA，我在数组（在相同的排序方式，您可能使用数据透视表）严格使用VBA想就如何最好地过滤数据的一些建议。我创建将会使基于现有数据的一些数据决策的窗体。我可以想像怎么做的不够好，但我不认为在VBA编程精通。
Using Excel and VBA, I wanted some advice on how to best filter data in an array (in the same sort of way one might use a pivot table) strictly using VBA. I am creating a UserForm that is going to make some data decisions based on currently existing data. I can visualize how to do it well enough but am not that versed in VBA programming.
下面是一个例子。
A B C
bob 12 Small
sam 16 Large
sally 1346 Large
sam 13 Small
sally 65 Medium
bob 1 Medium
要抢在阵列中的数据，我可以使用
To grab the data in an Array, I could use
Dim my_array As Variant
my_array = Range("A1").CurrentRegion
现在，我所熟悉的通过二维数组循环，但我想知道：什么来筛选二维数组数据（不通过阵列时间循环和再）的最有效的方法是什么？
Now, I am familiar with looping through 2D arrays, but I wondered: what the most effective way to filter 2D array data (without looping through the array time and again)?
例如，我如何才能是说得到这样的数据：
For example, how do I get would be to say get this kind of data:
data_for_sally As Variant 'rows with sally as name in ColA
data_for_sally_less_than_ten As Variant ' all rows with sally's name in ColA and colB < 10
data_for_all_mediums as Variant ' all rows where ColC is Medium
建议？我可以工作了这一点，带着一帮自定义功能和循环，但我认为必须有一个更好的办法。谢谢你。
Suggestions? I could work this out with a bunch of custom functions and loops but I thought there must be a better way. Thanks.
推荐答案
我假设你想使用VBA只。
I assume you want to use VBA only.
我认为这取决于几个参数，主要是：
I think it depends on several parameters, mainly on:
- 您运行的频率相同的条件下=>你存储过滤器的结果，或者你重新计算每一次？
- 您需要经常过滤的东西=>如果经常，这是值得拥有的地方适当的code结构，如果没有那么一关环显然是要走的路。
从面向对象的角度来看，假设性能（速度和安培;内存）是不是一个问题，我会去下面的设计（我不会进入实施的细节，只给一般的想法）。创建一个类（姑且称之为想象力ArrayFilter），你可以使用这个样子。
From an OO perspective, assuming performance (speed & memory) is not an issue, I would go for the following design (I won't go into the details of the implementation, only give the general idea). Create a class (let's call it imaginatively ArrayFilter) that you could use like this.
设置过滤器
Dim filter As New ArrayFilter
With filter
.name = "sam"
.category = "Medium"
.maxValue = 10
End With
或者
filter.add(1, "sam") 'column 1
filter.add(3, "Medium") 'column 3
filter.addMax(2, 10) 'column 2
创建过滤的数据集
filteredArray = getFilteredArray(originalArray, filter)
该getFilteredArray是相当简单的写：你循环数组检查，如果该值匹配的过滤器，把有效线路在新的阵列上：
The getFilteredArray is fairly straightforward to write: you loop over the array checking if the values match the filter and put the valid lines in a new array:
If filter.isValidLine(originalArray, lineNumber) Then 'append to new array
赞成
- 清洁的设计
- 可重复使用，尤其是在您使用的列数的第二个版本。这可以用来真正过滤任何阵列。
- 过滤code是一个函数，您可以测试
- 推论：避免code重复
缺点
- 过滤重新计算每一次，即使您使用相同的过滤器的两倍。您可以将结果存储在一个字典 - 例如，见下文
- 内存：到getFilteredArray每次调用创建新的数组，但不知道如何能够避免反正
- 这增加了code的不少线路，所以只有当它有助于使code更容易阅读/维护我会做到这一点。
PS：如果需要缓存结果来提高性能，一种方法是用于存储在字典中的结果，并添加一些逻辑到getFilteredArray功能。请注意，除非你的数组是真正的大和/或运行相同的过滤器了很多，这可能是不值得的。
ps: If you need to cache the results to improve performance, one way would be to store the results in a dictionary and add some logic to the getFilteredArray function. Note that unless your arrays are really big and/or you run the same filter a lot, this is probably not worth it.
filters.add filter, filteredArray 'filters is a dictionary
这样的话，当你调用getFilteredArray下一次，你可以做这样的事情：
That way, when you call getFilteredArray next time, you can do something like this:
For each f in filters
'Check if all conditions in f and newFilter are the same
'If they are:
getFilteredArray = filters(f)
Exit Function
Next
'Not found in cache: compute the result
这篇关于过滤二维数组在Excel VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
