Pridobite več vrednosti iskanja v eni celici (z & brez ponavljanja)

Ali lahko v Excelu poiščemo in vrnemo več vrednosti v eni celici (ločene z vejico ali presledkom)?

To vprašanje so mi večkrat zastavili številni kolegi in bralci.

Excel ima nekaj neverjetnih iskalnih formul, kot so VLOOKUP, INDEX/MATCH (in zdaj XLOOKUP), vendar nobena od teh ne ponuja načina za vrnitev več ujemajočih se vrednosti. Vse to deluje tako, da prepozna prvo ujemanje in jo vrne.

Zato sem naredil nekaj kodiranja VBA, da bi v Excelu pripravil funkcijo po meri (imenovano tudi uporabniško določena funkcija).

Nadgradnja: Potem ko je Excel izdal dinamične matrike in odlične funkcije, kot sta UNIQUE in TEXTJOIN, je zdaj mogoče uporabiti preprosto formulo in vrne vse ujemajoče se vrednosti v eno celico (obravnavano v tej vadnici).

V tej vadnici vam bom pokazal, kako to storite (če uporabljate najnovejšo različico programa Excel - Microsoft 365 z vsemi novimi funkcijami), pa tudi način, kako to storiti, če uporabljate starejše različice ( z uporabo VBA).

Pa začnimo!

Iskanje in vrnitev več vrednosti v eni celici (uporaba formule)

Če uporabljate Excel 2016 ali starejše različice, pojdite na naslednji razdelek, kjer bom pokazal, kako to storiti z uporabo VBA.

Z naročnino na Microsoft 365 ima vaš Excel zdaj veliko zmogljivejše funkcije in funkcije, ki jih v prejšnjih različicah ni (na primer XLOOKUP, Dynamic Arrays, UNIQUE/FILTER itd.)

Če torej uporabljate Microsoft 365 (prej znan kot Office 365), lahko uporabite metode, opisane v tem razdelku, da poiščete in vrnete več vrednosti v eni sami celici v Excelu.

In kot boste videli, je to zelo preprosta formula.

Spodaj imam niz podatkov, kjer imam imena ljudi v stolpcu A in usposabljanje, ki so ga opravili v stolpcu B.

Kliknite tukaj, če želite prenesti primer datoteke in nadaljevati

Za vsako osebo želim izvedeti, kakšno usposabljanje so opravili. V stolpcu D imam seznam edinstvenih imen (iz stolpca A) in želim hitro poiskati in izvleči vse usposabljanje, ki ga je opravila vsaka oseba, ter jih dobiti v enem samem nizu (ločenih z vejico).

Spodaj je formula, ki bo to naredila:

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

Ko vnesete formulo v celico E2, jo kopirajte za vse celice, kjer želite rezultate.

Kako deluje ta formula?

Dovolite mi, da razčlenim to formulo in razložim vsak del tega, kako se združuje, in tako dobimo rezultat.

Logični test v formuli IF (D2 = $ A $ 2: $ A $ 20) preveri, ali je imenska celica D2 enaka tisti v območju A2: A20.

Gre skozi vsako celico v območju A2: A20 in preveri, ali je ime v celici D2 enako ali ne. če je isto ime, vrne TRUE, sicer vrne FALSE.

Tako vam bo ta del formule dal matriko, kot je prikazano spodaj:

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

Ker želimo samo pridobiti usposabljanje za Boba (vrednost v celici D2), moramo opraviti vse ustrezno usposabljanje za celice, ki vračajo TRUE v zgornjem nizu.

To preprosto naredite tako, da kot vrednost območja, ki ima usposabljanje, določite [value_if_true] del formule IF. To zagotavlja, da če se ime v celici D2 ujema z imenom v obsegu A2: A20, formula IF vrne vse usposabljanje, ki ga je opravila ta oseba.

In kjerkoli matrika vrne vrednost FALSE, smo vrednost [value_if_false] podali kot »« (prazno), zato vrne prazno.

IF del formule vrne matriko, kot je prikazano spodaj:

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

Kjer je ime usposabljanja, ki ga je opravil Bob, in prazna polja, kjer ni ime Bob.

Zdaj moramo le združiti to ime usposabljanja (ločeno z vejico) in ga vrniti v eno celico.

To lahko preprosto storite z novo formulo TEXTJOIN (na voljo v Excelu 2021-2022 in Excelu v Microsoft 365)

Formula TEXTJOIN ima tri argumente:

• ločilo - v našem primeru je »,« saj želim, da je vadba ločena z vejico in presledkom
• TRUE - ki formuli TEXTJOIN pove, naj prezre prazne celice in združi samo tiste, ki niso prazne
• Formula If, ki vrne besedilo, ki ga je treba združiti

Če uporabljate Excel v Microsoft 365, ki že ima dinamične matrike, lahko preprosto vnesete zgornjo formulo in pritisnete enter. Če uporabljate Excel2021-2022, morate vnesti formulo, pridržite tipko Control in Shift ter pritisnite Enter

Kliknite tukaj, če želite prenesti primer datoteke in nadaljevati

Pridobite več iskalnih vrednosti v eni celici (brez ponavljanja)

Ker je formula UNIQUE na voljo samo v Excelu v Microsoft 365, te metode ne boste mogli uporabiti v Excelu 2021-2022

V primeru, da se v vašem podatkovnem nizu ponavljajo, kot je prikazano spodaj, morate formulo nekoliko spremeniti, tako da dobite le seznam edinstvenih vrednosti v eni celici.

V zgornjem nizu podatkov so se nekateri ljudje večkrat udeležili usposabljanja. Bob in Stan sta se na primer dvakrat udeležila izobraževanja Excel, Betty pa dvakrat MS Word. Toda v našem rezultatu ne želimo, da se ime vadbe ponovi.

Za to lahko uporabite spodnjo formulo:

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

Zgornja formula deluje na enak način, z manjšo spremembo. formulo IF smo uporabili v funkciji UNIQUE, tako da bi jo funkcija UNIQUE v primeru ponovitve v rezultatu formule if odstranila.

Kliknite tukaj, če želite prenesti primer datoteke

Iskanje in vračanje več vrednosti v eni celici (z uporabo VBA)

Če uporabljate Excel 2016 ali starejše različice, potem ne boste imeli dostopa do formule TEXTJOIN. Zato je najboljši način, da nato poiščete in dobite več ujemajočih se vrednosti v eni celici, s pomočjo formule po meri, ki jo lahko ustvarite z uporabo VBA.

Če želimo v eno celico dobiti več iskalnih vrednosti, moramo ustvariti funkcijo v VBA (podobno kot funkcija VLOOKUP), ki preveri vsako celico v stolpcu in če je iskalna vrednost najdena, jo doda k rezultatu.

Tukaj je koda VBA, ki to lahko stori:

'Koda s strani Sumit Bansal (https://trumpexcel.com) Funkcija SingleCellExtract (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i Kot 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) Končana funkcija

Kam postaviti to kodo?

1. Odprite delovni zvezek in kliknite Alt + F11 (s tem se odpre okno urejevalnika VBA).
2. V tem oknu urejevalnika VBA na levi strani je raziskovalec projektov (kjer so navedeni vsi delovni zvezki in delovni listi). Z desno miškino tipko kliknite kateri koli predmet v delovnem zvezku, kjer želite, da deluje ta koda, in pojdite na Vstavi -> Modul.
3. V oknu modula (ki se prikaže na desni) kopirajte in prilepite zgornjo kodo.
4. Zdaj ste pripravljeni. Pojdite v katero koli celico v delovnem zvezku in vnesite = SingleCellExtract in vključite zahtevane vhodne argumente (npr. LookupValue, LookupRange, ColumnNumber).

Kako deluje ta formula?

Ta funkcija deluje podobno kot funkcija VLOOKUP.

Za vnos potrebujete 3 argumente:

1. Iskanje vrednosti - Niz, ki ga moramo poiskati v vrsti celic.
2. LookupRange - Niz celic, od koder moramo pridobiti podatke (v tem primeru $ B3: $ C18).
3. Številka stolpca - To je številka stolpca tabele/matrike, iz katere je treba vrniti ujemajočo se vrednost (v tem primeru 2).

Ko uporabite to formulo, preveri vsako celico v skrajnem levem stolpcu v obsegu iskanja in ko najde ujemanje, k rezultatu doda celico, v kateri ste uporabili formulo.

Zapomni si: Če želite znova uporabiti to formulo, shranite delovni zvezek kot delovni zvezek, ki podpira makro (.xlsm ali .xls). Tudi ta funkcija bi bila na voljo samo v tem delovnem zvezku in ne v vseh delovnih zvezkih.

Kliknite tukaj, če želite prenesti primer datoteke

Naučite se avtomatizirati dolgočasna ponavljajoča se opravila z VBA v Excelu. Pridružite se Tečaj Excel VBA

Pridobite več iskalnih vrednosti v eni celici (brez ponavljanja)

Obstaja možnost, da se bodo podatki ponovili.

Če uporabite zgoraj uporabljeno kodo, se bo tudi v rezultatu ponovil.

Če želite dobiti rezultat, kjer ni ponovitev, morate kodo nekoliko spremeniti.

Tukaj je koda VBA, ki vam bo dala več iskalnih vrednosti v eni celici brez ponovitev.

'Koda s strani Sumit Bansal (https://trumpexcel.com) Funkcija MultipleLookupNoRept (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i Kot Long Dim Result As String For i = 1 To LookupRange.Columns (1). .Count If LookupRange.Cells (i, 1) = Lookupvalue Potem za J = 1 Za i - 1 Če LookupRange.Cells (J, 1) = Lookupvalue Potem Če LookupRange.Cells (J, ColumnNumber) = LookupRange.Cells (i, ColumnNumber) Potem pojdite na Preskoči konec, če je konec, če je naslednji J Result = Result & "" & LookupRange.Cells (i, ColumnNumber) & "," Skip: End If Next i MultipleLookupNoRept = Left (Result, Len (Result) - 1) End Funkcija

Ko to kodo postavite v urejevalnik VB (kot je prikazano zgoraj v vadnici), boste lahko uporabljali MultipleLookupNoRept funkcijo.

Tukaj je posnetek rezultata, ki ga boste dobili s tem MultipleLookupNoRept funkcijo.

Kliknite tukaj, če želite prenesti primer datoteke

V tej vadnici sem obravnaval, kako uporabiti formule in VBA v Excelu za iskanje in vrnitev več iskalnih vrednosti v eni celici v Excelu.

Čeprav to preprosto storite s preprosto formulo, če uporabljate Excel v naročnini na Microsoft 365, če uporabljate prejšnje različice in nimate dostopa do funkcij, kot je TEXTJOIN, lahko to še vedno storite z uporabo VBA tako, da ustvarite svoj lastna funkcija po meri.