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Ę
- Przykład połączenia INDEX i DOPASUJ
- INDEKS i DOPASUJ, aby zastosować lewe wyszukiwanie
- INDEKS i DOPASUJ, aby zastosować wyszukiwanie dwukierunkowe
- INDEKS i DOPASUJ, aby zastosować wyszukiwanie z uwzględnieniem wielkości liter
- INDEKS i DOPASUJ, aby zastosować wyszukiwanie z wieloma kryteriami
- INDEKS i DOPASUJ, aby zastosować wyszukiwanie w wielu kolumnach
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:
- 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
√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
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:
- 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
√ 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
☞ 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
Cóż, ponieważ formuła może wyglądać na skomplikowaną, przejrzyjmy każdą jej część.
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))
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
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:
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:
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:
√ 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:
=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:
√ 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:
=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:
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:
=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:
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Ę?
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.
3. Wybierać Wyszukiwanie z typu formuły, a następnie kliknij Indeksuj i dopasowuj w wielu kolumnach.
4. Kliknij pierwszy 
przycisk 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 przycisk po prawej stronie Tabela_rng aby wybrać komórki, aby dopasować wartości w zaznaczonych Lookup_col, czyli nazwiska uczniów.
C. Kliknij trzeci przycisk po prawej stronie Wyszukaj_wartość aby wybrać komórkę do wyszukania, tj. imię ucznia, które chcesz dopasować do jego/jej klasy.
5. kliknij Ok, zobaczysz nazwę klasy Jimmy'ego w komórce docelowej.
6. Teraz możesz przeciągnąć uchwyt wypełniania w dół, aby wypełnić zajęcia innych uczniów.
Najlepsze narzędzia biurowe
Kutools for Excel rozwiązuje większość twoich problemów i zwiększa wydajność o 80%
- 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ę...
- 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!
You are guest
or post as a guest, but your post won't be published automatically.
Be the first to comment.
