Komplizierte Provisionstabelle

[MatrimTheGambler]

Hallo Excel-Gemeinde,

ich möchte für die Vertriebsleute in Ausbildung ein "Excel-Tool" zur Berechnung der Provision erstellen. Ich bin nun wirklich kein Excel-Profi, dennoch habe ich bereits verschachtelte Wenn-Dann Funktionen geschrieben. Doch aufgrund der Komplexität der Berechnungen habe ich mich gerade in SVERWEIS eingelesen und suche nun Hilfe. Grund: ich will die Tabelle so einfach wie möglich und fehlerfrei halten.

Ich schildere nun einmal kurz das Projekt im Detail (eine Beispieldatei hängt an):

In Spalte A des Tabellenblattes 2 tragen die Vertriebler eigenständig das Produkt ein, um das es sich handelt. In Spalte B erfolgt die Eingabe des erzielten Verkaufserlöses. Spalte C soll nun anhand von Formeln per Multiplikation automatisch errechnen, welche Provision anfällt. Im Tabellenblatt 1 tragen die Vertriebler in den entsprechenden Spalten ihre vereinbarten Provisionssätze ein. Dies klingt nun erst mal recht simpel.

Problem jedoch: die Vertriebler haben Staffeln in ihren Provisionen, die teilweise pro Produkt unterschiedliche Provisionssätze auslösen. (Siehe nun Tabellenblatt 2) So sorgt zum Beispiel ein Wert von <=150 beim Produkt KV für eine Verdopplung, während ein Wert zwischen 151 und 300 für eine Verdreifachung, ein Wert von über 300 für eine Vervierfachung sorgt. Das Produkt GI neu sorgt für 30%, wenn es jedoch um eine bloße Anpassung geht fallen 15% an. usw ...

Wie kann ich nun - eventuell auch mit SVERWEIS - eine fehlerfrei und möglichst unkomplizierte Berechnung erfolgen lassen?

Vielen Dank für Eure Lösungsvorschläge / Euer Feedback

 

[owieortho]

Damit der SVERWEIS sauber funktionieren kann, muss die Spalte A sortiert sein, das vorweg.
Ich gebe dann üblicherweise Tabellenblatt1!Spalte A einen Namen, sagen wir mal "Produkte". Das nutze ich dann als Auswahlliste, indem Tabellenblatt2!Spalte A eine Gültigkeitsprüfung Liste mit dem Wert =Produkt bekommt.

Der Bereich Tabellenblatt2!A:B bekommt ebenfalls einen Namen, bspw. "Matrix". Dann lautet nun der SVERWEIS in Tabellenblatt1!Spalte B:
=SVERWEIS(A2;Matrix;2)
Du musst nun nur aufpassen, die Namensbereiche anzupassen, wenn Du neue Provisionen hinzufügst. Am einfachsten geht das, wenn Du mitten in der Matrix Zeilen einfügst und anschließend neu sortierst. Das ist schneller als über den Namensmanager.

Viel Erfolg
O.

 

[Chris_EDI]

Damit der SVERWEIS sauber funktionieren kann, muss die Spalte A sortiert sein, das vorweg.

In dem Fall, wenn die Artikel über eine Auswahlliste eingetragen werden und damit immer in der Matrix vorhanden sind, braucht man nicht unbedingt sortieren. Im SVERWEIS muss dann aber als letzter Parameter FALSE (=genaue Übereinstimmung) stehen. =SVERWEIS(A2;Matrix;2;FALSE)

Sehe gerade, dass die Provision pro Artikel auch variiert, dass könntest du mit einer verschachtelten WENN Funktion bei der Berechnung des Erlöses berechnen. Werde mir das zu Mittag genauer ansehen.

 

[MatrimTheGambler]

Ok klasse, danke schon mal, werde mir das im Lauf des Tages mal ansehen.
Frage an Chris_EDI: könntest du mir mal so ne Beispielhafte Formel mit inkludiertem Verweis zeigen? Wär super!

 

[owieortho]

braucht man nicht unbedingt sortieren

In meinem Beispiel führt das aber auch dazu, dass die Auswahlliste immer schön sortiert ist, was bei längeren Listen hilfreich ist.
O.

 

[Chris_EDI]

immer schön sortiert ist, was bei längeren Listen hilfreich ist.

... und auch besser aussieht

 

[Chris_EDI]

Hier eine Lösung, die allerdings nur einen Multiplikationsfaktor berücksichtigt.
Falls er tatsächlich für jeden Artikel unterschiedlich ist, müsste man das in die Artikel-Tabelle eintragen. Lösung habe ich dafür aber noch keine
Edit: Vielleicht nach Artikelgruppen aufteilen??

 

[Chris_EDI]

Lösungsansatz mit Staffeln/Artikel. (Noch nicht wirklich überprüft)

 

[MatrimTheGambler]

Hey, vielen vielen Dank! ich schaue am WE mal, wie ich es am Besten in meine eigene Tabelle dann einflechten kann. Sieht auf jeden Fall schon mal sehr gut aus!

 

[Chris_EDI]

Gerne!
Hier noch der Link zur