ich versuche in Excel meine Ernährungsplanung zu optimieren. Es stehen die 3 Makronährstoffe (Protein, Fett und Kohlenhydrate) in einer fest vorgegebenen Menge zur Verfügung, die möglichst optimal ausgefüllt werden sollen.
Also sagen wir folgendes:
P: 180
F: 100
K: 290
Dann habe ich Lebensmittel, die jeweils eine gewisse Menge dieser 3 Makronährstoffe enthalten, beispielsweise folgende fiktive Werte
Lebensmittel P F K
A 5 0 3
B 1 8 5
C 0 1 9
...
T 0 1 9
...
Kann ich Excel irgendwie dazu bringen, mir nach Auswahl von ein paar dieser Lebensmittel (z.B. A, C und T) die optimale Mengen ausrechnen zu lassen, so dass die eingangs definierten Maximal-Werte (P: 180, F: 100 und K: 290) möglichst optimal erreicht werden?
Hier gibt es nicht unbedingt nur 1 eindeutige Lösung, was aber okay ist. Also z.B. habe ich dann ein mögliches Ergebnis diese Obergrenzen zu erreichen, z.B. x*A, y*C, z*T <= P, F, K
in der Anlage habe ich deine Fragestellung einmal für den Solver umgesetzt.
1) In der Spalte E wird über den Eintrag einer 1 die zu berücksichtigenden Lebensmittel ausgewählt (ansonsten 0),
2) in der Zeile 29 werden die gesuchten Mengen eingetragen und
b) über "Daten">"Analye">"Solver" wird der Solver mit der Lösungsmethode EA gestartet.
helmut
Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität. Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
02.07.2019, 07:34 (Dieser Beitrag wurde zuletzt bearbeitet: 02.07.2019, 18:47 von Zwergel.
Bearbeitungsgrund: unnützes Zitat entfernt
)
Hallo Helmut,
zunächst einmal vielen Dank für Deine Mühe und die Tabelle! Das schaut ja richtig professionell aus :)
Irgendwie ist mir die Anwendung dieses Solvers diesbezüglich noch nicht klar bzw. es kommen keine Werte raus.
Die definierten Nebenbedingungen im Feld sind mir klar und machen Sinn für mich.
Die Zielzelle und was darin abgelegt wird, ist mir leider noch nicht ganz klar.
Die veränderbaren Zellen habe ich so auf dem Bereich $F$2:$F$27 gelassen und ist so gesehen auch klar.
Wenn ich auf Lösen klicke, sagt er mir, dass er eine Lösung gefunden hätte, aber nicht welche bzw. ich hätte jetzt eine Übersicht erwartet, mit den möglichen Lösungen oder so, da es ja durchaus mehrere geben kann.
Was meintest Du mit Lösungsmethode EA? Diese Einstellung habe ich hier nicht:
02.07.2019, 18:38 (Dieser Beitrag wurde zuletzt bearbeitet: 02.07.2019, 18:47 von Zwergel.
Bearbeitungsgrund: unnützes Zitat entfernt
)
Hallo @Ego (Helmut) und Elex,
vielen Dank für Eure Hilfe bei diesem Thema!
Jetzt verstehe ich auch, wie dieser Solver gemeint ist und funktioniert.
Die Zielzelle muss also einen Wert enthalten, den es zu maximieren/minimieren gilt (z.B. könnte das die Summe der errechneten Gesamtwerte sein, die so groß wie möglich unter den vorhandenen Randbedingungen sein woll). Der Bereich "Veränderbare Zellen" ist praktisch der Bereich, der errechnet wird. Die Nebenbedingungen geben Randbedingungen vor. Hier könnte ich auch für jedes Nahrungsmittel/Artikel noch eine Mindestmenge vorgeben (z.B. 1g Reis macht keinen Sinn, es sollten schon mind. 50g oder so sein).
Die Auswahl von Helmut mit 0/1 finde ich auch ganz praktisch und wäre wohl noch eine Nebenbedingung.
Vielleicht definiere ich auch noch irgendwie einen Puffer von 10% Abweichung oder sowas :)
Zitat:Die Auswahl von Helmut mit 0/1 finde ich auch ganz praktisch und wäre wohl noch eine Nebenbedingung.
1. In meinem Beispiel sind die 0/1 Vorgaben (daher grün) und können/brauchen nicht in einer Nebenbedingung berücksichtgt werden.
2. Ich halte Elex Methode der Auswahl für sehr viel besser. Insbesonder wenn die Liste der Lebensmittel etwas länger ist. Dann muss der Solver nicht so viele unnütze Variablen testen.
B) zu
Zitat:Was meintest Du mit Lösungsmethode EA? Diese Einstellung habe ich hier nicht:
EA steht für evolutionärer Algorithmus. Ich weis nicht ab welcher Exelversion diese Lösungsmethode, die für solch ein Problem geeigneter ist, im Solver zur Verfügung steht.
C) zu
Zitat:Die Zielzelle und was darin abgelegt wird, ist mir leider noch nicht ganz klar.
Das ist die gängige Methode ("Methode der kleinste Quadrate") in der Mathematik, wenn mehrere Abstände gemeinsam minimiert werden sollen.
helmut
Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität. Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
