Note: The other languages of the website are Google-translated. Back to English
Zaloguj Się  \/ 
x
or
x
Rejestruję się  \/ 
x

or

Jak używać INDEKSU i DOPASOWYWANIA razem w programie Excel?

Podczas pracy z tabelami programu Excel możesz stale znaleźć sytuacje, w których musisz wyszukać wartość. W tym samouczku pokażemy, jak zastosować kombinację funkcji INDEKS i PODAJ.POZYCJĘ, aby wykonać wyszukiwania poziome i pionowe, wyszukiwania dwukierunkowe, wyszukiwania uwzględniające wielkość liter oraz wyszukiwania spełniające wiele kryteriów.

Co robią funkcje INDEKS i PODAJ.POZYCJĘ w programie Excel

Jak używać jednocześnie funkcji INDEX i PODAJ.POZYCJĘ


Co robią funkcje INDEKS i PODAJ.POZYCJĘ w programie Excel

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.

Wykorzystanie funkcji INDEX w programie Excel

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

=INDEX(array, row_num, [column_num])
  • szyk (wymagane) odnosi się do zakresu, z którego chcesz zwrócić wartość.
  • numer_wiersza (wymagane, chyba że występuje numer_kolumny) odnosi się do numeru wiersza tablicy.
  • numer_kolumny (opcjonalne, ale wymagane, jeśli pominięto numer_wiersza) odnosi się do numeru kolumny tablicy.

Na przykład, aby wiedzieć końcowy wynik egzaminu Jeff, szósty uczeń na liście, możesz użyć funkcji INDEX w ten sposób:

=INDEKS(E2:E11;6) >>> powraca 60

dopasowanie indeksu Excela 01

√Uwaga: zakres E2: E11 jest tam, gdzie znajduje się egzamin końcowy, a liczba 6 znajduje wynik egzaminu z 6uczeń.

Zróbmy mały test. Dla formuły =INDEKS(B2:E2,3), jaką wartość zwróci? --- Tak, wróci ChinyThe 3 wartość w podanym zakresie.

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ć kraj, z którego pochodzi Emily z INDEKSEM możemy zlokalizować wartość z numerem wiersza 8 i numerem kolumny 3 w komórkach od B2 do E11 w ten sposób:

=INDEKS(B2:E11,8,3) >>> powraca Chiny

dopasowanie indeksu Excela 02

Zgodnie z powyższymi przykładami o funkcji INDEX w programie Excel powinieneś wiedzieć, że:

  • Funkcja INDEX może pracować z zakresami pionowymi i poziomymi.
  • W funkcji INDEKS nie jest rozróżniana wielkość liter.
  • Numer wiersza wyprzedza numer kolumny (jeśli potrzebujesz obu liczb) w formule INDEKS.

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Ę.

Teraz nauczmy się najpierw podstaw funkcji PODAJ.POZYCJĘ.


Użycie 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:

=MATCH(lookup_value, lookup_array, [match_type])
  • lookup_array (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 lookup_array należy umieścić w porządku rosnącym.
  • 0, MATCH znajdzie pierwszą wartość dokładnie równą lookup_value. Wartości w lookup_array 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 lookup_array 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:

=PODZIEL("vera";C2:C11,0) >>> powraca 4

dopasowanie indeksu Excela 03

√ Uwaga: W funkcji PODAJ.POZYCJĘ nie jest rozróżniana wielkość liter. Wynik „4” oznacza, że ​​nazwisko „Vera” znajduje się na 4 pozycji listy. „0” w formule to typ dopasowania, który znajdzie pierwszą wartość w tablicy wyszukiwania, która jest dokładnie równa wartości wyszukiwania „Vera”.

Wiedzieć pozycja partytury „96” w rzędzie od B2 do E2, możesz użyć funkcji PODAJ.POZYCJĘ w ten sposób:

= PODAJ.POZYCJĘ(96;B2:E2,0) >>> powraca 4

dopasowanie indeksu Excela 04

☞ Rzeczy, które powinniśmy wiedzieć o funkcji DOPASUJ w programie Excel:

  • Funkcja PODAJ.POZYCJĘ zwraca pozycję wartości odnośnika w tablicy odnośników, a nie samą wartość.
  • Funkcja PODAJ.POZYCJĘ zwraca pierwsze dopasowanie w przypadku duplikatów.
  • Podobnie jak funkcja INDEX, funkcja PODAJ.POZYCJĘ może działać również z zakresami pionowymi i poziomymi.
  • W MATCH również nie jest rozróżniana wielkość liter.
  • Jeśli wartość wyszukiwania formuły PODAJ.POZYCJĘ jest w postaci tekstu, umieść ją w cudzysłowie.

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 używać jednocześnie funkcji INDEX i PODAJ.POZYCJĘ

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

Przykład połączenia INDEX i DOPASUJ

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

Na przykład, aby wiedzieć Wynik egzaminu końcowego Evelyn, powinniśmy skorzystać ze wzoru:

=INDEX(A2:D11,MATCH("evelyn",B2:B11,0),MATCH("final exam",A1:D1,0)) >>> powraca 90

dopasowanie indeksu Excela 05

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

dopasowanie indeksu Excela 06

Jak widać powyżej, duży INDEKS formuła zawiera trzy argumenty:

  • szyk: A2: D11 mówi INDEX, aby zwrócił pasującą wartość z komórek do A2 do D11.
  • numer_wiersza: PODAJ.POZYCJĘ("evelyn";B2:B11,0) mówi INDEX dokładny wiersz wartości.
  • Jeśli chodzi o formułę PODAJ 5.
  • numer_kolumny: PODAJ.POZYCJĘ("egzamin końcowy",A1:D1,0) mówi INDEX dokładną kolumnę wartości.
  • Jeśli chodzi o formułę PODAJ 4.

Możesz więc zobaczyć dużą formułę tak prostą, jak ta, którą pokazaliśmy poniżej:

=INDEKS(A2: D11,5,4)

W tym przykładzie użyliśmy wartości zakodowanych na sztywno, „evelyn” i „egzamin końcowy”. Jednak w tak dużej formule nie chcemy zakodowanych na stałe wartości, ponieważ będziemy musieli je zmieniać za każdym razem, gdy będziemy szukać czegoś nowego. W takich okolicznościach możemy użyć odwołań do komórek, aby uczynić formułę dynamiczną w następujący sposób:

=INDEKS(A2: D11,MECZ(G2,B2:B11,0),MECZ(F3,A1:D1,0))

dopasowanie indeksu Excela 07


INDEKS i DOPASUJ, aby zastosować lewe wyszukiwanie

Teraz powiedzmy, że musisz znać klasę Evelyn, jak możemy użyć indeksu i dopasowania, aby poznać odpowiedź? Jeśli zwróciłeś na to uwagę, powinieneś zauważyć, że kolumna klasy znajduje się po lewej stronie kolumny nazwy, co wykracza poza możliwości innej potężnej funkcji wyszukiwania Excela, WYSZUKAJ.PIONOWO.

W rzeczywistości możliwość wyszukiwania w lewo jest jednym z aspektów, w których kombinacja INDEKS i PODAJ.POZYCJĘ jest lepsza niż WYSZUKAJ.PIONOWO.

Wiedzieć Klasa Evelyn, wystarczy zmienić wartość w komórce F3 na „Klasa” i użyć tej samej formuły, jak pokazano powyżej, funkcje INDEKS i PODAJ.POZYCJĘ od razu odpowiedzą:

=INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0)) >>> powraca A

dopasowanie indeksu Excela 08

Jeśli masz instalacjędoprowadziło Kutools dla programu Excel, profesjonalny dodatek do Excela opracowany przez nasz zespół, możesz również zastosować lewe wyszukiwanie dla określonych wartości z jego WYSZUKAJ od prawej do lewej funkcja za pomocą kilku kliknięć. Aby wdrożyć tę funkcję, przejdź do Kutools w Excelu, znajdź Formuła grupę i kliknij WYSZUKAJ od prawej do lewej na liście rozwijanej Super WYSZUKAJ. Zobaczysz takie wyskakujące okno dialogowe:

dopasowanie indeksu Excela 09

Kliknij tutaj, aby uzyskać konkretne kroki, aby zastosować lewą funkcję wyszukiwania za pomocą Kutools for Excel.


INDEKS i DOPASUJ, aby zastosować wyszukiwanie dwukierunkowe

Czy teraz możesz utworzyć formułę kombinacji INDEKS i PODAJ.POZYCJĘ z dynamicznymi wartościami wyszukiwania, aby zastosować wyszukiwania dwukierunkowe? Przećwiczmy tworzenie formuł w komórkach G3, G4 i G5, jak pokazano poniżej:

dopasowanie indeksu Excela 10

Oto odpowiedzi:

Komórka G3: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0))
Komórka G4: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F4,A1:D1,0))
Komórka G5: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F5,A1:D1,0))

√ Uwaga: Po zastosowaniu formuł możesz łatwo uzyskać informacje o dowolnym uczniu, zmieniając imię w komórce G2.


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

Z powyższych przykładów wiemy, że w funkcjach INDEX i MATCH nie jest rozróżniana wielkość liter. Jednak w przypadkach, gdy potrzebujesz formuły do ​​rozróżnienia wielkich i małych liter, możesz dodać DOKŁADNY funkcja do twoich formuł w ten sposób:

=INDEX(return_range,MATCH(TRUE,EXACT("lookup_value1",range1),0),MATCH("lookup_value2",range2,0))
√ Uwaga: To jest formuła tablicowa, która wymaga wprowadzenia za pomocą Ctrl + Shift + Enter. Na pasku formuły pojawi się wtedy para nawiasów klamrowych.

Na przykład, aby wiedzieć Wynik egzaminu JIMMY'ego, użyj funkcji w ten sposób:

dopasowanie indeksu Excela 11

=INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),MATCH("final exam",A1:C1,0)) >>> powraca 86

Możesz też użyć odwołań do komórek:

=INDEX(A2:C11,MATCH(TRUE,EXACT(F2,A2:A11),0),MATCH(E3,A1:C1,0)) >>> powraca 86
Uwaga: nie zapomnij wejść za pomocą Ctrl + Shift + Enter.


INDEKS i DOPASUJ, aby zastosować wyszukiwanie z wieloma kryteriami

Kiedy mamy do czynienia z dużą bazą danych z kilkoma kolumnami i podpisami wierszy, zawsze trudno jest znaleźć coś, co spełnia wiele warunków. W takim przypadku zapoznaj się z poniższą formułą, aby wyszukać wiele kryteriów:

=INDEX(return_range,MATCH(1,(lookup_value1=range1)*(lookup_value2=range2)*(…),0))
Uwaga: jest to formuła tablicowa, która wymaga wprowadzenia za pomocą Ctrl + Shift + Enter. Na pasku formuły pojawi się wtedy para nawiasów klamrowych.

Na przykład, aby znaleźć końcowy wynik egzaminu Coco klasy A, który pochodzi z Indii, wzór wygląda następująco:

dopasowanie indeksu Excela 12

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0)) >>> powraca 88
√ Uwaga: nie zapomnij wejść za pomocą Ctrl + Shift + Enter.

A co, jeśli ciągle zapominasz o użyciu Ctrl + Shift + Enter uzupełnić formułę, aby formuła zwracała nieprawidłowe wyniki? Tutaj mamy bardziej złożoną formułę, którą możesz uzupełnić jednym prostym wchodzić klawisz:

=INDEX(return_range,MATCH(1,INDEX((lookup_value1=range1)*(lookup_value2=range2)*(…),0,1),0))

Dla tego samego przykładu powyżej, aby znaleźć końcowy wynik egzaminu Coco klasy A, który pochodzi z Indii, formuła, która potrzebuje tylko zwykłego wchodzić trafienie wygląda następująco:

dopasowanie indeksu Excela 13

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

Tutaj nie będziemy używać wartości zakodowanych na sztywno, ponieważ będziemy potrzebować uniwersalnej formuły w przypadku wielu kryteriów. Tylko w ten sposób możemy łatwo uzyskać pożądany wynik, zmieniając wartości w komórkach G2, G3, G4 w powyższym przykładzie.

Z Kutools dla programu Excel"s Funkcja wyszukiwania wielowarunkowego umożliwia wyszukiwanie określonych wartości za pomocą wielu kryteriów za pomocą kilku kliknięć. Aby wdrożyć tę funkcję, przejdź do Kutools w Excelu, znajdź Formuła grupę i kliknij Wyszukiwanie wielowarunkowe na liście rozwijanej Super WYSZUKAJ. Następnie zobaczysz wyskakujące okno dialogowe, jak pokazano poniżej:

dopasowanie indeksu Excela 14

Kliknij tutaj, aby uzyskać konkretne kroki, aby zastosować funkcję wyszukiwania wielu warunków za pomocą Kutools for Excel.


INDEKS i DOPASUJ, aby zastosować wyszukiwanie w wielu kolumnach

Jeśli mamy arkusz kalkulacyjny Excela z różnymi kolumnami udostępniającymi jeden podpis, jak pokazano poniżej, w jaki sposób możemy dopasować imię każdego ucznia do jego klasy za pomocą funkcji INDEKS i PODAJ.POZYCJĘ?

dopasowanie indeksu Excela 15

Tutaj pokażę Ci, jak wykonać zadanie za pomocą naszego profesjonalnego narzędzia Kutools dla programu Excel. Z jego Pomocnik formuły, możesz szybko dopasować uczniów do ich klas, jak pokazano poniżej:

1. Wybierz komórkę docelową, w której chcesz zastosować funkcję.

2. Pod Kutools kartę, przejdź do Pomocnik formułykliknij Pomocnik formuły na liście rozwijanej.

dopasowanie indeksu Excela 16

3. Wybierać Wyszukiwanie z typu formuły, a następnie kliknij Indeksuj i dopasowuj w wielu kolumnach.

dopasowanie indeksu Excela 17

4. Kliknij pierwszy ikona dopasowania indeksu Excelprzycisk po prawej stronie Lookup_col aby wybrać komórki, z których chcesz zwrócić wartość, tj. nazwy klas. (Tutaj możesz wybrać tylko jedną kolumnę lub wiersz).
    b. Kliknij drugi ikona dopasowania indeksu Excelprzycisk po prawej stronie Tabela_rng aby wybrać komórki, aby dopasować wartości w zaznaczonych Lookup_col, czyli nazwiska uczniów.
    C. Kliknij trzeci ikona dopasowania indeksu Excelprzycisk po prawej stronie Wyszukaj_wartość aby wybrać komórkę do wyszukania, tj. imię ucznia, które chcesz dopasować do jego/jej klasy.

dopasowanie indeksu Excela 18

5. kliknij Ok, zobaczysz nazwę klasy Jimmy'ego w komórce docelowej.

dopasowanie indeksu Excela 19

6. Teraz możesz przeciągnąć uchwyt wypełniania w dół, aby wypełnić zajęcia innych uczniów.

dopasowanie indeksu Excela 20

Kliknij, aby pobrać Kutools for Excel na 30-dniową bezpłatną wersję próbną.



  • Pasek Super Formula (łatwo edytować wiele wierszy tekstu i formuły); Układ do czytania (łatwe odczytywanie i edytowanie dużej liczby komórek); Wklej do filtrowanego zakresu...
  • Scal komórki / wiersze / kolumny i przechowywanie danych; Podziel zawartość komórek; Połącz zduplikowane wiersze i sumę / średnią... Zapobiegaj zduplikowanym komórkom; Porównaj zakresy...
  • Wybierz Duplikat lub Unikalny Wydziwianie; Wybierz puste wiersze (wszystkie komórki są puste); Super Find i Fuzzy Find w wielu zeszytach ćwiczeń; Losowy wybór ...
  • Dokładna kopia Wiele komórek bez zmiany odwołania do formuły; Automatyczne tworzenie odniesień do wielu arkuszy; Wstaw punktory, Pola wyboru i nie tylko ...
  • Ulubione i szybkie wstawianie formuł, Zakresy, wykresy i obrazy; Szyfruj komórki z hasłem; Utwórz listę mailingową i wysyłaj e-maile ...
  • Wyodrębnij tekst, Dodaj tekst, Usuń według pozycji, Usuń przestrzeń; Tworzenie i drukowanie podsumowań stronicowania; Konwertuj zawartość komórek i komentarze...
  • Super filtr (zapisz i zastosuj schematy filtrów do innych arkuszy); Zaawansowane sortowanie według miesiąca / tygodnia / dnia, częstotliwości i innych; Specjalny filtr pogrubieniem, kursywą ...
  • Połącz skoroszyty i arkusze robocze; Scal tabele na podstawie kluczowych kolumn; Podziel dane na wiele arkuszy; Konwersja wsadowa xls, xlsx i PDF...
  • Grupowanie tabel przestawnych według numer tygodnia, dzień tygodnia i więcej ... Pokaż odblokowane, zablokowane komórki w różnych kolorach; Podświetl komórki, które mają formułę / nazwę...
karta kte 201905
  • Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
  • Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
  • Zwiększa produktywność o 50% i zmniejsza setki kliknięć myszą każdego dnia!
officetab dół
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.