Wyszukiwanie od końca w Excelu

W pierwszej kolejności będziemy pracować z danymi posortowanymi po dacie (Rysunek 1), aby znaleźć odpowiedź na pytanie, co ostatnio kupił u nas klient.

X.WYSZUKAJ

Jako pierwszą zaprezentujemy formułę z funkcją X.WYSZUKAJ (dostępną od 2021 r.), ponieważ ma ona wbudowany argument decydujący, czy wyszukujemy od początku czy od końca danych. Formuła jest następująca:

=X.WYSZUKAJ(G2;$C$2:$C$21;$B$2:$B$21;;;-1)

Pokazuje ją Rysunek 2.

Pierwszym argumentem funkcji X.WYSZUKAJ jest wartość, której szukamy, czyli nazwa Kupca (G2). Drugi argument to kolumna, w której szukamy wartości podanej w pierwszym argumencie, czyli w tym przykładzie przeszukujemy kolumnę C, w której znajdują się imiona kupców (zakres $C$2:$C$21). Trzeci argument to kolumna, z której funkcja ma zwrócić wartość na podstawie miejsca, w którym funkcja X.WYSZUKAJ znajdzie szukaną wartość (pierwszy argument) w przeszukiwanej kolumnie (drugi argument). Czwarty i piąty argument nie jest dla nas istotny w tym przykładzie, dlatego całkiem je pomijamy. Oznacza to, że w funkcji piszemy trzy średniki tuż po sobie. Szósty argument jest tym, który najbardziej nas interesuje, bo w nim możemy wybrać opcję wyszukiwania od końca, wpisując w niego wartość minus 1.

Dokładne właściwości szóstego argumentu pokazuje Rysunek 3.

WYSZUKAJ przed 2021

Jako kolejne rozwiązanie omówimy formułę, której możemy użyć nawet w Excelu 2007. Wykorzystuje ona funkcję WYSZUKAJ, która zawsze dokonuje wyszukiwania na zasadzie przybliżonej, ignoruje błędy, a dodatkowo obsługuje tablice. Wszystkie te jej właściwości ułatwią nam wyszukiwanie od końca. Tym razem formuła przyjmie następującą postać:

=WYSZUKAJ(2;1/($C$2:$C$21=G2);$B$2:$B$21)

Pokazuje ją Rysunek 4.

Pierwszym argumentem tej funkcji jest wyszukiwana wartość. W tym przykładzie szukamy liczby 2. Nigdy nie wystąpi ona w stworzonej przez nas w drugim argumencie w przeszukiwanym wektorze, ale istotne jest, że 2 będzie liczbą większą od dowolnej liczby występującej w przeszukiwanym wektorze. Oznacza to przy wyszukiwaniu przybliżonym odnalezienie miejsca ostatniej liczby. W drugi argument wpisujemy działanie 1/($C$2:$C$21=G2), czyli dzielimy liczbę 1 przez test logiczny, dokładnie przyrównanie wartości z kolumny z imionami kupców do imienia konkretnego kupca (np.: komórka G2). Ten test logiczny zwróci ciąg wartości logicznych PRAWDA i FAŁSZ. Przy dzieleniu i innych operacjach matematycznych wartość logiczna PRAWDA zostanie zamieniona na 1, a FAŁSZ na 0. Oznacza to, że stworzyliśmy ciąg z liczbami 1 oraz z błędami dzielenia przez zero (#DZIEL/0!). Ponieważ szukamy w tym wektorze liczby 2, a funkcja WYSZUKAJ ignoruje błędy i szuka na zasadzie przybliżonej, oznacza to, że zostanie znalezione miejsce ostatniej jedynki. Na tej podstawie funkcja WYSZUKAJ zwróci powiązaną wartość z wektora wynikowego ($B$2:$B$21).

Zwracanie kilku wartości

W poprzednich przykładach wyszukiwaliśmy zawsze pojedynczej ostatniej wartości (nazwy produktu). Czasami jednak chcemy wyszukać wartości z kilku wierszy. Jeśli chcemy zwrócić wartość z sąsiadujących kolumn, możemy łatwo zmodyfikować formułę z funkcją X.WYSZUKAJ np.:

=X.WYSZUKAJ(G2;$C$2:$C$21;$A$2:$B$21;;;-1)

Niestety, nie możemy w ten sposób zmodyfikować formuły z funkcją WYSZUKAJ, co oznacza, że dla każdej zwracanej wartości musimy napisać osobną formułę:

=WYSZUKAJ(2;1/($C$2:$C$21=G2);$B$2:$B$21)

=WYSZUKAJ(2;1/($C$2:$C$21=G2);$A$2:$A$21)

WYCINEK, czyli Excel 365