Obsah
Hledání klíčových slov ve zdrojovém textu je jednou z nejčastějších úloh při práci s daty. Podívejme se na jeho řešení několika způsoby pomocí následujícího příkladu:
Předpokládejme, že vy a já máme seznam klíčových slov – názvy značek automobilů – a velkou tabulku všech druhů náhradních dílů, kde popisy mohou někdy obsahovat jednu nebo několik značek najednou, pokud náhradní díl pasuje na více než jeden značka auta. Naším úkolem je najít a zobrazit všechna zjištěná klíčová slova v sousedních buňkách prostřednictvím daného oddělovacího znaku (například čárky).
Metoda 1. Power Query
Samozřejmě nejprve převedeme naše tabulky na dynamické („chytré“) pomocí klávesové zkratky Ctrl+T nebo příkazy Domů – Formát jako tabulka (Domů — Formátovat jako tabulku), dejte jim jména (např Známkyи Náhradní díly) a načtěte jednu po druhé do editoru Power Query výběrem na kartě Data – z tabulky/rozsahu (Data – z tabulky/rozsahu). Pokud máte starší verze Excelu 2010-2013, kde je Power Query nainstalován jako samostatný doplněk, pak bude požadované tlačítko na kartě Dotaz na napájení. Pokud máte zcela novou verzi Excelu 365, pak tlačítko Z tabulky/rozsahu volal tam teď S listy (Z listu).
Po načtení každé tabulky v Power Query se s příkazem vrátíme zpět do Excelu Domů — Zavřít a načíst — Zavřít a načíst do… — Pouze vytvořit připojení (Domů — Zavřít a načíst — Zavřít a načíst do… — Vytvořit pouze připojení).
Nyní vytvoříme duplicitní požadavek Náhradní dílykliknutím na něj pravým tlačítkem a výběrem Duplicitní požadavek (Duplicitní dotaz)a poté přejmenujte výsledný požadavek na kopírování na Výsledky a budeme s ním nadále spolupracovat.
Logika akcí je následující:
- Na kartě Upřesnit Přidání sloupce vybrat tým Vlastní sloupec (Přidat sloupec – Vlastní sloupec) a zadejte vzorec = Značky. Po kliknutí na OK získáme nový sloupec, kde v každé buňce bude vnořená tabulka se seznamem našich klíčových slov – značek automobilek:
- Pomocí tlačítka s dvojitými šipkami v záhlaví přidaného sloupce rozbalíte všechny vnořené tabulky. Zároveň se řádky s popisy náhradních dílů vynásobí násobkem počtu značek a získáme všechny možné dvojice-kombinace „značka náhradních dílů“:
- Na kartě Upřesnit Přidání sloupce vybrat tým Podmíněný sloupec (Podmíněný sloupec) a nastavit podmínku pro kontrolu výskytu klíčového slova (značky) ve zdrojovém textu (popis části):
- Aby se při hledání nerozlišovala malá a velká písmena, ručně přidejte třetí argument do řádku vzorců Compare.OrdinalIgnoreCase na funkci kontroly výskytu Text.Obsahuje (pokud není řádek vzorců viditelný, lze jej povolit na kartě přezkoumání):
- Výslednou tabulku vyfiltrujeme, v posledním sloupci necháme samé jedničky, tedy shody a nepotřebný sloupec odstraníme Výskyt.
- Seskupení identických popisů s příkazem Skupina vytvořená Karta Proměna (Transformovat — Seskupit podle). Jako operaci agregace vyberte Všechny řádky (Všechny řádky). Na výstupu získáme sloupec s tabulkami, který obsahuje všechny podrobnosti pro každý náhradní díl, včetně značek automobilek, které potřebujeme:
- Chcete-li extrahovat stupně pro každý díl, přidejte na kartu další vypočítaný sloupec Přidání sloupce – Vlastní sloupec (Přidat sloupec – Vlastní sloupec) a použijte vzorec skládající se z tabulky (jsou umístěny v našem sloupci Detaily) a název extrahovaného sloupce:
- Klikneme na tlačítko s dvojitými šipkami v záhlaví výsledného sloupce a vybereme příkaz Extrahujte hodnoty (Výpis hodnot)pro výstup razítek s libovolným oddělovacím znakem:
- Odstranění nepotřebného sloupce Detaily.
- Abychom do výsledné tabulky přidali části, které z ní zmizely, kde nebyly v popisech nalezeny žádné značky, provedeme postup pro spojení dotazu Výsledek s původní žádostí Náhradní díly tlačítko Kombinovat Karta Domů (Domů – Sloučit dotazy). Typ připojení - Vnější spojení vpravo (Pravý vnější spoj):
- Zbývá pouze odstranit nadbytečné sloupce a přejmenovat-přesunout zbývající – a náš úkol je vyřešen:
Metoda 2. Vzorce
Pokud máte verzi Excelu 2016 nebo novější, pak lze náš problém vyřešit velmi kompaktním a elegantním způsobem pomocí nové funkce KOMBAJN (TEXTJOIN):
Logika tohoto vzorce je jednoduchá:
- funkce VYHLEDÁVÁNÍ (NALÉZT) vyhledá postupně výskyt každé značky v aktuálním popisu dílu a vrátí buď sériové číslo symbolu, od kterého byla značka nalezena, nebo chybu #HODNOTA! pokud značka není v popisu.
- Poté pomocí funkce IF (LI) и EOSIBKA (chyba) chyby nahradíme prázdným textovým řetězcem „“, a pořadová čísla znaků samotnými názvy značek.
- Výsledné pole prázdných buněk a nalezených značek se pomocí funkce sestaví do jediného řetězce pomocí daného oddělovacího znaku KOMBAJN (TEXTJOIN).
Porovnání výkonu a ukládání dotazů do vyrovnávací paměti Power Query pro zrychlení
Pro testování výkonu vezměme jako výchozí údaje tabulku 100 popisů náhradních dílů. Na něm dostaneme následující výsledky:
- Doba přepočtu podle vzorců (Metoda 2) – 9 sec. při prvním zkopírování vzorce do celého sloupce a 2 sec. při opakovaném (pravděpodobně ovlivňuje vyrovnávací paměť).
- Doba aktualizace dotazu Power Query (metoda 1) je mnohem horší – 110 sekund.
Samozřejmě hodně záleží na hardwaru konkrétního PC a nainstalované verzi Office a aktualizacích, ale celkový obrázek je myslím jasný.
Abychom urychlili dotaz Power Query, uložme do vyrovnávací paměti vyhledávací tabulku Známky, protože se v procesu provádění dotazu nemění a není nutné jej neustále přepočítávat (jako to de facto dělá Power Query). K tomu použijeme funkci Table.Buffer z vestavěného jazyka Power Query M.
Chcete-li to provést, otevřete dotaz Výsledky a na kartě přezkoumání zmáčknout tlačítko Pokročilý editor (Zobrazit — Pokročilý editor). V okně, které se otevře, přidejte řádek s novou proměnnou Marky 2, což bude verze našeho adresáře automobilky s vyrovnávací pamětí, a tuto novou proměnnou použijte později v následujícím příkazu dotazu:
Po takovém upřesnění se rychlost aktualizace našeho požadavku zvýší téměř 7krát – až na 15 sekund. Úplně jiná věc 🙂
- Fuzzy textové vyhledávání v Power Query
- Hromadné nahrazování textu vzorci
- Hromadné nahrazování textu v Power Query pomocí funkce List.Accumulate