Bunnlinje: Lær hvordan du beregner provisjoner i Excel for en grunnleggende lagdelt plan og rate-tabell ved HJELP AV FUNKSJONENE IF, FINN. RAD eller XLOOKUP.

Ferdighetsnivå: Middels

Videoopplæring

Beregning av provisjoner i Excel kan være en svært vanskelig oppgave. Dette gjelder spesielt hvis du har prøvd å bruke FLERE IF-setninger til å beregne provisjoner for hvert nivå i en satstabell.

Denne artikkelen vil forklare hvordan DU bruker FINN. RAD-funksjonen for å gjøre denne prosessen mye enklere. Hemmeligheten er å sette det siste argumentet I finn. rad TIL TRUE, for å finne nærmeste match.

sjekk ut artikkelen Min Om HVORDAN Du Bruker FINN. RAD for Å Finne Det Nærmeste Treffet for en detaljert forklaring på hvordan du angir det siste argumentet TIL SANN.

Oppdatering: Innlegget er oppdatert for å inkludere instruksjoner for beregning av provisjoner med den nye xlookup-funksjonen. Her er en artikkel & video PÅ XLOOKUP.

Last Ned Fil

Last ned filen for å følge med.

Provisjonsberegninger – HVIS VS FINN. RAD Vs XLOOKUP.xlsx (31,5 KB)

Beregne Provisjoner med FINN. RAD

med en enkel provisjonsplan har du vanligvis en kurtabell som viser utbetalingsraten på hvert salgsnivå. Som salgsrepresentant gjør mer salg, vil hans / hennes utbetaling vanligvis øke.

utbetalingsrenten kan være et flatt dollarbeløp, prosentandel av inntekter, prosentandel av kvote, etc. JOBBEN TIL FINN. RAD er å finne reps salgsbeløp i kurstabellen, og returnere den tilsvarende utbetalingsrenten.

for dette eksemplet ser vår provisjonsplan ut som følgende:

• Rep selger $0-$50,000, de tjener 5%
• Rep selger $51,000-$100,000, de tjener 7%
• Rep selger $100,001-$150,000, de tjener 10%
• Rep selger over $150,001, de tjener 15%

rep vil gjøre en viss mengde salg i løpet av måneden. Han / hun vil tjene en viss utbetalingsrate avhengig av salgsnivået oppnådd.

Vi kan bruke EN FINN. RAD-formel Til å beregne utbetalingsraten for et gitt salgsbeløp (oppslagsverdi). For at dette skal fungere må vi sette det siste argumentet I vlookup TIL TRUE.

når det siste argumentet er SATT TIL TRUE, vil finn. rad finne nærmeste samsvar med oppslagsverdien som er mindre enn eller lik oppslagsbeløpet. Dette tillater oss i utgangspunktet å finne en verdi mellom områder av to tall (tiers).

det er derfor det siste argumentet i FINN. RAD heter range_lookup. NÅR dette argumentet ER SANT, søker FINN. RAD mellom verdiområder i nivåminimum-kolonnen for å finne et eksakt samsvar eller verdien som er mindre enn oppslagsverdien.

Slik Konfigurerer Du Satstabellen

når du setter denne satstabellen I Excel, trenger du bare å oppgi nivåminimum for oppslagsområdet. IGJEN VIL FINN. RAD søke etter et «nærmeste samsvar» som er mindre enn eller lik oppslagsverdien. Hvis den finner en verdi som er større enn oppslagsverdien, vil den returnere forrige rad.

i dette eksemplet for en provisjonssatstabell må den første raden i oppslagsområdet være null. Dette skyldes at salgsrepresentanten potensielt kan ha salg på $0 og oppslagsverdien vil være null. Hvis oppslagsverdien (salgsbeløp) var et negativt tall, ville finn. rad returnere en feil.

det er viktig å vite dette og sette opp din rate tabell for alle mulige oppslagsverdier.

hvis salgsbeløpet er større enn den siste raden i oppslagsområdet, returnerer finn. rad den siste raden. For eksempel, hvis repen gjorde salg på $175 000, ville vlookup returnere 15%.

Beregn Provisjoner For Å Returnere En Dollarverdi

utbetalingen kan også returneres som en dollarverdi, i stedet for en prosentandel. Med dette oppsettet utbetalingen vil være en flat rate.

dette betyr at utbetalingen vil være den samme, uavhengig av hva salgsbeløpet er innenfor nivået. I eksemplet nedenfor, hvis utbetalingen vil være $1000 hvis salgsbeløpet er $55 000 eller $ 95 000.

utbetalingen er IKKE i glidende skala. Det er en flat sats for hvert nivå.

hvis du er ute etter en glidende skala beregning, se min artikkel om beregning av provisjon med en tiered rate struktur ved HJELP AV SUMPRODUCT.

Provisjonsberegninger med XLOOKUP

Vi kan også bruke den nye xlookup-funksjonen for denne beregningen. Det fungerer svært lik VLOOKUP når finne nærmeste kamp.

Fordeler MED XLOOKUP

det er to hovedforskjeller og fordeler MED XLOOKUP:

1. med XLOOKUP angir vi oppslagstavlen og returnerer matrisen som separate områder. Dette Er B4: B7 Og D4: D7 i bildet ovenfor. Dette er en fordel over FINN. RAD FORDI vi kan sette inn eller slette rader Mellom kolonner B og D, og formelen vil fortsatt fungere.
   Når vi legger til / sletter kolonner i TABELLGRUPPEN AV FINN. RAD, må vi manuelt endre kolonneindeksnummeret eller bruke en formel for å beregne det dynamisk.
2. XLOOKUP krever ikke at dataene skal sorteres ved Bruk Av Eksakt samsvar eller neste mindre / større element for samsvarsmodus. XLOOKUP vil faktisk se etter neste mindre / større element og returnere resultatet fra den raden / kolonnen.
   FINN. RAD krever at dataene sorteres når det siste argumentet ER SANT. Vanligvis vil du sortere dataene dine for disse typer tabeller uansett, men DET er godt å vite at du bare kan sette nye hastighetsnivåer nederst på bordet, OG XLOOKUP vil fungere.

Ulempen MED XLOOKUP

den største ulempen MED XLOOKUP er kompatibilitet. Både du og alle brukerne av filen må være på en versjon Av Microsoft / Office 365 som HAR XLOOKUP. Den er ikke bakoverkompatibel eller tilgjengelig på eldre Versjoner Av Excel.

DERFOR FUNGERER FINN. RAD bra i dette scenariet og er mye enklere å skrive enn en nestet IF-formel.

Unngå Nestede IF-Formler

EN felles tilnærming til beregning av provisjoner bruker IF-setninger. Med en rate tabell som dette må du skrive flere HVIS uttalelser. Du må i utgangspunktet skrive EN IF-setning for hvert nivå (rad) i tabellen.

Du må da bli med ALLE IF-setningene i en lang og stygg formel. Disse kalles nestede IF-setninger.

jeg prøver alltid å unngå nestede IFs når det er mulig fordi de er vanskelige å lese og forstå, og de kan være tregere For Excel å beregne. Hvis du har tusenvis AV nestede IF-formler i arbeidsboken, kan beregningstiden bli tregere.

IF-formelen er mye vanskeligere å vedlikeholde. Det krever endring når rader legges til / slettes fra bordet. VLOOKUP og XLOOKUP krever ikke denne typen vedlikehold.

hvis DU bruker IF, må du sørge for at hver cellereferanse til rate-tabellen er en absolutt referanse (F4 på tastaturet). Ellers får du feil resultater når du kopierer formelen ned.

Å Bruke FINN. RAD Er mye enklere og renere enn å bruke nestede IF-formler. Som du kan se med dette eksemplet, lar vlookup deg bruke en formel for å beregne provisjonsutbetalingsraten for et gitt salgsbeløp.

Ekstra Ressurser

• Hvordan å Bruke VLOOKUP å Finne det Nærmeste treffet – Siste Argumentet er Lik SANT
• Hvordan å Beregne Kumulativ Lagdelt Provisjoner med SUMPRODUCT
• VLOOKUP Forklart i Enkle Termer på Starbucks
• VLOOKUP & MATCH – EN Dynamisk Duo
• Den Nye XLOOKUP Funksjon for Excel + Video Tutorial
• HVIS Funksjonen Forklart: Hvordan å Skrive en IF-setning Formel i Excel

Hvordan kan Du Bruke VLOOKUP med Nærmeste Match?

FINN. RAD med nærmeste match-teknikk kan også brukes til beregninger av skatteparentes, prismatching osv. Hva bruker du denne teknikken til å beregne?