Obsah
Vyhľadávanie kľúčových slov v zdrojovom texte je jednou z najčastejších úloh pri práci s údajmi. Pozrime sa na jeho riešenie niekoľkými spôsobmi pomocou nasledujúceho príkladu:
Predpokladajme, že vy a ja máme zoznam kľúčových slov – názvy značiek áut – a veľkú tabuľku všetkých druhov náhradných dielov, kde popisy môžu niekedy obsahovať jednu alebo niekoľko takýchto značiek naraz, ak náhradný diel pasuje na viac značka auta. Našou úlohou je nájsť a zobraziť všetky zistené kľúčové slová v susedných bunkách cez daný oddeľovací znak (napríklad čiarku).
Metóda 1. Power Query
Samozrejme, najprv zmeníme naše tabuľky na dynamické („inteligentné“) pomocou klávesovej skratky ctrl+T alebo príkazy Domov – Formátovať ako tabuľku (Domov – Formátovať ako tabuľku), dajte im mená (napr Známkyи Náhradné diely) a po jednom načítajte do editora Power Query výberom na karte Údaje – z tabuľky/rozsahu (Údaje – z tabuľky/rozsahu). Ak máte staršie verzie Excelu 2010-2013, kde je Power Query nainštalovaný ako samostatný doplnok, požadované tlačidlo bude na karte mocenský dotaz. Ak máte úplne novú verziu Excelu 365, potom tlačidlo Z tabuľky/rozsahu volali tam teraz S listami (Z hárku).
Po načítaní každej tabuľky v Power Query sa vrátime späť do Excelu s príkazom Domov — Zavrieť a načítať — Zavrieť a načítať do... — Iba vytvoriť pripojenie (Domov — Zavrieť a načítať — Zavrieť a načítať do... — Vytvoriť iba pripojenie).
Teraz vytvoríme duplicitnú žiadosť Náhradné dielykliknutím naň pravým tlačidlom myši a výberom Duplicitná žiadosť (Duplicitný dopyt), potom premenujte výslednú žiadosť o kopírovanie na Výsledky a budeme s ním naďalej spolupracovať.
Logika akcií je nasledovná:
- Na karte Rozšírené Pridanie stĺpca vybrať tím Vlastný stĺpec (Pridať stĺpec – vlastný stĺpec) a zadajte vzorec = Značky. Po kliknutí na OK dostaneme nový stĺpec, kde v každej bunke bude vnorená tabuľka so zoznamom našich kľúčových slov – značky automobilov:
- Pomocou tlačidla s dvojitými šípkami v hlavičke pridaného stĺpca rozbalíte všetky vnorené tabuľky. Zároveň sa riadky s popismi náhradných dielov vynásobia násobkom počtu značiek a získame všetky možné dvojice-kombinácie „značky náhradných dielov“:
- Na karte Rozšírené Pridanie stĺpca vybrať tím Podmienený stĺpec (Podmienený stĺpec) a nastaviť podmienku pre kontrolu výskytu kľúčového slova (značky) v zdrojovom texte (popis časti):
- Ak chcete, aby sa pri hľadaní nerozlišovali malé a veľké písmená, manuálne pridajte tretí argument do riadka vzorcov Compare.OrdinalIgnoreCase na funkciu kontroly výskytu Text.Obsahuje (ak riadok vzorcov nie je viditeľný, možno ho povoliť na karte preskúmanie):
- Výslednú tabuľku prefiltrujeme, v poslednom stĺpci necháme len jedničky, teda zhody a nepotrebný stĺpec odstránime výskyty.
- Zoskupenie identických popisov s príkazom Skupina podľa pútko Premena (Transformovať – zoskupiť podľa). Ako operáciu agregácie vyberte Všetky riadky (všetky riadky). Na výstupe dostaneme stĺpec s tabuľkami, ktorý obsahuje všetky podrobnosti pre každý náhradný diel vrátane značiek výrobcov automobilov, ktoré potrebujeme:
- Ak chcete extrahovať známky pre každú časť, pridajte na kartu ďalší vypočítaný stĺpec Pridanie stĺpca – vlastný stĺpec (Pridať stĺpec – vlastný stĺpec) a použite vzorec pozostávajúci z tabuľky (nachádzajú sa v našom stĺpci podrobnosti) a názov extrahovaného stĺpca:
- Klikneme na tlačidlo s dvojitými šípkami v hlavičke výsledného stĺpca a vyberieme príkaz Extrahovať hodnoty (Výber hodnôt)na výstup pečiatok s ľubovoľným oddeľovacím znakom, ktorý chcete:
- Odstránenie nepotrebného stĺpca podrobnosti.
- Aby sme do výslednej tabuľky pridali časti, ktoré z nej zmizli, kde v popisoch neboli nájdené žiadne značky, vykonáme postup na spojenie dotazu Výsledok s pôvodnou požiadavkou Náhradné diely gombík Kombinovať pútko Domov (Domov – Zlúčiť dopyty). Typ pripojenia - Vonkajšie pripojenie vpravo (pravé vonkajšie spojenie):
- Zostáva len odstrániť nadbytočné stĺpce a premenovať-presunúť zvyšné – a naša úloha je vyriešená:
Metóda 2. Vzorce
Ak máte verziu Excelu 2016 alebo novšiu, náš problém možno vyriešiť veľmi kompaktným a elegantným spôsobom pomocou novej funkcie COMBINE (TEXTJOIN):
Logika tohto vzorca je jednoduchá:
- Funkcie HĽADANIE (NÁJSŤ) vyhľadá postupne výskyt každej značky v aktuálnom popise dielu a vráti buď sériové číslo symbolu, od ktorého bola značka nájdená, alebo chybu #HODNOTA! ak značka nie je v popise.
- Potom použite funkciu IF (AK) и EOSIBKA (chyba) chyby nahradíme prázdnym textovým reťazcom „“, a poradové čísla znakov samotnými názvami značiek.
- Výsledné pole prázdnych buniek a nájdených značiek sa pomocou funkcie zostaví do jedného reťazca pomocou daného oddeľovacieho znaku COMBINE (TEXTJOIN).
Porovnanie výkonu a ukladanie dotazov do vyrovnávacej pamäte Power Query pre zrýchlenie
Pre testovanie výkonu si zoberme tabuľku so 100 popismi náhradných dielov ako počiatočné údaje. Na ňom dostaneme nasledujúce výsledky:
- Čas prepočtu podľa vzorcov (Metóda 2) – 9 sekúnd. pri prvom skopírovaní vzorca do celého stĺpca a 2 sek. pri opakovanom (pravdepodobne ovplyvňuje tlmenie).
- Čas aktualizácie dotazu Power Query (metóda 1) je oveľa horší – 110 sekúnd.
Samozrejme, veľa závisí od hardvéru konkrétneho PC a nainštalovanej verzie Office a aktualizácií, ale celkový obraz je myslím jasný.
Ak chcete zrýchliť dotaz Power Query, uložme do vyrovnávacej pamäte vyhľadávaciu tabuľku Známky, pretože sa nemení v procese vykonávania dotazu a nie je potrebné ho neustále prepočítavať (ako to de facto robí Power Query). Na to používame funkciu Table.Buffer zo vstavaného jazyka Power Query M.
Ak to chcete urobiť, otvorte dopyt Výsledky a na karte preskúmanie stlač tlačidlo Pokročilý editor (Zobraziť — Pokročilý editor). V okne, ktoré sa otvorí, pridajte riadok s novou premennou Marky 2, čo bude verzia nášho adresára automobilky s vyrovnávacou pamäťou, a túto novú premennú použite neskôr v nasledujúcom príkaze dotazu:
Po takomto spresnení sa rýchlosť aktualizácie našej požiadavky zvýši takmer 7-krát – až na 15 sekúnd. Úplne iná vec 🙂
- Fuzzy textové vyhľadávanie v Power Query
- Hromadné nahradenie textu vzorcami
- Hromadné nahradenie textu v Power Query pomocou funkcie List.Accumulate