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 INDEKS, MATCH i IF funkcje, można to szybko zrobić w programie Excel.
Jak wyszukać najbliższą wartość dopasowania z więcej niż jednym kryterium?
Jak pokazano na poniższym zrzucie ekranu, musisz znaleźć odpowiednią osobę na dane stanowisko w oparciu o dwa kryteria „głównym jest Komputer”I„ doświadczenie zawodowe 15 lat ”.
Note: Aby to działało poprawnie, jeśli istnieją zduplikowane kierunki, doświadczenia zawodowe tych zduplikowanych kierunków należy posortować w kolejności rosnącej.
1. Wybierz pustą komórkę, aby wyświetlić wynik, a następnie skopiuj do niej poniższą formułę i naciśnij Ctrl + Shift + Wchodzę klucze, aby uzyskać wynik.
=INDEX(D3:D8,MATCH(G5,IF(B3:B8=G4,C3:C8),1))
Uwagi: w tym wzorze:
- D3: D8 to zakres kolumn zawiera wynik, którego szukasz;
- G5 zawiera drugie kryterium (doświadczenie numer 15), na podstawie którego będziesz szukać wartości;
- G4 zawiera pierwsze kryterium (komputer), na podstawie którego będziesz szukać wartości;
- B3: B8 jest zakresem komórek pasujących do pierwszego kryterium;
- C3: C8 jest zakresem komórek pasujących do drugiego kryterium;
- Numer 1 jest przybliżonym wyszukiwaniem, co oznacza, że jeśli nie można znaleźć dokładnej wartości, znajdzie największą wartość, która jest mniejsza niż wartość wyszukiwania;
- Tę formułę należy wprowadzić jako formułę tablicową z rozszerzeniem Ctrl + Shift + Wchodzę klawiatura.
Jak działa ta formuła
Ten wzór można podzielić na kilka elementów:
- IF(B3:B8=G4,C3:C8): funkcja JEŻELI w tym miejscu zwraca wynik jako {9;13;FALSE;FALSE;FALSE;FALSE}, który pochodzi z testowania wartości w B3: B8 w celu sprawdzenia, czy pasują do wartości w G4. Jeśli istnieje dopasowanie, zwraca odpowiednią wartość, w przeciwnym razie zwraca FALSE. Tutaj znajduje dwa dopasowania i cztery niezgodności.
- Formuła tablicowa =MATCH(G5,{9;13;FALSE;FALSE;FALSE;FALSE},1): funkcja MATCH znajduje pozycję liczby 15 (wartość w G5) w zakresie C3: C8. Ponieważ nie można znaleźć liczby 15, pasuje ona do kolejnej najmniejszej wartości 13. Zatem wynik to 2.
- oraz =INDEX(D3:D8,2): Funkcja INDEKS zwraca wartość drugiej komórki w zakresie D3: D8. Więc ostateczny wynik to Amy.
Powiązane funkcje
Funkcja Excel IF
Funkcja JEŻELI jest jedną z najprostszych i najbardziej przydatnych funkcji w skoroszycie programu Excel. Wykonuje prosty test logiczny, który zależy od wyniku porównania i zwraca jedną wartość, jeśli wynik jest PRAWDA, lub inną wartość, jeśli wynik jest FAŁSZ.
Funkcja Excel MATCH
Funkcja Microsoft Excel MATCH wyszukuje określoną wartość w zakresie komórek i zwraca względną pozycję tej wartości.
Funkcja Excel INDEKS
Funkcja INDEKS zwraca wyświetlaną wartość na podstawie podanej pozycji z zakresu lub tablicy.
Powiązane artykuły
Średnia liczba komórek na podstawie wielu kryteriów
W programie Excel większość z nas może znać funkcje LICZ.JEŻELI i SUMA.JEŻELI, mogą one pomóc nam zliczać lub sumować wartości na podstawie kryteriów. Ale czy kiedykolwiek próbowałeś obliczyć średnią wartości na podstawie co najmniej jednego kryterium w programie Excel? Ten samouczek zawiera szczegółowe przykłady i formuły, które ułatwiają wykonanie tego.
Kliknij, aby dowiedzieć się więcej ...
Policz komórki, jeśli spełniło jedno z wielu kryteriów
W tym samouczku opisano sposoby liczenia komórek zawierających X, Y lub Z… itd. W programie Excel.
Kliknij, aby dowiedzieć się więcej ...
Policz unikalne wartości na podstawie wielu kryteriów
W tym artykule przedstawiono kilka przykładów liczenia unikatowych wartości na podstawie co najmniej jednego kryterium w arkuszu ze szczegółowymi metodami krok po kroku.
Kliknij, aby dowiedzieć się więcej ...
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.
