在 Excel VBA 中过滤二维数组

Filtering 2D Arrays in Excel VBA

提问by thornomad

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.

使用 Excel 和 VBA，我想就如何严格使用 VBA 最好地过滤数组中的数据（以与使用数据透视表相同的方式）提供一些建议。我正在创建一个用户窗体，它将根据当前现有的数据做出一些数据决策。我可以想象如何做得足够好，但我并不精通 VBA 编程。

Here is an example

这是一个例子

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

现在，我熟悉遍历 2D 数组，但我想知道：过滤 2D 数组数据的最有效方法是什么（无需一次又一次地遍历数组）？

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.

建议？我可以用一堆自定义函数和循环来解决这个问题，但我认为必须有更好的方法。谢谢。

回答by assylias

I assume you want to use VBA only.

我假设您只想使用 VBA。

I think it depends on several parameters, mainly on:

我认为这取决于几个参数，主要是：

• how often you run the same condition => do you store the result of a filter or do you recalculate every time?
• how often you need to filter stuff => if often, it is worth having a proper code structure in place, if not then a one off loop is clearly the way to go.

• 你多久运行一次相同的条件 => 你是存储过滤器的结果还是每次都重新计算？
• 你需要多久过滤一次 => 如果经常，有一个适当的代码结构是值得的，如果没有，那么一次性循环显然是要走的路。

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.

从面向对象的角度来看，假设性能（速度和内存）不是问题，我会进行以下设计（我不会深入实现的细节，只给出大致的想法）。创建一个您可以像这样使用的类（让我们想象一下称之为 ArrayFilter）。

Setup the filter

设置过滤器

Dim filter As New ArrayFilter
With filter
    .name = "sam"
    .category = "Medium"
    .maxValue = 10
End With

Or

或者

filter.add(1, "sam") 'column 1
filter.add(3, "Medium") 'column 3
filter.addMax(2, 10) 'column 2

Create the filtered data set

创建过滤数据集

filteredArray = getFilteredArray(originalArray, filter)

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:

getFilteredArray 编写起来相当简单：遍历数组检查值是否与过滤器匹配，并将有效行放入新数组中：

If filter.isValidLine(originalArray, lineNumber) Then 'append to new array

Pros

优点

• Clean design
• Reusable, especially with the second version where you use the column number. This can be used to filter any arrays really.
• Filtering code is in one function that you can test
• Corollary: avoid duplication of code

• 干净的设计
• 可重用，尤其是在使用列号的第二个版本中。这可用于真正过滤任何数组。
• 过滤代码位于您可以测试的一个函数中
• 推论：避免代码重复

Cons

缺点

• Filtering is recalculated every time, even if you use the same filter twice. You can store the results in a Dictionary for example - see below.
• Memory: every call to the getFilteredArray creates a new array, but not sure how this can be avoided anyway
• This adds quite a few lines of code, so I would do it only if it helps make the code easier to read / maintain.

• 过滤每次都会重新计算，即使您使用相同的过滤器两次。例如，您可以将结果存储在字典中 - 见下文。
• 内存：每次调用 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.

ps：如果您需要缓存结果以提高性能，一种方法是将结果存储在字典中并向 getFilteredArray 函数添加一些逻辑。请注意，除非您的数组非常大和/或您经常运行相同的过滤器，否则这可能不值得。

filters.add filter, filteredArray 'filters is a dictionary

That way, when you call getFilteredArray next time, you can do something like this:

这样，当您下次调用 getFilteredArray 时，您可以执行以下操作：

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

回答by ??c Thanh Nguy?n

Try this

尝试这个

' credited to ndu
Function Filter2DArray(ByVal sArray, ByVal ColIndex As Long, ByVal FindStr As String, ByVal HasTitle As Boolean)
  Dim tmpArr, i As Long, j As Long, Arr, Dic, TmpStr, Tmp, Chk As Boolean, TmpVal As Double
  On Error Resume Next
  Set Dic = CreateObject("Scripting.Dictionary")
  tmpArr = sArray
  ColIndex = ColIndex + LBound(tmpArr, 2) - 1
  Chk = (InStr("><=", Left(FindStr, 1)) > 0)
  For i = LBound(tmpArr, 1) - HasTitle To UBound(tmpArr, 1)
    If Chk Then
      TmpVal = CDbl(tmpArr(i, ColIndex))
      If Evaluate(TmpVal & FindStr) Then Dic.Add i, ""
    Else
      If UCase(tmpArr(i, ColIndex)) Like UCase(FindStr) Then Dic.Add i, ""
    End If
  Next
  If Dic.Count > 0 Then
    Tmp = Dic.Keys
    ReDim Arr(LBound(tmpArr, 1) To UBound(Tmp) + LBound(tmpArr, 1) - HasTitle, LBound(tmpArr, 2) To UBound(tmpArr, 2))
    For i = LBound(tmpArr, 1) - HasTitle To UBound(Tmp) + LBound(tmpArr, 1) - HasTitle
      For j = LBound(tmpArr, 2) To UBound(tmpArr, 2)
        Arr(i, j) = tmpArr(Tmp(i - LBound(tmpArr, 1) + HasTitle), j)
      Next
    Next
    If HasTitle Then
      For j = LBound(tmpArr, 2) To UBound(tmpArr, 2)
        Arr(LBound(tmpArr, 1), j) = tmpArr(LBound(tmpArr, 1), j)
      Next
    End If
  End If
  Filter2DArray = Arr
End Function