Note: The other languages of the website are Google-translated. Back to English

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

indeks pasuje do wielu kolumn 1

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 + przesunięcie + 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 + przesunięcie + 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.

indeks pasuje do wielu kolumn 2

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)))

  • --($C$5:$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(--($C$5:$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(--($C$5:$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(--($C$5:$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 INDEKS

Funkcja Excel INDEX zwraca wyświetlaną wartość na podstawie podanej pozycji z zakresu lub tablicy.

Funkcja Excel MATCH

Funkcja Excel PODAJ.POZYCJĘ wyszukuje określoną wartość w zakresie komórek i zwraca względną pozycję wartości.

Funkcja Excel MMULT

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 TRANSPONUJ Excela

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 Excel COLUMN

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

Chcesz szybko i perfekcyjnie zakończyć swoją codzienną pracę? Kutools dla programu Excel oferuje 300 zaawansowanych zaawansowanych funkcji (Łącz skoroszyty, sumuj według koloru, dziel zawartość komórek, konwertuj daty itd.) i oszczędzaj 80% czasu.

  • Zaprojektowany dla 1500 scenariuszy pracy, pomaga rozwiązać 80% problemów z Excelem.
  • Zmniejsz tysiące kliknięć klawiatury i myszy każdego dnia, odciąż zmęczone oczy i dłonie.
  • Zostań ekspertem Excela w 3 minuty. Nie musisz już pamiętać żadnych bolesnych formuł i kodów VBA.
  • 30-dniowy nieograniczony bezpłatny okres próbny. 60-dniowa gwarancja zwrotu pieniędzy. Bezpłatna aktualizacja i wsparcie przez 2 lata.
Wstążka programu Excel (z zainstalowanym Kutools dla programu Excel)

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 zakładki do pakietu Office (w tym Excel), podobnie jak Chrome, Firefox i nowy Internet Explorer.
Zrzut ekranu programu Excel (z zainstalowaną kartą Office)
Sortuj komentarze według
Komentarze (0)
Brak ocen. Oceń jako pierwszy!
Nie ma tu jeszcze żadnych komentarzy
Zostaw swój komentarz
Publikowanie jako gość
×
Oceń ten post:
0   Postacie
Sugerowane lokalizacje

Bądż na bieżąco

Prawa autorskie © 2009 - www.extendoffice.com. | Wszelkie prawa zastrzeżone. Zasilany przez ExtendOffice, | Mapa strony
Microsoft i logo Office są znakami towarowymi lub zastrzeżonymi znakami towarowymi Microsoft Corporation w Stanach Zjednoczonych i / lub innych krajach.
Chronione przez Sectigo SSL