[Anleitung] Wie erstelle ich einen Ernährungsplan mit Excel?
Da das Thema für mich gerade sehr aktuell ist und evtl. auch andere ein Nutzen darin finden, dachte ich mir ich schreibe mal eine kleine Anleitung, wie man mit Excel relativ schnell einen Ernährungsplan erstellen und seinen Wünschen entsprechend anpassen kann.
Übersicht
-----------------------------------
1. Beispieldatei
2. Die Nährwerttabelle
3. Die Mahlzeiten
3.1 Suchbereich definieren
3.2 Dropdownfeld
3.3 SVERWEIS
4. EP Woche
4.1 Differenz berechnen
5. Schlusswort
-----------------------------------
1. Beispieldatei
Zu aller erst mal eine Beispieldatei, die ich mir erstellt habe und an welcher ich erklären werde, wie das ganze funktioniert:
http://www.muskelbody.info/downloads...plan_excel.xls
In der Excel Datei seht ihr 3 Tabellenblätter: Nährwerttabelle, Mahlzeiten und EP Woche (unten in der Leiste)
EP Woche: Hier können die verschiedenen Mahlzeiten pro Wochentag über ein Dropdownfeld ausgewählt werden. Nährwerte und Mengen werden übernommen und das Defizit zum Erhaltungsumsatz, Eiweiß, Kohlenhydrate u. Fett wird angezeigt.
2. Die Nährwerttabelle
In der Nährwerttabelle werden alle Lebensmittel aufgelistet, welche für das zusammenstellen der Mahlzeiten benötigt werden. Es macht Sinn, diese an seine verwendeten Produkte bzw. eigenen Bedürfnisse anzupassen.
Ich habe mich hier bewusst auf die Werte Kcal, KH, EW und Fett gewählt um es einfach zu halten. (reicht auch in den meisten Fällen aus)
3. Die Mahlzeiten
Hier können die in "Nährwerttabelle" aufgeführten Lebensmittel/Zutaten zu einer Mahlzeit zusammengeführt werden, die später dann wieder mehrfach in "EP Woche" ausgewählt werden können.
Doch jetzt wird es ein wenig komplizierter ;) (halb so wild^^)
http://s7.directupload.net/images/11...p/h6mzywnq.png
Wie im Bild zu sehen ist, können wir die in "Nährwerttabelle" aufgelisteten Lebensmittel über ein Dropdownfeld auswählen, aber wie habe ich das gemacht?
3.1 Suchbereich definieren
An sich ist es eigentlich ganz einfach. Zuerst muss ein Bereich definiert werden, welcher später in dem Dropdownfeld die Werte liefert.
Hierzu geht ihr über Einfügen -> Namen -> Definieren (siehe Bild) und wählt den gewünschten Bereich aus. Dieser ist in unserem Beispiel $A$2:$A$800 (Ab Spalte A, Zeile 2, bis Spalte A, Zeile 800)
http://s7.directupload.net/images/11...p/wk9yta9n.png
http://s1.directupload.net/images/11...p/exwf9wl6.png
Also sprich: Alle aufgelisteten Lebensmittelbezeichnungen, ohne die Überschrift (falls ihr mehr als 799 Lebensmittel auflisten wollt, was in den meisten Fällen wohl kaum zutreffen wird, dann müsst ihr das dementsprechend erweitern)
3.2 Dropdownfeld
Nun müssen wir noch dem Feld sagen, das es ein Dropdownfeld sein soll ;)
Und zwar wie folgt:
Ihr wählt das gewünschte Feld aus und geht auf Daten -> Gültigkeit (siehe Bild)
http://s7.directupload.net/images/11...p/22qg2hu8.png
Ihr erhaltetet daraufhin folgendes Bild:
http://s1.directupload.net/images/11...p/cowdz5e8.png
Da wir eine Liste ausgeben möchten, wählt ihr bei "Zulassen" Liste aus.
Nun wird eine Quelle benötigt, aus welcher die Werte ermittelt werden.
Wir erinnern uns, diese haben wir vorhin angelegt ;) Also tragt ihr die Bezeichnung ein, die ihr für den Wertebereich verwendet habt (in meinem Beispiel: =Lebensmittel)
Wenn ihr jetzt mit OK bestätigt, sollte sich in eurem ausgewählten Feld ein Dropdownfeld befinden, in dem ihr die Lebensmittel auswählen könnt. (Um dieses zu duplizieren, einfach über STRG + C (kopieren) und STRG + V (einfügen) an die gewünschte Stelle kopieren!)
Jetzt ist es uns schon möglich, die Lebensmittel über ein Dropdownfeld auszuwählen, ohne jedes mal die Bezeichnung abtippen zu müssen.
Aber wo kommen die Nährwerte her?
3.3 SVERWEIS
Diese werden über einen sogenannten SVERZWEIS ermittelt.
Zitat:
=SVERWEIS(Suchkriterium;Quelle;Spalte;exakter Wert)
Suchkriterium:
Als Suchkriterium, muss das Dropdownfeld angegeben werden, z.B. $A3
Quelle:
Die Quelle gibt an, womit die Daten abgeglichen werden sollen bzw. woher die Daten stammen, die am Ende zurückgeliefert werden.
Spalte:
Gibt an, aus welcher Spalte ein Wert ausgegeben soll. Steht Spalte auf 2, so würde uns in unserem Beispiel "Kcal" zurückgeliefert werden.
Exakter Wert:
Hier kann FALSCH oder RICHTIG angegeben werden. Wir benötigen hier FALSCH. Das bedeutet, das nur der exakt zutreffende Wert ausgegben wird.
Beispiel:
Als Suchkriterium wird "Apfe" angegeben. Somit wird der SVERWEIS keinen Treffer erzielen, da der exakte Begriff "Apfel" lautet.
So könnte dann der fertige SVERWEIS für unser Beispiel aussehen:
Zitat:
=SVERWEIS($A3;Nährwerttabelle!$A$2:$E$888;2;FALSCH )
Da wir in unserer "Nährwerttabelle" alle Angaben auf 100g angegeben haben und in "Mahlzeiten" die Möglichkeit besteht auch kleinere/größere Mengen anzugeben, müssen wir das Ergebnis * Gramm / 100 rechnen.
In unserem Beispiel für Zeile 3 dann:
Zitat:
=SVERWEIS($A3;Nährwerttabelle!$A$2:$E$888;2;FALSCH )*B3/100
4. EP Woche
Wir sind nun also soweit, das wir uns Mahlzeiten erstellen können.
Im letzten Schritt brauchen wir nurnoch die Möglichkeit, die verschiedenen Mahlzeiten einem Tag in unserem Ernährungsplan zuordnen zu können.
Hierzu benötigen wir als aller erstes wieder einen Suchbereich.
Dazu legt ihr im Tabellenblatt "Mahlzeiten" rechts von der eigentlichen Tabelle eine weitere an (siehe Bild)
http://s1.directupload.net/images/11...p/d8s3z8n3.png
Jetzt folgt der gleiche Schritt wie in 3.1 !
Nachdem dies erfolgt ist, müssen wir wieder wie in Schritt 3.2 eine Dropdownbox anlegen, dieses mal verweist dieser aber auf "Mahlzeiten", also der neue definierte Suchbereich.
4.1 Differenz berechnen
Als letztes berechnen wir noch die Kcal/EW/KH/Fett Diffrenz, die wir am Ende eines Tages haben.
Dazu benötigen wir eine Liste, welche unseren angestrebten Tageswert enthält, z.B. wie im folgenden Bild:
http://s7.directupload.net/images/11...p/loioqyyu.png
Nehmen wir das Beispiel im Bild. Wir wollen die Kcal Differenz vom Montag errechenen.
Dafür müssen wir in das Feld B6 folgendes schreiben: =B5-I2
Also lediglich: KcalMontag - Erhaltungsumsatz
Das gleiche kann für Eiweiß, Kohlenhydrate u. Fett wiederholt werden!
5. Schlusswort
So, das wars soweit! Falls Fragen bestehen sollten, bitte ich euch dies möglich über P.N. mit mir zu klären und nicht hier im Thread um etwas Übersichtlichkeit zu gewähren!
Rechtschreibfehler sind selbst verständlich so von mir gewollt. Die im Beispiel bereitgestellte Exceldatei kann gerne weiterverwendet, gelöscht, oder verbreitet werden, mir egal ;)
Ich hoffe ich konnte dem ein oder anderen ein wenig helfen!
Liste der Anhänge anzeigen (Anzahl: 1)
Ich habe gerade für eine Anabole Diät ein Spread Sheet erstellt. Es enthält:
Rechenblatt, auf dem man das prozentuale Verhältnis der Nährstoffe EW, KH, F einstellen kann. Desweiteren gibt man seinen gewünschten Kalorienbedarf an, den man vorher natürlich erst einmal ermitteln muss.
In allen anderen Blättern befinden sich Beispieltage, also eine Liste, in der Zutaten mit Mengenangaben stehen (z.B. Rinderhack 300g) und dann jeweils wieder EW, KH und F.
Der Blattname wird dann im Rechenblatt selbem übergeben und automatisch die Soll-/Ist-Nährstoffbilanz prozentual angegeben.
Je nach Diätform kann man das prozentuale Verhältnis von EW, KH und F verstellen.
Anhang 41655
Ich habe ihn zwar für eine AD gemacht, aber er lässt sich im Prinzip auch für jede andere Diätform verwenden.
- Man kann 2 verschiedene Verhältnisse EW/KH/F angeben und berechnen lassen
- Für bis zu zwei Leute gleichzeitig
Wer möchte, dem schick ich es gerne. Aber nicht weitergeben, nur für Eigengebrauch!
Gruß
PS: Bei Interesse bitte E-Mail per Pn
