Trikovi u Excelu 116. deo – Kako doći do poslednjeg unosa u koloni, ako se kolone razlikuju u broju redova?

Kako prikazati poslednji unos.

PROBLEM KORISNIKA: Jedan moj kolega je uneo neke podatke. Za svaku grupu, podaci polaze od 5. reda i idu naniže izvestan broj redova. U svakoj koloni ima različit broj ćelija s podacima. Treba da dođem do poslednjeg unosa u svakoj koloni (Slika 1).

REŠENJE: Ima više rešenja za ovaj problem. Mogli biste da nezgodnu funkciju OFFSET kombinujete s funkcijom COUNT, ali ću vam pokazati kako da taj problem rešite pomoću verzije funkcije VLOOKUP s aproksimativnim MATCH parametrom.

    Na Slikama 2 i 3 prikazani su primeri korišćenja verzije funkcije VLOOKUP s aproksimativnim MATCH parametrom da bi se pronašla stopa provizije. Tabela ima unose kao što su1000, 5000, 10000 i 20000. Kada je neko imao prodaju od 12.345 $, funkcija VLOOKUP bi pronašla stopu provizije za nivo od 10.000 $ jer je taj nivo najbliži iznosu od 12.345 $ koji ste tražili.

    Možete i da iskoristite “rupu” u Excelu. Recimo da tražite broj koji je veći od bilo koje vrednosti u tabeli. Kada se to dogodi, Excel će prikazati poslednju unetu stavku u tabeli. Pošto naučnici, računovođe i službenici Poreske uprave koji redovno koriste aproksimativnu verziju funkcije VLOOKUP uvek sortiraju podatke po rastućem redosledu, njima koristi prikazivanje poslednje unete stavke u tabeli.

    U ovom slučaju podaci nisu sortirani niti treba da budu. Međutim, ukoliko zatražite da funkcija VLOOKUP potraži zaista veliki broj, ona će automatski da prikaže poslednju unetu stavku u koloni!

    Neki bi predložili da upotrebite:

            9.99999999999999E+307

kao vrednost koja se traži. To je najveći mogući broj u Excel-u. Ipak, umesto da unesete sve ove znakove, možete jednostavno upotrebiti broj koji je veći no što bi iko očekivao. Na primer, ako radite za kompaniju koja godišnje ima prihod od 1 milion $, nema šanse da bi prodaja za jedan dan ikada premašila 100.000 $. Slobodno možete da tražite broj 99999.

    Kao na Slici 4, držite taster 9 jednu sekundu pa potražite 9,9 miliona. Nema veze šta tačno tražite, dokle god je to veće od bilo kog mogućeg broja na listi. Upotrebite funkciju =VLOOKUP(9999999,B5:B20,1,TRUE).

    Ova funkcija prikazaće poslednju numeričku vrednost.

    Ovo je stvarno sjajan način korišćenja retke verzije formule VLOOKUP. Kao što možete videti u koloni G, prazne ćelije ne zbunjuju formulu. Ona će prikazati samo numeričke vrednosti pa je ignorisan pogrešan unos ZZZ u ćeliji H8 kao i greška #N/A u ćeliji F10.

    Ako su unosi u koloni tekstualni, tada biste tražili neki tekst koji bi po abecedi dolazio iza svakog teksta koji bi se mogao očekivati. Na primer, potražite “ZZZZZZ” (Slika 5).

    Kolona H ilustruje problem s ovim metodom. Ako vrednosti mogu da sadrže tekst ili broj, formula VLOOKUP neće raditi.

    Šta ako se kolone prebace u redove pa treba da dođete do poslednje vrednosti iz svakog reda? Tada umesto funkcije VLOOKUP koristite funkciju HLOOKUP (Slika 6).

JOŠ POJEDINOSTI: Ni kod jedne od ovih formula ne morate na kraju da stavite parametar ,TRUE. Ako izostavite taj četvrti parametar, Excel će podrazumevati da mislite na TRUE.

Međutim, pošto 99,9% funkcija VLOOKUP na svetu koristi parametar FALSE, vi stavite TRUE kao podsetnik da se s tom formulom dešava nešto neuobičajeno.