Bottom line: Lær hvordan du beregner provisioner i VLOOKUP for en grundlæggende plan og sats tabel ved hjælp af funktionerne IF, VLOOKUP eller VLOOKUP.

færdighedsniveau: mellemliggende

Video Tutorial

beregning af provisioner kan være en meget vanskelig opgave. Dette gælder især, hvis du har forsøgt at bruge flere if-udsagn til at beregne provisioner for hvert niveau i en satstabel.

denne artikel forklarer, hvordan du bruger VLOOKUP-funktionen til at gøre denne proces meget lettere. Hemmeligheden er at sætte det sidste argument i vlookup til TRUE, for at finde det nærmeste match.

tjek venligst min artikel om, hvordan du bruger VLOOKUP til at finde det nærmeste Match for en detaljeret forklaring på at indstille det sidste argument til sandt.

opdatering: Indlægget er blevet opdateret til at indeholde instruktioner til beregning af provisioner med den nye OPSLAGSFUNKTION. Her er en artikel & video på opslag.

Hent fil

Hent filen for at følge med.

provisioner beregninger – hvis Vs VLOOKUP Vs lookup.(31,5 KB)

beregning af provisioner med VLOOKUP

med en simpel provisionsplan har du typisk en satstabel, der viser udbetalingsraten på hvert salgsniveau. Da salgsrepræsentanten gør mere salg, vil hans / hendes udbetalingsrate typisk stige.

udbetalingsgraden kan være et fladt dollarbeløb, procentdel af indtægter, procentdel af kvote osv. Jobbet med VLOOKUP er at finde rep ‘ s salgsbeløb i satstabellen og returnere den tilsvarende udbetalingsrate.

i dette eksempel ser vores provisionsplan ud som følgende:

• Rep sælger $0-$50.000, de tjener 5%
• Rep sælger $51.000-$100.000, de tjener 7%
• Rep sælger $100.001-$150.000, de tjener 10%
• Rep sælger over $150.001, de tjener 15%

rep vil foretage en vis mængde salg i løbet af måneden. Han / hun tjener en vis udbetalingsrate afhængigt af det opnåede salgsniveau.

vi kan bruge en VLOOKUP-formel til at beregne udbetalingsfrekvensen for et givet salgsbeløb (opslagsværdi). For at dette skal fungere, er vi nødt til at indstille det sidste argument i vlookup til TRUE.

når det sidste argument er sat til sand, finder vlookup det nærmeste match til opslagsværdien, der er mindre end eller lig med opslagsbeløbet. Dette giver os grundlæggende mulighed for at finde en værdi mellem intervaller på to tal (niveauer).

dette er grunden til, at det sidste argument i VLOOKUP hedder range_lookup. Når dette argument er sandt, ser VLOOKUP mellem værdier i kolonnen tier minimum for at finde et nøjagtigt match eller værdien mindre end opslagsværdien.

Sådan opsættes Satstabellen

når du sætter denne satstabel i udmærke sig, behøver du kun at angive niveauminimum for opslagsområdet. Igen vil VLOOKUP søge efter en “nærmeste match”, der er mindre end eller lig med opslagsværdien. Hvis den finder en værdi, der er større end opslagsværdien, returnerer den den forrige række.

i dette eksempel for en provisionstabel skal den første række i opslagsområdet være nul. Dette skyldes, at salgsrepræsentanten potentielt kunne have et salg på $ 0, og opslagsværdien ville være nul. Hvis opslagsværdien (salgsbeløb) var et negativt tal, ville vlookup returnere en fejl.

det er vigtigt at kende dette og opsætte din sats tabel for alle mulige opslag værdier.

hvis salgsbeløbet er større end den sidste række i opslagsområdet, returnerer vlookup den sidste række. For eksempel, hvis rep solgte $175.000, ville vlookup returnere 15%.

Beregn provisioner for at returnere en dollarværdi

udbetalingen kan også returneres som en dollarværdi i stedet for en procentdel. Med denne opsætning udbetalingen vil være en fast sats.

dette betyder, at udbetalingen vil være den samme, uanset hvad salgsbeløbet er inden for niveauet. I eksemplet nedenfor, hvis udbetalingen vil være $1.000, hvis salgsbeløbet er $55.000 eller $95.000.

udbetalingen er ikke på en glidende skala. Det er en fast sats for hvert niveau.

hvis du er på udkig efter en glidende skala beregning, se min artikel om beregning provision med en differentieret Sats struktur ved hjælp SUMPRODUKT.

Kommissionsberegninger med opslag

vi kan også bruge den nye OPSLAGSFUNKTION til denne beregning. Det fungerer meget lig VLOOKUP, når man finder det nærmeste match.

fordele med opslag

der er to hovedforskelle og fordele med opslag:

1. med opslag specificerer vi opslagsarrayet og returarrayet som separate intervaller. Dette er B4: B7 og D4:D7 på billedet ovenfor. Dette er en fordel i forhold til VLOOKUP, fordi vi kan indsætte eller slette rækker mellem kolonne B og D, og formlen fungerer stadig.
   når vi tilføjer/sletter kolonner i tabelarrayet af VLOOKUP, skal vi manuelt ændre kolonneindeksnummeret eller bruge en formel til dynamisk at beregne det.
2. kræver ikke, at dataene sorteres, når du bruger eksakt match eller næste mindre/større element til matchtilstand. Lookup vil faktisk kigge efter det næste mindre/større element og returnere resultatet fra den række/kolonne.
   VLOOKUP kræver, at dataene sorteres, når det sidste argument er sandt. Typisk vil du alligevel sortere dine data for disse typer tabeller, men det er godt at vide, at du bare kan sætte nye satsniveauer i bunden af tabellen, og opslag fungerer.

ulempe ved opslag

den største ulempe ved opslag er Kompatibilitet. Både du og alle brugere af din fil skal være på en version af Microsoft/Office 365, der har . Det er ikke bagudkompatibelt eller tilgængeligt på ældre versioner af .

derfor fungerer VLOOKUP godt i dette scenarie og er meget lettere at skrive end en indlejret if-formel.

undgå indlejrede if-formler

en fælles tilgang til beregning af provisioner bruger if-udsagn. Med en sats tabel som denne ville du nødt til at skrive flere if udsagn. Du skal dybest set skrive en if-sætning for hvert niveau (række) i tabellen.

du skal derefter deltage i alle if-udsagnene i en lang og grim formel. Disse kaldes indlejrede if-udsagn.

jeg forsøger altid at undgå indlejrede IFs, når det er muligt, fordi de er vanskelige at læse og forstå, og de kan være langsommere for at beregne. Hvis du har tusindvis af indlejrede if-formler i din projektmappe, kan din beregningstid blive langsommere.

if-formlen er meget vanskeligere at vedligeholde. Det kræver ændring, når rækker tilføjes/slettes fra tabellen. VLOOKUP og VLOOKUP kræver ikke denne type vedligeholdelse.

hvis du bruger IF, skal du sørge for at gøre hver celle reference til Sats tabellen en absolut reference (F4 på tastaturet). Ellers får du forkerte resultater, når du kopierer formlen ned.

brug af VLOOKUP er meget lettere og renere end at bruge indlejrede if-formler. Som du kan se med dette eksempel, giver vlookup dig mulighed for at bruge en formel til at beregne provisionsudbetalingsraten for et givet salgsbeløb.

yderligere ressourcer

• Sådan bruges VLOOKUP til at finde det nærmeste Match – Sidste Argument er sandt
• Sådan beregnes kumulative differentierede provisioner med SUMPRODUKT
• VLOOKUP forklaret i enkle vendinger på Starbucks
• VLOOKUP & MATCH – en dynamisk Duo
• den nye video tutorial
• if funktion forklaret: Sådan skriver du en if-sætningsformel i

Hvordan bruger du VLOOKUP med nærmeste match?

VLOOKUP med nærmeste matchteknik kan også bruges til beregninger af skatteklasse, prismatchning osv. Hvad bruger du denne teknik til at beregne?