Obsah
O tom, jak lze rychle slepit text z více buněk do jedné a naopak rozložit dlouhý textový řetězec na komponenty, jsem již psal. Nyní se podíváme na blízký, ale o něco složitější úkol – jak slepit text z několika buněk při splnění určité zadané podmínky.
Řekněme, že máme databázi zákazníků, kde jednomu názvu společnosti může odpovídat několik různých emailů jejích zaměstnanců. Naším úkolem je shromáždit všechny adresy podle názvů společností a zřetězit je (oddělené čárkami nebo středníky), abychom vytvořili například mailing list pro zákazníky, tedy získali výstup něco jako:
Jinými slovy, potřebujeme nástroj, který bude lepit (linkovat) text podle podmínky – obdobu funkce SUMMESLI (SUMIF), ale pro text.
Metoda 0. Vzorec
Ne moc elegantní, ale nejjednodušší způsob. Můžete napsat jednoduchý vzorec, který ověří, zda se firma v dalším řádku liší od předchozího. Pokud se neliší, nalepte další adresu oddělenou čárkou. Pokud se liší, „resetujeme“ nahromaděné a začínáme znovu:
Nevýhody tohoto přístupu jsou zřejmé: ze všech získaných buněk dodatečného sloupce potřebujeme pro každou společnost pouze ty poslední (žluté). Pokud je seznam velký, budete muset pomocí funkce přidat další sloupec, abyste je mohli rychle vybrat DLSTR (LEN), kontrola délky nashromážděných řetězců:
Nyní můžete ty odfiltrovat a zkopírovat potřebné nalepení adresy pro další použití.
Metoda 1. Makrofunkce lepení jednou podmínkou
Pokud původní seznam není seřazený podle společnosti, pak výše uvedený jednoduchý vzorec nefunguje, ale snadno se obejdete s malou vlastní funkcí ve VBA. Otevřete Editor jazyka Visual Basic stisknutím klávesové zkratky Alt + F11 nebo pomocí tlačítka Visual Basic Karta vývojka (Vývojář). V okně, které se otevře, vložte přes nabídku nový prázdný modul Vložit – Modul a zkopírujte tam text naší funkce:
Funkce MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " lepení se navzájem nerovnají - skončíme s chybou If SearchRange.Count <> TextRange.Count Then MergeIf = CVErr(xlErrRef) Konec Funkce End If 'projděte všechny buňky, zkontrolujte podmínku a shromážděte text v proměnné OutText For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'zobrazit výsledky bez posledního oddělovače MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End funkce
Pokud se nyní vrátíte do aplikace Microsoft Excel, pak v seznamu funkcí (tlačítko fx na řádku vzorců nebo na kartě Vzorce – funkce vložení) bude možné najít naši funkci MergeIf v kategorii Definováno uživatelem (definováno uživatelem). Argumenty funkce jsou následující:
Metoda 2. Zřetězení textu podle nepřesné podmínky
Pokud nahradíme první znak ve 13. řádku našeho makra = operátorovi přibližné shody Like, pak bude možné provést lepení nepřesnou shodou výchozích dat s kritériem výběru. Pokud lze například název společnosti napsat v různých variantách, můžeme je všechny zkontrolovat a shromáždit pomocí jedné funkce:
Standardní zástupné znaky jsou podporovány:
- hvězdička (*) – označuje libovolný počet libovolných znaků (včetně jejich nepřítomnosti)
- otazník (?) – představuje libovolný jednotlivý znak
- znak libry (#) – znamená libovolnou jednu číslici (0-9)
Operátor Like standardně rozlišuje velká a malá písmena, tj. chápe například „Orion“ a „orion“ jako různé společnosti. Chcete-li ignorovat malá a velká písmena, můžete přidat řádek na úplný začátek modulu v editoru jazyka Visual Basic Možnost Porovnat text, který přepne Like na rozlišení velkých a malých písmen.
Tímto způsobem můžete sestavit velmi složité masky pro kontrolu podmínek, například:
- ?1##??777RUS – výběr všech SPZ regionu 777, počínaje 1
- LLC* – všechny společnosti, jejichž název začíná na LLC
- ##7## – všechny produkty s pětimístným digitálním kódem, kde třetí číslice je 7
- ????? – všechna jména o pěti písmenech atd.
Metoda 3. Funkce makra pro lepení textu za dvou podmínek
V práci může nastat problém, když potřebujete propojit text více než jednu podmínku. Představme si například, že v naší předchozí tabulce přibyl ještě jeden sloupec s městem a lepení by se mělo provádět nejen pro danou firmu, ale i pro dané město. V tomto případě bude muset být naše funkce mírně modernizována přidáním další kontroly rozsahu:
Funkce MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = ", " 'oddělovací znaky (lze nahradit mezerou nebo ; atd.) e.) 'pokud se rozsahy ověření a lepení navzájem nerovnají, ukončete s chybou If SearchRange1.Count <> TextRange.Count Nebo SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Exit Function End If 'projděte všechny buňky, zkontrolujte všechny podmínky a shromážděte text do proměnné OutText For i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells(i) = Condition1 And SearchRange2.Cells(i) = Condition2 Then OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'zobrazit výsledky bez posledního oddělovače MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
Bude použito přesně stejným způsobem – nyní je třeba více specifikovat argumenty:
Metoda 4. Seskupování a lepení v Power Query
Problém můžete vyřešit bez programování ve VBA, pokud použijete bezplatný doplněk Power Query. Pro Excel 2010-2013 je ke stažení zde a v Excelu 2016 je již ve výchozím nastavení zabudován. Pořadí akcí bude následující:
Power Query neumí pracovat s běžnými tabulkami, takže prvním krokem je přeměnit naši tabulku na „chytrou“. Chcete-li to provést, vyberte jej a stiskněte kombinaci Ctrl+T nebo vyberte z karty Domů – Formát jako tabulka (Domů — Formátovat jako tabulku). Na kartě, která se poté zobrazí Stavitel (Design) můžete nastavit název tabulky (ponechal jsem standard Tabulka 1):
Nyní načteme naši tabulku do doplňku Power Query. Chcete-li to provést, na kartě Data (pokud máte Excel 2016) nebo na kartě Power Query (pokud máte Excel 2010-2013) klikněte Od stolu (Data – z tabulky):
V okně editoru dotazů, které se otevře, vyberte sloupec kliknutím na záhlaví O nás a stiskněte tlačítko nahoře Skupina (Skupina vytvořená). Zadejte název nového sloupce a typ operace v seskupení – Všechny řádky (Všechny řádky):
Klikněte na OK a získáme minitabulku seskupených hodnot pro každou společnost. Obsah tabulek je dobře viditelný, pokud ve výsledném sloupci kliknete levým tlačítkem na bílé pozadí buněk (nikoli na text!):
Nyní přidáme ještě jeden sloupec, kde pomocí funkce slepíme obsah sloupců Adresa v každé z minitabulek oddělených čárkami. Chcete-li to provést, na kartě Přidat sloupec tiskneme Vlastní sloupec (Přidat sloupec – Vlastní sloupec) a v okně, které se objeví, zadejte název nového sloupce a spojovací vzorec v jazyce M integrovaném do Power Query:
Všimněte si, že všechny M-funkce rozlišují velká a malá písmena (na rozdíl od Excelu). Po kliknutí na OK dostaneme nový sloupec se slepenými adresami:
Zbývá odstranit již nepotřebný sloupec TableAddresses (klikněte pravým tlačítkem na název) Smazat sloupec) a kliknutím na záložku nahrajte výsledky do listu Domů — Zavřete a stáhněte (Domů – Zavřít a načíst):
Důležitá nuance: Na rozdíl od předchozích metod (funkcí) se tabulky z Power Query neaktualizují automaticky. Pokud v budoucnu dojde ke změnám ve zdrojových datech, budete muset kliknout pravým tlačítkem myši kdekoli v tabulce výsledků a vybrat příkaz Aktualizovat a uložit (Obnovit).
- Jak rozdělit dlouhý textový řetězec na části
- Několik způsobů, jak slepit text z různých buněk do jedné
- Použití operátoru Like k testování textu proti masce