Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Sonstiges

Beitrag: saldierte/aufgefüllte Staffelpreise

Aufgabe
Im Gegensatz zu unserer Formel Nr. 124 (Sonstiges) wird nicht ein Preis für die Gesamtmenge angesetzt, sondern die darunter liegenden Mengen werden mit den entsprechenden Preisen berücksichtigt.

       A               B               C               D       
1 Preis  bis Menge  bestellt   
2        
3 50,00  10  460  10  
4 49,00  25    15  
5 48,00  50    25  
6 46,00  100    50  
7 44,00  250    150  
8 40,00  500    210  
9 36,00  1.000    0  
10 30,00  999.999    0  
11        
12        
13 19.735,00       


Lösung
In C3 steht die Gesamtmenge - ab A3 die Staffelpreise - ab B3 die Mengen.

Lösung mit Hilfsspalte D:
in D3 steht:
=MIN(B3;C$3)-SUMME(D$2:D2)
bis D10 runterkopieren
das Ergebnis:
A13:=SUMMENPRODUKT(A3:A10;D3:D10)

das Ergebnis ohne Hilfsspalte D:
{=SUMMENPRODUKT(((B3:B10)-(B2:B9))*(C$3>B2:B9)*A3:A10)-MIN(WENN((B3:B10-C3)*A3:A10>0;(B3:B10-C3)*A3:A10))}

ohne {}-Abschluss als Arrayformel (Josef Burch):
=SUMMENPRODUKT((B3:B10-B2:B9)*(C3>=B2:B9)*A3:A10)-VERWEIS(-1;-(B2:B9<=C3);(B3:B10-C3)*A3:A10)

aus der Finanzpolitik: das 5-Stufen-Steuermodell der FDP 2010
Von A3 bis A8 steht: 0 / 14 / 25 / 35 / 42 / 45
Von B3 bis B8 steht: 8004 / 12500 / 35000 / 53000 / 250730 / =10^9
Das zu versteuernde Einkommen steht in C3.

Grundtabelle:
{=MAX(0;(SUMMENPRODUKT(((B3:B10)-(B2:B9))*(C$3>B2:B9)*A3:A10)-MIN(WENN((B3:B10-C3)*A3:A10>0;(B3:B10-C3)*A3:A10)))/100)}

Splittingtabelle:
{=MAX(0;(SUMMENPRODUKT(((B3:B10)*2-(B2:B9)*2)*(C$3>(B2:B9)*2)*A3:A10)-MIN(WENN(((B3:B10)*2-C3)*A3:A10>0;((B3:B10)*2-C3)*A3:A10)))/100)}