✅ Finn den nærmeste kampen i Excel ved hjelp av formler

Innholdsfortegnelse

Finn den nærmeste kampen i Excel

Excel -funksjoner kan være ekstremt kraftige hvis du får en kombinasjon av forskjellige formler. Ting som kan ha virket umulige, ville plutselig begynne å se ut som et barns lek.

Et slikt eksempel er å finne den nærmeste matchen til en oppslagsverdi i et datasett i Excel.

Det er et par nyttige oppslagsfunksjoner i Excel (for eksempel VLOOKUP & INDEX MATCH), som kan finne den nærmeste matchen i noen få enkle tilfeller (som jeg vil vise med eksempler nedenfor).

Men det beste er at du kan kombinere disse oppslagsfunksjonene med andre Excel -funksjoner for å gjøre mye mer (inkludert å finne det nærmeste samsvaret med en oppslagsverdi i en usortert liste).

I denne opplæringen vil jeg vise deg hvordan du finner det nærmeste samsvaret med en oppslagsverdi i Excel med oppslagsformler.

Finn den nærmeste kampen i Excel

Det kan være mange forskjellige scenarier der du må lete etter den nærmeste kampen (eller den nærmeste matchende verdien).

Nedenfor er eksemplene jeg vil dekke i denne artikkelen:

Finn provisjonsrenten basert på salg
Finn den beste kandidaten (basert på den nærmeste erfaringen)
Finner neste hendelsesdato

La oss komme i gang!

Klikk her for å laste ned eksempelfilen

Finn provisjonsrente (ser etter den nærmeste salgsverdien)

Anta at du har et datasett som vist nedenfor, der du vil finne provisjonsprisene for alt salgspersonellet.

Provisjonen tildeles basert på salgsverdien. Og dette beregnes ved hjelp av tabellen til høyre.

For eksempel, hvis en selger gjør det totale salget på 5000, er provisjonen 0%, og hvis han/hun gjør det totale salget på 15000, er provisjonen 5%.

For å få provisjonsrenten, må du finne det nærmeste salgsintervallet bare lavere enn salgsverdien. For eksempel, for 15000 salgsverdi, vil provisjonen være på 10.000 (som er 5%) og for salgsverdi på 25000, vil provisjonsrenten være på 20.000 (som er 7%).

For å finne den nærmeste salgsverdien og få provisjonsrenten, kan du bruke den omtrentlige kampen i VLOOKUP.

Formelen nedenfor vil gjøre dette:

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

Vær oppmerksom på at i denne formelen er det siste argumentet 1, som forteller formelen om å bruke et omtrentlig oppslag. Dette betyr at formelen vil gå gjennom salgsverdiene i kolonne E og finne verdien som bare er lavere enn oppslagsverdien.

Deretter vil VLOOKUP -formelen gi provisjonsrenten for denne verdien.

Merk: For at dette skal fungere, må du ha dataene sortert i stigende rekkefølge.

Klikk her for å laste ned eksempelfilen

Finn den beste kandidaten (basert på den nærmeste erfaringen)

I eksemplet ovenfor måtte dataene sorteres i stigende rekkefølge. Men det kan være tilfeller der dataene ikke er sortert.

Så la oss dekke et eksempel og se hvordan vi kan finne den nærmeste matchen i Excel ved å bruke en kombinasjon av formler.

Nedenfor er et eksempel på datasett der jeg må finne ansattnavnet som har arbeidserfaringen nærmest ønsket verdi. Ønsket verdi i dette tilfellet om 2,5 år.

Vær oppmerksom på at dataene ikke er sortert. Den nærmeste opplevelsen kan også være mindre eller mer enn gi -opplevelsen. For eksempel er 2 år og 3 år like like (forskjell på 0,5 år).

Nedenfor er formelen som vil gi oss resultatet:

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

Trikset i denne formelen er å endre oppslagsoppsettet og oppslagsverdien for å finne den minste erfaringsforskjellen i nødvendige og faktiske verdier.

La oss først forstå hvordan du ville gjøre det manuelt (og så vil jeg forklare hvordan denne formelen fungerer).

Når du gjør dette manuelt, vil du gå gjennom hver celle i kolonne B og finne forskjellen i opplevelsen mellom det som kreves og det en person har. Når du har alle forskjellene, finner du den som er minst og henter navnet på den personen.

Dette 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 (som er 2,5 år) og alle de andre erfaringene. I dette eksemplet returnerer det 0,3

Legg merke til at jeg har brukt ABS for å sikre at jeg leter etter det nærmeste (som kan være mer eller mindre enn den gitte opplevelsen).

Nå blir denne minimumsverdien vår oppslagsverdi.

Oppslagsoppsettet i MATCH-funksjonen er ABS (D2- $ B $ 2: $ B $ 15).

Dette gir oss en rekke tall hvorfra 2,5 (den nødvendige erfaringen) er trukket fra.

Så nå har vi en oppslagsverdi (0,3) og en oppslagsoppstilling ({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 også er posisjonen til personens navn som har den nærmeste opplevelsen.

Dette posisjonsnummeret brukes deretter av INDEX -funksjonen for å returnere navnet på personen.

Merk: Hvis det er flere kandidater som har samme minimumserfaring, vil formelen ovenfor gi navnet på den første matchende medarbeideren.

Finn datoen for neste arrangement

Dette er et annet eksempel der du kan bruke oppslagsformler 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 arrangementet for dette kommende arrangementet.

Nedenfor er formelen som vil gi navnet på det kommende arrangementet:

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

Og formelen nedenfor vil gi den kommende hendelsesdatoen:

= INDEKS ($ 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 etter gjeldende dato i kolonne B. I dette tilfellet leter vi ikke etter en eksakt samsvar, men en omtrentlig. Og derfor er det siste argumentet til MATCH -funksjonen 1 (som finner den største verdien som er mindre enn eller lik oppslagsverdien).

Så MATCH -funksjonen ville returnere posisjonen til cellen som har datoen som er bare mindre enn eller lik gjeldende dato. Så den neste hendelsen, i dette tilfellet, vil være i den neste cellen (ettersom listen er sortert i stigende rekkefølge).

Så for å få den kommende hendelsesdatoen, bare legg til en i celleposisjonen som returneres av MATCH -funksjonen, og den gir deg celleposisjonen til neste hendelsesdato.

Denne verdien er deretter gitt av INDEX -funksjonen.

For å få hendelsesnavnet brukes den samme formelen, og området i INDEX -funksjonen endres fra kolonne B til kolonne A.

Klikk her for å laste ned eksempelfilen

Ideen om dette eksemplet kom til meg da en venn henvendte seg med en forespørsel. Han hadde en liste over alle bursdagene til venner/slektninger i en spalte og ville vite neste bursdag (og navnet på personen).

Dette er tre eksempler som viser hvordan du finner den nærmeste samsvarende verdien i Excel ved hjelp av oppslagsformler.

Du kan også like følgende Excel -tips/opplæringsprogrammer