Gå til hovedindhold

Hvordan returneres flere opslagsværdier i en kommaadskilt celle?

I Excel kan vi anvende VLOOKUP-funktionen til at returnere den første matchede værdi fra en tabelceller, men nogle gange er vi nødt til at udtrække alle matchende værdier og derefter adskilles af en bestemt afgrænser, såsom komma, bindestreg osv ... i en enkelt celle som følgende skærmbillede vist. Hvordan kunne vi få og returnere flere opslagsværdier i en kommasepareret celle i Excel?

doc returnerer flere værdier komma adskilt 1

Returner flere opslagsværdier i en kommaadskilt celle med brugerdefineret funktion

Returner flere opslagsværdier i en kommaadskilt celle med Kutools til Excel

Returner flere opslagsværdier i en kommaadskilt celle med brugerdefineret funktion

Normalt er der ingen direkte måde for os at udtrække og returnere de flere matchende værdier og adskilt med komma i en celle. Her kan du oprette en brugerdefineret funktion til at løse dette job, gør som følger:

1. Hold nede ALT + F11 nøgler til at åbne Microsoft Visual Basic til applikationer vindue.

2. Klik indsatte > Moduler, og indsæt følgende kode i Moduler Vindue.

VBA-kode: Returner flere opslagsværdier i en kommasepareret celle

Function SingleCellExtract(LookupValue As String, LookupRange As Range, ColumnNumber As Integer, Char As String)
'Updateby Extendoffice
    Dim I As Long
    Dim xRet As String
    For I = 1 To LookupRange.Columns(1).Cells.Count
        If LookupRange.Cells(I, 1) = LookupValue Then
            If xRet = "" Then
                xRet = LookupRange.Cells(I, ColumnNumber) & Char
            Else
                xRet = xRet & "" & LookupRange.Cells(I, ColumnNumber) & Char
            End If
        End If
    Next
    SingleCellExtract = Left(xRet, Len(xRet) - 1)
End Function

3. Gem derefter denne kode, og luk modulvinduet, gå tilbage til dit regneark, og indtast denne formel: = SingleCellExtract (D2, A2: B15,2, ",") i en tom celle, som du vil returnere resultatet. Og tryk derefter på Indtast nøgle for at få resultatet, se skærmbillede:

doc returnerer flere værdier komma adskilt 2

Bemærk: I ovenstående formel:

D2: angiver de celleværdier, du vil slå op;

A2: B15: er det dataområde, som du vil hente dataene;

2: tallet 2 er det kolonnenummer, som matchende værdi skal returneres;

,: kommaet er separatoren, som du vil adskille de flere værdier.

Du kan ændre dem til dit behov.

Returner flere opslagsværdier i en kommaadskilt celle med Kutools til Excel

Hvis du har Kutools til Excel, denne opgave vil ikke længere være et problem. Det Avancerede kombinere rækker hjælpeprogram kan hjælpe dig med at kombinere alle relative værdier baseret på en kolonne.

Kutools til Excel : med mere end 300 praktiske Excel-tilføjelsesprogrammer, gratis at prøve uden begrænsning på 30 dage. 
Gå til Download
Gratis prøveperiode 30 dageKøb
PayPal / MyCommerce

Efter installation Kutools til Excelskal du gøre som følger:

1. Vælg det dataområde, som du vil kombinere alle matchende værdier baseret på en kolonne.

2. Klik Kutools > Flet og del > Avancerede kombinere rækker, se skærmbillede:

3. I Kombiner rækker baseret på kolonne Klik på det kolonnenavn, du vil kombinere baseret på, og klik derefter på Primærnøgle knap, se skærmbillede:

doc returnerer flere værdier komma adskilt 4

4. Klik derefter på et andet kolonnenavn, som du vil kombinere deres matchede værdier, og klik på Kombiner for at vælge en separator for at adskille de kombinerede værdier, se skærmbillede:

doc returnerer flere værdier komma adskilt 5

5. Klik derefter på OK knap, er alle de tilsvarende celler med den samme værdi kombineret til en celle, der er adskilt med kommaet, se skærmbilleder:

doc returnerer flere værdier komma adskilt 6 2 doc returnerer flere værdier komma adskilt 7

Klik for at få flere oplysninger om dette Advanced Combine Rows-værktøj ...

Download og gratis prøveversion Kutools til Excel nu!

Demo: Returner flere opslagsværdier i en kommaadskilt celle med Kutools til Excel

Kutools til Excel: med mere end 300 praktiske Excel-tilføjelsesprogrammer, gratis at prøve uden begrænsning på 30 dage. Download og gratis prøveversion nu!

Bedste kontorproduktivitetsværktøjer

🤖 Kutools AI Aide: Revolutionér dataanalyse baseret på: Intelligent udførelse   |  Generer kode  |  Opret brugerdefinerede formler  |  Analyser data og generer diagrammer  |  Aktiver Kutools funktioner...
Populære funktioner: Find, fremhæv eller identificer dubletter   |  Slet tomme rækker   |  Kombiner kolonner eller celler uden at miste data   |   Runde uden formel ...
Super opslag: VLookup med flere kriterier  |   Multiple Value VLookup  |   VOpslag på tværs af flere ark   |   Fuzzy Lookup ....
Avanceret rulleliste: Opret hurtigt rulleliste   |  Afhængig rulleliste   |  Multivælg rulleliste ....
Column Manager: Tilføj et bestemt antal kolonner  |  Flyt kolonner  |  Skift synlighedsstatus for skjulte kolonner  |  Sammenlign områder og kolonner ...
Fremhævede funktioner: Grid fokus   |  Designvisning   |   Stor Formel Bar   |  Arbejdsbog & Ark Manager   |  Ressourcebibliotek (Autotekst)   |  Datovælger   |  Kombiner regneark   |  Krypter/Dekrypter celler   |  Send e-mails efter liste   |  Superfilter   |   Specielt filter (filter fed/kursiv/gennemstreget...) ...
Top 15 værktøjssæt:  12 tekst Værktøjer (tilføje tekst, Fjern tegn, ...)   |   50 + Chart Typer (Gantt kort, ...)   |   40+ Praktisk formler (Beregn alder baseret på fødselsdag, ...)   |   19 Indsættelse Værktøjer (Indsæt QR-kode, Indsæt billede fra sti, ...)   |   12 Konvertering Værktøjer (Tal til ord, Valutaomregning, ...)   |   7 Flet og del Værktøjer (Avancerede kombinere rækker, Opdel celler, ...)   |   ... og mere

Overlad dine Excel-færdigheder med Kutools til Excel, og oplev effektivitet som aldrig før. Kutools til Excel tilbyder over 300 avancerede funktioner for at øge produktiviteten og spare tid.  Klik her for at få den funktion, du har mest brug for...

Beskrivelse

Fanen Office bringer en grænseflade til et kontor med Office, og gør dit arbejde meget lettere

  • Aktiver redigering og læsning af faner i Word, Excel, PowerPoint, Publisher, Access, Visio og Project.
  • Åbn og opret flere dokumenter i nye faner i det samme vindue snarere end i nye vinduer.
  • Øger din produktivitet med 50 % og reducerer hundredvis af museklik for dig hver dag!
Comments (19)
Rated 5 out of 5 · 2 ratings
Bruno
Bruno
This comment was minimized by the moderator on the site
Como faço para ajustar a função do "MultipleValues" para ignorar células vazias?

Pois no meu caso tenho algumas células em branco e quando faço o merge, fico com algumas vírgulas repetidas.

Ex.: (001,002,,,,003).

Obrigado!
Reply
0
0
skyyang
skyyang     Bruno
This comment was minimized by the moderator on the site
Hello, Bruno,
To ignore the blank cells when combine, pleae apply the following code:

Function SingleCellExtract(LookupValue As String, LookupRange As Range, ColumnNumber As Integer, Char As String) As String
    Dim I As Long
    Dim xRet As String
    For I = 1 To LookupRange.Columns(1).Cells.Count
        If LookupRange.Cells(I, 1) = LookupValue Then
            ' Check if the cell in the specified column is not empty before appending
            If Not IsEmpty(LookupRange.Cells(I, ColumnNumber)) Then
                If xRet = "" Then
                    xRet = LookupRange.Cells(I, ColumnNumber) & Char
                Else
                    xRet = xRet & LookupRange.Cells(I, ColumnNumber) & Char
                End If
            End If
        End If
    Next
    If Len(xRet) > 0 Then
        SingleCellExtract = Left(xRet, Len(xRet) - 1)
    Else
        SingleCellExtract = ""
    End If
End Function


Please have a try, hope it can help you!
Reply
Report
0
0
Bruno
Bruno     skyyang
This comment was minimized by the moderator on the site
Hello skyyang,

Yes, it works!

Thank you!
Reply
0
0
Yery
Yery
This comment was minimized by the moderator on the site
No soy programador pero necesito ayuda para que la función de abajo en lugar de devolverme esto

S-01-08-0017->Micro Post 1R, Black, Stainless Steel -End,->4;S-01-08-0057->Micro Post 2R, Black, Stainless Steel -End,->2

me devuelva los valores en líneas diferentes.
S-01-08-0017->Micro Post 1R, Black, Stainless Steel -End,->4
S-01-08-0057->Micro Post 2R, Black, Stainless Steel -End,->2

La función es:
Function SingleCellExtract(LookupValue As String, LookupRange As Range, ColumnNumber As Integer, Char As String)
'Updateby Extendoffice
Dim I As Long
Dim xRet As String
For I = 1 To LookupRange.Columns(1).Cells.Count
If LookupRange.Cells(I, 1) = LookupValue Then
If xRet = "" Then
xRet = LookupRange.Cells(I, ColumnNumber) & Char
Else
xRet = xRet & "" & LookupRange.Cells(I, ColumnNumber) & Char
End If
End If
Next
SingleCellExtract = Left(xRet, Len(xRet) - 1)
End Function
Reply
0
0
skyyang
skyyang     Yery
This comment was minimized by the moderator on the site
Hello, Yery,
Did you mean to split a cell into multiple rows based on the semicolon character?
If so, the following VBA code can help you:
Sub SplitAll()
    Dim xRg As Range
    Dim xRg1 As Range
    Dim xCell As Range
    Dim I As Long
    Dim xAddress As String
    Dim xUpdate As Boolean
    Dim xRet As Variant
    On Error Resume Next
    xAddress = Application.ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select a range", "Kutools for Excel", xAddress, , , , , 8)
    Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
    If xRg Is Nothing Then Exit Sub
        If xRg.Columns.Count > 1 Then
            MsgBox "You can't select multiple columns", , "Kutools for Excel"
            Exit Sub
            End If
            Set xRg1 = Application.InputBox("Split to (single cell):", "Kutools for Excel", , , , , , 8)
            Set xRg1 = xRg1.Range("A1")
            If xRg1 Is Nothing Then Exit Sub
                xUpdate = Application.ScreenUpdating
                Application.ScreenUpdating = False
                For Each xCell In xRg
                    xRet = Split(xCell.Value, ";")
                    xRg1.Worksheet.Range(xRg1.Offset(I, 0), xRg1.Offset(I + UBound(xRet, 1), 0)) = Application.WorksheetFunction.Transpose(xRet)
                    I = I + UBound(xRet, 1) + 1
                Next
                Application.ScreenUpdating = xUpdate
            End Sub

Please have a try, hope it can help you!
Reply
Report
0
0
Manikanta
Manikanta
This comment was minimized by the moderator on the site
Hi,

I want exact validation code for multiple values separated by comma and space for each value.

Example:
Lucy, Tom, Nicol, Akash, Apple

Please replay if you have any suggestions.
Reply
0
0
Rated 4.5 out of 5
skyyang
skyyang     Manikanta
This comment was minimized by the moderator on the site
Hello， Manikanta
To separate the multiple values by a comma and space, you just need to add a space behind the comma, change the formula as this: =SingleCellExtract(D2,A2:B15,2,", ").
Please try, hope it can help you!
Reply
Report
0
0
Manikanta
Manikanta     skyyang
This comment was minimized by the moderator on the site
Hi Skyyang,

Thanks for your replay!

I tried already same way but in the cell value last one extra comma (,) coming below is the example.

Lucy, Tom, Nicol, Akash, Apple,

This will not work for Json file, so I want values separated by comma and space like below.

Lucy, Tom, Nicol, Akash, Apple

Thank you!
Reply
0
0
skyyang
skyyang     Manikanta
This comment was minimized by the moderator on the site
Hello, Manikanta
In this case, you can apply the below User Defined Function:

Function MultipleValues(work_range As Range, criteria As Variant, merge_range As Range, Optional Separator As String = ",") As Variant
Dim outcome As String
On Error Resume Next
If work_range.Count <> merge_range.Count Then
MultipleValues = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To work_range.Count
If work_range.Cells(i).Value = criteria Then
outcome = outcome & Separator & merge_range.Cells(i).Value
End If
Next i
If outcome <> "" Then
outcome = VBA.Mid(outcome, VBA.Len(Separator) + 1)
End If
MultipleValues = outcome
Exit Function
End Function


After pasting the code, please use this formula: =MultipleValues($A$2:$A$15,D2,$B$2:$B$15,", ")

Please try, hope this can help you!
If you still have any other problem, please comment here.
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-return-multiple-matching-1.png
Reply
Report
0
0
Manikanta
Manikanta     skyyang
This comment was minimized by the moderator on the site
Hi Skyyang,

This is working now, Thank you for your quick response.

It's very use full to me once again Thank you for your help.

Regards,
Manikanta.
Reply
0
0
Rated 5 out of 5
KPRS92
KPRS92
This comment was minimized by the moderator on the site
Good Morning,

the VBA code worked perfectly with my worksheet, pretty clear and simple, however, I was trying to find a way to tell excel to only return the unique values. Would that be possible using this same code?
Reply
Report
0
0
Rated 5 out of 5
Biplab Das
Biplab Das
This comment was minimized by the moderator on the site
When 2 Criteria Match then Return Multiple Lookup Values In One Comma Separated Cell
A2=B2 Then Result From Range by "SingleCellExtract" - Please.......
Reply
0
0
abhi
abhi
This comment was minimized by the moderator on the site
Error pops up if increase the array size
Reply
0
0
Js
Js
This comment was minimized by the moderator on the site
VB command breaks when the range is longer than 154 rows (ie :B154)....
Reply
0
0
M Macklam
M Macklam
This comment was minimized by the moderator on the site
thank you, firstly i managed to get this to work without the slow down in performance. I'm using values rather that text so my question is i want to bring back all those with less than say 19 points in a list. Can the single cell Extract work for that or does it have to be a specific value?
Reply
0
0
A Ward
A Ward
This comment was minimized by the moderator on the site
This simply does not work. I was unable to get it to work in my own application, so I copy/pasted the vba and the formula and it returned an error every time
Reply
0
0
K Ray
K Ray
This comment was minimized by the moderator on the site
This works but slows down my excel majorly! Any tips to help speed?
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations