Excel - 여러 값을 조회하기 위한 복잡한 인덱스 일치

Question

테이블에서 꽤 복잡한 조회를 수행하고 여러 값을 반환하려고 합니다.

포함하지 않은 ID경우 각 그룹을 그룹으로 묶어야 합니다 .Service Code을 더한또는 포함되어 있는 경우예외, 무시해. 만약에 Sales그리고Service ~하지 않다일치합니다. 무시하세요. 그 밖의 모든 것은 목록으로 출력해야 합니다.

머리가 너무 복잡해서 어디서부터 시작해야 할지조차 모르겠습니다.

---

즉,을 더한서비스 Sales이며 Service일치하지 않는 한 일치할 수 없습니다.예외만들어진. A, B, C 데이터는 전혀 중요하지 않지만 거기에 있습니다.

Answer 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 2

내가 이해한 것이 맞다면 위와 같은 목록을 원할 것입니다. 단, ID의 서비스 코드에 플러스가 포함되지 않은 경우 ID 코드를 제외하려는 경우는 제외됩니다. 또한 판매와 서비스가 일치하지 않는 경우 exception서비스 코드에 이 있는 경우를 제외하고는 포함되지 않습니다 .

도우미 칼럼에 대해 생각해 보셨나요?

이를 수행하는 가장 쉬운 방법은 E열에 A+B열을 연결하는 것입니다. 그러면 ID 번호와 서비스 코드 행이 제공됩니다.

ID그런 다음 행 E에서 countif 문을 사용하여 where 및 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")

이를 통해(내 공식이 정확하다면- ymmmv!) "INCLUDE"로 목록을 필터링할 수 있습니다. 짜잔, 및 가 또는 일치 하는 ID위치 목록 과 최소한 의 서비스 코드 항목 중 하나에 다음이 포함됩니다.servicesaleExceptionIDplus

Answer 3

여기에는 VBA보다 더 유연한 솔루션이 있지만 다소 수동 작업 흐름이 혼합되어 있습니다.

"Serviced by Sales"라는 제목의 TRUE/ 열 을 추가합니다 .FALSE

=C2=D2

그런 다음 "예외 있음"이라는 제목의 또 다른 TRUE/ 열FALSE

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

그런 다음 Serviced by Sales = TRUE및 테이블을 필터링하기만 하면 됩니다 Has exception = FALSE. 목록을 얻으려면 ID를 복사하여 다른 곳에 붙여넣고 Data > Remove Duplicates.

목록의 실시간 보고서 생성을 기대한다면 VBA 솔루션을 따르겠습니다.