Získajte viac hodnôt vyhľadávania v jednej bunke (s opakovaním aj bez neho)

Môžeme vyhľadať a vrátiť viac hodnôt do jednej bunky v programe Excel (oddelených čiarkou alebo medzerou)?

Túto otázku mi už mnohokrát položili mnohí moji kolegovia a čitatelia.

Excel má niekoľko úžasných vyhľadávacích vzorcov, ako napríklad VLOOKUP, INDEX/MATCH (a teraz XLOOKUP), ale žiadny z nich neponúka spôsob, ako vrátiť viac zodpovedajúcich hodnôt. Všetky tieto funkcie fungujú tak, že identifikujete prvý zápas a vrátite ho.

Skúsil som teda kódovanie VBA, aby som v Exceli vyvinul vlastnú funkciu (nazývanú aj funkcia definovaná používateľom).

Aktualizácia: Potom, čo Excel vydal dynamické polia a úžasné funkcie, ako sú UNIQUE a TEXTJOIN, je teraz možné použiť jednoduchý vzorec a vráťte všetky zodpovedajúce hodnoty do jednej bunky (popísané v tomto návode).

V tomto tutoriále vám ukážem, ako to urobiť (ak používate najnovšiu verziu programu Excel - Microsoft 365 so všetkými novými funkciami), ako aj spôsob, ako to urobiť v prípade, že používate staršie verzie ( pomocou VBA).

Začnime teda!

Vyhľadajte a vráťte viac hodnôt do jednej bunky (pomocou vzorca)

Ak používate Excel 2016 alebo predchádzajúce verzie, prejdite na ďalšiu časť, kde vám ukážem, ako to urobiť pomocou VBA.

Vďaka predplatnému Microsoft 365 má váš Excel teraz oveľa výkonnejšie funkcie a funkcie, ktoré v predchádzajúcich verziách neboli (napríklad funkcie XLOOKUP, Dynamic Arrays, UNIQUE/FILTER atď.)

Ak teda používate Microsoft 365 (predtým známy ako Office 365), môžete použiť metódy popísané v tejto časti a vyhľadať a vrátiť viacero hodnôt do jednej bunky v Exceli.

A ako uvidíte, je to skutočne jednoduchý vzorec.

Nasleduje súbor údajov, kde sú uvedené mená ľudí v stĺpci A a školenia, ktoré absolvovali v stĺpci B.

Kliknutím sem stiahnete ukážkový súbor a budete pokračovať

Pri každom človeku chcem zistiť, aké školenie absolvoval. V stĺpci D mám zoznam jedinečných mien (zo stĺpca A) a chcem rýchlo vyhľadať a extrahovať všetky školenia, ktoré každý človek absolvoval, a získať ich v rámci jednej sady (oddelenej čiarkou).

Nasleduje vzorec, ktorý to urobí:

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

Po zadaní vzorca do bunky E2 ho skopírujte pre všetky bunky, v ktorých chcete dosiahnuť výsledky.

Ako tento vzorec funguje?

Dovoľte mi dekonštruovať tento vzorec a vysvetliť každú časť toho, ako sa spája, a dáva nám výsledok.

Logický test vo vzorci IF (D2 = $ A $ 2: $ A $ 20) kontroluje, či je bunka názvu D2 rovnaká ako v rozsahu A2: A20.

Prechádza každou bunkou v rozsahu A2: A20 a kontroluje, či je názov rovnaký v bunke D2 alebo nie. ak je to rovnaké meno, vráti hodnotu TRUE, v opačnom prípade vráti hodnotu FALSE.

Táto časť vzorca vám teda poskytne pole, ako je uvedené nižšie:

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

Pretože chceme získať iba školenie pre Boba (hodnota v bunke D2), musíme získať všetky zodpovedajúce školenia pre bunky, ktoré vo vyššie uvedenom poli vracajú hodnotu TRUE.

To sa dá ľahko dosiahnuť zadaním [value_if_true] časti vzorca IF ako rozsahu, v ktorom prebieha školenie. To zaisťuje, že ak sa názov v bunke D2 zhoduje s názvom v rozsahu A2: A20, vzorec IF vráti všetky školenia, ktoré táto osoba absolvovala.

A kdekoľvek pole vracia FALSE, zadali sme hodnotu [value_if_false] ako „“ (prázdne), takže vráti prázdne miesto.

IF časť vzorca vracia pole ako je uvedené nižšie:

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

Tam, kde sú uvedené názvy školení, absolvoval Bob a prázdne miesta, kde sa meno nenachádzalo Bob.

Teraz všetko, čo musíme urobiť, je skombinovať tieto názvy školení (oddelené čiarkou) a vrátiť ich do jednej bunky.

A to sa dá ľahko vykonať pomocou nového vzorca TEXTJOIN (k dispozícii v programe Excel2021-2022 a Excel v Microsoft 365)

Vzorec TEXTJOIN má tri argumenty:

• oddeľovač - čo je v našom prípade „“, pretože chcem, aby bol tréning oddelený čiarkou a medzerníkom
• TRUE - čo hovorí vzorcu TEXTJOIN, aby ignoroval prázdne bunky a kombinoval iba tie, ktoré nie sú prázdne
• Vzorec If, ktorý vracia text, ktorý je potrebné skombinovať

Ak používate Excel v Microsoft 365, ktorý už má dynamické polia, stačí zadať vyššie uvedený vzorec a stlačiť kláves Enter. A ak používate Excel2021-2022, musíte zadať vzorec, podržať kláves Control a Shift a potom stlačiť Enter

Kliknutím sem stiahnete ukážkový súbor a budete pokračovať

Získajte viac hodnôt vyhľadávania v jednej bunke (bez opakovania)

Pretože vzorec UNIQUE je k dispozícii iba pre Excel v Microsoft 365, túto metódu nebudete môcť použiť v programe Excel2021-2022

V prípade, že sa vo vašom súbore údajov opakujú, ako je uvedené nižšie, musíte vzorec trochu zmeniť, aby ste získali zoznam jedinečných hodnôt iba v jednej bunke.

V uvedenom súbore údajov niektorí ľudia absolvovali školenie viackrát. Bob a Stan napríklad dvakrát absolvovali školenie v Exceli a Betty dvakrát absolvovala školenie v programe MS Word. Ale v našom výsledku nechceme, aby sa názov výcviku opakoval.

Na tento účel môžete použiť nasledujúci vzorec:

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

Vyššie uvedený vzorec funguje rovnako, s malou zmenou. vo funkcii UNIQUE sme použili vzorec IF, aby v prípade, že by vo vzorci if došlo k opakovaniu, funkcia UNIQUE by ho odstránila.

Kliknutím sem stiahnete ukážkový súbor

Vyhľadajte a vráťte viac hodnôt do jednej bunky (pomocou VBA)

Ak používate Excel 2016 alebo predchádzajúce verzie, nebudete mať prístup k vzorcu TEXTJOIN. Najlepším spôsobom, ako potom vyhľadať a získať viacero zhodných hodnôt v jednej bunke, je použitie vlastného vzorca, ktorý môžete vytvoriť pomocou jazyka VBA.

Aby sme získali viac vyhľadávacích hodnôt v jednej bunke, musíme vo VBA vytvoriť funkciu (podobnú funkcii VLOOKUP), ktorá skontroluje každú bunku v stĺpci a ak sa vyhľadaná hodnota nájde, pridá ju k výsledku.

Tu je kód VBA, ktorý to dokáže:

'Code by Sumit Bansal (https://trumpexcel.com) Funkcia SingleCellExtract (Lookupvalue ako reťazec, LookupRange ako rozsah, ColumnNumber ako celé číslo) Dim i Ako dlhý Dim Výsledok ako reťazec pre i = 1 To LookupRange.Columns (1) .Cells .Count If LookupRange.Cells (i, 1) = Lookupvalue Then Result = Result & "" & LookupRange.Cells (i, ColumnNumber) & "," End If Next i SingleCellExtract = Left (Result, Len (Result) - 1) Koncová funkcia

Kam vložiť tento kód?

1. Otvorte zošit a kliknite na Alt + F11 (tým sa otvorí okno editora VBA).
2. V tomto okne editora VBA je vľavo prieskumník projektov (kde sú uvedené všetky zošity a pracovné listy). Kliknite pravým tlačidlom myši na ľubovoľný objekt v zošite, v ktorom má tento kód fungovať, a prejdite na položku Vložiť -> Modul.
3. V okne modulu (ktoré sa zobrazí vpravo) skopírujte a prilepte vyššie uvedený kód.
4. Teraz ste všetci pripravení. Prejdite na ľubovoľnú bunku v zošite a napíšte = SingleCellExtract a zapojte požadované vstupné argumenty (t.j. LookupValue, LookupRange, ColumnNumber).

Ako tento vzorec funguje?

Táto funkcia funguje podobne ako funkcia VLOOKUP.

Na vstup sú potrebné tri argumenty:

1. Hodnota vyhľadávania - Reťazec, ktorý musíme vyhľadať v rade buniek.
2. Rozsah vyhľadávania - Pole buniek, z ktorých potrebujeme načítať údaje (v tomto prípade $ B3: $ C18).
3. Číslo stĺpca - Je to číslo stĺpca tabuľky/poľa, z ktorého sa má vrátiť zodpovedajúca hodnota (v tomto prípade 2).

Keď použijete tento vzorec, skontroluje každú bunku v stĺpci úplne vľavo v rozsahu vyhľadávania a keď nájde zhodu, pridá sa k výsledku v bunke, v ktorej ste použili vzorec.

Pamätajte si: Uložte zošit ako zošit s povoleným makrom (.xlsm alebo .xls), aby ste tento vzorec znova použili. Tiež táto funkcia bude k dispozícii iba v tomto zošite a nie vo všetkých zošitoch.

Kliknutím sem stiahnete ukážkový súbor

Naučte sa automatizovať nudné opakujúce sa úlohy pomocou VBA v programe Excel. Pripojiť sa k Kurz Excel VBA

Získajte viac hodnôt vyhľadávania v jednej bunke (bez opakovania)

Existuje možnosť, že v údajoch môžete mať opakovania.

Ak použijete vyššie uvedený kód, vo výsledku vám to tiež prinesie opakovania.

Ak chcete dosiahnuť výsledok tam, kde sa neopakujú, musíte kód trochu upraviť.

Tu je kód VBA, ktorý vám poskytne viac hodnôt vyhľadávania v jednej bunke bez akýchkoľvek opakovaní.

'Code by Sumit Bansal (https://trumpexcel.com) Funkcia MultipleLookupNoRept (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long Dim Result As String For i = 1 To LookupRange.Columns (1) .Cells .Count If LookupRange.Cells (i, 1) = Lookupvalue Then For J = 1 To i - 1 If LookupRange.Cells (J, 1) = Lookupvalue Then If LookupRange.Cells (J, ColumnNumber) = LookupRange.Cells (i, ColumnNumber) Then GoTo Skip End if End If Next J Result = Result & "" & LookupRange.Cells (i, ColumnNumber) & "," Skip: End If Next i MultipleLookupNoRept = Left (Result, Len (Result) - 1) End Funkcia

Akonáhle vložíte tento kód do editora VB (ako je uvedené vyššie v návode), budete môcť používať MultipleLookupNoRept funkciu.

Tu je prehľad výsledkov, ktoré s tým získate MultipleLookupNoRept funkciu.

Kliknutím sem stiahnete ukážkový súbor

V tomto návode som sa zaoberal tým, ako pomocou vzorcov a jazyka VBA v programe Excel nájsť a vrátiť viac hodnôt vyhľadávania do jednej bunky v programe Excel.

Aj keď to môžete ľahko vykonať pomocou jednoduchého vzorca, ak v predplatnom Microsoft 365 používate Excel, ak používate predchádzajúce verzie a nemáte prístup k funkciám, ako je TEXTJOIN, stále to môžete urobiť pomocou jazyka VBA vytvorením svojho vlastná vlastná funkcia.