下面是一个UDF，它的功能与FILTER()类似

'Filter range `rng` according one or more tests and return all
'  rows which pass all the tests.
'Example usage:
'   =myfilter(A3:K10,F3:F10="fred")             one test
'   =myfilter(A3:K10,F3:F10="fred",H3:H10="y")  two tests
Function MyFilter(rng As Range, ParamArray tests())
    Dim rw As Range, i As Long, n As Long, c As Long, rOut As Long, arr, arrout()
    Dim arrOK() As Boolean, t As Long, anyRows As Boolean
    
    arr = rng.Value  'all input data as array
    ReDim arrOK(1 To UBound(arr, 1))
    
    'Find which rows satisfy all the tests
    'Each item in `tests()` is a 2-d array of True/False values
    For i = 1 To UBound(arr, 1)
        arrOK(i) = True 'defaults to true
        For t = 0 To UBound(tests)
            If Not tests(t)(i, 1) Then
                arrOK(i) = False 'flag skip row
                Exit For         'stop testing
            End If
        Next t
        If arrOK(i) = True Then n = n + 1 'increase row count if all tests passed
    Next i
    
    'if any good rows, populate the array for output
    If n > 0 Then
        ReDim arrout(1 To n, 1 To UBound(arr, 2))
        For i = 1 To UBound(arr)
            If arrOK(i) Then  'this row passed all tests?
                rOut = rOut + 1
                For c = 1 To UBound(arr, 2)
                    arrout(rOut, c) = arr(i, c)
                Next c
            End If
        Next i
    End If
    MyFilter = IIf(n > 0, arrout, "No rows")
End Function

字符串