Kas me saame Exceli ühes lahtris otsida ja tagastada mitu väärtust (eraldatud koma või tühikuga)?
Seda küsimust on paljud kolleegid ja lugejad minult mitu korda küsinud.
Excelil on hämmastavaid otsinguvalemeid, näiteks VLOOKUP, INDEX/MATCH (ja nüüd XLOOKUP), kuid ükski neist ei paku võimalust mitme sobiva väärtuse tagastamiseks. Kõik need toimingud tuvastavad esimese vaste ja tagastavad selle.
Nii et tegin natuke VBA kodeerimist, et leida Excelis kohandatud funktsioon (mida nimetatakse ka kasutaja määratud funktsiooniks).
Värskenda: Pärast seda, kui Excel avaldas dünaamilised massiivid ja suurepärased funktsioonid, nagu UNIQUE ja TEXTJOIN, on nüüd võimalik kasutada lihtsat valemit ja tagastab kõik vastavad väärtused ühes lahtris (selles õpetuses käsitletud).Selles õpetuses näitan teile, kuidas seda teha (kui kasutate Exceli uusimat versiooni - Microsoft 365 koos kõigi uute funktsioonidega), samuti seda, kuidas seda teha, kui kasutate vanemaid versioone ( kasutades VBA -d).
Nii et alustame!
Otsige ja tagastage mitu väärtust ühes lahtris (kasutades valemit)
Kui kasutate Excel 2016 või varasemaid versioone, minge järgmise jao juurde, kus näitan, kuidas seda VBA abil teha.
Microsoft 365 tellimusega on teie Excelil nüüd palju võimsamaid funktsioone ja funktsioone, mida varasemates versioonides pole (nt XLOOKUP, dünaamilised massiivid, ainulaadsed/filtreerivad funktsioonid jne).
Nii et kui kasutate Microsoft 365 (varem tuntud kui Office 365), saate selles jaotises käsitletud meetodeid kasutades otsida ja tagastada mitu väärtust ühes Exceli lahtris.
Ja nagu näete, on see tõesti lihtne valem.
Allpool on mul andmekogum, kus mul on veerus A olevate inimeste nimed ja veerus B läbitud koolitused.
Näidisfaili allalaadimiseks ja selle järgimiseks klõpsake siin
Iga inimese kohta tahan teada saada, millise koolituse nad on läbinud. Veerus D on mul unikaalsete nimede loend (veerust A) ja soovin kiiresti otsida ja välja võtta kõik koolitused, mida iga inimene on teinud, ja saada need ühte komplekti (eraldatud komaga).
Allpool on valem, mis seda teeb:
= TEXTJOIN (",", TRUE, IF (D2 = $ A $ 2: $ A $ 20, $ B $ 2: $ B $ 20, ""))
Pärast valemi sisestamist lahtrisse E2 kopeerige see kõikide lahtrite jaoks, kus soovite tulemusi.
Kuidas see valem töötab?
Las ma dekonstrueerin selle valemi ja selgitan iga osa, kuidas see kokku annab tulemuse.
IF valemi loogiline test (D2 = $ A $ 2: $ A $ 20) kontrollib, kas nime lahter D2 on sama mis vahemikus A2: A20.
See läbib kõik lahtrid vahemikus A2: A20 ja kontrollib, kas nimi on lahtris D2 sama või mitte. kui see on sama nimi, tagastab see tõe, muidu tagastab vale.
Nii et see valemi osa annab teile massiivi, nagu allpool näidatud:
{TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}
Kuna me tahame saada koolitust ainult Bobile (väärtus lahtris D2), peame saama kõik vastavad koolitused rakkudele, mis tagastavad ülaltoodud massiivi tõe.
Seda on lihtne teha, määrates IF -valemi osa [value_if_true] vahemikuks, millel on koolitus. See tagab, et kui lahtris D2 olev nimi ühtib nimega vahemikus A2: A20, tagastab IF -valem kogu selle inimese koolituse.
Ja kõikjal, kus massiiv tagastab vale, oleme määranud [value_if_false] väärtuseks „” (tühi), seega tagastab see tühiku.
Valemi IF osa tagastab massiivi, nagu allpool näidatud:
{"Excel"; ""; ""; "PowerPoint"; ""; "" "" "" "" "" ""; "" "" "" ""; ""; "" ";" "}
Seal, kus on Bobi koolituse nimed ja tühjad kohad, kus nimi polnud Bob.
Nüüd peame vaid ühendama need koolituse nimed (eraldatud komaga) ja tagastama need ühes lahtris.
Ja seda saab hõlpsasti teha uue TEXTJOIN valemi abil (saadaval Excel2021-2022 ja Excelis Microsoft 365)
TEXTJOIN valem sisaldab kolme argumenti.
- eraldaja - mis on meie näites „“, kuna soovin, et koolitus oleks eraldatud koma ja tühikuga
- TRUE - mis käsib TEXTJOIN valemil tühje lahtreid ignoreerida ja kombineerida ainult need, mis pole tühjad
- Valem If, mis tagastab kombineeritava teksti
Kui kasutate Microsoft 365 -s Excelit, millel on juba dünaamilised massiivid, võite lihtsalt sisestada ülaltoodud valemi ja vajutada sisestusklahvi. Ja kui kasutate Excel2021-2022, peate sisestama valemi, hoidma all klahve Control ja Shift ning seejärel vajutama sisestusklahvi
Näidisfaili allalaadimiseks ja selle järgimiseks klõpsake siin
Hankige mitu otsinguväärtust ühes lahtris (ilma kordusteta)
Kuna UNIKAALNE valem on saadaval ainult Excelis Microsoft 365-s, ei saa te seda meetodit rakenduses Excel2021-2022 kasutadaKui teie andmekogumis on kordusi, nagu allpool näidatud, peate valemit veidi muutma, nii et ainulaadsete väärtuste loend kuvatakse ainult ühes lahtris.
Ülaltoodud andmekogumis on mõned inimesed mitu korda koolitust läbinud. Näiteks Bob ja Stan on käinud kaks korda Exceli koolitusel ja Betty kaks korda MS Wordi koolitusel. Kuid meie tulemusena ei taha me koolituse nime kordamist.
Selleks võite kasutada järgmist valemit:
= TEXTJOIN (",", TRUE, UNIQUE (IF (D2 = $ A $ 2: $ A $ 20, $ B $ 2: $ B $ 20, "")))
Ülaltoodud valem töötab samamoodi, väikese muudatusega. oleme funktsiooni UNIQUE raames kasutanud IF -valemit, nii et juhul, kui if -valemi tulemuses on kordusi, eemaldab funktsioon UNIQUE selle.
Näidisfaili allalaadimiseks klõpsake siin
Otsige ja tagastage mitu väärtust ühes lahtris (kasutades VBA -d)
Kui kasutate Excel 2016 või varasemaid versioone, pole teil juurdepääsu TEXTJOIN valemile. Seega on parim viis otsida ja hankida ühest lahtrist mitu sobivat väärtust, kasutades kohandatud valemit, mille saate luua VBA abil.Mitme otsinguväärtuse saamiseks ühes lahtris peame looma VBA -s funktsiooni (sarnane funktsiooniga VLOOKUP), mis kontrollib veeru iga lahtrit ja kui otsinguväärtus leitakse, lisab selle tulemusele.
Siin on VBA -kood, millega seda teha saab:
'Sumit Bansali kood (https://trumpexcel.com) Funktsioon SingleCellExtract (otsinguväärtus stringina, otsingupiirkonna vahemik, veeruarv täisarvuna) Dim i kui pikk hämar tulemus stringina i = 1 kuni otsingupiirkonna.veerud (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) Lõppfunktsioon
Kuhu see kood panna?
- Avage töövihik ja klõpsake Alt + F11 (see avab VBA redaktori akna).
- Selles VBA redaktori aknas vasakul on projektiuurija (kus on loetletud kõik töövihikud ja töölehed). Paremklõpsake töövihiku mis tahes objektil, kus soovite seda koodi töötada, ja minge Sisesta -> Moodul.
- Kopeerige ja kleepige ülaltoodud kood mooduli aknas (mis kuvatakse paremal).
- Nüüd olete kõik valmis. Minge töövihiku mis tahes lahtrisse ja tippige = SingleCellExtract ja sisestage vajalikud sisendargumendid (st LookupValue, LookupRange, ColumnNumber).
Kuidas see valem töötab?
See funktsioon töötab sarnaselt funktsiooniga VLOOKUP.
Sisendiks on 3 argumenti:
1. Otsinguväärtus - String, mille peame otsima erinevatest lahtritest.
2. Otsinguvahemik - Lahtrite massiiv, kust peame andmed tooma ($ B3: sel juhul $ C18).
3. Veerunumber - See on tabeli/massiivi veerunumber, millest tuleb tagastada vastav väärtus (antud juhul 2).
Kui kasutate seda valemit, kontrollib see otsinguvahemiku kõige vasakpoolsema veeru iga lahtrit ja kui see leiab vaste, lisab see tulemusele lahtris, milles te valemit kasutasite.
Pidage meeles: Selle valemi uuesti kasutamiseks salvestage töövihik makrofunktsiooniga töövihikuna (.xlsm või .xls). Samuti oleks see funktsioon saadaval ainult selles töövihikus ja mitte kõigis töövihikutes.
Näidisfaili allalaadimiseks klõpsake siin
Siit saate teada, kuidas automatiseerida igavaid korduvaid ülesandeid Excelis VBA abil. Ühine Exceli VBA kursusHankige mitu otsinguväärtust ühes lahtris (ilma kordusteta)
Võimalik, et andmetes võib esineda kordusi.
Kui kasutate ülaltoodud koodi, annab see ka tulemuses kordusi.
Kui soovite tulemust saada seal, kus kordusi pole, peate koodi veidi muutma.
Siin on VBA -kood, mis annab teile mitu otsinguväärtust ühes lahtris ilma kordusteta.
'Sumit Bansali (https://trumpexcel.com) kood .Count If LookupRange.Cells (i, 1) = Otsinguväärtus Siis J = 1 To i - 1 If LookupRange.Cells (J, 1) = Lookupvalue Then If LookupRange.Cells (J, ColumnNumber) = LookupRange.Cells (i, Veerunumber) Seejärel minge vahele Jäta vahele, kui lõpp, kui järgmine J Tulemus = Tulemus & "" & LookupRange.Cells (i, ColumnNumber) & "," Skip: End If Next i MultipleLookupNoRept = Left (Result, Len (Result) - 1) End Funktsioon
Kui olete selle koodi VB redaktorisse paigutanud (nagu õpetuses ülal näidatud), saate kasutada MultipleLookupNoRept funktsiooni.
Siin on ülevaade tulemusest, mille saate sellega MultipleLookupNoRept funktsiooni.
Näidisfaili allalaadimiseks klõpsake siin
Selles õpetuses käsitlesin, kuidas Exceli valemite ja VBA abil leida ja otsida Exceli ühes lahtris mitu otsinguväärtust.
Kuigi seda saab hõlpsasti teha lihtsa valemiga, kui kasutate Excelit Microsoft 365 tellimuses, kui kasutate varasemaid versioone ja teil pole juurdepääsu sellistele funktsioonidele nagu TEXTJOIN, saate seda siiski teha VBA abil, luues oma oma kohandatud funktsioon.
