Czasami potrzebujemy znaleźć przedostatnią wartość na liście, a nawet przedostatnią wartość pod warunkiem. Rozpatrzmy to na prostym zestawie biegaczy i ich czasów, tylko nie chodzi nam o ich przedostatni czas w kontekście wartości, ale miejsca na naszej liście.

Widzowie 115 - Jak znaleźć przedostatni wiersz po warunku 01

W pierwszej kolejności będziemy musieli sprawdzać warunek – wykonać test logiczny, a jak wykonujemy test logiczny, to niemal nieodzowna jest funkcja JEŻELI. Właśnie w niej napiszemy nasz prosty warunek, czy pozycja na liście jest równa wybranemu przez nas biegaczowi.

=JEŻELI(A2:A21=E1

Widzowie 115 - Jak znaleźć przedostatni wiersz po warunku 02

Jeśli warunek jest spełniony to chcemy uzyskać numer wiersza danych (funkcja WIERSZ), gdzie ten warunek został spełniony. W przeciwnej sytuacji chcemy mieć zwracaną wartość FAŁSZ – wystarczy, że nie wypełnimy trzeciego argumentu funkcji JEŻELI:

=JEŻELI(A2:A21=E1;WIERSZ(A2:A21)-WIERSZ(A2)+1)

Widzowie 115 - Jak znaleźć przedostatni wiersz po warunku 03

Jeśli podejrzymy wynik formuły (klawiszem F9) to zobaczymy tablicę wartości FAŁSZ i numerów wiersza danych tam, gdzie warunek został spełniony.

{FAŁSZ;FAŁSZ;FAŁSZ;4;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ}

Nas interesuje druga maksymalna wartość, czyli potrzebujemy skorzystać z funkcji MAX.K, gdzie jej drugim argumentem będzie wartość 2.

=MAX.K(JEŻELI(A2:A21=E1;WIERSZ(A2:A21)-WIERSZ(A2)+1);2)

Widzowie 115 - Jak znaleźć przedostatni wiersz po warunku 04

Musimy tylko pamiętać zatwierdzać formułę kombinacją klawiszy Ctrl + Shift + Enter, ponieważ jest to formuła tablicowa. Uzyskujemy wtedy numer wiersza, gdzie jest interesująca nas wartość, a my potrzebujemy samej wartości. Wystarczy, że skorzystamy z funkcji INDEKS, której podamy kolumnę, z której chcemy poznać wartość.

=INDEKS($B$2:$B$21;MAX.K(JEŻELI($A$2:$A$21=E1;WIERSZ($A$2:$A$21)-1);2))

Widzowie 115 - Jak znaleźć przedostatni wiersz po warunku 05

Ciągle musimy pamiętać zatwierdzać formułę Ctrl + Shift + Enter. Analogicznie możesz budować formuły pozwalające Ci odszukać kolejne wystąpienia warunku zmieniając funkcję MAX.K na MIN.K

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP