✅ Poiščite najbližje ujemanje v Excelu z uporabo formul

Kazalo

Poiščite najbližje ujemanje v Excelu

Excelove funkcije so lahko izjemno zmogljive, če kombinirate različne formule. Stvari, ki bi se zdele nemogoče, bi nenadoma začele izgledati kot otroška igra.

Eden takih primerov je najti najbližje ujemanje iskalne vrednosti v nizu podatkov v Excelu.

V Excelu je nekaj uporabnih iskalnih funkcij (na primer VLOOKUP & INDEX MATCH), ki lahko najdejo najbližje ujemanje v nekaj preprostih primerih (kot bom pokazal s spodnjimi primeri).

Najboljši del pa je, da lahko te iskalne funkcije združite z drugimi Excelovimi funkcijami, da naredite veliko več (vključno z iskanjem najbližjega ujemanja iskalne vrednosti na nerazvrščenem seznamu).

V tej vadnici vam bom pokazal, kako z Excelovimi formulami poiščete najbližje ujemanje iskalne vrednosti v Excelu.

Poiščite najbližje ujemanje v Excelu

Obstaja lahko veliko različnih scenarijev, kjer morate poiskati najbližje ujemanje (ali najbližjo ujemajočo se vrednost).

Spodaj so primeri, ki jih bom obravnaval v tem članku:

Poiščite stopnjo provizije na podlagi prodaje
Poiščite najboljšega kandidata (glede na najbližje izkušnje)
Iskanje datuma naslednjega dogodka

Začnimo!

Kliknite tukaj, če želite prenesti primer datoteke

Poiščite obrestno mero provizije (iščete najbližjo prodajno vrednost)

Recimo, da imate nabor podatkov, kot je prikazano spodaj, kjer želite najti stopnje provizije za vse prodajno osebje.

Provizija se dodeli glede na prodajno vrednost. In to se izračuna s pomočjo tabele na desni.

Na primer, če prodajalec opravi skupno prodajo 5000, potem je provizija 0%, če pa on/ona opravi skupno prodajo 15000, je provizija 5%.

Če želite dobiti provizijo, morate najti najbližji prodajni obseg, ki je nižji od prodajne vrednosti. Na primer, za prodajno vrednost 15000 bi bila provizija 10.000 (kar je 5%), za prodajno vrednost 25000 pa bi bila provizija 20.000 (kar je 7%).

Če želite najti najbližjo prodajno vrednost in dobiti provizijo, lahko uporabite približno ujemanje v VLOOKUP -u.

Spodnja formula bi to naredila:

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

Upoštevajte, da je v tej formuli zadnji argument 1, ki formuli pove, naj uporabi približen iskanje. To pomeni, da bi formula šla skozi prodajne vrednosti v stolpcu E in našla vrednost, ki je le nižja od iskalne vrednosti.

Potem bo formula VLOOKUP dala provizijo za to vrednost.

Opomba: Da bi to delovalo, morate podatke razvrstiti po naraščajočem vrstnem redu.

Kliknite tukaj, če želite prenesti primer datoteke

Poiščite najboljšega kandidata (glede na najbližje izkušnje)

V zgornjem primeru je bilo treba podatke razvrstiti po naraščajočem vrstnem redu. Lahko pa pride do primerov, ko podatki niso razvrščeni.

Zato pokrijmo primer in poglejmo, kako lahko s kombinacijo formul najdemo najbližje ujemanje v Excelu.

Spodaj je vzorčni niz podatkov, kjer moram najti ime zaposlenega, ki ima delovne izkušnje najbližje želeni vrednosti. Želena vrednost v tem primeru v 2,5 letih.

Upoštevajte, da podatki niso razvrščeni. Prav tako je lahko najbližja izkušnja manjša ali večja od tiste, ki jo damo. Na primer, dve in tri leta sta si enako blizu (razlika 0,5 leta).

Spodaj je formula, ki nam bo dala rezultat:

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

Trik v tej formuli je spremeniti matriko iskanja in iskalno vrednost, da bi našli najmanjšo razliko izkušenj v zahtevanih in dejanskih vrednostih.

Najprej razumemo, kako bi to storili ročno (nato bom razložil, kako deluje ta formula).

Ko to počnete ročno, boste pregledali vsako celico v stolpcu B in ugotovili razliko v izkušnjah med zahtevanim in tistim, ki ga ima oseba. Ko boste ugotovili vse razlike, boste našli tisto, ki je minimalna, in dobili ime te osebe.

Ravno to počnemo s to formulo.

Naj razložim.

Iskalna vrednost v formuli MATCH je MIN (ABS (D2-B2: B15)).

Ta del vam daje minimalno razliko med danimi izkušnjami (to je 2,5 leta) in vsemi drugimi izkušnjami. V tem primeru vrne 0,3

Upoštevajte, da sem uporabil ABS, da bi se prepričal, da iščem najbližjega (kar je lahko več ali manj od danih izkušenj).

Zdaj ta najmanjša vrednost postane naša iskalna vrednost.

Iskalno polje v funkciji MATCH je ABS (D2- $ B $ 2: $ B $ 15).

Tako dobimo niz številk, od katerih smo odšteli 2,5 (zahtevane izkušnje).

Zdaj imamo iskalno vrednost (0,3) in iskalno polje ({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})

Funkcija MATCH najde položaj 0,3 v tem nizu, kar je tudi položaj imena osebe, ki ima najbližje izkušnje.

To številko položaja nato funkcija INDEX uporabi za vrnitev imena osebe.

Opomba: Če obstaja več kandidatov z enakimi minimalnimi izkušnjami, bo zgornja formula podala ime prvega ustreznega zaposlenega.

Poiščite datum naslednjega dogodka

To je še en primer, kjer lahko z iskalnimi formulami poiščete naslednji datum dogodka na podlagi trenutnega datuma.

Spodaj je niz podatkov, kjer imam imena dogodkov in datume dogodkov.

Želim si ime naslednjega dogodka in datum dogodka za ta prihajajoči dogodek.

Spodaj je formula, ki bo dala ime prihajajočega dogodka:

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

Spodnja formula bo dala datum prihajajočega dogodka:

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

Naj razložim, kako deluje ta formula.

Če želite dobiti datum dogodka, funkcija MATCH išče trenutni datum v stolpcu B. V tem primeru ne iščemo natančnega ujemanja, ampak približnega. In zato je zadnji argument funkcije MATCH 1 (ki najde največjo vrednost, ki je manjša ali enaka iskalni vrednosti).

Tako bi funkcija MATCH vrnila položaj celice z datumom, ki je le manjši ali enak trenutnemu datumu. Torej bi bil naslednji dogodek v tem primeru v naslednji celici (saj je seznam razvrščen po naraščajočem vrstnem redu).

Če želite dobiti datum prihajajočega dogodka, ga samo dodajte v položaj celice, ki ga vrne funkcija MATCH, in vam bo dal položaj celice naslednjega datuma dogodka.

To vrednost nato poda funkcija INDEX.

Za pridobitev imena dogodka se uporabi ista formula, obseg v funkciji INDEX pa se spremeni iz stolpca B v stolpec A.

Kliknite tukaj, če želite prenesti primer datoteke

Zamisel o tem primeru se mi je porodila, ko se je prijatelj obrnil s prošnjo. V stolpcu je imel seznam vseh rojstnih dni svojih prijateljev/sorodnikov in želel je vedeti, kdaj bo naslednji rojstni dan (in ime osebe).

To so trije primeri, ki prikazujejo, kako najti najbližjo ujemajočo se vrednost v Excelu z uporabo iskalnih formul.

Morda vam bodo všeč tudi naslednji Excelovi nasveti/vaje