☰

Excel — сложное сопоставление индексов для поиска нескольких значений

tag-icon • tag-icon microsoft-excel
Excel — сложное сопоставление индексов для поиска нескольких значений

Я пытаюсь выполнить довольно сложный поиск в таблице и вернуть несколько значений.

Мне нужно взять каждую IDгруппу, если Service Codeне содержитПлюсили если он содержитИсключение, проигнорируйте его. Если SalesиService несовпадение, проигнорировать его. Все остальное мне нужно вывести в список.

Я совсем запутался и даже не знаю, с чего начать.

Другими словами, наПлюсобслуживание Salesи Serviceне может соответствовать, если толькоИсключениеДанные A, B, C не имеют никакого значения, но они есть.

Что я собираюсь делать

решение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

Результат:

введите описание изображения здесь

решение2

Если я правильно вас понял, вам нужен список, как указано выше, за исключением того, что вы хотите исключить коды ID, если коды услуг ID не включают плюс. Кроме того, если продажи и услуги не совпадают, они не должны быть включены - ЕСЛИ exceptionв коде услуг нет плюса.

Вы когда-нибудь задумывались о вспомогательных колонках?

Самый простой способ сделать это — объединить столбцы A+B в столбец E, что даст вам строку идентификационных номеров и кодов услуг.

Затем можно использовать оператор countif в строке E, чтобы проверить, где IDи 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 и имя совпадает = Match

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

Это (если мои формулы верны - уууу!) должно позволить вам затем отфильтровать список по "INCLUDE", и вуаля, ваш список, IDгде serviceи saleявляется Exceptionили совпадает, и по крайней мере одна из IDзаписей кода службы содержитplus

решение3

Вот более гибкое решение, чем VBA, однако представляющее собой гибрид ручного рабочего процесса.

Добавьте TRUEстолбец FALSEпод названием «Обслуживается отделом продаж»

=C2=D2

Затем еще один TRUEстолбец FALSEпод названием «Имеет исключение»

=COUNTIFS(A:A, A2, B:B, "Exception")

Затем вы просто отфильтруете таблицу, где Serviced by Sales = TRUEи Has exception = FALSE. Чтобы получить свой список, вы скопируете и вставите идентификаторы в другом месте и запустите Data > Remove Duplicates.

Если вы ожидаете создания отчетов по спискам в реальном времени, я бы рекомендовал использовать решение VBA.

Связанный контент