Live-Forum - Die aktuellen Beiträge
Datum
Titel
19.04.2024 14:04:05
PDF per VBA ausgeben - Seitenskalierung
19.04.2024 13:58:02
Set Worksheet geht im O365 aber im Excel 2016 nicht
19.04.2024 13:49:38
Per Kopfdruck als pdf speichern und Mail erstellen
19.04.2024 12:58:59
Daten einfügen aus anderem Sheet
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
916to920
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
916to920
916to920
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Staffelpreise - etwas komplizierter

Staffelpreise - etwas komplizierter
15.10.2007 20:49:39
Bernd
Ich muss aufgrund einer Preisstaffe einen Gesamtpreis aufgrund einer bestimmten Stückzahl ermitteln. Die Staffel ist wie folgt aufgebaut:
Tabelle1

 ABC
1Gruppeab StckPreis
200120
311000100
42200080
53300050
64400040
75500030


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Wenn jemand 2500 Stck. bestellt, zahllt er für die ersten Tausend eine Stückpreis von 120, für die zweiten Tausend einen Stückpreis von 100 und für die restlichen 500 einen Stückpreis von 80, d.h.
1000 * 120 = 120000
1000 * 100 = 100000
500 * 80 = 40000
insgesamt also 260000.
Leider schaffe ich es nicht, für die Berechnung des Gesamtpreises eine Formel aufzubauen, damit gleich der Gesamtpreis - im Beispiel 260000 ermittelt wird. Ich muss noch ergänzen, dass es nicht nur 5 Preisgruppen gibt, wie im Beispiel, sondern insgesamt 20.
Vielleicht ist die Lösung ja relativ einfach, aber ich finde keine!

34
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Vorschlag mit Hilfsspalten
15.10.2007 21:10:00
Christian
Hallo Bernd,
anbei ein Ansatz mit Hilfsspalten:
Tabelle2

 ABCDEFG
1Gruppeab StckPreis Stückzahl  
200120 2.5001.000 
311000100 Staffelung1.000 
42200080 1.000500 
53300050 Endpreis0 
64400040 260.0000 
75500030  0 
8       
9       

Formeln der Tabelle
ZelleFormel
F2=WENN(E2>=E4;E4;REST(E2;E4))
F3=WENN(SUMME($F$2:F2)+$E$4>$E$2;REST($E$2-SUMME($F$2:F2); $E$4); $E$4)
F4=WENN(SUMME($F$2:F3)+$E$4>$E$2;REST($E$2-SUMME($F$2:F3); $E$4); $E$4)
F5=WENN(SUMME($F$2:F4)+$E$4>$E$2;REST($E$2-SUMME($F$2:F4); $E$4); $E$4)
E6=SUMMENPRODUKT(C2:C7;F2:F7)
F6=WENN(SUMME($F$2:F5)+$E$4>$E$2;REST($E$2-SUMME($F$2:F5); $E$4); $E$4)
F7=WENN(SUMME($F$2:F6)+$E$4>$E$2;REST($E$2-SUMME($F$2:F6); $E$4); $E$4)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
MfG Christian

Anzeige
AW: Staffelpreise - etwas komplizierter
15.10.2007 21:29:00
Daniel
Hallo
könnte mit folgender Formel gehen: (Stückzahl steht in E1, Staffelpreise sind Fix)
=120*MIN(1000;E1) + 100*MIN(1000;E1-1000)*(E1>1000) + 80*MIN(1000;E1-2000)*(E1>2000)...


die Formel ist jetzt nur bis zur 3. Gruppe geschrieben und muß noch fortgesetzt werden, aber ich hoffe, das Prinzip wird klar.
Gruß, Daniel

AW: Staffelpreise - etwas komplizierter
15.10.2007 22:39:00
Bernd
Hallo!
Vielen Dank für die Vorschläge. Es gibt allerdings noch kleine Probleme.
Bei dem Vorschlag von Christian wird eine feste Staffelung unterstellt.
Leider ist die Staffelung bei meiner Preisermittlung nicht immer einheitlich 1000, sondern wird bei größeren Mengen 5000 bzw. 10000.
Die vorgeschlagene Formel von Daniel wird meiner Meinung nach bei einer Staffel mit 20 Preisgruppen etwas zu umfangreich.
Mfg
Bernd

Anzeige
AW: Staffelpreise - etwas komplizierter
15.10.2007 22:44:43
Lars
Hi,
du solltest die Vorteile von VBA schätzen lernen, damit geht es, wie fast immer, besser
und einfacher, als mit Formeln.
mfg Lars

Einspruch !
15.10.2007 22:57:25
Daniel
Hi
das ist so nicht richtig.
Formeln haben gegenüber VBA-Lösugen auch Vorteile.
Gruß, Daniel

AW: Einspruch !
15.10.2007 23:10:00
Lars
Hi,
ja ja, ich ahne den Sinn des Einspruchs. Natürlich sollte man in VBA die enormen Geschwindigkeits-
vorteile der Worksheetfunction nutzen, wenn immer das möglich ist. Ansonsten sehe ich keinen
Grund, Formeln zu verwenden.
mfg Lars

AW: Einspruch !
15.10.2007 23:37:00
Daniel
Hi
es ist nicht nur der Geschwindigkeitsvorteil und es sind schon gar nicht die Worksheetfunctions
ich sehe in einer Formel-Lösung auch noch folgende Vorteile:
- ein Makro muss immer explizit gestartet werden, Formellösungen berechnen sich selbsttätig neu.
- bei einer Formellösung kann ich einfach Zeilen löschen und einfügen, die Formeln passen sich automatisch an. Ein Makro muss in diesem Fall aufwendig überarbeitet werden ( oder mit erhöhtem Aufwand flexibel gestaltet werden)
- für die Erstellung und Wartung eines Makros sind Programmierkenntnisse erforderlich, für Formeln nicht.
- auch in komplexen Projekten lässt sich jede Formel einzeln entwickeln und testen, komplexe Makros funtionieren oft nur als ganzes, so daß das austesten von Einzelstellen aufwendig werden kann (insbesondere in Schleifen mit grossen Datenmengen)
Und selbst wenn ich Makros schreibe, kommt es oft vor, daß diese Makros hauptsächlich Excelformeln an der richtigen Stelle in der Tabelle einfügen.
Gruß, Daniel

Anzeige
AW: Einspruch !
15.10.2007 23:44:52
Lars
Hi,
"...- für die Erstellung und Wartung eines Makros sind Programmierkenntnisse erforderlich, für Formeln nicht..."
Dafür sind aber Formelkenntnisse nötig, die oft schwieriger zu erlernen sind, als VBA, weil sie nicht
der humanen Denkweise entsprechen, VBA dagegen schon eher.
Ende des Beitrags.
mfg Lars

Die ganze Diskussion ist...
15.10.2007 23:45:00
{Boris}
Hi Daniel,
...mit Blick auf die sehr überschaubare Formelvariante völlig sinnlos.
Grüße Boris

AW: Die ganze Diskussion ist...
15.10.2007 23:55:58
Daniel
zugegeben, wir sind etwas vom eigentlichen Thema abgewichen.
aber prinzipiell ist es schon ne spannende Frage, wann nehme ich ne Formellösung und wann verwende ich VBA?
vielleicht liegt das Opitmum da darin, wenn ich beisdes geschickt kombiniere?
Gruß, Daniel

Anzeige
Ja, interessant, aber...
16.10.2007 00:09:00
{Boris}
Hi Daniel,
...da hast Du Dir wohl den falschen Diskussionspartner ausgesucht.
Grüße Boris

AW: Ja, interessant, aber...
16.10.2007 00:13:08
Daniel
Stimmt,
wer bei solchen Aufgaben schon nach VBA-Lösungen sucht, kennt sich nicht wirklich mit Formeln aus.
Gruß, Daniel

AW: Ja, interessant, aber...
16.10.2007 00:16:59
Lars
Hi,
bemerke, Diskussion zwecklos. Es geht nicht ums Auskennen, mehr um die Sinnhaftigkeit
und die Effektivität.
mfg Lars

AW: Ja, interessant, aber...
16.10.2007 00:26:05
Daniel
Hi
nur wer sich auskennt, kann Sinnhaftikeit und Effektivität richtig einschätzen.
Gruß, Daniel

AW: Ja, interessant, aber...
16.10.2007 00:14:20
Lars
Hi,
wie ist das wohl zu deuten? Meinst du, du wärst dazu geeigneter?
mfg Lars

Anzeige
Ich vermisse lediglich....
16.10.2007 00:16:33
{Boris}
Hi Lars,
...Deinen Lösungsvorschlag zu diesem Thema - dann reden wir gerne weiter.
Grüße Boris

AW: Ich vermisse lediglich....
16.10.2007 00:19:56
Lars
Hi,
"...Der Einfachheit halber mit einer Hilfsspalte..."
Woher weißt du eigentlich, dass das so möglich ist, kennst du die Tabellenstruktur?
mfg Lars

Ich kenne sie natürlich nicht...
16.10.2007 00:21:00
{Boris}
Hi Lars,
...aber eine der 256 Spalten wird sicher frei sein.
Nochmal: Wo ist Dein Vorschlag?
Grüße Boris

AW: Ich vermisse lediglich....
16.10.2007 00:33:00
Daniel
HI
wie ich schon schrieb, in einer reinen Formellösung kann ich einfach einfach Spalten einfügen und muss mir keine weiteren Gedanken machen, das Formelwerk passt sich im Hintergrund automatisch an.
Speicherresidenz hat auch Vorteile.
Gruß, Daniel

Anzeige
Nochmals...
16.10.2007 00:48:00
{Boris}
Hi Daniel,
...Du diskutierst hier mit dem Falschen:
Nicht willens, eine geeignete VBA-Lösung zu posten, aber darüber diskutieren, ob eine Hilfsspalte (in einem der zig möglichen Blätter mit der Staffelpreismatrix) wirklich möglich ist.
Es lohnt nicht, sich mit Lafontaine über die CDU zu unterhalten.
Grüße Boris

@Boris : *fg* "Lafontaine und die CDU"
16.10.2007 00:54:00
NoNet
Hi Boris,
ich stimme Dir voll zu : Ich "amüsiere" mich auch immer über die Theoretiker, die nur rummäkeln, aber trotz mehrfacher Bitte nicht in der Lage sind, einen adäquaten Lösungsvorschlag zu präsentieren.
Bin mal gespannt, wann Oskar der Christlichen Fraktion beitritt - oder konvertiert G. Beckstein doch noch zu den "Linken" ;-) ?
Gruß und Gute Nacht, NoNet

Anzeige
AW: Die ganze Diskussion ist...
16.10.2007 00:11:00
Lars
Hi,
ist doch ganz einfach, Formeln nur dann, wenn sie sich nicht vermeiden lassen.
Formeln sind speicherresident*, VBA nicht.
mfg Jan
*Zitat eines geschätzten Solvers des Forums.

AW: Die ganze Diskussion ist...
16.10.2007 00:23:00
Daniel
Hi
das ist grundsätzliche ne wertneutrale Eigensschaft, obs ein Vor- oder Nachteil ist, hängt von verschiedenen Faktoren ab.
Gruß, Daniel

In 3 von deinen 4 Punkten irrst du,...
16.10.2007 02:06:09
deinen
...Daniel!
Es gibt nämlich auch noch benutzerdefinierte Funktionen (UDF), die direkt im TabBlatt verwendet wdn können analog den xlStandardfktt (übrigens hat MS einige Zusatzfktt auch nur in dieser Art beigefügt) und die dann annähernd analog fktionieren. Außerdem gibt es ja noch die Möglichkeit, Subroutinen ("Makros") von Ereignissen auslösen zu lassen. Und dann existieren auch noch die XLM-Funktionen und -Prozeduren aus xl4, die ggüber VBA noch andere Zugriffsmöglichkeiten bieten und z.T. auch über benannte Formeln eingesetzt wdn können.
Die xl/vb-Welt ist keine Scheibenwelt (auch, wenn es Manchem so scheinen mag), sondern vielschichtig und mehrdimensional... ;-)
Gruß Luc :-?

Anzeige
AW: In 3 von deinen 4 Punkten irrst du,...
16.10.2007 22:03:00
deinen
Hi
ja, die UDF kenne ich auch, ich würde das mal als Zwischenschritt zwischen VBA und Formel bezeichnen.
die Kunst ist halt, aus dem vielfältigen Angebot an Lösungsmöglichkeiten die optimale Herauszufinden.
Gruß, Daniel

AW: Staffelpreise - etwas komplizierter
15.10.2007 22:46:53
Daniel
Hi
jenü, 20 Preisstaffelungen sind auch viele.
da es keine Klammerebenen gibt, sollte es aber kein Problem sein, alles in eine Formel zu packen, ist halt Tipparbeit.
oder du verwendest halt doch Hilfsspalte.
in diese Hifsspalte fügst du dann die einzlnen Summanden meiner Formel ein und bildest dann die Summe über die Hilfsspalte.
mit ein bisschen Geschick und kreativer Mischung von Absolutem und Relativem Zellbezug kannst du die Formeln in der Hilfsspalte auch so aufbauen, daß die sie Werte aus deiner Staffeltabelle ziehen und du die Formel nur einemal eingeben musst und dann nach unten kopieren kannst.
gruß, Daniel

Anzeige
AW: Staffelpreise - etwas komplizierter
15.10.2007 22:47:00
Christian
Hallo Bernd,
die Staffelung wird in meinem Bsp. in E4 festgelegt, ist sozusagen variabel ;-)
Da könnte man noch eine Abfrage einbauen, die die Staffelung wiedergibt, je nach dem die Bestellmenge ist.
MfG Christian

Staffelpreise - Miniformeln
15.10.2007 23:44:00
{Boris}
Hi Bernd,
mit Formeln ist das ein Klacks - da brauchst Du nun wirklich kein VBA für. Der Einfachheit halber mit einer Hilfsspalte. Alles bezogen auf Dein Beispiel:
Die Bestellmenge (2.500) steht in E1.
In D2 schreibst Du:
=MIN(E1;B3)
In D3:
=MIN(B3;E$1-SUMME(D$2:D2))
und soweit runterkopieren, wie Du Staffelpreise hast.
Den zu zahlenden Gesamtbetrag erhälst Du mit:
=SUMMENPRODUKT(C2:C99;D2:D99)
Grüße Boris

Mit meinen Funktionen zahle ich weniger
16.10.2007 00:58:00
NoNet
Hey {Boris},
schau Dir bitte mal meinen Lösungsvorschlag an, der berechnet für 2500 Stück nur einen Betrag von 220.000 € (= 2000 * 80 € + 500 * 120 €).
Gruß, NoNet

Deine Lösung geht aber...
16.10.2007 01:12:00
{Boris}
Hi Nonet,
...leider an der Aufgabe vorbei. Schau Dir mal das Beispiel des Fragers an ;-)
Grüße Boris

Hatte etwas POSITIVER gedacht
16.10.2007 01:29:00
NoNet
Jep {Boris],
meine Version war etwas progressiver im Sinne des Kunden !
Tatsächlich fragte Christian jedoch nach der konservativeren Lösung (im Interesse des Verkäufers) - da waren sie also wieder mal, diese beiden unterschiedlciehn Parteien ;-)
Mit meiner Version (orangefarben) stimmt das Ergebnis dann mit Deinem Wert überein :
So, jetzt gehe ich aber malganz konservativ ins Bett um mir eine progressive Nachtruhe zu genehmigen ;-)
Bis denne, NoNet

Noch 'ne Lösung mit Hilfsspalten
16.10.2007 00:50:00
NoNet
Hallo Christian,
hier noch ein Lösungsvorschlag mit Hilfsspalten (D und E), in Spalte H die Mengenangabe und der endgültige Preis :
Gruß, NoNet

AW: Noch 'ne Lösung mit Hilfsspalten
16.10.2007 10:46:00
Christian
Hallo NoNet,
danke, aber die Frage kam von Bernd ;-)
MfG Christian

AW: Staffelpreise - endlich mit VBA gelöst
16.10.2007 21:58:00
Daniel
Hi
da Lars ja leider nicht in der Lage ist, eine VBA-Lösung zu liefern, hier mal als Zwischenschritt zur echten VBA-Lösung eine UDF:

Function Staffelpreis(Menge As Long, Staffelpreismatrix As Range) As Double
'Menge: anzahl der gekauften Waren
'Staffelpreismatrix
'   Spalte 1: Stückzahlen, bei denen eine neue Preisgruppe beginnt
'   Spalte 2: Einzelpreis der Preisgruppe
Dim arrStaffel
Dim i As Long
arrStaffel = Staffelpreismatrix
For i = 1 To Menge
Staffelpreis = Staffelpreis + WorksheetFunction.VLookup(i-1, Staffelpreismatrix, 2, 1)
Next
End Function


ok. sie nutzt immer noch eine Worksheetfunction, aber mein Gott, immerhin habe ich dafür bezahlt, daß ich sie nutzen darf und der nichtaufblasbare BillyBoy ist der reichste Mann der Welt.
Gruß, Daniel

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige