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
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
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!
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
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.
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))
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);""))
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
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 |
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);"")))