Cum să vizualizați pentru a returna mai multe valori într-o singură celulă în Excel?

În mod normal, în Excel, când utilizați funcția VLOOKUP, dacă există mai multe valori care să corespundă criteriilor, puteți obține prima. Dar, uneori, doriți să returnați toate valorile corespunzătoare care îndeplinesc criteriile într-o singură celulă, după cum se arată în următoarea captură de ecran, cum ați putea să o rezolvați?

Vlookup pentru a returna mai multe valori într-o singură celulă cu funcția TEXTJOIN (Excel 2019 și Office 365)

• Vlookup pentru a returna toate valorile potrivite într-o singură celulă
• Vlookup pentru a returna toate valorile potrivite fără duplicate într-o singură celulă

Vlookup pentru a returna mai multe valori într-o singură celulă cu funcția definită de utilizator

• Vlookup pentru a returna toate valorile potrivite într-o singură celulă
• Vlookup pentru a returna toate valorile potrivite fără duplicate într-o singură celulă

Vlookup pentru a returna mai multe valori într-o singură celulă cu o caracteristică utilă

Vlookup pentru a returna mai multe valori într-o singură celulă cu funcția TEXTJOIN (Excel 2019 și Office 365)

Dacă aveți versiunea superioară a Excelului, cum ar fi Excel 2019 și Office 365, există o nouă funcție - TEXTJOIN, cu această funcție puternică, puteți vizualiza și returna rapid toate valorile potrivite într-o singură celulă.

  Vlookup pentru a returna toate valorile potrivite într-o singură celulă

Vă rugăm să aplicați formula de mai jos într-o celulă goală unde doriți să puneți rezultatul, apoi apăsați Ctrl + Shift + Enter tastele împreună pentru a obține primul rezultat, apoi glisați mânerul de umplere în jos în celula pe care doriți să o utilizați cu această formulă și veți obține toate valorile corespunzătoare, după cum se arată în imaginea de mai jos:

  Vlookup pentru a returna toate valorile potrivite fără duplicate într-o singură celulă

Dacă doriți să returnați toate valorile potrivite pe baza datelor de căutare fără duplicate, formula de mai jos vă poate ajuta.

Vă rugăm să copiați și să lipiți următoarea formulă într-o celulă goală, apoi apăsați Ctrl + Shift + Enter tastele împreună pentru a obține primul rezultat, apoi copiați această formulă pentru a umple alte celule și veți obține toate valorile corespunzătoare fără cele dulpicate, după cum se arată în imaginea de mai jos:

Vlookup pentru a returna mai multe valori într-o singură celulă cu funcția definită de utilizator

Funcția TEXTJOIN de mai sus este disponibilă numai pentru Excel 2019 și Office 365, dacă aveți alte versiuni mai mici de Excel, ar trebui să utilizați câteva coduri pentru finalizarea acestei sarcini.

  Vlookup pentru a returna toate valorile potrivite într-o singură celulă

1. Țineți apăsat butonul ALT + F11 tastele și deschide fișierul Microsoft Visual Basic pentru aplicații fereastră.

2. Clic Insera > Moduleși lipiți următorul cod în Fereastra modulului.

Cod VBA: Vlookup pentru a returna mai multe valori într-o singură celulă

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. Apoi salvați și închideți acest cod, reveniți la foaia de lucru și introduceți această formulă: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") într-o celulă goală specifică în care doriți să plasați rezultatul, apoi trageți mânerul de umplere în jos pentru a obține toate valorile corespunzătoare într-o celulă pe care o doriți, vedeți captura de ecran:

  Vlookup pentru a returna toate valorile potrivite fără duplicate într-o singură celulă

Pentru a ignora duplicatele din valorile de potrivire returnate, faceți acest lucru cu codul de mai jos.

1. Țineți apăsat butonul Alt + F11 tastele pentru a deschide Microsoft Visual Basic pentru aplicații fereastră.

2. Clic Insera > Moduleși lipiți următorul cod în Fereastra modulului.

Cod VBA: Vlookup și returnează mai multe valori unice potrivite într-o singură celulă

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. După introducerea codului, faceți clic pe Instrumente > Referinte în deschise Microsoft Visual Basic pentru aplicații fereastră, apoi, în fereastra ieșită Referințe - VBAProject casetă de dialog, bifați Runtime Microsoft Scripting opțiune în Referințe disponibile casetă listă, vezi capturi de ecran:

4. Apoi apasa OK pentru a închide caseta de dialog, salvați și închideți fereastra de cod, reveniți la foaia de lucru și introduceți această formulă: =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 pentru a returna mai multe valori într-o singură celulă cu o caracteristică utilă

 Dacă aveți noastre Kutools pentru Excel, Cu său Rânduri combinate avansate caracteristică, puteți îmbina sau combina rapid rândurile pe baza aceleiași valori și puteți face unele calcule după cum aveți nevoie.

După instalare Kutools pentru Excel, vă rugăm să faceți următoarele:

1. Selectați intervalul de date pe care doriți să combinați datele unei coloane pe baza altei coloane.

2. Clic Kutools > Merge & Split > Rânduri combinate avansate, vezi captura de ecran:

3. În pop-out Rânduri combinate avansate căsuță de dialog:

• Faceți clic pe numele coloanei cheie pentru a fi combinat, apoi faceți clic pe Cheia principala
• Apoi faceți clic pe o altă coloană pe care doriți să o combinați pe baza coloanei cheie și faceți clic pe Combina pentru a alege un separator pentru separarea datelor combinate.

4. Apoi faceți clic pe OK și veți obține următoarele rezultate:

Descărcați și proba gratuită Kutools pentru Excel acum!

Mai multe articole relative: