[Anleitung] Wie erstelle ich einen Ernährungsplan mit Excel?

[crushak]

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^^)



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)





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)



Ihr erhaltetet daraufhin folgendes Bild:



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.

=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:

=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:

=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)



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:



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!

[Sebi[S.G.]]

super, das ist gerade für anfänger sehr wichtig. danke!


edit: allerdings gehst du hier nur von dem fall der diät/des deficits aus.

[crushak]

super, das ist gerade für anfänger sehr wichtig. danke!#+


edit: allerdings gehst du hier nur von dem fall der diät/des deficits aus.

Verstehe nich ganz wie du das meinst?
Wenn Beispielsweise im Erhaltungsumsatz 3000 steht und in der Tagesbilanz 3250, steht als Differenz 250...somit ist es doch sowohl für eine Diät, als auch für eine Massephase geeignet?

Aber du hast recht, der Begriff "Defizit" ist hier der falsche, ich werds auf Differenz abändern

[Sebi[S.G.]]

dann passts(steht immer noch n paar mal drin)

[crushak]

Habs hoffentlich jetzt überall angepasst, danke.

[Patta90]

Super Arbeit! Ich habe ihn mal sticky gemacht. Alle, die dankbar sind bitte "Dankebutton" anklicken.

[crushak]

Danke für's Sticky machen Patta

[Bounty14]

Kann mir da mal wer helfen? :/

Wenn ich auf Gültigkeit klicke wie auf den Bildern zu sehen ist, und dann das genau so mache, also "Liste" Ausgewählt und im Feld eingegeben "=Lebensmittel"....

Wenn ich dann auf das Dropdown-Feld klicke, kann ich nur auswählen =Lebensmittel, anstatt den ganzen Lebensmitteln -.-

Was mach ich falsch ? Plag mich mit dem schon ne halbe Stunde jetzt..



EDIT: Hat sich erledigt. Habe jetzt einfach in das Feld bei Gültigkeit unter "Liste" alle Lebensmittel reinkopiert, sodass man sie auswählen kann

[Kermit]

wow ...

ich habe mir bis jetzt alle Items mit den nährwerten in eine Registerkarte mit allen Infos eingetragen und immer mal die Menge genommen, aber das sieht ja sehr professionell aus.. Vielen lieben Dank

[Dobby]

Warum kann ich den das Excel-Sheet nicht downloaden?

[pong]

Warum kann ich den das Excel-Sheet nicht downloaden?

Diese Datei wurde vom User oder durch eine Abuse-Meldung gelöscht

Wie wäre es wenn du dich einfach per PM an den Ersteller wendest?

[davvman]

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.
Unbenannt.jpg
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

[maloross]

ich hätte auf jeden Fall Interesse daran, sieht ja schon sehr professionell aus...

[crushak]

Warum kann ich den das Excel-Sheet nicht downloaden?

Ich muss mal schauen ob ich die Datei noch habe, dann lad ich sie neu hoch. Gelöscht habe ich sie selbst jedenfalls nit, wenn dann muss es der Hoster gewesen sein /:

[crushak]

So, habe die Excel Datei wiedergefunden.

Leider kann ich sie über das Forum direkt nicht anhängen. Hab sie jetzt bei einem externen Hoster erneut hochgeladen.

http://www.file-upload.net/download-...splan.xls.html

Falls jemand die Datei nochmal direkt an den Thread hängen kann wäre ich dankbar, dann verschwindet sie auch nicht einfach wieder nach ein paar Tagen.

Und @Admins: Ich selber kann meine Beiträge nicht mehr editieren, ich wäre aber sehr dankbar dafür wenn ihr den Link im 1. Beitrag entsprechend aktualisieren würdet, insofern möglich.


Danke