Ernährungsplan in excel

Hidiho Liebe Community,

ich habe folgendes kleines Problem,
ich würde mir in excel gerne einen Ernährungsplan erstellen, bei dem ich bei den Einzelnen Nahrungsmitteln die Menge, KH, Fett und Eiweiß eingebe, da ich aber keine Lust habe jedesmal wenn ich die Menge ändere auch die Makronährstoffe zu ändern, wäre es voll toll wenn man es irgendwie hinkriegen könnte dass ich eine zweite Tabelle erstelle, in die ich komplett für die Lebensmittel die 100g angaben eintrage und dann diese Werte in mein "Tagebuch" übernommen werden würden

lange Rede kurzer Sinn

WIE GEHT DAS?
und kann mir da einer vielleicht ein Beispiel erstellen?

Versuche doch mal fddb.info - das ist genau das was du vor hast im online-verfahren. Dazu sind auch noch alle Lebensmittel in einer Datenbank aufgeführt mit den jeweiligen Angaben / ggf. selbst abänderbar.

Hm das ist etwas komplizierter als es sich vielleicht anhört. Redest du jetzt überwiegend von Fertiggerichten wo die Angaben auch schon drauf stehen oder von Rohwaren?

Also du könntest natürlich einfach eine Tabelle (Nährwerte) machen, wo du pro Zeile die Werte KH, Fett, Eiweiß etc für ein Lebensmittel bei 100g angibst (Beispiel: [Produkt] Müsli Schoko - [Gewicht] 100g - [Eiweiß] 11g - [Zucker] 13g - etc)

Dann in einer anderen Tabelle (Tagesplan) Das Lebensmittel auswählen und die verzehrte Menge angeben.
Nun mußt du in den nächsten Zellen die Nährwertdaten mit der Menge multiplizieren. Dafür mußt du allerdings erstmal die Daten aus der zuerst erstellten Tabelle auslesen. Hierfür bietet sich der SVerweis an.

So wirds gemacht:

[Tagesplan / Zelle, in der Eiweißgehalt für dein Müsli mit 200g angegeben werden soll] : =
SVERWEIS([Tagesplan / Zelle wo die Nahrung drinsteht; [Nährwerte/ Bereich aller Produkte incl Nährwerten] ;4 (entspricht der Spalte wo der Eiweißwert aufgelistet ist) ;FALSCH) * [Tagesplan / Gewichtsangabe] / 100

Und ja ich weiß, man kann die Werte eigentlich nicht einfach hochrechnen. Eine 200g Müsliportion hat nicht einfach doppelt so viel Eiweiß... Muß aber der Einfachhalt halber reichen...

@archiv: Aber das wär doch langweilig

@ archiv fddb kenn ich, problem dabei ist dass die Lebensmittel die dort eingetragen sind nicht exakt denen entsprechen die ich verwende, und ich erstelle den Plan nicht erst im nachhinein, sondern plane die Mahlzeiten schon im Vorraus, da ist ne excel tabelle, gerade wenn man änderungen durchführt, wesentlich schneller. Außerdem druck ich mir den Plan immer aus, da ich am Tag vorher die Mahlzeiten für den nächsten Tag vorbereite.

@ Elfenlied nein, es handelt sich nicht um vertiggerichte, sondern alles selbst erstellt, ich hau einfach mal rein wie der Plan für eine Woche aktuell aussieht.

Also, Tabelle 1 ist der Wochenplan, und in Tabelle 2 wollt ich dann die Lebensmittel mit den 100g (oder Einheit meiner wahl, bei Brot zum beispiel pro scheibe)

http://www.nbogo.de/futterplan.xls

Datei gibts nicht

Ich denke, das was Du willst, ist realtiv einfach zu verwirklichen; Du gibst beispielsweise für ein Lebensmittel die Werte für 100g an, beispielsweise so:


A1 A2 A 3 A4 A5 A6
100g | Brot | 80g Kohlehydrate | 3g Eiweiß | 1g Salz | 5g Fett

Sodann würdest Du die Bezüge einfach folgendermassen einstellen:

in Zelle A3 | = A1*A3 |
in Zelle A4 | = A1*A4 |
in Zelle A5 | = A1*A5 |
in Zelle A6 | = A1*A6 |


Wenn Du nun die Mengenangaben in Zelle A1 änderst, erhältst Du automatisch die neuen Werte in den Spalten A4 bis A6 !

War es das, was Du meintest ?

okay, datei ist jetzt vorhanden, hatte sie dummerweise groß geschrieben -.-

Jetzt dürfte sich erklären was ich meine

Ja, dann funktioniert es doch genauso wie ichs geschrieben habe

Ich machs mal an einem Beispiel deutlich:
Tabelle1 ->Montag ->Frühstück ->Belag (ich werd mich nur hierrauf beziehen)

Da hast du [B9] 100, [C9] 2; [D9] 2; [E9] 20;
Die Werte hast du vermutlich manuell eingetragen statt sie aus Tabelle2 automatisch auslesen zu lassen.

Ersetze den Inhalt der Tabelle1.Zelle [C9] durch:
=VLOOKUP($A9;Tabelle2!$A$1:$E$16;3;FALSE)
wenn du die deutsche Version hast:
=SVerweis($A9;Tabelle2!$A$1:$E$16;3;Falsch)

Jetzt sollte in der Zelle wieder der Wert "2" stehen, allerdings dieses mal aus der Tabelle2 ausgelesen...

Erklärung:
$A9 -> Der Suchbegriff der in der Tabelle2 nachgeschlagen werden soll
Tabelle2!$A$1:$E$16 -> Der gesamte Bereich, in dem nach dem Suchbegriff gesucht werden soll. Achte also drauf, dass du diesen Bereich möglichst groß wählst. Wenn "Belag" also zB in Tabelle2.Zelle A17 stehen würde, würde es nicht erkannt werden
3 -> Der Wert aus der 3ten Spalte wird zurückgegeben (Die 3te Spalte steht ja für KH in Tabelle2)
$ -> Das $ hält die den Wert "fest", wenn du die Formel in andere Zellen ziehst. $A9 in der nächsten Formel würde also zu $A9 - $A9 - $A9 ... werden statt B9 - C9 - D9 etc. $A$9 sollte dann klar sein...

Für "Fett" wäre die Formel dann
=VLOOKUP($A9;Tabelle2!$A$1:$E$16;4;FALSE)

Für "Eiweis"
=VLOOKUP($A9;Tabelle2!$A$1:$E$16;5;FALSE)

Erweiterung:
Um den tatsächlichen Nährgehalt zu bekommen mußt du es natürlich noch mit dem Gewicht verrechnen.
Für KH:
=VLOOKUP($A9;Tabelle2!$A$1:$E$16;3;FALSE) * B9 / VLOOKUP($A9;Tabelle2!$A$1:$E$16;2;FALSE)

Erster Block ist wie oben beschrieben. Zweiter Block (B9) ist das verzehrte Gewicht/Menge. Dritter Block sucht ähnlich wie der erste Block die "Standardmenge" heraus... Wenn du den Wert in B9 veränderst (zB 200Gramm) siehst du wie es funktioniert.

Kleine Ergänzung:
Du bildest Summen immer so: =SUM(C6+C10+C14+C18+C21+C26)
Besser wäre =SUM(C6;C10;C14;C18;C21;C26)
sonst kannst du dir die Summenfunktion auch schenken

Kleine Anmerkung:
KH, Fett und Eiweis sind leider nicht die einzigen relevanten Werte... Auch gibt es einen großen Unterschied zwischen gesättigte und ungesättigte Fettsäuren, auch zwischen Traubenzucker und raffinierter Zucker...

Okay, soweit hab ich das verstanden, aber theoretisch kann ich bei

Tabelle2!$A$1:$E$16
ja auch
Tabelle2!$A$1:$E$4000

nehmen, oder?

Und das mit der Kleinern Anmerkung weiß ich.Aber da ich die Lebensmittel ja handverlese, und somit die Faktoren mit den Fetten und Zucker arten dadurch schon ausschließe, brauch ich nur noch die Makronährstoffe ^^

SO hat alles geklappt, hatte das mit der Multiplikation der Menge aber ausversehen überlesen, bin aber dann von selbst drauf gekommen *ganz stolz bin*

VIELEN DANK nochmal ^^

hm.. ja.. aber laß das lieber^^

würde dann mit Indirekt arbeiten:
Auf Tabelle2 eine Zelle (A1) mit dem aktuellen Spaltenbuchstaben und eine (A2) mit der aktuellen Zeilenanzahl anlegen. Wenn in A1 jetzt "E" und in A2 "16" steht:

INDIRECT("A2:$"&$A$1&"$"&$A$2)
Gibt die Matrix aus: "A2:$E$16"

Achtung, das bedeutet in diesem Fall, dass die Tabelle mit den Nährstoffen auf Tabellenblatt2 erst in der 2ten Zeile los geht, nicht in der ersten wie bisher (weil in der ersten ja jetzt die Endkoordinaten der Matrix stehen)


Um die Suchmatrix nun zu vergrößern mußt du nur die Werte in (A1) und (A2) ändern...

Okay, auch dieser schritt hat wunderbar geklappt, jetzt fehlt nur noch das tüpfelchen auf dem I, und zwar, gibt es eine möglichkeit die Nahrungsmittel nach gruppen irgendwie zu ordnen und dann im Futterplan per drop-down menü auszuwählen, oder zumindest die Auto-Ergänzung auf die Begriffe in der Lebensmittelliste zugreifen zu lassen?

Na klar
Ich weiß allerdings nicht welche Version/Sprache du von Excel hast. Ich habe 2007, die Befehle könnten bei dir also wo anders liegen.

Nahrungsmittel nach Gruppen sortieren:
Erstelle bei den Nahrungsmitteln in Tabelle2 eine weitere Spalte hinter dem Namen. Achtung: Dadurch ändert sich natürlich auch der Bezug in der bisherigen Formel. Spalte "3" steht jetzt vielleicht nicht mehr für KH... Um das zu vermeiden kannst du das Spaltenargument (3tes Argument in der VLookup Funktion) natürlich auch mittels Indirect angeben, also aus einer Zelle auslesen lassen.

In die neu erstellte Spalte (zB Tageszeit) nun ein Kürzel eingeben für die Nahrungsmittel, zB F für Frühstück, M für Mittagessen, A für Abendessen, oder irgend so etwas. Dann die komplette Liste markieren und auf Sortieren drücken. Im Sortiermenu zuerst nach der neuen Spalte (Tageszeit) sortieren lassen, dann ein weiteres Level hinzufügen was nach dem Namen sortiert. Jetzt kommen zuerst alle Frühstückskomponenten nach Namen sortiert, dann die fürs Mittagessen, dann Abendessen...

Jetzt noch die einzelnen Gruppen markieren (also zB die Namen alle Frühstückskomponenten) und dem Bereich einen Namen ("Frühstück") zuweisen (macht man indem man links oben neben der Funktionszeile in das weiße Kästchen schreibt, da wo normal der die Zellenbezeichnung ("A1" etc) drin steht).

Dropbox:
In der gewünschten Zelle eine Dropbox erstellen (zu finden unter dem Reiter "Data"-> "Data Validation").

In den Dropdownmenue Funktionen unter "erlaubt" den Punkt "Liste" wählen.
Unter "Source" den Namen des oben erstellten Bereiches angeben, zB. "=Frühstück". Achte auf das "=" Zeichen. Schon fertig