Obțineți valori multiple de căutare într-o singură celulă (cu și fără repetare)

Putem căuta și returna mai multe valori într-o singură celulă din Excel (separate prin virgulă sau spațiu)?

Mi s-a pus această întrebare de mai multe ori de mulți dintre colegii și cititorii mei.

Excel are câteva formule uimitoare de căutare, cum ar fi VLOOKUP, INDEX / MATCH (și acum XLOOKUP), dar niciuna dintre acestea nu oferă o modalitate de a returna mai multe valori de potrivire. Toate acestea funcționează prin identificarea primului meci și returnarea acestuia.

Așa că am făcut un pic de codare VBA pentru a veni cu o funcție personalizată (numită și funcție definită de utilizator) în Excel.

Actualizați: După ce Excel a lansat tablouri dinamice și funcții minunate, cum ar fi UNIQUE și TEXTJOIN, acum este posibil să utilizați o formulă simplă și returnează toate valorile potrivite într-o singură celulă (acoperit în acest tutorial).

În acest tutorial, vă voi arăta cum să faceți acest lucru (dacă utilizați cea mai recentă versiune de Excel - Microsoft 365 cu toate funcțiile noi), precum și o modalitate de a face acest lucru în cazul în care utilizați versiuni mai vechi ( folosind VBA).

Asadar, haideti sa începem!

Căutați și returnați mai multe valori într-o singură celulă (folosind formula)

Dacă utilizați Excel 2016 sau versiunile anterioare, accesați secțiunea următoare unde vă arăt cum să faceți acest lucru folosind VBA.

Cu abonamentul Microsoft 365, Excel-ul dvs. are acum funcții și funcții mult mai puternice care nu există în versiunile anterioare (cum ar fi XLOOKUP, Dynamic Arrays, funcții UNIQUE / FILTER etc.)

Deci, dacă utilizați Microsoft 365 (cunoscut anterior ca Office 365), puteți utiliza metodele acoperite în această secțiune ar putea căuta și returna mai multe valori într-o singură celulă din Excel.

Și, după cum veți vedea, este o formulă foarte simplă.

Mai jos am un set de date în care am numele persoanelor din coloana A și instruirea pe care au urmat-o în coloana B.

Faceți clic aici pentru a descărca fișierul de exemplu și a continua

Pentru fiecare persoană, vreau să aflu ce pregătire au urmat. În coloana D, am lista cu nume unice (din coloana A) și vreau să caut rapid și să extrag toate cursurile pe care le-a făcut fiecare persoană și să le obțin într-un singur set (separat printr-o virgulă).

Mai jos este formula care va face acest lucru:

= TEXTJOIN (",", ADEVĂRAT, DACĂ (D2 = $ A $ 2: $ A $ 20, $ B $ 2: $ B $ 20, ""))

După introducerea formulei în celula E2, copiați-o pentru toate celulele în care doriți rezultatele.

Cum funcționează această formulă?

Permiteți-mi să deconstruiesc această formulă și să explic fiecare parte a modului în care se reunesc ne dă rezultatul.

Testul logic din formula IF (D2 = $ A $ 2: $ A $ 20) verifică dacă celula de nume D2 este aceeași cu cea din intervalul A2: A20.

Trece prin fiecare celulă din intervalul A2: A20 și verifică dacă numele este același în celula D2 sau nu. dacă este același nume, revine ADEVĂRAT, altfel revine FALS.

Deci, această parte a formulei vă va oferi o matrice așa cum se arată mai jos:

{ADEVĂRAT; FALS; FALS; ADEVĂR; FALS; FALS; FALS; FALS; FALS; FALS; FALS; FALS; FALS; FALS; FALS; FALS; FALS; FALS; FALS}

Întrucât dorim doar să obținem instruirea pentru Bob (valoarea din celula D2), trebuie să obținem tot antrenamentul corespunzător pentru celulele care returnează TRUE în matricea de mai sus.

Acest lucru se face cu ușurință prin specificarea [value_if_true] parte a formulei IF ca interval care are pregătirea. Acest lucru asigură că, dacă numele din celula D2 se potrivește cu numele din intervalul A2: A20, formula IF va returna toată pregătirea pe care a urmat-o persoana respectivă.

Și oriunde matricea returnează un FALS, am specificat valoarea [value_if_false] ca „” (gol), deci returnează un gol.

Partea IF a formulei returnează matricea așa cum se arată mai jos:

{„Excel”; ””; ””; ”PowerPoint”; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””}

Unde are numele antrenamentului pe care l-a luat Bob și goluri oriunde numele nu era Bob.

Acum, tot ce trebuie să facem este să combinăm aceste nume de antrenament (separate printr-o virgulă) și să le returnăm într-o singură celulă.

Și asta se poate face cu ușurință folosind noua formulă TEXTJOIN (disponibilă în Excel2021-2022 și Excel în Microsoft 365)

Formula TEXTJOIN ia trei argumente:

• Delimitatorul - care este „,” în exemplul nostru, deoarece vreau ca antrenamentul să fie separat printr-o virgulă și un caracter spațial
• ADEVĂRAT - care spune formulei TEXTJOIN să ignore celulele goale și să le combine doar pe cele care nu sunt goale
• Formula If care returnează textul care trebuie combinat

Dacă utilizați Excel în Microsoft 365 care are deja tablouri dinamice, puteți introduce formula de mai sus și apăsați Enter. Și dacă utilizați Excel2021-2022, trebuie să introduceți formula și să țineți apăsată tasta Control și Shift și apoi apăsați Enter

Faceți clic aici pentru a descărca fișierul de exemplu și a continua

Obțineți valori multiple de căutare într-o singură celulă (fără repetare)

Deoarece formula UNICĂ este disponibilă numai pentru Excel în Microsoft 365, nu veți putea utiliza această metodă în Excel2021-2022

În cazul în care există repetări în setul de date, așa cum se arată mai jos, trebuie să schimbați puțin formula, astfel încât să obțineți doar o listă de valori unice într-o singură celulă.

În setul de date de mai sus, unii oameni au urmat instruirea de mai multe ori. De exemplu, Bob și Stan au urmat antrenamentul Excel de două ori, iar Betty a urmat antrenamentul MS Word de două ori. Dar în rezultatul nostru, nu vrem să repetăm ​​numele antrenamentului.

Puteți utiliza formula de mai jos pentru a face acest lucru:

= TEXTJOIN (",", TRUE, UNIQUE (IF (D2 = $ A $ 2: $ A $ 20, $ B $ 2: $ B $ 20, "")))

Formula de mai sus funcționează în același mod, cu o modificare mică. am folosit formula IF în cadrul funcției UNIQUE astfel încât, în cazul în care există repetări în formula dacă rezultatul, funcția UNIQUE ar elimina-o.

Faceți clic aici pentru a descărca fișierul de exemplu

Căutați și returnați mai multe valori într-o singură celulă (folosind VBA)

Dacă utilizați Excel 2016 sau versiunile anterioare, atunci nu veți avea acces la formula TEXTJOIN. Deci, cel mai bun mod de a căuta apoi și de a obține mai multe valori de potrivire într-o singură celulă este folosind o formulă personalizată pe care o puteți crea utilizând VBA.

Pentru a obține mai multe valori de căutare într-o singură celulă, trebuie să creăm o funcție în VBA (similară funcției VLOOKUP) care verifică fiecare celulă dintr-o coloană și dacă valoarea de căutare este găsită, o adaugă la rezultat.

Iată codul VBA care poate face acest lucru:

'Code by Sumit Bansal (https://trumpexcel.com) Funcție SingleCellExtract (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long Dim Result As String For i = 1 To LookupRange.Columns (1). .Count If LookupRange.Cells (i, 1) = Lookupvalue Then Result = Result & "" & LookupRange.Cells (i, ColumnNumber) & "," End If Next i SingleCellExtract = Left (Result, Len (Result) - 1) Funcția de sfârșit

Unde să puneți acest cod?

1. Deschideți un registru de lucru și faceți clic pe Alt + F11 (aceasta deschide fereastra VBA Editor).
2. În această fereastră VBA Editor, în stânga, există un explorator de proiecte (unde sunt listate toate registrele de lucru și foile de lucru). Faceți clic dreapta pe orice obiect din registrul de lucru unde doriți să funcționeze acest cod și accesați Insert -> Module.
3. În fereastra modulului (care va apărea în dreapta), copiați și lipiți codul de mai sus.
4. Acum ești pregătit. Mergeți la orice celulă din registrul de lucru și tastați = SingleCellExtract și conectați argumentele de intrare necesare (de exemplu, LookupValue, LookupRange, ColumnNumber).

Cum funcționează această formulă?

Această funcție funcționează similar cu funcția VLOOKUP.

Sunt necesare 3 argumente ca intrări:

1. Lookupvalue - Un șir pe care trebuie să îl căutăm într-o gamă de celule.
2. LookupRange - O serie de celule de unde trebuie să preluăm datele ($ B3: $ C18 în acest caz).
3. Număr coloană - Este numărul coloanei tabelului / matricei din care trebuie returnată valoarea potrivită (2 în acest caz).

Când utilizați această formulă, aceasta verifică fiecare celulă din coloana din stânga din intervalul de căutare iar când găsește o potrivire, se adaugă la rezultatul din celula în care ați folosit formula.

Tine minte: Salvați registrul de lucru ca registru de lucru activat pentru macro (.xlsm sau .xls) pentru a refolosi din nou această formulă. De asemenea, această funcție ar fi disponibilă numai în acest registru de lucru și nu în toate registrele de lucru.

Faceți clic aici pentru a descărca fișierul de exemplu

Aflați cum să automatizați sarcinile repetitive plictisitoare cu VBA în Excel. Alatura-te Curs Excel VBA

Obțineți valori multiple de căutare într-o singură celulă (fără repetare)

Este posibil să aveți repetări în date.

Dacă utilizați codul utilizat mai sus, acesta vă va oferi și repetări în rezultat.

Dacă doriți să obțineți rezultatul acolo unde nu există repetări, trebuie să modificați puțin codul.

Iată codul VBA care vă va oferi mai multe valori de căutare într-o singură celulă fără repetări.

'Code by Sumit Bansal (https://trumpexcel.com) Funcție MultipleLookupNoRept (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i Res Long Dim Rezultat Ca șir pentru i = 1 To LookupRange.Columns (1). .Count If LookupRange.Cells (i, 1) = Lookupvalue Then For J = 1 To i - 1 If LookupRange.Cells (J, 1) = LookupRange.Cells (J, ColumnNumber) = LookupRange.Cells (i, ColumnNumber) Apoi GoTo Skip End If End If Next J Result = Rezultat & "" & LookupRange.Cells (i, ColumnNumber) & "," Skip: End if Next i MultipleLookupNoRept = Left (Result, Len (Result) - 1) End Funcţie

După ce ați plasat acest cod în Editorul VB (așa cum se arată mai sus în tutorial), veți putea utiliza fișierul MultipleLookupNoRept funcţie.

Iată un instantaneu al rezultatului pe care îl veți obține cu acest lucru MultipleLookupNoRept funcţie.

Faceți clic aici pentru a descărca fișierul de exemplu

În acest tutorial, am prezentat modul de utilizare a formulelor și VBA în Excel pentru a găsi și a returna mai multe valori de căutare într-o singură celulă în Excel.

Deși se poate face cu ușurință cu o formulă simplă dacă utilizați Excel în abonamentul Microsoft 365, dacă utilizați versiuni anterioare și nu aveți acces la funcții precum TEXTJOIN, puteți face acest lucru folosind VBA creând funcție personalizată proprie.