Wyszukiwanie ostatniej pasującej wartości

Większość funkcji wyszukiwania zwraca pierwsze znalezione dopasowanie. Czasami potrzebne jest jednak ostatnie: najnowsza cena produktu, ostatnia aktualizacja statusu albo końcowy wpis klienta.

Gdy lista z czasem się powiększa i ten sam klucz pojawia się wiele razy, najniższy wiersz zwykle zawiera najświeższe dane. Standardowe funkcje VLOOKUP lub MATCH z dokładnym dopasowaniem uparcie zwrócą jednak górny wiersz.

W tej lekcji pokazano kilka niezawodnych sposobów pobierania ostatniej pasującej wartości.

MATCH(value, range, 0) przeszukuje zakres od góry do dołu i zatrzymuje się przy pierwszym dokładnym dopasowaniu. Jeśli "Apple" występuje w wierszach 2, 5 i 9, MATCH zwróci 2.

To idealne rozwiązanie, gdy klucze są unikatowe, ale pomija nowsze wiersze. Aby dotrzeć do ostatniego wystąpienia, potrzebujemy techniki przeszukującej dane od dołu albo zwracającej pozycję ostatniego dopasowania.

Jeśli korzystasz z nowoczesnej wersji Excel lub Google Sheets, funkcja XLOOKUP znacznie ułatwia zadanie. Jej piąty i szósty argument sterują trybem dopasowania oraz kierunkiem wyszukiwania.

Przekaż -1 jako argument trybu wyszukiwania, aby przeszukiwać dane od końca do początku. XLOOKUP zwróci wtedy wartość powiązaną z najniższym pasującym kluczem.

W tym przykładzie funkcja wyszukuje produkt z G1 w zakresie A2:A10 i zwraca odpowiadającą mu cenę z B2:B10, rozpoczynając wyszukiwanie od dołu.

W starszych arkuszach często stosuje się znany trik wykorzystujący LOOKUP, liczbę 2 i sprytne dzielenie przez warunek.

Wyrażenie 1/(A2:A10=G1) zwraca 1 dla pasujących wierszy, a dla pozostałych powoduje błąd dzielenia. LOOKUP, szukając wartości 2 (większej niż każda występująca wartość), pomija błędy i zatrzymuje się na ostatniej poprawnej wartości 1, zwracając odpowiadającą jej wartość z B2:B10.

Prześledźmy działanie wyrażenia 1/(A2:A10=G1):

• Wiersze, w których klucz pasuje, dają 1/TRUE = 1.
• Wiersze, które nie pasują, dają 1/FALSE = błąd #DIV/0!.

LOOKUP ignoruje błędy i gdy nie może znaleźć szukanej wartości (2), zwraca wynik odpowiadający ostatniemu wpisowi bez błędu. Ponieważ wszystkie dopasowania mają wartość 1, wygrywa ostatnia jedynka, więc otrzymujemy wartość z ostatniego pasującego wiersza.

Można również pozostać przy rodzinie INDEX-MATCH. Pomysł polega na znalezieniu pozycji ostatniego dopasowania, a następnie przekazaniu jej do INDEX.

Użyj tego samego triku z dzieleniem wewnątrz MATCH: wyszukaj 2 w 1/(A2:A10=G1), aby uzyskać pozycję wiersza ostatniego dopasowania. Następnie przekaż tę pozycję do INDEX działającego na kolumnie wynikowej.

Gdy trzeci argument MATCH zostanie pominięty, domyślnie przyjmuje wartość 1, czyli przybliżone dopasowanie w rosnąco uporządkowanych danych. MATCH szuka wtedy największej wartości mniejszej lub równej 2.

Tablica 1/(A2:A10=G1) zawiera tylko jedynki i błędy. Największą wartością nieprzekraczającą 2 jest 1, a MATCH zwraca pozycję ostatniej takiej jedynki. Ta pozycja dokładnie odpowiada ostatniemu pasującemu wierszowi.

Załóżmy, że A2:A10 zawiera rejestrowane w czasie statusy zamówienia "Order-7", a B2:B10 zawiera tekst statusu. "Order-7" występuje w wierszach 3, 6 i 9.

• Tablica dopasowań oznacza wiersze 3, 6 i 9 jedynką, a pozostałe jako błędy.
• MATCH(2, ...) zwraca 9 jako pozycję, licząc od początku zakresu, czyli ostatnie dopasowanie.
• INDEX zwraca status z tego końcowego wiersza, czyli najnowszy status.

Jakiego podejścia użyć?

• XLOOKUP z -1: najczystsze i najbardziej czytelne rozwiązanie, jeśli aplikacja je obsługuje.
• LOOKUP(2, 1/...): działa niemal wszędzie i nie wymaga konkretnej wersji.
• INDEX-MATCH(2, 1/...): przydatne, gdy potrzebujesz również pozycji albo chcesz zwrócić wartość z innej kolumny.

Wszystkie trzy sposoby dają tę samą odpowiedź. Wybierz metodę odpowiednią do używanych narzędzi i oczekiwanej czytelności formuły.

Zwróć uwagę na następujące kwestie:

• Niezgodne rozmiary zakresów: zakres warunku i zakres wynikowy muszą mieć tę samą wysokość, inaczej wiersze przestaną się zgadzać.
• Ukryte duplikaty: spacje na końcu mogą sprawić, że "Apple " będzie różnić się od "Apple". Najpierw oczyść tekst za pomocą TRIM.
• Brak dopasowania: trik zwróci błąd, jeśli nic nie pasuje. Użyj IFERROR, aby zapewnić przyjazną wartość zastępczą.

Trik z ostatnim dopasowaniem można połączyć z dwoma warunkami. Pomnóż testy warunków wewnątrz dzielenia, aby tylko wiersze spełniające oba klucze dawały wynik 1.

Na przykład znajdź najnowszą cenę, dla której produkt jest równy G1 i region jest równy G2. Trik LOOKUP(2, ...) nadal wskaże ostatni wiersz spełniający warunki.

Jest to przydatne w dziennikach ze znacznikami czasu, w których ten sam produkt występuje w kilku regionach.

Sprawdź, czy rozumiesz wyszukiwanie ostatniego dopasowania.

Aby zwrócić ostatnią pasującą wartość zamiast pierwszej:

• Użyj XLOOKUP(..., -1), aby w obsługiwanych wersjach wyszukiwać od dołu do góry.
• W dowolnej wersji użyj klasycznego triku LOOKUP(2, 1/(range=key), result).
• Użyj INDEX(result, MATCH(2, 1/(range=key))), gdy potrzebujesz również pozycji.

Pamiętaj, aby zakresy miały ten sam rozmiar, usuwać zbędne spacje i dla bezpieczeństwa opakować formułę w IFERROR.