INDEKS i DOPASUJ w wielu kolumnach
Aby wyszukać wartość przez dopasowanie w wielu kolumnach, formuła tablicowa oparta na INDEKS i MATCH funkcje, które zawiera WIELE, TRANSPONOWAĆ i KOLUMNA zrobi ci przysługę.
Jak wyszukać wartość, dopasowując wiele kolumn?
Aby wypełnić odpowiednia klasa każdego ucznia jak pokazano w powyższej tabeli, gdzie informacje są wymienione w wielu kolumnach, możesz najpierw użyć sztuczki funkcji MMULT, TRANSPOSE i COLUMN, aby utworzyć macierz macierzy. Następnie funkcja MATCH poda pozycję wartości wyszukiwania, która zostanie przekazana do funkcji INDEX w celu pobrania wartości, której szukasz w tablicy.
Ogólna składnia
=INDEX(return_range,(MATCH(1,MMULT(--(lookup_array=lookup_value),TRANSPOSE(COLUMN(lookup_array)^0)),0)))
√ Uwaga: To jest formuła tablicowa, która wymaga wprowadzenia za pomocą Ctrl + Shift + Wchodzę.
- zakres_zwrotów: Zakres, z którego formuła ma zwracać informacje o klasie. Tutaj odnosi się do zakresu klas.
- szukana_wartość: Wartość użyta przez formułę do znalezienia odpowiedniej informacji o klasie. Tutaj odnosi się do podanego imienia.
- szukana_tablica: Zakres komórek, w których lookup_value znajduje się na liście; Zakres z wartościami do porównania z lookup_value. Tutaj odnosi się do zakresu nazw.
- typ_dopasowania 0: Wymusza MATCH, aby znaleźć pierwszą wartość, która jest dokładnie równa lookup_value.
Aby znaleźć klasa Jimmy'ego, skopiuj lub wprowadź poniższą formułę w komórce H5 i naciśnij Ctrl + Shift + Wchodzę aby uzyskać wynik:
=INDEKS(5 B $: 7 B $,(MATCH(1,MMULT(--()5 $ C$: 7 $ E $=G5),TRANSPOZYCJA(KOLUMNA(5 $ C$: 7 $ E $)^0)),0))))
√ 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. Zauważ, że nie należy dodawać znaków dolara do odwołania do komórki, które reprezentuje wartość wyszukiwania, ponieważ chcesz, aby była ona względna podczas kopiowania jej do innych komórek. Po wprowadzeniu formuły przeciągnij uchwyt wypełniania w dół, aby zastosować formułę do poniższych komórek.
Wyjaśnienie formuły
=INDEX($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0)))
- --(5 $ C $: $ E $ 7 = G5): Ten segment sprawdza każdą wartość w zakresie 5 $ C$: 7 $ E $ jeśli są równe wartości w komórce G5 i generuje tablicę PRAWDA i FAŁSZ w następujący sposób:
{PRAWDA,FAŁSZ,FAŁSZ;FAŁSZ,FAŁSZ,FAŁSZ;FAŁSZ,FAŁSZ,FAŁSZ}.
Podwójny ujem zamieni następnie TRUE i FALSE na jedynki i zera, aby uzyskać tablicę taką jak ta:
{1,0,0; 0,0,0; 0,0,0}. - KOLUMNA($C$5:$E$7): Funkcja COLUMN zwraca numery kolumn dla zakresu 5 $ C$: 7 $ E $ w tablicy takiej jak ta: 3,4,5 {}.
- TRANSPONOWAĆ(KOLUMNA($C$5:$E$7)^ 0) = TRANSPONOWAĆ(3,4,5 {}^ 0): Po podniesieniu potęgi do 0 wszystkie liczby w tablicy {3,4,5} zostaną przekonwertowane na 1: {1,1,1}. Funkcja TRANSPONUJ następnie konwertuje tablicę kolumn na tablicę wierszy w następujący sposób: {1; 1; 1}.
- MULT(--(5 $ C $: $ E $ 7 = G5),TRANSPONOWAĆ(KOLUMNA($C$5:$E$7)^ 0)) = MULT({1,0,0; 0,0,0; 0,0,0},{1; 1; 1}): Funkcja MMULT zwraca iloczyn macierzy dwóch tablic w następujący sposób: {1; 0; 0}.
- DOPASUJ(1,MULT(--(5 $ C $: $ E $ 7 = G5),TRANSPONOWAĆ(KOLUMNA($C$5:$E$7)^ 0)), 0) = DOPASUJ(1,{1; 0; 0}, 0): Typ dopasowania 0 wymusza, aby funkcja MATCH zwróciła pozycję pierwszego dopasowania z 1 w tablicy {1; 0; 0}, który jest 1.
- INDEKS(5 B $: 7 B $,(DOPASUJ(1,MULT(--(5 $ C $: $ E $ 7 = G5),TRANSPONOWAĆ(KOLUMNA($C$5:$E$7)^ 0)), 0))) = INDEKS(5 B $: 7 B $,1): Funkcja INDEX zwraca 1st wartość w zakresie klas 5 B $: 7 B $, który jest A.
Aby łatwo wyszukać wartość, dopasowując wiele kolumn, możesz również skorzystać z naszego profesjonalnego dodatku do programu Excel Kutools dla programu Excel. Zobacz instrukcję tutaj, aby wykonać misję.
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 Excel MMULT zwraca iloczyn macierzy dwóch tablic. Wynik tablicy ma taką samą liczbę wierszy jak tablica1 i taką samą liczbę kolumn jak tablica2.
Funkcja Excel TRANSPOSE obraca orientację zakresu lub tablicy. Na przykład może obracać tabelę ułożoną poziomo w rzędach do pionowej w kolumnach lub odwrotnie.
Funkcja COLUMN zwraca numer kolumny, w której pojawia się formuła, lub zwraca numer kolumny danego odwołania. Na przykład formuła =KOLUMNA(BD) zwraca 56.
Powiązane formuły
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Ę.
Dwukierunkowe wyszukiwanie z INDEX i MATCH
Aby wyszukać coś zarówno w wierszach, jak i kolumnach w programie Excel, lub mówimy, aby wyszukać wartość na przecięciu określonego wiersza i kolumny, możemy skorzystać z pomocy funkcji INDEKS i PODAJ.POZYCJĘ.
Wyszukaj najbliższą wartość dopasowania z wieloma kryteriami
W niektórych przypadkach może być konieczne wyszukanie najbliższej lub przybliżonej wartości dopasowania na podstawie więcej niż jednego kryterium. Dzięki połączeniu funkcji INDEKS, DOPASUJ i JEŻELI możesz szybko wykonać to w programie Excel.
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.
