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żesz użyć formuły tablicowej z the INDEKS i MATCH fnamaszczenia.
Jak przeprowadzić wyszukiwanie z wieloma kryteriami?
Aby dowiedzieć się produkt to jest biały i średni-wielkości z ceną $18 jak pokazano na powyższym obrazku, możesz skorzystać z logiki logicznej, aby wygenerować tablicę jedynek i zer, aby pokazać wiersze spełniające kryteria. Funkcja PODAJ.POZYCJĘ znajdzie następnie pozycję pierwszego wiersza, który spełnia wszystkie kryteria. Następnie INDEX znajdzie odpowiedni identyfikator produktu w tym samym wierszu.
Ogólna składnia
=INDEX(return_range,MATCH(1,(criteria_value1=criteria_range1*criteria_value2=criteria_range2*(…),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 kombinacji ma zwracać identyfikator produktu. Tutaj odnosi się do zakresu identyfikatorów produktów.
- wartość_kryteriów: Kryteria używane do zlokalizowania pozycji identyfikatora produktu. Tutaj odnosi się do wartości w komórkach H4, H5 i H6.
- zakres_kryteriów: Odpowiednie zakresy, w których kryteria_wartości są wymienione. Tutaj odnosi się do kolorów, rozmiarów i przedziałów cenowych.
- typ_dopasowania 0: Wymusza MATCH, aby znaleźć pierwszą wartość, która jest dokładnie równa lookup_value.
Aby znaleźć produkt, który jest biały i średni-wielkości z ceną $18, skopiuj lub wprowadź poniższą formułę w komórce H8 i naciśnij Ctrl + Shift + Wchodzę aby uzyskać wynik:
=INDEKS(B5: B10, DOPASUJ(1,("Biały"=C5: C10) * ("Średni"=D5: D10) * (18=E5: E10),0))
Możesz też użyć odwołania do komórki, aby uczynić formułę dynamiczną:
=INDEKS(B5: B10, DOPASUJ(1,(H4=C5: C10) * (H5=D5: D10) * (H6=E5: E10),0))
Wyjaśnienie formuły
=INDEX(B5:B10,MATCH(1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))
- (H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10): Formuła porównuje kolor w komórce H4 na tle wszystkich kolorów w asortymencie C5: C10; porównuje rozmiar w H5 przeciwko wszystkim rozmiarom w D5: D10; porównuje cenę w H6 w stosunku do wszystkich cen w E5: E10. Początkowy wynik wygląda tak:
{PRAWDA;FAŁSZ;PRAWDA;FAŁSZ;PRAWDA;FAŁSZ}*{FAŁSZ;FAŁSZ;PRAWDA;PRAWDA;PRAWDA;FAŁSZ}*{FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA;PRAWDA;FAŁSZ}.
Mnożenie przekształci PRAWDA i FAŁSZ na jedynki i zera:
{1;0;1;0;1;0}*{0;0;1;1;1;0}*{0;0;0;1;1;0}.
Po mnożeniu otrzymamy jedną tablicę taką jak ta:
{0;0;0;0;1;0}. - DOPASUJ(1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10), 0) = DOPASUJ(1,{0;0;0;0;1;0}, 0): Typ dopasowania 0 prosi funkcję MATCH o znalezienie dokładnego dopasowania. Funkcja zwróci wtedy pozycję 1 w tablicy {0;0;0;0;1;0}, który jest 5.
- INDEKS(B5: B10,DOPASUJ(1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10), 0)) = INDEKS(B5: B10,5): Funkcja INDEX zwraca 5th wartość w zakresie identyfikatorów produktu B5: B10, który jest 30005.
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.
Powiązane formuły
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.
Przybliżone dopasowanie z INDEX i MATCH
Czasami musimy znaleźć przybliżone dopasowania w programie Excel, aby ocenić wydajność pracowników, ocenić wyniki uczniów, obliczyć opłatę pocztową na podstawie wagi itp. W tym samouczku omówimy, jak używać funkcji INDEKS i DOPASUJ do pobierania wyniki, których potrzebujemy.
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 przeglądania z innego arkusza lub skoroszytu nie będą dla Ciebie problemem. Samouczek pokaże Ci, jak przeglądać wartości z innego arkusza roboczego 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.
