AutoFilter可以从“字典”键中获取包容性和非包容性通配符吗？ [英] Can AutoFilter take both inclusive and non-inclusive wildcards from Dictionary keys?

问题描述

我一直在寻找一种方法来过滤具有两个以上通配符的Excel电子表格。如果我可以直接在VBA中将多个两个通配符放入AutoFilter中，而不是在工作表中使用高级过滤器，我以前问过StackOverflow，因为我们的宏主要通过PowerShell脚本使用，该脚本会传递输入。这些通配符用于过滤各种电子表格，并保存结果。

I have been looking for a way to filter an Excel spreadsheet with more than two wildcards. I asked on StackOverflow previously if I could put more than two wildcards in to AutoFilter in VBA directly instead of using advanced filter in the worksheet, as my macros are mostly used via PowerShell scripts, which pass input through. These wildcards are used to filter various spreadsheets and the result is saved.

一个非常有用的用户提出了一个答案，给出了一个使用字典键的示例宏，然后我再扩展接受数组作为输入，然后循环遍历数组中的所有项以过滤为通配符。非常好，按照预期工作！

A very helpful user came up with an answer gave an example macro using Dictionary keys, which I then extended to accept an array as input, and then loop through all the items in the array to filter as wildcards. Excellent, working as intended!

现在我想扩展这个，以传递更多具体的通配符，我想要排除。例如，我想要包括A *和B *，但不包括BB *，以便BA *仍然存在。下面的宏可以通过<> BB *通过？

Now I want to extend this to pass more specific wildcards I want to exclude. Say for example, I want to include "A*" and "B*", but not "BB*", so that "BA*" would still be there, for example. Could the below macro work with maybe passing <>BB* through?

hierArray只包含一个包含最多10个（但很少超过3个字符）

The hierArray only ever contains a list of simple strings consisting of a maximum 10 (but rarely more than 3 characters).

Public Function multiHier(hierArray As Variant)

Dim v As Long, vVALs As Variant, dVALs As Object
Dim colNum As Long, hierLen As Integer, hier As Variant
Dim rng As Range

Set dVALs = CreateObject("Scripting.Dictionary")
dVALs.comparemode = vbTextCompare
colNum = Application.Match("*ierarchy*", Range("A1:Z1"), 0)

With Worksheets(1)
    'If .AutoFilterMode Then .AutoFilterMode = False

    With .Cells(1, 1).CurrentRegion
        vVALs = .Columns(colNum).Cells.Value2

        For v = LBound(vVALs, 1) To UBound(vVALs, 1)
            If Not dVALs.exists(vVALs(v, 1)) Then

                For Each hier In hierArray
                    hierLen = Len(hier)

                    Select Case UCase(Left(vVALs(v, 1), hierLen))
                        Case hier
                            dVALs.Add Key:=vVALs(v, 1), item:=vVALs(v, 1)
                        Case Else
                   End Select

               Next hier
            End If
        Next v

        If CBool(dVALs.Count) Then
            'populated the dictionary; now use the keys
            .AutoFilter Field:=colNum, Criteria1:=dVALs.keys, Operator:=xlFilterValues

            Set rng = Worksheets(1).AutoFilter.Range
            multiHier = rng.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
        Else
            multiHier = 0
        End If

    End With

End With

dVALs.RemoveAll: Set dVALs = Nothing

End Function

推荐答案

我将坚持使用丢弃物的！前缀，因为这是一个字符。

I'm going to stick to the ! prefix for the discards as that is a single character.

    Dim h As Long, hstr As String   'put these at the top with the other var declarations

    For v = LBound(vVALs, 1) To UBound(vVALs, 1)
        For h = LBound(hierArray) To UBound(hierArray)   'I just prefer to work this way
            hstr = hierArray(h) & Chr(42)   'stick a * on the end
            If Left(hstr, 1) = Chr(33) And LCase(vVALs(v, 1)) Like LCase(Mid(hstr, 2)) Then     'starts with a ! and pattern matches the value
                'matched a discard pattern. check to see if it was previously added
                If dVALs.Exists(vVALs(v, 1)) Then _
                    dVALs.Remove vVALs(v, 1)    'get rid of it
                Exit For  'discarded. do not keep checking to add
            ElseIf LCase(vVALs(v, 1)) Like LCase(hstr) Then
                If NOT dVALs.Exists(vVALs(v, 1)) Then _
                    dVALs.Add Key:=vVALs(v, 1), Item:=vVALs(v, 1)
            End If
        Next h
    Next v

创建 hierArray 字符串时，可以先放弃丢弃模式来保存几个周期。这样，他们就不会被添加，然后被删除。

When creating the hierArray string, you can save a few cycles by putting the discard patterns first. That way, they will not get added and then subsequently removed.

这方面的任何进一步的工作都有可能保证切换到一个完整的正则表达式 （正则表达式）模式匹配方法。

Any further work in this areas would likely warrant switching to a full Regular Expression (regexp) pattern matching method.

这篇关于AutoFilter可以从“字典”键中获取包容性和非包容性通配符吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！