Splošna formula
(=MAX(IF(criteria,ROW(rng)-MIN(ROW(rng))+1)))
Povzetek
Če želite dobiti položaj zadnjega ujemanja (tj. Zadnjega pojava) iskalne vrednosti, lahko uporabite matrično formulo, ki temelji na funkcijah IF, ROW, INDEX, MATCH in MAX. V prikazanem primeru je formula v H6:
(=MAX(IF(names=H5,ROW(names)-MIN(ROW(names))+1)))
Kjer je "imena" imenovano območje C4: C11.
Opomba: to je matrična formula, ki jo je treba vnesti s tipko control + shift + enter.
Pojasnilo
Bistvo te formule je, da sestavimo seznam številk vrstic za dani obseg, ki se ujema z vrednostjo, nato pa s funkcijo MAX dobimo največjo številko vrstice, ki ustreza zadnji ujemajoči se vrednosti. Imenovani obseg "imen" uporabljamo samo za udobje.
Ta del formule bo deloval od znotraj navzven in ustvaril sorazmerni niz številk vrstic:
ROW(names)-MIN(ROW(names))+1
Rezultat zgornjega izraza je niz številk, kot je ta:
(1;2;3;4;5;6;7;8)
Opazimo, da dobimo 8 številk, ki ustrezajo 8 vrsticam v tabeli. Za podrobnosti o tem, kako deluje ta del formule, glejte to stran.
Za namen te formule želimo samo številke vrstic za ujemajoče se vrednosti, zato uporabljamo funkcijo IF za filtriranje vrednosti tako:
IF(names=H5,ROW(names)-MIN(ROW(names))+1)
Rezultat je matrika, ki je videti takole:
(1;FALSE;FALSE;4;FALSE;FALSE;7;FALSE)
Upoštevajte, da ta matrica še vedno vsebuje osem elementov. Vendar so se ohranile le številke vrstic, pri katerih je vrednost v imenovanem obsegu "imena" enaka "amy" (tj. 1, 4, 7). Vsi drugi elementi v matriki so FALSE, ker niso uspeli na logičnem preizkusu v funkciji IF.
Na koncu funkcija IF poda to matriko v funkcijo MAX. MAX vrne najvišjo vrednost v polju, številko 7, ki ustreza zadnji številki vrstice, kjer je ime "amy". Ko poznamo zadnjo ujemajočo se številko vrstice, lahko s pomočjo INDEX pridobimo vrednost na tem mestu.
Predzadnji itd.
Če želite dobiti drugo do zadnjo pozicijo, tretjo do zadnjo itd., Lahko preklopite s funkcije MIN na funkcijo LARGE, kot je ta:
(=LARGE(IF(criteria,ROW(rng)-MIN(ROW(rng))+1),k))
kjer k predstavlja "n-to največjo". Če želite na primer dobiti zadnje do zadnjega ujemanja v zgornjem primeru, lahko uporabite:
(=LARGE(IF(names=H5,ROW(names)-MIN(ROW(names))+1),2))
Kot prej je to tudi matrična formula, ki jo je treba vnesti s tipko control + shift + enter.
