sadržaj
Pretraživanje 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 koristeći sljedeći primjer:
Pretpostavimo da vi i ja imamo listu ključnih riječi – nazive marki automobila – i veliku tabelu svih vrsta rezervnih dijelova, gdje opisi ponekad mogu sadržavati jednu ili više takvih marki odjednom, ako rezervni dio odgovara više od jednog marka automobila. Naš zadatak je da pronađemo i prikažemo sve detektovane ključne riječi u susjednim ćelijama kroz dati znak za razdvajanje (na primjer, zarez).
Metod 1. Power Query
Naravno, prvo pretvorimo naše tabele u dinamičke („pametne“) koristeći prečicu na tastaturi Ctrl+T ili komande Početna – Formatirajte kao tabelu (Početna — Format kao tabela), dajte im imena (npr Poštanske markeи Rezervni dijelovi) i učitavajte jedan po jedan u uređivač Power Query odabirom na kartici Podaci – iz tabele/opseg (Podaci — iz tabele/opseg). Ako imate starije verzije programa Excel 2010-2013, gdje je Power Query instaliran kao poseban dodatak, tada će željeno dugme biti na kartici power query. Ako imate potpuno novu verziju programa Excel 365, onda dugme Iz tabele/opseg zvao tamo sada Sa lišćem (Sa lista).
Nakon učitavanja svake tabele u Power Query, vraćamo se nazad u Excel sa komandom Početna — Zatvori i učitaj — Zatvori i učitaj na… — Samo kreiraj vezu (Početna — Zatvori i učitaj — Zatvori i učitaj na… — Samo kreiraj vezu).
Sada kreirajmo duplikat zahtjeva Rezervni dijelovidesnim klikom na njega i odabirom Duplikat zahtjeva (Duplikat upita), a zatim preimenujte rezultirajući zahtjev za kopiranje u Rezultati i nastavićemo da radimo sa njim.
Logika akcija je sledeća:
- Na kartici Napredno Dodavanje kolone izabrati tim Prilagođena kolona (Dodaj kolonu — Prilagođena kolona) i unesite formulu = Brendovi. Nakon klika na OK dobićemo novu kolonu, u kojoj će se u svakoj ćeliji nalaziti ugniježđena tabela sa listom naših ključnih riječi – marke proizvođača automobila:
- Koristite dugme sa dvostrukim strelicama u zaglavlju dodane kolone da proširite sve ugnežđene tabele. Istovremeno, redovi sa opisima rezervnih delova će se množiti sa višestrukim brojem marki, i dobićemo sve moguće parove-kombinacije "rezervni deo-brend":
- Na kartici Napredno Dodavanje kolone izabrati tim Uslovna kolona (Uslovni stupac) i postaviti uslov za provjeru pojavljivanja ključne riječi (brend) u izvornom tekstu (opis dijela):
- Da biste učinili pretraživanje neosjetljivim na velika i mala slova, ručno dodajte treći argument u traku formule Compare.OrdinalIgnoreCase na funkciju provjere pojavljivanja Tekst.Sadrži (ako traka formule nije vidljiva, onda se može omogućiti na kartici pregled):
- Filtriramo rezultirajuću tabelu, ostavljajući samo one u zadnjoj koloni, tj. podudaranja i uklanjamo nepotrebnu kolonu Pojave.
- Grupiranje identičnih opisa pomoću naredbe Grupa po tabulator transformacija (Transformacija — Grupirajte po). Kao operaciju agregacije, izaberite Sve linije (Svi redovi). Na izlazu dobivamo stupac s tabelama, koji sadrži sve detalje za svaki rezervni dio, uključujući marke proizvođača automobila koje su nam potrebne:
- Da biste izdvojili ocjene za svaki dio, dodajte još jednu izračunatu kolonu na karticu Dodavanje kolone – Prilagođena kolona (Dodaj kolonu — Prilagođena kolona) i koristite formulu koja se sastoji od tabele (nalaze se u našoj koloni detalji) i naziv izdvojene kolone:
- Kliknemo na dugme sa dvostrukim strelicama u zaglavlju rezultirajuće kolone i izaberemo naredbu Izdvoj vrijednosti (Izdvoji vrijednosti)za izlaz pečata sa bilo kojim znakom za razdvajanje koji želite:
- Uklanjanje nepotrebne kolone detalji.
- Da bismo u rezultirajuću tablicu dodali dijelove koji su nestali iz nje, gdje u opisima nisu pronađene marke, provodimo proceduru za kombiniranje upita rezultat sa originalnim zahtjevom Rezervni dijelovi dugme Kombinujte tabulator Početna (Početna — spajanje upita). Vrsta veze - Desno vanjsko spajanje (desni vanjski spoj):
- Ostaje samo ukloniti dodatne kolone i preimenovati-premjestiti preostale – i naš zadatak je riješen:
Metoda 2. Formule
Ako imate verziju Excel 2016 ili noviju, onda se naš problem može riješiti na vrlo kompaktan i elegantan način pomoću nove funkcije POGLEDAJTE (TEKST PRIDRUŽI SE):
Logika ove formule je jednostavna:
- funkcija TRAŽI (PRONAĐI) traži pojavljivanje svake marke redom u trenutnom opisu dijela i vraća ili serijski broj simbola, počevši od kojeg je marka pronađena, ili grešku #VRIJEDNOST! 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 brendova.
- Rezultirajući niz praznih ćelija i pronađenih marki sastavlja se u jedan niz kroz dati znak za razdvajanje pomoću funkcije POGLEDAJTE (TEKST PRIDRUŽI SE).
Poređenje performansi i baferovanje upita Power Query za ubrzanje
Za testiranje performansi, uzmimo tabelu sa 100 opisa rezervnih dijelova kao početne podatke. Na njemu dobijamo sledeće rezultate:
- Vreme preračunavanja po formulama (Metod 2) – 9 sek. kada prvi put kopirate formulu u cijeli stupac i 2 sek. pri ponovljenom (verovatno utiče puferovanje).
- Vrijeme ažuriranja Power Query upita (Metoda 1) je mnogo lošije – 110 sekundi.
Naravno, mnogo zavisi od hardvera određenog računara i instalirane verzije Officea i ažuriranja, ali ukupna slika je, mislim, jasna.
Da bismo ubrzali Power Query upit, stavimo bafer u tabelu pretraživanja Poštanske marke, jer se ne mijenja u procesu izvršavanja upita i nije ga potrebno stalno preračunavati (kao što Power Query de facto čini). Za to koristimo funkciju Table.Buffer iz ugrađenog Power Query jezika M.
Da biste to učinili, otvorite upit Rezultati i na kartici pregled pritisnite dugme Napredni uređivač (Prikaz — Napredni uređivač). U prozoru koji se otvori dodajte red sa novom varijablom Marky 2, koji će biti baferirana verzija našeg direktorija automakera, i koristiti ovu novu varijablu kasnije u sljedećoj naredbi upita:
Nakon takvog preciziranja, brzina ažuriranja našeg zahtjeva povećava se za skoro 7 puta – do 15 sekundi. Sasvim drugačija stvar 🙂
- Pretraživanje nejasnog teksta u Power Queryju
- Skupna zamjena teksta formulama
- Grupna zamjena teksta u Power Queryju s funkcijom List.Accumulate