Nedavno mi se prijatelj obratio s molbom da mu pomognem oko generiranja svih mogućih fraza koje se sastoje od skupa zadanih riječi. Problemi ove vrste mogu nastati prilikom sastavljanja popisa ključnih riječi i fraza za online oglašavanje i SEO promociju, kada morate proći kroz sve moguće permutacije riječi u upitu za pretraživanje:
U matematici se ova operacija naziva Kartezijanski produkt. Službena definicija je sljedeća: Kartezijev proizvod skupova A i B je skup svih parova, čija prva komponenta pripada skupu A, a druga komponenta pripada skupu B. Štoviše, elementi skupova mogu biti i brojevi i tekst.
Prevedeno na ljudski jezik to znači da ako u skupu A imamo npr. riječi “bijelo” i “crveno”, a u skupu B “BMW” i “Mercedes”, onda nakon Kartezijevog produkta ta dva skupa imamo dobiti na izlazu je skup svih mogućih varijanti izraza, sastavljen od riječi s oba popisa:
- bijeli bmw
- crveni bmw
- bijeli Mercedes
- crveni mercedes
… tj. upravo ono što nam treba. Pogledajmo nekoliko načina za rješavanje ovog zadatka u Excelu.
Metoda 1. Formule
Počnimo s formulama. Pretpostavimo da kao početne podatke imamo tri popisa izvornih riječi u stupcima A, B i C, redom, a broj elemenata u svakom popisu može varirati:
Najprije napravimo tri stupca s indeksima, odnosno rednim brojevima riječi iz svakog popisa u svim mogućim kombinacijama. Prvi red jedinica (E2:G2) unijet ćemo ručno, a za ostale ćemo koristiti sljedeću formulu:
Logika je ovdje jednostavna: ako je indeks u nadređenoj prethodnoj ćeliji već došao do kraja liste, tj. jednak je broju elemenata na listi izračunatom funkcijom TOČKA (COUNTA), zatim ponovno pokrećemo numeriranje. U suprotnom povećavamo indeks za 1. Obratite posebnu pozornost na pametno fiksiranje raspona sa znakovima dolara ($) tako da možete kopirati formulu dolje i udesno.
Sada kada imamo redne brojeve riječi koje su nam potrebne sa svakog popisa, možemo izdvojiti same riječi pomoću funkcije INDEKS (INDEKS) u tri odvojena stupca:
Ako se prije niste susreli s ovom funkcijom u svom radu, onda vam toplo savjetujem da je proučite barem dijagonalno - pomaže u mnogim situacijama i korisna je ništa manje (pa čak i više!) VPR (VLOOKUP).
Pa, nakon toga, ostaje samo zalijepiti dobivene fragmente red po red pomoću simbola ulančavanja (&):
… ili (ako imate najnoviju verziju programa Excel) s praktičnom funkcijom KOMBINIRATI (SPOJI TEKST), koji može lijepiti cijeli sadržaj navedenih ćelija kroz zadani znak razdjelnika (razmak):
Metoda 2. Kroz Power Query
Power Query je moćan dodatak za Microsoft Excel koji obavlja dva glavna zadatka: 1. učitavanje podataka u Excel iz gotovo bilo kojeg vanjskog izvora i 2. sve vrste transformacija učitanih tablica. Power Query je već ugrađen u Excel 2016-2019, a za Excel 2010-2013 instaliran je kao zaseban dodatak (možete ga besplatno preuzeti sa službene Microsoftove stranice). Ako još niste počeli koristiti Power Query u svom radu, onda je vrijeme da razmislite o tome, jer se transformacije poput ovih gore opisanih tamo rade jednostavno i prirodno, u samo par pokreta.
Prvo, učitajmo izvorne popise kao zasebne upite u Power Queryju. Da biste to učinili, za svaku tablicu izvedite sljedeće korake:
- Pretvorimo stolove u “pametne” gumbom Formatiraj kao tablicu kartica Naslovna (Početna — Formatiraj kao tablicu) ili tipkovni prečac Ctrl+T. Svaka će tablica automatski dobiti naziv Tablica 1,2,3…, koji se, međutim, po želji može promijeniti na kartici Konstruktor (Oblikovati).
- Nakon što postavite aktivnu ćeliju u tablici, pritisnite gumb Sa stola (Iz tablice) kartica Datum (Datum) ili na kartici Upit snage (ako ga imate instaliranog kao zasebni dodatak za Excel 2010-2013).
- U prozoru uređivača upita koji se otvori odaberite naredbu Početna — Zatvori i učitaj — Zatvori i učitaj u… (Početna — Zatvori&Učitaj — Zatvori&Učitaj u..) a zatim opciju Samo stvorite vezu (Stvori samo vezu). Ovo će ostaviti učitanu tablicu u memoriji i omogućiti joj pristup u budućnosti.
Ako sve učinite ispravno, tada bi izlaz na desnoj ploči trebao biti tri zahtjeva u načinu rada Samo veza s našim imenima tablica:
Sada desnom tipkom miša kliknite prvi upit i odaberite naredbu Veza (Referenca)da napravite njegovu kopiju koja se može ažurirati, a zatim dodajte dodatni stupac podacima putem naredbe Dodavanje stupca ž – Prilagođeni stupac (Dodaj stupac -ž prilagođeni stupac). U prozor za unos formule unesite naziv novog stupca (na primjer, Fragment2) i krajnje jednostavan izraz kao formulu:
=Tablica2
… tj. drugim riječima naziv drugog upita:
Nakon što kliknete OK vidjet ćemo novi stupac u čijoj će svakoj ćeliji biti ugniježđena tablica s frazama iz druge tablice (sadržaj ovih tablica možete vidjeti ako kliknete u pozadini ćelije pored riječi Stol):
Ostaje proširiti sav sadržaj ovih ugniježđenih tablica pomoću gumba s dvostrukim strelicama u zaglavlju dobivenog stupca i poništavanjem oznake Koristite izvorni naziv stupca kao prefiks (Koristite izvorni naziv stupca kao prefiks):
... i dobivamo sve moguće kombinacije elemenata iz prva dva skupa:
Dalje, sve je slično. Dodajte još jedan izračunati stupac s formulom:
=Tablica3
…, a zatim ponovno proširite ugniježđene tablice – i sada već imamo sve moguće opcije za permutaciju riječi iz tri skupa, redom:
Ostaje odabrati sva tri stupca s lijeva na desno, držeći Ctrl, te ulančati njihov sadržaj odvojen razmacima pomoću naredbe Spoji stupce (Spoji stupce) s kartice transformacija (Transformirati):
Rezultirajući rezultati mogu se istovariti natrag na list pomoću već poznate naredbe Početna — Zatvori i učitaj — Zatvori i učitaj u… (Početna — Zatvori&Učitaj — Zatvori&Učitaj u..):
Ako se u budućnosti nešto promijeni u našim izvornim tablicama s fragmentima, tada će biti dovoljno samo ažurirati generirani upit desnim klikom na rezultirajuću tablicu i odabirom naredbe Ažurirajte i spremite (Osvježiti) ili pritiskom na tipkovni prečac Ctrl+drugo+F5.
- Što je Power Query, Power Pivot, Power Map i Power BI i zašto im je potreban Excel korisnik
- Stvaranje gantograma u Power Queryju
- 5 načina za korištenje funkcije INDEX