Nedávno ma oslovil kamarát s prosbou o pomoc pri generovaní všetkých možných fráz, ktoré pozostávajú zo sady daných slov. Problémy tohto druhu môžu nastať pri zostavovaní zoznamov kľúčových slov a fráz pre online reklamu a SEO propagáciu, keď potrebujete prejsť všetkými možnými permutáciami slov vo vyhľadávacom dopyte:
V matematike sa táto operácia nazýva karteziánsky súčin. Oficiálna definícia je nasledovná: karteziánsky súčin množín A a B je množina všetkých párov, z ktorých prvá zložka patrí do množiny A a druhá zložka patrí do množiny B. Prvky množín môžu byť oboje. čísla a text.
Preložené do ľudskej reči to znamená, že ak v množine A máme napríklad slová „biela“ a „červená“ a v množine B „BMW“ a „Mercedes“, potom po karteziánskom súčine týchto dvoch množín get on output je množina všetkých možných variantov fráz, ktorá sa skladá zo slov oboch zoznamov:
- biele bmw
- červené bmw
- biely Mercedes
- červený mercedes
... teda presne to, čo potrebujeme. Pozrime sa na niekoľko spôsobov, ako vyriešiť túto úlohu v programe Excel.
Metóda 1. Vzorce
Začnime vzorcami. Predpokladajme, že ako počiatočné údaje máme tri zoznamy pôvodných slov v stĺpcoch A, B a C a počet prvkov v každom zozname sa môže líšiť:
Najprv si urobme tri stĺpce s indexmi, teda radovými číslami slov z každého zoznamu vo všetkých možných kombináciách. Prvý riadok jednotiek (E2:G2) bude zadaný ručne a pre zvyšok použijeme nasledujúci vzorec:
Logika je tu jednoduchá: ak index v predchádzajúcej nadradenej bunke už dosiahol koniec zoznamu, tj rovná sa počtu prvkov v zozname vypočítanom funkciou COUNT (COUNTA), potom číslovanie reštartujeme. V opačnom prípade zvýšime index o 1. Venujte zvláštnu pozornosť šikovnému zafixovaniu rozsahov znakmi dolára ($), aby ste mohli vzorec skopírovať nadol a doprava.
Teraz, keď máme poradové čísla slov, ktoré potrebujeme z každého zoznamu, môžeme pomocou funkcie extrahovať samotné slová INDEX (INDEX) do troch samostatných stĺpcov:
Ak ste sa s touto funkciou vo svojej práci ešte nestretli, dôrazne vám odporúčam, aby ste si ju naštudovali aspoň diagonálne – v mnohých situáciách pomáha a je užitočná nemenej (a ešte viac!) VPR (VLOOKUP).
Potom zostáva len prilepiť výsledné fragmenty riadok po riadku pomocou symbolu zreťazenia (&):
… alebo (ak máte najnovšiu verziu Excelu) s praktickou funkciou COMBINE (TEXTJOIN), ktorý dokáže prilepiť celý obsah zadaných buniek cez daný oddeľovací znak (medzera):
Metóda 2. Prostredníctvom Power Query
Power Query je výkonný doplnok pre Microsoft Excel, ktorý vykonáva dve hlavné úlohy: 1. načítanie údajov do Excelu z takmer akéhokoľvek externého zdroja a 2. všetky druhy transformácií načítaných tabuliek. Power Query je už zabudovaný do Excelu 2016-2019 a pre Excel 2010-2013 je nainštalovaný ako samostatný doplnok (môžete si ho zadarmo stiahnuť z oficiálnej webovej stránky Microsoftu). Ak ste ešte nezačali používať Power Query vo svojej práci, potom je čas sa nad tým zamyslieť, pretože transformácie, ako sú tie popísané vyššie, sa tam robia ľahko a prirodzene, iba niekoľkými pohybmi.
Najprv načítajme zdrojové zoznamy ako samostatné dotazy v Power Query. Ak to chcete urobiť, pre každú tabuľku vykonajte nasledujúce kroky:
- Premeňme stoly na „inteligentné“ pomocou tlačidla Formátovať ako tabuľku pútko Domov (Domov – Formátovať ako tabuľku) alebo klávesová skratka ctrl+T. Každá tabuľka dostane automaticky názov Tabuľka 1,2,3…, ktoré je však možné v prípade potreby zmeniť na karte staviteľ (Dizajn).
- Po nastavení aktívnej bunky v tabuľke stlačte tlačidlo Od stola (Z tabuľky) pútko dátum (Dátum) alebo na karte mocenský dotaz (ak ho máte nainštalovaný ako samostatný doplnok pre Excel 2010-2013).
- V okne editora dotazov, ktoré sa otvorí, vyberte príkaz Domov — Zavrieť a načítať — Zavrieť a načítať v… (Home — Close&Load — Close&Load to..) a potom možnosť Stačí vytvoriť spojenie (Vytvoriť iba pripojenie). Načítaná tabuľka tak zostane v pamäti a umožní sa k nej v budúcnosti pristupovať.
Ak urobíte všetko správne, výstup na pravom paneli by mali byť tri požiadavky v režime Iba pripojenie s názvami našich stolov:
Teraz kliknite pravým tlačidlom myši na prvý dotaz a vyberte príkaz odkaz (Referencia)na vytvorenie jeho aktualizovateľnej kópie a potom k údajom pomocou príkazu pridajte ďalší stĺpec Pridanie stĺpca ž – Vlastný stĺpec (Pridať stĺpec -ž vlastný stĺpec). V okne na zadanie vzorca zadajte názov nového stĺpca (napríklad Fragment2) a extrémne jednoduchý výraz ako vzorec:
=Tabuľka2
… tj inými slovami názov druhého dotazu:
Po kliknutí na OK zobrazí sa nám nový stĺpec, v každej bunke ktorého bude vnorená tabuľka s frázami z druhej tabuľky (obsah týchto tabuliek vidíte, ak kliknete na pozadie bunky vedľa slova Tabuľka):
Zostáva rozbaliť celý obsah týchto vnorených tabuliek pomocou tlačidla s dvojitými šípkami v hlavičke výsledného stĺpca a odškrtnutím Ako predponu použite pôvodný názov stĺpca (ako predponu použite pôvodný názov stĺpca):
…a dostaneme všetky možné kombinácie prvkov z prvých dvoch množín:
Ďalej je všetko podobné. Pridajte ďalší vypočítaný stĺpec so vzorcom:
=Tabuľka3
… a potom znova rozbaľte vnorené tabuľky – a teraz už máme všetky možné možnosti na permutovanie slov z troch množín, resp.
Zostáva vybrať všetky tri stĺpce zľava doprava a držať ctrla pomocou príkazu zreťazte ich obsah oddelený medzerami Zlúčiť stĺpce (Zlúčiť stĺpce) z karty Premena (Transformácia):
Výsledné výsledky je možné načítať späť na hárok pomocou už známeho príkazu Domov — Zavrieť a načítať — Zavrieť a načítať v… (Home — Close&Load — Close&Load to..):
Ak sa v budúcnosti niečo zmení v našich zdrojových tabuľkách s fragmentmi, potom bude stačiť len aktualizovať vygenerovaný dotaz kliknutím pravým tlačidlom myši na výslednú tabuľku a výberom príkazu Aktualizovať a uložiť (Obnoviť) alebo stlačením klávesovej skratky ctrl+ostatné+F5.
- Čo je Power Query, Power Pivot, Power Map a Power BI a prečo potrebujú používateľa Excelu
- Vytvorenie Ganttovho diagramu v Power Query
- 5 spôsobov použitia funkcie INDEX