Note: The other languages of the website are Google-translated. Back to English

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 mai multe valori într-o singură celulă cu funcția definită de utilizator

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:

=TEXTJOIN(",",TRUE,IF($A$2:$A$11=E2,$C$2:$C$11,""))

Notă: În formula de mai sus, A2: A11 este intervalul de căutare conține datele de căutare, E2 este valoarea de căutare, C2: C11 este intervalul de date din care doriți să returnați valorile potrivite, ","este separatorul pentru a separa înregistrările multiple.

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:

=TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$11, IF(E2=$A$2:$A$11, $C$2:$C$11, ""), 0),"")=MATCH(ROW($C$2:$C$11), ROW($C$2:$C$11)), $C$2:$C$11, ""))

Notă: În formula de mai sus, A2: A11 este intervalul de căutare conține datele de căutare, E2 este valoarea de căutare, C2: C11 este intervalul de date din care doriți să returnați valorile potrivite, ","este separatorul pentru a separa înregistrările multiple.

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:

Notă: În formula de mai sus, A2: A11 este intervalul de căutare conține datele de căutare, E2 este valoarea de căutare, C2: C11 este intervalul de date din care doriți să returnați valorile potrivite, ","este separatorul pentru a separa înregistrările multiple.

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 unelte > 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:

Notă: În formula de mai sus, A2: C11 este intervalul de date pe care doriți să îl utilizați, E2 este valoarea de căutare, numărul 3 este numărul coloanei care conține valorile returnate.

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.

Notă:Pentru a aplica acest lucru Rânduri combinate avansate, în primul rând, ar trebui să descărcați fișierul Kutools pentru Excel, apoi aplicați caracteristica rapid și ușor.

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 Office

Kutools pentru Excel vă rezolvă majoritatea problemelor și vă crește productivitatea cu 80%

  • reutilizarea: Introduceți rapid formule complexe, diagrame și orice ai folosit anterior; Criptați celulele cu parola; Creați o listă de corespondență și trimiteți e-mailuri ...
  • Super Formula Bar (editați cu ușurință mai multe linii de text și formulă); Layout de citire (citiți și editați cu ușurință un număr mare de celule); Lipiți la interval filtrat...
  • Merge celule / rânduri / coloane fără a pierde date; Conținut de celule divizate; Combinați rânduri / coloane duplicate... Prevenirea celulelor duplicate; Comparați gamele...
  • Selectați Duplicat sau Unic Rânduri; Selectați Rânduri goale (toate celulele sunt goale); Super Find și Fuzzy Find în multe cărți de lucru; Selectare aleatorie ...
  • Copie exactă Mai multe celule fără modificarea referinței formulelor; Creați automat referințe la foi multiple; Introduceți gloanțe, Casete de selectare și multe altele ...
  • Extrageți textul, Adăugați text, eliminați după poziție, Eliminați spațiul; Creați și imprimați subtotaluri de paginare; Convertiți conținutul dintre celule și comentarii...
  • Super Filtru (salvați și aplicați scheme de filtrare altor foi); Sortare avansată după lună / săptămână / zi, frecvență și multe altele; Filtru special cu bold, italic ...
  • Combinați cărți de lucru și foi de lucru; Merge Tables pe baza coloanelor cheie; Împărțiți datele în mai multe foi; Conversia în loturi xls, xlsx și PDF...
  • Peste 300 de funcții puternice. Suportă Office / Excel 2007-2021 și 365. Acceptă toate limbile. Implementare ușoară în întreprinderea sau organizația dvs. Funcții complete Probă gratuită de 30 de zile. Garanție de returnare a banilor de 60 de zile.
fila kte 201905

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!
fundul officetab
Comentarii (43)
Încă nu există evaluări. Fii primul care evaluează!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Cum aș ajusta această formulă pentru a separa fiecare valoare returnată, dar „, ” precum și pentru a returna numai valori unice?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Multumesc pentru cod!!

În ceea ce privește metacaracterele, o modalitate de ocolire este utilizarea INSTR

Puteți înlocui [ If rng = pValue Then ] cu [ InStr(1, rng.Value, pValue) Then ] și dacă nu doriți să fie sensibilă la majuscule, atunci utilizați [ InStr(1, rng.Value, pValue, vbTextCompare) Apoi ]
Acest comentariu a fost redus la minimum de moderatorul de pe site
Mulțumesc pentru codul VBA de mai sus. Îmi puteți spune cum să faceți ca rezultatele să intre într-o nouă linie în celulă, cum ar fi Alt-Enter 300 400 1000 1300
Acest comentariu a fost redus la minimum de moderatorul de pe site
Vă mulțumim pentru distribuirea codului de mai sus. Folosesc asta de câteva luni, dar astăzi nu pare să funcționeze. Primesc celule goale în loc de eroarea obișnuită când există date de returnat. Ceva gânduri?
Acest comentariu a fost redus la minimum de moderatorul de pe site
O lucrare minunată.. Am primit exact ceea ce îmi doresc !!! Iubesc !!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună, sunt foarte impresionat de lucru și este atât de ușor să creez unul pentru a utiliza această funcție. totuși am nevoie de sprijin suplimentar. Ale mele ? este așa cum pot selecta un număr dintr-o celulă cu mai multe celule în matricea mea de căutare. adică dacă celula A1 = 100, A2 = 350, A3 = 69 C1 = 100; 1222; 12133 C2 = 69; 222 D1 = Apple D2 = banană Deci, cum pot selecta 100 din coloana mea de tabel C pentru a deriva corespondentul D1 = Apple. Aș aprecia foarte mult dacă ați putea să rezolvați acest lucru și să mă ajutați să economisesc mult timp.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Vă mulțumim pentru codul VBA. Am primit exact ce vreau! Am modificat doar codul " rng.Offset(0, pIndex - 1) " la " rng.Offset(0, pIndex - 2) ". La fel, MYVLOOKUP poate căuta de la dreapta la stânga.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Este exact ceea ce căutam și nu m-am gândit să-mi fac propriul UDF. Cu toate acestea, nu va funcționa exact ca VLOOKUP. Dacă șirul pe care îl căutați nu se află doar în prima coloană, atunci vă poate oferi date în afara intervalului inițial trecut. Nume Număr Alt nume Coloana care nu se află în interval a fost trecută Jay 1 Jay 1 Jay 2 Jay 2 Chris 3 Chris 3 Jorge 4 Jorge 4 Jay 5 Jay 5 Jorge 6 Jorge 6 Dacă tabelul de mai sus au fost celulele A1:D7 dacă ați trecut doar A1:C7 dvs. Funcția „MYVLOOKUP” returnează 1 1 2 2 5 5 când v-ați aștepta să returneze 1 2 5. Modificările de mai jos rezolvă problema: Function MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long) „Actualizare 20150310 „Actualizat 6 /9/16 Jay Coltrain 'Dim rng As Range Dim xResult As String xResult = "" Dim Rows As Long, i As Long Rows = pWorkRng.Rows.Count For i = 1 To Rows If pWorkRng.Cells(i, 1). Value = pValue Then xResult = xResult & " " & pWorkRng.Cells(i, 1).Offset(0, pIndex - 1) End If Next i 'Pentru fiecare rng în pWorkRng ' Dacă rng = pValue Then ' xResult = xResult & " " & rng.Offset(0, pIndex - 1) ' End If 'Next MYVLOOKUP = xResult End Function
Acest comentariu a fost redus la minimum de moderatorul de pe site
Acest lucru funcționează excelent, dar am nevoie de ajutor cu comanda pentru a elimina duplicatele din rezultate. Serios, totuși, mare treabă.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Funcționează excelent, dar încă am nevoie de ajutor cu funcția de comandă pentru a elimina duplicatele din rezultate.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Anunțați-mă cu privire la comentariile ulterioare
Acest comentariu a fost redus la minimum de moderatorul de pe site
Nu returnați nimic! după aplicarea MYLOOKUP nu dă niciun rezultat decât gol.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună, funcționează bine. Ceea ce aș dori să fac este să adaptez codul pentru a separa rezultatele valorii cu „///” sau orice alt marker (din motive tehnice, nu vreau doar un singur separator de caractere). De asemenea, am observat că această formulă nu funcționează cu un wild card. Știu că cer prea multe, dar nu funcționează, deoarece vlookup poate funcționa când caut =myvlookup("*"&E6&"*",$A$2:$C$15,2) ceea ce ar face/ar putea face. Orice ajutor?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Atenție. Mi-am dat seama cum să obțin orice separator în acea ieșire. Este rudimentar. Dar mi-am dat seama. xResult = xResult & "///" & rng.Offset(0, pIndex - 1) Totuși, ultimul lucru și cel mai dorit este să îi permită să funcționeze cu metacaractere în criteriile de căutare. Vă mulțumesc încă o dată pentru această soluție frumoasă și genială. Extrem de util. Acum vreau doar să fac macrocomanda să ruleze și să fie instalată în excelul meu permanent, indiferent de ceea ce fac, astfel încât să o pot folosi când am nevoie. Și wildcards! Mulțumesc foarte mult. Wildcardurile sunt tot ce mai rămâne de făcut.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Pentru a obține o înregistrare unică, puteți utiliza mai jos: (modificat prin referiți la alt cod de utilizator) Funcția MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long) 'Update 20150310 'Updated 6/9/16 Jay Coltrain 'Dim rng As Range Dim xResult As String xResult = "" Dim Rows As Long, i As Long Rows = pWorkRng.Rows.Count For i = 1 To Rows If pWorkRng.Cells(i, 1).Value = pValue Then xResult = xResult & "," & pWorkRng.Cells(i, 1).Offset(0, pIndex - 1) End If Next i Dim varSection As Variant Dim sTemp As String Dim sDelimiter As String sDelimiter = "," Pentru fiecare varSection In Split(xResult, sDelimiter) If InStr(1, sDelimiter & sTemp & sDelimiter, sDelimiter & varSection & sDelimiter, vbTextCompare) = 0 Apoi sTemp = sTemp & sDelimiter & varSection End If Next varSection MYVLOOKUP = Mid(sTemp, Len) Function (sDelimiter) +(sDelimiter)
Acest comentariu a fost redus la minimum de moderatorul de pe site
Acest lucru a funcționat perfect, dar mi-a luat ceva timp pentru ca funcția să funcționeze corect în fila mea de 20, foaia de calcul de 50k+ linii. Acum, întrebarea MAREA este cum să luați acel șir delimitat și apoi să utilizați fiecare intrare ca valoare de căutare Index/Potrivire (nu este căsătorită cu Index/Potrivire, dar pare mai rapidă) într-un alt set de date, returnând valoarea SUMA a tuturor returnărilor într-o singură celulă . Scenariul meu este că am o singură comandă care are mai multe facturi. Funcția dvs. MYVLOOKUP funcționează excelent pentru a raporta toate facturile într-o singură celulă. Ceea ce vreau să fac acum este să iau fiecare returnare concatenată cu celula raportată, să rotesc acea matrice și să totalizez sumele de plată ale fiecărei facturi înapoi în celula formulei. Apreciez orice ajutor pe care îl puteți oferi în acest sens și vă mulțumesc pentru funcția MYVLOOKUP!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Indiferent ce fac, primesc întotdeauna #valoare! returnat în loc de rezultat. vlookup funcționează bine, deci datele funcționează. Am urmat deja procesul de activare a macrocomenzilor. Chiar am combinat totul într-o singură foaie. Vreo idee??
Acest comentariu a fost redus la minimum de moderatorul de pe site
Grozavă macro, utilă. Dar trebuie să știu dacă poate fi modificat pentru a verifica 2 criterii și s-a găsit cineva oricum pentru a face metacaracterele să funcționeze pe el. Orice ajutor?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Există o modalitate de a modifica rezultatul, astfel încât în ​​loc să arate 1000 1000 -1000, ar arăta, de exemplu, 1,000/1,000/(1,000)?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Funcție grozavă, cu toate acestea, fragmentarea a 100,000 de înregistrări se dovedește puțin pentru bietul meu laptop, va trebui să-l las să funcționeze peste noapte!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Este uimitor, mulțumesc!
Nu există comentarii postate aici încă
Încărcați mai
Lăsa comentarii
Postare ca invitat
×
Evaluează această postare:
0   Caractere
Locații sugerate

Urmărește-ne

Copyright © 2009 - www.extendoffice.com. | Toate drepturile rezervate. Cu sprijinul ExtendOffice. | Harta site-ului
Microsoft și sigla Office sunt mărci comerciale sau mărci comerciale înregistrate ale Microsoft Corporation în Statele Unite și / sau în alte țări.
Protejat de Sectigo SSL