Fazit: Erfahren Sie, wie Sie Provisionen in Excel für einen einfachen abgestuften Plan und eine Tarif-Tabelle mithilfe der Funktionen IF, VLOOKUP oder XLOOKUP berechnen.
Schwierigkeitsgrad: Mittelstufe
Video-Tutorial
Die Berechnung von Provisionen in Excel kann eine sehr schwierige Aufgabe sein. Dies gilt insbesondere, wenn Sie versucht haben, mehrere IF-Anweisungen zu verwenden, um Provisionen für jede Stufe in einer Tariftabelle zu berechnen.
In diesem Artikel wird erläutert, wie Sie die VLOOKUP-Funktion verwenden, um diesen Vorgang erheblich zu vereinfachen. Das Geheimnis besteht darin, das letzte Argument im vlookup auf TRUE zu setzen, um die nächstgelegene Übereinstimmung zu finden.
Bitte lesen Sie meinen Artikel über die Verwendung von VLOOKUP, um die nächstgelegene Übereinstimmung zu finden, um eine detaillierte Erklärung zum Setzen des letzten Arguments auf TRUE zu erhalten.
Aktualisieren: Der Beitrag wurde aktualisiert und enthält Anweisungen zur Berechnung von Provisionen mit der neuen XLOOKUP-Funktion. Hier ist ein Artikel & Video auf XLOOKUP.
Datei herunterladen
Laden Sie die Datei herunter, um zu folgen.
Provisionsberechnungen- WENN Vs VLOOKUP Vs XLOOKUP.xlsx (31.5 KB)
Provisionsberechnung mit VLOOKUP
Mit einem einfachen Provisionsplan haben Sie normalerweise eine Ratenübersicht, in der die Auszahlungsrate auf jeder Verkaufsebene aufgeführt ist. Da der Vertriebsmitarbeiter mehr Umsatz macht, wird seine Auszahlungsrate in der Regel steigen.
Die Auszahlungsrate könnte ein flacher Dollarbetrag, Prozentsatz des Umsatzes, Prozentsatz der Quote usw. sein. Die Aufgabe des VLOOKUPS besteht darin, den Verkaufsbetrag des Vertreters in der Tariftabelle zu finden und die entsprechende Auszahlungsrate zurückzugeben.
In diesem Beispiel sieht unser Provisionsplan wie folgt aus:
- Rep verkauft $0-$ 50,000, sie verdienen 5%
- Rep verkauft $51,000-$100,000, sie verdienen 7%
- Rep verkauft $100,001-$150,000, sie verdienen 10%
- Rep verkauft über $ 150.001, verdienen sie 15%
Der Vertreter wird im Laufe des Monats eine bestimmte Menge an Verkäufen tätigen. Er / sie wird eine bestimmte Auszahlungsrate verdienen, abhängig von der Höhe des erzielten Umsatzes.
Wir können eine VLOOKUP-Formel verwenden, um die Auszahlungsrate für einen bestimmten Verkaufsbetrag (Lookup-Wert) zu berechnen. Damit dies funktioniert, müssen wir das letzte Argument im vlookup auf TRUE setzen.
Wenn das letzte Argument auf TRUE gesetzt ist, findet vlookup die nächstgelegene Übereinstimmung mit dem Lookup-Wert, der kleiner oder gleich dem Lookup-Betrag ist. Dies ermöglicht es uns im Grunde, einen Wert zwischen Bereichen von zwei Zahlen (Ebenen) zu finden.
Aus diesem Grund heißt das letzte Argument in VLOOKUP range_lookup . Wenn dieses Argument WAHR ist, sucht VLOOKUP zwischen Wertebereichen in der Spalte tier minimum, um eine genaue Übereinstimmung oder den Wert unter dem Nachschlagewert zu finden.
Einrichten der Tariftabelle
Wenn Sie diese Tariftabelle in Excel einfügen, müssen Sie nur das Tier-Minimum für den Suchbereich auflisten. Auch hier sucht das VLOOKUP nach einer „engsten Übereinstimmung“, die kleiner oder gleich dem Nachschlagewert ist. Wenn ein Wert gefunden wird, der größer als der Nachschlagewert ist, wird die vorherige Zeile zurückgegeben.
In diesem Beispiel für eine Provisionstabelle muss die erste Zeile im Nachschlagebereich Null sein. Dies liegt daran, dass der Vertriebsmitarbeiter möglicherweise einen Umsatz von 0 USD erzielen könnte und der Nachschlagewert Null wäre. Wenn der Suchwert (Verkaufsbetrag) eine negative Zahl wäre, würde der vlookup einen Fehler zurückgeben.
Es ist wichtig, dies zu wissen und Ihre Rate-Tabelle für alle möglichen Lookup-Werte einzurichten.
Wenn der Verkaufsbetrag größer als die letzte Zeile im Nachschlagebereich ist, gibt vlookup die letzte Zeile zurück. Wenn der Vertreter beispielsweise einen Umsatz von 175.000 US-Dollar tätigte, würde vlookup 15% zurückgeben.
Provisionen berechnen, um einen Dollarwert zurückzugeben
Die Auszahlung kann auch als Dollarwert anstelle eines Prozentsatzes zurückgegeben werden. Bei diesem Setup wird die Auszahlung pauschal sein.
Dies bedeutet, dass die Auszahlung gleich ist, unabhängig davon, wie hoch der Verkaufsbetrag innerhalb der Stufe ist. Im Beispiel unten, wenn die Auszahlung $1.000, wenn der Umsatzbetrag $55.000 oder $95.000.
Die Auszahlung erfolgt NICHT auf einer gleitenden Skala. Es ist eine Pauschale für jede Stufe.
Wenn Sie nach einer gleitenden Berechnung suchen, lesen Sie meinen Artikel zur Berechnung der Provision mit einer abgestuften Tarifstruktur mit SUMPRODUCT .
Provisionsberechnungen mit XLOOKUP
Für diese Berechnung können wir auch die neue XLOOKUP-Funktion verwenden. Es funktioniert sehr ähnlich wie VLOOKUP, wenn die nächste Übereinstimmung zu finden.
Vorteile mit XLOOKUP
Es gibt zwei Hauptunterschiede und Vorteile mit XLOOKUP:
- Mit XLOOKUP geben wir das Lookup-Array und das Return-Array als separate Bereiche an. Dies ist B4: B7 und D4: D7 im Bild oben. Dies ist ein Vorteil gegenüber VLOOKUP, da wir Zeilen zwischen den Spalten B und D einfügen oder löschen können und die Formel weiterhin funktioniert.
Wenn wir Spalten innerhalb des Tabellenarrays von VLOOKUP hinzufügen / löschen, müssen wir die Spaltenindexnummer manuell ändern oder eine Formel verwenden, um sie dynamisch zu berechnen. - XLOOKUP erfordert NICHT, dass die Daten sortiert werden, wenn exakte Übereinstimmung oder nächstes kleineres / größeres Element für den Übereinstimmungsmodus verwendet werden. XLOOKUP sucht tatsächlich nach dem nächsten kleineren / größeren Element und gibt das Ergebnis aus dieser Zeile / Spalte zurück.
VLOOKUP erfordert, dass die Daten sortiert werden, wenn das letzte Argument WAHR ist. Normalerweise möchten Sie Ihre Daten sowieso nach diesen Tabellentypen sortieren, aber es ist gut zu wissen, dass Sie einfach neue Ratenstufen am Ende der Tabelle einfügen können und XLOOKUP funktioniert.
Nachteil von XLOOKUP
Der Hauptnachteil von XLOOKUP ist die Kompatibilität. Sowohl Sie als auch alle Benutzer Ihrer Datei müssen sich in einer Version von Microsoft / Office 365 befinden, die über XLOOKUP verfügt. Es ist NICHT abwärtskompatibel oder in älteren Versionen von Excel verfügbar.
Daher funktioniert VLOOKUP in diesem Szenario hervorragend und ist viel einfacher zu schreiben als eine verschachtelte IF-Formel.
Vermeiden Sie verschachtelte IF-Formeln
Ein gängiger Ansatz zur Berechnung von Provisionen ist die Verwendung von IF-Anweisungen. Mit einer solchen Rate-Tabelle müssten Sie mehrere IF-Anweisungen schreiben. Grundsätzlich müssen Sie für jede Ebene (Zeile) in der Tabelle eine IF-Anweisung schreiben.
Sie müssen dann alle IF-Anweisungen zu einer langen und hässlichen Formel zusammenfügen. Diese werden als verschachtelte IF-Anweisungen bezeichnet.
Ich versuche immer, verschachtelte IFs nach Möglichkeit zu vermeiden, da sie schwer zu lesen und zu verstehen sind und für Excel langsamer berechnet werden können. Wenn Sie Tausende von verschachtelten IF-Formeln in Ihrer Arbeitsmappe haben, kann sich Ihre Berechnungszeit verlangsamen.
Die IF-Formel ist viel schwieriger zu pflegen. Es muss geändert werden, wenn Zeilen der Tabelle hinzugefügt / gelöscht werden. VLOOKUP und XLOOKUP erfordern diese Art der Wartung NICHT.
Wenn Sie IF verwenden, stellen Sie sicher, dass jede Zelle, die auf die Ratentabelle verweist, eine absolute Referenz ist (F4 auf der Tastatur). Andernfalls erhalten Sie beim Kopieren der Formel falsche Ergebnisse.
Die Verwendung von VLOOKUP ist viel einfacher und sauberer als die Verwendung verschachtelter IF-Formeln. Wie Sie in diesem Beispiel sehen können, können Sie mit dem vlookup eine Formel verwenden, um die Provisionsauszahlungsrate für einen bestimmten Verkaufsbetrag zu berechnen.
Zusätzliche Ressourcen
- So verwenden Sie VLOOKUP, um die nächstgelegene Übereinstimmung zu finden – Das letzte Argument entspricht TRUE
- So berechnen Sie kumulative abgestufte Provisionen mit SUMPRODUCT
- VLOOKUP in einfachen Worten bei Starbucks erklärt
- VLOOKUP & MATCH – Ein dynamisches Duo
- Die neue XLOOKUP-Funktion für Excel + Video-Tutorial
- IF-Funktion erklärt: So schreiben Sie eine IF-Anweisungsformel in Excel
Wie verwenden Sie VLOOKUP mit der nächsten Übereinstimmung?
Die VLOOKUP with closest Match-Technik kann auch für Steuerklammerberechnungen, Preisanpassung usw. verwendet werden. Was verwenden Sie diese Technik zu berechnen?