Pred kratkim se je name obrnil prijatelj s prošnjo za pomoč pri generiranju vseh možnih besednih zvez, sestavljenih iz nabora danih besed. Težave te vrste se lahko pojavijo pri sestavljanju seznamov ključnih besed in fraz za spletno oglaševanje in SEO promocijo, ko morate iti skozi vse možne permutacije besed v iskalni poizvedbi:
V matematiki se ta operacija imenuje kartezični produkt. Uradna definicija je naslednja: kartezični produkt množic A in B je množica vseh parov, katerih prva komponenta pripada množici A, druga komponenta pa množici B. Še več, elementi množic so lahko obe številke in besedilo.
Prevedeno v človeški jezik to pomeni, da če imamo v množici A na primer besedi "bela" in "rdeča", v množici B pa "BMW" in "Mercedes", potem imamo za kartezičnim produktom teh dveh množic get on output je nabor vseh možnih različic fraz, sestavljenih iz besed obeh seznamov:
- bel bmw
- rdeči bmw
- beli mercedes
- rdeči mercedes
… torej ravno tisto, kar potrebujemo. Oglejmo si nekaj načinov za rešitev te naloge v Excelu.
Metoda 1. Formule
Začnimo s formulami. Predpostavimo, da imamo kot začetne podatke tri sezname izvirnih besed v stolpcih A, B in C, število elementov v vsakem seznamu pa se lahko spreminja:
Najprej naredimo tri stolpce z indeksi, torej zaporednimi številkami besed iz vsakega seznama v vseh možnih kombinacijah. Prvo vrstico enot (E2:G2) bomo vnesli ročno, za ostale pa bomo uporabili naslednjo formulo:
Logika je preprosta: če je indeks v nadrejeni prejšnji celici že dosegel konec seznama, tj. je enak številu elementov na seznamu, ki ga izračuna funkcija COUNT (COUNTA), nato ponovno začnemo s številčenjem. V nasprotnem primeru povečamo indeks za 1. Bodite posebno pozorni na pametno fiksiranje obsegov z znaki za dolar ($), da lahko kopirate formulo navzdol in na desno.
Zdaj, ko imamo redne številke besed, ki jih potrebujemo iz vsakega seznama, lahko same besede izluščimo s funkcijo INDEX (KAZALO) v tri ločene stolpce:
Če se s to funkcijo še niste srečali pri svojem delu, vam toplo svetujem, da jo preučite vsaj diagonalno - pomaga v mnogih situacijah in je uporabna nič manj (in celo več!) VPR (VLOOKUP).
No, po tem ostane samo lepljenje nastalih fragmentov vrstico za vrstico z uporabo simbola veriženja (&):
… ali (če imate najnovejšo različico Excela) s priročno funkcijo KOMBIN (ZDRUŽI BESEDILO), ki lahko zlepi celotno vsebino podanih celic skozi dani ločilni znak (presledek):
2. način. Prek Power Query
Power Query je zmogljiv dodatek za Microsoft Excel, ki opravlja dve glavni nalogi: 1. nalaganje podatkov v Excel iz skoraj vseh zunanjih virov in 2. vse vrste transformacij naloženih tabel. Power Query je že vgrajen v Excel 2016-2019, za Excel 2010-2013 pa je nameščen kot ločen dodatek (brezplačno ga lahko prenesete z uradne Microsoftove strani). Če še niste začeli uporabljati Power Queryja pri svojem delu, je čas, da razmislite o tem, saj se transformacije, kot so zgoraj opisane, tam izvajajo preprosto in naravno, v samo nekaj gibih.
Najprej naložimo izvorne sezname kot ločene poizvedbe v Power Query. Če želite to narediti, za vsako tabelo izvedite naslednje korake:
- Spremenimo mize v “pametne” z gumbom Oblikuj kot tabelo tab Domov (Domov — Oblikuj kot tabelo) ali bližnjico na tipkovnici Ctrl+T. Vsaka tabela bo samodejno dobila ime Tabela 1,2,3…, ki pa ga lahko po želji spreminjamo na zavihku Konstruktor (Oblikovanje).
- Ko nastavite aktivno celico v tabeli, pritisnite gumb Iz mize (Iz tabele) tab datum (Datum) ali na zavihku poizvedba o moči (če ga imate nameščenega kot ločen dodatek za Excel 2010-2013).
- V oknu urejevalnika poizvedb, ki se odpre, izberite ukaz Domov — Zapri in naloži — Zapri in naloži v… (Domov — Zapri&Naloži — Zapri&Naloži na..) in potem možnost Samo ustvarite povezavo (Ustvari samo povezavo). To bo pustilo naloženo tabelo v pomnilniku in omogočilo dostop do nje v prihodnosti.
Če vse naredite pravilno, bi moral biti rezultat na desni plošči tri zahteve v načinu Samo povezava z imeni naših tabel:
Zdaj z desno miškino tipko kliknite prvo poizvedbo in izberite ukaz Link (Sklic)da naredite kopijo, ki jo je mogoče posodobiti, nato pa podatkom z ukazom dodajte dodaten stolpec Dodajanje stolpca ž – stolpec po meri (Dodaj stolpec -ž stolpec po meri). V okno za vnos formule vnesite ime novega stolpca (na primer Fragment2) in izjemno preprost izraz kot formulo:
=Tabela2
… tj. z drugimi besedami, ime druge poizvedbe:
Po kliku OK videli bomo nov stolpec, v vsaki celici katerega bo ugnezdena tabela s frazami iz druge tabele (vsebino teh tabel si lahko ogledate, če kliknete v ozadju celice poleg besede Tabela):
Ostaja še razširiti vso vsebino teh ugnezdenih tabel s pomočjo gumba z dvojnimi puščicami v glavi nastalega stolpca in počistiti potrditveno polje Kot predpono uporabite izvirno ime stolpca (Uporabi izvirno ime stolpca kot predpono):
... in dobimo vse možne kombinacije elementov iz prvih dveh nizov:
Nadalje je vse podobno. Dodajte še en izračunani stolpec s formulo:
=Tabela3
… in nato znova razširimo ugnezdene tabele – in zdaj že imamo vse možne možnosti za permutacijo besed iz treh nizov oz.
Ostaja, da izberete vse tri stolpce od leve proti desni in držite Ctrl, in z ukazom združite njihovo vsebino, ločeno s presledki Združi stolpce (Združi stolpce) iz zavihka Preoblikovanje (preobrazba):
Dobljene rezultate je mogoče razložiti nazaj na list z že znanim ukazom Domov — Zapri in naloži — Zapri in naloži v… (Domov — Zapri&Naloži — Zapri&Naloži na..):
Če se bo v prihodnosti kaj spremenilo v naših izvornih tabelah z fragmenti, potem bo dovolj, da samo posodobite generirano poizvedbo tako, da z desnim klikom na nastalo tabelo izberete ukaz Posodobi in shrani (Osveži) ali s pritiskom na bližnjico na tipkovnici Ctrl+druga+F5.
- Kaj je Power Query, Power Pivot, Power Map in Power BI in zakaj potrebujejo uporabnika Excela
- Ustvarjanje gantograma v Power Query
- 5 načinov uporabe funkcije INDEX