Generička formula
(=MAX(IF(criteria,ROW(rng)-MIN(ROW(rng))+1)))
Sažetak
Da biste dobili položaj zadnjeg podudaranja (tj. Zadnjeg pojavljivanja) vrijednosti pretraživanja, možete upotrijebiti formulu niza koja se temelji na funkcijama IF, ROW, INDEX, MATCH i MAX. U prikazanom primjeru formula u H6 je:
(=MAX(IF(names=H5,ROW(names)-MIN(ROW(names))+1)))
Gdje je "imena" imenovani raspon C4: C11.
Napomena: ovo je formula niza i mora se unijeti sa control + shift + enter.
Obrazloženje
Suština ove formule je da gradimo popis brojeva redaka za zadani raspon, podudarajući se na vrijednosti, a zatim koristimo funkciju MAX da bismo dobili najveći broj retka, koji odgovara posljednjoj podudarnoj vrijednosti. Imenovani raspon "imena" koristimo samo radi praktičnosti.
Radeći iznutra prema van, ovaj dio formule generirat će relativni skup brojeva redaka:
ROW(names)-MIN(ROW(names))+1
Rezultat gornjeg izraza je niz brojeva poput ovog:
(1;2;3;4;5;6;7;8)
Primijetimo da dobivamo 8 brojeva, koji odgovaraju 8 redaka u tablici. Pogledajte ovu stranicu za detalje o načinu funkcioniranja ovog dijela formule.
U svrhu ove formule, želimo samo brojeve redaka za podudaranje vrijednosti, pa koristimo funkciju IF za filtriranje vrijednosti na sljedeći način:
IF(names=H5,ROW(names)-MIN(ROW(names))+1)
Rezultat je niz koji izgleda ovako:
(1;FALSE;FALSE;4;FALSE;FALSE;7;FALSE)
Imajte na umu da ovaj niz još uvijek sadrži osam predmeta. Međutim, preživjeli su samo brojevi redaka u kojima je vrijednost u imenovanom rasponu "imena" jednaka "amy" (tj. 1, 4, 7). Sve ostale stavke u nizu su FALSE, jer nisu uspjele na logičkom testu u funkciji IF.
Konačno, funkcija IF isporučuje ovaj niz funkciji MAX. MAX vraća najvišu vrijednost u polju, broj 7, koji odgovara zadnjem broju reda gdje je ime "amy". Jednom kada znamo posljednji odgovarajući broj retka, pomoću INDEX-a možemo dohvatiti vrijednost na tom položaju.
Pretposljednji itd.
Da biste dobili drugo do posljednje mjesto, treće do posljednje, itd. Možete prebaciti s funkcije MIN na funkciju LARGE ovako:
(=LARGE(IF(criteria,ROW(rng)-MIN(ROW(rng))+1),k))
gdje k predstavlja "n-ti najveći". Na primjer, da biste dobili posljednje podudaranje u gornjem primjeru, možete upotrijebiti:
(=LARGE(IF(names=H5,ROW(names)-MIN(ROW(names))+1),2))
Kao i prije, ovo je formula niza i mora se unijeti sa control + shift + enter.
