Office: Mengen mit günstigsten Varianten "auffüllen"

Hallo beisammen,
ich habe ein Problem, das sich so einfach anhört, dass ich mir sicher bin, dass es eine recht einfache Excel-Lösung gibt. Leider kenne ich sie aber nicht...

Hier die Datenlage:

- In Spalte A steht der Name einer Produktvariante
- In Spalte B steht die Menge der Variante, die maximal verfügbar ist
- In Spalte C steht der jeweilige Preis der Variante im Jahr x, in Spalte D der Preis im Jahr y, in E der Preis im jahr z
- Es gibt, sagen wir mal, sieben unterschiedliche Produktvarianten (in Zeilen angeordnet)
- In einem weiteren Zeile ist für jedes Jahr eine Gesamtnachfragemenge angegeben (z.B. im Jahr x 300, Jahr y 250, Jahr z 320)

Aufgabe:
Excel soll die Gesamtnachfragemenge jedes Jahres auffüllen und dabei mit der günstigsten Produktvariante (des Jahres) anfangen und anschließend die jeweils nächst teure Variante nehmen. Sobald die Gesamtnachfrage erreicht (oder überschritten) ist, soll Excel den Preis der LETZT-gewählten Produktvariante ausgeben. Für jedes Jahr steht dann also am Ende der Preis der Variante, die "den letzten Zuschlag" bekommen hat.

Einschränkung:
Die Tabelle soll in einem Stück bleiben und nicht aufgeteilt werden.

Hinweis:
Insgesamt soll die Preisbildung über 20 Jahre beobachtet werden.

Hat jemand eine Idee? Könnte man die Tabelle einfach sortieren, könnte man einen SVerweis nehmen, da die Preise der Varianten aber unterschidlich schwanken, ist aber mal Variante 2 teurer als Variante 3 und mal nicht. Daher ist ein Weg über eine Sortierung nicht möglich und es muss wohl ain VBA ran, oder?

Danke und viele Grüße
Tecksmex

 

Hallo,

ne Bsp.-Datei wäre dabei schon mal ein guter Schritt! :-D

 

Hallo,

Du willst im Prinzip Folgendes:
1) Pro Jahr soll die Gesamtmenge durch die Detailmengen der Produktvarianten aufgebracht werden;
2) Die verfügbaren Detailmengen der Produktvarianten sind beschränkt.
3) Die Detailmengen der Produktvarianten sollen je Jahr so ermittelt werden, dass der Gesamtpreis für die Gesamtmenge (je Jahr) möglichst klein bleibt, das heißt, dass ein Minimum bildet.

Diese Aufgabenstellung ist ein klassisches Anwendungsbeispiel für die sogenannte "lineare Optimierung".
Dafür stellt Microsoft seit vielen Excel-Versionen ein Add-In zur Verfügung: den Solver.

Geht man von diesem Datenmodell aus:

	A	B	C	D
1	Varianten	MaxMenge	PreisJahr1	ZielMenge
2	ProdVar1	500	€ 70,00	100
3	ProdVar2	450	€ 60,00	450
4	ProdVar3	340	€ 90,00	200
5	ProdVar4	550	€ 60,00	550
6	ProdVar5	233	€ 100,00	200
7	ProdVar6	600	€ 40,00	700
8	ProdVar7	800	€ 50,00	800
9		3473		3000
10
11		Notwend.	Durchschn.	Minimale
12		Menge	Preis	Zielkosten
13		3000	€ 57,67	€ 173.000,00

Zelle	Formel
B9	=SUMME(B2:B8)
D9	=SUMME(D2:D8)
C13	=D13/B13
D13	=SUMMENPRODUKT(C2:C8;D2:D8)

so stehen im Bereich D2:D8 die Mengen der Produktvarianten, die insgesamt stets eine notwendige Gesamtmenge von 3000 Einheiten ergeben sollen. Die maximal verfügbaren Mengen pro Produktvariante stehen in B2:B8 (=3473 Einheiten).
In D13 steht der Preis, der sich aufgrund der Mengen D2:D8 ergibt. Dieser Preis soll jedoch ein Minimum (=minimale Zielkosten) ausmachen (=Forderung!). Welche Zielmenge D2:D8 müssen genommen werden?

Das erledigt der Solver mit folgenden Einstellungen, wie sie unten im Solver-Parameter-Dialogfenster gezeigt sind. Indem man abschließend auf den Button 'Lösen' klickt, erhält man folgende Minimumlösung (nur mehr € 168.000,- statt ursprünglich € 173.000,-)

	A	B	C	D
1	Varianten	MaxMenge	PreisJahr1	ZielMenge
2	ProdVar1	500	€ 70,00	500,00
3	ProdVar2	450	€ 60,00	450,00
4	ProdVar3	340	€ 90,00	100,00
5	ProdVar4	550	€ 60,00	550,00
6	ProdVar5	233	€ 100,00	0,00
7	ProdVar6	600	€ 40,00	600,00
8	ProdVar7	800	€ 50,00	800,00
9		3473		3.000,00
10
11		Notwend.	Durchschn.	Minimale
12		Menge	Preis	Zielkosten
13		3000	€ 56,00	168.000,00
14

 

Danke für Eure Antworten,

der Solver ist eventuell ein guter Hinweis. Allerdings ist mein Ziel ein bisschen anders (und meiner Meinung nach viel einfacher). Es geht mir darum, den Preis der teuersten Variante auszugeben, die "noch in den Warenkorb" kommen muss, um mengenmäßig die Gesamtnachfrage zu erfüllen.
In meinem speziellen Fall ist es nämlich so, dass der Preis der Variante, die noch notwendig ist, um die Gesamtnachfrage zu decken, den Preis für die gesamte Lieferung bestimmt. Da Der Warenkorb zuerst mit den günstigsten Varianten gefüllt wird und sukzessiv mit den nächst teuren, bestimmt also letztlich der Preis der teuerste Produktvariante den Gesamtpreis.

Ich brauche als Ausgabe also nur diesen "höchsten Preis", der den letzten "Zuschlag" gekriegt hat...

Im Anhang habe ich mal eine Beispieltabelle skizziert. Alles was vorgegeben ist, ist grün hinterlegt. Das was ich als Ausgabe brauche ist rot (Ist jetzt nicht im 2003er Format, aber im Büro habe ich nur 2003).


Ist das verständlich?
Ist das mit dem Solver möglich?

Beste Grüße!
Tecksmex

 

Hallo!

Wahrscheinlich ist meine Lösung um drei Ecken zuviel gedacht, aber wo sie schon mal fertig ist...
Musst du aber auf Herz und Nieren prüfen, da ich keine Garantie übernehmen kann!
(Die Formel aus C21 nach unten ziehen und dann alle Formeln nach rechts ziehen für die weiteren Jahre.)

	B	C
16		Jahr 1
17	Gesamtnachfrage	450
18	"Zuschlagspreis"	Variante 4
19
20
21		50
22		60
23		160
24		360
25		1110
26		1410
27		1470
28

Zelle	Formel
C18	{=INDEX($B4:$B10;VERGLEICH(KKLEINSTE(D4:D10+ZEILE(4:10)%%%;VERGLEICH(C17;C21:C27)+(ZÄHLENWENN(C21:C27;C17)=0));D4:D10+ZEILE(4:10)%%%;))}
C21	{=INDEX($C$4:$C$10;VERGLEICH(KKLEINSTE(D$4:D$10+ZEILE($4:$10)%%%;ZEILE($A1));D$4:D$10+ZEILE($4:$10)%%%;))+C19}
C22	{=INDEX($C$4:$C$10;VERGLEICH(KKLEINSTE(D$4:D$10+ZEILE($4:$10)%%%;ZEILE($A2));D$4:D$10+ZEILE($4:$10)%%%;))+C21}
C23	{=INDEX($C$4:$C$10;VERGLEICH(KKLEINSTE(D$4:D$10+ZEILE($4:$10)%%%;ZEILE($A3));D$4:D$10+ZEILE($4:$10)%%%;))+C22}
C24	{=INDEX($C$4:$C$10;VERGLEICH(KKLEINSTE(D$4:D$10+ZEILE($4:$10)%%%;ZEILE($A4));D$4:D$10+ZEILE($4:$10)%%%;))+C23}
C25	{=INDEX($C$4:$C$10;VERGLEICH(KKLEINSTE(D$4:D$10+ZEILE($4:$10)%%%;ZEILE($A5));D$4:D$10+ZEILE($4:$10)%%%;))+C24}
C26	{=INDEX($C$4:$C$10;VERGLEICH(KKLEINSTE(D$4:D$10+ZEILE($4:$10)%%%;ZEILE($A6));D$4:D$10+ZEILE($4:$10)%%%;))+C25}
C27	{=INDEX($C$4:$C$10;VERGLEICH(KKLEINSTE(D$4:D$10+ZEILE($4:$10)%%%;ZEILE($A7));D$4:D$10+ZEILE($4:$10)%%%;))+C26}

<table><tr><td>Achtung, Matrixformel enthalten!</td></tr><tr><td><span>Die geschweiften Klammern{} werden </span><span>nicht</span><span> eingegeben.</span></td></tr><tr><td><span>Verlassen Sie den Zelleneditor mit </span><span>Strg+Shift + Enter</span><span>, statt Enter alleine.</span></td></tr></table>[/parsehtml]

 

Hallo Schatzi,

abgesehen von dem kleinen Baumangel in C21, der sicher vom Server verursacht wurde (+C20), ist es ein wundevolles dreieckiges Bauwerk!
Dies Bauwerk ist vergleichbar mit der Erimitage.

RESPEKT! GENIAL! ;-)

 

Hallo Rene!

Fein beobachtet!
Natürlich muss es C20 statt C19 heißen.
(War aber mein Fehler und hat nix mit dem Server zu tun...)

Danke für das Lob!
Nun muss es nur noch funktionieren... :-D

 

Hallo!

So, nun habe ich doch noch einige Ecken etwas abrunden können...

	B	C
16		Jahr 1
17	Gesamtnachfrage	470
18	"Zuschlagspreis"	2,00 €
19
20
21		50
22		60
23		160
24		360
25		1110
26		1410
27		1470

Zelle	Formel
C18	=KKLEINSTE(D4:D10;1+ZÄHLENWENN(C21:C27;"<"&C17))
C21	=SUMMENPRODUKT($C$4:$C$10*(KKLEINSTE(D$4:D$10+ZEILE($4:$10)%%;ZEILE($A1))>=D$4:D$10+ZEILE($4:$10)%%))
C22	=SUMMENPRODUKT($C$4:$C$10*(KKLEINSTE(D$4:D$10+ZEILE($4:$10)%%;ZEILE($A2))>=D$4:D$10+ZEILE($4:$10)%%))
C23	=SUMMENPRODUKT($C$4:$C$10*(KKLEINSTE(D$4:D$10+ZEILE($4:$10)%%;ZEILE($A3))>=D$4:D$10+ZEILE($4:$10)%%))
C24	=SUMMENPRODUKT($C$4:$C$10*(KKLEINSTE(D$4:D$10+ZEILE($4:$10)%%;ZEILE($A4))>=D$4:D$10+ZEILE($4:$10)%%))
C25	=SUMMENPRODUKT($C$4:$C$10*(KKLEINSTE(D$4:D$10+ZEILE($4:$10)%%;ZEILE($A5))>=D$4:D$10+ZEILE($4:$10)%%))
C26	=SUMMENPRODUKT($C$4:$C$10*(KKLEINSTE(D$4:D$10+ZEILE($4:$10)%%;ZEILE($A6))>=D$4:D$10+ZEILE($4:$10)%%))
C27	=SUMMENPRODUKT($C$4:$C$10*(KKLEINSTE(D$4:D$10+ZEILE($4:$10)%%;ZEILE($A7))>=D$4:D$10+ZEILE($4:$10)%%))

 

Vielen Dank!

Vielen Dank für Eure und, Schatzi, insbesondere für Deine Hilfe!
Funktioniert bislang bestens!

Viele Grüße aus Berlin!