Jak uzyskać WYSZUKAJ.PIONOWO w celu zwrócenia * ostatniego * dopasowania?

Jestem przyzwyczajony do pracy z VLOOKUP, ale tym razem mam wyzwanie. Nie chcę pierwszej pasującej wartości, ale ostatniej. W jaki sposób? (Pracuję z LibreOffice Calc, ale rozwiązanie MS Excel powinno być równie przydatne.)

Powodem jest to, że mam dwie kolumny tekstowe z tysiącami wierszy, powiedzmy, że jedna to lista odbiorców transakcji (Amazon, Ebay, pracodawca, sklep spożywczy itp.), A druga to lista kategorii wydatków (płace, podatki, gospodarstwo domowe, czynsz itp.). Niektóre transakcje nie mają tej samej kategorii wydatków za każdym razem i chcę skorzystać z ostatnio używanej. Zauważ, że lista nie jest sortowana według żadnej kolumny (w rzeczywistości według daty) i nie chcę zmieniać kolejności sortowania.

To, co mam (oprócz obsługi błędów), to zwykła formuła „pierwszego dopasowania”:

=VLOOKUP( 
[payee field] , [payee+category range] , [index of category column] , 
0 )

Widziałem takie rozwiązania , ale dostaję #DIV/0!błędy:

=LOOKUP(2 , 1/( [payee range] = [search value] ) , [category range] )

Rozwiązaniem może być dowolna formuła, niekoniecznie WYSZUKAJ.PIONOWO. Mogę również zamieniać kolumny odbiorcy / kategorii. Poprostu brak zmian w kolumnie sortowania.

Punkty bonusowe za rozwiązanie, które wybiera najczęstszą wartość zamiast ostatniej!

Możesz użyć formuły tablicowej, aby uzyskać dane z ostatniego pasującego rekordu.

=INDEX(IF($A$1:$A$20="c",$B$1:$B$20),MAX(IF($A$1:$A$20="c",ROW($A$1:$A$20))))

Wprowadź formułę za pomocą Ctrl+ Shift+ Enter.

Działa to podobnie jak INDEX/ MATCHkonstrukcja a VLOOKUP, ale z użyciem warunku MAXzamiast MATCH.

Zauważ, że zakłada to, że twoja tabela zaczyna się w pierwszym rzędzie. Jeśli twoje dane zaczynają się w innym wierszu, musisz dostosować ROW(...)część, odejmując różnicę między górnym wierszem a 1.

(Odpowiedź tutaj jako osobne pytanie dla posortowanych danych).

Jeśli dane były sortowane, można użyć VLOOKUPz range_lookupargumentem TRUE(lub jest pominięty, ponieważ jest to ustawienie domyślne), która jest oficjalnie opisany dla programu Excel jako „szukaj przybliżonej meczu”.

Innymi słowy, dla posortowanych danych:

• ustawienie ostatniego argumentu tak, aby FALSEzwracał pierwszą wartość, oraz
• ustawienie ostatniego argumentu w celu TRUEzwrócenia ostatniej wartości.

Jest to w dużej mierze nieudokumentowane i niejasne, ale datuje się na VisiCalc (1979), a dziś ma co najmniej w Microsoft Excel, LibreOffice Calc i Arkuszach Google. Jest to ostatecznie spowodowane początkową implementacją LOOKUPw VisiCalc (i stamtąd VLOOKUPi HLOOKUP), kiedy nie było czwartego parametru. Wartość znajduje się w wyszukiwaniu binarnym , z wykorzystaniem włączonej lewej granicy i wyłącznej prawej granicy (wspólna i elegancka implementacja), co powoduje takie zachowanie.

Technicznie oznacza to, że wyszukiwanie rozpoczyna się od przedziału kandydującego [0, n), gdzie njest długość tablicy, a warunkiem niezmiennym dla pętli jest to, że A[imin] <= key && key < A[imax](lewa granica to <= cel, prawa granica, która zaczyna się po końcu, to: > cel; w celu sprawdzenia poprawności albo sprawdź wartości w punktach końcowych przed, albo sprawdź wynik po), a następnie dzielenie na sekcje i wybranie dowolnej strony zachowuje ten niezmiennik: wykluczając jedną ze stron, dopóki nie dojdziesz do przedziału z 1 terminem [k, k+1), a algorytm następnie zwraca k. To nie musi być dokładne dopasowanie (!): To tylko najbliższe dopasowanie od dołu. W przypadku zduplikowanych dopasowań powoduje to zwrócenie ostatniego dopasowania, ponieważ wymaga, aby następna wartość była większaniż klucz (lub koniec tablicy). W przypadku duplikatów trzeba pewne zachowania, a to jest rozsądne i łatwe do wdrożenia.

To zachowanie jest wyraźnie określone w starym artykule z bazy wiedzy Microsoft Knowledge Base (wyróżnienie dodane): „XL: Jak zwrócić pierwszy lub ostatni wynik w tablicy” ( Q214069 ):

Możesz użyć funkcji WYSZUKAJ (), aby wyszukać wartość w tablicy posortowanych danych i zwrócić odpowiednią wartość zawartą w tej pozycji w innej tablicy. Jeśli wartość wyszukiwania jest powtarzana w obrębie tablicy, zwraca ostatnie napotkane dopasowanie . To zachowanie dotyczy funkcji WYSZUKAJ.PIONOWO (), WYSZUKAJ.PIONOWO () i WYSZUKAJ ().

Poniżej przedstawiono oficjalną dokumentację niektórych arkuszy kalkulacyjnych; nie podano też zachowania „ostatniego dopasowania”, ale jest to sugerowane w dokumentacji Arkuszy Google:

• Microsoft Excel

  PRAWDA zakłada, że ​​pierwsza kolumna w tabeli jest posortowana numerycznie lub alfabetycznie, a następnie wyszuka najbliższą wartość .

• Arkusze Google :

  Jeśli is_sortedjest TRUElub zostało pominięte, zwracane jest najbliższe dopasowanie ( mniejsze lub równe kluczowi wyszukiwania)

Jeśli wartości w tablicy wyszukiwania są sekwencyjne (tzn. Szukasz największej wartości, takiej jak najnowsza data), nie musisz nawet używać funkcji POŚREDNIE. Wypróbuj ten prosty kod:

=MAX(IF($A$1:$A$20="c",$B$1:$B$20,)

Ponownie wprowadź formułę za pomocą CTRL + SHIFT + ENTER

Próbowałem najczęstszej wartości. Nie jestem pewien, czy zadziała w libreOffice, ale wydaje się, że działa w programie Excel

= INDEKS (2 $ B $: 9 $ B $, MECZ (MAKS. (- (2 $ A $: 9 $ A $ = D2) * LICZNIKI (2 $ B $: 9 $ B $, $ 2 B $: 9 $ B $, 2 $ A : 9 $ A $, D2)), - (2 $ A $: 9 $ A = D2) * LICZBY (2 $ B $: 9 $ B $, 2 $ B $: 9 $ B $, 2 USD: 9 $ A, D2 ), 0))

Kolumna A to odbiorca, kolumna B to kategoria, D2 to odbiorca, według którego chcesz filtrować. Nie jestem pewien, dlaczego dodaje dodatkowe łamanie linii w powyższej funkcji.

Moja funkcja znajdowania ostatniej komórki wyglądałaby następująco:

= POŚREDNIE („B” i MAKS (- (2 $ A $: 9 $ A $ = D2) * WIERSZ (2 $ A $: 9 $ A $)))

Pośrednie pozwala mi określić kolumnę, którą chcę zwrócić, i znaleźć wiersz bezpośrednio (więc nie muszę odejmować liczby wierszy nagłówka.

Obie te funkcje należy wprowadzić za pomocą Ctrl + Shift + Enter

=LOOKUP([payee field] , [payee range] , [category range])

Otrzymasz ostatnią wartość

Czy dostaję punkty bonusowe za spóźnienie 3 lata?

Wystąpiły #DIV/0!błędy, ponieważ powinieneś raczej napisać formułę:

=LOOKUP(2;IF(([payee range] = [search value]);1;"");[category range])

to zadziała i znajdzie ostatni mecz.

([payee range] = [search value]) : macierz logiczna TRUE / FALSE

IF(([payee range] = [search value]);1;"") : pseudo-boolean matrix 1 / ""

=LOOKUP(2; {pseudo-boolean matrix 1/""} );[category range]): zawsze zwracaj ostatnią 1pozycję