Hallo zusammen,
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
Wie könnte ich denn Excel sowas berechnen lassen?
Hallo O...,
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.
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.
[
attachment=25123]
Was meintest Du mit Lösungsmethode EA? Diese Einstellung habe ich hier nicht:
[
attachment=25124]
[
attachment=25125]
Hi
@Ego seine Lösung scheint unter Excel 2007 nicht (ohne Änderungen im Solver) zu laufen. Unter Excel 2010 läuft sie.
im Anhang eine Solver Lösung erstellt mit Excel 2007.
[
attachment=25130]
Gruß Elex
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 :)
Hallo O...,
A. zu
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.
Hallo,
Falls Du Deine Aufgabenstellung variieren möchtest, gib bei Wolframalpha.com das Wort Nutrition ein und spiel mit dessen Funktionalität.
Viele Grüße,
Bernd P