Formuła ogólna
(=MAX(IF(criteria,ROW(rng)-MIN(ROW(rng))+1)))
Podsumowanie
Aby uzyskać pozycję ostatniego dopasowania (tj. Ostatniego wystąpienia) wartości wyszukiwania, można użyć formuły tablicowej opartej na funkcjach JEŻELI, WIERSZ, INDEKS, PODAJ.POZYCJĘ i MAKS. W pokazanym przykładzie formuła w H6 to:
(=MAX(IF(names=H5,ROW(names)-MIN(ROW(names))+1)))
Gdzie „nazwy” to nazwany zakres C4: C11.
Uwaga: to jest formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter.
Wyjaśnienie
Istota tej formuły polega na tym, że tworzymy listę numerów wierszy dla danego zakresu, dopasowując wartość, a następnie używamy funkcji MAX, aby uzyskać największy numer wiersza, który odpowiada ostatniej pasującej wartości. Używamy nazwanego zakresu „nazw” wyłącznie dla wygody.
Działając od wewnątrz, ta część formuły wygeneruje względny zestaw numerów wierszy:
ROW(names)-MIN(ROW(names))+1
Wynikiem powyższego wyrażenia jest tablica liczb podobna do tej:
(1;2;3;4;5;6;7;8)
Zauważ, że otrzymujemy 8 liczb, które odpowiadają 8 wierszom w tabeli. Zobacz tę stronę, aby uzyskać szczegółowe informacje na temat działania tej części formuły.
Na potrzeby tej formuły chcemy tylko numerów wierszy dla pasujących wartości, więc używamy funkcji JEŻELI do filtrowania wartości w następujący sposób:
IF(names=H5,ROW(names)-MIN(ROW(names))+1)
W rezultacie powstaje tablica, która wygląda następująco:
(1;FALSE;FALSE;4;FALSE;FALSE;7;FALSE)
Zauważ, że ta tablica nadal zawiera osiem elementów. Jednak przetrwały tylko numery wierszy, w których wartość w nazwanym zakresie „names” jest równa „amy” (tj. 1, 4, 7). Wszystkie inne elementy w tablicy mają wartość FALSE, ponieważ nie przeszły testu logicznego w funkcji JEŻELI.
Wreszcie funkcja JEŻELI dostarcza tę tablicę do funkcji MAX. MAX zwraca najwyższą wartość w tablicy, liczbę 7, która odpowiada numerowi ostatniego wiersza, w którym nazwa to „ania”. Gdy znamy ostatni pasujący numer wiersza, możemy użyć INDEKSU do pobrania wartości na tej pozycji.
Przedostatni itp.
Aby uzyskać pozycję od drugiej do ostatniej, od trzeciej do ostatniej itd., Możesz przełączyć się z funkcji MIN na funkcję LARGE w następujący sposób:
(=LARGE(IF(criteria,ROW(rng)-MIN(ROW(rng))+1),k))
gdzie k oznacza „n-ty największy”. Na przykład, aby uzyskać przedostatnie dopasowanie w powyższym przykładzie, możesz użyć:
(=LARGE(IF(names=H5,ROW(names)-MIN(ROW(names))+1),2))
Jak poprzednio, jest to formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter.
