Nedávno mě oslovil můj kamarád s prosbou o pomoc s generováním všech možných frází skládajících se z množiny daných slov. Problémy tohoto druhu mohou nastat při sestavování seznamů klíčových slov a frází pro online reklamu a SEO propagaci, když potřebujete projít všechny možné permutace slov ve vyhledávacím dotazu:
V matematice se tato operace nazývá kartézský součin. Oficiální definice je následující: kartézský součin množin A a B je množina všech dvojic, jejichž první složka patří do množiny A a druhá složka patří do množiny B. Prvky množin navíc mohou být obě čísla a text.
Přeloženo do lidské řeči to znamená, že pokud v množině A máme například slova „bílá“ a „červená“ a v množině B „BMW“ a „Mercedes“, pak po kartézském součinu těchto dvou množin get on output je množina všech možných variant frází složená ze slov obou seznamů:
- bílé bmw
- červené bmw
- bílý Mercedes
- červený mercedes
… tedy přesně to, co potřebujeme. Podívejme se na několik způsobů, jak tento úkol vyřešit v aplikaci Excel.
Metoda 1. Vzorce
Začněme vzorci. Předpokládejme, že jako počáteční data máme tři seznamy původních slov ve sloupcích A, B a C a počet prvků v každém seznamu se může lišit:
Nejprve si udělejme tři sloupce s indexy, tedy pořadovými čísly slov z každého seznamu ve všech možných kombinacích. První řádek jednotek (E2:G2) bude zadán ručně a pro zbytek použijeme následující vzorec:
Logika je zde jednoduchá: pokud index v nadřazené předchozí buňce již dosáhl konce seznamu, tj. je roven počtu prvků v seznamu vypočítanému funkcí COUNT (COUNTA), pak číslování restartujeme. V opačném případě zvýšíme index o 1. Věnujte zvláštní pozornost chytrému zafixování rozsahů znakem dolaru ($), abyste mohli vzorec zkopírovat dolů a doprava.
Nyní, když máme pořadová čísla slov, která potřebujeme z každého seznamu, můžeme pomocí funkce extrahovat samotná slova INDEX (INDEX) do tří samostatných sloupců:
Pokud jste se s touto funkcí ve své práci ještě nesetkali, pak vám důrazně doporučuji prostudovat si ji alespoň diagonálně – v mnoha situacích pomáhá a neméně (a ještě více!) je užitečná. VPR (VYHLEDAT).
Poté zbývá pouze slepit výsledné fragmenty řádek po řádku pomocí symbolu zřetězení (&):
… nebo (pokud máte nejnovější verzi Excelu) s praktickou funkcí KOMBAJN (TEXTJOIN), který dokáže slepit celý obsah zadaných buněk přes daný oddělovací znak (mezera):
Metoda 2. Prostřednictvím Power Query
Power Query je výkonný doplněk pro Microsoft Excel, který plní dva hlavní úkoly: 1. načítání dat do Excelu z téměř jakéhokoli externího zdroje a 2. všechny druhy transformací načtených tabulek. Power Query je již zabudován do Excelu 2016-2019 a pro Excel 2010-2013 je nainstalován jako samostatný doplněk (můžete si jej zdarma stáhnout z oficiálních stránek Microsoftu). Pokud jste ještě nezačali používat Power Query ve své práci, pak je čas se nad tím zamyslet, protože transformace, jako jsou ty popsané výše, se tam provádějí snadno a přirozeně, jen několika pohyby.
Nejprve načteme zdrojové seznamy jako samostatné dotazy v Power Query. Chcete-li to provést, pro každou tabulku proveďte následující kroky:
- Proměňme stoly na „chytré“ pomocí tlačítka Formátovat jako tabulku Karta Domů (Domů — Formátovat jako tabulku) nebo klávesovou zkratkou Ctrl+T. Každé tabulce bude automaticky přidělen název Tabulka1,2,3…, které však lze na přání změnit na záložce Stavitel (Design).
- Po nastavení aktivní buňky v tabulce stiskněte tlačítko Od stolu (Z tabulky) Karta Data (Datum) nebo na kartě Dotaz na napájení (pokud jej máte nainstalovaný jako samostatný doplněk pro Excel 2010-2013).
- V okně editoru dotazů, které se otevře, vyberte příkaz Domů — Zavřít a načíst — Zavřít a načíst v… (Domů — Zavřít&Načíst — Zavřít&Načíst do..) a pak možnost Stačí vytvořit spojení (Vytvořit pouze připojení). Tím zůstane načtená tabulka v paměti a umožní se k ní v budoucnu přistupovat.
Pokud vše uděláte správně, pak by výstupem v pravém panelu měly být tři požadavky v režimu Pouze připojení s názvy našich stolů:
Nyní klikněte pravým tlačítkem na první dotaz a vyberte příkaz Odkaz (Odkaz)vytvořit jeho aktualizovatelnou kopii a poté k datům přidat další sloupec pomocí příkazu Přidání sloupce ž – Vlastní sloupec (Přidat sloupec -ž vlastní sloupec). V okně pro zadání vzorce zadejte název nového sloupce (například Fragment2) a extrémně jednoduchý výraz jako vzorec:
= Tabulka2
… tj. jinými slovy název druhého dotazu:
Po kliknutí na tlačítko OK uvidíme nový sloupec, v jehož každé buňce bude vnořená tabulka s frázemi z druhé tabulky (obsah těchto tabulek uvidíte, pokud kliknete na pozadí buňky vedle slova Tabulka):
Zbývá rozbalit veškerý obsah těchto vnořených tabulek pomocí tlačítka s dvojitými šipkami v záhlaví výsledného sloupce a odškrtnutím Jako předponu použijte původní název sloupce (Jako předponu použijte původní název sloupce):
…a dostaneme všechny možné kombinace prvků z prvních dvou sad:
Dále je vše podobné. Přidejte další vypočítaný sloupec se vzorcem:
= Tabulka3
… a pak znovu rozbalte vnořené tabulky – a nyní již máme všechny možné možnosti permutace slov ze tří sad, resp.
Zbývá vybrat všechny tři sloupce zleva doprava, držet Ctrla zřetězit jejich obsah oddělený mezerami pomocí příkazu Sloučit sloupce (Sloučit sloupce) ze záložky Proměna (Přeměnit):
Výsledné výsledky lze načíst zpět na list již známým příkazem Domů — Zavřít a načíst — Zavřít a načíst v… (Domů — Zavřít&Načíst — Zavřít&Načíst do..):
Pokud se v budoucnu něco změní v našich zdrojových tabulkách s fragmenty, pak bude stačit pouze aktualizovat vygenerovaný dotaz kliknutím pravým tlačítkem myši na výslednou tabulku a výběrem příkazu Aktualizovat a uložit (Obnovit) nebo stisknutím klávesové zkratky Ctrl+Další+F5.
- Co je Power Query, Power Pivot, Power Map a Power BI a proč potřebují uživatele Excelu
- Vytvoření Ganttova diagramu v Power Query
- 5 způsobů použití funkce INDEX