Bottom line: lees hoe u commissies in Excel kunt berekenen voor een basic tiered plan en tarieftabel met behulp van de IF -, VLOOKUP-of XLOOKUP-functies.

vaardigheidsniveau: gemiddeld

videolessen

het berekenen van commissies in Excel kan een zeer lastige taak zijn. Dit geldt vooral als u hebt geprobeerd om meerdere IF-verklaringen te gebruiken om commissies te berekenen voor elke tier in een tarieftabel.

dit artikel zal uitleggen hoe de VLOOKUP functie gebruikt kan worden om dit proces veel gemakkelijker te maken. Het geheim is het instellen van het laatste argument in de vlookup op TRUE, om de dichtstbijzijnde match te vinden.

bekijk mijn artikel over het gebruik van VLOOKUP om de dichtstbijzijnde overeenkomst te vinden voor een gedetailleerde uitleg over het instellen van het laatste argument op TRUE.

bijwerken: De post is bijgewerkt met instructies voor het berekenen van commissies met de nieuwe xlookup functie. Hier is een artikel & video op XLOOKUP.

download bestand

Download het volgende bestand.

Commissieberekeningen-IF Vs VLOOKUP Vs XLOOKUP.xlsx (31.5 KB)

commissies berekenen met VLOOKUP

met een eenvoudig commissieplan heb je meestal een tarieftabel die het uitbetalingspercentage op elk verkoopniveau weergeeft. Als de vertegenwoordiger maakt meer omzet, zijn / haar uitbetaling tarief zal meestal toenemen.

het uitbetalingspercentage zou kunnen zijn een plat dollarbedrag, percentage van de inkomsten, percentage van de quota, enz. De taak van de VLOOKUP is om het verkoopbedrag van de rep in de tarieftabel te vinden en het overeenkomstige uitbetalingspercentage terug te geven.

voor dit voorbeeld ziet ons commissieplan er als volgt uit:

• Rep verkoopt $ 0 – $ 50.000, ze verdienen 5%
• Rep verkoopt $ 51.000 – $ 100.000, ze verdienen 7%
• Rep verkoopt $ 100.001 – $ 150.000, ze verdienen 10%
• Rep verkoopt meer dan $ 150.001, ze verdienen 15%

de rep zal een bepaald bedrag van de verkoop te maken tijdens de maand. Hij / zij zal verdienen een bepaalde uitbetaling tarief afhankelijk van het niveau van de omzet bereikt.

we kunnen een VLOOKUP-formule gebruiken om het uitbetalingspercentage voor een bepaald verkoopbedrag (opzoekwaarde) te berekenen. Om dit te laten werken moeten we het laatste argument in de vlookup op TRUE zetten.

met het laatste argument op TRUE gezet, zal vlookup de dichtstbijzijnde overeenkomst vinden met de opzoekwaarde die kleiner is dan of gelijk is aan de opzoekwaarde. Dit stelt ons in principe in staat om een waarde te vinden tussen bereiken van twee getallen (tiers).

daarom heet het laatste argument in VLOOKUP range_lookup. Wanneer dit argument waar is, zoekt VLOOKUP tussen waarden in de kolom tier minimum om een exacte overeenkomst of de waarde kleiner dan de opzoekwaarde te vinden.

hoe de tarieftabel

in te stellen wanneer u deze tarieftabel in Excel plaatst, hoeft u alleen het tierminimum voor het opzoekbereik op te geven. Opnieuw zal de VLOOKUP zoeken naar een” dichtstbijzijnde overeenkomst ” die kleiner is dan of gelijk is aan de opzoekwaarde. Als het een waarde vindt die groter is dan de opzoekwaarde, retourneert het de vorige rij.

in dit voorbeeld voor een commissietabel moet de eerste rij in de lookup range nul zijn. Dit komt omdat de vertegenwoordiger zou kunnen verkopen van $0 en de lookup waarde nul zou zijn. Als de opzoekwaarde (verkoopbedrag) een negatief getal was, zou de vlookup een fout retourneren.

het is belangrijk om dit te weten en uw tarieftabel in te stellen voor alle mogelijke lookup waarden.

als het verkoopbedrag groter is dan de laatste rij in het opzoekbereik, retourneert de vlookup de laatste rij. Bijvoorbeeld, als de rep maakte een verkoop van $175.000 dan vlookup zou terugkeren 15%.

Bereken commissies om een dollarwaarde

terug te geven de uitbetaling kan ook worden geretourneerd als een dollarwaarde, in plaats van een percentage. Met deze setup de uitbetaling zal een vast tarief.

dit betekent dat de uitbetaling hetzelfde zal zijn, ongeacht het verkoopbedrag binnen het tier. In het onderstaande voorbeeld, als de uitbetaling zal zijn $ 1,000 als het verkoopbedrag is $ 55,000 of $ 95,000.

de uitbetaling gebeurt niet volgens een glijdende schaal. Het is een vast tarief voor elk niveau.

als u op zoek bent naar een glijdende schaalberekening, zie mijn artikel over het berekenen van commissie met een gelaagde tariefstructuur met behulp van SUMPRODUCT.

Commissieberekeningen met XLOOKUP

kunnen we ook de nieuwe XLOOKUP functie gebruiken voor deze berekening. Het werkt zeer vergelijkbaar met VLOOKUP bij het vinden van de dichtstbijzijnde match.

voordelen met XLOOKUP

er zijn twee belangrijke verschillen en voordelen met XLOOKUP:

1. met XLOOKUP specificeren we de lookup array en retourneren array als afzonderlijke bereiken. Dit is B4: B7 en D4: D7 in de afbeelding hierboven. Dit is een voordeel ten opzichte van VLOOKUP omdat we rijen tussen de kolommen B en D kunnen invoegen of verwijderen en de formule nog steeds werkt.
   wanneer we kolommen toevoegen / verwijderen binnen de tabelarray van VLOOKUP, moeten we het kolomindexnummer handmatig wijzigen of een formule gebruiken om het dynamisch te berekenen.
2. XLOOKUP vereist niet dat de gegevens gesorteerd worden bij gebruik van exacte overeenkomst of het volgende kleinere/grotere item voor match-modus. XLOOKUP zal daadwerkelijk zoeken naar het volgende kleinere / grotere item en het resultaat van die rij/kolom retourneren.
   VLOOKUP vereist dat de gegevens gesorteerd worden wanneer het laatste argument waar is. Meestal wilt u uw gegevens te sorteren voor dit soort tabellen anyways, maar het is goed om te weten dat je gewoon kunt zetten nieuwe tarief niveaus aan de onderkant van de tabel en XLOOKUP zal werken.

nadeel van XLOOKUP

het grootste nadeel van XLOOKUP is compatibiliteit. Zowel u als alle gebruikers van uw bestand moet op een versie van Microsoft/Office 365 die XLOOKUP heeft. Het is niet achterwaarts compatibel of beschikbaar op oudere versies van Excel.

daarom werkt VLOOKUP geweldig in dit scenario en is het veel gemakkelijker om te schrijven dan een geneste if-formule.

vermijd geneste IF-formules

een gemeenschappelijke benadering voor het berekenen van commissies is het gebruik van IF-verklaringen. Met een tarieftabel als deze zou je meerdere IF statements moeten schrijven. Je moet in principe een IF statement schrijven voor elke rij (rij) in de tabel.

dan moet u alle if statements samenvoegen in één lange en lelijke formule. Deze worden geneste IF statements genoemd.

ik probeer altijd geneste if ‘ s te vermijden wanneer dat mogelijk is omdat ze moeilijk te lezen en te begrijpen zijn, en ze kunnen langzamer zijn voor Excel om te berekenen. Als u duizenden geneste IF-formules in uw werkmap hebt, kan uw rekentijd vertragen.

de IF-formule is veel moeilijker te handhaven. Het vereist wijziging wanneer rijen worden toegevoegd / verwijderd uit de tabel. VLOOKUP en XLOOKUP hebben dit type onderhoud niet nodig.

als u IF gebruikt, zorg er dan voor dat elke celverwijzing naar de tarieftabel een absolute referentie is (F4 op het toetsenbord). Anders krijgt u onjuiste resultaten bij het kopiëren van de formule.

het gebruik van VLOOKUP is veel eenvoudiger en schoner dan het gebruik van geneste IF-formules. Zoals u kunt zien met dit voorbeeld, kunt u met de vlookup één formule gebruiken om het commissieuitkeringspercentage voor een bepaald verkoopbedrag te berekenen.

Extra Bronnen

• Hoe Gebruik VERT.ZOEKEN om het Vinden van de beste Match – Laatste Argument is Gelijk aan TRUE
• het Berekenen van de Cumulatieve Gedifferentieerde Opdrachten met SOMPRODUCT
• VERT.ZOEKEN Uitgelegd in Eenvoudige Termen bij Starbucks
• VERT.ZOEKEN & MATCH – EEN Dynamische Duo
• De Nieuwe XLOOKUP Functie voor Excel + Video Tutorial
• ALS Functie Uitgelegd: Het Schrijven van een Instructie ALS een Formule in Excel

Hoe Gebruik Je de functie VERT.ZOEKEN met de beste Match?

de VLOOKUP with closest match techniek kan ook worden gebruikt voor belastingschijf berekeningen, prijs matching, enz. Wat gebruik je deze techniek om te berekenen?