Excel - 复杂索引匹配以查找多个值

Question 1

好吧，我接受了 VBA 的挑战。我对你想要什么有点困惑例外存在，所以目前如果出现异常，它将完全跳过该组。如果这不是您想要的行为，请告诉我。您可能是对的，公式存在，并且可能让这个看起来愚蠢地过度设计。希望它能帮助某人！

Sub CalculateList()
    Dim startRowCount As Integer: startRowCount = 2     'Row to start on
    Dim columnId As Integer: columnId = 1               'Column containing ID
    Dim OutputColumn As Integer: OutputColumn = 7       'Column to output into

    '------ Begin

    Cells(1, OutputColumn).Select                       'Selects the output cell, incase there are any successes

    Dim rowCount: rowCount = startRowCount              'Sets our start row count for the loop
    Dim RowsContainingId()                              'Array containing the row ID's matching the current ID of the loop
    ReDim RowsContainingId(1)
    Dim CompletedIds()                                  'Array containing the ID's that have already been done
    ReDim CompletedIds(1)

    Do                                                  'Begin loop
nextNumberStart:                                        'Label to go back to if we hit an already completed ID
        Dim CurrentID As String: CurrentID = Cells(rowCount, columnId).Value            'Get the CurrentID of this loop
        If (CurrentID = "") Then                        'If we hit a blank, we're done, so...
            Exit Do                                     'Exit the loop
        End If
        If (IsInArray(CurrentID, CompletedIds)) Then    'Check if this ID has already been handled
            rowCount = rowCount + 1                     'It has, so select the next row
            GoTo nextNumberStart:                       '...And begin the loop again
        End If

        GetAllMatchingRowIds CurrentID, RowsContainingId, startRowCount, columnId   'Runs sub lower down to populate the array with all the rows with this ID
        If (TestIdMeetsCriteria(RowsContainingId)) Then     'Test these rows to see if it meets your criteria (function below)
            ActiveCell.Value = CurrentID                'It's a success, so write this ID in our active cell
            ActiveCell.Offset(1, 0).Activate            'And select the cell one row down, ready to write again if needed
        End If


        CompletedIds(UBound(CompletedIds) - 1) = CurrentID      'Add this ID to our completed list
        ReDim Preserve CompletedIds(UBound(CompletedIds) + 1)   'Grow our array one more ready for next completed item

        Erase RowsContainingId                          'Wipe out our Matching ID array for next loop
        ReDim RowsContainingId(1)
        rowCount = rowCount + 1
    Loop
End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)           'Checks if a string is in the array
End Function

Function TestIdMeetsCriteria(arr As Variant) As Boolean
    Dim row
    Dim HasException, ValidPlus: HasException = False: ValidPlus = False        'Set our bools to False
    For Each row In arr                                             'Loop through each row containing our ID
        If (LCase(Cells(row, 2).Value) = "plus") Then               'Check for "Plus" (case insensitively)
            If (LCase(Cells(row, 3).Value) = LCase(Cells(row, 4).Value)) Then   'We got Plus, do we have matching Service and Sales (case insensitive)?
                ValidPlus = True                                    'We do. Set ValidPlus to true for comparison later
            End If
        ElseIf (LCase(Cells(row, 2).Value) = "exception") Then      'Now check if any rows contains Exception (case insensitive)
            HasException = True                                     'We do. Set HasException to true for later comparison
        End If
    Next
    TestIdMeetsCriteria = Not (HasException) And ValidPlus          'We finished the loop. If HasException is still false, and ValidPlus is now true, it was a success, otherwise a failure
End Function

Sub GetAllMatchingRowIds(idToFind As String, arr As Variant, startRowId As Integer, columnId As Integer)
    Dim rowCount: rowCount = startRowId
    Do                                                          'Loop through our ID column rows
        If (Cells(rowCount, columnId).Value = idToFind) Then    'Check if the ID is the one we're after
            arr(UBound(arr) - 1) = rowCount                     'It is, add it to our array
            ReDim Preserve arr(UBound(arr) + 1)                 'Grow our array for next lop
        End If
        rowCount = rowCount + 1                                 'Next row
    Loop Until IsEmpty(Cells(rowCount, columnId).Value)         'Keep looping until we hit a blank
    ReDim Preserve arr(UBound(arr) - 2)                         'Remove additional entries in array we don't need
End Sub

结果：

Answer

好吧，我接受了 VBA 的挑战。我对你想要什么有点困惑例外存在，所以目前如果出现异常，它将完全跳过该组。如果这不是您想要的行为，请告诉我。您可能是对的，公式存在，并且可能让这个看起来愚蠢地过度设计。希望它能帮助某人！

Sub CalculateList()
    Dim startRowCount As Integer: startRowCount = 2     'Row to start on
    Dim columnId As Integer: columnId = 1               'Column containing ID
    Dim OutputColumn As Integer: OutputColumn = 7       'Column to output into

    '------ Begin

    Cells(1, OutputColumn).Select                       'Selects the output cell, incase there are any successes

    Dim rowCount: rowCount = startRowCount              'Sets our start row count for the loop
    Dim RowsContainingId()                              'Array containing the row ID's matching the current ID of the loop
    ReDim RowsContainingId(1)
    Dim CompletedIds()                                  'Array containing the ID's that have already been done
    ReDim CompletedIds(1)

    Do                                                  'Begin loop
nextNumberStart:                                        'Label to go back to if we hit an already completed ID
        Dim CurrentID As String: CurrentID = Cells(rowCount, columnId).Value            'Get the CurrentID of this loop
        If (CurrentID = "") Then                        'If we hit a blank, we're done, so...
            Exit Do                                     'Exit the loop
        End If
        If (IsInArray(CurrentID, CompletedIds)) Then    'Check if this ID has already been handled
            rowCount = rowCount + 1                     'It has, so select the next row
            GoTo nextNumberStart:                       '...And begin the loop again
        End If

        GetAllMatchingRowIds CurrentID, RowsContainingId, startRowCount, columnId   'Runs sub lower down to populate the array with all the rows with this ID
        If (TestIdMeetsCriteria(RowsContainingId)) Then     'Test these rows to see if it meets your criteria (function below)
            ActiveCell.Value = CurrentID                'It's a success, so write this ID in our active cell
            ActiveCell.Offset(1, 0).Activate            'And select the cell one row down, ready to write again if needed
        End If


        CompletedIds(UBound(CompletedIds) - 1) = CurrentID      'Add this ID to our completed list
        ReDim Preserve CompletedIds(UBound(CompletedIds) + 1)   'Grow our array one more ready for next completed item

        Erase RowsContainingId                          'Wipe out our Matching ID array for next loop
        ReDim RowsContainingId(1)
        rowCount = rowCount + 1
    Loop
End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)           'Checks if a string is in the array
End Function

Function TestIdMeetsCriteria(arr As Variant) As Boolean
    Dim row
    Dim HasException, ValidPlus: HasException = False: ValidPlus = False        'Set our bools to False
    For Each row In arr                                             'Loop through each row containing our ID
        If (LCase(Cells(row, 2).Value) = "plus") Then               'Check for "Plus" (case insensitively)
            If (LCase(Cells(row, 3).Value) = LCase(Cells(row, 4).Value)) Then   'We got Plus, do we have matching Service and Sales (case insensitive)?
                ValidPlus = True                                    'We do. Set ValidPlus to true for comparison later
            End If
        ElseIf (LCase(Cells(row, 2).Value) = "exception") Then      'Now check if any rows contains Exception (case insensitive)
            HasException = True                                     'We do. Set HasException to true for later comparison
        End If
    Next
    TestIdMeetsCriteria = Not (HasException) And ValidPlus          'We finished the loop. If HasException is still false, and ValidPlus is now true, it was a success, otherwise a failure
End Function

Sub GetAllMatchingRowIds(idToFind As String, arr As Variant, startRowId As Integer, columnId As Integer)
    Dim rowCount: rowCount = startRowId
    Do                                                          'Loop through our ID column rows
        If (Cells(rowCount, columnId).Value = idToFind) Then    'Check if the ID is the one we're after
            arr(UBound(arr) - 1) = rowCount                     'It is, add it to our array
            ReDim Preserve arr(UBound(arr) + 1)                 'Grow our array for next lop
        End If
        rowCount = rowCount + 1                                 'Next row
    Loop Until IsEmpty(Cells(rowCount, columnId).Value)         'Keep looping until we hit a blank
    ReDim Preserve arr(UBound(arr) - 2)                         'Remove additional entries in array we don't need
End Sub

结果：

Question 2

如果我理解正确的话，您需要上述列表，但如果 ID 的服务代码不包含加号，则需要排除 ID 代码。此外，如果销售和服务不匹配，则不应包括它们 - 除非服务代码exception中有加号。

您是否考虑过辅助列？

最简单的方法是将 E 列中的 A 列 + B 列连接起来，这样就会得到一行 ID 号和服务代码。

然后，您可以在 E 行中使用 countif 语句来检查 whereID和PLUS= true。

然后您需要检查名称和例外情况。

然后，您可以使用嵌套的 IF 语句来检查匹配、异常和加号。

在 E 中显然是=CONCATENATE(a2,b2)

F 看起来会像这样=if(countif(E:E,CONCATENATE(a2,"PLUS"))>0,"PLUS","")

你需要使用 G 来检查名称 - 因此你需要使用=Concatenate(a2,if(c2=d2,"Match","ERROR")

您需要使用 H 来检查名称是否全部正确或是否存在异常。

=if(and(countif(g:G,concatenate(a2,"ERROR")>0,NOT(countif(e:e,concatenate(a2,"EXCEPTION")>0))),"No match","match")

最后，在 I 中，您可以查找 Plus = Plus 和 name match = Match

=IF(AND(F2="Plus",H2="Match"),"Include","Exclude")

这（如果我的公式是正确的- ymmmv！）应该允许您通过“INCLUDE”过滤列表，等等，您的列表ID和是或匹配，并且至少其中一个服务代码条目包含servicesaleExceptionIDplus

Answer