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 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:
- Funcția VLOOKUP cu câteva exemple de bază și avansate
- În Excel, funcția VLOOKUP este o funcție puternică pentru majoritatea utilizatorilor Excel, care este utilizată pentru a căuta o valoare în partea din stânga a intervalului de date și pentru a returna o valoare potrivită în același rând dintr-o coloană pe care ați specificat-o. Acest tutorial vorbește despre cum să utilizați funcția VLOOKUP cu câteva exemple de bază și avansate în Excel.
- Returnează mai multe valori de potrivire pe baza unuia sau mai multor criterii
- În mod normal, căutarea unei valori specifice și returnarea articolului care se potrivește este ușor pentru majoritatea dintre noi utilizând funcția VLOOKUP. Dar, ați încercat vreodată să returnați mai multe valori de potrivire pe baza unuia sau mai multor criterii? În acest articol, voi introduce câteva formule pentru rezolvarea acestei sarcini complexe în Excel.
- Vizualizați și returnați mai multe valori pe verticală
- În mod normal, puteți utiliza funcția Vlookup pentru a obține prima valoare corespunzătoare, dar, uneori, doriți să returnați toate înregistrările de potrivire pe baza unui criteriu specific. În acest articol, voi vorbi despre cum să vizualizați și să returnați toate valorile potrivite pe verticală, orizontală sau într-o singură celulă.
- Vizualizați și returnați mai multe valori din lista derulantă
- În Excel, cum ați putea să căutați și să returnați mai multe valori corespunzătoare dintr-o listă derulantă, ceea ce înseamnă că atunci când alegeți un articol din lista derulantă, toate valorile sale relative sunt afișate simultan. În acest articol, voi introduce soluția pas cu pas.
Cele mai bune instrumente de productivitate de birou
Îmbunătățiți-vă abilitățile Excel cu Kutools pentru Excel și experimentați eficiența ca niciodată. Kutools pentru Excel oferă peste 300 de funcții avansate pentru a crește productivitatea și a economisi timp. Faceți clic aici pentru a obține funcția de care aveți cea mai mare nevoie...
Fila Office aduce interfața cu file în Office și vă face munca mult mai ușoară
- Activați editarea și citirea cu file în Word, Excel, PowerPoint, Publisher, Access, Visio și Project.
- Deschideți și creați mai multe documente în filele noi ale aceleiași ferestre, mai degrabă decât în ferestrele noi.
- Vă crește productivitatea cu 50% și reduce sute de clicuri de mouse pentru dvs. în fiecare zi!
