✅ Znajdź najbliższe dopasowanie w Excelu za pomocą formuł | Wskazówki dotyczące programu Excel 2025

Spisie treści

Znajdź najbliższy odpowiednik w Excelu

Funkcje programu Excel mogą być niezwykle wydajne, jeśli opanujesz łączenie różnych formuł. Rzeczy, które mogły wydawać się niemożliwe, nagle zaczęły wyglądać jak dziecięca zabawa.

Jednym z takich przykładów jest znalezienie najbliższego dopasowania wartości wyszukiwania w zestawie danych w programie Excel.

Istnieje kilka przydatnych funkcji wyszukiwania w programie Excel (takich jak WYSZUKAJ.PIONOWO I DOPASOWANIE INDEKSU), które mogą znaleźć najbliższe dopasowanie w kilku prostych przypadkach (jak pokażę na poniższych przykładach).

Ale najlepsze jest to, że możesz połączyć te funkcje wyszukiwania z innymi funkcjami programu Excel, aby zrobić o wiele więcej (w tym znaleźć najbliższe dopasowanie wartości wyszukiwania na nieposortowanej liście).

W tym samouczku pokażę, jak znaleźć najbliższe dopasowanie wartości wyszukiwania w programie Excel za pomocą formuł wyszukiwania.

Znajdź najbliższy odpowiednik w Excelu

Może istnieć wiele różnych scenariuszy, w których trzeba szukać najbliższego dopasowania (lub najbliższej pasującej wartości).

Poniżej znajdują się przykłady, które omówię w tym artykule:

Znajdź stawkę prowizji na podstawie sprzedaży
Znajdź najlepszego kandydata (na podstawie najbliższego doświadczenia)
Znalezienie następnej daty wydarzenia

Zacznijmy!

Kliknij tutaj, aby pobrać przykładowy plik

Znajdź stawkę prowizji (poszukiwanie najbliższej wartości sprzedaży)

Załóżmy, że masz zestaw danych, jak pokazano poniżej, w którym chcesz znaleźć stawki prowizji dla wszystkich pracowników sprzedaży.

Prowizja naliczana jest na podstawie wartości sprzedaży. A to jest obliczane na podstawie tabeli po prawej stronie.

Na przykład, jeśli sprzedawca dokona całkowitej sprzedaży 5000, to prowizja wynosi 0%, a jeśli dokona całkowitej sprzedaży 15000, to prowizja wynosi 5%.

Aby uzyskać stawkę prowizji, musisz znaleźć najbliższy zakres sprzedaży nieco niższy niż Wartość sprzedaży. Na przykład przy sprzedaży 15000 prowizja wyniesie 10000 (czyli 5%), a przy wartości sprzedaży 25000 prowizja wyniesie 20000 (czyli 7%).

Aby znaleźć najbliższą wartość sprzedaży i uzyskać stawkę prowizji, możesz użyć przybliżonego dopasowania w funkcji WYSZUKAJ.PIONOWO.

Poniższa formuła zrobiłaby to:

=WYSZUKAJ.PIONOWO(B2,$E$2:$F$6,2,1)

Zauważ, że w tej formule ostatnim argumentem jest 1, co oznacza, że formuła ma użyć przybliżonego wyszukiwania. Oznacza to, że formuła przejdzie przez wartości sprzedaży w kolumnie E i znajdzie wartość, która jest tylko niższa niż wartość wyszukiwania.

Następnie formuła WYSZUKAJ.PIONOWO poda stawkę prowizji dla tej wartości.

Notatka: Aby to zadziałało, musisz mieć dane posortowane w porządku rosnącym.

Kliknij tutaj, aby pobrać przykładowy plik

Znajdź najlepszego kandydata (na podstawie najbliższego doświadczenia)

W powyższym przykładzie dane należało posortować w kolejności rosnącej. Ale mogą wystąpić przypadki, w których dane nie są sortowane.

Przyjrzyjmy się więc przykładowi i zobaczmy, jak możemy znaleźć najbliższe dopasowanie w programie Excel za pomocą kombinacji formuł.

Poniżej znajduje się przykładowy zestaw danych, w którym muszę znaleźć nazwisko pracownika, którego doświadczenie zawodowe jest najbliższe pożądanej wartości. Pożądana wartość w tym przypadku za 2,5 roku.

Zauważ, że dane nie są sortowane. Również najbliższe doświadczenie może być mniejsze lub większe niż doświadczenie dające. Na przykład 2 lata i 3 lata są równie bliskie (różnica 0,5 roku).

Poniżej formuła, która da nam wynik:

=INDEKS($A$2:$A$15,DOPASOWANIE(MIN(ABS(D2-B2:B15));ABS(D2-$B$2:$B$15);0))

Sztuczka w tej formule polega na zmianie tablicy wyszukiwania i wartości wyszukiwania, aby znaleźć minimalną różnicę doświadczenia w wartościach wymaganych i rzeczywistych.

Najpierw zrozummy, jak zrobiłbyś to ręcznie (a potem wyjaśnię, jak działa ta formuła).

Robiąc to ręcznie, przejdziesz przez każdą komórkę w kolumnie B i znajdziesz różnicę w doświadczeniu między tym, co jest wymagane, a tym, które ma dana osoba. Kiedy już masz wszystkie różnice, znajdziesz tę, która jest minimalna i pobierzesz imię tej osoby.

Dokładnie to robimy z tą formułą.

Pozwól mi wyjaśnić.

Wartość wyszukiwania w formule PODAJ.POZYCJĘ to MIN(ABS(D2-B2:B15)).

Ta część podaje minimalną różnicę między danym doświadczeniem (czyli 2,5 roku) a wszystkimi innymi doświadczeniami. W tym przykładzie zwraca 0,3

Zauważ, że użyłem ABS, aby upewnić się, że szukam najbliższego (co może być mniej więcej niż podane doświadczenie).

Teraz ta minimalna wartość staje się naszą wartością wyszukiwania.

Tablica wyszukiwania w funkcji PODAJ.POZYCJĘ to ABS(D2-$B$2:$B$15).

Daje nam to tablicę liczb, od których odjęto 2,5 (wymagane doświadczenie).

Więc teraz mamy wartość wyszukiwania (0,3) i tablicę wyszukiwania ({6,8;0,8;19,5;21,8;14,5;11,2;0,3;9,2;2;9,8;14,8;0,4;23,8;2,9})

Funkcja MATCH znajduje w tej tablicy pozycję 0.3, która jest jednocześnie pozycją imienia osoby, która ma najbliższe doświadczenie.

Ten numer pozycji jest następnie używany przez funkcję INDEX do zwrócenia nazwiska osoby.

Uwaga: W przypadku kilku kandydatów z takim samym minimalnym doświadczeniem, powyższa formuła poda nazwisko pierwszego pasującego pracownika.

Znajdź datę następnego wydarzenia

To kolejny przykład, w którym możesz użyć formuł wyszukiwania, aby znaleźć następną datę wydarzenia na podstawie bieżącej daty.

Poniżej znajduje się zbiór danych, w którym mam nazwy wydarzeń i daty wydarzeń.

To, czego chcę, to nazwa następnego wydarzenia i data wydarzenia dla tego nadchodzącego wydarzenia.

Poniżej znajduje się formuła, która da nazwę nadchodzącego wydarzenia:

=INDEKS($A$2:$A$11,DOPASOWANIE(E1,$B$2:$B$11,1)+1)

A poniżej formuła poda datę nadchodzącego wydarzenia:

=INDEKS($B$2:$B$11,DOPASOWANIE(E1,$B$2:$B$11,1)+1)

Pozwólcie, że wyjaśnię, jak działa ta formuła.

Aby uzyskać datę zdarzenia, funkcja MATCH wyszukuje bieżącą datę w kolumnie B. W tym przypadku nie szukamy dokładnego dopasowania, ale przybliżone. A zatem ostatnim argumentem funkcji PODAJ.POZYCJĘ to 1 (co powoduje znalezienie największej wartości, która jest mniejsza lub równa wartości wyszukiwania).

Tak więc funkcja PODAJ.POZYCJĘ zwróci pozycję komórki, której data jest mniejsza lub równa bieżącej dacie. Zatem następne zdarzenie, w tym przypadku, będzie znajdować się w następnej komórce (ponieważ lista jest posortowana w porządku rosnącym).

Aby uzyskać datę nadchodzącego wydarzenia, po prostu dodaj jeden do pozycji komórki zwróconej przez funkcję MATCH, a otrzymasz pozycję komórki z datą następnego wydarzenia.

Ta wartość jest następnie podawana przez funkcję INDEKS.

Aby uzyskać nazwę zdarzenia, używana jest ta sama formuła, a zakres w funkcji INDEX jest zmieniany z kolumny B na kolumnę A.

Kliknij tutaj, aby pobrać przykładowy plik

Pomysł na ten przykład przyszedł mi do głowy, gdy przyjaciel przyszedł z prośbą. Miał w kolumnie listę urodzin wszystkich swoich znajomych/krewnych i chciał wiedzieć, kiedy zbliżają się następne urodziny (i imię osoby).

Oto trzy przykłady, które pokazują, jak znaleźć najbardziej pasującą wartość w programie Excel za pomocą formuł wyszukiwania.

Możesz również polubić następujące porady / samouczki dotyczące programu Excel