Preuzmite primjer radne knjige
Ovaj vodič će vas naučiti kako potražiti posljednju vrijednost u stupcu ili retku u Excelu.
Zadnja vrijednost u stupcu
Pomoću funkcije LOOKUP možete pronaći zadnju ćeliju koja nije prazna u stupcu.
| 1 | = POGLEDAJ (2,1/(B: B ""), B: B) |
Prođimo kroz ovu formulu.
Dio formule B: B ”” vraća niz koji sadrži True i False vrijednosti: {FALSE, TRUE, TRUE,…}, testiranje svake ćelije u stupcu B je prazno (FALSE).
| 1 | = LOOKUP (2,1/({FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE;…), B: B) |
Ove Booleove vrijednosti pretvaraju se u 0 ili 1 i koriste se za dijeljenje 1.
| 1 | = LOOKUP (2, {#DIV/0!; 1; 1; 1; 1; 1;#DIV/0!;, B: B) |
Ovo je lookup_vector za funkciju LOOKUP. U našem slučaju, lookup_value je 2, ali najveća vrijednost u lookup_vector je 1, pa će funkcija LOOKUP odgovarati posljednjih 1 u nizu i vratiti odgovarajuću vrijednost u result_vector.
Ako ste sigurni da u stupcu imate samo numeričke vrijednosti, vaši podaci počinju od retka 1, a raspon podataka neprekidno, možete upotrijebiti nešto jednostavniju formulu s funkcijama INDEX i COUNT.
| 1 | = INDEKS (B: B, COUNT (B: B)) |
Funkcija COUNT vraća broj ćelija ispunjenih podacima u kontinuiranom rasponu (4), pa funkcija INDEX daje vrijednost ćelije u ovom odgovarajućem retku (4.).
Da biste izbjegli moguće pogreške kada vaš raspon podataka sadrži mješavinu numeričkih i ne numeričkih vrijednosti, ili čak neke prazne ćelije, možete koristiti funkciju LOOKUP zajedno s funkcijama ISBLANK i NOT.
| 1 | = POGLEDAJ (2,1/(NE (ISBLANK (B: B))), B: B) |
ISBLANK funkcija vraća niz koji sadrži vrijednosti True i False, koje odgovaraju 1 i 0. Funkcija NOT mijenja True (tj. 1) u False i False (tj. 0) u True. Ako obrnemo ovaj rezultirajući niz (kada podijelimo 1 s ovim nizom), dobit ćemo niz rezultata koji opet sadrži #DIV/0! pogreške i 1, koje se mogu koristiti kao niz pretraživanja (lookup_vector) u našoj funkciji LOOKUP. Funkcionalnost funkcije LOOKUP tada je ista kao u našem prvom primjeru: vraća vrijednost vektora rezultata na poziciji posljednje 1 u nizu za pretraživanje.
Kad trebate vratiti broj retka s zadnjim unosom, možete izmijeniti formulu korištenu u našem prvom primjeru zajedno s funkcijom ROW u vašem rezulta_vektoru.
| 1 | = POGLEDAJ (2,1/(B: B ""), RED (B: B)) |
Zadnja vrijednost u retku
Da biste zadnju vrijednost neprazne ćelije u redu ispunili numeričkim podacima, možda ćete htjeti upotrijebiti sličan pristup, ali s različitim funkcijama: funkciju OFFSET zajedno s funkcijama MATCH i MAX.
| 1 | = OFFSET (Referenca, Redovi, Stupci) |
| 1 | = OFFSET (B2,0, MATCH (MAX (B2: XFD2)+1, B2: XFD2,1) -1) |
Pogledajmo kako funkcionira ova formula.
MATCH funkcija
Koristimo funkciju MATCH za "brojanje" koliko je vrijednosti ćelija ispod 1 + najviše od svih vrijednosti u retku 2 počevši od B2.
| 1 | = MATCH (lookup_value, lookup_array, [match_type]) |
| 1 | = MATCH (MAX (B2: XFD2)+1, B2: XFD2,1) |
Lookup_value funkcije MATCH najveća je od svih vrijednosti u retku2 + 1. Budući da ova vrijednost očito ne postoji u retku2 i match_type je postavljen na 1 (manje ili jednako lookup_value), funkcija MATCH vratit će zadnji "provjereni" položaj ćelije u nizu, odnosno broj ćelija ispunjenih podacima u rasponu B2: XFD2 (XFD je posljednji stupac u novijim verzijama Excela).
OFFSET funkcija
Zatim koristimo OFFSET funkciju da dobijemo vrijednost ove ćelije čiji je položaj vratila funkcija MATCH.
| 1 | = OFFSET (B2,0, C4-1) |
