Preisberechnung Aufschlagtabelle

[hund1234]

Hallo,

habe ein Problem. 

Meine Ausgangssituation ist folgende:

Ich habe eine Aufschlagtabelle: 
zb von 5 € bis 9 € 5 % Aufschlag
von 9,5 € bis 10 € 6 % Aufschlag
usw
und zudem auch Festaufschläge
von 13 € bis 14 € 5€ Aufschlag


Nun soll es folgendermaßen aussehen.

Ich gebe bei Menge und Preis ein. Excel sucht die richtige Preisspanne schlägt den Prozentualen Aufschlag auf, zieht einen fixen Prozentsatz ab und teilt dieses Ergebnis wieder durch die Menge um den Einzelpreis zu erhalten.

Irgendwie bin ich zu blöd dafür.

Würde mich über Hilfe freuen.

Vielen Dank

Grüße

[echo]

hallo
was ist zB. mit dem Aufschlag zwischen 9 und 9,49€?
Auch die Geschichte der Festaufschläge müsstest du genauer definieren.
Deine Angaben sind ein wenig widersprüchlich oder unvollständig.

egal

Erzeuge eine Preisliste die aufsteigend sortiert ist und daneben die Aufschläge enthält.
Diese fragst du per SVerweis() ab.
http://www.online-excel.de/excel/singsel.php?f=9

Gruß Holger

[WillWissen]

Hi,

ergänzend zu Holgers Beitrag: es reicht ein einfacher VERWEIS, wenn, wie dargestellt lediglich zwei Spalten vorhanden sind, die aufsteigend sortiert wurden.

Tabelle1

	C	D	E
1	Ges.Preis	Aufschlag	Neuer Preis
2	6,00 €	1,05	6,30 €
3	6,00 €	1,05	6,30 €

Formeln der Tabelle

Zelle Formel D2 =SVERWEIS(C2;$I$2:$J$12;2) D3 =VERWEIS(C3;$I$2:$J$7)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Bei einem SVERWEIS muss die Referenzliste nicht sortiert sein!

[hund1234]

Hallo,

danke für die Antwort.

Anbei eine übersichtlichere Version.


ab 543,92 € + 8,263% + 118,24

Am ende müssen von jedem Ergebnis 5 % abgezogen werden und danach wieder durch die Menge geteilt werden.

Grüße

[WillWissen]

Hi,

nach den Vorgaben deiner Beispieltabelle:

Tabelle1 (2)

	B	C	D	E	F	G	H	I	J	K	L
1				Aufschlag	HS
2	0	bis	1,22 €	0,68	P		Menge	Preis	HS	Einzel-Brutto	Gesamt
3	1,23 €	bis	1,34 €	0,83 €	F		2	0,88	P	1,48 €	2,96 €
4	1,35 €	bis	3,88 €	0,62	P		2	1,25	F	2,08 €	4,16 €
5	3,89 €	bis	4,22 €	2,41 €	F		2	9	P	13,32 €	26,64 €
6	4,23 €	bis	7,30 €	0,57	P		2	600	P	649,58 €	1.299,16 €
7	7,31 €	bis	8,67 €	4,16 €	F
8	8,68 €	bis	12,14 €	0,48	P
9	12,15 €	bis	13,55 €	5,83 €	F
10	13,56 €	bis	19,42 €	0,43	P
11	19,43 €	bis	22,57 €	8,35 €	F
12	22,58 €	bis	29,14 €	0,37	P
13	29,15 €	bis	35,94 €	10,78 €	F
14	35,95 €	bis	543,91 €	0,3	P
15	543,92 €			0,08263	P

Formeln der Tabelle

Zelle Formel J3 =SVERWEIS(I3;$B$2:$F$15;5;1) K3 =WENN(J3="P";$I3+$I3*SVERWEIS(I3;$B$2:$F$15;4;1);WENN(J3="F";$I3+SVERWEIS(I3;$B$2:$F$15;4;1)))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8


Du brauchst bei meinem Vorschlag zwei Hilfsspalten (einmal in der Referenztabelle und die zweite neben deiner Auswertung), wobei du die neben der Auswertung einfach ausblenden kannst, falls sie stört.

[Jockel]

Hallo Günter in der Formel in K fehlt der Sonst_Wert... Absicht..? Ist nur 'ne Frage. Die Richtigkeit der Rechnerei habe ich nicht überprüft, aber man sie etwas verschlanken..:

Code:

=WENN(I3="";"";I3+SVERWEIS(I3;$B$2:$F$15;4;1)*WENN(J3="P";I3;1))

[WillWissen]

Hi Jörg,

der fehlende Sonst-Wert war tatsächlich ein Versehen; deine Verschlankung der Formel super. Trotzdem der Vollständigkeit halber für den TE meine um den Sonst-Wert ergänzte Formel:

Code:

=WENN(J3="P";$I3+$I3*SVERWEIS(I3;$B$2:$F$15;4;1);WENN(J3="F";$I3+SVERWEIS(I3;$B$2:$F$15;4;1);""))

[hund1234]

Hallo zusammen,

vielen Dank für die super Antworten.

Leider bekomme ich nicht die Richtigen werte raus.

Bei beispielsweise 500€ müsste 500 + 30 % = 650: danach 650 - 5% = 617,5€ wäre das Ergebnis

Excel rechnet mit der Tabelle aus dem Thread aber leider: 619,04 € aus

Am ende müssen immer 5% abgezogen werden.


Wo liegt der Fehler?

edit: Ah lag an mir. Habe einen Fehler bei der Prozentrechnung gemacht.
Kurze Frage noch am ende. Wie bekomme ich bei der Preisspanne ab 543,92 noch die 118,24 € drauf?
Zudem muss die Berechnung wie folgt aussehen. 
Menge * Preis = X danach erst den Aufschlag suchen und addieren. Danach Fix 5 Prozent abziehen und danach wieder durch die Menge teilen

Hoffe, dass es verständlich ist

Grüße

[Jockel]

Hallo, passt das in deinen Kram..?  Wohl nicht, ich habe den Schluss mit den 118,xx zu spät gelesen.. 

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G	H	I	J
1					Aufschlag
2	von		bis	1,22 €	68%			Menge	Preis
3		1,35 €	bis	3,88 €	62%			2	43	106,21
4		4,23 €	bis	7,30 €	57%			1	500	617,5
5		8,68 €	bis	12,14 €	48%			1	550	565,67
6		13,56 €	bis	19,42 €	43%
7		22,58 €	bis	29,14 €	37%
8		35,95 €	bis	543,91 €	30%
9	ab	543,92 €			8,263%	118,24 €

Zelle	Formel
J3	=DM(H3*I3*(1+VERWEIS(I3;B1:E8))*0,95)*1

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

[WillWissen]

Hi,

falls du mit meiner (längeren) Formel arbeitest, dann so:

Tabelle1 (2)

	H	I	J	K	L
7	1	543,91 €	P	707,08 €	707,08 €
8	1	543,92 €	P	588,86 €	588,86 €

Formeln der Tabelle

Zelle Formel K7 =WENN(J7="P";$I7+$I7*SVERWEIS(I7;$B$2:$F$15;4;1);WENN(J7="F";$I7+SVERWEIS(I7;$B$2:$F$15;4;1);WENN(I7>=$B$15;$I7+$I7*SVERWEIS(I7;$B$2:$F$15;4;1)+118,24;"")))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Ich habe allerdings den Abzug der 5% nirgendwo berechnet. Aber ich denke, das schaffst du auch so.  :32:

Tante Edit meint:

Die gepostete Formel ist leider falsch. Der zusätzlich Term gehört an anderer Stelle hin. So ist's richtig:

Code:

=WENN(I3>=$B$15;$I3+$I3*SVERWEIS(I3;$B$2:$F$15;4;1)+118,24;WENN(J3="P";$I3+$I3*SVERWEIS(I3;$B$2:$F$15;4;1);WENN(J3="F";$I3+SVERWEIS(I3;$B$2:$F$15;4;1);"")))