Excel-funksjoner kan være svært kraftige hvis du får taket på å kombinere forskjellige formler.Det som en gang virket umulig kan plutselig bli som en lek for barn.
Et slikt eksempel er å finne den nærmeste verdien i et Excel-datasett.
Det er flere nyttige oppslagsfunksjoner i Excel (som VLOOKUP og INDEX MATCH) som kan finne den nærmeste值(som vist i eksempelet nedenfor).
Men det beste er at du kan kombinere disse oppslagsfunksjonene med andre Excel-funksjoner for å gjøre så mye mer (inkludert å finne den som passer nærmest en oppslagsverdi i en usortert liste).
I denne opplæringen skal jeg vise deg hvordan du bruker en oppslagsformel for å finne den nærmeste samsvaret med en oppslagsverdi i Excel.
Det er mange forskjellige scenarier der du må finne den nærmeste matchen (eller den nærmeste samsvarsverdien).
Her er et eksempel som jeg skal dekke i denne artikkelen:
- Finn provisjonssatser basert på salg
- Finn den beste kandidaten (basert på nærmeste erfaring)
- Finn neste arrangementsdato
la oss begynne!
Innhold
Finn provisjonssatser (se etter nærmeste salgsverdi)
Anta at du har et datasett som det nedenfor der du vil finne provisjonssatsene for alle selgere.
Provisjoner fordeles basert på verdien av salget.Dette er beregnet ved hjelp av tabellen til høyre.
For eksempel, hvis selgerens totale salg er 5000, er provisjonen 0%, og hvis hans/hennes totale salg er 15000, er provisjonen 5%.
For å få en provisjonssats må du finne det nærmeste salgsområdet som er like under salgsverdien.For eksempel, for 15000 10,000 salg er provisjonen 5 25000 (eller 20,000 %), og for 7 XNUMX salg er provisjonssatsen XNUMX XNUMX (eller XNUMX %).
For å finne nærmeste salgsverdi og få en provisjonssats kan du bruke omtrentlig matching i VLOOKUP.
Følgende formel kan gjøre dette:
=OPSLAKK(B2,$E$2:$F$6,2,1)
Merk at i denne formelen er den siste parameteren 1, som forteller at formelen skal bruke omtrentlig oppslag.Dette betyr at formelen vil gå gjennom salgsverdien i kolonne E og finne verdien rett under oppslagsverdien.
VLOOKUP-formelen vil da gi provisjonssatsen for den verdien.
注意: For å gjøre dette, må du sortere dataene i stigende rekkefølge.
Finn den beste kandidaten (basert på nærmeste erfaring)
I eksemplet ovenfor må dataene sorteres i stigende rekkefølge.Men det kan være tilfeller der dataene ikke er sortert.
Så la oss ta et eksempel og se hvordan vi kan bruke formelkombinasjoner for å finne den nærmeste matchen i Excel.
Nedenfor er et eksempeldatasett hvor jeg trenger å finne navn på ansatte som har arbeidserfaring nærmest ønsket verdi.Forventet verdi i dette tilfellet er 2.5 år.
Merk at dataene ikke er sortert.I tillegg kan de nærmeste opplevelsene være færre eller flere enn en gitt opplevelse.For eksempel er 2 år og 3 år like nære (0.5 års forskjell).
Her er formelen som gir oss resultatet:
=INDEX($A$2:$A$15,MATCH(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))
Trikset i denne formelen er å endre oppslagsmatrisen og oppslagsverdiene for å finne den minste empiriske forskjellen mellom de ønskede og faktiske verdiene.
La oss først forstå hvordan du ville gjort det manuelt (og så skal jeg forklare hvordan denne formelen fungerer).
Når du gjør dette manuelt, går du gjennom hver celle i kolonne B og finner forskjellen mellom opplevelsen du ønsker og erfaringen en har.Når du har alle forskjellene, finner du den minste og får den personens navn.
Det er akkurat det vi gjør med denne formelen.
La meg forklare.
Oppslagsverdien i MATCH-formelen er MIN(ABS(D2-B2:B15)).
Denne delen gir deg minimumsforskjellen mellom den gitte erfaringen (dvs. 2.5 år) og alle andre erfaringer.I dette tilfellet returnerer den 0.3
Merk at jeg bruker ABS for å være sikker på at jeg ser etter det nærmeste (sannsynligvis mer eller mindre enn den gitte opplevelsen).
Nå blir denne minimumsverdien vår oppslagsverdi.
Oppslagsmatrisen i MATCH-funksjonen er ABS(D2-$B$2:$B$15).
Dette gir oss en rekke tall som 2.5 (erfaring kreves) trekkes fra.
所以現在我們有一個查找值 (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})
MATCH-funksjonen finner posisjonen 0.3 i denne matrisen, som er posisjonen til navnet til personen med den nærmeste erfaringen.
INDEKS-funksjonen bruker deretter jobbnummeret til å returnere personens navn.
Merk: Hvis det er flere kandidater med samme minimumserfaring, vil formelen ovenfor gi navnet på den første matchende medarbeideren.
Finn dato for neste arrangement
Her er et annet eksempel der du kan bruke en oppslagsformel for å finne neste dato for en hendelse basert på gjeldende dato.
Nedenfor er datasettet der jeg har hendelsesnavn og hendelsesdatoer.
Det jeg vil ha er navnet på det neste arrangementet og datoen for dette kommende arrangementet.
Her er formelen som gir navnet på det kommende arrangementet:
=INDEX($A$2:$A$11,MATCH(E1,$B$2:$B$11,1)+1)
Følgende formel vil gi de kommende arrangementsdatoene:
=INDEX($B$2:$B$11,MATCH(E1,$B$2:$B$11,1)+1)
La meg forklare hvordan denne formelen fungerer.
For å få hendelsesdatoen, ser MATCH-funksjonen i kolonne B etter gjeldende dato.I dette tilfellet er vi ikke ute etter et eksakt samsvar, men et omtrentlig samsvar.Derfor er den siste parameteren til MATCH-funksjonen 1 (den finner den største verdien mindre enn eller lik oppslagsverdien).
Så MATCH-funksjonen vil returnere posisjonen til cellen hvis dato tilfeldigvis er mindre enn eller lik gjeldende dato.Så i dette tilfellet vil neste hendelse være i neste celle (siden listen er sortert i stigende rekkefølge).
Så for å få den kommende hendelsesdatoen, bare legg til en til celleposisjonen returnert av MATCH-funksjonen, og den vil gi deg celleposisjonen til neste hendelsesdato.
Denne verdien er da gitt av INDEX-funksjonen.
For å få hendelsesnavnet, bruk samme formel og endre området i INDEX-funksjonen fra kolonne B til kolonne A.
Dette eksemplet kom til meg da en venn kom med en forespørsel.Han lister opp bursdagene til alle vennene/slektningene sine i en kolonne og vil vite at neste bursdag kommer (og personens navn).
De tre eksemplene ovenfor viser hvordan du bruker oppslagsformelen for å finne den nærmeste verdien i Excel.
