Jak pominąć zwracanie wielu wartości w jednej komórce w programie Excel?

Zwykle w programie Excel, gdy używasz funkcji WYSZUKAJ.PIONOWO, jeśli istnieje wiele wartości pasujących do kryteriów, możesz po prostu uzyskać pierwszą. Ale czasami chcesz zwrócić wszystkie odpowiednie wartości, które spełniają kryteria, do jednej komórki, jak pokazano na zrzucie ekranu, jak możesz to rozwiązać?

Vlookup, aby zwrócić wiele wartości do jednej komórki za pomocą funkcji TEXTJOIN (Excel 2019 i Office 365)

• Vlookup, aby zwrócić wszystkie pasujące wartości do jednej komórki
• Vlookup, aby zwrócić wszystkie pasujące wartości bez duplikatów do jednej komórki

Vlookup, aby zwrócić wiele wartości do jednej komórki za pomocą funkcji zdefiniowanej przez użytkownika

• Vlookup, aby zwrócić wszystkie pasujące wartości do jednej komórki
• Vlookup, aby zwrócić wszystkie pasujące wartości bez duplikatów do jednej komórki

Vlookup, aby zwrócić wiele wartości do jednej komórki z przydatną funkcją

Vlookup, aby zwrócić wiele wartości do jednej komórki za pomocą funkcji TEXTJOIN (Excel 2019 i Office 365)

Jeśli masz wyższą wersję programu Excel, taką jak Excel 2019 i Office 365, dostępna jest nowa funkcja - TEKSTJOINDzięki tej potężnej funkcji możesz szybko przeglądać i zwracać wszystkie pasujące wartości do jednej komórki.

  Vlookup, aby zwrócić wszystkie pasujące wartości do jednej komórki

Zastosuj poniższą formułę do pustej komórki, w której chcesz umieścić wynik, a następnie naciśnij Ctrl + Shift + Enter klucze razem, aby uzyskać pierwszy wynik, a następnie przeciągnij uchwyt wypełniania w dół do komórki, której chcesz użyć tej formuły, a otrzymasz wszystkie odpowiednie wartości, jak pokazano na zrzucie ekranu:

  Vlookup, aby zwrócić wszystkie pasujące wartości bez duplikatów do jednej komórki

Jeśli chcesz zwrócić wszystkie pasujące wartości na podstawie danych wyszukiwania bez duplikatów, może Ci pomóc poniższy wzór.

Skopiuj i wklej poniższą formułę do pustej komórki, a następnie naciśnij Ctrl + Shift + Enter klucze razem, aby uzyskać pierwszy wynik, a następnie skopiuj tę formułę, aby wypełnić inne komórki, a otrzymasz wszystkie odpowiadające jej wartości bez zdeformowanych, jak pokazano na zrzucie ekranu:

Vlookup, aby zwrócić wiele wartości do jednej komórki za pomocą funkcji zdefiniowanej przez użytkownika

Powyższa funkcja TEXTJOIN jest dostępna tylko dla Excel 2019 i Office 365, jeśli masz inne niższe wersje Excela, powinieneś użyć niektórych kodów do zakończenia tego zadania.

  Vlookup, aby zwrócić wszystkie pasujące wartości do jednej komórki

1. Przytrzymaj ALT + F11 klucze i otwiera plik Microsoft Visual Basic for Applications okno.

2. Kliknij wstawka > Modułi wklej następujący kod w Okno modułu.

Kod VBA: Vlookup zwraca wiele wartości do jednej komórki

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Updateby Extendoffice
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
    ConcatenateIf = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To CriteriaRange.Count
    If CriteriaRange.Cells(i).Value = Condition Then
        xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

3. Następnie zapisz i zamknij ten kod, wróć do arkusza i wprowadź następującą formułę: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") do określonej pustej komórki, w której chcesz umieścić wynik, a następnie przeciągnij uchwyt wypełniania w dół, aby uzyskać wszystkie odpowiednie wartości w jednej komórce, którą chcesz, zobacz zrzut ekranu:

  Vlookup, aby zwrócić wszystkie pasujące wartości bez duplikatów do jednej komórki

Aby zignorować duplikaty w zwracanych pasujących wartościach, wykonaj poniższy kod.

1. Przytrzymaj Alt + F11 klawisze, aby otworzyć Microsoft Visual Basic for Applications okno.

2. Kliknij wstawka > Modułi wklej następujący kod w Okno modułu.

Kod VBA: przeglądaj i zwracaj wiele unikalnych dopasowanych wartości do jednej komórki

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
'Updateby Extendoffice
    Dim xDic As New Dictionary
    Dim xRows As Long
    Dim xStr As String
    Dim i As Long
    On Error Resume Next
    xRows = LookupRange.Rows.Count
    For i = 1 To xRows
        If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
            xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
        End If
    Next
    xStr = ""
    MultipleLookupNoRept = xStr
    If xDic.Count > 0 Then
        For i = 0 To xDic.Count - 1
            xStr = xStr & xDic.Keys(i) & ","
        Next
        MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
    End If
End Function

3. Po wstawieniu kodu kliknij Tools > Referencje w otwartym Microsoft Visual Basic for Applications okno, a następnie w wyskoczył Referencje - VBAProject okno dialogowe, zaznacz Środowisko wykonawcze skryptów firmy Microsoft Microsoft opcja w Dostępne referencje pole listy, zobacz zrzuty ekranu:

4. Następnie kliknij OK aby zamknąć okno dialogowe, zapisz i zamknij okno kodu, wróć do arkusza i wprowadź następującą formułę: =MultipleLookupNoRept(E2,$A$2:$C$11,3) into a blank cell where you want to output the result, and then drag the fill hanlde down to get all matching values, see screenshot:

Vlookup, aby zwrócić wiele wartości do jednej komórki z przydatną funkcją

 Jeśli posiadamy Kutools dla programu Excel, Z siedzibą w Zaawansowane wiersze łączenia możesz szybko scalać lub łączyć wiersze na podstawie tej samej wartości i wykonywać obliczenia według potrzeb.

Po zainstalowaniu Kutools dla programu Excelwykonaj następujące czynności:

1. Wybierz zakres danych, w którym chcesz połączyć dane z jednej kolumny na podstawie innej kolumny.

2. Kliknij Kutools > Połącz i podziel > Zaawansowane wiersze łączeniazobacz zrzut ekranu:

3. W wyskoczył Zaawansowane wiersze łączenia Okno dialogowe:

• Kliknij nazwę kolumny kluczowej, na podstawie której chcesz połączyć, a następnie kliknij Główny klucz
• Następnie kliknij inną kolumnę, którą chcesz połączyć z danymi na podstawie kolumny kluczowej, i kliknij Połączyć aby wybrać jeden separator do oddzielenia połączonych danych.

4. Następnie kliknij OK przycisk, a otrzymasz następujące wyniki:

Pobierz i bezpłatną wersję próbną Kutools dla programu Excel teraz!

Więcej artykułów względnych: