Wyszukaj n-te dopasowanie w tabeli za pomocą funkcji INDEX & MATCH

W tym artykule dowiemy się, jak wyszukać n-te dopasowanie w tabeli za pomocą funkcji INDEX & MATCH.

Scenariusz:

Na przykład, musimy znaleźć numerycznie n-te dopasowanie i odpowiadające mu wyniki z tabeli. Potrzebujemy ogólnego wzoru, który pomoże w znalezieniu n-tego dopasowania, aby uzyskać wymaganą wartość.

Jak rozwiązać problem?

Aby formuła zrozumiała, najpierw musimy trochę zrewidować następujące funkcje

funkcja INDEKS
Funkcja PODAJ.POZYCJĘ

Teraz stworzymy formułę korzystając z powyższych funkcji. Funkcja MATCH zwróci indeks najniższego dopasowania z zakresu. Funkcja INDEKS przyjmuje indeks wiersza jako argument i zwraca odpowiednie wymagane wyniki. Ta funkcja wyszukuje

Formuła ogólna:

= INDEKS ( dane , PODAJ.POZYCJĘ ( MAŁY (zakres, n ), zakres, typ_dopasowania ) , nr_kolumny )

dane : tablica wartości w tabeli bez nagłówków

zasięg : lookup_array dla najniższego dopasowania

n : numer, n-ty mecz

typ_dopasowania : 1 (dokładnie lub następny najmniejszy) lub 0 (dokładne dopasowanie) lub -1 (dokładnie lub następny największy)

numer_kolumny : numer kolumny, wymagana wartość do pobrania z kolumny tabeli.

Przykład:

Powyższe stwierdzenia mogą być trudne do zrozumienia. Zrozummy to, używając wzoru w przykładzie

Tutaj mamy tabelę zawierającą szczegóły dotyczące kontynentu światowego. Musimy znaleźć kod kraju z podanej tabeli z wartości wyszukiwania jako nazwę kraju.

Nazwany zakres używany dla tablicy tabeli (E5:I10) i zakresu (I5:I10).
Tutaj użyliśmy nazwanego zakresu zamiast tablicy odwołań do komórek, ponieważ jest to łatwe do zrozumienia. Musimy szukać wszystkich szczegółów, w których wartość ceny jest minimalna lub najniższa w zakresie.
Użyj powyższego wzoru, aby uzyskać pierwszy szczegół, którym jest kapitał
Użyj formuły w komórce G6:

= INDEKS ( tabela ,MATCH ( MAŁY (zasięg, 1) ,zasięg , 0 ) , 4 )

Nazwane zakresy

tabela (E5:I10)

zakres (I5:I10)

Wyjaśnienie:

Funkcja MAŁE znajduje pierwsze najniższe dopasowanie w zakresie i zwraca wartość do funkcji PODAJ.POZYCJĘ.
Funkcja MATCH dopasowuje dokładne dopasowanie wartości minimalnej w zakresie ceny i zwraca indeks wiersza do funkcji INDEX.
Funkcja INDEX odnajduje wartość Capital z indeksem ROW i czwartą kolumną w tabeli o nazwie range.

Jak widać na powyższym zrzucie, uzyskaliśmy wszystkie szczegóły pasujące do n-tej wartości w tabeli. Wyodrębnij szczegóły z tabeli posługując się powyższym wzorem.

Przykład:

Możesz również użyć funkcji LARGE, aby uzyskać n-te dopasowanie od góry lub n-te najwyższe dopasowanie z zakresu i zwróci odpowiednie wyniki.

Formuła ogólna:

= INDEX ( dane , MATCH ( LARGE ( zakres , n ), zakres , match_type ) , col_num )

dane : tablica wartości w tabeli bez nagłówków

zasięg : lookup_array dla najwyższego dopasowania

n : numer, n-ty mecz

typ_dopasowania : 1 (dokładnie lub następny najmniejszy) lub 0 (dokładne dopasowanie) lub -1 (dokładnie lub następny największy)

numer_kolumny : numer kolumny, wymagana wartość do pobrania z kolumny tabeli.

Przykład:

Powyższe stwierdzenia mogą być trudne do zrozumienia. Zrozummy to, używając wzoru w przykładzie

Tutaj mamy tabelę zawierającą szczegóły dotyczące kontynentu światowego. Musimy znaleźć kod kraju z podanej tabeli z wartości wyszukiwania jako nazwę kraju.

Użyj powyższego wzoru, aby uzyskać pierwszy szczegół, którym jest kapitał
Użyj formuły w komórce G6:

= INDEKS ( tabela ,MATCH ( DUŻY (zasięg, 1) ,zasięg , 0 ) , 4 )

Nazwane zakresy

tabela (E5:I10)

zakres (I5:I10)

Wyjaśnienie:

Funkcja LARGE znajduje pierwsze najwyższe dopasowanie w zakresie i zwraca wartość do funkcji PODAJ.POZYCJĘ.
Funkcja PODAJ.POZYCJĘ dopasowuje dokładnie n-te dopasowanie wartości w zakresie ceny i zwraca indeks wiersza do funkcji INDEKS.
Funkcja INDEX znajduje wartość Capital, która ma indeks ROW i czwartą kolumnę w tabeli o nazwie range.

Formuła zwraca wszystkie wyniki dla pierwszej najniższej wartości. Teraz skopiuj formułę, aby uzyskać drugą najmniejszą lub trzecią najniższą, wystarczy zmienić argument n w funkcji MAŁA, aby uzyskać różne wyniki.

Teraz po prostu zmień wartość n i uzyskaj wyniki, jak pokazano poniżej.

Jak widać na powyższym zrzucie, uzyskaliśmy wszystkie szczegóły pasujące do n-tej wartości w tabeli. Wyodrębnij szczegóły z tabeli posługując się powyższym wzorem.

Oto kilka uwag obserwacyjnych podczas korzystania z powyższego wzoru.

Uwagi:

Wartość n nie może być mniejsza niż 1 ani większa niż długość zakresu
Funkcja zwraca błąd #NA, jeśli argument tablicy wyszukiwania funkcji PODAJ.POZYCJĘ nie ma tej samej długości co tablica tabeli.
Formuła zwraca błąd, jeśli szukana_wartość nie jest zgodna z wartością w tabeli szukana_tablica.
Funkcja dopasowuje dokładną wartość, ponieważ argument typu dopasowania do funkcji PODAJ.POZYCJĘ ma wartość 0.
Wartości wyszukiwania można podać jako odwołanie do komórki lub bezpośrednio za pomocą symbolu cudzysłowu ( " ) w formule jako argumentów.

Mam nadzieję, że zrozumiałeś, jak wyszukać n-te dopasowanie w tabeli za pomocą funkcji INDEX & MATCH. Zapoznaj się z innymi artykułami na temat wartości wyszukiwania w programie Excel tutaj. Zachęcamy do składania zapytań poniżej w polu komentarza. Z pewnością Ci pomożemy.

Jeśli podobały Ci się nasze blogi, podziel się nimi ze znajomymi na Facebooku. A także możesz śledzić nas na Twitterze i Facebooku. Chcielibyśmy usłyszeć od Ciebie, daj nam znać, jak możemy ulepszyć, uzupełnić lub unowocześnić naszą pracę i uczynić ją lepszą dla Ciebie. Napisz do nas na stronie e-mail

Użyj INDEKSU i DOPASUJ, aby wyszukać wartość : Funkcja INDEX & MATCH do wyszukiwania wartości zgodnie z wymaganiami.

Zakres SUMA z INDEKSEM w Excelu : Użyj funkcji INDEX, aby znaleźć SUMA wartości zgodnie z wymaganiami.

Jak korzystać z funkcji INDEKS w programie Excel? : Znajdź indeks tablicy za pomocą funkcji INDEX wyjaśnionej na przykładzie.

Jak korzystać z funkcji PODAJ.POZYCJĘ w programie Excel : Znajdź pozycję PODAJ.POZYCJĘ w tablicy przy użyciu wartości INDEX wewnątrz funkcji PODAJ.POZYCJĘ wyjaśnionej na przykładzie.

Jak korzystać z funkcji WYSZUKAJ w programie Excel : Znajdź wartość wyszukiwania w tablicy za pomocą funkcji WYSZUKAJ wyjaśnionej na przykładzie.

Jak korzystać z funkcji WYSZUKAJ.PIONOWO w programie Excel : Znajdź wartość wyszukiwania w tablicy za pomocą funkcji WYSZUKAJ.PIONOWO wyjaśnionej na przykładzie.

popularne artykuły

50 skrótów Excela, aby zwiększyć produktywność

Edytuj listę rozwijaną

Absolutne odniesienie w Excelu

Jeśli z formatowaniem warunkowym

Jeśli z symbolami wieloznacznymi

Przeglądaj według daty

Dołącz imię i nazwisko w programie Excel