✅ Pronađite najbliže podudaranje u Excelu pomoću formula

Sadržaj

Pronađite najbliže podudaranje u Excelu

Excel funkcije mogu biti izuzetno moćne ako se usudite kombinirati različite formule. Stvari koje bi se mogle činiti nemogućim odjednom bi počele izgledati kao dječja igra.

Jedan od takvih primjera je pronaći najbliže podudaranje vrijednosti pretraživanja u skupu podataka u Excelu.

Postoji nekoliko korisnih funkcija pretraživanja u Excelu (poput VLOOKUP & INDEX MATCH), koje mogu pronaći najbliže podudaranje u nekoliko jednostavnih slučajeva (kao što ću pokazati s primjerima u nastavku).

No, najbolji dio je što ove funkcije pretraživanja možete kombinirati s drugim Excel funkcijama kako biste učinili puno više (uključujući pronalaženje najbližeg podudaranja vrijednosti pretraživanja na nerazvrstanom popisu).

U ovom ću vam vodiču pokazati kako pronalazite najbliže podudaranje vrijednosti pretraživanja u Excelu s formulama za pretraživanje.

Pronađite najbliže podudaranje u Excelu

Može postojati mnogo različitih scenarija u kojima morate tražiti najbliže podudaranje (ili najbližu odgovarajuću vrijednost).

Ispod su primjeri koje ću prikazati u ovom članku:

Pronađite proviziju na temelju prodaje
Pronađite najboljeg kandidata (na temelju najbližeg iskustva)
Pronalaženje datuma sljedećeg događaja

Započnimo!

Kliknite ovdje za preuzimanje datoteke primjera

Pronađi stopu provizije (traži se najbliža prodajna vrijednost)

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku gdje želite pronaći stope provizije za sve prodajno osoblje.

Provizija se dodjeljuje na temelju prodajne vrijednosti. A to se izračunava pomoću tablice s desne strane.

Na primjer, ako prodavač ostvari ukupnu prodaju od 5000, tada je provizija 0%, a ako on/ona obavi ukupnu prodaju od 15000, onda je provizija 5%.

Da biste dobili proviziju, morate pronaći najbliži raspon prodaje samo niži od prodajne vrijednosti. Na primjer, za prodajnu vrijednost od 15000, provizija bi bila 10.000 (što je 5%), a za prodajnu vrijednost od 25000, provizija bi bila 20.000 (što je 7%).

Da biste pronašli najbližu vrijednost prodaje i dobili proviziju, možete upotrijebiti približno podudaranje u VLOOKUP -u.

Formula u nastavku bi to učinila:

= VLOOKUP (B2, $ E $ 2: $ F $ 6,2,1)

Imajte na umu da je u ovoj formuli posljednji argument 1 koji govori formuli da koristi približno pretraživanje. To znači da bi formula prošla kroz prodajne vrijednosti u stupcu E i pronašla vrijednost koja je samo niža od tražene vrijednosti.

Tada će formula VLOOKUP dati proviziju za ovu vrijednost.

Bilješka: Da bi ovo funkcioniralo, podatke morate sortirati uzlazno.

Kliknite ovdje za preuzimanje datoteke primjera

Pronađite najboljeg kandidata (na temelju najbližeg iskustva)

U gornjem primjeru podatke je potrebno sortirati uzlaznim redoslijedom. No mogli bi postojati slučajevi u kojima se podaci ne razvrstavaju.

Pa pokrijmo primjer i vidimo kako možemo pronaći najbliže podudaranje u Excelu pomoću kombinacije formula.

Ispod je uzorak skupova podataka u kojima moram pronaći ime zaposlenika koje ima radno iskustvo najbliže željenoj vrijednosti. Željena vrijednost u ovom slučaju za 2,5 godine.

Imajte na umu da se podaci ne razvrstavaju. Također, najbliže iskustvo može biti manje ili više od danog iskustva. Na primjer, 2 i 3 godine su jednako bliske (razlika od 0,5 godina).

Ispod je formula koja će nam dati rezultat:

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

Trik u ovoj formuli je promijeniti matricu pretraživanja i vrijednost traženja kako bi se pronašla minimalna razlika u iskustvu u potrebnim i stvarnim vrijednostima.

Prvo shvatimo kako biste to učinili ručno (a zatim ću objasniti kako ova formula radi).

Kad to radite ručno, proći ćete kroz svaku ćeliju u stupcu B i pronaći razliku u iskustvu između onoga što je potrebno i onog koje osoba ima. Nakon što savladate sve razlike, pronaći ćete onu koja je minimalna i dohvatiti ime te osobe.

Upravo to radimo s ovom formulom.

Dopustite mi da objasnim.

Tražena vrijednost u formuli MATCH je MIN (ABS (D2-B2: B15)).

Ovaj dio daje minimalnu razliku između danog iskustva (koje iznosi 2,5 godine) i svih ostalih iskustava. U ovom primjeru vraća 0,3

Imajte na umu da sam upotrijebio ABS kako bih bio siguran da tražim najbliže (što može biti više ili manje od navedenog iskustva).

Sada ova minimalna vrijednost postaje naša tražilica.

Niz traženja u funkciji MATCH je ABS (D2- $ B $ 2: $ B $ 15).

To nam daje niz brojeva od kojih je oduzeto 2,5 (potrebno iskustvo).

Dakle, sada imamo vrijednost traženja (0,3) i niz traženja ({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})

MATCH funkcija nalazi poziciju 0,3 u ovom nizu, što je ujedno i pozicija imena osobe koja ima najbliže iskustvo.

Taj broj pozicije zatim koristi funkcija INDEX za vraćanje imena osobe.

Napomena: U slučaju da ima više kandidata s istim minimalnim iskustvom, gornja formula će dati ime prvog zaposlenika koji se podudara.

Pronađite datum sljedećeg događaja

Ovo je još jedan primjer gdje možete koristiti formule za traženje da biste pronašli sljedeći datum događaja na temelju trenutnog datuma.

Ispod je skup podataka u kojem imam nazive događaja i datume događaja.

Ono što želim je naziv sljedećeg događaja i datum događaja za ovaj nadolazeći događaj.

Ispod je formula koja će dati naziv nadolazećeg događaja:

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

I ispod formula daje datum nadolazećeg događaja:

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

Dopustite mi da objasnim kako ova formula radi.

Da bi dobila datum događaja, funkcija MATCH traži trenutni datum u stupcu B. U ovom slučaju ne tražimo točno podudaranje, već približan. Stoga je posljednji argument funkcije MATCH 1 (koja pronalazi najveću vrijednost koja je manja ili jednaka vrijednosti pretraživanja).

Tako bi funkcija MATCH vratila položaj ćelije koja ima datum koji je samo manji ili jednak trenutnom datumu. Dakle, sljedeći događaj, u ovom slučaju, bio bi u sljedećoj ćeliji (budući da je popis poredan uzlaznim redoslijedom).

Dakle, da biste dobili nadolazeći datum događaja, samo dodajte jedan u položaj ćelije koji vraća funkcija MATCH, i dobit ćete položaj ćelije sljedećeg datuma događaja.

Tu vrijednost tada daje funkcija INDEX.

Za dobivanje naziva događaja koristi se ista formula, a raspon u funkciji INDEX mijenja se iz stupca B u stupac A.

Kliknite ovdje za preuzimanje datoteke primjera

Ideja o ovom primjeru sinula mi je kad mi se prijatelj obratio sa zahtjevom. U koloni je imao popis svih rođendana svojih prijatelja/rodbine i želio je znati sljedeći rođendan koji dolazi (i ime osobe).

Ovo su tri primjera koji pokazuju kako pronaći najbližu odgovarajuću vrijednost u Excelu pomoću formula za pretraživanje.

Možda će vam se svidjeti i sljedeći Excel savjeti/vodiči