INDEX i MATCH z wieloma tablicami
Załóżmy, że masz kilka tabel z takimi samymi podpisami, jak pokazano poniżej, aby wyszukać wartości odpowiadające podanym kryteriom w tych tabelach może być dla Ciebie trudnym zadaniem. W tym samouczku omówimy, jak wyszukać wartość w wielu tablicach, zakresach lub grupach, dopasowując określone kryteria do INDEKS, MATCH i WYBIERZ funkcje.
Jak wyszukać wartość w wielu tablicach?
Aby poznać liderzy różnych grup należących do różnych działów, możesz najpierw użyć funkcji WYBIERZ, aby wskazać tabelę, z której ma zostać zwrócona nazwa lidera. Funkcja MATCH będzie wtedy znała pozycję lidera w tabeli, do której on/ona należy. Na koniec funkcja INDEX pobierze lidera na podstawie informacji o pozycji oraz określonej kolumny, w której wymienione są nazwiska liderów.
Ogólna składnia
=INDEX(CHOOSE(array_num,array1,array2,…),MATCH(lookup_value,lookup_array,0),column_num)
- numer_tablicy: Liczba WYBIERZ używana do wskazania tablicy z listy tablica1, tablica2,… aby zwrócić wynik z.
- tablica1, tablica2,…: Tablice, z których ma zostać zwrócony wynik. Tutaj odnosi się do trzech tabel.
- szukana_wartość: Wartość formuły kombinacji użytej do znalezienia pozycji odpowiadającej jej linii odniesienia. Tutaj odnosi się do danej grupy.
- szukana_tablica: Zakres komórek, w których lookup_value znajduje się na liście. Tutaj odnosi się do zakresu grupy. Uwaga: Możesz użyć zakresu grup z dowolnego działu, ponieważ wszystkie są takie same, a my musimy tylko uzyskać numer pozycji.
- numer_kolumny: Kolumna, którą wskazujesz, z której chcesz pobrać dane.
Aby poznać lider Grupy D należącej do Działu A, skopiuj lub wprowadź poniższą formułę w komórce G5 i naciśnij Wchodzę aby uzyskać wynik:
=INDEKS(WYBIERZ(1,$B5$:$C$8,$B11$:$C$14,$B17$:$C$20),MECZ(F5,5 B $: 8 B $0),2)
√ Uwaga: Znaki dolara ($) powyżej oznaczają odwołania bezwzględne, co oznacza, że zakresy nazw i klas w formule nie zmienią się po przeniesieniu lub skopiowaniu formuły do innych komórek. Po wprowadzeniu formuły przeciągnij uchwyt wypełniania w dół, aby zastosować formułę do poniższych komórek, a następnie zmień tablica_numer odpowiednio.
Wyjaśnienie formuły
=INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)
- CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20): Funkcja WYBIERZ zwraca 1st tablica z trzech tablic wymienionych w formule. Więc to wróci $B5$:$C$8, czyli zakres danych Departamentu A.
- DOPASUJ (F5, $ B $ 5: $ B $ 8,0): Typ dopasowania 0 wymusza, aby funkcja MATCH zwróciła pozycję pierwszego dopasowania z Grupa D, wartość w komórce F5, w tablicy 5 B $: 8 B $, który jest 4.
- INDEKS(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),DOPASUJ (F5, $ B $ 5: $ B $ 8,0),2) = INDEKS($B5$:$C$8,4,2): Funkcja INDEX pobiera wartość na przecięciu 4wiersz i 2druga kolumna zakresu $B5$:$C$8, który jest Emily.
Aby uniknąć zmiany tablica_numer w formule za każdym razem, gdy ją kopiujesz, możesz użyć kolumny pomocniczej, kolumny D. Formuła wyglądałaby tak:
=INDEKS(WYBIERZ(D5,$B5$:$C$8,$B11$:$C$14,$B17$:$C$20),MECZ(F5,5 B $: 8 B $0),2)
√Uwaga: liczby 1, 2, 3 w kolumnie pomocnika wskaż array1, array2, array3 wewnątrz funkcji WYBIERZ.
Powiązane funkcje
Funkcja Excel INDEX zwraca wyświetlaną wartość na podstawie podanej pozycji z zakresu lub tablicy.
Funkcja Excel PODAJ.POZYCJĘ wyszukuje określoną wartość w zakresie komórek i zwraca względną pozycję wartości.
Funkcja WYBIERZ zwraca wartość z listy argumentów wartość na podstawie podanego numeru indeksu. Na przykład WYBIERZ(3”,Jabłko”,„Brzoskwinia”,„Pomarańczowy”) zwraca w funkcji wartość Orange, numer indeksu to 3, a Orange to trzecia wartość po numerze indeksu.
Powiązane formuły
Wyszukaj wartości z innego arkusza lub skoroszytu
Jeśli wiesz, jak używać funkcji WYSZUKAJ.PIONOWO do wyszukiwania wartości w arkuszu, wartości podglądu z innego arkusza lub skoroszytu nie będą dla Ciebie problemem.
Vlookup z dynamiczną nazwą arkusza
W wielu przypadkach może być konieczne zebranie danych z wielu arkuszy w celu uzyskania podsumowania. Dzięki połączeniu funkcji WYSZUKAJ.PIONOWO i POŚREDNI możesz utworzyć formułę do wyszukiwania określonych wartości w arkuszach z dynamiczną nazwą arkusza.
Wyszukiwanie wielu kryteriów za pomocą funkcji INDEX i PODAJ.POZYCJĘ
Kiedy mamy do czynienia z dużą bazą danych w arkuszu kalkulacyjnym Excel z kilkoma kolumnami i podpisami wierszy, zawsze trudno jest znaleźć coś, co spełnia wiele kryteriów. W takim przypadku można użyć formuły tablicowej z funkcjami INDEKS i PODAJ.POZYCJĘ.
Najlepsze narzędzia biurowe
Kutools dla programu Excel - pomaga wyróżnić się z tłumu
Kutools dla programu Excel oferuje ponad 300 funkcji, Pewność, że to, czego potrzebujesz, jest w zasięgu jednego kliknięcia...
Karta Office - Włącz czytanie i edycję na kartach w Microsoft Office (w tym Excel)
- Jedna sekunda, aby przełączać się między dziesiątkami otwartych dokumentów!
- Zmniejsz liczbę kliknięć myszą każdego dnia, pożegnaj się z dłonią myszy.
- Zwiększa produktywność o 50% podczas przeglądania i edytowania wielu dokumentów.
- Wprowadza wydajne karty do pakietu Office (w tym programu Excel), podobnie jak przeglądarki Chrome, Edge i Firefox.
