Dzięki funkcji WYSZUKAJ.PIONOWO zawsze otrzymujemy pierwsze dopasowanie. To samo dzieje się z funkcją INDEX MATCH. Jak więc VLOOKUP drugi mecz lub trzeci lub n-ty? W tym artykule dowiemy się, jak uzyskać N-te wystąpienie wartości w zakresie.
Formuła ogólna
{=MAŁY(JEŻELI(zakres=wartość,WIERSZ(zasięg)-WIERSZ(pierwsza_komórka_w_zakresie)+1),n)}
Uwaga: to jest formuła tablicowa. Musisz go wprowadzić za pomocą CTRL + SHIFT + ENTER.
Zasięg: zakres, w którym chcesz wyszukać nth pozycja wartość.
Wartość: wartość której szukasz nth pozycja wzasięg.
First_cell_in_range: pierwsza komórka wzasięg. Jeśli zakres to A2:A10, to pierwsza komórka w zakresie to A2.
n: ten występowanie Liczba wartości.
Zobaczmy przykład, aby wszystko było jasne.
Przykład: Znajdź drugie dopasowanie w programie Excel
Więc tutaj mam listę nazwisk w zakresie Excela A2:A10. Nazwałem ten zakres jako nazwy. Teraz chcę uzyskać pozycję drugiego wystąpienia „Rony” w nazwy.
Na powyższym obrazku widzimy, że znajduje się na 7 pozycji w zakresie A2:A10 (nazwy). Teraz musimy ustalić jego pozycję za pomocą formuły Excela.
Zastosuj powyższą ogólną formułę w C2, aby wyszukać drugie wystąpienie Rony na liście.
{=MAŁY(JEŻELI(imiona="Rony" ,WIERSZ(nazwy)-WIERSZ(A2)+1),2)}
Wpisz go, naciskając CTRL + SHIFT + ENTER…
I mamy odpowiedź. Pokazuje 7, co jest poprawne. Jeśli zmienisz wartość n na 3, da to 8. Jeśli zmienisz wartość n większą niż wystąpienie wartości w zakresie, zwróci błąd #LICZBA.
Jak to działa?
Cóż, to całkiem proste. Zobaczmy każdą część po kolei.
JEŚLI(imiona="Rony" ,WIERSZ(nazwy)-WIERSZ(A2)+1) :
W JEŻELI, names=„Rony” zwraca tablicę PRAWDA i FAŁSZ. TRUE, gdy komórka jest w zasięgu nazwiska (A2:A10) pasuje do „Rony”.{PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA;PRAWDA;FAŁSZ}.
Następny wiersz(nazwy)-WIERSZ(A2)+1:
WIERSZ(nazwy): tutaj funkcja WIERSZ zwraca numer wiersza każdej komórki w nazwach. {2;3;4;5;6;7;8;9;10}.
WIERSZ(nazwy)-WIERSZ(A2)Następnie od każdej wartości w danej tablicy odejmujemy numer wiersza A2. To daje nam tablicę numerów seryjnych zaczynając od 0. {0;1;2;3;4;5;6;7;8}.
WIERSZ(nazwy)-WIERSZ(A2)+1: Aby uzyskać numery seryjne zaczynające się od 1, dodajemy 1 do każdej wartości w tej tablicy. Daje nam to numer seryjny zaczynający się od 1. {1;2;3;4;5;6;7;8;9}.
Teraz mamy JEŻELI({PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA;PRAWDA;FAŁSZ},{1;2;3;4;5;6;7;8;9}). To rozwiązuje {1;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;7;8;FAŁSZ}.
Teraz mamy formułę rozwiązaną na MAŁY({1;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;7;8;FAŁSZYWE},2). Teraz SMALL zwraca drugą najmniejszą wartość w zakresie, czyli 7.
Jak tego używamy?
Pojawia się pytanie: jaka jest korzyść z otrzymania surowego indeksu n-tego dopasowania? Byłoby bardziej przydatne, gdybyś mógł pobrać powiązane informacje z n-tej wartości. Cóż, to też można zrobić. Jeśli chcemy uzyskać wartość z wartości sąsiedniej komórki n-tego dopasowania w zakresie nazwy (A2: A10).
{=INDEKS(B2:B10; MAŁY(JEŻELI()imiona="Rony" ,WIERSZ(nazwy)-WIERSZ(A2)+1),2))}
Więc tak, tak możesz uzyskać n-ty mecz w zakresie. Mam nadzieję, że wystarczająco wyjaśniłem. Jeśli masz jakiekolwiek wątpliwości dotyczące tego artykułu lub jakiegokolwiek innego tematu związanego z excelem/VBA, napisz w sekcji komentarzy poniżej.
Jak uzyskać sekwencyjny numer wiersza w programie Excel?
Vlookup Top 5 wartości ze zduplikowanymi wartościami przy użyciu INDEX-MATCH w programie Excel
WYSZUKAJ.PIONOWO Wiele wartości
Użyj INDEKSU i DOPASUJ, aby wyszukać wartość
Wartość wyszukiwania z wieloma kryteriami
Popularne artykuły:
Funkcja WYSZUKAJ.PIONOWO w programie Excel
LICZ.JEŻELI w Excelu 2016
Jak korzystać z funkcji SUMIF w programie Excel?