Sadržaj
Sve klasične funkcije pretraživanja i zamjene tipa VPR (VLOOKUP), GPR (HLOOKUP), EKSPONIRANIJI (UTAKMICA) a njima slični imaju jednu važnu osobinu – pretražuju od početka do kraja, tj. slijeva nadesno ili odozgo prema dolje u izvornim podacima. Čim se pronađe prvo podudaranje, pretraga se zaustavlja i nalazi se samo prvo pojavljivanje elementa koji nam je potreban.
Što učiniti ako trebamo pronaći ne prvo, već zadnje pojavljivanje? Na primjer, zadnja transakcija za klijenta, zadnja uplata, posljednja narudžba itd.?
Metoda 1: Pronalaženje posljednjeg retka pomoću formule polja
Ako originalna tablica nema stupac s datumom ili rednim brojem retka (narudžbe, plaćanja...), onda je naš zadatak zapravo pronaći zadnji redak koji zadovoljava zadani uvjet. To se može učiniti pomoću sljedeće formule polja:
Ovdje:
- funkcija IF (AKO) provjerava sve ćelije u stupcu jednu po jednu Kupac i prikazuje broj retka ako sadrži naziv koji nam je potreban. Broj retka na listu zadaje nam funkcija ONLINE (RED), ali kako nam treba broj reda u tablici, dodatno moramo oduzeti 1, jer imamo zaglavlje u tablici.
- Zatim funkcija MAX (MAX) odabire maksimalnu vrijednost iz formiranog skupa brojeva redaka, odnosno broj najnovije linije klijenta.
- funkcija INDEKS (INDEKS) vraća sadržaj ćelije s pronađenim zadnjim brojem iz bilo kojeg drugog potrebnog stupca tablice (Šifra narudžbe).
Sve ovo mora biti uneseno kao formula niza, tj.:
- U sustavu Office 365 s 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 tipkovni prečac Ctrl+smjena+ući, koji će mu automatski dodati vitičaste zagrade u traci formule.
Metoda 2: Obrnuto pretraživanje s novom funkcijom LOOKUP
Već sam napisao dugačak članak s videom o novoj značajci POGLED (XLOOKUP), koji se pojavio u najnovijim verzijama sustava Office kako bi zamijenio stari VLOOKUP (VLOOKUP). Uz pomoć BROWSE-a naš je zadatak 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 s rednim brojem ili datumom koji ima sličnu ulogu, tada se zadatak mijenja – moramo pronaći ne zadnji (najniži) redak s podudaranjem, već redak s najnovijim ( maksimalni) datum.
Već sam detaljno raspravljao o tome kako to učiniti pomoću klasičnih funkcija, a sada pokušajmo iskoristiti snagu novih funkcija dinamičkog niza. Za veću ljepotu i praktičnost, također pretvaramo originalni stol u "pametni" stol pomoću tipkovničkog prečaca Ctrl+T ili naredbe Početna – Format kao tablica (Početna — Formatiraj kao tablicu).
Uz njihovu pomoć ovaj “ubojiti par” vrlo graciozno rješava naš problem:
Ovdje:
- Funkcija prije svega FILTER (FILTAR) odabire samo one retke iz naše tablice gdje u stupcu Kupac – ime koje nam treba.
- Zatim funkcija GRADE (VRSTA) razvrstava odabrane retke prema datumu silaznim redoslijedom, s najnovijom pogodbom na vrhu.
- funkcija INDEKS (INDEKS) izdvaja prvi red, tj. vraća zadnji trade koji nam treba.
- I, konačno, vanjska 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 stupce želimo (1) ili ne želimo (0) prikazati.
Metoda 4: Pronalaženje posljednjeg podudaranja u Power Queryju
Pa, radi cjelovitosti, pogledajmo rješenje našeg problema obrnutog pretraživanja pomoću dodatka Power Query. Uz njezinu pomoć sve se vrlo brzo i lijepo riješi.
1. Pretvorimo našu izvornu tablicu u "pametnu" pomoću prečice na tipkovnici Ctrl+T ili naredbe Početna – Format kao tablica (Početna — Formatiraj kao tablicu).
2. Učitajte ga u Power Query pomoću gumba Iz tablice/raspona kartica Datum (Podaci — iz tablice/raspona).
3. Našu tablicu sortiramo (putem padajućeg popisa filtra u zaglavlju) prema silaznom redoslijedu datuma, tako da su najnovije transakcije na vrhu.
4… U kartici transformacija izabrati tim Grupa po (Transformacija — Grupiraj po) i postavite grupiranje po kupcima, a kao agregirajuću funkciju odaberite opciju Sve linije (Svi redovi). Novi stupac možete nazvati kako god želite – na primjer Detaljnije.
Nakon grupiranja, dobit ćemo popis jedinstvenih imena naših klijenata iu stupcu Detaljnije – tablice sa svim transakcijama svakog od njih, gdje će prvi red biti posljednja transakcija, što nam treba:
5. Dodajte novi izračunati stupac pomoću gumba Prilagođeni stupac kartica Dodajte stupac (Dodaj stupac — Dodaj prilagođeni stupac)i unesite sljedeću formulu:
Ovdje Detaljnije – ovo je stupac iz kojeg preuzimamo tablice po kupcima, i 0 {} je broj retka koji želimo izdvojiti (numeriranje redaka u Power Queryju počinje od nule). Dobivamo stupac sa zapisima (Zapis), gdje je svaki unos prvi red iz svake tablice:
Preostaje proširiti sadržaj svih zapisa tipkom s dvostrukim strelicama u zaglavlju stupca Zadnji dogovor odabir željenih stupaca:
... a zatim izbrišite stupac koji više nije potreban Detaljnije desnim klikom na njegov naslov – Ukloni stupce (Ukloni stupce).
Nakon učitavanja rezultata na list kroz Početna — Zatvori i utovari — Zatvori i utovari (Početna — Zatvori i učitaj — Zatvori i učitaj u…) dobit ćemo tako lijepu tablicu s popisom nedavnih transakcija, kakvu smo željeli:
Kada mijenjate izvorne podatke, ne smijete zaboraviti ažurirati rezultate desnim klikom na njih – naredba Ažurirajte i spremite (Osvježiti) ili tipkovni prečac Ctrl+drugo+F5.
- Funkcija LOOKUP potomak je funkcije VLOOKUP
- Kako koristiti nove funkcije dinamičkog polja SORT, FILTER i UNIC
- Pronalaženje posljednje neprazne ćelije u retku ili stupcu pomoću funkcije LOOKUP