여러 결과로 탁월한 vlookup

여러 결과로 탁월한 vlookup

워크시트를 검색하고 계정 번호를 일치시킨 다음 지정된 값을 반환하는 데 vlookup 또는 유사한 기능을 사용하려고 합니다.제 문제는 중복된 계좌번호가 있다는 것이고 결과를 하나의 문자열로 연결하고 싶습니다.

Acct No   CropType
-------   ---------
0001      Grain
0001      OilSeed
0001      Hay
0002      Grain  

첫 번째 워크시트의 경우, 두 번째 워크시트에는 다른 정보와 함께 Act No가 있으며 일치하는 모든 결과를 두 번째 워크시트의 한 열에 넣어야 합니다."그레인 오일 씨드 건초"

여기 그것을 해줄 기능이 있습니다.전체 범위가 아닌 검색 열만 제공한다는 점에서 Vlookup과 조금 다릅니다. 세 번째 매개 변수로 반환 값을 얻기 위해 왼쪽(음의 숫자) 또는 오른쪽(양의)으로 이동할 열 수를 알려줍니다.

나는 또한 구분자를 사용하는 옵션을 추가했는데, 당신의 경우 "."를 사용할 것입니다. Acct 번호가 A이고 결과가 B인 첫 번째 행을 가정하면 다음과 같은 함수 호출이 있습니다.

=vlookupall("0001", A:A, 1, " ")

기능은 다음과 같습니다.

Function VLookupAll(ByVal lookup_value As String, _
                    ByVal lookup_column As range, _
                    ByVal return_value_column As Long, _
                    Optional seperator As String = ", ") As String

Dim i As Long
Dim result As String

For i = 1 To lookup_column.Rows.count
    If Len(lookup_column(i, 1).text) <> 0 Then
        If lookup_column(i, 1).text = lookup_value Then
            result = result & (lookup_column(i).offset(0, return_value_column).text & seperator)
        End If
    End If
Next

If Len(result) <> 0 Then
    result = Left(result, Len(result) - Len(seperator))
End If

VLookupAll = result

End Function

주의:

• 결과를 입력하지 않으면 ","를 결과의 기본 구분자로 설정했습니다.
• 만약 하나 이상의 히트가 있다면, 저는 문자열이 여분의 구분자로 끝나지 않는지 확인하기 위해 끝에 약간의 확인을 추가했습니다.
• 저는 당신의 범위를 모르기 때문에 A:A를 범위로 사용했지만, 분명히 당신이 실제 범위로 입력하면 더 빠릅니다.

이를 위한 한 가지 방법은 배열 공식을 사용하여 모든 일치 항목을 숨겨진 열에 채운 다음 해당 값을 문자열에 연결하여 표시하는 것입니다.

=IFERROR(INDEX(cropTypeValues,SMALL(IF(accLookup=accNumValues,ROW(accNumValues)-MIN(ROW(accNumValues))+1,""),ROW(A1))),"")

• cropType 값:자르기 유형 목록을 보관하는 명명된 범위입니다.
• accLookup: 조회할 계정 번호를 보유한 명명된 범위입니다.
• accNum 값:계정 번호 목록을 보관하는 명명된 범위입니다.

배열 공식(Ctrl+Shift+Enter)으로 입력한 다음 필요한 만큼 복사합니다.

공식에서 설명이 필요한 부분이 있으면 알려주세요.

저는 방금 비슷한 문제를 겪었고 오랫동안 비슷한 해결책을 찾아봤지만 아무 것도 저를 납득시키지 못했습니다.매크로를 작성하거나 특별한 기능을 사용해야 했지만, 제가 필요로 하는 가장 쉬운 해결책은 예를 들어 피벗 테이블을 사용하는 것입니다.훌륭합니다.

데이터에서 새 피벗 테이블을 만들고 먼저 "Act No"를 행 레이블로 추가한 다음 "CropType"을 행 레이블로 추가하면 각 계정에 대해 모든 자르기 유형을 나열하는 매우 멋진 그룹이 생성됩니다.하지만 단일 셀에서는 그렇지 않습니다.

여기 엑셀 조회보다 더 좋은 코드가 있습니다. 기준 열을 선택할 수 있고 구분 기호(캐리지 리턴)도 선택할 수 있기 때문입니다.

Function Lookup_concat(source As String, tableau As Range, separator As String, colSRC As Integer, colDST As Integer) As String
    Dim i, y As Integer
    Dim result As String

    If separator = "CRLF" Then
        separator = Chr(10)
    End If

    y = tableau.Rows.Count
    result = ""
    For i = 1 To y
        If (tableau.Cells(i, colSRC) = source) Then
            If result = "" Then
                result = tableau.Cells(i, colDST)
            Else
                result = result & separator & tableau.Cells(i, colDST)
            End If
        End If
    Next
    Lookup_concat = result
End Function

또한 동일한 셀의 여러 요소(동일한 구분 기호 기준)를 검색할 수도 있습니다.정말 유용합니다.

Function Concat_Lookup(source As String, tableau As Range, separator As String, colSRC As Integer, colDST As Integer) As String
    Dim i, y As Integer
    Dim result As String

    Dim Splitted As Variant

    If separator = "CRLF" Then
        separator = Chr(10)
    End If

    Splitted = split(source, separator)

    y = tableau.Rows.Count
    result = ""
    For i = 1 To y
        For Each word In Splitted
            If (tableau.Cells(i, colSRC) = word) Then
                If result = "" Then
                    result = tableau.Cells(i, colDST)
                Else
                    Dim Splitted1 As Variant
                    Splitted1 = split(result, separator)
                    If IsInArray(tableau.Cells(i, colDST), Splitted1) = False Then
                        result = result & separator & tableau.Cells(i, colDST)
                    End If
                End If
            End If
        Next
    Next
    Concat_Lookup = result
End Function

이전 하위 항목에는 이 기능이 필요합니다.

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

Function VLookupAll(vValue, rngAll As Range, iCol As Integer, Optional sSep As String = ", ")
    Dim rCell As Range
    Dim rng As Range
    On Error GoTo ErrHandler
    Set rng = Intersect(rngAll, rngAll.Columns(1))
    For Each rCell In rng
        If rCell.Value = vValue Then
            VLookupAll = VLookupAll & sSep & rCell.Offset(0, iCol - 1).Value
        End If
    Next rCell
    If VLookupAll = "" Then
        VLookupAll = CVErr(xlErrNA)
    Else
        VLookupAll = Right(VLookupAll, Len(VLookupAll) - Len(sSep))
    End If
ErrHandler:
    If Err.Number <> 0 Then VLookupAll = CVErr(xlErrValue)
End Function

다음과 같이 사용:

=VLookupAll(K1, A1:C25, 3)

A1 범위에서 K1 값이 발생하는 모든 항목을 조회합니다.A25 및 쉼표로 구분하여 C열에서 해당 값을 반환합니다.

값을 합하려면 SUMIF를 사용할 수 있습니다(예:

=SUMIF(A1:A25, K1, C1:C25)

열 A의 해당 값이 K1의 값과 동일한 C1:C25의 값을 합합니다.

최선을 다합니다.

언급URL : https://stackoverflow.com/questions/6754605/excel-vlookup-with-multiple-results