Gaukite kelias paieškos vertes vienoje ląstelėje (su kartojimu ir be jo)

Ar galime ieškoti ir grąžinti kelias reikšmes viename „Excel“ langelyje (atskirtas kableliu ar tarpais)?

Šį klausimą man daug kartų uždavė daugelis kolegų ir skaitytojų.

„Excel“ turi keletą nuostabių paieškos formulių, tokių kaip VLOOKUP, INDEX/MATCH (ir dabar XLOOKUP), tačiau nė viena iš jų nesuteikia galimybės grąžinti kelias atitinkančias reikšmes. Visa tai padeda nustatyti pirmąją atitiktį ir ją grąžinti.

Taigi aš šiek tiek kodavau VBA, kad „Excel“ sukurtų pasirinktinę funkciją (dar vadinamą vartotojo apibrėžta funkcija).

Atnaujinti: „Excel“ išleidus dinaminius masyvus ir nuostabias funkcijas, tokias kaip UNIQUE ir TEXTJOIN, dabar galima naudoti paprastą formulę ir grąžinkite visas atitinkamas vertes viename langelyje (aprašyta šioje pamokoje).

Šioje pamokoje parodysiu, kaip tai padaryti (jei naudojate naujausią „Excel“ versiją - „Microsoft 365“ su visomis naujomis funkcijomis), taip pat kaip tai padaryti, jei naudojate senesnes versijas ( naudojant VBA).

Taigi pradėkime!

Ieškoti ir grąžinti kelias reikšmes vienoje langelyje (naudojant formulę)

Jei naudojate „Excel 2016“ ar ankstesnes versijas, eikite į kitą skyrių, kuriame parodysiu, kaip tai padaryti naudojant VBA.

Naudodamiesi „Microsoft 365“ prenumerata, jūsų „Excel“ dabar turi daug galingesnių funkcijų ir funkcijų, kurių nėra ankstesnėse versijose (pvz., „XLOOKUP“, „Dynamic Arrays“, „UNIQUE/FILTER“ funkcijos ir kt.)

Taigi, jei naudojate „Microsoft 365“ (anksčiau žinomą kaip „Office 365“), galite naudoti šiame skyriuje aprašytus metodus, kad galėtumėte ieškoti ir grąžinti kelias reikšmes viename „Excel“ langelyje.

Ir kaip pamatysite, tai tikrai paprasta formulė.

Žemiau turiu duomenų rinkinį, kuriame A stulpelyje nurodomi žmonių vardai ir mokymai, kuriuos jie atliko B skiltyje.

Spustelėkite čia, norėdami atsisiųsti pavyzdinį failą ir sekti toliau

Kiekvienam asmeniui noriu sužinoti, kokius mokymus jie baigė. D stulpelyje turiu unikalių pavadinimų sąrašą (iš A stulpelio) ir noriu greitai surasti ir ištraukti visus mokymus, kuriuos atliko kiekvienas žmogus, ir gauti juos viename rinkinyje (atskirtas kableliu).

Žemiau yra formulė, kuri tai padarys:

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

Įvedę formulę į langelį E2, nukopijuokite ją į visas ląsteles, kuriose norite gauti rezultatų.

Kaip veikia ši formulė?

Leiskite man dekonstruoti šią formulę ir paaiškinti kiekvieną dalį, kaip ji susideda, duoda mums rezultatą.

Loginis testas IF formulėje (D2 = $ A $ 2: $ A $ 20) patikrina, ar pavadinimo langelis D2 yra toks pat kaip diapazone A2: A20.

Jis eina per kiekvieną langelį diapazone A2: A20 ir tikrina, ar pavadinimas tas pats D2 langelyje, ar ne. jei tai tas pats pavadinimas, jis grąžina TRUE, kitu atveju grąžina FALSE.

Taigi ši formulės dalis suteiks jums masyvą, kaip parodyta žemiau:

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

Kadangi norime gauti tik Bobo mokymą (D2 langelio reikšmė), turime gauti visus atitinkamus mokymus ląstelėms, kurios grąžina TRUE aukščiau esančiame masyve.

Tai lengva padaryti, nurodant [value_if_true] IF formulės dalį kaip diapazoną, kuris turi mokymą. Tai užtikrina, kad jei pavadinimas langelyje D2 sutampa su pavadinimu diapazone A2: A20, IF formulė grąžins visą tą asmenį.

Visur, kur masyvas grąžina FALSE, mes [value_if_false] reikšmę nurodėme kaip „“ (tuščią), todėl jis pateikia tuščią.

IF formulės dalis grąžina masyvą, kaip parodyta žemiau:

{"Excel"; ""; ""; "PowerPoint"; ""; "" ";" ";" ";" ";" ";" " ; ""; "" ";" "}

Ten, kur yra Bobo mokymų pavadinimai ir tuščios vietos visur, kur vardas nebuvo Bobas.

Dabar viskas, ką mums reikia padaryti, yra sujungti šį mokymo pavadinimą (atskirtą kableliu) ir grąžinti jį vienoje langelyje.

Tai galima lengvai padaryti naudojant naują TEXTJOIN formulę (pasiekiama „Excel2021-2022“ ir „Excel“ „Microsoft 365“)

TEXTJOIN formulė apima tris argumentus:

• ribotuvas - mūsų pavyzdyje yra „“, nes noriu, kad mokymas būtų atskirtas kableliu ir tarpo simboliu
• TRUE - liepia TEXTJOIN formulei nepaisyti tuščių langelių ir derinti tik tuščias
• Jei formulė grąžina tekstą, kurį reikia sujungti

Jei „Microsoft 365“ naudojate „Excel“, kurioje jau yra dinaminių masyvų, galite tiesiog įvesti aukščiau pateiktą formulę ir paspausti „Enter“. O jei naudojate „Excel2021-2022“, turite įvesti formulę, palaikykite nuspaudę „Control“ ir „Shift“ klavišus, tada paspauskite „Enter“

Spustelėkite čia, norėdami atsisiųsti pavyzdinį failą ir sekti toliau

Gaukite kelias paieškos vertes vienoje ląstelėje (be pasikartojimo)

Kadangi UNIKALI formulė pasiekiama tik naudojant „Microsoft 365“ „Excel“, šio metodo negalėsite naudoti programoje „Excel2021-2022“

Jei duomenų rinkinyje yra pasikartojimų, kaip parodyta žemiau, turite šiek tiek pakeisti formulę, kad unikalių verčių sąrašas būtų rodomas tik viename lange.

Aukščiau pateiktame duomenų rinkinyje kai kurie žmonės mokėsi kelis kartus. Pavyzdžiui, Bobas ir Stanas du kartus lankė „Excel“ mokymus, o Betty - „MS Word“. Tačiau dėl to mes nenorime, kad treniruotės pavadinimas būtų kartojamas.

Norėdami tai padaryti, galite naudoti šią formulę:

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

Aukščiau pateikta formulė veikia taip pat, su nedideliais pakeitimais. mes panaudojome IF formulę UNIQUE funkcijoje, kad jei formulės if rezultatas pasikartotų, funkcija UNIQUE ją pašalintų.

Spustelėkite čia, jei norite atsisiųsti pavyzdinį failą

Kelių verčių paieška ir grąžinimas viename lange (naudojant VBA)

Jei naudojate „Excel 2016“ ar ankstesnes versijas, neturėsite prieigos prie „TEXTJOIN“ formulės. Taigi geriausias būdas ieškoti ir gauti kelias atitinkančias vertes vienoje ląstelėje yra naudojant pasirinktinę formulę, kurią galite sukurti naudodami VBA.

Norėdami gauti kelias peržiūros vertes viename lange, turime sukurti funkciją VBA (panašią į funkciją VLOOKUP), kuri tikrina kiekvieną stulpelio langelį ir, jei paieškos vertė randama, prideda ją prie rezultato.

Čia yra VBA kodas, kuris gali tai padaryti:

„Sumito Bansalo kodas (https://trumpexcel.com) Funkcija„ SingleCellExtract “(„ ​​Lookupvalue “kaip eilutė,„ LookupRange “kaip diapazonas,„ ColumnNumber “kaip sveikasis skaičius)„ Dim i As Long Dim Result As String For i = 1 To LookupRange.Column “(1). .Skaičiuokite, jei LookupRange.Cells (i, 1) = Lookupvalue then Result = Result & "" & LookupRange.Cells (i, ColumnNumber) & "," End If Next i SingleCellExtract = Left (Result, Len (Result) - 1) Pabaigos funkcija

Kur dėti šį kodą?

1. Atidarykite darbaknygę ir spustelėkite Alt + F11 (taip atidaromas VBA redaktoriaus langas).
2. Šiame VBA redaktoriaus lange kairėje yra projektų tyrinėtojas (kuriame išvardytos visos darbaknygės ir darbalapiai). Dešiniuoju pelės mygtuku spustelėkite bet kurį darbaknygės objektą, kuriame norite, kad šis kodas veiktų, ir eikite į Įterpti -> Modulis.
3. Modulio lange (kuris bus rodomas dešinėje) nukopijuokite ir įklijuokite aukščiau esantį kodą.
4. Dabar esate pasiruošę. Eikite į bet kurią darbo knygos langelį ir įveskite = SingleCellExtract ir įjunkite reikiamus įvesties argumentus (t. y. „LookupValue“, „LookupRange“, „ColumnNumber“).

Kaip veikia ši formulė?

Ši funkcija veikia panašiai kaip funkcija VLOOKUP.

Įvestyje naudojami 3 argumentai:

1. Paieškos vertė - Eilutė, kurią turime ieškoti langelių diapazone.
2. „LookupRange“ - Ląstelių masyvas, iš kurio turime gauti duomenis (šiuo atveju $ B3: $ C18).
3. Stulpelio numeris - Tai lentelės/masyvo stulpelio numeris, iš kurio turi būti grąžinta atitinkanti vertė (šiuo atveju - 2).

Kai naudojate šią formulę, ji tikrina kiekvieną langelį kairiajame stulpelyje paieškos diapazone ir radęs atitikimą, jis prideda rezultatą langelyje, kuriame naudojote formulę.

Prisiminti: Išsaugokite darbaknygę kaip makrokomandų darbaknygę (.xlsm arba .xls), kad galėtumėte dar kartą naudoti šią formulę. Be to, ši funkcija būtų prieinama tik šioje darbaknygėje, o ne visose darbaknygėse.

Spustelėkite čia, jei norite atsisiųsti pavyzdinį failą

Sužinokite, kaip automatizuoti nuobodžias pasikartojančias užduotis naudojant „VBA“ programoje „Excel“. Prisijungti prie Excel VBA kursai

Gaukite kelias paieškos vertes vienoje ląstelėje (be pasikartojimo)

Yra tikimybė, kad duomenyse gali būti pasikartojimų.

Jei naudosite aukščiau naudojamą kodą, jis taip pat pakartos rezultatą.

Jei norite gauti rezultatą ten, kur nėra pasikartojimų, turite šiek tiek pakeisti kodą.

Čia yra VBA kodas, kuris suteiks jums kelias paieškos reikšmes vienoje ląstelėje be pakartojimų.

„Sumito Bansalo kodas (https://trumpexcel.com) Funkcija„ MultipleLookupNoRept “(„ ​​Lookupvalue “kaip eilutė,„ LookupRange “kaip diapazonas,„ ColumnNumber “kaip sveikasis skaičius)„ Dim i As Long Dim Result As String For i = 1 “į„ LookupRange.Column “(1). .Skaičiuokite, jei „LookupRange.Cells“ (i, 1) = „Lookupvalue“, tada „J“ = 1 „i“ - 1 „If LookupRange.Cells“ (J, 1) = „„ Lookupvalue ““ tada, jei „LookupRange.Cells“ („J“, „ColumnNumber“) = „LookupRange.Cells“ (i, ColumnNumber) Tada GoTo Praleisti pabaigą, jei pabaiga Jei kitas J Result = Result & "" & LookupRange.Cells (i, ColumnNumber) & "," Skip: End If Next i MultipleLookupNoRept = Left (Result, Len (Result) - 1) End Funkcija

Įdėję šį kodą į VB redaktorių (kaip parodyta aukščiau pamokoje), galėsite naudoti „MultipleLookupNoRept“ funkcija.

Čia yra rezultato, kurį gausite naudodami šį vaizdą, momentinė nuotrauka „MultipleLookupNoRept“ funkcija.

Spustelėkite čia, jei norite atsisiųsti pavyzdinį failą

Šioje pamokoje aptariau, kaip naudoti „Excel“ formules ir VBA, norint rasti ir grąžinti kelias peržiūros vertes vienoje „Excel“ langelyje.

Nors tai galite lengvai padaryti naudodami paprastą formulę, jei naudojate „Excel“ „Microsoft 365“ prenumeratoje, jei naudojate ankstesnes versijas ir neturite prieigos prie tokių funkcijų kaip TEXTJOIN, vis tiek galite tai padaryti naudodami VBA, sukurdami savo pasirinktinę funkciją.