Formuła ogólna
=XLOOKUP(max,dates,results,,-1) // latest match by date
Podsumowanie
Aby uzyskać najnowsze dopasowanie w zestawie danych według daty, możesz użyć XLOOKUP w trybie przybliżonego dopasowania, ustawiając match_mode na -1. W pokazanym przykładzie wzór w G5 skopiowany na dół to:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
gdzie data (C5: C15), pozycja (B5: B15) i cena (D5: D15) to nazwane zakresy.
Wyjaśnienie
XLOOKUP oferuje kilka funkcji, które sprawiają, że jest wyjątkowo dobry do bardziej skomplikowanych wyszukiwań. W tym przykładzie zależy nam na ostatniej cenie towaru według daty. Gdyby dane zostały posortowane rosnąco według daty, byłoby to bardzo proste. Jednak w tym przypadku dane są nieposortowane.
Domyślnie XLOOKUP zwróci pierwsze dopasowanie w zestawie danych. Aby uzyskać ostatnie dopasowanie, możemy ustawić opcjonalny argument tryb_wyszukiwania na -1, aby XLOOKUP przeszukiwał „od ostatniego do pierwszego”. Jednak nie możemy tutaj zastosować tego podejścia, ponieważ nie ma gwarancji, że najnowsza cena przedmiotu pojawi się jako ostatnia.
Zamiast tego możemy ustawić opcjonalny argument match_mode na -1, aby wymusić przybliżone dopasowanie „dokładnego lub następnego najmniejszego” i dostosować wartość wyszukiwania i tablicę wyszukiwania, jak wyjaśniono poniżej. Wzór w G5 skopiowany poniżej to:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
Analizując argumenty jeden po drugim, lookup_value to największa (najnowsza) data w danych:
MAX(date) // get max date value
Lookup_array jest wyprowadzana za pomocą logicznego wyrażenia logicznego:
(item=F5)*date
Porównując każdy element z wartością podaną w F5, „Pas”, otrzymujemy tablicę wartości PRAWDA / FAŁSZ:
(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE)
gdzie wartości PRAWDA reprezentują wpisy dla „pasa”. Ta tablica działa jak filtr. Po pomnożeniu przez wartości z nazwanego zakresu dat , wartości PRAWDA / FAŁSZ są obliczane na 1 i 0:
=(1;0;0;0;0;0;1;0;1;0;0)*date
Rezultatem jest tablica zawierająca tylko zera i daty pasów:
=(43484;0;0;0;0;0;43561;0;43671;0;0)
Uwaga: numery seryjne to ważne daty w programie Excel.
Ta tablica jest dostarczana bezpośrednio do XLOOKUP jako argument lookup_array.
Return_array to nazwana cena przedziałowa (D5: D15)
Nie podano opcjonalnego argumentu not_found.
Match_mode jest ustawiona na -1, dla dokładnego dopasowania lub następnego najmniejszego elementu.
XLOOKUP przegląda tablicę odnośników pod kątem maksymalnej wartości daty. Ponieważ tablica została już przefiltrowana w celu wykluczenia dat niezwiązanych z „Pasem”, XLOOKUP po prostu wyszukuje najlepsze dopasowanie (dokładną datę lub następną najmniejszą datę), które odpowiada najnowszej dacie.
Ostatecznym wynikiem jest cena powiązana z najpóźniejszym terminem. Formuła będzie nadal działać, gdy dane zostaną posortowane w dowolnej kolejności.
