Dobijte više vrijednosti traženja u jednoj ćeliji (sa i bez ponavljanja)

Možemo li potražiti i vratiti više vrijednosti u jednoj ćeliji u Excelu (odvojene zarezom ili razmakom)?

Mnoge su mi kolege i čitatelji više puta postavljali ovo pitanje.

Excel ima neke nevjerojatne formule za pretraživanje, kao što su VLOOKUP, INDEX/MATCH (i sada XLOOKUP), ali niti jedna od ovih ne nudi način za vraćanje više odgovarajućih vrijednosti. Svi ovi radovi identificiraju prvu utakmicu i vraćaju je.

Tako sam napravio malo VBA kodiranja kako bih došao do prilagođene funkcije (koja se naziva i korisnički definirana funkcija) u Excelu.

Ažuriraj: Nakon što je Excel objavio dinamičke nizove i izvrsne funkcije, poput UNIQUE i TEXTJOIN, sada je moguće koristiti jednostavnu formulu i vratiti sve odgovarajuće vrijednosti u jednu ćeliju (obrađeno u ovom vodiču).

U ovom vodiču pokazat ću vam kako to učiniti (ako koristite najnoviju verziju programa Excel - Microsoft 365 sa svim novim funkcijama), kao i način na koji to možete učiniti u slučaju da koristite starije verzije ( pomoću VBA).

Pa krenimo!

Potražite i vratite više vrijednosti u jednu ćeliju (pomoću formule)

Ako koristite Excel 2016 ili starije verzije, prijeđite na sljedeći odjeljak gdje ću pokazati kako to učiniti pomoću VBA.

Uz pretplatu na Microsoft 365, vaš Excel sada ima puno snažnije funkcije i značajke kojih nema u prethodnim verzijama (kao što su XLOOKUP, dinamički nizovi, funkcije UNIQUE/FILTER itd.)

Dakle, ako koristite Microsoft 365 (ranije poznat kao Office 365), možete koristiti metode opisane u ovom odjeljku za traženje i vraćanje više vrijednosti u jednu ćeliju u Excelu.

I kao što ćete vidjeti, to je doista jednostavna formula.

Ispod imam skup podataka gdje imam imena ljudi u stupcu A i obuku koju su prošli u stupcu B.

Kliknite ovdje za preuzimanje datoteke primjera i slijedite je

Za svaku osobu želim saznati koju su obuku završili. U stupcu D imam popis jedinstvenih imena (iz stupca A) i želim brzo pronaći i izdvojiti svu obuku koju je svaka osoba prošla te ih dobiti u jednom skupu (odvojenom zarezom).

Ispod je formula koja će to učiniti:

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

Nakon što unesete formulu u ćeliju E2, kopirajte je za sve ćelije u koje želite rezultate.

Kako funkcionira ova formula?

Dopustite mi da dekonstruiram ovu formulu i objasnim svaki dio kako se ona spaja daje nam rezultat.

Logički test u formuli IF (D2 = $ A $ 2: $ A $ 20) provjerava je li ćelija naziva D2 ista kao ona u rasponu A2: A20.

Prolazi kroz svaku ćeliju u rasponu A2: A20 i provjerava je li naziv isti u ćeliji D2 ili ne. ako je isti naziv, vraća TRUE, inače vraća FALSE.

Dakle, ovaj dio formule će vam dati niz kao što je prikazano u nastavku:

{TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}

Budući da želimo samo proći obuku za Boba (vrijednost u ćeliji D2), moramo proći sav odgovarajući trening za ćelije koje vraćaju TRUE u gornji niz.

To se lako postiže navođenjem [value_if_true] dijela formule IF kao raspona koji ima obuku. To osigurava da ako se naziv u ćeliji D2 podudara s imenom u rasponu A2: A20, formula IF vraća sve obuke koje je ta osoba prošla.

I gdje god polje vrati FALSE, naveli smo vrijednost [value_if_false] kao “” (prazno), pa vraća prazno.

IF dio formule vraća niz kao što je prikazano u nastavku:

{“Excel”; ””; ””; ”PowerPoint”; ””; ””; ””; ””; ””; ””; ””; ””; ”” ”” ”” ”” ”” ; ””; ””; ””}

Tamo gdje ima imena obuke koju je Bob prošao i prazna mjesta gdje god se nije zvao Bob.

Sada, sve što trebamo učiniti je kombinirati ove nazive treninga (odvojene zarezom) i vratiti ga u jednu ćeliju.

A to se lako može učiniti pomoću nove formule TEXTJOIN (dostupna u Excel2021-2022 i Excel u Microsoft 365)

Formula TEXTJOIN ima tri argumenta:

• razdjelnik - koji je u našem primjeru "," jer želim da se trening odvoji zarezom i razmakom
• TRUE - koja govori formuli TEXTJOIN da zanemaruje prazne ćelije i kombinira samo one koje nisu prazne
• Formula If koja vraća tekst koji je potrebno kombinirati

Ako koristite Excel u sustavu Microsoft 365 koji već ima dinamičke nizove, možete samo unijeti gornju formulu i pritisnuti enter. A ako koristite Excel2021-2022, morate unijeti formulu i držati tipku Control i Shift, a zatim pritisnuti Enter

Kliknite ovdje za preuzimanje datoteke primjera i slijedite je

Dobijte više vrijednosti traženja u jednoj ćeliji (bez ponavljanja)

Budući da je formula UNIQUE dostupna samo za Excel u Microsoft 365, nećete moći koristiti ovu metodu u Excelu 2021-2022

U slučaju da u vašem skupu podataka ima ponavljanja, kao što je prikazano u nastavku, morate malo promijeniti formulu tako da dobijete samo popis jedinstvenih vrijednosti u jednoj ćeliji.

U gornjem skupu podataka neki su ljudi prošli obuku više puta. Na primjer, Bob i Stan su dva puta prošli Excel program, a Betty dva puta MS Word. Ali u našem rezultatu ne želimo da se naziv treninga ponavlja.

Za to možete koristiti donju formulu:

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

Gornja formula radi na isti način, s manjom promjenom. upotrijebili smo formulu IF unutar funkcije UNIQUE kako bi je, u slučaju ponavljanja u rezultatu formule if, funkcija UNIQUE uklonila.

Kliknite ovdje za preuzimanje datoteke primjera

Potražite i vratite više vrijednosti u jednu ćeliju (pomoću VBA)

Ako koristite Excel 2016 ili starije verzije, nećete imati pristup formuli TEXTJOIN. Stoga je najbolji način da potražite i dobijete više odgovarajućih vrijednosti u jednoj ćeliji pomoću prilagođene formule koju možete stvoriti pomoću VBA.

Da bismo dobili više vrijednosti pretraživanja u jednoj ćeliji, moramo stvoriti funkciju u VBA (slično kao funkcija VLOOKUP) koja provjerava svaku ćeliju u stupcu i ako se pronađe vrijednost pretraživanja, dodaje je rezultatu.

Evo VBA koda koji to može učiniti:

'Kod prema Sumit Bansal (https://trumpexcel.com) Funkcija SingleCellExtract (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i Kao Long Dim Result As String For i = 1 Za LookupRange.Columns (1). .Count If LookupRange.Cells (i, 1) = Lookupvalue Then Result = Result & "" & LookupRange.Cells (i, ColumnNumber) & "," End If Next i SingleCellExtract = Left (Rezultat, Len (Rezultat) - 1) Završna funkcija

Gdje staviti ovaj kod?

1. Otvorite radnu knjigu i kliknite Alt + F11 (ovo otvara prozor VBA Editor).
2. U ovom prozoru VBA Editor, s lijeve strane, nalazi se istraživač projekata (gdje su navedene sve radne knjige i radni listovi). Desnom tipkom miša kliknite bilo koji objekt u radnoj knjizi na kojem želite da ovaj kôd radi, a zatim idite na Umetni -> Modul.
3. U prozoru modula (koji će se pojaviti s desne strane) kopirajte i zalijepite gornji kôd.
4. Sada ste spremni. Idite na bilo koju ćeliju u radnoj knjizi i upišite = SingleCellExtract i uključite potrebne ulazne argumente (tj. LookupValue, LookupRange, ColumnNumber).

Kako funkcionira ova formula?

Ova funkcija radi slično kao funkcija VLOOKUP.

Za ulaz su potrebna 3 argumenta:

1. Lookupvalue - Niz koji moramo potražiti u nizu ćelija.
2. LookupRange - Niz ćelija odakle moramo dohvatiti podatke ($ B3: $ C18 u ovom slučaju).
3. Broj stupca - To je broj stupca tablice/niza iz kojeg se mora vratiti odgovarajuća vrijednost (2 u ovom slučaju).

Kada koristite ovu formulu, ona provjerava svaku ćeliju u krajnjem lijevom stupcu u rasponu pretraživanja a kad pronađe podudarnost, dodaje rezultat u ćeliju u kojoj ste koristili formulu.

Zapamtiti: Spremite radnu knjigu kao radnu knjigu s omogućenim makroima (.xlsm ili .xls) da biste ponovno upotrijebili ovu formulu. Također, ova bi funkcija bila dostupna samo u ovoj radnoj knjizi, a ne u svim radnim bilježnicama.

Kliknite ovdje za preuzimanje datoteke primjera

Saznajte kako automatizirati dosadne ponavljajuće zadatke s VBA -om u Excelu. Pridruži se Excel VBA tečaj

Dobijte više vrijednosti traženja u jednoj ćeliji (bez ponavljanja)

Postoji mogućnost da se podaci ponavljaju.

Ako koristite gore korišteni kôd, dobit ćete i ponavljanja u rezultatu.

Ako želite dobiti rezultat gdje nema ponavljanja, morate malo izmijeniti kod.

Ovdje je VBA kôd koji će vam dati više vrijednosti pretraživanja u jednoj ćeliji bez ponavljanja.

'Kôd pomoću Sumit Bansal (https://trumpexcel.com) Funkcija MultipleLookupNoRept (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i Kao Long Dim Result As String For i = 1 To LookupRange.Columns (1). Cells .Count If LookupRange.Cells (i, 1) = Lookupvalue Zatim za J = 1 To i - 1 Ako je LookupRange.Cells (J, 1) = Lookupvalue Tada If LookupRange.Cells (J, ColumnNumber) = LookupRange.Cells (i, Broj stupca) Zatim idite na Preskoči kraj ako završi ako je sljedeće J rezultat = rezultat & "" & LookupRange.Cells (i, broj stupca) & "," Preskoči: završi ako je sljedeće i MultipleLookupNoRept = lijevo (rezultat, Len (rezultat) - 1) kraj Funkcija

Nakon što postavite ovaj kôd u VB Editor (kao što je gore prikazano u vodiču), moći ćete koristiti MultipleLookupNoRept funkcija.

Ovdje je kratki pregled rezultata koji ćete dobiti s ovim MultipleLookupNoRept funkcija.

Kliknite ovdje za preuzimanje datoteke primjera

U ovom sam vodiču opisao kako koristiti formule i VBA u Excelu za pronalaženje i vraćanje više vrijednosti pretraživanja u jednoj ćeliji u Excelu.

Iako se to lako može učiniti jednostavnom formulom ako koristite Excel u pretplati na Microsoft 365, ako koristite prethodne verzije i nemate pristup funkcijama kao što je TEXTJOIN, to ipak možete učiniti pomoću VBA stvaranjem vlastitu prilagođenu funkciju.