Excelove funkcije so lahko zelo zmogljive, če se naučite kombinirati različne formule.Kar se je nekoč zdelo nemogoče, lahko nenadoma postane kot otroška igra.
Eden takšnih primerov je iskanje najbližje vrednosti v naboru podatkov Excel.
V Excelu obstaja več uporabnih funkcij iskanja (kot sta VLOOKUP in INDEX MATCH), ki lahko poiščejo najbližje值(kot je prikazano v spodnjem primeru).
Najboljši del pa je, da lahko te funkcije iskanja združite z drugimi funkcijami Excela, da naredite veliko več (vključno z iskanjem najbližje vrednosti iskanja na nerazvrščenem seznamu).
V tej vadnici vam bom pokazal, kako uporabiti formulo iskanja za iskanje najbližje vrednosti iskanja v Excelu.
Obstaja veliko različnih scenarijev, kjer morate najti najbližje ujemanje (ali najbližjo vrednost ujemanja).
Tukaj je primer, ki ga bom obravnaval v tem članku:
- Poiščite stopnje provizije glede na prodajo
- Poiščite najboljšega kandidata (na podlagi najbližjih izkušenj)
- Poiščite datum naslednjega dogodka
Začnimo!
vsebina
Poiščite stopnje provizije (poiščite najbližjo prodajno vrednost)
Recimo, da imate nabor podatkov, kot je spodnji, kjer želite najti stopnje provizije za vse prodajalce.
Provizije se razdelijo glede na vrednost prodaje.To se izračuna s pomočjo tabele na desni.
Na primer, če je prodajalčeva skupna prodaja 5000, je provizija 0%, in če je njegova/njena skupna prodaja 15000, je provizija 5%.
Če želite dobiti provizijo, morate najti najbližji prodajni obseg, ki je tik pod prodajno vrednostjo.Na primer, za 15000 prodaj je provizija 10,000 (ali 5 %), za 25000 prodaj pa je provizija 20,000 (ali 7 %).
Če želite najti najbližjo prodajno vrednost in dobiti stopnjo provizije, lahko uporabite približno ujemanje v VLOOKUP.
To lahko naredi naslednja formula:
= OGLED (B2, $ E $ 2: $ F $ 6,2,1)
Upoštevajte, da je v tej formuli zadnji parameter 1, kar formuli pove, naj uporabi približno iskanje.To pomeni, da bo formula šla skozi prodajno vrednost v stolpcu E in našla vrednost tik pod iskalno vrednostjo.
Formula VLOOKUP bo nato podala stopnjo provizije za to vrednost.
注意: Če želite to narediti, morate podatke razvrstiti v naraščajočem vrstnem redu.
Poiščite najboljšega kandidata (na podlagi najbližjih izkušenj)
V zgornjem primeru je treba podatke razvrstiti v naraščajočem vrstnem redu.Lahko pa obstajajo primeri, ko podatki niso razvrščeni.
Vzemimo torej primer in poglejmo, kako lahko uporabimo kombinacije formul za iskanje najbližjega ujemanja v Excelu.
Spodaj je primer podatkovnega niza, kjer moram poiskati imena zaposlenih, katerih delovne izkušnje so najbližje želeni vrednosti.Pričakovana vrednost v tem primeru je 2.5 leta.
Upoštevajte, da podatki niso razvrščeni.Poleg tega je lahko najbližjih izkušenj manj ali več kot dana izkušnja.Na primer, 2 leti in 3 leta sta enako blizu (0.5 leta razlike).
Tukaj je formula, ki nam daje rezultat:
=INDEX($A$2:$A$15,MATCH(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))
Trik v tej formuli je, da spremenite niz iskanja in iskalne vrednosti, da poiščete najmanjšo empirično razliko med želenimi in dejanskimi vrednostmi.
Naj najprej razumemo, kako bi to naredili ročno (nato bom razložil, kako ta formula deluje).
Ko to naredite ročno, greste skozi vsako celico v stolpcu B in poiščete razliko med izkušnjo, ki jo želite, in izkušnjo, ki jo imate.Ko imate vse razlike, poiščete najmanjšo in dobite ime te osebe.
Točno to počnemo s to formulo.
Naj razložim.
Iskalna vrednost v formuli MATCH je MIN(ABS(D2-B2:B15)).
V tem razdelku je podana minimalna razlika med danimi izkušnjami (tj. 2.5 leta) in vsemi drugimi izkušnjami.V tem primeru vrne 0.3
Upoštevajte, da uporabljam ABS, da se prepričam, da iščem najbližje (verjetno bolj ali manj od dane izkušnje).
Zdaj ta minimalna vrednost postane naša iskalna vrednost.
Iskalna matrika v funkciji MATCH je ABS(D2-$B$2:$B$15).
Tako dobimo niz številk, od katerih se odšteje 2.5 (zahtevane izkušnje).
所以現在我們有一個查找值 (0.3) 和一個查找數組 ({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 v tem nizu najde položaj 0.3, ki je položaj imena osebe z najbližjo izkušnjo.
Funkcija INDEX nato uporabi številko opravila za vrnitev imena osebe.
Opomba: Če je več kandidatov z enakimi minimalnimi izkušnjami, bo zgornja formula dala ime prvega ujemajočega se zaposlenega.
Poiščite datum naslednjega dogodka
Tukaj je še en primer, kjer lahko s formulo za iskanje poiščete naslednji datum dogodka na podlagi trenutnega datuma.
Spodaj je nabor podatkov, kjer imam imena dogodkov in datume dogodkov.
Kar hočem, je ime naslednjega dogodka in datum dogodka tega prihajajočega dogodka.
Tukaj je formula, ki daje ime prihajajočemu dogodku:
=INDEX($A$2:$A$11,MATCH(E1,$B$2:$B$11,1)+1)
Naslednja formula bo dala datume prihajajočih dogodkov:
=INDEX($B$2:$B$11,MATCH(E1,$B$2:$B$11,1)+1)
Naj razložim, kako deluje ta formula.
Za pridobitev datuma dogodka funkcija MATCH išče trenutni datum v stolpcu B.V tem primeru ne iščemo natančnega ujemanja, ampak približno.Zato je zadnji parameter funkcije MATCH 1 (poišče največjo vrednost, ki je manjša ali enaka iskalni vrednosti).
Funkcija MATCH bo torej vrnila položaj celice, katere datum je manjši ali enak trenutnemu datumu.Torej bo v tem primeru naslednji dogodek v naslednji celici (ker je seznam razvrščen v naraščajočem vrstnem redu).
Torej, da dobite datum prihajajočega dogodka, samo dodajte enega k položaju celice, ki ga vrne funkcija MATCH, in dal vam bo položaj celice za naslednji datum dogodka.
To vrednost nato poda funkcija INDEX.
Če želite dobiti ime dogodka, uporabite isto formulo in spremenite obseg v funkciji INDEX iz stolpca B v stolpec A.
Ta primer mi je prišel na misel, ko je prijatelj vložil prošnjo.V stolpec našteje rojstne dneve vseh svojih prijateljev/sorodnikov in želi vedeti, da se bliža naslednji rojstni dan (in ime osebe).
Zgornji trije primeri prikazujejo, kako uporabiti formulo za iskanje za iskanje najbližje vrednosti v Excelu.
