Sadržaj
Traženje ključnih riječi u izvornom tekstu jedan je od najčešćih zadataka pri radu s podacima. Pogledajmo njegovo rješenje na nekoliko načina pomoću sljedećeg primjera:
Pretpostavimo da ti i ja imamo popis ključnih riječi – imena marki automobila – i veliku tablicu svih vrsta rezervnih dijelova, gdje opisi ponekad mogu sadržavati jednu ili više takvih marki odjednom, ako rezervni dio odgovara za više od jedne marka automobila. Naš zadatak je pronaći i prikazati sve otkrivene ključne riječi u susjednim ćelijama kroz zadani znak za razdvajanje (na primjer, zarez).
Metoda 1. Power Query
Naravno, prvo svoje tablice pretvaramo u dinamične ("pametne") pomoću prečice na tipkovnici Ctrl+T ili naredbe Početna – Format kao tablica (Početna — Formatiraj kao tablicu), dajte im imena (npr Markeи Rezervni djelovi) i učitajte jedan po jedan u uređivač Power Query odabirom na kartici Podaci – iz tablice/raspona (Podaci — iz tablice/raspona). Ako imate starije verzije programa Excel 2010-2013, gdje je Power Query instaliran kao zaseban dodatak, tada će željeni gumb biti na kartici Upit snage. Ako imate potpuno novu verziju programa Excel 365, tada gumb Iz tablice/raspona zove tamo sada S lišćem (Iz lista).
Nakon učitavanja svake tablice u Power Query, vraćamo se natrag u Excel s naredbom Početna — Zatvori i učitaj — Zatvori i učitaj u… — Samo stvori vezu (Početna — Zatvori i učitaj — Zatvori i učitaj u… — Samo stvori vezu).
Kreirajmo sada duplikat zahtjeva Rezervni djelovidesnim klikom na njega i odabirom Duplicirani zahtjev (Dvostruki upit), zatim preimenujte rezultirajući zahtjev za kopiranje u Rezultati i nastavit ćemo s njim surađivati.
Logika djelovanja je sljedeća:
- Na kartici Napredno Dodavanje stupca izabrati tim Prilagođeni stupac (Dodaj stupac — prilagođeni stupac) i unesite formulu = Marke. Nakon klika na OK dobit ćemo novi stupac, gdje će u svakoj ćeliji biti ugniježđena tablica s popisom naših ključnih riječi – marki proizvođača automobila:
- Koristite gumb s dvostrukim strelicama u zaglavlju dodanog stupca za proširenje svih ugniježđenih tablica. Pritom će se redovi s opisima rezervnih dijelova pomnožiti s višestrukim brojem marki, te ćemo dobiti sve moguće parove-kombinacije "rezervni dio-brand":
- Na kartici Napredno Dodavanje stupca izabrati tim Uvjetni stupac (uvjetni stupac) i postaviti uvjet za provjeru pojavljivanja ključne riječi (brand) u izvornom tekstu (opis dijela):
- Da pretraživanje ne razlikuje velika i mala slova, ručno dodajte treći argument u traku formule Usporedi.OrdinalIgnoreCase na funkciju provjere pojavljivanja Tekst.Sadrži (ako traka formule nije vidljiva, tada se može omogućiti na kartici pregled):
- Filtriramo dobivenu tablicu ostavljajući samo one u zadnjem stupcu tj. podudaranja i uklanjamo nepotrebni stupac događaji.
- Grupiranje identičnih opisa s naredbom Grupa po kartica transformacija (Transformacija — Grupiraj po). Kao operaciju združivanja odaberite Sve linije (Svi redovi). Na izlazu dobivamo stupac s tablicama koji sadrži sve pojedinosti za svaki rezervni dio, uključujući marke proizvođača automobila koji su nam potrebni:
- Da biste izdvojili ocjene za svaki dio, dodajte još jedan izračunati stupac na kartici Dodavanje stupca – prilagođeni stupac (Dodaj stupac — prilagođeni stupac) i koristite formulu koja se sastoji od tablice (nalaze se u našem stupcu Detaljnije) i naziv izdvojenog stupca:
- Kliknite na gumb s dvostrukim strelicama u zaglavlju dobivenog stupca i odaberite naredbu Ekstrakt vrijednosti (Izdvoj vrijednosti)za izlaz markica s bilo kojim znakom za razdvajanje koji želite:
- Uklanjanje nepotrebnog stupca Detaljnije.
- Da bismo rezultirajućoj tablici dodali dijelove koji su iz nje nestali, gdje u opisima nisu pronađene robne marke, provodimo postupak za kombiniranje upita Proizlaziti s originalnim zahtjevom Rezervni djelovi dugme Kombinirati kartica Naslovna (Početna — spoji upite). Vrsta veze – Vanjski spoj desno (Desni vanjski spoj):
- Sve što preostaje je ukloniti suvišne stupce i preimenovati-premjestiti preostale – i naš je zadatak riješen:
Metoda 2. Formule
Ako imate verziju Excela 2016 ili noviju, onda se naš problem može riješiti na vrlo kompaktan i elegantan način pomoću nove funkcije KOMBINIRATI (SPOJI TEKST):
Logika iza ove formule je jednostavna:
- funkcija PRETRAŽI (PRONAĆI) traži redom pojavljivanje svake marke u trenutnom opisu dijela i vraća ili serijski broj simbola, počevši od kojeg je marka pronađena, ili pogrešku #VALUE! ako marka nije u opisu.
- Zatim pomoću funkcije IF (AKO) и EOSHIBKA (ISGREŠKA) greške zamjenjujemo praznim tekstualnim nizom “”, a redne brojeve znakova samim nazivima robnih marki.
- Rezultirajući niz praznih ćelija i pronađenih robnih marki sastavlja se u jedan niz kroz zadani znak razdjelnika pomoću funkcije KOMBINIRATI (SPOJI TEKST).
Usporedba performansi i međuspremnik upita Power Query za ubrzanje
Za testiranje performansi, uzmimo tablicu od 100 opisa rezervnih dijelova kao početne podatke. Na njemu dobivamo sljedeće rezultate:
- Vrijeme ponovnog izračuna po formulama (Metoda 2) – 9 sek. kada prvi put kopirate formulu u cijeli stupac i 2 sek. pri ponovljenom (vjerojatno utječe međuspremnik).
- Vrijeme ažuriranja Power Query upita (Metoda 1) puno je gore – 110 sekundi.
Naravno, puno ovisi o hardveru pojedinog računala i instaliranoj verziji Officea i ažuriranja, ali ukupna slika je, mislim, jasna.
Kako bismo ubrzali Power Query upit, stavimo u međuspremnik tablicu pretraživanja Marke, jer se ne mijenja u procesu izvršavanja upita i nije ga potrebno stalno preračunavati (kao što de facto radi Power Query). Za to koristimo funkciju Tablica.Međuspremnik iz ugrađenog Power Query jezika M.
Da biste to učinili, otvorite upit Rezultati i na kartici pregled pritisni gumb Napredni uređivač (Prikaz — napredni uređivač). U prozoru koji se otvori dodajte redak s novom varijablom Marky 2, koji će biti verzija u međuspremniku našeg imenika proizvođača automobila, i upotrijebite ovu novu varijablu kasnije u sljedećoj naredbi upita:
Nakon takvog usavršavanja, brzina ažuriranja našeg zahtjeva povećava se gotovo 7 puta – do 15 sekundi. Sasvim druga stvar 🙂
- Pretraživanje neizrazitog teksta u Power Queryju
- Skupna zamjena teksta formulama
- Skupna zamjena teksta u Power Queryju s funkcijom List.Accumulate