Przejdź do głównej zawartości

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 maszyn ciężkich 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

Popularne funkcje: Znajdź, wyróżnij lub zidentyfikuj duplikaty  |  Usuń puste wiersze  |  Łącz kolumny lub komórki bez utraty danych  |  Okrągły bez wzoru ...
Super VLookup: Wiele kryteriów  |  Wiele wartości  |  W wielu arkuszach  |  Wyszukiwanie rozmyte...
Adw. Lista rozwijana: Łatwa lista rozwijana  |  Zależna lista rozwijana  |  Lista rozwijana wielokrotnego wyboru...
Menedżer kolumn: Dodaj określoną liczbę kolumn  |  Przesuń kolumny  |  Przełącz stan widoczności ukrytych kolumn  Porównaj kolumny z Wybierz Te same i różne komórki ...
Polecane funkcje: Fokus siatki  |  Widok projektu  |  Duży pasek formuły  |  Menedżer skoroszytów i arkuszy | Biblioteka zasobów (Automatyczny tekst)  |  Selektor dat  |  Połącz arkusze  |  Szyfruj/odszyfruj komórki  |  Wysyłaj e-maile według listy  |  Super filtr  |  Specjalny filtr (filtruj pogrubienie/kursywa/przekreślenie...) ...
15 najlepszych zestawów narzędzi12 Tekst Tools (Dodaj tekst, Usuń znaki ...)  |  50 + Wykres rodzaje (Wykres Gantta ...)  |  40+ Praktyczne Wzory (Oblicz wiek na podstawie urodzin ...)  |  19 Wprowadzenie Tools (Wstaw kod QR, Wstaw obraz ze ścieżki ...)  |  12 Konwersja Tools (Liczby na słowa, Przeliczanie walut ...)  |  7 Połącz i podziel Tools (Zaawansowane wiersze łączenia, Podziel komórki Excela ...)  |  ... i więcej

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.
Comments (1)
Rated 0.5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Keep yrs at "15" and switch major to "science"...formula busts. This is not a robust formula...
Rated 0.5 out of 5
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations