Väzba textu podľa podmienky

Už som písal o tom, ako sa dá rýchlo zlepiť text z viacerých buniek do jednej a naopak, rozložiť dlhý textový reťazec na komponenty. Teraz sa pozrime na blízku, no o niečo zložitejšiu úlohu – ako zlepiť text z niekoľkých buniek pri splnení určitej špecifikovanej podmienky. 

Povedzme, že máme databázu zákazníkov, kde jednému názvu firmy môže zodpovedať viacero rôznych emailov jej zamestnancov. Našou úlohou je zhromaždiť všetky adresy podľa názvov spoločností a zreťaziť ich (oddelené čiarkami alebo bodkočiarkami), aby sme vytvorili napríklad zoznam adries pre zákazníkov, teda dostali výstup niečo ako:

Inými slovami, potrebujeme nástroj, ktorý bude lepiť (linkovať) text podľa podmienky – analóg funkcie SUMMESLI (SUMIF), ale pre text.

Spôsob 0. Vzorec

Nie veľmi elegantný, ale najjednoduchší spôsob. Môžete napísať jednoduchý vzorec, ktorý skontroluje, či sa spoločnosť v nasledujúcom riadku líši od predchádzajúceho. Ak sa nelíši, prilepte ďalšiu adresu oddelenú čiarkou. Ak sa líši, nahromadené „resetujeme“ a začneme znova:

Nevýhody tohto prístupu sú zrejmé: zo všetkých získaných buniek dodatočného stĺpca potrebujeme pre každú spoločnosť len posledné (žlté). Ak je zoznam veľký, budete musieť pomocou funkcie pridať ďalší stĺpec, aby ste ich mohli rýchlo vybrať DLSTR (LEN), kontrola dĺžky nahromadených reťazcov:

Teraz si môžete tie odfiltrovať a skopírovať potrebné nalepenie adries pre ďalšie použitie.

Metóda 1. Makrofunkcia lepenia jednou podmienkou

Ak pôvodný zoznam nie je zoradený podľa spoločnosti, potom vyššie uvedený jednoduchý vzorec nefunguje, ale môžete sa ľahko obísť pomocou malej vlastnej funkcie vo VBA. Otvorte Editor jazyka Visual Basic stlačením klávesovej skratky Alt + F11 alebo pomocou tlačidla Visual Basic pútko vývojka (vývojár). V okne, ktoré sa otvorí, vložte nový prázdny modul cez ponuku Vložiť – Modul a skopírujte tam text našej funkcie:

Funkcia MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " lepenia nie sú rovnaké - skončíme s chybou If SearchRange.Count <> TextRange.Count Potom MergeIf = CVErr(xlErrRef) Koniec Funkcia End If 'prejdi cez všetky bunky, skontroluj podmienku a zozbieraj text do premennej OutText For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'zobraziť výsledky bez posledného oddeľovača MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End funkciu  

Ak sa teraz vrátite do programu Microsoft Excel, potom v zozname funkcií (tlačidlo fx na paneli vzorcov alebo na karte Vzorce – funkcia vloženia) bude možné nájsť našu funkciu MergeIf v kategórii Definované užívateľom (Definované užívateľom). Argumenty funkcie sú nasledovné:

Metóda 2. Zreťazte text podľa nepresnej podmienky

Ak nahradíme prvý znak v 13. riadku nášho makra = operátorovi približnej zhody Ako, potom bude možné vykonať lepenie nepresnou zhodou počiatočných údajov s výberovým kritériom. Napríklad, ak môže byť názov spoločnosti napísaný v rôznych variantoch, potom ich môžeme všetky skontrolovať a zhromaždiť pomocou jednej funkcie:

Podporované sú štandardné zástupné znaky:

• hviezdička (*) – označuje ľubovoľný počet ľubovoľných znakov (vrátane ich absencie)
• otáznik (?) – predstavuje ľubovoľný znak
• znak libry (#) – predstavuje ľubovoľnú jednu číslicu (0-9)

Operátor Like štandardne rozlišuje veľké a malé písmená, čiže napríklad „Orion“ a „orion“ chápe ako rôzne spoločnosti. Ak chcete ignorovať malé a veľké písmená, môžete pridať riadok na úplný začiatok modulu v editore jazyka Visual Basic Možnosť Porovnať text, ktorý prepne Páči sa mi na nerozlišovanie malých a veľkých písmen.

Týmto spôsobom môžete zostaviť veľmi zložité masky na kontrolu podmienok, napríklad:

• ?1##??777RUS – výber všetkých ŠPZ regiónu 777, počnúc 1
• LLC* – všetky spoločnosti, ktorých názov začína na LLC
• ##7## – všetky produkty s päťmiestnym digitálnym kódom, kde tretia číslica je 7
• ????? – všetky mená pozostávajúce z piatich písmen atď.
  

Metóda 3. Funkcia makra na lepenie textu za dvoch podmienok

V práci môže nastať problém, keď potrebujete prepojiť text viac ako jednu podmienku. Predstavme si napríklad, že v našej predchádzajúcej tabuľke pribudol ešte jeden stĺpec s mestom a lepenie by sa malo realizovať nielen pre danú firmu, ale aj pre dané mesto. V tomto prípade bude potrebné našu funkciu mierne zmodernizovať pridaním ďalšej kontroly rozsahu:

Funkcia MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = ", " 'oddeľovacie znaky (možno nahradiť medzerou alebo ; atď.) e.) 'ak sa rozsahy validácie a lepenia navzájom nezhodujú, ukončite s chybou. 'prejdi všetky bunky, skontrolujte všetky podmienky a zhromaždi text do premennej OutText For i = 1 To SearchRange2.Cells.Count If SearchRange1.Cells(i) = Condition1 And SearchRange1.Cells(i) = Condition1 Then OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'zobraziť výsledky bez posledného oddeľovača MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function  

Použije sa presne rovnakým spôsobom – teraz je potrebné viac špecifikovať argumenty:

Metóda 4. Zoskupovanie a lepenie v Power Query

Problém môžete vyriešiť bez programovania vo VBA, ak použijete bezplatný doplnok Power Query. Pre Excel 2010-2013 si ho môžete stiahnuť tu a v Exceli 2016 je už štandardne zabudovaný. Postupnosť akcií bude nasledovná:

Power Query nevie, ako pracovať s bežnými tabuľkami, takže prvým krokom je zmeniť našu tabuľku na „inteligentnú“. Ak to chcete urobiť, vyberte ho a stlačte kombináciu ctrl+T alebo vyberte z karty Domov – Formátovať ako tabuľku (Domov – Formátovať ako tabuľku). Na karte, ktorá sa potom zobrazí staviteľ (Dizajn) môžete nastaviť názov tabuľky (nechal som štandard Tabuľka 1):

Teraz načítajme našu tabuľku do doplnku Power Query. Ak to chcete urobiť, na karte dátum (ak máte Excel 2016) alebo na karte Power Query (ak máte Excel 2010-2013) kliknite Od stola (Údaje – z tabuľky):

V okne editora dotazov, ktoré sa otvorí, vyberte stĺpec kliknutím na hlavičku Spoločnosť a stlačte tlačidlo vyššie skupina (skupina podľa). Zadajte názov nového stĺpca a typ operácie v zoskupení – Všetky riadky (všetky riadky):

Kliknite na tlačidlo OK a získame minitabuľku zoskupených hodnôt pre každú spoločnosť. Obsah tabuliek je jasne viditeľný, ak kliknete ľavým tlačidlom myši na biele pozadie buniek (nie na text!) vo výslednom stĺpci:

Teraz pridajme ešte jeden stĺpec, kde pomocou funkcie zlepíme obsah stĺpcov Adresa v každej z minitabuľiek oddelených čiarkami. Ak to chcete urobiť, na karte Pridať stĺpec stlačíme Vlastný stĺpec (Pridať stĺpec – vlastný stĺpec) a v zobrazenom okne zadajte názov nového stĺpca a spojovací vzorec v jazyku M zabudovanom do Power Query:

Všimnite si, že všetky M-funkcie rozlišujú veľké a malé písmená (na rozdiel od Excelu). Po kliknutí na OK dostaneme nový stĺpec s prilepenými adresami:

Zostáva odstrániť už nepotrebný stĺpec TableAddresses (kliknite pravým tlačidlom myši na názov) Odstrániť stĺpec) a kliknutím na záložku nahrajte výsledky do hárka Domov — Zatvorte a stiahnite (Domov – zavrieť a načítať):

Dôležitá nuansa: Na rozdiel od predchádzajúcich metód (funkcií) sa tabuľky z Power Query neaktualizujú automaticky. Ak v budúcnosti dôjde k zmenám v zdrojových údajoch, budete musieť kliknúť pravým tlačidlom myši kdekoľvek v tabuľke výsledkov a vybrať príkaz Aktualizovať a uložiť (Obnoviť).

• Ako rozdeliť dlhý textový reťazec na časti
• Niekoľko spôsobov, ako zlepiť text z rôznych buniek do jednej
• Použitie operátora Like na testovanie textu s maskou