Hanki useita hakuarvoja yhdessä solussa (toistolla ja ilman toistoa)

Voimmeko etsiä ja palauttaa useita arvoja yhdessä Excel-solussa (pilkulla tai välilyönnillä erotettuna)?

Monet kollegani ja lukijani ovat esittäneet tämän kysymyksen useita kertoja.

Excelissä on hämmästyttäviä hakukaavoja, kuten VLOOKUP, INDEX/MATCH (ja nyt XLOOKUP), mutta mikään näistä ei tarjoa tapaa palauttaa useita vastaavia arvoja. Kaikki nämä toimivat tunnistamalla ensimmäinen ottelu ja palauttamalla se.

Joten tein vähän VBA -koodausta keksimään mukautetun toiminnon (jota kutsutaan myös käyttäjän määrittämäksi funktioksi) Excelissä.

Päivittää: Kun Excel on julkaissut dynaamisia matriiseja ja mahtavia toimintoja, kuten UNIQUE ja TEXTJOIN, on nyt mahdollista käyttää yksinkertaista kaavaa ja palauttaa kaikki vastaavat arvot yhteen soluun (katettu tässä opetusohjelmassa).

Tässä opetusohjelmassa näytän sinulle, kuinka tämä tehdään (jos käytät Excelin uusinta versiota - Microsoft 365 ja kaikki uudet toiminnot), sekä tapa tehdä tämä, jos käytät vanhempia versioita ( VBA: n avulla).

Aloitetaan siis!

Etsi ja palauta useita arvoja yhdessä solussa (kaavan avulla)

Jos käytät Excel 2016: ta tai aiempia versioita, siirry seuraavaan osioon, jossa näytän, miten tämä tehdään VBA: n avulla.

Microsoft 365 -tilauksella Excelissäsi on nyt paljon tehokkaampia toimintoja ja ominaisuuksia, joita ei ole aiemmissa versioissa (kuten XLOOKUP, Dynamic Arrays, UNIQUE/FILTER -toiminnot jne.)

Joten jos käytät Microsoft 365: tä (aiemmin tunnettu nimellä Office 365), voit käyttää tässä osassa kuvattuja menetelmiä ja etsiä ja palauttaa useita arvoja yhdessä solussa Excelissä.

Ja kuten näet, se on todella yksinkertainen kaava.

Alla on tietojoukko, jossa sarakkeessa A on ihmisten nimet ja sarakkeessa B suoritetut koulutukset.

Lataa esimerkkitiedosto napsauttamalla tätä ja seuraa sitä

Haluan jokaiselle henkilölle selvittää, minkä koulutuksen hän on suorittanut. Sarakkeessa D on luettelo yksilöllisistä nimistä (sarakkeesta A), ja haluan nopeasti etsiä ja poimia kaiken koulutuksen, jonka jokainen henkilö on tehnyt, ja saada nämä yhdeksi sarjaksi (pilkulla erotettuna).

Alla on kaava, jolla tämä tehdään:

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

Kun olet syöttänyt kaavan soluun E2, kopioi se kaikille soluille, joista haluat tulokset.

Kuinka tämä kaava toimii?

Haluan purkaa tämän kaavan ja selittää jokainen osa siitä, miten se tulee yhteen, antaa meille tuloksen.

IF -kaavan looginen testi (D2 = $ A $ 2: $ A $ 20) tarkistaa, onko nimisolu D2 sama kuin alueen A2: A20.

Se kulkee alueen A2: A20 jokaisen solun läpi ja tarkistaa, onko nimi sama solussa D2 vai ei. jos se on sama nimi, se palauttaa TOSI, muuten palauttaa EPÄTOSI.

Joten tämä kaavan osa antaa sinulle taulukon alla esitetyllä tavalla:

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

Koska haluamme vain saada koulutuksen Bobille (arvo solussa D2), meidän on saatava kaikki vastaava koulutus soluille, jotka palauttavat TOSI yllä olevassa taulukossa.

Tämä on helppo tehdä määrittämällä [value_if_true] osa IF -kaavaa alueeksi, jolla on koulutus. Tämä varmistaa, että jos solun D2 nimi vastaa alueen A2: A20 nimeä, IF -kaava palauttaa kaiken kyseisen henkilön suorittaman koulutuksen.

Ja missä tahansa taulukko palauttaa FALSE -arvon, olemme määrittäneet [value_if_false] -arvon arvoksi "" (tyhjä), joten se palauttaa tyhjän.

Kaavan IF -osa palauttaa taulukon alla esitetyllä tavalla:

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

Siellä on Bobin suorittaman koulutuksen nimet ja tyhjät kohdat, missä nimi ei ollut Bob.

Nyt meidän tarvitsee vain yhdistää nämä koulutuksen nimi (pilkulla erotettuna) ja palauttaa se yhteen soluun.

Ja tämä voidaan tehdä helposti käyttämällä uutta TEXTJOIN-kaavaa (saatavana Excel2021-2022 ja Excel Microsoft 365: ssä)

TEXTJOIN -kaava sisältää kolme argumenttia:

• erotin - joka on esimerkissämme "," koska haluan, että koulutus erotetaan pilkulla ja välilyönnillä
• TOSI - joka käskee TEXTJOIN -kaavan ohittamaan tyhjät solut ja yhdistämään vain ne, jotka eivät ole tyhjiä
• If -kaava, joka palauttaa yhdistettävän tekstin

Jos käytät Exceliä Microsoft 365: ssä, jossa on jo dynaamisia taulukkoja, voit kirjoittaa yllä olevan kaavan ja painaa enter. Ja jos käytät Excel2021-2022, sinun on syötettävä kaava, pidettävä Control- ja Shift-näppäimiä painettuna ja painettava sitten Enter-näppäintä

Lataa esimerkkitiedosto napsauttamalla tätä ja seuraa sitä

Hanki useita hakuarvoja yhdessä solussa (ilman toistoa)

Koska UNIQUE-kaava on käytettävissä vain Excelissä Microsoft 365: ssä, et voi käyttää tätä menetelmää Excel2021-2022: ssa

Jos tietojoukossasi on toistoja, kuten alla on esitetty, sinun on muutettava kaavaa hieman niin, että saat luettelon yksittäisistä arvoista vain yhdessä solussa.

Yllä olevassa tietojoukossa jotkut ihmiset ovat osallistuneet koulutukseen useita kertoja. Esimerkiksi Bob ja Stan ovat käyneet Excel -koulutuksen kahdesti ja Betty MS Word -koulutuksen kahdesti. Tuloksena emme kuitenkaan halua, että koulutuksen nimi toistetaan.

Voit tehdä tämän käyttämällä seuraavaa kaavaa:

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

Yllä oleva kaava toimii samalla tavalla pienellä muutoksella. olemme käyttäneet IF -kaavaa UNIQUE -funktiossa, jotta jos kaavan if tuloksessa on toistoja, UNIQUE -funktio poistaisi sen.

Lataa esimerkkitiedosto napsauttamalla tätä

Etsi ja palauta useita arvoja yhdessä solussa (VBA: n avulla)

Jos käytät Excel 2016: ta tai aiempia versioita, et voi käyttää TEXTJOIN -kaavaa. Joten paras tapa etsiä ja saada useita vastaavia arvoja yhdessä solussa on käyttää mukautettua kaavaa, jonka voit luoda VBA: n avulla.

Jotta saisimme useita hakuarvoja yhdestä solusta, meidän on luotava VBA: han funktio (samanlainen kuin VLOOKUP -toiminto), joka tarkistaa sarakkeen jokaisen solun ja jos hakuarvo löytyy, lisää sen tulokseen.

Tässä on VBA -koodi, joka voi tehdä tämän:

'Sumit Bansalin koodi (https://trumpexcel.com) Toiminto SingleCellExtract (Lookupvalue as String, LookupRange as Range, ColumnNumber as Integer) Dim i Long Dim Result as String For i = 1 To LookupRange.Columns (1). .Laskenta, jos LookupRange.Cells (i, 1) = Lookupvalue Then Result = Result & "" & LookupRange.Cells (i, ColumnNumber) & "," End If Next i SingleCellExtract = Left (Result, Len (Result) - 1) Lopeta toiminto

Minne tämä koodi laitetaan?

1. Avaa työkirja ja napsauta Alt + F11 (tämä avaa VBA Editor -ikkunan).
2. Tässä VBA Editor -ikkunan vasemmalla puolella on projektinhallinta (jossa kaikki työkirjat ja laskentataulukot on lueteltu). Napsauta hiiren kakkospainikkeella mitä tahansa työkirjan objektia, jossa haluat tämän koodin toimivan, ja siirry kohtaan Lisää -> Moduuli.
3. Kopioi ja liitä yllä oleva koodi moduuli -ikkunassa (joka näkyy oikealla).
4. Nyt olet valmis. Siirry mihin tahansa työkirjan soluun ja kirjoita = SingleCellExtract ja liitä vaaditut syöttöargumentit (eli LookupValue, LookupRange, ColumnNumber).

Kuinka tämä kaava toimii?

Tämä toiminto toimii samalla tavalla kuin VLOOKUP -toiminto.

Syötteinä tarvitaan 3 argumenttia:

1. Hakuarvo - Jono, joka meidän on etsittävä eri soluista.
2. LookupRange - Joukko soluja, joista meidän on haettava tiedot ($ B3: $ C18 tässä tapauksessa).
3. SarakeNumero - Se on taulukon/taulukon sarakkeen numero, josta vastaava arvo on palautettava (tässä tapauksessa 2).

Kun käytät tätä kaavaa, se tarkistaa kaikki hakualueen vasemmanpuoleisen sarakkeen solut ja kun se löytää osuman, se lisää tuloksen soluun, jossa olet käyttänyt kaavaa.

Muistaa: Tallenna työkirja makron mahdollistavana työkirjana (.xlsm tai .xls), jotta voit käyttää tätä kaavaa uudelleen. Tämä toiminto olisi myös käytettävissä vain tässä työkirjassa eikä kaikissa työkirjoissa.

Lataa esimerkkitiedosto napsauttamalla tätä

Opi automatisoimaan tylsät toistuvat tehtävät VBA: n avulla Excelissä. Liity Excel VBA -kurssi

Hanki useita hakuarvoja yhdessä solussa (ilman toistoa)

On mahdollista, että tiedoissa on toistoja.

Jos käytät yllä käytettyä koodia, se antaa sinulle myös toistoja tuloksessa.

Jos haluat saada tuloksen, jossa ei ole toistoja, sinun on muokattava koodia hieman.

Tässä on VBA -koodi, joka antaa sinulle useita hakuarvoja yhdessä solussa ilman toistoja.

'Sumit Bansalin koodi (https://trumpexcel.com) Toiminto MultipleLookupNoRept (Lookupvalue as String, LookupRange as Range, ColumnNumber as Integer) Dim i Long Dim Resurs as String For i = 1 To LookupRange.Columns (1). .Laske Jos LookupRange.Cells (i, 1) = Hakutaulukko sitten J = 1 To i - 1 If LookupRange.Cells (J, 1) = Lookupvalue then If LookupRange.Cells (J, ColumnNumber) = LookupRange.Cells (i, 1) ColumnNumber) Sitten GoTo Skip End if End Jos Next J Result = Result & "" & LookupRange.Cells (i, ColumnNumber) & "," Skip: End If Next i MultipleLookupNoRept = Left (Result, Len (Result) - 1) End Toiminto

Kun olet asettanut tämän koodin VB Editoriin (kuten yllä opetusohjelmassa on esitetty), voit käyttää MultipleLookupNoRept toiminto.

Tässä on tilannekuva tuloksesta, jonka saat tämän kanssa MultipleLookupNoRept toiminto.

Lataa esimerkkitiedosto napsauttamalla tätä

Tässä opetusohjelmassa kävin läpi kaavojen ja VBA: n käyttämisen Excelissä useiden hakuarvojen löytämiseksi ja palauttamiseksi yhdessä Excel -solussa.

Vaikka se voidaan tehdä helposti yksinkertaisella kaavalla, jos käytät Exceliä Microsoft 365 -tilauksessa, jos käytät aiempia versioita etkä voi käyttää toimintoja, kuten TEXTJOIN, voit silti tehdä tämän käyttämällä VBA: ta luomalla oma mukautettu toiminto.