Salt la conținutul principal

Cum să vizualizați și să concatenați mai multe valori corespunzătoare în Excel?

După cum știm cu toții, VLOOKUP funcția în Excel ne poate ajuta să căutăm o valoare și să returnăm datele corespunzătoare într-o altă coloană, dar, în general, poate obține prima valoare relativă numai dacă există mai multe date de potrivire. În acest articol, voi vorbi despre cum să vizualizați și să concatenați mai multe valori corespunzătoare într-o singură celulă sau o listă verticală.

Căutați și returnați mai multe valori de potrivire pe verticală cu formula

Vizualizați și concatenați mai multe valori de potrivire într-o celulă cu funcția definită de utilizator

Vezi și concatenează mai multe valori de potrivire într-o celulă cu Kutools pentru Excel


Presupunând că am următoarea gamă de date, pentru a obține toate valorile corespunzătoare pe baza unei valori specifice pe verticală, după cum se arată în următoarea captură de ecran, puteți aplica o formulă matrice.

doc vlookup concatenate 1

1. Introduceți această formulă: =IF(COUNTIF($A$1:$A$16,$D$2)>=ROWS($1:1),INDEX($B$1:$B$16,SMALL(IF($A$1:$A$16=$D$2,ROW($1:$16)),ROW(1:1))),"") într-o celulă goală în care doriți să puneți rezultatul, de exemplu, E2, apoi apăsați Ctrl + Shift + Enter tastele împreună pentru a obține baza relativă a valorii pe un criteriu specific, consultați captura de ecran:

doc vlookup concatenate 2

notițe: În formula de mai sus:

A1: A16 este intervalul de coloane care conține valoarea specifică pe care doriți să o căutați;

D2 indică valoarea specifică pe care doriți să o vizualizați;

B1: B16 este gama de coloane din care doriți să returnați datele corespunzătoare;

$ 1: $ 16 indică referința rândurilor din interval.

2. Apoi selectați celula E2 și trageți mânerul de umplere în jos până la celule până când obțineți celule goale și toate valorile potrivite sunt listate în coloană, după cum urmează:

doc vlookup concatenate 3


În loc să obțineți valorile relative pe verticală, uneori, doriți să se potrivească valorile într-o singură celulă și să le concateneze cu un separator specific. În acest caz, următoarea funcție definită de utilizator vă poate face o favoare.

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

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

Cod VBA: vizualizați și concatenați mai multe valori de potrivire într-o celulă

Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange
    If x = lookupval Then
        result = result & " " & x.Offset(0, indexcol - 1)
    End If
Next x
CusVlookup = result
End Function

3. Apoi salvați și închideți acest cod, reveniți la foaia de lucru și introduceți această formulă: = cusvlookup (D2, A1: B16,2) într-o celulă goală unde doriți să puneți rezultatul și apăsați Intrați cheie, toate valorile corespunzătoare bazate pe date specifice au fost returnate într-o singură celulă cu separator de spațiu, vezi captura de ecran:

doc vlookup concatenate 4

notițe: În formula de mai sus: D2 indică valorile celulei pe care doriți să le căutați, A1: B16 este intervalul de date pe care doriți să îl preluați, numărul 2 este numărul coloanei din care trebuie returnată valoarea potrivită, puteți schimba aceste referințe la nevoile dvs.


Dacă aveţi Kutools pentru Excel, Cu său Rânduri combinate avansate caracteristică, puteți finaliza rapid această lucrare cu ușurință. Această caracteristică vă poate ajuta să combinați toate valorile potrivite cu un anumit delimitator bazat pe aceleași date într-o altă coloană.

Kutools pentru Excel : cu mai mult de 300 de programe de completare Excel la îndemână, gratuit pentru a încerca fără limitări în 30 de zile.

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

1. Selectați intervalul de date pe care doriți să obțineți valorile corespunzătoare pe baza datelor specifice.

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

3. În Rânduri combinate avansate caseta de dialog, faceți clic pe numele coloanei pe care doriți să o combinați, apoi faceți clic pe Cheia principala buton, vezi captura de ecran:

doc vlookup concatenate 6

4. Apoi faceți clic pe un alt nume de coloană pe care doriți să le returnați valorile potrivite și faceți clic pe Combina pentru a alege un separator pentru a separa valorile combinate, consultați captura de ecran:

doc vlookup concatenate 7

5. Apoi faceți clic pe Ok buton, toate valorile corespunzătoare bazate pe aceleași valori au fost combinate împreună cu un separator specific, consultați capturi de ecran:

doc vlookup concatenate 8 2 doc vlookup concatenate 9

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


Kutools pentru Excel: cu mai mult de 300 de programe de completare Excel la îndemână, încercați fără limitări în 30 de zile. Descărcați și proba gratuită acum!

Cele mai bune instrumente de productivitate de birou

Caracteristici populare: Găsiți, evidențiați sau identificați duplicatele   |  Ștergeți rândurile goale   |  Combinați coloane sau celule fără a pierde date   |   Rundă fără Formula ...
Super căutare: VLookup cu mai multe criterii    VLookup cu valori multiple  |   VLookup pe mai multe foi   |   Căutare fuzzy ....
Listă derulantă avansată: Creați rapid o listă derulantă   |  Listă drop-down dependentă   |  Listă derulantă cu selectare multiplă ....
Manager de coloane: Adăugați un număr specific de coloane  |  Mutați coloanele  |  Comutați starea vizibilității coloanelor ascunse  |  Comparați intervale și coloane ...
Caracteristici prezentate: Focus pe grilă   |  Vedere de proiectare   |   Big Formula Bar    Manager registru de lucru și foi   |  Biblioteca de resurse (Text automat)   |  Data Picker   |  Combinați foi de lucru   |  Criptare/Decriptare celule    Trimiteți e-mailuri după listă   |  Super Filtru   |   Filtru special (filtrează bold/italic/barat...) ...
Top 15 seturi de instrumente12 Text Instrumente (Adăuga text, Eliminați caractere,...)   |   50+ Diagramă Tipuri de (Gantt Chart,...)   |   40+ Practic Formule (Calculați vârsta pe baza zilei de naștere,...)   |   19 inserare Instrumente (Introduceți codul QR, Inserați imaginea din cale,...)   |   12 Convertire Instrumente (Numere la cuvinte, conversie valutara,...)   |   7 Merge & Split Instrumente (Rânduri combinate avansate, Celule divizate,...)   |   ... și altele

Î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 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!
Comments (16)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Is there any way to get the unique "name" for "class1"
This comment was minimized by the moderator on the site
Hello, sym-john,
Maybe the below article can solve your problem, please view it:
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html
This comment was minimized by the moderator on the site
This is working great for me - is there anyway to change it that it checks if the cell contains rather than a complete match? Basically I have a list of tasks where:
Column A: Dependencies (eg 10003 10004 10008)
Column B: Task Reference (eg 10001)
Column C: Dependent Tasks (the column for the formula result) - where it would lookup the task reference to see which rows contain it in Column A, and then list the Task Reference of those tasks.

E.g:

Row | Column A | Column B | Column C
1 | | 10001 | 10002 10003
2 | 10001 | 10002 | 10003
3 | 10001 10002 | 10003 |
This comment was minimized by the moderator on the site
you would want to use the Instr() function which will check for something in a string of text in a cell. You can also use Left() and Right() if you are looking for the starting or ending details.
This comment was minimized by the moderator on the site
The cusVlookup worked great for me. Another way to have a different separator is to wrap in two substitute functions. The first (from inside to out) replaces the first space with no space, the second replaces all other spaces with a " / " in mine. Could use "," if you want commas.
=SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Table1,2)," ","",1)," "," / ")

Also, if your lookup value isn't the first column, you can use 0 or negative numbers to go to column to the left.
=SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Table1,-1)," ","",1)," "," / ")
This comment was minimized by the moderator on the site
Hi, jeff,
Thanks for your sharing, you must be a warmhearted man.
This comment was minimized by the moderator on the site
I have to say, I have been trying to get a formula for combining multiple values and returning them to a single cell for 2 days now. This "How To" has saved me!! Thank you SO much! I would never have gotten it without your Module!
I do have 2 questions though. I have the deliminator as a comma instead of a space and because of that it starts out with a comma. Is there a way to prevent the start comma but keep the rest?
My second question is; When I use the fill handle it changes the range values as well as the cell value I want to look up. I want it to continue to change the cell number I want to look up but keep the same range values. How can I make this happen?

Thank you so much for your help!!
This comment was minimized by the moderator on the site
Is there a way to delete the duplicate values in the concatenate?
This comment was minimized by the moderator on the site
Hello, Jacob,
May be the following article can help you to solve your problem.
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html

Please try, hope it can help you!
This comment was minimized by the moderator on the site
Is there a way to list the duplicate values only once, using the vba code and formula above? I am not sure where to put the countif>1 statement in the formula bar, or in the vba itself. Please help
This comment was minimized by the moderator on the site
you can add two extra condition to skip blank cells and to skip duplicates:For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells(i).Value = Condition Then
If ConcatenateRange.Cells(i).Value <> "" Then 'SKIP BANKS
If InStr(xResult, ConcatenateRange.Cells(i).Value) = 0 Then 'SKIP IF FOUND DUPLICATE
xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
End If
End If
End If
Next i
This comment was minimized by the moderator on the site
This is amazing but i am looking for something else, i have a table with RollNo StudentName sub1, sub2, sub3 ... Total Result, When I enter Rollnumber it should give a result like "SName Sub1 64, sub2 78,... Total 389, Result pass", is it possible
This comment was minimized by the moderator on the site
Loved the function for Excel 2013 but amended it slightly to change the separating character to ";" instead of " " and then remove the prefixed ";" from the concantenated values Results matching values in my example would have ;result01 or ;result01;result02 . Added the extra If Left(xResult, 1) = ";" to remove any extra ";" at the beginning of the string if it is the 1st character. I'm sure there is a neater way of doing it but it worked for me. :) Function CusVlookup(pValue As String, pWorkRng As Range, pIndex As Long) Dim rng As Range Dim xResult As String xResult = "" For Each rng In pWorkRng If rng = pValue Then xResult = xResult & ";" & rng.Offset(0, pIndex - 1) If Left(xResult, 1) = ";" Then xResult = MID(xResult,2,255) End If End If Next CusVlookup = xResult End Function
This comment was minimized by the moderator on the site
Make if condition for result if empty.

Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice 20151118
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange
If x = lookupval Then
If Not result = "" Then
result = result & " " & x.Offset(0, indexcol - 1)
Else
result = x.Offset(0, indexcol - 1)
End If
Next x
CusVlookup = result
End Function
This comment was minimized by the moderator on the site
When using the cusvlookup is there a way to add the last name as well with a comma in between that might appear in Column C
This comment was minimized by the moderator on the site
How to get the result. Please help. data data1 result a 1 a1 b 2 a2 c b1 b2 c1 c2
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations