Nedavno mi se obratio prijatelj sa molbom da pomognem u generisanju svih mogućih fraza koje se sastoje od skupa zadatih reči. Problemi ove vrste mogu nastati prilikom sastavljanja lista ključnih riječi i fraza za online oglašavanje i SEO promociju, kada treba proći kroz sve moguće permutacije riječi u upitu za pretragu:
U matematici se ova operacija naziva Kartezijanski proizvod. Zvanična definicija je sljedeća: kartezijanski proizvod skupova A i B je skup svih parova, čija prva komponenta pripada skupu A, a druga pripada skupu B. Štaviše, elementi skupova mogu biti oba brojevima i tekstom.
Prevedeno na ljudski jezik, to znači da ako u skupu A imamo, na primjer, riječi “bijeli” i “crveni”, au skupu B “BMW” i “Mercedes”, onda nakon kartezijanskog proizvoda ova dva skupa get on the output je skup svih mogućih varijanti fraza, sastavljen od riječi oba lista:
- bijeli bmw
- crveni bmw
- bijeli Mercedes
- crveni mercedes
… tj. upravo ono što nam treba. Pogledajmo nekoliko načina rješavanja ovog zadatka u Excelu.
Metoda 1. Formule
Počnimo sa formulama. Pretpostavimo da kao početne podatke imamo tri liste originalnih riječi u kolonama A, B i C, redom, a broj elemenata u svakoj listi može varirati:
Prvo napravimo tri kolone sa indeksima, odnosno rednim brojevima riječi sa svake liste u svim mogućim kombinacijama. Prvi red jedinica (E2:G2) biće unet ručno, a za ostale ćemo koristiti sledeću formulu:
Logika je ovdje jednostavna: ako je indeks u gornjoj prethodnoj ćeliji već došao do kraja liste, tj. jednak je broju elemenata u listi izračunatom funkcijom COUNT (COUNTA), zatim ponovo pokrećemo numerisanje. U suprotnom, povećavamo indeks za 1. Obratite posebnu pažnju 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 svake liste, možemo izdvojiti same riječi pomoću funkcije INDEX (INDEX) u tri odvojene kolone:
Ako se do sada 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 rezultirajuće fragmente red po red pomoću simbola konkatenacije (&):
… ili (ako imate najnoviju verziju Excela) sa praktičnom funkcijom POGLEDAJTE (TEKST PRIDRUŽI SE), koji može zalijepiti cijeli sadržaj navedenih ćelija kroz dati znak za razdvajanje (razmak):
Metod 2. Putem Power Queryja
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 tabela. Power Query je već ugrađen u Excel 2016-2019, a za Excel 2010-2013 instaliran je kao poseban dodatak (možete ga besplatno preuzeti sa službene Microsoft web stranice). Ako još niste počeli koristiti Power Query u svom radu, onda je vrijeme da razmislite o tome, jer se transformacije poput gore opisanih tamo rade lako i prirodno, u samo nekoliko pokreta.
Prvo, učitajmo izvorne liste kao zasebne upite u Power Queryju. Da biste to učinili, za svaku tablicu izvršite sljedeće korake:
- Pretvorimo stolove u „pametne“ pomoću dugmeta Formatirajte kao tabelu tabulator Početna (Početna — Format kao tabela) ili prečica na tastaturi Ctrl+T. Svaka tabela će automatski dobiti ime Tabela 1,2,3…, što se, međutim, može promijeniti po želji na kartici konstruktor (Dizajn).
- Nakon što postavite aktivnu ćeliju u tabeli, pritisnite dugme Sa stola (iz tabele) tabulator podaci (Datum) ili na kartici power query (ako ste ga instalirali kao poseban 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 (Kreiraj samo vezu). Ovo će ostaviti učitanu tabelu u memoriji i omogućiti joj da joj se pristupi u budućnosti.
Ako sve uradite ispravno, onda bi izlaz u desnom panelu trebao biti tri zahtjeva u modu Samo veza sa našim imenima tabela:
Sada kliknite desnim tasterom miša na prvi upit i izaberite komandu link (Referenca)da napravite njegovu kopiju koja se može ažurirati, a zatim dodajte dodatnu kolonu podacima putem naredbe Dodavanje kolone ž – Prilagođena kolona (Dodaj kolonu -ž prilagođenu kolonu). U prozor za unos formule unesite naziv nove kolone (na primjer, Fragment2) i izuzetno jednostavan izraz kao formulu:
=Tabela2
… tj. drugim riječima, naziv drugog upita:
Posle klika na OK vidjet ćemo novu kolonu u kojoj će se u svakoj ćeliji nalaziti ugniježđena tabela sa frazama iz druge tabele (sadržaj ovih tabela možete videti ako kliknete na pozadinu ćelije pored reči sto):
Ostaje proširiti sav sadržaj ovih ugniježđenih tabela pomoću dugmeta sa dvostrukim strelicama u zaglavlju rezultirajuće kolone i poništiti izbor Koristite originalno ime kolone kao prefiks (Koristite originalno ime kolone kao prefiks):
… i dobijamo sve moguće kombinacije elemenata iz prva dva seta:
Dalje, sve je slično. Dodajte još jednu izračunatu kolonu sa formulom:
=Tabela3
…, a zatim ponovo proširite ugniježđene tablice – i sada već imamo sve moguće opcije za permutiranje riječi iz tri skupa, redom:
Ostaje odabrati sve tri kolone s lijeva na desno, držeći Ctrl, i spojite njihov sadržaj odvojen razmacima pomoću naredbe Spoji kolone (Spoji kolone) sa kartice transformacija (Transformacija):
Rezultirajući rezultati se mogu vratiti na list pomoću već poznate komande 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 miša na rezultirajuću tablicu i odabirom naredbe Ažurirajte i sačuvajte (Osvježiti) ili pritiskom na prečicu na tastaturi Ctrl+alt+F5.
- Šta je Power Query, Power Pivot, Power Map i Power BI i zašto im je potreban Excel korisnik
- Kreiranje gantograma u Power Queryju
- 5 načina za korištenje funkcije INDEX