sadržaj
Sve klasične funkcije pretraživanja i zamjene tipova VPR (Vlookup), GPR (HLOOKUP), IZLOŽENIJI (UTAKMICA) a oni poput njih imaju jednu važnu osobinu – pretražuju od početka do kraja, tj. slijeva nadesno ili od vrha do dna u izvornim podacima. Čim se pronađe prvo podudaranje, pretraga se zaustavlja i pronalazi se samo prvo pojavljivanje elementa koji nam je potreban.
Šta učiniti ako moramo pronaći ne prvu, već posljednju pojavu? Na primjer, zadnja transakcija za klijenta, posljednja uplata, posljednja narudžba, itd.?
Metoda 1: Pronalaženje posljednjeg reda pomoću formule niza
Ako originalna tabela nema kolonu sa datumom ili serijskim brojem reda (narudžba, plaćanje…), onda je naš zadatak, zapravo, pronaći zadnji red koji zadovoljava zadati uslov. To se može uraditi sa sljedećom formulom niza:
Ovdje:
- funkcija IF (AKO) provjerava sve ćelije u koloni jednu po jednu kupac i prikazuje broj reda ako sadrži ime koje nam je potrebno. Broj reda na listu nam daje funkcija LINE (RED), ali pošto nam je potreban broj reda u tabeli, moramo dodatno oduzeti 1, jer imamo zaglavlje u tabeli.
- Zatim funkcija MAX (MAX) bira maksimalnu vrijednost iz formiranog skupa brojeva redova, odnosno broj posljednje linije klijenta.
- funkcija INDEX (INDEX) vraća sadržaj ćelije sa pronađenim zadnjim brojem iz bilo koje druge potrebne kolone tabele (Šifra narudžbe).
Sve ovo mora biti upisano kao formula niza, odnosno:
- U Office 365 sa instaliranim najnovijim ažuriranjima i podrškom za dinamičke nizove, možete jednostavno pritisnuti ući.
- U svim ostalim verzijama, nakon unosa formule, morat ćete pritisnuti prečicu na tipkovnici Ctrl+smjena+ući, koji će mu automatski dodati vitičaste zagrade u traku formule.
Metoda 2: Obrnuto traženje s novom funkcijom LOOKUP
Već sam napisao dugačak članak s videom o novoj funkciji VIEW (XLOOKUP), koji se pojavio u najnovijim verzijama Officea kako bi zamijenio stari VLOOKUP (Vlookup). Uz pomoć BROWSE naš zadatak je riješen sasvim elementarno, jer. za ovu funkciju (za razliku od VLOOKUP-a), možete eksplicitno postaviti smjer pretraživanja: odozgo prema dolje ili odozdo prema gore – za to je odgovoran njen posljednji argument (-1):
Metoda 3. Potražite niz s najnovijim datumom
Ako u izvornim podacima imamo stupac sa serijskim brojem ili datumom koji ima sličnu ulogu, tada je zadatak izmijenjen – moramo pronaći ne zadnji (najniži) red s podudaranjem, već red s najnovijim ( maksimalni) datum.
Već sam detaljno raspravljao o tome kako to učiniti koristeći klasične funkcije, a sada pokušajmo iskoristiti snagu novih funkcija dinamičkog niza. Za veću ljepotu i praktičnost, također pretvaramo originalnu tablicu u "pametnu" tablicu koristeći prečicu na tipkovnici Ctrl+T ili komande Početna – Formatirajte kao tabelu (Početna — Format kao tabela).
Uz njihovu pomoć ovaj „par ubica“ vrlo graciozno rješava naš problem:
Ovdje:
- Prvo funkcija FILTER (FILTER) bira samo one redove iz naše tabele gde se nalaze u koloni kupac – ime koje nam treba.
- Zatim funkcija GRADE (SORTIRAJ) sortira odabrane redove po datumu u opadajućem redoslijedu, s najnovijim dogovorom na vrhu.
- funkcija INDEX (INDEX) izdvaja prvi red, tj. vraća poslednju trgovinu koja nam je potrebna.
- I, konačno, eksterna funkcija FILTER uklanja dodatne 1. i 3. stupce iz rezultata (Šifra narudžbe и kupac) i ostavlja samo datum i iznos. Za to se koristi niz konstanti. {0;1;0;1}, definirajući koje kolone želimo (1) ili ne želimo (0) da se prikazuju.
Metoda 4: Pronalaženje posljednjeg podudaranja u Power Queryju
Pa, radi kompletnosti, pogledajmo rješenje našeg problema obrnutog pretraživanja pomoću dodatka Power Query. Uz njenu pomoć sve se rješava vrlo brzo i lijepo.
1. Pretvorimo našu originalnu tabelu u “pametnu” koristeći prečicu na tastaturi Ctrl+T ili komande Početna – Formatirajte kao tabelu (Početna — Format kao tabela).
2. Učitajte ga u Power Query pomoću dugmeta Iz tabele/opseg tabulator podaci (Podaci — iz tabele/opseg).
3. Mi sortiramo (kroz padajuću listu filtera u zaglavlju) našu tabelu po opadajućem redosledu datuma, tako da su najnovije transakcije na vrhu.
4… U kartici transformacija izabrati tim Grupa po (Transformacija — Grupiraj po) i postavite grupisanje po kupcima i kao funkciju agregiranja odaberite opciju Sve linije (Svi redovi). Novu kolonu možete imenovati kako god želite – na primjer detalji.
Nakon grupisanja, dobićemo listu jedinstvenih imena naših klijenata iu koloni detalji – tabele sa svim transakcijama svake od njih, gde će u prvom redu biti poslednja transakcija, što nam je potrebno:
5. Dodajte novu izračunatu kolonu pomoću dugmeta Prilagođena kolona tabulator Dodajte stupac (Dodaj kolonu — Dodaj prilagođenu kolonu)i unesite sljedeću formulu:
ovdje detalji – ovo je kolona iz koje uzimamo tabele po kupcima, i 0 {} je broj reda koji želimo da izdvojimo (numeracija redova u Power Queryju počinje od nule). Dobijamo kolonu sa zapisima (rekord), gdje je svaki unos prvi red iz svake tabele:
Ostaje da proširite sadržaj svih zapisa pomoću dugmeta sa dvostrukim strelicama u zaglavlju kolone Poslednji dogovor odabirom željenih kolona:
… a zatim izbrišite kolonu koja više nije potrebna detalji desnim klikom na njegov naslov – Uklonite stupce (Ukloni kolone).
Nakon učitavanja rezultata na list do kraja Početna — Zatvori i učitaj — Zatvori i učitaj (Početna — Zatvori i učitaj — Zatvori i učitaj do…) dobićemo tako lepu tabelu sa listom nedavnih transakcija, kakvu smo želeli:
Kada mijenjate izvorne podatke, ne smijete zaboraviti ažurirati rezultate desnim klikom na njih – naredbom Ažurirajte i sačuvajte (Osvježiti) ili prečica na tastaturi Ctrl+alt+F5.
- Funkcija LOOKUP je potomak VLOOKUP-a
- Kako koristiti nove funkcije dinamičkog niza SORT, FILTER i UNIC
- Pronalaženje posljednje ćelije koja nije prazna u redu ili stupcu pomoću funkcije LOOKUP