Wyszukaj najbliższe dopasowanie
Aby wyszukać najbliższe dopasowanie wartości wyszukiwania w liczbowym zestawie danych w programie Excel, możesz użyć INDEKS, MATCH, ABS i MIN działają razem.
Jak znaleźć najbliższe dopasowanie w programie Excel?
Aby wiedzieć, które sprzedawca dokonał sprzedaży najbliższej docelowej 20,000 XNUMX $ jak pokazano powyżej, formuła łącząca funkcje INDEX, MATCH, ABS i MIN pomoże Ci w ten sposób: Funkcja ABS sprawi, że wszystkie różnice między sprzedażą dokonaną przez każdego sprzedawcę a celem sprzedaży będą dodatnie, a następnie MIN znajdzie wartość najmniejsza różnica, co oznacza dopasowanie do szafy. Teraz możemy użyć funkcji MATCH, aby zlokalizować pozycję najbliższego dopasowania i mieć INDEX, aby pobrać wartość w odpowiedniej pozycji.
Ogólna składnia
=INDEX(return_range,MATCH(MIN(ABS(lookup_array-lookup_value)),ABS(lookup_array-lookup_value),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 zwrócić sprzedawcę. Tutaj odnosi się do zakresu nazw.
- szukana_tablica: Zakres komórek z wartościami do porównania z lookup_value. Tutaj odnosi się do zakresu sprzedaży.
- szukana_wartość: Wartość do porównania w celu znalezienia najbliższego dopasowania. Tutaj odnosi się do celu sprzedaży.
Aby wiedzieć, które sprzedawca dokonał sprzedaży najbliższej docelowej 20,000 XNUMX $, skopiuj lub wprowadź poniższą formułę w komórce F5 i naciśnij Ctrl + Shift + Wchodzę aby uzyskać wynik:
=INDEKS(B5: B10, DOPASUJ(MIN(ABS(C5: C10-20000)), ABS (C5: C10-20000),0))
Możesz też użyć odwołania do komórki, aby uczynić formułę dynamiczną:
=INDEKS(B5: B10, DOPASUJ(MIN(ABS(C5: C10-F4)), ABS (C5: C10-F4),0))
Wyjaśnienie formuły
=INDEX(B5:B10,MATCH(MIN(ABS(C5:C10-F4)),ABS(C5:C10-F4),0))
- ABS(C5:C10-F4): Część C5:C10-F4 uzyska wszystkie wartości różnic między każdą sprzedażą w zakresie C5: C10 i docelowa sprzedaż $20,000 w komórce F4 w tablicy takiej jak ta: {-4322;2451;6931;-1113;6591;-4782}. Funkcja ABS zmieni wszystkie liczby ujemne na dodatnie w następujący sposób: {4322;2451;6931;1113;6591;4782}.
- MIN(ABS(C5:C10-F4)) = MIN({4322;2451;6931;1113;6591;4782}): Funkcja MIN znajdzie najmniejszą liczbę z tablicy {4322;2451;6931;1113;6591;4782}, co oznacza najmniejszą różnicę lub mówimy o dopasowaniu do szafy. Więc funkcja wróci 1113.
- MECZ(MIN(ABS(C5:C10-F4)),ABS(C5:C10-F4), 0) = MECZ(1113,{4322;2451;6931;1113;6591;4782}, 0): Połączenia typ_dopasowania 0 wymusza znalezienie przez funkcję MATCH pozycji dokładnej liczby 1113 w tablicy {4322;2451;6931;1113;6591;4782}. Funkcja powróci 4 ponieważ numer jest na 4pozycja.
- INDEKS(B5: B10,MECZ(MIN(ABS(C5:C10-F4)),ABS(C5:C10-F4), 0)) = INDEKS(B5: B10,4): Funkcja INDEX zwraca 4th wartość w zakresie nazw B5: B10, który jest Bela.
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.
Funkcja ABS zwraca wartość bezwzględną liczby. Liczby ujemne zostaną zamienione na dodatnie za pomocą tej funkcji, ale liczby dodatnie i zero pozostaną nienaruszone.
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 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
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.
