W programie Excel dokładne pobieranie określonych danych jest często koniecznością. Chociaż funkcje INDEKS i DOPASUJ mają swoje mocne strony, połączenie ich odblokowuje potężny zestaw narzędzi do wyszukiwania danych. Razem zapewniają szeroki zakres możliwości wyszukiwania, od podstawowych wyszukiwań poziomych i pionowych po bardziej zaawansowane funkcje, takie jak wyszukiwanie dwukierunkowe, rozróżnianie wielkości liter i wyszukiwanie wielokryterialne. Oferując ulepszone możliwości w porównaniu z VLOOKUP, połączenie INDEX i MATCH pozwala na szerszy zakres opcji wyszukiwania danych. W tym samouczku zagłębimy się w możliwości, jakie mogą wspólnie osiągnąć.

• funkcja INDEKS
• Funkcja DOPASUJ
• Połącz INDEKS i DOPASUJ

• Wyszukiwanie dwukierunkowe
• Wyszukiwanie w lewo
• Wyszukiwanie z uwzględnieniem wielkości liter
• Najbliższe dopasowanie
• Wyszukiwanie z wieloma kryteriami
• Wyszukiwanie w wielu kolumnach
• Wyszukaj pierwszą niepustą wartość
• Wyszukaj pierwszą wartość liczbową
• Wyszukaj powiązania MAX lub MIN
• Wskazówka: dostosuj własne komunikaty o błędach #N/A

Jak używać INDEKSU i DOPASOWANIA w Excelu?

Zanim użyjemy funkcji INDEKS i PODAJ.POZYCJĘ, upewnijmy się, że wiemy, w jaki sposób INDEKS i PODAJ.POZYCJĘ mogą nam pomóc w wyszukiwaniu wartości.

Jak korzystać z funkcji INDEKS w programie Excel

Połączenia INDEKS funkcja w programie Excel zwraca wartość w danej lokalizacji w określonym zakresie. Składnia funkcji INDEKS jest następująca:

• szyk (wymagane) odnosi się do zakresu, z którego chcesz zwrócić wartość.
• numer_wiersza (wymagane, chyba że numer_kolumny jest obecny) odnosi się do numeru wiersza tablicy.
• numer_kolumny (opcjonalnie, ale wymagane, jeśli numer_wiersza jest pominięty) odnosi się do numeru kolumny tablicy.

Na przykład, aby wiedzieć wynik JeffaThe 6trzeciego ucznia na liście, możesz użyć funkcji INDEX w następujący sposób:

=INDEX(C2:C11,6)

√Uwaga: zakres C2: C11 to miejsce, w którym znajdują się wyniki, a liczba 6 znajduje wynik egzaminu z 6uczeń.

Zróbmy mały test. Dla formuły =INDEKS(A1:C1,2;XNUMX), jaką wartość zwróci? --- Tak, powróci Data urodzeniaThe 2druga wartość w danym wierszu.

Powinniśmy teraz wiedzieć, że funkcja INDEX może doskonale działać z zakresami poziomymi lub pionowymi. Ale co, jeśli potrzebujemy go do zwrócenia wartości w większym zakresie z kilkoma wierszami i kolumnami? Cóż, w tym przypadku powinniśmy zastosować zarówno numer wiersza, jak i numer kolumny. Na przykład, aby się dowiedzieć Wynik Jeffa w obrębie tabeli, a nie w pojedynczej kolumnie, możemy zlokalizować jego wynik za pomocą a wiersz numer 6 oraz numer kolumny 3 komórki od A2 do C11 lubię to:

=INDEX(A2:C11,6,3)

Jednak w przypadku naprawdę dużej bazy danych z wieloma wierszami i kolumnami z pewnością nie jest dla nas wygodne stosowanie formuły z dokładnym numerem wiersza i numerem kolumny. I to właśnie wtedy powinniśmy połączyć użycie funkcji PODAJ.POZYCJĘ.

Niesamowity! Korzystanie z wydajnych zakładek w programie Excel, takich jak Chrome, Edge, Firefox i Safari!
Zaoszczędź 50% swojego czasu i zmniejszaj tysiące kliknięć myszą każdego dnia!

Jak korzystać z funkcji DOPASUJ w programie Excel

Funkcja PODAJ.POZYCJĘ w programie Excel zwraca wartość liczbową, lokalizację określonego elementu w podanym zakresie. Składnia funkcji PODAJ.POZYCJĘ jest następująca:

• lookup_value (wymagane) odnosi się do wartości do dopasowania w pliku wyszukiwana_tablica.
• wyszukiwana_tablica (wymagane) odnosi się do zakresu komórek, w których ma zostać wyszukane polecenie DOPASUJ.
• typ_dopasowania (opcjonalny): 1, 0 or -1.
  • 1 (domyślnie), MATCH znajdzie największą wartość, która jest mniejsza lub równa lookup_value. Wartości w wyszukiwana_tablica należy umieścić w porządku rosnącym.
  • 0, MATCH znajdzie pierwszą wartość dokładnie równą lookup_value. Wartości w wyszukiwana_tablica może być w dowolnej kolejności. (W przypadkach, w których typ dopasowania jest ustawiony na 0, możesz użyć symboli wieloznacznych).
  • -1, MATCH znajdzie najmniejszą wartość, która jest większa lub równa lookup_value. Wartości w wyszukiwana_tablica należy umieścić w porządku malejącym.

Na przykład, aby wiedzieć pozycja Very na liście nazwisk, możesz użyć formuły PODAJ.POZYCJĘ w następujący sposób:

=MATCH("Vera",A2:A11,0)

√ Uwaga: Wynik „4” oznacza, że ​​imię „Vera” znajduje się na 4. pozycji zestawienia.

Teraz, gdy znamy już podstawowe zastosowania funkcji INDEKS i PODAJ.POZYCJĘ w programie Excel, zakasajmy rękawy i przygotujmy się do połączenia tych dwóch funkcji.

Jak połączyć INDEKS i DOPASOWANIE w Excelu

Zobacz poniższy przykład, aby dowiedzieć się, jak możemy połączyć funkcje INDEX i MATCH:

Znaleźć Wynik Evelyn, ze świadomością, że wyniki egzaminu znajdują się w 3trzecia kolumna, możemy użyj funkcji MATCH, aby automatycznie określić pozycję wiersza bez konieczności ręcznego liczenia. Następnie możemy użyć funkcji INDEX, aby pobrać wartość na przecięciu wskazanego wiersza i trzeciej kolumny:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

Cóż, ponieważ formuła może wyglądać na nieco skomplikowaną, przejrzyjmy każdą jej część.

Połączenia INDEKS formuła zawiera trzy argumenty:

• numer_wiersza: DOPASUJ("Ewelina",A2:A11,0) dostarcza INDEX pozycję wiersza wartości „Evelyn" w zasięgu A2: A11, który jest 5.
• numer_kolumny: 3 określa 3rd kolumna dla INDEX, aby zlokalizować wynik w tablicy.
• szyk: A2: C11 instruuje INDEX, aby zwrócił pasującą wartość na przecięciu określonego wiersza i kolumny, w zakresie od A2 do C11. Wreszcie otrzymujemy wynik 90.

W powyższym wzorze zastosowaliśmy zakodowaną na stałe wartość, "Ewelina". Jednak w praktyce wartości zakodowane na stałe są niepraktyczne, ponieważ wymagałyby modyfikacji za każdym razem, gdy szukamy innych danych, takich jak wynik innego ucznia. W takich scenariuszach możemy używać odwołań do komórek do tworzenia dynamicznych formuł. Na przykład w tym przypadku to zrobię zmień „Evelyn” na F2:

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

Przykłady formuł INDEKS i DOPASUJ

W tej części omówimy różne okoliczności korzystania z funkcji INDEKS i PODAJ.POZYCJĘ w celu zaspokojenia różnych potrzeb.

INDEKS i DOPASUJ, aby zastosować wyszukiwanie dwukierunkowe

W poprzednim przykładzie znaliśmy numer kolumny i użyliśmy formuły DOPASUJ, aby znaleźć numer wiersza. Ale co, jeśli nie jesteśmy pewni również numeru kolumny?

W takich przypadkach możemy przeprowadzić wyszukiwanie dwukierunkowe, zwane także wyszukiwaniem macierzowym, używając dwóch funkcji DOPASUJ: jednej do znalezienia numeru wiersza i drugiej do określenia numeru kolumny. Na przykład wiedzieć Wynik Evelyn, powinniśmy skorzystać ze wzoru:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

INDEKS i DOPASUJ, aby zastosować lewe wyszukiwanie

Rozważmy teraz scenariusz, w którym musisz określić klasę Evelyn. Być może zauważyłeś, że kolumna klasy jest umieszczona na lewo od kolumny nazwy, co przekracza możliwości innej zaawansowanej funkcji wyszukiwania programu Excel, WYSZUKAJ.PIONOWO.

W rzeczywistości możliwość wyszukiwania po lewej stronie jest jednym z aspektów, w których połączenie INDEKSU i PODAJNIKA przyćmiewa WYSZUKAJ.PIONOWO.

Znaleźć Klasa Evelyn, zastosuj następujący wzór do szukaj Evelyn w B2:B11 i pobierz odpowiednią wartość z A2:A11.

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

INDEKS i DOPASUJ, aby zastosować wyszukiwanie z uwzględnieniem wielkości liter

W funkcjach DOPASUJ wielkość liter z natury nie uwzględnia wielkości liter. Jeśli jednak potrzebujesz, aby formuła rozróżniała wielkie i małe litery, możesz ją ulepszyć, włączając DOKŁADNY funkcjonować. Łącząc funkcję DOPASUJ z DOKŁADNYM w formule INDEKS, możesz skutecznie przeprowadzić wyszukiwanie z uwzględnieniem wielkości liter, jak pokazano poniżej:

• szyk odnosi się do zakresu, z którego chcesz zwrócić wartość.
• lookup_value odnosi się do wartości do dopasowania, biorąc pod uwagę wielkość liter, w pliku wyszukiwana_tablica.
• wyszukiwana_tablica odnosi się do zakresu komórek, z którymi chcesz porównać funkcję DOPASUJ lookup_value.

Na przykład, aby wiedzieć Wynik egzaminu JIMMY'ego, użyj następującego wzoru:

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ Uwaga: To jest formuła tablicowa, która wymaga wprowadzenia za pomocą Ctrl + Shift + Wchodzę, z wyjątkiem Excel 365 i Excel 2021.

INDEX i MATCH, aby znaleźć najbliższe dopasowanie

W programie Excel mogą wystąpić sytuacje, w których trzeba znaleźć najbliższe dopasowanie do określonej wartości w zestawie danych. W takich scenariuszach niezwykle pomocne może być użycie kombinacji funkcji INDEX i MATCH wraz z funkcjami ABS i MIN.

• szyk odnosi się do zakresu, z którego chcesz zwrócić wartość.
• wyszukiwana_tablica odnosi się do zakresu wartości, do którego chcesz znaleźć najbliższe dopasowanie lookup_value.
• lookup_value odnosi się do wartości, aby znaleźć jej najbliższe dopasowanie.

Na przykład, aby się dowiedzieć którego wynik jest najbliższy 85, użyj poniższego wzoru wyszukaj wynik najbliższy 85 w C2:C11 i pobierz odpowiednią wartość z A2:A11.

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ Uwaga: To jest formuła tablicowa, która wymaga wprowadzenia za pomocą Ctrl + Shift + Wchodzę, z wyjątkiem Excel 365 i Excel 2021.

Niesamowity! Korzystanie z wydajnych zakładek w programie Excel, takich jak Chrome, Edge, Firefox i Safari!
Zaoszczędź 50% swojego czasu i zmniejszaj tysiące kliknięć myszą każdego dnia!

INDEKS i DOPASUJ, aby zastosować wyszukiwanie z wieloma kryteriami

Aby znaleźć wartość spełniającą wiele warunków, co wymaga przeszukania dwóch lub więcej kolumn, zastosuj poniższą formułę. Formuła umożliwia wyszukiwanie wielokryterialne poprzez określenie różnych warunków w różnych kolumnach, co pomaga znaleźć żądaną wartość spełniającą wszystkie określone kryteria.

√ Uwaga: To jest formuła tablicowa, która wymaga wprowadzenia za pomocą Ctrl + Shift + Wchodzę. Na pasku formuły pojawi się wtedy para nawiasów klamrowych.

• szyk odnosi się do zakresu, z którego chcesz zwrócić wartość.
• (wartość_wyszukiwania=tablica_wyszukiwania) reprezentuje pojedynczy warunek. Warunek ten sprawdza, czy konkretny lookup_value odpowiada wartościom w wyszukiwana_tablica.

Na przykład, aby znaleźć wynik Coco z klasy A, której data urodzenia to 7, możesz użyć następującej formuły:

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

INDEKS i DOPASUJ, aby zastosować wyszukiwanie w wielu kolumnach

Wyobraź sobie scenariusz, w którym masz do czynienia z wieloma kolumnami danych. Pierwsza kolumna pełni rolę klucza do klasyfikacji danych w pozostałych kolumnach. Aby określić kategorię lub klasyfikację konkretnego wpisu, należy przeszukać kolumny danych i powiązać je z odpowiednim kluczem w kolumnie referencyjnej.

Na przykład, jak w poniższej tabeli możemy dopasować ucznia Shawna do odpowiedniej klasy za pomocą INDEKSU i PODAJ. Cóż, można to osiągnąć za pomocą formuły, ale formuła jest dość obszerna i może być trudna do zrozumienia, nie mówiąc już o zapamiętaniu i wpisaniu.

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

To tam gdzie Kutools dla programu Excel's Indeksuj i dopasowuj w wielu kolumnach funkcja się przydaje. Upraszcza to proces, dzięki czemu można szybko i łatwo dopasować określone wpisy do odpowiednich kategorii. Aby odblokować to potężne narzędzie i bez wysiłku dopasować Shawna do jego klasy, po prostu pobierz i zainstaluj dodatek Kutools dla programu Excel, a następnie wykonaj następujące czynności:

1. Wybierz komórkę docelową, w której chcesz wyświetlić pasującą klasę.
2. Na Kutools tab, kliknij Pomocnik formuły > Wyszukiwanie i odniesienie > Indeksuj i dopasowuj w wielu kolumnach.
3. 
3. W wyskakującym oknie dialogowym wykonaj następujące czynności:
   1. Kliknij pierwszy przycisk obok wyszukiwanie_kol , aby wybrać kolumnę zawierającą kluczowe informacje, które chcesz zwrócić, tj. nazwy klas. (Tutaj możesz wybrać tylko jedną kolumnę.)
   2. Kliknij 2 przycisk obok Tabela_rng aby wybrać komórki, aby dopasować wartości w zaznaczonych wyszukiwanie_kol, tj. imiona uczniów.
   3. Kliknij 3 przycisk obok Wyszukaj_wartość aby wybrać komórkę zawierającą imię i nazwisko ucznia, którego chcesz dopasować do jego klasy, w tym przypadku Shawna.
   4. Kliknij OK.
   5. 

Wynik

Kutools automatycznie wygenerował formułę i natychmiast zobaczysz nazwę klasy Shawna w komórce docelowej.

INDEX i MATCH, aby wyszukać pierwszą niepustą wartość

Aby pobrać z kolumny lub wiersza pierwszą niepustą wartość, ignorując błędy, można zastosować formułę opartą na funkcjach INDEKS i PODAJ. Jeśli jednak nie chcesz ignorować błędów ze swojego zakresu, dodaj funkcję ISBLANK.

• Uzyskaj pierwszą niepustą wartość w kolumnie lub wierszu, ignorując błędy:

• =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))

• Uzyskaj pierwszą niepustą wartość w kolumnie lub wierszu, łącznie z błędami:

• =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

  

INDEX i MATCH, aby wyszukać pierwszą wartość liczbową

Aby pobrać pierwszą wartość liczbową z kolumny lub wiersza, należy skorzystać ze wzoru opartego na funkcjach INDEKS, PODAJ.POZYCJĘ i LICZBA.

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

INDEX i MATCH, aby wyszukać powiązania MAX lub MIN

Jeśli chcesz pobrać wartość powiązaną z wartością maksymalną lub minimalną w zakresie, możesz użyć funkcji MAX lub MIN wraz z funkcjami INDEKS i PODAJ.

• INDEX i MATCH, aby pobrać wartość powiązaną z wartością maksymalną:
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
• INDEX i MATCH, aby pobrać wartość powiązaną z wartością minimalną:
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
• W powyższych wzorach występują dwa argumenty:
• • szyk odnosi się do zakresu, z którego chcesz zwrócić powiązane informacje.
  • wyszukiwana_tablica reprezentuje zbiór wartości, które należy sprawdzić lub przeszukać pod kątem określonych kryteriów, tj. wartości maksymalnych lub minimalnych.

Na przykład, jeśli chcesz określić kto ma najwyższy wynik, zastosuj następującą formułę:

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

Wskazówka: dostosuj własne komunikaty o błędach #N/A

Podczas pracy z funkcjami INDEKS i DOPASUJ w programie Excel może wystąpić błąd #N/A, gdy nie ma pasującego wyniku. Na przykład w poniższej tabeli podczas próby znalezienia wyniku ucznia o imieniu Samanta pojawia się błąd #N/A, ponieważ nie ma jej w zbiorze danych.

Aby Twoje arkusze kalkulacyjne były bardziej przyjazne dla użytkownika, możesz dostosować ten komunikat o błędzie, zawijając formułę DOPASOWANIE INDEKSU do funkcji IFNA:

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

Powyżej znajduje się cała istotna treść związana z funkcjami INDEKS i PODAJ.POZYCJĘ w programie Excel. Mam nadzieję, że tutorial okaże się pomocny. Jeśli chcesz poznać więcej porad i wskazówek dotyczących programu Excel, kliknij tutaj aby uzyskać dostęp do naszej obszernej kolekcji ponad tysięcy samouczków.