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

Cum se returnează mai multe valori de căutare într-o celulă separată prin virgule?

În Excel, putem aplica funcția VLOOKUP pentru a returna prima valoare potrivită dintr-o celulă de tabel, dar, uneori, trebuie să extragem toate valorile potrivite și apoi să le separăm printr-un delimitator specific, cum ar fi virgulă, liniuță etc. într-o singură celulă după cum se arată în următoarea captură de ecran. Cum am putea obține și returna mai multe valori de căutare într-o singură celulă separată prin virgule în Excel?

doc returnează mai multe valori separate prin virgulă 1

Returnează mai multe valori de căutare într-o celulă separată prin virgulă cu Funcția definită de utilizator

Returnează mai multe valori de căutare într-o celulă separată prin virgulă cu Kutools pentru Excel


Returnează mai multe valori de căutare într-o celulă separată prin virgulă cu Funcția definită de utilizator

În mod normal, nu există o modalitate directă de a extrage și a returna valorile de potrivire multiple și separate prin virgulă într-o singură celulă, aici puteți crea o funcție definită de utilizator pentru a rezolva această sarcină, vă rugăm să faceți următoarele:

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 Module Fereastră.

Cod VBA: returnează mai multe valori de căutare într-o singură celulă separată prin virgulă

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. Apoi salvați acest cod și închideți fereastra Module, reveniți la foaia de lucru și introduceți această formulă: = SingleCellExtract (D2, A2: B15,2, ",") într-o celulă goală în care doriți să returnați rezultatul. Și apoi apăsați Intrați cheie pentru a obține rezultatul, vezi captura de ecran:

doc returnează mai multe valori separate prin virgulă 2

notițe: În formula de mai sus:

D2: indică valorile celulei pe care doriți să le căutați;

A2: B15: este intervalul de date pe care doriți să îl preluați;

2: numărul 2 este numărul coloanei care trebuie returnată valoarea potrivită;

,: virgula este separatorul pe care doriți să separați valorile multiple.

Le puteți schimba după nevoile dvs.


Returnează mai multe valori de căutare într-o celulă separată prin virgulă cu Kutools pentru Excel

Dacă aveţi Kutools pentru Excel, această sarcină nu va mai fi o problemă. Rânduri combinate avansate utilitarul vă poate ajuta să combinați toate valorile relative pe baza unei coloane.

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ă combinați toate valorile potrivite pe baza unei coloane.

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

3. În Combinați rândurile pe baza coloanei 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 returnează mai multe valori separate prin virgulă 4

4. Apoi faceți clic pe numele altei coloane pe care doriți să le combinaț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 returnează mai multe valori separate prin virgulă 5

5. Apoi apasa OK buton, toate celulele corespunzătoare cu aceeași valoare au fost combinate într-o singură celulă care este separată cu virgula, a se vedea capturile de ecran:

doc returnează mai multe valori separate prin virgulă 6 2 doc returnează mai multe valori separate prin virgulă 7

Faceți clic pentru a afla mai multe detalii despre acest utilitar Advanced Combine Rows ...

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


Demo: Returnează mai multe valori de căutare într-o celulă separată prin virgule cu Kutools pentru Excel

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 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 (17)
Evaluat 5 din 5 · evaluări 2
Acest comentariu a fost redus la minimum de moderatorul de pe site
În timp ce vreau să lipesc și să salvez modulul, apare un mesaj pop-up care spune că verificatorul de compatibilitate cu pierderea semnificativă a funcționalității
Acest comentariu a fost redus la minimum de moderatorul de pe site
Mulțumesc pentru această postare. Știți cum aș proceda pentru a manipula cele două numere întregi separate pe care le creează acest lucru. De exemplu, să spunem că funcția „=SingleCellExtract” produce acum (1, 2). Există o modalitate de a avea lângă ea o celulă care face (1+.5, 2+.5)?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Acest lucru funcționează, dar îmi încetinește excelul în mare parte! Vreo sfaturi pentru a ajuta viteza?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Acest lucru pur și simplu nu funcționează. Nu am reușit să-l fac să funcționeze în propria mea aplicație, așa că am copiat/lipit vba și formula și a returnat o eroare de fiecare dată
Acest comentariu a fost redus la minimum de moderatorul de pe site
mulțumesc, în primul rând am reușit să fac acest lucru să funcționeze fără încetinirea performanței. Folosesc valori mai degrabă decât textul, așa că întrebarea mea este că vreau să îi aduc înapoi pe toți cei cu mai puțin de 19 puncte într-o listă. Extractul cu o singură celulă poate funcționa pentru asta sau trebuie să fie o anumită valoare?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Comanda VB se întrerupe atunci când intervalul este mai mare de 154 de rânduri (adică :B154)....
Acest comentariu a fost redus la minimum de moderatorul de pe site
Apare eroarea dacă creșteți dimensiunea matricei
Acest comentariu a fost redus la minimum de moderatorul de pe site
Când se potrivesc 2 criterii, returnați mai multe valori de căutare într-o celulă separată prin virgulă
A2=B2 Apoi rezultă din interval prin „SingleCellExtract” - Vă rugăm.......
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună dimineața,

codul VBA a funcționat perfect cu foaia mea de lucru, destul de clar și simplu, cu toate acestea, încercam să găsesc o modalitate de a spune Excel să returneze numai valorile unice. Ar fi posibil folosind același cod?
Evaluat 5 din 5
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună,

Vreau cod de validare exact pentru mai multe valori separate prin virgulă și spațiu pentru fiecare valoare.

Exemplu:
Lucy, Tom, Nicol, Akash, Apple

Vă rugăm să redați din nou dacă aveți sugestii.
Evaluat 4.5 din 5
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună, Manikanta
Pentru a separa valorile multiple printr-o virgulă și spațiu, trebuie doar să adăugați un spațiu în spatele virgulei, schimbați formula astfel: =SingleCellExtract(D2,A2:B15,2,", ").
Vă rugăm să încercați, sper că vă poate ajuta!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Salut Skyyang,

Mulțumesc pentru reluare!

Am încercat deja în același mod, dar în valoarea celulei ultima virgulă suplimentară (,) care vine mai jos este exemplul.

Lucy, Tom, Nicol, Akash, Apple,

Acest lucru nu va funcționa pentru fișierul Json, așa că vreau valori separate prin virgulă și spațiu, ca mai jos.

Lucy, Tom, Nicol, Akash, Apple

Mulțumesc!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună, Manikanta
În acest caz, puteți aplica funcția definită de utilizator de mai jos:

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


După ce lipiți codul, utilizați această formulă: =MultipleValues($A$2:$A$15,D2,$B$2:$B$15,", ")

Vă rugăm să încercați, sper că acest lucru vă poate ajuta!
Dacă mai aveți orice altă problemă, vă rugăm să comentați aici.
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-return-multiple-matching-1.png
Acest comentariu a fost redus la minimum de moderatorul de pe site
Salut Skyyang,

Acest lucru funcționează acum, vă mulțumesc pentru răspunsul rapid.

Îmi este foarte folositor încă o dată. Mulțumesc pentru ajutor.

Salutari,
Manikanta.
Evaluat 5 din 5
Acest comentariu a fost redus la minimum de moderatorul de pe site
No soy programador, pero necesito ayuda pentru că funcția de jos în loc de devolverme esto

S-01-08-0017->Micro stâlp 1R, negru, oțel inoxidabil -Capat,->4;S-01-08-0057->Micro stâlp 2R, negru, oțel inoxidabil -Capat,->2

îmi devuelva los valores în linii diferite.
S-01-08-0017->Micro stâlp 1R, negru, oțel inoxidabil -Capat,->4
S-01-08-0057->Micro stâlp 2R, negru, oțel inoxidabil -Capat,->2

Funcția este:
Funcția SingleCellExtract (LookupValue ca șir, LookupRange ca interval, ColumnNumber ca întreg, Char ca șir)
'Actualizare de Extendoffice
Dim I As Long
Dim xRet As String
Pentru I = 1 To LookupRange.Columns(1).Cells.Count
Dacă LookupRange.Cells(I, 1) = LookupValue, atunci
Dacă xRet = "" Atunci
xRet = LookupRange.Cells(I, ColumnNumber) & Char
Altfel
xRet = xRet & "" & LookupRange.Cells(I, ColumnNumber) & Char
Final, dacă
Final, dacă
Pagina Următoare →
SingleCellExtract = Left(xRet, Len(xRet) - 1)
Sfârşit Funcţia
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună, Yery,
Ați vrut să împărțiți o celulă în mai multe rânduri pe baza caracterului punct și virgulă?
Dacă da, următorul cod VBA vă poate ajuta:
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

Vă rugăm să încercați, sper că vă poate ajuta!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Unde/cum modific codul VBA pentru a produce „text” dacă nu se găsește o potrivire -- cu codul de mai sus, dacă nu este găsită o valoare, „#VALUE!” este afișat în celulă. Acesta nu este cel mai bun aspect pentru registrul de lucru. Mulțumesc.
Nu există comentarii postate aici încă
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