Bottom line: lär dig hur man beräknar provisioner i Excel för en grundläggande nivåplan och rate-tabell med funktionerna IF, VLOOKUP eller XLOOKUP.

skicklighetsnivå: mellanliggande

Video Tutorial

beräkning av provisioner i Excel kan vara en mycket knepig uppgift. Detta gäller särskilt om du har försökt använda flera IF-satser för att beräkna provisioner för varje nivå i en räntetabell.

den här artikeln kommer att förklara hur man använder VLOOKUP-funktionen för att göra denna process mycket enklare. Hemligheten sätter det sista argumentet i vlookup till TRUE, för att hitta närmaste match.

Vänligen kolla in min artikel om hur du använder VLOOKUP för att hitta närmaste Match för en detaljerad förklaring om att ställa in det sista argumentet till TRUE.

Uppdatering: Inlägget har uppdaterats för att inkludera instruktioner för beräkning av provisioner med den nya xlookup-funktionen. Här är en artikel & video på XLOOKUP.

ladda ner filen

ladda ner filen för att följa med.

provisioner beräkningar – om Vs VLOOKUP Vs XLOOKUP.xlsx (31.5 KB)

beräkning av provisioner med VLOOKUP

med en enkel provisionsplan har du vanligtvis en räntetabell som visar utbetalningsgraden på varje försäljningsnivå. Som säljare gör mer försäljning, hans / hennes utbetalningsgraden kommer vanligtvis att öka.

utbetalningsgraden kan vara ett platt dollarbelopp, procentandel av intäkter, procentandel av kvoten etc. VLOOKUP-jobbet är att hitta rep: s försäljningsbelopp i kurstabellen och returnera motsvarande utbetalningsgrad.

för det här exemplet ser vår kommissionsplan ut som följande:

• Rep säljer $0-$50,000, de tjänar 5%
• Rep säljer $51,000-$100,000, de tjänar 7%
• Rep säljer $100,001-$150,000, de tjänar 10%
• Rep säljer över $150,001, de tjänar 15%

rep kommer att göra en viss mängd försäljning under månaden. Han / hon kommer att tjäna en viss utbetalningsgrad beroende på försäljningsnivån.

vi kan använda en VLOOKUP formel för att beräkna utbetalningsgraden för en given försäljningsbelopp (lookup värde). För att detta ska fungera måste vi ställa in det sista argumentet i vlookup till TRUE.

med det sista argumentet satt till TRUE kommer vlookup att hitta den närmaste matchningen till uppslagsvärdet som är mindre än eller lika med uppslagsbeloppet. Detta tillåter oss i princip att hitta ett värde mellan intervall med två siffror (nivåer).

det är därför det sista argumentet i VLOOKUP heter range_lookup. När detta argument är sant, letar VLOOKUP mellan värden i kolumnen tier minimum för att hitta en exakt matchning eller värdet mindre än uppslagsvärdet.

så här ställer du in Rate Table

när du lägger denna rate table i Excel behöver du bara lista tier minimum för uppslagsområdet. Återigen söker VLOOKUP efter en” närmaste matchning ” som är mindre än eller lika med uppslagsvärdet. Om det hittar ett värde som är större än uppslagsvärdet kommer det att returnera föregående rad.

i det här exemplet för en provisionstabell måste den första raden i uppslagsområdet vara noll. Detta beror på att försäljningsrepresentanten potentiellt kan ha en försäljning på $ 0 och uppslagsvärdet skulle vara noll. Om uppslagsvärdet (försäljningsbeloppet) var ett negativt tal, skulle vlookup returnera ett fel.

det är viktigt att veta detta och ställa in din hastighetstabell för alla möjliga uppslagningsvärden.

om försäljningsbeloppet är större än den sista raden i uppslagsområdet returnerar vlookup den sista raden. Till exempel, om rep gjorde försäljning av $175,000 då vlookup skulle återvända 15%.

beräkna provisioner för att returnera ett dollarvärde

utbetalningen kan också returneras som ett dollarvärde istället för en procentandel. Med denna inställning utbetalningen kommer att vara ett schablonbelopp.

detta innebär att utbetalningen kommer att vara densamma, oavsett vad försäljningsbeloppet ligger inom nivån. I exemplet nedan, om utbetalningen blir $1,000 om försäljningsbeloppet är $55,000 eller $95,000.

utbetalningen är inte på en glidande skala. Det är en fast ränta för varje nivå.

om du letar efter en glidande skala beräkning, se min artikel om beräkning provision med en differentierad räntestruktur med SUMPRODUCT.

Kommissionsberäkningar med Xlookup

vi kan också använda den nya xlookup-funktionen för denna beräkning. Det fungerar mycket lik VLOOKUP när du hittar närmaste match.

fördelar med XLOOKUP

det finns två huvudsakliga skillnader och fördelar med XLOOKUP:

1. med XLOOKUP anger vi lookup-arrayen och returnerar array som separata intervall. Detta är B4:B7 och D4: D7 i bilden ovan. Detta är en fördel jämfört med LETARAD eftersom vi kan infoga eller ta bort rader mellan kolumnerna B och D, och formeln fungerar fortfarande.
   när vi lägger till/tar bort kolumner i TABELLMATRISEN för VLOOKUP måste vi manuellt ändra kolumnindexnumret eller använda en formel för att dynamiskt beräkna det.
2. XLOOKUP kräver inte att data sorteras när du använder exakt matchning eller nästa mindre/större objekt för matchningsläge. XLOOKUP kommer faktiskt att leta efter nästa mindre/större objekt och returnera resultatet från den raden / kolumnen.
   VLOOKUP kräver att data sorteras när det sista argumentet är sant. Vanligtvis vill du sortera dina data för dessa typer av tabeller ändå, men det är bra att veta att du bara kan lägga nya räntenivåer längst ner i tabellen och XLOOKUP fungerar.

nackdel med XLOOKUP

den största nackdelen med XLOOKUP är Kompatibilitet. Både du och alla användare av filen måste vara på en version av Microsoft / Office 365 som har XLOOKUP. Det är inte bakåtkompatibelt eller tillgängligt i äldre versioner av Excel.

därför fungerar VLOOKUP bra i detta scenario och är mycket lättare att skriva än en kapslad IF-formel.

Undvik kapslade IF-formler

en vanlig metod för beräkning av provisioner använder IF-uttalanden. Med en hastighetstabell som denna måste du skriva flera IF-uttalanden. Du måste i princip skriva ett IF-uttalande för varje nivå (rad) i tabellen.

du måste sedan gå med i alla IF-uttalanden i en lång och ful formel. Dessa kallas kapslade IF-uttalanden.

jag försöker alltid undvika kapslade IFs när det är möjligt eftersom de är svåra att läsa och förstå, och de kan vara långsammare för Excel att beräkna. Om du har tusentals kapslade IF-formler i arbetsboken kan din beräkningstid sakta ner.

IF-formeln är mycket svårare att underhålla. Det kräver ändring när rader läggs till/tas bort från tabellen. VLOOKUP och XLOOKUP kräver inte denna typ av underhåll.

om du använder IF, se till att varje cellreferens till hastighetstabellen är en absolut referens (F4 på tangentbordet). Annars får du felaktiga resultat när du kopierar formeln ner.

att använda VLOOKUP är mycket enklare och renare än att använda kapslade IF-formler. Som du kan se med det här exemplet låter vlookup dig använda en formel för att beräkna provisionens utbetalningsgrad för ett visst försäljningsbelopp.

ytterligare resurser

• hur man använder VLOOKUP för att hitta närmaste match – sista argumentet är sant
• hur man beräknar kumulativa differentierade provisioner med SUMPRODUCT
• VLOOKUP förklaras i enkla termer på Starbucks
• VLOOKUP & MATCH – a Dynamic Duo
• den nya xlookup funktion för Excel + video tutorial
• om funktionen förklaras: hur man skriver en IF uttalande formel i Excel

Hur använder du VLOOKUP med närmaste match?

VLOOKUP med närmaste matchningsteknik kan också användas för skattekonsolberäkningar, prismatchning etc. Vad använder du den här tekniken för att beräkna?