Ressourcenoptimierung

Question

Meine Freundin bäckt gerne Kekse, doch jedes Jahr hat sie das Problem, dass ihr Mehl, Eier oder sonstwas übrigbleibt. Ich möchte ihr daher eine Ressourcenoptimierung mit Excel machen. Vereinfacht gesagt habe ich es mir so vorgestellt:

In einer Tabelle erfasse ich die benötigten Zutaten für eine Standardmenge von Teig:

| Keksart | Mehl | Eier | Zucker |
| Keks 1 | 200 | 2 | 150 |
| Keks 2 | 300 | 1 | 200 |


In einer 2. Tabelle erfasse ich die Zutaten, die zur Verfügung stehen:

| Mehl | Eier | Zucker |
| 4000 | 10 | 5000 |

Und nun würde ich mit Excel gerne ermitteln, wieviele Einheiten Keks 1 bzw. Keks 2 man machen muss, um am Ende möglichst wenig Rest bei den Zutaten zu haben.

Ist so etwas überhaupt mit Standardformeln machbar, oder muss man das in jedem Fall programmieren (was ich leider nicht beherrsche)?

Ich wäre für jede Hilfe dankbar, auch wenn es nur ein Denkanstoß ist.

Answer 1

Hi Keksplaner,

bei so einer Aufgabe würde ich eine Verhältnissmäßigkeit auf die kleinste Einheit (bei Dir wäre das wohl 1 Ei, da ohne ein Ei wird es wohl keine Kekse geben können) setzen und dann die Werte hochrechnen lassen.

d.h. für Keks1 wären es dann 100 gr. Mehl und 75 gr. Zucker
Keks 2 steht schon für 1 Ei und somit sind die Grunddaten vorhanden.

Jetzt wäre halt die Frage welche Kekse soll denn zuerst gebacken werden - Keks1 oder Keks2?

Dann würde ich mir noch eine Datentabelle anlegen für Keks1 und Keks2 mit den entsprechenden Zahlnen für 1 Ei, 2 Eier usw...

In einem Arbeitsblatt würde ich mir die möglichen Werte anzeigen lassen - Daten aus Keks1 bei 1 Ei und Daten aus Keks2 bei 9 Eiern (wenn ich 10 Eier zur Verfügung hätte) und die Gesamtsummen zu den Vorgaben von dem vorhanden Mehl und Zucker und das eben in jeder Kombination.
Wo die kleinste Differenz dann wäre, das wäre dann die beste Kombination zum Keksbacken.

Ich hoffe Du konntest meinen Ausführungen folgen. Jedenfalls so kompliziert würde ich vorgehen. Evlt. hat aber noch ein anderer Helferlein eine bessere Idee.

In diesem Sinne... have a nice Day... Gruß Helmut

Answer 2

Ich würde per Programm so vorgehen, wie ich es auch ohne Rechner machen würde:
1.) Ich lege eine Priorität fest, was zuerst immer zu verbrauchen ist, da es schnell verdirb: Das sind die Eier! Also Eier sollten keine übrig sein.
2.) Jetzt liegt fest, dass alle Kombinationen die 0 Eier ergeben zu testen sind, bei welcher die Restsumme aus Mehl und Zucker minimal wird. Kann mit einer Programmschleife erfolgen.

Kombinationen sind
Keks1 | Keks2 | Rest : Mehl+Zucker
---------+------------+---------------------------
5 | 0 | 7250
4 | 2 | 6600
3 | 4 | 5950
2 | 6 | 5300
1 | 8 | 4650
0 | 10 | 4000

Die möglichen Kombinationen, die 0 Eier ergeben kann über eine Schelife gefunden werden, indem alle möglichen Kombinationen geprüft in einer geschachtelten Schleife geprüft werden. Ist eine Kombination gefunden, so wird für die restlichen Zutaten: Mehl und Zucker die Restmenge errechnet und gespeichert. ergibt diese Kombination einen geringeren Restbestand als die Vorherige, wird die Kombination gespeichert. Das Wiederholt sich, bis alle Kombinationen durchlaufen sind. Dann ist das Optimum gefunden.
So sollte es gehen. Aber Progammierung in VBA ist dazu notwendig.

Answer 3

Ich möchte es noch ein wenig präziser formulieren. Die Verderblichkeit der Zutaten spielen keine Rolle, sonst wird es zu kompliziert. Die Kekse haben auch keine Gewichtung, denn meistens handelt es sich schon um Reste, die nur möglichst umfangreich verwertet werden sollen.

Mit anderen Worten: Priorität 1 ist eine möglichst geringe Menge als Rest zu haben. Das zahlenmäßige Missverhältnis zwischen Eiern und Mehl würde ich einfach umgehen, indem ist anstelle von 2 Eiern 200 Eier eintrage (1 Ei entspricht 100 Gramm Mehl) oder etwas in der Art.

Jedenfalls vielen Dank für die sehr hilfreichen Antworten. Vielleicht findet sich ja noch die eine oder andere Anregung. Bis Weihnachten ist ja noch Zeit. :-)

Answer 4

Hi Keksplaner,

unter dieser Adresse dieser Adresse kannst Du dir meine Datei herunter laden.

Ich habe meine Gedanken ein wenig schweifen lassen und versucht einige Angaben von Dir zu verarbeiten.

Auf der Tabelle 1 wird in der Zelle A4 der entsprechende Teig eingegeben. Die in der Tabelle 2 enthaltenen Daten werden eingefügt und entsprechend der Vorgaben umgerechnet.

Natürlich sind die einzelnen Angaben frei gewählt und stimmen mit tatsächlichen Rezepten nicht überein.

Das Makro öffnet automatisch die Eingabemaske, um ein neues Rezept einzugeben. Nach der Eingabe eines Rezeptes entweder Neu drücken für eine weitere Eingabe, oder Schließen, wenn kein weiteres Rezept eingegeben werden soll.

Für die angelegten konstanten Namen wird die Aktualisierungsroutine automatisch aktiviert. Jede Abfrage bitte mit JA beantworten.

Es soll zwar nicht sein, aber ausnahmsweise für die direkte Anfrage meine Email. fuhaga@arcor.de

Ich hoffe, ich konnte ein wenig weiter helfen.

Gruß
Joachim

Answer 5

probleme solcher art löst man z.b. durch lineare optimierung - gemeint ist also das teilgebiet der diskreten mathematik, welches im operation research u.a. eine tragende rolle spielt.

das o.g. problem ist nämlich ein extrem häufig bei z.b. der einsatzplanung von ressourcen in einem betrieb. operations research ist eine wissenschaft, die ganze klassen derartiger betriebswirtschaftlichen probleme adressiert.

die realisierung erfolgt i.a. mittels eines gleichungssystemes in einem solver.

und welch glück: excel bietet einen solchen, auch wenn niemand sonst ihn gern benuzt (es gibt allerlei spezielle, besser geeignete software). ;)

schau zunächst hier um mehr über lineare optimierung zu erfahren und dort (von mir auf die schnelle gefunden.. gibt sicher mehr) um eine einführung zum solver von excel zur realisierung eines LP zu lesen.

viel erfolg...

Answer 6

@ achim_13086: Vielen Dank für die Mühe. Ich habe mir die Datei geholt, konnte aber noch nicht feststellen, wie ich ermittle, welche Kombination an Teilmengen meine Restbestände minimiert. Werde die Datei aber noch weiter untersuchen. :-)

@ Jaja: Ebenfalls herzlichen Dank. Ich werde mal versuchen, mich wieder in die Materie einzulesen. Die Schule ist leider schon zu lange her, da war das alles noch ein wenig klarer. :-)

Answer 7

@ Jaja: Nach einigen Schwierigkeiten (die Denkweise von Excel zu verstehen) habe ich es nun geschafft. Falls jemand wissen will, wie ich es ungefähr gemacht habe:

Ich habe am Ende der Tabelle eine Spalte für die Menge (ganzzahliges Vielfaches der Teigmenge) eingefügt:

| Keksart | Mehl | Eier | Zucker | Menge |
| Keks 1 | 200 | 2 | 150 | x1 |
| Keks 2 | 300 | 1 | 200 | x2 |

Am Ende der Liste habe ich je Zutat eine Formel eingefügt, um den Rest der jeweiligen Zutat zu errechnen:

Bestand an Mehl - (x1*200 + x2*300 ......)

In einer weiteren Zelle habe ich einfach alle Reste zusammenaddiert und den Solver lasse ich das Minimum für diese Zelle suchen, wobei natürlich x1, x2 usw. die variablen Zellen sind.

Nochmals vielen Dank an Jaja und alle anderen, die mir geholfen haben.