Excel Gutschriften ausrechnen?
Hallo, mal wieder eine Excel Frage..
ich hab in einer Tabelle die Umsätze von Kunden; je nach Umsatz bekommen sie eine Gutschrift. z.B. bei einem Umsatz von mindestens 100€ bekommen sie 5€, bei mindestens 1000€ bekommen sie 10€ usw.
Nun brauche ich eine Formel, mit der ich die jeweilige Gutschrift angeben kann. Mit der WENN-Funktion kann ich es selber, es soll aber auch noch eine alternative Formel verwendet werden auf die ich leider nicht komme..
Danke!
4 Antworten
Moin!
Du stellst als erstes eine Tabelle auf mit den Werten entsprechend der Vorgabe:
A1: Umsatz [EUR] - - B1: Rabatt [EUR]
A2: 0 - B2: 0
A3: 100 - B3: 5
A4: 1000 - B4: 10
A5: 2000 - B5: 25
usw.
Jetzt verwendes Du den sverweis (bzw. vlookup auf EN). In meinem Bespiel ist der Einkaufswert in Zelle C1:
= sverweis( C1;$A$2:$B$5;2;1) (die 5 bei B5 anpassen)
Der erste Wert - C1 - wird gesucht. Der zweite Wert - A2:B5, hier fixiert, ist die Matrix, der Wert wird immer in der ersten genannten Spalte gesucht. Der dritte Wert bezeichnet die Spalte, deren Wert zurückgegeben wird - 1 ist die Suchspalte, 2 dann die daneben und so weiter.
Der letzte Wert der Formel - hier 1 - kann 0 oder 1 sein. Bei 0 sucht die Formel den EXAKTEN WERT, also würde z. B. 873 EUR nicht gefunden und das Ergebnis wäre #N/A.
Bei 1 erwartet die Formel eine SORTIERTE LISTE, und findet den nächstKLEINEREN Wert - also bei 99 EUR die 0, bei 100 die 100, bei 873 EUR die 100. Die Matrix geht von A bis B, die zweite Spalte wird ausgegeben - also die 5 (bei 873 EUR).
Viel Spaß
Gruß
*Doppelt, weil ich mehr erklären wollte und dann die Zeit abgelaufen war... *
xthen
Du kannst es mit nem Switch Case machen
https://support.office.com/en-us/article/switch-function-47ab33c0-28ce-4530-8a45-d532ec4aa25e
Mehr dazu hier, wie man es aufbauen kann: https://stackoverflow.com/a/12259830/5759837
Du kannst komplett auf Formeln verzichten, indem sich die Gutschrift aus Wahrheitsprüfungen zusammensetzt, z.b.
=(A1>=100)*5+(A1>=1000)*5
usw, wenn in A1 der Umsatz steht, das Könnte man auch in eine Wahlfunktion implementieren:
=Wahl(1+(A1>=100)+(A1>=1000);0;5;10)
Man kann eine Max Funktion einsetzen:
=Max((A1>=100)*5;(A1>=1000)*10)
Du kannst mittels Sverweis oder Index/Vergleich aus einer sortierten Liste die passende Gutschrift ermitteln.
Es gibt viele Möglichkeiten
Die Spalte A ist hier die Spalte für den bezahlten Preis. Diese kann nach Belieben angepasst werden.
=WENN(A1>2000;"25";WENN(A1>1000;"10";WENN(A1>100;"5";"")))