Łatwo jest znaleźć wartość za pomocą jednego unikalnego klucza w tabeli. Możemy po prostu użyć funkcji WYSZUKAJ.PIONOWO. Ale jeśli nie masz tej unikalnej kolumny w swoich danych i musisz wyszukiwać w wielu kolumnach, aby dopasować wartość, WYSZUKAJ.PIONOWO nie pomaga.
Tak więc, aby wyszukać wartość w tabeli z wieloma kryteriami, użyjemy formuły INDEKS-DOPASUJ-INDEKS.
Ogólny wzór do wyszukiwania wielu kryteriów
= INDEKS(przeszukaj_zakres,MATCH(1,INDEX((kryterium1 =zakres1)*(kryterium2=zakres2)*(kryteriumN=zakresN),0,1),0))
zakres_wyszukiwania: Jest to zakres, z którego chcesz pobrać wartość.
Kryteria1, Kryteria2, Kryteria N: Są to kryteria, które chcesz dopasować w zakresie 1, zakres 2 i Zakres N. Możesz mieć do 270 kryteriów - par zakresów.
Zakres1, zakres2, zakresN : Są to zakresy, w których dopasujesz swoje kryteria.
Jak to będzie działać? Zobaczmy…
INDEKS i DOPASUJ z przykładem wielu kryteriów
Tutaj mam tabelę danych. Chcę pobrać nazwę klienta za pomocą Daty rezerwacji, Konstruktora i Obszaru. Więc tutaj mam trzy kryteria i jeden zakres wyszukiwania.
Napisz tę formułę w komórce I4 naciśnij enter.
=INDEKS(E2:E16,PODZIEL(1,INDEKS((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1),0))
Jak to działa:
Wiemy już, jak działają funkcje INDEX i MATCH w programie EXCEL, więc nie będę tego tutaj wyjaśniał. Porozmawiamy o sztuczce, której tutaj użyliśmy.
(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16): Główna część jest taka. Każda część tej instrukcji zwraca tablicę wartości prawda fałsz.
Gdy wartości logiczne są mnożone, zwracają tablicę 0 i 1. Mnożenie działa jak operator AND. Zatem, gdy wszystkie wartości są prawdziwe tylko wtedy zwraca 1 w przeciwnym razie 0
(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16) To w ogóle wróci
{FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA;PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ}* {FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA;PRAWDA;PRAWDA;PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA}* {FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ}
Co przełoży się na
{0;0;0;0;0;0;0;1;0;0;0;0;0;0;0}
INDEKS((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1): INDEX Funkcja zwróci tę samą tablicę ({0;0;0;0;0;0;0;1;0;0;0;0;0;0;0}) do funkcji PODAJ.POZYCJĘ jako tablica wyszukiwania.
MECZ(1,INDEKS((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1): Funkcja PODAJ.POZYCJĘ będzie szukać 1 w tablicy {0;0;0;0;0;0 ;0;1;0;0;0;0;0;0;0}. I zwróci numer indeksu pierwszego 1 znalezionego w tablicy. To jest 8 tutaj.
INDEKS(E2:E16,MECZ(1,INDEKS((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1),0)): Na koniec INDEX zwróci wartość z podanego zakresu (E2:E16) w momencie znalezienia indeks (8).
Prosty????. Przepraszamy, nie można tego uprościć.
Rozwiązanie tablicy
Jeśli możesz konsekwentnie nacisnąć CTRL + SHIFT + ENTER, możesz wyeliminować wewnętrzną funkcję INDEX. Po prostu napisz tę formułę i naciśnij CTRL + SHIFT + ENTER.
=INDEKS(E2:E16,PODZIEL(1,(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0))
Ogólna formuła tablicowa do wyszukiwania wielu kryteriów
=INDEKS(przeszukiwany_zakres,MATCH(1,(kryterium1 =zakres1)*(kryterium2=zakres2)*(kryteriumN=zakresN),0))
Formuła działa tak samo jak powyższe wyjaśnienie.
Starałem się jak najprościej wyjaśnić to. Ale jeśli nie byłem wystarczająco jasny, daj mi znać w sekcji komentarzy poniżej. Nawiasem mówiąc, nie musisz wiedzieć, jak działa silnik, aby prowadzić samochód. Musisz tylko wiedzieć, jak nim jeździć. I wiesz o tym bardzo dobrze.
Jak wyszukać 5 najlepszych wartości za pomocą zduplikowanych wartości za pomocą INDEX-MATCH w programie Excel?
Jak WYSZUKAJ.PIONOWO wiele wartości w programie Excel
Jak WYSZUKAJ.PIONOWO z dynamicznym indeksem Col w programie Excel
Jak korzystać z funkcji WYSZUKAJ.PIONOWO z dwóch lub więcej tabel przeglądowych w programie Excel
Popularne artykuły:
50 skrótów Excela, które zwiększą Twoją produktywność
Jak korzystać z funkcji WYSZUKAJ.PIONOWO w programie Excel
Jak korzystać z funkcji LICZ.JEŻELI w programie Excel
Jak korzystać z funkcji SUMA.JEŻELI w programie Excel