Das Sternenhimmelstuermer-Prae-Adipositas-Moppelchen  will eine Diät im Selbstversuch durchführen. Grund genug sich eine interaktive Exceltabelle zu erstellen und ein kleines Brainstoarming über Ernährung zu publizieren.

Weitere Tipps zur Ernährung mit ballaststoffreichen Lebensmittel finden sie in dem Link: https://www.greenforce.com/blogs/ernaehrung/was-sind-ballaststoffreiche-lebensmittel

Zuerst folgt eine Beschreibung der Exceltabelle, die Sie zur Zeit mit wenigen Daten herunterladen können.  Lohnt sich der Aufwand eigene Daten einzutragen?

Lesen Sie nach der kurzen Leistungsbeschreibung und Kurzanleitung die Projektabhandlung, wie die Tabelle entstand und bilden Sie sich selbst ihr Meinung.

Den geistigen Überbau des Sternenhimmelstuermers finden Sie in dieserAbhandlung, ist hier aber kein Theama.

Zur Orientierung befindet sich eine Inhaltsangabe mit Sprungmarken vor der Projektabhandlung für Excelfans.  

Download der Exceltabelle (Zip) in Version 2007, ca. 500 KB

Leistungsspezifikation der Tabelle in Kurzform:

------
Bug in Alkoholberechnung am 23.08.2011 beglichen!

06.10.2011 Die Tabelle wurde dank dem US-Government (UDSA - Unitedt states agriculture service) nun um einige Daten ergänzt. Gleichzeitig die Funktionalität der Tabelle vereinfacht und verbessert. In den Einträgen der Lebensmittelprodukte wurden nun Listenfelder implentiert, so dass Sie nun eine Liste mit Dropdownmenü mit allen Daten (Lebensmittelproduktnamen) mit Scrollbalken haben. Suchen Sie einen Wert aus, so wird dieser Wert berechnet. Sie können die Daten aus der Datenbank der UDSA übrigens frei verwenden und in allen möglichen Versionen von Excel bis Access herunterladen. Die Verbreitung ist erwünscht und das englische Wikipedia zitiert fleißig aus dieser Datenbank.  Übrigens eine Anregung an das deutsche Wikipedia-Team: Da sind fast wirklich alle erdenklichen Werte von Lebensmitteln drin. Deshalb der Link zur Onlinesuche noch einmal seperat. Nebenbei mal die Frage an das Entwicklungsland Deutschland: Wo sind denn die veröffentlichten europäischen Werte von Lebensmitteln? Glaubt Ihr im Landwirtschaftsministerium allen ernstes, dass wir uns vielleicht mit Ampeln abspeisen lassen,  wo ein Nußallergiker dann bei grüner Ampel im wahrsten Sinne des Wortes überfahren wird? 


Kurzanleitung für den Laien


Die Tabellenseite Style!, Daten! und Rechnungen!

Style!: Hier ist die Eingabe- und Ausgabeseite. Leichte Berechnungen sind in den Zellen, aber die sind an dieser Stelle erstmal uninteressant. Die grauen Felder sind Eingabefelder, rote und grüne Schriften deuten auf ein Zwischenergebnis hin und Zellen mit grünem Hintergrund sind wichtige Ergebnisse.

Sie sollten sich nach den Gliederungspunkten durcharbeiten, da fehlende Werte des Gewichtes, der Größe und des Lebensalters zu Fehlern in den Auswerungen führen. Die meisten Berechnungen sind miteinander verknüpft, auch wenn es zunächst nicht so erscheint. Die Stärke der Tabelle liegt in der Interaktivität!

Auf der linken Seite des ersten Tabellenblattes style! ist unter Punkt 1 die Berechnung des Bodymaßindexes:

Punkt 1: Bodymaßindex

Quelle: Wikipedia
 
a) Eingabe der Größe in Meter, also z. B. 1,64 ohne Einheiten für 164 cm.
b) Eingabe des Gewichtes in Kg in blanken Zahlen ohne Einheiten
c) Eingabe des Lebensalters

Danach scrollen Sie nach unten auf der linken Seite und arbeiten sich je nach Bedarf durch die Punkte durch. Die Auswertungen stehen jeweils am Ende der Punkte, im Falle des Punktes 1 der Bodymaßindex. Darunter wird je nach Bodymaßindex in der Auswertung farblich unterlegt Untergewicht - Adipositas 3 automatisch ausgeworfen (Zelle: B9).

Darunter sind die Angaben des Bodymaßindexes bei amputierten Gliedmaßen. Berücksichtigt wurde, dass wenn ein Oberarm fehlt, Unterarm und Hand ebenfalls fehlen. In späteren Berechnungen werden die Werte von amputierten Gliedmaßen nicht berücksichtigt.

Darunter ist die Berechnung des Normalgewichtes und Idealgewichtes nach der guten alten Formel: Körpergröße - 100 und einem prozentualen Abschlag für Männer (10 %) und Frauen (15 %) für das Idealgewicht. Die Ausgabe der Berechnungen folgen für Mann und Frau getrennt.

Bodyziel: Dieser Wert ist ein wenig missverständlich, da ein wenig Eigenkreation. Durch die Berechnung mit der einfachen alten Formel können Sie schnell und ein wenig unpräzise ein Abnahmeziel festlegen, was mit dem Bodymaßindex ein wenig mühsamer ist. Zur Sicherheit wird von den beiden Werten von Mann und Frau darunter noch einmal der Bodymaßindex berechnet - Diesmal ausgehend vom Idealwert nach der alten Formel. Liegt der Ausgabewert bei Untergewicht (nach Zählart des Bodymaßindexes), so sollten Sie einen höheren Bodyinndexwert anstreben! Alles selbstverständlich nur nach Rücksprache mit Arzt oder professionellen Ernährungsberater!

Punkt 2: Blutdruck

Quelle der Daten und Berechnungen: Wikipedia 1 und 2 (Blutdrucktabelle mit systolischen und diastolischen Werten)

Die nachfolgenden Berechnungen funktionieren auch ohne Angabe des Blutdrucks. Der Sternenhimmelstuermer findet es aber wichtig, dass Sie - gerade bei einer Diät - den diastolischen und systolischen Wert des Blutdruckes ein wenig im Auge behalten. Die Darstellungsweise ist freilich ein wenig gewöhnungsbedürftig: Normalerweise wird zuerst der höhere (systolische Wert) und dann der niedrige Wert angegeben. Der Sternenhimmelstuermer macht das umgekehrt. Die darauf folgende automatische Auswertung erfasst die wichtigsten Anomalien wie Hypertonie - die Werte richten sich nach Wikipedia und nicht alle Möglichkeiten sind abgedeckt. Informieren Sie sich selbstständig über Blutdruck - dieses ist keine Ärzteseite!

Punkt 3: Gesamtumsatz

Quelle Wikipedia 3, 4 Berechnungen nach Formeln für Grundumsatz und 5 Leistungsumsatz und Pal-Werte

Der Gesamtumsatz des Kalorienverbrauchs an einem Tag eines Menschen setzt sich aus dem Grundumsatz und Leistungsumsatz zusammen - Formel:

Gesamtumsatz=Gundumsatz+Leistungsumsatz

Die Berechnung des Grundumsatzes ist leicht, aber es wurden angelehnt an Wikipedia (6) vier  unterschiedliche Berechnungen durchgeführt.

Sie mussten bereits ihr Lebensalter, Gewicht und Größe in das graue Feld eingeben. Gewicht, Lebensalter und Größe werden ihnen in grüner Schrift automatsch angezeigt, es sei denn, Sie haben Punkt 1 böswillig ignoriert. Dann zurück zu Punkt 1 und dort die Werte nachtragen. Ansonsten zerstören Sie bei unmittelbarer Eingabe Zellbezüge - das wäre doch nun wirklich Schade!

 Wie dem auch sei - danach werden Ihnen in den Unterpunkten a-d vier Werte nach folgenden Formeln ausgeworfen:

a)Harris Benedict-Formel
b) P Broca ivm Formel a
c) die einfache Abschätzung für die grobe Berechnung ohne Hilfsmittel (24  * ihr Gewicht - bei Frauen - 10 %)
d) Mifflin-St.Jeor-Formel

Für die weiteren Berechnungen bildet d) die Grundlage, da chronologisch die letzte Formel - mit stark abweichenden Werten von den Berechnungen mit den Formel a und b.

Für den Leistungsumsatz sind wieder Sie mit Eingaben von Aktivitäten in Stunden gefordert. Am Ende müssen Sie 24 Stunden (einen Tag) erreichen. ist aus Urheberrechtsgründen nur eine kleine Auswahl. Siehe eine vollständige Auswahl auf dieser Seite.

Einschub: Auf der Tabellenseite Daten! sind in den Spalten L und m in grauen Feldern die zugrundeliegenden Daten und die zugehörigen PAL-Werte zur Berechnung des Leistungsumsatzes eingetragen.

Sie können Aktivitäten löschen und neue in die gelöschten Felder eintragen. Viele User haben diese Werte zuhause in Büchern oder Sie suchen die Aktivitäten wie z. B. Bergsteigen oder Fitnesscenter auf Seiten im Internet. Der Sternenhimmelstuermer beschränkte sich auf auf einige Werte nach Wikipedia und der o. a. Seite, die in diversen Seiten im Internet veröffentlicht wurden. Die eingetragenen Werte werden dann automatisch in das Tabellenblatt Style! übernommen.

Excel ist eine Mehrdimensionale Anwendung, was heißt, dass vom Tabellenblatt Style! in mehreren Fällen auf die Tabellenblätter Daten! und Rechnungen! zurückgegriffen wird.

Am Ende wird der Gesamtumsatz aus dem Grundumsatz und Leistungsumsatz berechnet. Dieses für Mann und Frau nach Geschlecht getrennt aufgeführt.

Punkt 4: Auswertung der nebenstehenden Ernährungstabellle

Hier sind wieder ihre Eingaben gefordert. im Maximalfall können zehn Tage mit vier Mahlzeiten berechnet werden.

Exemplarisch wird die erste Mahlzeit des ersten Tages erklärt. Die darunterliegenden Mahlzeiten werden addiert und unter Punkt 4 ausgewertet.

Im Kopf des ersten Tages am oberen Rand der Tabelle style! geben Sie unter Beginndatum (styleI2!) den ersten Tag  der Ernährungsaufzeichnung an.

Dieser Wert und Berechnung des Wochentags erfolgen nun für alle Tage in der Tabelle automatisch! Ausgewertet werden hingegen die Tage nur, wenn die Werte der Mahlzeiten über Null liegen.

Ruhepuls, Systolischer und Diastolischer Wert sind fakultativ eintragbar, unterliegen an dieser Stelle aber keiner Berechnung. Nur eine Hilfe für Sie ein paar Notizen zu machen.

Danach füllen Sie die grauen Felder Nahrungsmittel laut Datenbank und Faktor aus. Der Faktor bewirkt, dass Sie die auf 100 g eingetragenen Grundwerte (ist so üblich) der Kalorien eines Produktes beliebig anpassen können.

Verwenden Sie also z. B. 25 g eines Produktes aus der Datenbank, so nehmen Sie den Faktor 0,25 ohne Einheit.
Denn 100*0,25 ist 25 g! Der Faktor ist mit Absicht erforderlich. Vergessen Sie ihn, so ist 100 * 0= 0, also eine Nullnummer.

Einschub

Bei der Eingabe eines Produktes wie Ei  muss das Produkt mit sämtlichen Werten von Kalorien über Kohlenhydrate, Ballaststoffe bis zu Eiweiß vorhanden sein. Dafür müssen Sie im Tabellenblatt !Daten in den grauen Feldern valide Werte eintragen. Diese Werte bekommen Sie von vielen Internetseiten wie z. B. dieser. Copy und Paste darf der Sternenhimmelstuermer nicht ausdrücklich erwähnen, aber es wäre natürlich rein theoretisch denkbar. Nachtrag: 56 valide Daten der USDA wurden wie bereits im Intro erwähnt eingefügt. Die können Sie ruhig drinnen lassen. Der Sternenhimmelstuermer wird auh so die Datnbank erweitern - der nächste Urlaub wird dann wieder lustig....

Danach müssen die Werte noch ein wenig bearbeitet werden. Bis zu 2400 Datensätze untereinander sind erlaubt. Diese werden über die Berechnungsseite (!Rechnung) verarbeitet und an die Style!-Tabelle weitergegeben. Die Getränke in den 400 ern sind Wiederholungen von der Getränkedatenbank. Das ist nicht ganz so glücklich, aber Getränke müssen nun in der Getränkedatenbank vorhanden sein, um im Listenfeld aufgeführt zu werden. Der Sternenhimmelstuermer beschränkte die Tabellenfelder gleichzeitig auf 470 Felder, damit die Dropdownlisten nicht unnötig aufgebläht werden. Das lässt sich aber schnell wieder revidieren. Schauen Sie im Kapitel Listenfelder nach.

Suchen Sie dann einen Wert aus dem Listenfeld aus der Datenbank wie z. B. Ei ein und die anderen Daten werden automatisch ergänzt und Berechnungen vorgenommen.

Vorsicht: Jeder Name in der Datenbank darf nur einmal enthalten sein. Ansonsten fällt ein zweites Ei im o. a. Beispiel einfach mal unter den Tisch! Die Technik dahinter heißt Sverweis... ei1 und ei2 wiederum ist in der Datnbank erlaubt.

Mit jedem Ausfüllen der Mahlzeitwerte berechnet Excel die komplette Tabelle mit Auswertungen neu und passt die Werte an. Keine Sorge, dass passiert in Bruchteilen von Sekunden ohne merkliche Zeitverzögerung!

Sie können von 1 Tag bis zehn Tage die Ernährungstabelle führen. Die Auswertung passt das automatisch an. Tage, an denen 0 Kalorien verbraucht worden sind, werden nicht erfasst.

 Die Faktorenzahl 1 kann immer bleiben, weil 1 * 0 immer Null ergibt!

Ende des Einschubs

Die Auswertung berücksichtigt also je nachdem, wieviel Tage Sie ausgefüllt haben, sämtliche in der Kalorientabelle eingetragenen Werte!

Wieviel Mahlzeiten Sie an einem Tag zu sich genommen haben, ist Schnurzpiepegal, da am Ende des Tages eine Zwischenbilanz gemacht wird.

Nur sollten Sie nicht den Kalorienverbrauch eines Tages über mehrere Tage verteilen, da bei späteren Berechnungen der Durchschnittswert der verbrauchten Kalorien in Punkt 4 errechnet wird. Die verbrauchten Kalorien der Tage werden unter Punkt 4 für jeden Tag angezeigt und durch die Anzahl der Tage geteilt, deren Werte über Null liegen.

Geschieht alles automatisch. Eine Eingabe Ihrerseits ist nicht erforderlich!

Wie gesagt wird mit grünem Hintergrund der Tagesdurchschnitt der Kalorienaufnahme angezeigt. Bei einem Tag also der singuläre Wert dieses Tages.

In den nächsten Zeilen wird der zuvor errechnete Gesamtumsatz für Sie nach Mann und Frau aufgeteilt separat angezeigt.

Nun wird ihr Gesamtumsatz mit dem errechneten Kaloriensatz des Verbrauches nach der Tabelle verrechnet. Ist der Gesamtumsatz niedriger als die Kalorienaufnahme, so wird in den nächsten Spalten vorgeschlagen, wie Sie die überflüssigen Kalorien durch Sport abbauen können.

Radfahren, Schwimmen und Joggen sind hier in langsamer oder schneller Variante die zur Auswahl vorgeschlagenen Alterativen.

Auf der Tabellenseite Daten können Sie wieder die Sportarten und PAL-Werte ändern und an ihren Lebensstil anpassen.

Jedenfalls werden unter Punkt 4 Vorschläge gemacht, wie Sie die überflüssigen Kalorien (Zahl über Gesamtumsatz) mit Sport abtrainieren können. Die Ausgabe erfolgt in Minuten Sport.

Bei über zwei Stunnden im Sport im Ernährungszeitplanraum wird die Meldung schwer bis gar nicht zu schaffen ausgeworfen.

Vorsicht: Gerade für untrainierte oder während einer Diät ist Sport mit Risiken verbunden. Befragen Sie im Zweifelsfall ihren Arzt oder Ernährungsberater!

Neben der Auswertungsspalte befindet sich eine visualisierte Ernährungspyramide, deren Werte von Ballaststoffen, Proteine, Kohlenhydrate und Fett sich aus der Ernährungstabelle speisen. Die Reihenfolge entspricht der Wirkung auf ihr Gewicht, nicht einer Ernährungspyramide im ernährungspolitischen Sinne für eine gesunde Ernährung.

Kleiner Tipp: machen Sie sich Kopien von den Tabellen, wenn Sie einen längeren Beobachtungszeitraum erhalten wollen oder lesen Sie die folgende Beschreibung und ergänzen Sie die Tabelle nach ihren eigenen Vorstellungen.

5. Flüssigkeitszunahme:


Zu jeder Mahlzeit in Style können  auch Flüssigkeiten wie Wasser, Cafe usw. eingetragen werden. Das System enspricht dem System der Nahrungsaufnahme. Die Datenbank mit Flüssigkeiten können Sie in der Tabelle Daten! in Spalte Daten!P ergänzen. Vergessen Sie nicht in der Nachbarspalte Daten!Q eine Null bei nichtalkoholischen Getränken oder den Volumengehalt bei alkoholischen Getränken (steht auf der Flasche, z. B. Bier 5,8 %) einzutragen.

Als Benutzer tragen Sie in die Styletabelle! bei den Mahlzeiten eine Flüssigkeit ein. Danach den Faktor in der Nebenspalte wie bei Nahrungsmitteln, um den Grammwert bzw. Milliliterwert zu ermitteln.

Zwischenbilanzen und eine Tagesendbilanz wird berechnet und neben den Spalten der Mahlzeiten ausgegeben. Der Sternenhimmelstuermer trug schon eine Auswahl alkoholischer und nichtalkoholischer Getränke ein. Die sind im Tabellenblatt Style! in jedem Listenfeld am Ende des Dropdownmenüs auffindbar.

6. Berechnung von Promillegehalt


Eine spezielle Form von Flüssigkeit ist Alkohol dieser wird nebenbei in Promille berechnet, aber erst in der Endauswertung unter dem Prüfungstag 3 in der Tabelle Style! berücksichtigt.

Erforderlich für die Promilleberechnung sind die Tagesangabe am ersten Tag der Tabelle (Zelle style!I2) im grauen Feld, Körpergewicht (style!b6)  und Körperlänge (style!b5) vom Bodymaßindex.

Die voraussichtlich letzten Ergänzungen durch den Sternenhimmelstuermer sind sämtliche relevanten Vitamine (13 von A - K, einschließlich der ganzen B1,B2....). Die Tabelle ist halb interaktiv, da bestimmt nicht jeder Benutzer darauf steht, eine weitere Datenbank von Daten!S2 bis Daten!AG auszufüllen. Deshalb wurde von Wikipedia eine bestehende Tabelle mit Werten von Hyper- und Hypovitaminose ergänzt und unter der Tabelle Style! erstellt. Natürlich können Sie bei Bedarf wieder die Gesamtmenge und den Durchschnitt berechnen lassen, obwohl angemerkt werden muss, dass Werte in Lebensmitteln stark varieren können und es so eine unpräzise Annährung ist.

Als kleines Abschiedsgeschenk noch: eine Jokerfunktion . Sie können die Tabelle leicht um verschiedene Spurenelemente durch wenige Einträge um verschiedene Einträge ergänzen. Dafür schuf der Sternenhimmelstuermer einen neuen Rechnungssatz für die Vitamintabelle mit Relativen und absoluten Bezügen, die durch einfaches Kopieren ein schnelles Kopieren und Schaffen neuer Spurenelemente ermöglicht.

Aus diesem Grund sind dann die Arbeiten an dieser Abhandlung und Tabelle als abgeschlossen zu betrachten - mit der zur zeit perfektesten Freewaretabelle im Bereich Ernährung.

Nach dieser eher langen Anleitung kommen nun die Excelfans auf ihre Kosten:


Projekt Ernährungstabelle für den Excelfan erklärt. 

Inhaltsangabe
Einleitung
Vorüberlegung
Punkt 1: Bodymaßindex
2. Bluthochdruck
3. Gesamtumsatz
4. Die Ernährungstabelle
5. Berechnung der Flüssigkeiten
6. Die Alkoholberechnung
7. Berechnung des Promillegehaltes
8. Berechnung und Ausgabe vom Alkohol bzw. Promille auf dem Tabellenblatt Style!
9. Vitamintabelle und Jokerfunktion zum Erweitern der Tabelle
10. Fazit
11. Listenfelder 06.09.2011 Nachtrag

Einleitung

Die Sternenhimmelstuermerhomepage verfolgt einen kybernetischen Ansatz (Denken in Systemen). Deshalb gibt es einer Kulturseite entsprechend überwiegend Abhandlungen. Sie können anhand der Analyse des Projektes an die hundert Fragen auf Tipseiten sparen, wenn Sie Anfänger sind. Dazu wird nochmals das Projekt chronlogisch erörtert - nur ein wenig präziser.

Bei Projekten gibt es unterschiedliche Schwerpunkte. Die Sternenhimmelstuermerhomepage bietet drei andere Projekte mit verschiedenen Schwerpunkten:

Erstellung eines Kalenders mit Excel
Erstellung einer numerologischen Berechnung für Tarot

Verexcelung eines Paragrafen aus dem BGB


Bei allen Projekten können Sie sich die zugehörigen und quelloffenen Exceltabellen herrunterladen. Der Sternenhimmelstuermer ist durch Suchmaschinen wie Google geschützt, die genau wissen, wer diese Tabellen zuerst erstellte.

Vorüberlegung

Los geht alles mit der Idee. Die kam bei einem Blick auf die Waage. Danach erstmal eine Recherche - vorzugsweise bei Wikipedia, die beste Quelle für Projekte im Freewarebereich.

Danach die Grundfragen für Excel:

Welche Werte brauche ich (Eingaben durch User)? Die Formel dafür ist immer dieselbe...
Wie stelle ich die Antworten dar? Dafür arbeiten Sie vorzugsweise auf der ersten Tabellenseite die Ausgaben ab. Im Falle dieses Projektes bietet sich die Aufteilung in vier Punkte an. Zuerst Berechnung von Bodymaß, danach ein kleiner Ausflug zum Blutdruck, Gesamtumsatz und am Ende die Auswertung der Ernährungstabelle.

Es bietet sich an, dass Projekt nach der Salamitaktik in kleinere Einheiten aufzuteilen, um den Überblick zu behalten.

Grundsätzlich sollten Sie auf dem ersten Blatt sämtliche Eingaben und Berechnungen aufschreiben. Danach Schneiden Sie die Berechnungen in seperaten Zellen aus und verbannen Sie durch Einfügen auf das nächste Blatt. Dadurch sparen Sie sich viel Arbeit, denn Excel passt sämtliche Bezüge - egal ob relativ oder absolut - an die neue Situation an. Deshalb wurden Punkt 1-3 mit sämtlichen Berechnungen auf der Tabellenseite style! durchgeführt und danach nach in das dritte Blatt Rechnungen! kopiert.

Es bietet sich an, in Excel Formeln mit Zellbezügen und nicht mit Zahlen durchzuführen, um dem User die Möglichkeit zu geben Formeln leicht anzupassen. So wurden in die Tabelle Daten!, Platz für Eingabewerte geschaffen, wie z. B. die Datenbank per se, die Palwerte von täglichen Aktivitäten und die PAL- Werte von Sportlichen Aktivitäten.

Am Ende wird die fertige Tabelle überprüft. Das ist wichtig. Gerade bei größeren Projekte gibt es viele Fehler. Der Sternenhimmelstuermer ist Praktiker und handelt oft aus der Intuition heraus. Daher hier ein harter Cut und nun die einzelnen Berechnungen der Tabelle - vieles wird dann verständlicher..

zurück zum Inhaltsverzeichnis



Punkt 1: Der Bodymaßindex


Diese Demotabelle entspricht nicht der anspruchsvollen Grafik der Tabelle des Sternenhimmelstuermers - ist nur eine vereinfachte Darstellung zur Erklärung!



   fx=WENN(ODER(B5=0;B6=0);"Bitte Wert eingeben";B6/(B5*B5))

 ABC
D
5
Körpergröße
1,74
m

6
Gewicht80 Kg
7Lebensalter

41
Jahre

8
Index26,42 =WENN(ODER(B5=0;B6=0);"Bitte Wert eingeben";B6/(B5*B5))

9AuswertungPräadipositas
=Rechnung!C11



Kurze Erklärung (am Anfang leicht und ausführlich mit zunehmenden Kenntnisstand schneller):


Grundsätzlich arbeitet Excel nach dem Prinzip, dass Sie Werte in Nachbarzellen in einer beliebigen Zelle berechnen. Für den Bodymaßindex brauchen Sie die Werte Gewicht (Zelle B5) und Körpergröße (B6). Die muss der User eingeben. Daraus folgt die Berechnung und Ausgabe - also Interaktivität. Größe und Gewicht sind sogenannte Variablen. Die sind bei den Menschen verschieden. Das Lebensalter spielt bei dieser Berechnung keine Rolle, ist aber für spätere Berechnungen erforderlich - also eingeben!

In B8 gibt es die erste Berechnung mit ein wenig Spielerei:

=WENN(ODER(B5=0;B6=0);"Bitte Wert eingeben";B6/(B5*B5))


Es wurden zwei Formeln mit einer Berechnung verbunden. Als Anfänger hätten Sie einfach die Berechnung B6/(B5*B5) geschrieben, was nichts weiter als Gewicht durch Körpergewicht zum Quadrat bedeutet. Die Variablen Gewicht und Körpergröße werden durch konstante Werte ersetzt.

Eingebettet wird das Ganze in eine Wennformel, um den User daran zu erinnern, dass er doch eine Eingabe macht, wenn keine Werte bei der Körpergröße oder dem Gewicht eingegeben werden.

= bedeutet, dass eine Berechnug oder Formel eingeleitet wird

Wenn Einleitung der WennFormel: Die Syntax der WennFormel: Wenn(Prüfwert; Dannwert ; Sonstwert). Formeln beitzen zwei Klammern (). Eine am Anfang der Formel ( und eine am Ende ). Das ist Pflicht. Danach geht es weiter mit der Salamitaktik.

1) Es wird als erstes der Prüfwert bis zum Semikolon geprüft. Nicht ohne Absicht ist die erste Formel ein wenig fies:

ODER(B5=0;B6=0)


Das ist der Prüfwert. Die erste Klammeröffnung und das nächste Semikolon sind Bestandteile der Wennformel, die für den Prüfwert nur insoweit interessant sind, als dass Sie Anfangs- und Endpunkt der Prüfung markieren.

Oder ist wieder eine Formel, die man natürlich wie Berechnungen in einen Prüfugspunkt einbeziehen kann. Die OderFormel erlaubt die Überprüfung zweier Bedingungen. Eingeleitet wird sie durch die obligatorische Klammeröffnung. Danach kommen die sogenannten Wahrheitswerte. Nach jedem Wahrheitswert kommt ein Semikolon. B5=0 ist also die Prüfung, ob in der zelle B5 eine 0, also kein Wert steht. Für B6 dasselbe. Danach muss die Klammer für die ODER-Formel geschlossen werden! Das Semikolon nach dem Prüfwert lässt uns zu unserer nächsten Salamischeibe, der Wenn-Formel kommen:

2) DannWert

Wir haben also zwei Bedingungen, aber was passiert nun, wenn die Bedingung eintritt? Der Dannwert benötigt in diesem Fall keine Berechnung, sondern ist eine einfache Textausgabe in unserer Zelle

 "Bitte Wert eingeben" 

Die Anführungszeichen markieren einen beliebigen Text, der angezeigt wird, wenn die Bedingung zutrifft. Das nennt man dann Interaktivität. Genausogut hätte hier eine Berechnung bestehen können und ein Zahlenwert ausgegeben werden können - keine Sorge, kommt noch...Dann wieder das obligatorische Semikolon...

3) Was passiert nun wenn der User vorbildlich in B5 und B6 seine Werte eingibt? Dann kommt eine Berechnung des Bodymaßindexes - oder besser gesagt der Sonstwert in der Wennformel:

B6/(B5*B5)

Die mathematischen Operanden sind / und *. Der Sternenhimmelstuermer setzt gerne Sinnabschnitte in Klammern. Das ist eigentlich nur bei der mathematischen Regel Punktrechnung geht vor Strichrechnung: 2+2*2=6 und eben nicht (2+2)*2= 8 sinnvoll, aber lieber einmal zu viel, als einmal zu wenig.

Am Ende muss die Klammer der Wennformel geschlossen werden. Das ist megawichtig - übrigens einer der wenigen Fehler, die manchmal von der Plausibilitätsprüfung von Excel gefunden werden.

Fazit: Die Wennformel ist der Pisateil von Excel. Wer die beherrscht, hat die halbe Miete. Deshalb wird die auf der Sternenhimmelstuermerhomepage immer wieder durchexerziert. Dazu brauchen sie eben kein Mathematiker zu sein - dieser Teil wird Ihnen von Excel abgenommen. Logik ist hier gefragt.

Wenn eine Bedingung zutrifft , dann passiert dieses, ansonsten etwas anderes. Gleichzeitig macht dieses eben Interaktivität aus. Welcher Wert dann oder sonst ist, kann verschieden sein. In diesem Fall war die wichtige Bodymaßberechnung im Sonstteil! Deshalb wurde diese Formel auch so angewendet, damit Sie die ein wenig lernen.

In Zelle B9 steht:

=Rechnung!C11

Das bedeutet, dass hier auf das Tabellenblatt Rechnung! zugegriffen wird. Excel ist eine mehrdimensionale Anwendung. Das Tabellenblatt 3 wurde umbenannt, in dem der Reiter unten links mit der rechten Maus angeklickt wurde und aus dem Eigenschaftsmenü der Punkt umbenennen ausgewählt wurde (linker Mausklick auf umbenennen). Danach wurde Rechnung eingetragen und mit der Eingabetaste bestätigt.

Im Rahmen eines Projektes ist es sinnvoll Blätter umzubenennen und Daten, Berechnungen und Eingaben zu trennen. Alle anderen Projekte schaffte der Sternenhimmelstuermer auf einem Tabellenblatt. Hier ist das so sinnvoller.

Sie können nachträglich Namen von Tabellen ändern. Excel gleicht Formeln automatisch an. Was sich hier so lapidar anhört, ist bei einer umfangreichen Tabelle eine Heidenarbeit, die Excel im Hintergrund durchführt.

Doch was heißt die Formel konkret:

= leitet wieder eine Formel oder Berechnung ein

Rechnung!c11, dass der Inhalt der Zelle c11 auf der Tabellenseite Rechnung angezeigt wird. Ist so eine Art Pfadangabe für die Zelle. Das Ausrufezeichen nach dem Taellenblattnamen ist für Excel das Signal, auf das Tabellenblatt Rechnung zuzugreifen - in diesem Fall auf die Zelle Rechnung!c11.

Es ist sinnvoll, erstmal Berechnungen nur auf einem Tabellenblatt durchzuführen und dann durch Ausschneiden und Einfügen auf ein anderes Tabellenblatt zu übertragen, weil Sie ansonsten

1. immer zwischen zwei Tabellenseiten springen müssen, um noch einmal die Bezugszellen herauszufinden
2. Immer, wenn Sie in Formeln auf das o. a. Berechnungsblatt zugreifen, müssen Sie die lange Pfadangabe (z. B. Rechnung!+Zellname) der Rechnung voransetzen. Das ist eine Megamehrarbeit behaftet mit vielen Schreibfehlern und von daher zu vermeiden.

Exkurs Kopieren und Ausschneiden in Excel

Ist ein wenig anders als Word, weil Formeln verwendet werden:

Beim Kopieren (Markieren und Kopierysymbol; eintragen in neue Felder) verändert Excel relative Zellbezüge. Absolute Zellbezüge bleiben erhalten. Absolute Bezüge (mit Dollarzeichen z. B. durch Taste F4 in einem Argument) bleiben erhalten. Ohne in dieser Abhandlung näher auf relative und absolute Zellbezüge einzugehen - als Anfänger sollten Sie die Finger von der Kopiertaste lassen.

Ausschneiden: Grob gesagt: Das, was in der Zelle steht, wird angepasst und 1:1 übernommen. Relative und absolute Bezüge sind egal! Alles wird angepasst und bleibt so erhalten, wie Sie es zuvor ausgeschnitten haben. Das funktioniert aber nicht bei allen Formeln - beim Sverweis passt sic z, B. der Spaltenindex nicht an....

Nicht verstanden? Im Laufe  dieser Abhandlung wird vieles klarer...

Die Berechnung der Zelle Rechnung!c11


Wir befinden uns also in der Zelle c11 in dem Tabellenblatt Rechnungen!. Hier steht das Ergebnis einer Tabellenauswertung von Bodymaßindexwerten, die mit dem Wert der Zelle A8 und B8 von der Tabelle style verglichen werden und als Ergebnis eine Einschätzung nach Untergewicht bis Adipositas Grad 3 in den Zellen der Reihe D pro Zelle ausgegeben wird. In c11 befindet sich eine Formel, die aus den zuvor berechneten Werten denjenigen sucht, der für die Berechnung des Bodymaßes zutrifft. Die anderen Reihen interessieren nicht, da Sie für eine spätere Berechnung gebraucht werden.

1. Schritt:

Anhand der Zellen a-d3 wird die Berechnung erläutert:

a3= 1; b3=16; c3=Formel zur Berechnung für diese Zeile; d3: starkes Untergewicht.

In a3 steht der unterste Wert des Bodymaßindexes für die Einstufung Untergewicht. In b3 der oberste Wert für die Einstufung Untergewicht. In d3 steht lediglich der zutreffende Text: starkes Untergewicht.

2. Schritt: Formel Rechnung!C3 für Vergleich, ob Untergewicht vorliegt

=WENN(UND(style!$B$8>=A3;style!$B$8<=B3);1;0)

Die WennFormel wurde bereits ausführlich erklärt, also hier in Kürze:

= Formel beginnt
wenn( Einleitung der Formel für die Prüfung
UND(style!$B$8>=A3;style!$B$8<=B3)  Prüfwert: Hier wurde eine UND-Formel integriert. Die wird wie bei dem o. a. Beispiel oder verwendet wird.
Sieht nur auf dem ersten Blick kompliziert aus.

Die Formel UND legt zwei Bedingungen fest: Ist der Wert vom Bodymaßindex (B8) aus der Styletabelle gleich bzw. zwischen 1 (A3) und 16, dann ist der Prüfungsfall eingetreten. style! ist der Pfad zum Tabellenblatt style. $B$8 ist der absolute Wert B8, also Bodymaßindex im Tabellenblatt style, >= wenn die Zahl gleich oder größer als der Zellenwert A3, also dem eingetragenen Zahlenwert 1 ist.

Dann wieder das obligatorische Semikolon zwischen zwei Argumenten.

Der zweite Bedingungswert der UND-Funktion beginnt nach dem Semikolon. Die Erklärung ist fast dieselbe wie die letzte Ausführung, mit der kleinen Änderung, dass diesmal der Wert kleiner gleich 16 ist.

Danach wird die Klammer der Und-Funktion geschlossen.

Nach dem Semikolon kommt der Dannwert. Der ist eine 1. Ist der Bodymaßwert also in dem Bereich für ntergewicht, dann wirft Excel in dieser Zelle eine 1 aus.

Nach dem Semikolon kommt eine 0. Also wirft Excel als Sonstwert eine Null aus. Dann wird die Kammer geschlossen. Wir haben zur Zeit also nun eine 0 oder 1 als Zwischenergebnis. Das ist eine Brücke, über die wir gehen müssen, um in jeder Zeile von neuem zu berechnen, ob die Werte des Bodymaßindexes in einem bereich liegen.

Die Formel wurde nur einmal erstellt und dann durch ziehen am rechten unteren Anfasser der markierten Zelle c3 nach unten gezogen.
Deshalb wurden einige Zellen mit Dollarzeichen absolut gesetzt. Es ist wichtig, dass B8 (in dem der Bodymaßindex steht) immer gleich bleibt.
Setzt man keine Dollarzeichen, dann würde Excel in der nächsten Zeile B4 den Wert B9 aus der style!Tabelle verwenden. Das ist hier aber nicht gewollt!

Umgekehrt ist es erwünscht (relativ), dass A3 und B3 in der nächsten Zeile automatisch zu A4 und B4 werden, um in dieser Reihe den Vergleich für das Normalgewicht durchzuführen.

Wie dem auch sei - Sie haben in der Reihe c nun jede mengen nullen und eine 1. Diesen Wert wollen Sie nun der Reihe D zuordnen.

3. Schritt: Mit einem SVerweis wird der zugehörige Textwert ausgegeben

 
Dazu wird ein ein SVerweis verwendet:
 
In Rechnungen!c11 befindet sich eine Formel, die den ausgerechneten Bodymaßindex in den Zellen der Reihen C und D  vergleicht (Der Sternenhimmelstürmer wird in Zukunft den langen Pfad auslassen, achten Sie also immer darauf auf welcher Seite wir uns befinden) und aus einer Tabelle die Einschätzung für Untergewicht, Normalgewicht und Übergewicht auswirft. Das ist ein sogenannter SVerweis:

=SVERWEIS(1;C3:D10;2;FALSCH)

Sverweis ist wie die Wennformel für eine Interaktivität eine Schwerpunktformel, die komischereise von vielen Usern nicht verstanden wird.

Also nochmal eine aufwendige Erklärung:

= Einleitung für eine Formel oder Berechnung

Sverweis(Suchkriterium; Matrix; Spaltenindex; Bereich Verweis)

1 ist das Suchkriterium, dass wir mit der Wennformel zuvor berechnet haben. Bei Zahlen oder Zellen brauchen Sie keine Anführungszeichen zu setzen.
C3:D10 ist die Suchmatrix, also die zwei Spalten, die Sie vergleichen. Links wird immer mit Rechts verglichen! Hingegen kann eine Matrix auch einen größeren Raum umfassen.

Excel such also in der linken Spalte C eine 1, um den nebenstehenden Wert auszuwerfen.

Spaltenindex ist die nebenstehende Spalte, also eine zwei (c-d).

falsch ist eine von zwei Alternativen und steht für eine Übereinstimmung zu 100 %. Der Alternativwert ist wahr, mit einer ungefähren Übereinstimmung. Andere Alternativen gibt es nicht.

Als Ausgabewert steht je nach Bodymaßindex Untergewicht, Normalgewicht.... Dieser wird in der Tabelle Style!B9 übernommen.

Erinnern Sie sich? In Style!B9 stand:  =Rechnung!C11

Ach ja, die Ausgabe dieser Zelle wurde mit Regeln versehen, um eine farbliche Ausgabe von grün bis rot zu erreichen.

Damit enden die ausführlichen Beschreibungen. Ab hier wird es schneller gehen, um Ihre und meine Nerven zu schonen..

Wir sind wieder in der Tabelle Style: Hier wurden als nächstes die Bodymaßindexes von Menschen mit fehlenden Gliedmaßen berechnet
Von a11 - a16 stehen die amputierten Gliedmaßen. Daneben der Korrekturwert nach Wikipedia. In der letzten Zeile der neu berchnete Wert des Bodymaßindexes.

Examplarisch eine Formel in Style!D12:

=($B$6/(1-B12)/($B$5*$B$5))

Eine einfache Formel: B6 und B5 sind mal wieder durch Dollarzeichen absolut gesetzt, damit die Formel wieder mit der Methode: Markieren, am Anfasser rechts unten der Zelle D12 runterziehen kopiert werden kann.
Beim Gewicht (B6) wird abweichend von der Formel die o. a. Berechnung durchgeführt. B12 ist relativ, damit die Werte sich beim Kopieren anpassen.
Leider muss die Formel dennoch ein wenig in den nächsten Zeilen angepasst werden, da bei fehlendem Oberarm automatisch auch der Unterarm und die Hand fehlt

=($B$6/(1-(B12+B13+B14))/($B$5*$B$5))

Ansonsten gibt es keine Komplikationen...Punkt vor Strichrechnung wurde Rechnung getragen...und es sollten nach Möglichkeit immer die Zellen statt einzelne Werte eingetragen werden. Es ist leichter und übersichtlicher B12 einzutragen, statt 0,008 (Zahl für fehlende Hand). Zudem ist bei sich verändernen Werten in Excel die Methode, dass sich die anderen Zellen automatisch anpassen - mit jeder Betätigung der Eingabetaste..

Zu guter letzt wurde der Bodymaßindex für Mann und Frau nah der veralteten Formel berechnet:

Darunter ist die Berechnung des Normalgewichtes und Idealgewichtes nach der guten alten Formel: Körpergröße - 100  Zelle B21 und B22=B5*100-100 für den Normalwert ( und einem prozentualen Abschlag für Männer (10 %   c21=B21*0,9 ) und Frauen (15 % c22=B22*0,85) für das Idealgewicht. Die Ausgabe der Berechnungen folgen für Mann und Frau getrennt.

Bodyziel (c21=WENN(ODER(B5=0;C22=0);"Bitte Wert eingeben";C22/(B5*B5)): Dieser Wert ist ein wenig missverständlich, da ein wenig Eigenkreation. Durch die Berechnung mit der einfachen alten Formel können Sie schnell und ein wenig unpräzise ein Abnahmeziel festlegen, was mit dem Bodymaßindex ein wenig mühsamer ist. Zur Sicherheit wird von den beiden Werten von Mann und Frau darunter noch einmal der Bodymaßindex berechnet - Diesmal ausgehend vom Idealwert. Liegt der Ausgabewert bei Untergewicht (nach Zählart des Bodymaßindexes), so sollten Sie einen höheren Bodyinndexwert anstreben! Alles selbstverständlich nur nach Rücksprache mit Arzt oder professionellen Ernährungsberater!

Diese Formel (c21) ist so ziemlich dieselbe wie bei dem Bodymaßindex.

In D16 =Rechnung!E11 Ist wieder in Verweis in das Tabellenblatt Rechnung. Hier kommt in E11 wieder das Ergebnis für die Suche des Bodymaßindexes vom Mann (E11=SVERWEIS(1;E2:G10;3;FALSCH) mit einem SVerweis. Dieser generiert sich wiederum aus der Tabellenrechnung analog zur normalen Bodymaßberechnung (exemplarisch: E3=WENN(UND(style!$D$21>=A3;style!$D$21<=B3);1;0) , die eins wird wieder gesucht und übernommen für den Sverweis.  Damit sind auch die letzten Spalten der Tabelle auf der Tabellenseite Rechnung geklärt. Die wurden für diese Rechnung gebraucht.

Zu schnell? Dann zurück zum Anfang dieses Kapitels. Der Sternenhimmelstuermer erklärt jeden Schritt innerhalb einer Abhandlung, aber eben nur einmal!



zurück zum Inhaltsverzeichnis


2. Bluthochdruck

Der Bluthochdruck ist eine komplette Wiederholung des Bodymaßindex. Nur das die Werte und Zellen sich ändern und nicht auf KG und Größe zugegriffen wird. In den Spalten von Rechnung!A14 bis d14 sind die Berechungen im Tabellenblatt Rechnungen!.

Das Schema ist dasselbe. Die neu eingegebenen Werte diastolischer und systolischer Wert werden abgeglichen und bei einer bestimmten Anomalie ein Krankheitsnamenwert ausgegeben. Der Umweg über die Errechnung einer 1 mit einer wennformel wurde wieder verwendet.

Da C15 - d20 ein wenig anders, mal c16 ein wenig genauer:

=WENN(UND(style!$C$28>=A15;style!$C$28<=A16;style!$C$29>=B15;style!$C$29<=B16);1;0)

Hier wurden wieder mit der Und-Funktion diesmal vier Wahrheitswerte als Prüfwerte verwendet. Es wird überprüft, ob der systolische Wert der Tabelle style größer oder gleich dem Wert aus Zelle A15 ist oder kleiner ist als der Wert in A16, gleichzeitig wurde der diastolische Wert derselben Prüfung unterzogen.

Die Formeln unterscheiden sich alle ein wenig. Es geht nur darum, dass eben beliebig viele Wahrheitswerte geprüft werden können. Schauen sie sich ruhig die verschiedenen Zellen an - ist gar nicht so unkompliziziert. Sonst wie gesagt, alles wie gehabt!

zurück zum Inhaltsverzeichnis


3. Gesamtumsatz


Der Gesamtumsatz besteht aus dem Grundumsatz + Leistungsumsatz
Ist die letzte unkomplizierte Berechnung und Vorstufe für die Auswertung der Ernährungstbelle.

Für den Grundumsatz benötigen Sie in der Tabelle Style noch die Eingabe für das Lebensmittel in Zelle C 51. Die beiden Werte Gewicht und Größe werden nochmal aus Punkt 1 automatisch wiederholt. Dazu wird in B52 =B6 für das Gewicht eingetragen.

Das Gleichheitszeichen leitet eine Formel, Funktion oder Berechnung ein. Hier nur die Wiederholung des Wertes B6, also die Eingabe des Users vom Gewicht. Das ist sinnvoll, um die Werte in den folgenden Zellen nochmal leicht im Blick zu haben. Außerdem ein wenig Interaktivität.

Danach werden die vier Formeln berechnet:

a) Die Harris Benedict Formel jeweils für Mann und Frau:

Zelle

c57

=665+(9,6*C52)+(1,8*C53)-(4,7*C51) für die Frau

c58

=66,5+ (13,7*C52)+(5*C53)-(6,8*C51) für den Mann

Die Formeln sprechen für sich. Nur noch drei Hinweise:

Ab jetzt wird zweigleisig nah Frau und Mann berechnet. Doppelte Arbeit. Man hätte theoretisch mit komplierten Wennformeln und der Zuatzeingabe w und m das verkürzen können, aber das wird für andere schwer nachvollziehbar. Für den User ist es vielleicht auch mal ganz interessant zu sehen, wie das andere Geschlecht berechnet wird.

Zur Berechnung wurden für Gewicht, Größe und Alter Zellbezüge c51, c52 und c53 verwendet. Das muss so sein. Die sind bei den meisten verschieden. Feste Werte würden unweigerlich zu falschen Ergebnissen führen. Die zellen sind also Variablen. Das ist klassisch für Excel.

Die Berechnung kann in je nach geschlecht einer Zelle durchgeführt werden. Das alles in der Tabelle style!. Die Berechnungen müssen nicht ausgelagert werden. Diesen Fall werden Sie oft in kleineren Projekten haben.

b) P. Broca ivm. Formel a

Ist dasselbe wie Punkt a. Nach Mann und Frau:

c62 für die Frau

=(2,4*C52)+(9*C53)-(4,7*C51)-65

c63 für den Mann

=(3,4*C52)+(15,3*C53)-(6,8*C51)-961

c) einfache Abschätzung.

Ist ganz Cool, da ohne Taschenrechner möglich. Fast so einfach wie die leichte Abschätzung des Normalgewichtes oder Idealgewichtes.

Für die Frau in B68

=(24*C52)*0,9

Die mal 0,9 entsprechen Minus 10 % des Ergebnisses von gewicht mal 24. Der Sternenhimmelstuermer rechnet bei Abzug von Prozentabgaben immer nach diesem Schema.

Bei Abzug von 10 % 100-10=90. Dann durch 100, also das Komma um zwei Stellen links, also 0,9. Das ist durch einfachen Dreisatz gedeckt.

Für den Mann in c68

=24*C52

Das ist doch mal einfach 24 * Gewicht ergibt die Kcal/24 h. Einheiten spielen erst beim Ergebnis eine Rolle - jedenfalls für den eher schreibfaulen Sternenhimmelstuermer...

Danach folgt nur so zum Spass die Umrechnung in Kcal pro eine Stunde. In KJ in 24 Stunden und die Umrechnung in Watt. Das ist nur so eine kleine Hilfe. Damit Sie selbständig Einheiten Umrechnen können. Schauen Sie einfach mal in den Zellen nach. Ist keine Hexerei.

d) Als letzte Formel die Mifflin-St.Jeor-Formel, die in den 90 ' ern entwickelt wurde, mehr Kalorien im Grundumsatz zulässt und für die weiteren Berechnungen verwendet wurde. Sie können in späteren Formeln den Bezug gerne zu einer anderen Formel ändern. An gegebener Stelle wird darauf hingewiesen.

a) für den Mann in Zelle c 76

=(10*C52)+(6,25*C53)+(5*C51)+5

für die Frau in Zelle c77

=(10*C52)+(6,25*C53)+(5*C51)-161

Sämtliche Formeln werden bei Wkipedia erklärt. Deshalb werden die eher stiefmütterlich behandelt.

Der Leistungsumsatz ist ein wenig komplizierter. Langsam verdichtet sich das Ganze und geht in Richtung Auswertung. Das bedeutet, dass die Vorergebnisse für die Auswertung in der Ernährungstabelle geschaffen werden.

In den Zellen Style!C84 bis c90 werden die Eingaben für die Stunden nach den Aktivitäten von Style!A84 bis A90 erwartet. Insgesamt müssen 24 Stunden herauskommen.  In der Spalte für die Palwerte kommt es zu relativen verweisen auf das Tabellenblatt Daten!, obwohl wird doch normalerweise auf das Tabellenblatt Rechnungen! verweisen.

Hintergrund: Die Aktivitäten und Palwerte sollen austauschbar werden, wenn User durch Eigenarbeit andere Palwerte für andere Aktivitäten herausfinden. Deshalb wurde in den Reihen /Spalten L und M eine Mastertabelle von der Tabelle in Style erstellt. Die Tabelle Style! ist nur eine Kopie mit verweisen der Zellen auf das Tabellenblatt Daten! Werden die Aktivitäten und Daten geändert, so wirkt sich das direkt auf Style! aus.

Exemplarisch ein Beispiel:

In dem Tabellenblatt style! steht in den Zellen A 84 =Daten!L3

und D84 =Daten!M3*C84

Das heißt, in den Zellen L3 und D 84 im Tabellenblatt Daten! stehen
die Originalwerte Nacht-/Mittagsschlaf bzw. 0,9 (Das ist der Wert für eine Stunde, der im Tabellenblatt style! nochmal mit dem Zeitwert C84 multipliziert wird und dessen Ergebnis in der Zelle D84 angezeigt wird).

Fassen wir zusammen: In dieser Lektion haben sie gelernt, wie Sie dem User die Möglichkeit geben können, Daten zu ändern. Der User muss dazu keine Formeln kennen. Ihn interessieren nur die grauen Kästchen im Tabellenblatt Daten!, die durch eine einfache Hintergrundfarbenänderung formatiert wurden.

Dieses Blatt wird gleichzeitig Grundlage für die Datenbank, die der User wegen Urheberrecht selbst erstellen muss.

In Zelle D 91 steht eine einfache Summenformel:

=SUMME(D84:D90)/24

Die Bedarf keiner weiteren Erklärung?

= Einleitung Formel
obbligatorisch Klammer auf nach Summe
D84:D90 sind die Palwerte pro Stunde, die addiert werden
Klammer zu ist obligatorisch bei jeder Formel

Hinweis: D84:D90 können Sie mit der Maustaste durch markieren des Zellbereiches in die Adressleiste "eintragen" - geht manhmal wie bei Matrixformeln schneller...

Das Ganze durch 24, um den PAL-Wert für 1 Stunde zu bekommen.

Nun wird der Grundumsatz für 24 Stunden mit dem zuvor errechneten Wert des Grundumsatzes multipliziert,

also =C76*D91 in c92 für den Mann.

in Worten: Grundumsatz des Mannes multipliziert mit Palwert für eine Stunde ergibt den Gesamtumsatz.

Die Aussage Gesamtumsatz= Leistungsumsatz + Grundumsatz ist für den Laien also eher unverständlich, da es richtig heißen müsste: Gesamtumsatz= Grundumsatz * Palwert vom Leistungsumsatz.

zurück zum Inhaltsverzeichnis

4. Ernährungstabelle

Jetzt kommt also der Abschnitt Ernährungstabelle. Bevor wir die Auswertung machen können, brauchen wir wieder Userangaben und eine Datenbank (ist auf der Tabellenseite daten!), mit der die Werte verglichen werden. Die Datenbank wurde mit Datensätzen der UDSA (56) bestückt die natürlich noch nicht abshließend sind und durch Ihre Nahtrage oder des Sternenhimmelstuermers im nächsten Urlaub ergänzt werden können. Viele diätwillige Menschen besitzen zudem Bücher mit Tabellen, die auch schon oft in digitalisierter Form vorliegen. Tip: Tragen Sie die Werte der jeweiligen Lebensmittel, die Sie gerade verwenden, in die Tabelle der Datenbank ein. Das hat den Vorteil, dass Sie bzw. Excel die Werte auch über einen Sverweis findet. Wennformel und Sverweis wurden unter Punkt 1 ausführlich erläutert.

Aus den Punkten 1-3 ergeben sich schon die Formeln, der Aufbau der Datenblätter und Berechnungsschritte. Sie sollten daher diese Abhandlung chronologisch lesen. Dann ist das, was nun folgt, echt easy zu verstehen.

In den Spalten F-L befinden sich die vier Mahlzeiten des ersten Tages. Die anderen Tage sind weitestgehend nur eine Kopie (hier wird einmal Kopieren statt Ausschneiden benutzt, da es weitgehend erwünscht ist, dass sich relative Formeln anpassen!).

In der Spalte I wurden im Kopf Angaben zum Blutdruck, Puls und Beginndatum vorgesehen.

Allein das Datum wird für die folgenden Köpfe der Mahlzeiten weiterverwendet: eine kleine Spielerei.

In Zelle I2 gibt der User das Datum in der Form TT.MM.JJ ein. In Zelle I7 wird am ersten Tag lediglich mit =I2 das Datum wiederholt. Im Kopf des zweiten Tages steht dann =I2+1. Es wird also automatisch ein Tag zum Datum hinzu gerechnet und das Ergebnis hinzugerechnet. Das zieht sich dann alle Tage durch.

In J7 steht der Wochentag von I7 (Formel:  =WOCHENTAG(I7;1)   ). Die Formel Wochentag zieht den Namen des Wochentages aus einem Datum. Nach dem Öffnen der Klammer kommen zwei Argumente getrennt durch ein Semikolon. I7 ist die Zelle, in dem das Datum steht. Die 1 steht für Beginn der Woche mit Montag.

Danach richten wir die Ernährungstabelle im Tabellenblatt !style ein. Die Überschriften dürften verständlich sein:

Nahrungsmittel laut Datenbank: Hier wird in den nächsten Zeilen eine Eingabe des Produktnamens erwartet. In der folgenden F-Spalte finden erstmal in den Eingabefeldern keine Berechnungen statt. Der Zugriff auf die folgenden Eingaben findet von dem Tabellenblatt Rechnungen! aus statt. Die Nachbarzellen  unter der Spaltenüberschrift werden später automatisch ausgefüllt. In diesen sind auch Formeln...

Faktor 100 gramm Zahl: Unterdieser Spaltenüberschrift steht ein Faktor, um ihre verwendete Grammzahl für alle folgen Überschriften zu berechnen. Ist also eine Musszelle. Eine 1 wurde schon mal eingetragen, aber für 50 g kann dann z. B. eine 0,5 eingetragen werden.  

Danach folgen Kilokalorien, Eiweiß, Kohlenhydrate, Ballaststoffe und Fett.

Das reicht für eine Übersicht.

Gleichzeitig sind die Spaltenüberschriften des ersten Tages Masterüberschriften, d. h., dass eine Änderung eines Wortes in sämtlichen Mahlzeiten aller Tage übernommen wird. Das wurde durch Gleichsetzen der Zelleninhalte erreicht.

Im Tabellenblatt Daten! wurde nur eine Tabelle mit weitgehend identischen Überschriften erstellt. Die Daten werden vom User eingetragen. Auch hier wurden keine Formel verwendet.

Im Tabellenblatt Rechnung! ist eine Kopie der Tabelle. In der Tabelle ist Platz für 2400 Eingaben. Das dürfte reichen. Die Zellinhalte der Daten werden aus den Eingaben des Users in Daten! generiert! Das ganze wurde aus Gründen der Übersichtlichkeit von Listenfeldern in den Eingaben von Style auf 700 Einträge wieder begrenzt - brauchen Sie die volle Anzahl der Felder, dann gehen Sie in das Kapitel Listenfelder!

Wozu diese Kopie? Schauen Sie sich mal die Überschriften an! Ist eben keine Originalkopie, sondern neben den Reihen J und M (Name der Nahrungsmittel) stehen jeweils zwei Spalten (Eiweiß, Kohlenhydrate und Ballaststoffe, Fett) mit den Anzeigewerten der Tabelle style!.

Dieses ist eine kleine Schummelei, um mit Hilfe eines Sverweises die Daten auswerten zu können. Anders ist das mit einem Sverweis nicht machbar, da dieser nur eine begrenzte Anzahl von Spalten miteinander vergleichen kann.

Die eigentlichen Berechnungen befinden sich auf dem Tabellenblatt style! und werden für jede Zelle individuell getroffen.

Exemplarisch die Zelle H12. Hier steht der Wert von Kalorien, wenn in f12 ein Nahrungsmittel befindet, dass in der Datenbank daten!, vorhanden ist. Dieses ist ein wenig inkonseequent, da hier auf die Datenbank in der Tabelle Daten! direkt zugegriffen wird, weil es möglich per Sverweis ist, was in der danbenliegenden Zelle nicht mehr möglich ist und deshalb auf das Tabellenblatt Rechnungen! zugegriffen werden muss!:

=WENN(ISTNV(SVERWEIS(F12;Daten!$A$2:$C$2399;3;FALSCH));"0";SVERWEIS(F12;Daten!$A$2:$C$2399;3;FALSCH))*G12

Sieht auf den ersten Blick verwirrend aus, ist aber easy! Jetzt kommt ein wenig Luxus. Normalerweise könnte der Sternenenhimmelstuermer auf Punkt 1 verweisen, wo Wenn- und Sverweis-Formel erklärt sind. Da viele User unberechtigt vor langen Formeln Angst haben, hier nochmal ausführlich:

= leitet wie immer eine Formel, Berechnung oder Zellverweis ein.
Wenn( ist der obligatorische Anfang einer Formel: Klammer auf und einmal ein kurze Innehalten.

Die zugehörige Abschlussklammer ) liegt vor *G12, also dem Faktor, mit dem die Formel multipliziert wird, wenn ein Nahrungsmittel eingetragen wurde.
Die ursprüngliche Wennformel ist in drei Bereiche (Argumente) eingeteilt, die durch zwei Semikolons getrennt werden: Die Bereiche heißen Prüfung, Dannwert und Sonstwert.

Am Anfang steht die Frage: Was will ich mit der Formel erreichen: Wenn in F 12 kein oder ein anderes Produkt steht, dann gebe als Wert eine Null aus, sonst gebe den Wert in der Datentabelle wieder, der in der dritten Spalte steht. Danach multipliziere das Ergebnis mit dem Faktor für die Grammzahl.

Hm, hier wird auf die Tabelle Daten! zugegriffen, in den anderen Zellen rechts neben der Formel auf  die Tabelle Rechnungen! Das ist inkonsequent, aber in diesem Fall eine schöne Demonstration, dass man mit einem Sverweis auch auf eine dritte Spalte zugreifen kann...

Zurück zur Formel:

Die erste Verschachtelung in der Prüfung ist ISTNV( , was nur heißt, dass hier das Nichtvorhandensein eines Wertes geprüft wird - Negativdenken ist also angesagt. Die Formel wird wieder mit einer Klammer geöffnet. Die Formel ISTNV kennt nur als Eingabe einen Wert. Der wird im nächsten Schritt individuell ausgerechnet und zwar mit der Formel für Sverweis:

SVERWEIS(F12;Daten!$A$2:$C$2399;3;FALSCH)

Das ist also alles der Inhalt von SVwerweis, der am Ende natürlich wieder mit einer Klammer geschlossen werden muss. Das ist wiederum der Wert von ISTNV.

ISTNV ist wiederum der Prüfungswert und somit der erste Bereich der Wennformel. Deshalb kommt danach ein Semikolon.

SVERWEIS setzt sich zusammen aus: Prüfkriterium, Matrixbereich, Spaltenindex und der Bereich des Verweises:

Obligatorisch erstmal nach Sverweis eine Klammer auf.

F12, also die Usereingabe im Tabellenblatt style! wird überprüft. Dann kommt das Semikolon und als nächstes Argument der Matrixbereich. Der liegt im Tabellenblatt Daten! Dieser Verweis wurde dem Matrixbereich vorangestellt, da es eben zwei unterschiedliche Tabellenseiten sind. Der Text vor dem Ausrufungzeichen ist der Tabellenname.

A2:C2399 ist der Matrixbereich im Tabellenblatt Daten!. Der wurde durch Drücken der Taste F4 mit Dollarzeichen absolut gesetzt, um die Formel in die nächsten Zeilen durch Ziehen mit dem kleinen Anfasser unten Rehts im Tabellenblatt Style! kopieren zu können.

Dieses ist der einzige Grund, da Excel den Matrixbereich automatisch verschieben würde. Dagegen ist die Zelle F12, also die Userangabe des Nahrungsmittels, relativ. Dementsprechend wird beim Kopieren aus F12 Zelle G12, was erwünscht ist und zu 100% dem Denkschema von Excel entspricht.

 ; Das Semikolon leitet das nächste Argument, also den sogenannten Spaltenindex ein. Von Zellen A nach Zellen C sind drei Spalten - Die dritte Spalte ist C. Also wird mit der dritten Spalte von Daten!C2, C3....C2399 verglichen.

Nach dem letzten Semikolon kommt das ein wenig missglückt benannte Argument: Bereich des Verweises. Hier gibt es nur zwei Möglichkeiten WAHR oder FALSCH. Wahr ist ungefähre und Falsch der genaue Wert. Das entspricht ein wenig einer verquerten Logik! Danach wird der Sverweis mit einer Klammer geschlossen.

Wir sind jetzt an diesem Punkt:

=WENN(ISTNV(SVERWEIS(F12;Daten!$A$2:$C$2399;3;FALSCH));

Jetzt der Rest der Formel ein wenig schneller:

"0";SVERWEIS(F12;Daten!$A$2:$C$2399;3;FALSCH))*G12

Danach kommt die also die "0", zur Abwechslung mal als Text formatiert durch Anführungszeichen. Dieses ist dann der sogenannte Dannwert. Ist also das Produt (Nahrungsmittel) in der Matrix nicht vorhanden, so wird eine Null ausgegeben.

Nach dem Semikolon kommt dann der Sonstwert. Das ist wieder derselbe Sverweis wie in der Prüfung, muss hier also nicht nochmals ausgeführt werden. Der Sverweis als Formel wird per se durch eine Klammer geschlossen. Die letzte Klammer danach ist zum Schließen der Wennformel. Am Ende wird der ganze Wust mit G12, also dem Faktor multipliziert.

Wenn Sie die Ausführungen halbwegs begriffen haben, so können Sie nun sämtliche Eingabefelder lesen. Die Formeln sind immer wieder dieselben, mit der Variante, dass bei anderen Spalten rechts neben der Spalte H auf as Tabellenblatt Rechnungen zugegriffen wird. Die Erstellung der Tabelle auf diesem Blatt ist wie gesagt ein kleiner kunstgriff, da Excel nicht darauf ausgerichtet ist komplexe Tabellen auszuwerten. Sverweis ist eher eine Krücke. Wer dieses weis, kann im Vorfeld die Tabelle artgerecht aufsplitten. Da der Sternenhimmelstuermer immer Quellcodeoffen arbeitet für Sie als Anregung für ihr Produkt wichtigt.

In Zeile !style35 befindet sich die erste Zwischenauswertung der ersten Mahlzeit. Hier werden durch eine einfache Summenformel die Kalorienwerte, Eiweiß, Kohlenhydrate und Ballaststoffe jeweils in Garmm addiert.

Danach wurde das Ganze dreimal kopiert, so dass vier Mahlzeiten entstehen. Sind es mehr Malzeiten, so kann man die Werte einer beliebigen Mahlzeit zuordnen.

Danach wurden nebeneinander Kopien erstellt, so dass zehn Tage a 4 Mahlzeiten entstehen.

Unter der ersten Tabelle wurde nach der Zwischenbilanz der letzten Mahlzeit die Auswertung begonnen:

In Form einer Berechnung und einer grafischen Darstellung als Pyramide.

In der Zellenreihe stylel !21 wurden die Grammwerte eines Tages mit einer weiteren Summenformel addiert.

Darunter ist eine Auswertung in Pyramidenform. Sind lediglich die zusammengezählten Grammwerte aller Tage.

zurück zum Inhaltsverzeichnis

4. Auswertung

Die unmittelbare Auswertung der Ernährungstabelle wurde am Ende des letzten Kapitels besprochen. In unserer Auswertung auf der linken unteren Seite werden jetzt die Werte verbunden und ein paar Empfehlungen gegeben, wie man überflüssige Kalorien durch Sport abbaut.

Das sind die Komfortfunktionen, die diese Tabelle aufwerten und den Arbeitsaufwand der Datenbankerstellung durch den User rechtfertigen. Hier  ist ein wenig Kreativität gefragt, da nicht nur bloße Formeln, sondern eine sinnhafte Darstellung gefordert ist:

Dazu wurde in den Spalten style!A bis D ab Zeile 104 eine Auswertungstabelle angefertigt. In Spalte A werden die Tage 1-10 aufgeführt. Spalte B beinhaltet eine einfache Wiederholung der im vorherigen Punkt erläuterten verbrauchten Tageskalorien eines Tages, exemplarisch Zelle B105: =H112.

In Zelle D105  eine kleine logische Zählung. Ist an einem Tag keine Kalorien gezählt worden, so ist das Ergbnis 0 Kalorien. In so einem Fall wird der Tag nicht gezählt. Ansonsten wird der Tag gezählt.

Wozu? Es soll für die weiteren Berechnungen  der Tagesdurchschnitt der verbrauchten Kalorien ermittelt werden, damit der Wert mit dem Gesamtumsatz eines Tages verglichen werden kann.

In D105 mal z. b. die Formel: =WENN(B105>0;1;0)

Die Wennformel wurde in Punkt 1 und 4 bereits erläutert, daher hier nur kurz:

Prüfungswert: Ist der Wert in Zelle 105 größer als 0,
Dannwert: 1
Sonstwert: bleibt die Null.

In Zelle B115 werden einfach die Tage in Spalte B zusammengezählt, eine einfache Summenformel, die nach dem Vortraining nicht weiter erläutert wird: =SUMME(B105:B114).

Das entspricht also den zu sich genommenen Kalorien laut Ernährungstabelle aller gezählten Tage. Als Ergebnis mit grüner Farbe als Hintergrund. Darunter in Zelle B116 in grüner Schrift die ausgezählten Tage, die zuvor erfasst wurden mit der Summenformel: =SUMME(D105:D114)

Sie sehen, solche leichten Auswertungen kann man im Tabellenblatt Style! durchführen. eine Auslagerung in das Tabellenblatt Rechnungen ist unnötig.

In B119 teilen wir nun die aufgenommenen Kalorien durch Tage =B115/B116.

Danach wurden in den Zellen B124 und B125 die zuvor berechneten Gesamtumsätze für Mann und Frau nach der Mifflin-St.Jeor-Formel zur Erinnerung wiederholt und nebenstehend der Gesamtumsatz minus dem Tagesdurchschnitt genommen. Es wird immer mit den variablen (Zellnamen) gerechnet. Das ist die Stärke von Excel.

Diese Berechnung leitet nun die nächste Formel ein. Diese wird für den Gesamtumsatz von Mann und Frau getrennt berechnet, da unterchiedlicher Grundumsatz. Also für den Mann in c124 =B124-B119. Dieselbe Rechnung für die Frau eine Zelle darunter.

Ergibt sich ein negativer Wert bei der letzten Subtraktion, so wurden laut Ernährungstabelle im Tagesdurchschnitt mehr Kalorien aufgenommen, als laut Gesamtumsatz verbraucht. Diese überflüssigen Kalorien kann man bekanntlich durch Sport abbauen. Die deutsche Gesellschaft für Enährung empfiehlt zweimal Sport wöchentlich.

Damit wir herauskriegen, mit welchem Sport welche Zeit benötigt wird, brauchen wir mal wieder die Werte für den erhöhten Leistungsumsatz durch eine Sportart.   

Die Erklärung ist für den Sternenhimmelstuermer simpel, da die folgende Berechnung im Prinzip nur die Wiederholung der Berechnung des Leistungsumsatzes (also Punkt drei diese Abhandlung ist).

Daher nur im Schnelldurchlauf.

Es wurde wieder eine tabellarische Darstellungsform gewählt. In der ersten Spalte A stehen die Sportarten - eine Kopie der Mastertabelle auf dem Tabellenblatt Daten! Das hat den Vorteil, dass ein User Palwerte der sportlichen Aktivitäten individuell nach seiner Sportart individuell anpassen kann. Er muss lediglich diese Werte im Internet ermitteln. Daher steht exemplarisch in Zelle A133 =Daten!L12, was in der Zelle des Tabellenblattes Style! als langsames Joggen ausgeworfen wird.
 
Daneben steht in B135 das Ergebnis, wieviel Minuten ein Mann am Tag machen müßte, um die überflüssigen Kalorien abzutrainieren oder als Formel:

 =WENN($C$124>=0;"Gesamtumsatz OK oder besser!";$C$124/Rechnung!Q2) 


Prüfungswert: Wenn die überflüssigen Kalorien (die ja bekanntlich eine negative Zahl wären) kleiner als Null (Gesamtumsatz - Tagesdurchschnitt der zu sich genommenen Kalorien der Ernährungstabelle).

Dannwert: Auswerfen des Textes: Gesamtumsatz OK oder besser,

Sonstwert: den Tagesdurchschnitt der Kalorien durch die Rechnung im Tabellenblatt Rechnung! in Zelle Q2.

Wozu dieses?

Grobe Richtlinie: Wir Rechnen in der Zelle Q des Tabellenblattes Rechnung! aus, wieviel Kalorien wir in einer Minute beim langsamen Joggen verbrauchen. Die überflüssigen Kalorien werden also geteilt durch den Kalorienverbrauch für eine Minute. Das Ergebnis sind also die Minuten, die gebraucht werden, um die überflüssigen Kalorien abzutrainieren.

Schauen wir uns also die Berechnung auf dem Tabellenblatt Rechnungen! in der Zelle !RechnungQ2 an:

=Daten!M12*style!$B$6/60*-1

In Zelle m12 des Tabellenblattes Daten! steht der Palwert für die sportliche Aktivität Joggen: 10,75 für einen Mann. Dieser wird multipliziert mit der Zelle B6 des Tabellenblattes Style!, also dem Gewicht in Kilo, um einen Bezug auf das Körpergewicht herzustellen.

Das Ganze wird durch 60 geteilt, um den Wert auf eine Minute herrunterzurechnen.

Am Ende wird das Ergebnis mal -1 genommen. Das ist nur ein kleiner Kunstgriff, da wir aus der ersten Rechnung in Style ja noch eine negative Zahl in den überflüssigen Kalorien laut Ernährungstabelle haben. Und -/- ergibt später +

Nur in diesem Fall findet laut Wennformel dann schließlich auch die Berechnung statt, weil sonst "Gesamtumsatz OK..." in Zelle B135 des Tabellenblattes Style! steht.

Zurück zum Tabellenblatt Style!

Wir haben nun die Minutenzahl zum Abtrainieren überflüssiger Kalorien für den Minuten berechnet. Dasselbe wird darunter für eine Frau mit langsamen Joggen berechet. Danach die Sportarten Schwimmen und Radfahren in langsamer oder schneller Form. Die Zellen wurden dabei wieder durch den Anfasser rechts unten kopiert. Daher wurden wieder einige Zellen mit Dollarzeichen absolut gesetzt.

In Zelle style!E133 steht wieder exemplarisch für den langsam joggenden Mann die Formel:

=WENNFEHLER(WENN(B133>0;$G$132*B133;0);0)

Hier wird also nur eine Berechnung durchgeführt, wenn in B133 ein Zahlenwert über null, aber eben nicht der Text "Gesamtumsatz OK..." steht.

Dazu wurde die Formel wenn in die Formel wennfehler verschachtelt. Die Formel Wennfehler kennt nur zwei Argumente: Wert; Wert-Falls-Fehler.

Wenn also der Ergebniswert der Wennformel einen Fehler ergibt, dann setze die Zelle auf die Zahl Null.

Die Wennformel kurz erläutert:

Prüfwert: Wenn die Anzahl der Minuten des Sportes über Null (erinnern Sie sich - durch das geteilt  -1 arbeiten wir im positiven Bereich).

Dannwert: Dann nehmen wir das Ergebnis * Zelle G132, was in der Tabelle = B132 ist (B132 war der Wert der Anzahl der Tage, in denen die Kalorienaufnahme laut der Ernährungstabelle festgehalten wurde.

Wir haben also berechnet, wieviel langsames Jogging ein Mann in den gesamten Tagen der Nahrungsaufnahme machen müßte. Die Rechnung zuvor bezog sich ja nur auf einen Tag!

Sonstwert: Ansonsten - wenn kein Sport nötig - eine Null

In der Zelle F 133 noch eine kleine Einschätzung, ob eine Gewichtsabnahme durch Sport erreichbar wäre:

=WENN(E133<=120;"durch Sport erreichbar";"Schwer bis gar nicht durch Sport")

Eine kleine Spielerei: Mehr als zwei Stunden Sport (für viele eine Stunde) ist für viele Menschen "Schwer bis gar nicht durch Sport" möglich.

Unter 2 Stunden, also 120 Minuten ist das sehr wohl möglich. Ist also die Minutenzahl kleiner als 120 Minuten, dann ist das "durch Sport erreichbar".

Ist natürlich ein äußerst subjektiver Eindruck, aber vielleicht eine Hilfe. Die Wennformel muss nun überhaupt nicht mehr erklärt werden.

Zum Ende hin wird eine Abhandlung eben ein wenig schwerer verständlich, wenn der User nicht chronlogisch bzw. die Kapitel nach Nummern durchgeht.

zurück zum Inhaltsverzeichnis


5. Berechnung der Flüssigkeiten

Beispiel:

In Zelle style!F34 steht in blau Flüssigkeit. Dieses ist eine Masterzelle. Das Wort Flüssigkeit wird in den folgenden Zwischenbilanzzellen =f34 gesetzt. Ändern Sie den Text, so wirkt sich die Änderung auf alle anderen Zwischenbilanzen aus.

in Zelle F35 wird der Wert in Gramm bzw. ml ausgegeben (1g=1ml) . Dieser wird mit der Formel in Zelle F35

=SUMME(Rechnung!V3:V24)

berechnet. Rechnungen sind auf dem Tabellenblatt Rechnung! Sie erinnern sich?

Von Zelle Rechnnung!T1 - RECHNUNG1AE1 ist die Berechnung der Flüssigkeiten und Promillewerte.

Gehen wir die Spalten mal kurz durch:

Spalte T:  Hier sind nur die puren Verweise auf das Tabellenblatt Daten!

Der User kann in der Spalte Daten!P beliebige Getränke eingeben, ohne irgendeine Berechnung mitzubekommen. Synchronisiert wird das durch den Verweis von der Spalte Rechnung!T, also z. B.

Formel:  Zelle Rechnung!T1:

=Daten!P1

Wir sind jetzt wieder auf dem Tabellenblatt Rechnung!

In Spalte Rechnung!U wurde einfach jeder Wert mit dem Bezugswert 100 versehen (100ml). Das ist eine Verlegenheitslösung, damit wir später mit einem Sverweis einen zweiten Wert haben. Wozu? Das werden Sie bald sehen!

In Spalte Rechnung!V befindet sich unser Flüssigeitswert. Mal einmal die Formel Rechnung!V3 zum staunen:

=WENN(ISTNV(SVERWEIS(W3;$T$2:$U$2399;2;FALSCH));"0";SVERWEIS(W3;$T$2:$U2399;2;FALSCH))*style!G12

Die Formel sieht erstmal schrecklich aus - vielleicht hier mal als Text:

Wenn der vom User eingetragene Wert auf dem Blatt style! (dessen Wiederholung auf dem Rechnungsblatt in Spalte Rechnung!W vorgenommen wurde), nicht mit einem bekannten Wert in unserem Datenblatt übereinstimmt, dann kommt eine Null heraus ,sonst suche den Nachbarwert heraus (immer die 100). Diesen multipliziere mit der Faktorzahl von der Styletabelle. Eine ausführliche Beschreibung der Formel finden Sie unter dem Punkt Alkoholgehalt der Inhaltsangabe der ereiterten Tabelle.

Im ersten Fall (wenn Formel: dann) ist 0*100=0. Dieser Wert wird mit den anderen Werten später addiert und ergibt den Wert für den ersten Tag.

Ist der Wert vorhanden (wennformel sonst), dann wird er mit dem Multiplikationswert, z. B. 0,5 (um 50 ml zu erhalten), dann ergibt das 50.

Leider gibt es bei der Formel noch einen dritten Fall, der zu einem Fehler führen würde (eigentlich kein Fehler, sondern der Hinweis #NV - nicht Vorhanden - mit dem man nicht mehr weiterrechnen kann) , wenn wir nicht zuvor in der Spalte von Rechnung!W eine Vorsorge getroffen hätten: 

Es steht überhaupt kein Wert im Tabellenblatt Style!, also ein NV# in W3. Es kommt zu einer Fehlermeldung, beim kopieren der Zelle, weil man Nichts nach Excellogik mit etwas vergleichen kann. Wären nur zwei Bedingungen vorhanden, dann würde das Excel schlucken, aber be drei bedingungen leider nicht... Deshalb muss man in W3 auch im Falle einer leeren Zelle eine Bedeutung geben:

In Spalte Rechnung!W sind nun die Verweise sämtlicher Mahlzeiten aller Tabellentage in einer Spalte nacheinander aufgeführt. Aus Rücksicht darauf, dass es zu Fehlermeldungen beim Sverweis kommt, wenn kein Wert eingetragen ist, wird durch eine Wennformel der Text kein Wert ausgeworfen!

Beispiel Rechnung!W3

=WENN(style!F12=""; "kein Wert";style!F12)

Eine einfache Wennformel: Der erste Wert der Nahrungstabelle ist Style!F12. Steht dort nichts, was durch die beiden Anführungszeichen symbolisiert wird, dann wird kein Wert ausgeworfen - sonst wird jede beliebige Eingabe des Users übernommen.

Diese Vorgehensweise ist einer Eigenart vo Excel geschuldet und wird in der Spalte mit den Sverweisen eingehend erklärt.

In der Spalte X und Y stehen Getränk und Promille in Bezug zu der Tabelle Daten. Das ist nichts neues. Antialkoholische Getränke bekommen eine null. Ist zuerst verwirrend, aber das ist für die Alkoholberechnung, die in einem Abwasch durchgeführt wird, von Bedeutung!

Damit ist das zustandekommen des Flüssigkeitswertes hinreichend geklärt worden, wenn man die vorherige Beschreibung gelesen hat. So ist diese Projekthandlung aufgebaut: Am Anfang wird alles sehr ausführlich erklärt - hier nur noch, was neu ist!

Danach werden die Werte der Flüssigkeitsaufnahme nach altem Schema wie bei Kohlenhydraten, Kalorien usw. ausgezählt und im Tabellenblatt !style von Zelle O110 an ausgewertet nach Tagen,Tagesdurchschnitt und Gesamtmenge. Das ist dem Punkt Auswertungen vorbehalten.

Zurück zu den Rechnungen. Noch immer ist die Alkoholfrage offen.


zurück zum Inhaltsverzeichnis

6. Die Alkoholberechnung

In Spalte Rechnung!Z ordnen wir einer Eingabe im Tabellenblatt style! einer Eingabe den entsprechenden Volumengehalt zu, oder als Beispielformel:

=WENN(ISTNV(SVERWEIS(W2;$X$2:$Y$101;2;FALSCH));0;SVERWEIS(W2;$X$2:$Y$101;2;FALSCH))

Die Formel kennen wir wieder fast aus der vorherigen schrecklichen langen Formel. Sie sagt: Wenn es keine Übereinstimmung vom Eingabewert für den User mit dem Sverweis (diesmal Produt - Volumengehalt) gibt, dann setze eine Null, ansonsten schreibe den Volumengehalt von der Matrixformel. Immer daran denken: Macht der User keine Eingabe greift unser dritter Fall - die Spalte Rechnung!w beinhaltet zumindest den Text kein Wert....

nochmal kurz erklärt, da im Kapitel Flüssigkeiten ein wenig übergangen:

=wenn( Einleitung der Wennformel - immer gleich - immer daran denken: Es wurde eine Klammer geöffnet, die muss auch immer geschlossen werden...

ISTNV(Dieses heißt ist nicht vorhanden, eine Formel, also wieder eine Klammer geöffnet. Die Formel kennt ein Argument. In diesem Fall:

SVERWEIS(W2;$X$2:$Y$101;2;FALSCH)


Ist ein Sverweis, der in diesem Fall die Zelle W2 mit den Werten X2 bis x101 vergleicht und den danebenliegenden Wert von Y2 bis Y101(Das nennt sich Suchmatrix) als Antwort gibt. Y ist die zweite Spalte von X (2 =Spaltenindex). Falsch heißt, dass es eine genaue Übereinstimmung geben muss. Optional wäre wahr möglich, dann würde der nächste Wert gesucht werden....

) Dann wird die Klammer von Istnv( geschlossen und wir sind wieder in der wennformel.

;0; Nach dem abschließenden Semikolion der Prüfung der Wennformel kommt der sogenannte Dannwert. Es wird eine Null herausgegeben, wenn die Prüfung negativ verlief. Genausogut hätte in Anführungszeichen "Kein Wert" oder ein anderer beliebiger Text stehen können ...Abgeschlossen wird der Dannwert durch ein Semikolon.

SVERWEIS(W2;$X$2:$Y$101;2;FALSCH))Der Rest ist schnell erklärt: Der letzte Prüfungspunkt der Wennformel ist der Sonstwert. Ansonsten wird also mit einem Sverweis der zugehörige Matrixwert ausgedruckt. Die letzte Klammer schließt die Wennformel.

Wir haben nun also Volumengehalt und Flüssigkeit.

zurück zum Inhaltsverzeichnis

7. Berechnung des Promillegehaltes


Einschnitt: Ziel ist es nun, den Promillegehalt zu berechen. Dazu müssen Sie zuerst die Formel kennenlernen:

c=A/m*r

C= sind die uns bekannten Promille

A= die aufgenommene Menge des Alkohols in Gramm

Da Sie den Grammwert der Flüssigkeit, aber nicht den Grammwert des Alkohols in unserer Tabelle haben, müssen Sie den Grammwert in einer Spalte in den einzelnen Zellenn berechnen:

A=V*e*p

V: ml des Getränkes
e= ist der Volumengehalt, also Bier um die 5 %, steht immer so schön auf den Flaschen
p= Alkohol hat immer 0,8 g/ml

Diese Werte habe wir in der Tabelle bis auf p, also die Dichte von puren Alkohol, die immer bei konstant 0,8 g/ml liegt.

R= der Reduktionsfaktor im Körper

Nach Seidel ist der für Mann und Frau unterschiedlich - daher zwei Rechenwege:

RW = 0,31233 − 0,006446 · Körpergewicht + 0,004466 · Körperlänge für die Frau

RM = 0,31608 − 0,004821 · Körpergewicht + 0,004432 · Körperlänge für den Mann

siehe Wikipedia

Jetzt weiter in der Tabelle Rechnung!

In Spalte Rechnung!AA steht also die Berechnung der Grammzahl des Alkohols (obere Formel für A):

Beispiel: =WENN(Z3=0;0;0,8*(Z3/100*V3))

Die Wennformel sieht für die Nachbarzellen wieder zwei Fälle vor. Es steht eine Zelle mit  0 % Volumengehalt (eben kein Alkohol), dann bleibt es eine 0. Ansonsten wird der Volumengehalt mit 0,8 multipliziert und durch 100 mal dem zuvor errechneten Grammwert des Alkohols genommen.

Nun haben wir als Teildisziplin den Grammwert des Alkohols.

In den Spalten Rechnung!AB und Rechnung!AC stehen die berechneten rw-Werte für Mann und Frau in jeweils einer Zelle. Dieser berechnet sich individuell durch Eingabe des Gewichtes und der Körperlänge in der Tabelle Style!

Diese Berechnung nach Seidl bietet sich deshalb auch an. Hier als Stellvertreter die Berechnung für den Mann:

=0,31608-(0,004821*style!C52)+(0,004432*style!C53)

Deshalb ist eine Ernährungstabelle mit Bodymaßindex eine ideale Grundlage. Dieser enthält eben Gewicht und Größe des Probanden.

In den Spalten Rechnung!AD und Rechnung!AE wird am Ende je nach Mann und Frau die Berechnung für den Promillegehalt des Getränkes während zweier Mahlzeit durchgeführt. Exemplarisch wieder für den Mann:

=AA3/($AB$2*style!$C$52)

Das ist nichts weiter als die Einsetzung der zuvor errechneten Werte in die Formel für Promille.

Danach werden die Werte auf dem Tabellenblatt Style! berechnet.

zurück zum Inhaltsverzeichnis


8. Berechnung und Ausgabe vom Alkohol auf dem Tabellenblatt Style!

Unter dem Tag 3 im Tabellenblatt Style! befindet sich die Auswertung bezüglich der Promille.

Als erstes schauen Sie in die Tabelle auf dem Blatt style zur Auswertung im Zellbereich W115:AB126.

Dort befindet sich die Auswertungstabelle sortiert nach den Überschriften: Tag (w115), Promillegehalt Mann (x115), Frau (y115), gezählter Tag (z115), Abbau in Stunden (AA115) und Frau (AB115). Da die Berechnungen easy sind, wurden Sie direkt in den Zellen der Tabelle Style! durchgeführt.

Exemplarisch die darunterliegende Reihe 117 - der Rest wurde für die Tage nach unten kopiert:

Zelle: style!w117:  1

Keine Formel, sondern nur der erste von 10 Tagen. Ist mehr für die Optik. Die eigentliche Berechnung folgt in z117, nämlich ob dieser Tag Werte enthält oder nicht! Enthält der tag keinen Wert, so wird er nicht berücksichtigt.
Es ist egal ob nur ein Tag oder mehrere gezählt werden. Die Auswertung berücksichtigt jeden Tag einzeln einschließlich des Abbaus der Promille von Frau oder Mann. Sie können also jederzeit einen unabhängigen Alkoholtest wie in einer Onlinetablle oder mit Hilfe eines Promillerechners den exakten Promillewert und dessen Abbau bestimmen.

Zelle: x117: =SUMME(Rechnung!AD2:AD84)

Hier wurde eine einfache Summenrchnung durchgeführt. In Rechnung!AD2:AD84 sind die im letzten Kapitel berechneten Promillewerte. Nun werden die Promillewerte, die ein Spiegel der Nahrungseingaben des ersten Tages von Style! sind, addiert.

Abstecher in Zelle X128 =SUMME(X117:X126)

Hier werden die Promillenzahlen sämtlicher Prüfungstage addiert.

Zelle y117 =SUMME(Rechnung!AE2:AE84)

Dasselbe Spiel für die Frau. Nur das diesmal die zuvor errechneten Promillewerte für die Frau addiert werden.

Zelle z117 =WENN(X117>0;1;0)

Ein wenig tricky. Um  am Ende der Tabelle herauszubekommen, auf wieviel Tage sich der Alkoholkonsum verteilt, wird an dieser Stelle mit einer Wennformel entschieden, ob an diesem ersten Tag getrunken wurde. Sind Promillewerte erfasst, dann ist X117 größer als 0 (Prüfungskriterium der Wennformel), dann (Dannwert) wird eine 1 ausgeworfen, sonst eine 0.

Abstecher in Zelle: Z128: =SUMME(Z117:Z126)

Hier werden die einzelnen Tage mit einer 1 in einer einfachen Summenformel addiert - also die Tage des Alkoholkonsums im Prüfungszeitraum von 10 Tagen gezählt!

Zelle AA117 und AB117 für die Frau

In Zelle AA117 steht  =WENN(X117=0;0;X117/0,1+2)

Das ist für den Mann der Wert der Promille des ersten Tages aus den vier Mahlzeiten. Pro Stunde werden ca. 0,1 bis 0,2 Promille abgebaut.

Wenn der Wert 0 ist, dann wird eine Null ausgeworfen, sonst wird die o. a. Rechnung durchgeführt. Ansonsten wird für 0 Promille eine 2 stündige Zeit zum Abbau berechnet - ein ausgeglichener Bug am 23.08.2011 entdeckt!

Der Sternenhimmelstuermer nimmt lieber die ungünstigste Variante - vor Gericht hätte diese Berechnung keinen Bestand. Dann werden zum Ergebnis noch zwei Stunden hinzugerechnet, da der Abbau erst nach zwei Stunden nach der letzten Aufnahme aufgenommen wird. Das hört sich alles abenteuerlich an, ist aber valide. Die Ernährungstabelle braucht in Bezug auf Genauigkeit keinen Online-Promillerechner zu fürchten!

Für die Frau dasselbe in grün, nur eben bei der Formel y117 statt X117. Vorsicht! Es gibt Stimmen die behaupten, dass der Alkoholabbau bei Frauen langsamer vonstatten geht. Fakt ist, dass Frauen aufgrund ihrer Anatomie bei gleicher Alkohoaufnahme einen höheren Promillewert erreichen! Daher dauert der Abbau folglich auch länger.

Das die Leber hingegen langsamer arbeitet ist wissenschaftlich eher nicht bewiesen...

Wir haben also bisher folgende Endauswertungen

In der Spalte x und y die Promillewerte an einem Tag für Mann und Frau.

In der Spalte AA und AB der Abbau der Promille in Stunden am jeweiligen Tag.

Dazu kommen im Schnelldurchlauf:

In der Spalte w129 (=SUMME(X117:X126)) durch eine Summenformel die gesamte Promillemenge in allen Prüfungstagen für den Mann und in y127 für die Frau (=SUMME(Y117:Y126)).
In Spalte Z128 ( die gesamten gezählten Tage durch eine Summenformel)

In W129 den durchschnittlichen Alkoholkonsum an den gezählten Tagen (=X128/Z128) für den Mann. und in X129 für die Frau.

In Zelle AB 128 ist der Maximal erreichte Alkoholwert für den Mann in Promille an einem Tag - kein Grund stolz zu sein...(=MAX(X117:X126)). In der Zelle AB130 dasselbe für die Frau (=MAX(Y117:Y126)).

Jetzt wieder ein wenig präziser, da die darunter liegende Tabelle eine Einteilung nach Wochentagen und Wochenende beinhaltet. Sie können damit Ihr Trinkverhalten ein wenig beobachten...

In der Zelle U134 steht =I2.

Zur Erinnerung: In I2 steht sollte der User den ersten Tag in der Form TT.MM.JJJJ eintragen. In den folgenden Tagen passt Excel Dank Formeln in der Tabelle Style! Wochentag und Datum an.

Abgesehen davon greifen Sie nur auf I2 zu. in den folgendenen Spalten wird I2 immer mit 1, 2,3...- 9  addiert, um 10 Tage zu erhalten.

In der Nachbarspalte von U134 steht der dazugehörige Wochentag. Dieser wird wieder exemplarisch mit der Zelle v134 erklärt:

=TEXT(WOCHENTAG(U134); "TTTT") 

=Text( Formel, um den folgenden Inhalt als Text  darzustellen

WOCHENTAG(U134) ist  das erste Argument (Wert) des Textes: Der Wochentag von Zelle U134 soll als Text dargestellt werden.

Nach dem Semikolon kommt das zweite Argument von Text, nämlich das Textformat.

Das   "TTTT" ist das Textformat, in dem Excel den Wochentag ausgibt. Das ist leicht missverständlich. Hier in diesem Fall stehen vier T für den ausgeschrieben Wochentag. Geben Sie mal Spaßeshalber zwei T ein. Das ist dann die Ausgabe des Tages im Textformat als Zahl 10. Es ist also ein internes Anzeigeformat für Excel.

Daneben sind in den Spalten W und x die Promillewerte für Mann und Frau wiederholt - das sieht einfach nur schön aus - mehr nicht!

In Reihe Y sind nur die Werktage von Mo - Fr und Sa und So aufgelistet. Dazwischen eine Unterbrechung für die Ergebnisspalten, wo die zugehörigen Namen für Promille Werktag und Wochenende stehen.

Wie wurden nun in der nächsten Spalte die Werktage, Wochentage und Ergebnisse berechnet?

Mit Formeln für jeden Werktag und Wochenendtag.

Mal in Z134 für den Mann am Montag exemplarisch:

=SUMMEWENN($V$134:$V$143;"Montag";$W$134:$W$143)

Mit einer Summewennformel  die ziemlich easy ist, wenn man Sie erklärt:

=SUMMEWENN( ist die Formel zum erkennen und addieren der Werte.

Als Satz würde die Formel lauten: Wenn Sie in der Spalte für Wochentage (v in Textform - sie erinnern sich?) das Wort Montag finden, dann addieren Sie in der Nachbarspalte (w) den dazugehörigen in gleicher Reihe stehenden Wert.

Allein für den Vergleich brauchten wir also den Text "Montag". Was zwischen den Anführungszeichen an Text steht ist Excel inhaltlich gesehen schnuppe.In der Form ist dieses aber das Suchkriterium.

$V$134:$V$143; ist der Bereich, in dem der Vergleich vorgenommen wird  es bietet sich immer an die linke Spalte als Bereich zu wählen. Das Semikolon beendet das Argument.

"Montag" ist das Suchkriterium. Bei Text immer in Anführungszeichen, bei Zahlen ist das egal...abgeschlossen durch ein Semikolon.

$W$134:$W$143) Summebereich ist die nächste Spalte. Excel sucht automatisch bei dieser Konstellation die Nachbarzelle vom Bereich.

Dasselbe wurde am Di, Mi, Do und Fr. durchgeführt und dann das Ergebnis mit einer Summenformel in Zelle Z 139 addiert (=SUMME(Z134:Z138)).

Dasselbe für die Frau in der Nachbarzelle...

Das Schema können Sie nun für die Berechnung am Wochenende übertragen:

Samstag und Sonntag als Text darstellen, mit der Summewennformel addieren und fertig!

Diese Verfahrensweise können Sie auf viele Projekte übertragen - wannimmer Sie an beliebigen Tage Werte berechnen wollen. Das wird im Internet oft nachgefragt - hier haben Sie einen Lösungsweg ohne komplizierte Formeln.

Die letzte Auswertung betrifft den Maximalwert (style!AB129 für Mann oder styleAB!130 Frau) an einem Tag.

Wieder für den Mann exemplarisch:

W145 =AB130 - also die Wiederholung des höchsten Promillegehaltes eines Mannes im Prüfungszeitraum

X145

=WENN(UND(W145>=0;W145<0,5);"nicht berücksichtigt";WENN(UND(W145>=0,5;W145<=1);Rechnung!$AI$25;
WENN(UND(W145>1;W145<=2);Rechnung!$AI$26;
WENN(UND(W145>2;W145<=3);Rechnung!$AI$27;Rechnung!$AI$29))))

Hier wird nur mehrfach eine Wennformel verknüpft. Das Schema bedeutet: Wenn ein Promillewert zwischen 0 und 0,5, dann wird das in der Ausgabe "nicht berücksichtigt", ansonsten wenn der Promillenwert (W145) zwischen 0,5 und 1, dann gebe den der Zelle  ;Rechnung!$AI$25 wieder, sonst, wenn der Promillewert (eben W145) zwischen 1 und 2 liegt, dann den Wert der Zelle Rechnung!$AI$26, sonst, wenn der Wert zwischen 2 und 3 liegt, dann $AI$27 und ansonsten (also wenn der Wert 3 übersteigt oder negativ wär) den Wert Rechnung!$AI$29.

Danach werden die Klammern für alle zuvor geöffneten Wennformeln geschlossen, also 4 Klammern.

Die Und-Formel enthält also zwei Argumente. Einmal größer gleich und einmal kleiner.

Die Werte wurden mit Dollarzeichen (Taste F4) absolut gesetzt, damit die Formel für Frauen kopiert werden kann. Dollarzeichen wären also singulär betrachtet nicht nötig.

Die Zellen in Rechnung! beinhalten den jeweiligen Auswertungstext. Dieser kann beliebig verändert werden. Ob Zahl oder Text und Form in den Zellen von Rechnung spielen keine Rolle.

zurück zum Inhaltsverzeichnis

9. Vitamintabelle und Jokerfunktion zum Erweitern der Tabelle

Erst mal ganz entspannt die Beschreibung der Jokerfunktion für den Excelbeginner - mit leichten Kenntnissen können Sie dann die Tabelle beliebig erweitern.

Es gibt bekanntlich drei Tabellen: Style!, Daten! und Rechnungen!.

Tabellenblatt Daten!

Wir gehen nun chronologisch vor. Im Tabellenblatt Daten! überschreiben Sie in Daten!AG2 Joker1 und tragen z. B. Eisen für das Element Eisen ein. Danach schauen Sie in die Spalte S. und tragen in S3 z. B. Apfel ein. Danach können Sie die Werte für die Vitamine und den zugehörigen Wert für Eisen in AG3. Bezugsgröße sind immer 100 G. Durch den Multipliaktorwert in Style! wird das dann angepasst an ihren realen Verzehr.

Das machen Sie dann für andere Werte in den nächsten Reihe genauso und füllen so die Datenbank. Dazu brauchen Sie keine Formelkenntnis!

Tabellenblatt Rechnungen!

Dieses Blatt ist für den unbedarften User eigentlich tabu! Aber es wird Zeit nun mal sich in Bezug auf die Jokerfunktion sich mal dieses anzuschauen  keine Sorge, hier erstmal ohne große Formelbetrachtung.

Von Reihe AS bis CC stehen die Vitamine. In CB2 steht nun im Beispielfall Eisen (in der Funktionsleiste =Daten!AG2). Der Sternenhimmelstuermer kopierte bereits die kompletten Berechnungen in diese Reihe. In diesem Fall ist also nicht mehr im Blatt  Rechnungen zu tun!

Wie steht es aber, wenn Sie noch mehr Berechnungen durchführen wollen?

Dann tragen Sie im Tabellenblatt Daten! einfach neben der Jokerfunktion einen neuen Wert ein - aber bitte genau daneben, sonst funktionieren die Berechnungen im Tabellenblatt Rechnungen! nicht mehr! Wieder zurück zu den Rechnungen...
Sie markieren am besten die Reihen BY bis CC in der Überschriftenleiste und markieren die Reihe CD. Dann fügen Sie in CD die Reihe und drücken auf Einfügen - die Spalten werden ergänzt.
Das war es. Auf diese Weise haben Sie die Berechnungen für zwei neue Werte geschaffen und ohne Kenntnis können Sie natürlich nun auch in Daten einen zweiten Wert eintragen...

Das Tabellenblatt style!

Hier können Sie nun die Ergebnisse der Berechnungen beliebig gestalten. Am einfachsten kopieren Sie die letzte Auswertungsreihe ab CO155 bis CO 173. Nach dem kopieren müssen Sie nur noch  die ehemaligen kopierten Werte von CO 159 bis co168 anpassen.

Das sind die Tagesauswertungen der Mahlzeiten.

Mal als Beispiel C059=SUMME(Rechnung!CA3:CA84). In der kopierten Zelle haben sich die Buchstaben geändert. Das ist normal. Excel passt das automatisch an - leider in diesen Fällen nicht zu unseren Gunsten. Sie passen das nun manuell an, indem Sie die Buchstaben der Reihen hinter Rechnungen! anpassen. Die Zahlen bleiben unverändert!

aus =SUMME(Rechnung!CA3:CA84) wird z. B. bei unserem Joker1 (Beispiel Eisen) in  =SUMME(Rechnung!CB3:CB84).

CB
ist also die Berechnungsreihe für unseren Joker. Die Nummern dürfen nicht geändert werden. Denn 3 bis 84 entspricht den Berechnungen für die Mahlzeiten des ersten Tages.

Blind ohne Hintergründe ist das schwer zu verstehen, aber wenn Sie sämtliche Punkte dieser Abhandlung lesen, dann erscheint es Ihnen trivial.

Welchen Text Sie dazu schreiben, bleibt Ihnen überlassen, Sie haben auf diese Art immer den Durchschnittswert und die Gesamtmenge der aufgenommenen Nahrung.

Für den Excelfan

Jetzt geht es aber weiter für die Excelfans, die sich aber erst die vorherigen Kapitel anschauen sollten, um diesen Part und dessen Innovationen zu verstehen. Das grobe Thema ist Kopieren, relative und absolute Verweise und die ultimative Anwendung des Sverweises zum Auslesen einer Tabelle.

Die Tabelle Daten! muss nach den letzten Ausführen nicht mehr weiter erklärt werden.

Wir gehen also sofort ins Blatt Rechnungen!

Die Reihen AT bis AY sind interessant! Der Rest ist eine simple Kopie! Aber um Kopieren zu können ist ein wenig Vorausschau bei der Setzung von relativen und absoluten Verweisen nötig.

Gehen wir die Reihen mal durch:

AT

=WENN(Daten!S3=""; "kein Wert";Daten!S3)

Das ist die Wiederholung der eingetragenen Daten in der leeren Datenbank, um genau zu sagen die Spalte für Nahrungsmittel Äpfel, Birnen usw. - eben was vorher eingetragen wurde. Bis Zeile 830 sind Werte eintragbar und werden berechnet. Steht nichts in dieser Spalte, so wird "kein Wert" eingetragen.

Wenn kein Eintag in der Tabelle, dann "kein Wert", sonst der eingetragene Wert. Warum nicht eine Null? Weil dann der nächste Sverweis nicht mehr funktionieren würde. Diese Problematik wurde bereits in der letzten Tabellenauswertung besprochen. Es wird wie bereits mehrmals erwähnt zum Ende gegen dieser Abhandlung nicht mehr jede Formel und Zeile erläutert.

AU und AV

In AU und AV werden nur die eingetragenen Werte von Vitamin A und D in der Tabelle Daten wiederholt. Das sind dann alle Vorbereitungen, um nun den SVerweis anzuwenden.

AW

In AW2 steht

=WENN(ISTNV(SVERWEIS(style!$F12;Rechnung!AT$2:AU$830;2;FALSCH));"0";
SVERWEIS(style!$F12;Rechnung!AT$2:AU$830;2;FALSCH))*style!$G12

Die Formel wurde bereits in der letzten Tabellenauswertung dargestellt - wird wegen der relativen und absoluten Verweise aber nochmals mit dem neuen Schwerpunkt des späteren Kopierens nochmal erläutert:

Wenn also auf dem Tabellenblatt Style! in der Zelle F12 (Vergleichszelle vom Sverweis zu unserer Datenbank in den Reihen Rechnung!AT und Rechnung!AU - in diesem Fall der erste Eintrag in der Nahrungstabelle am ersten Tag der ersten Mahlzeit), kein Wert (Formel:Ist nicht vorhanden), dann eine Null, sonst Vergleiche den Wert mit dem Tabelleneintrag - z. B. einem Apfel. Der Apfel ist also in F12 und AU und der dazu eingetragene Wert würde - z. B. 0,5 -  mal den Multiplikatorwert genommen (Style!g12) - da die ursprüngliche Bezugsgröße von Style!F12 100 Gramm ist. Das wäre dann der Wert für 50 Gramm.

Um die Formel geht es hier nicht mehr, sondern um die Dollarzeichen, also absolute Verweise.

$F12 - Die Spalte F wurde absolut gesetzt. Die Reihe 12 bleibt relativ. Beim Kopieren mit dem Anfasser nach unten wird aus $F12 also $F13. Beim seitlichen kopieren nach rechts würde aus $F12 wieder $F12 werden, da F eben absolut ist.
Also kann in den Nachbarspalten immer wieder Style!F12 geprüft werden...

Im Sverweis befindet sich der nächste absolut gesetzte Wert: Rechnung!AT$2 
At kann also wieder verändert werden, die Zahl ist wieder zum kopieren optimiert. Excel passt also beim Kopieren wieder die Werte an. Dasselbe für den Multiplikator $G12.

AX

In AX steht

=WENN(ISTNV(SVERWEIS(style!$F12;Rechnung!AT$2:AV$830;3;FALSCH));"0";
SVERWEIS(style!$F12;Rechnung!AT$2:AV$830;3;FALSCH))*style!$G12

Fast dasselbe nochmal. Ein kleiner Unterschied sorgt aber dafür, dass diese Berechnung nochmal erstellt wurde.
Als Vergleichsspalte (Spaltenindex) wurde eine 3 statt einer 2 eingetragen. Der Wert wird also mit der zweiten Nachbarzelle verglichen. Das geht dann beim Kopieren schneller, aber ist erstmal beim Herrunterkopieren langsamer, da nach jeder Mahlzeit und jedem Tag die beiden Formeln angepasst werden mussten - eine Sauarbeit!.

Die Spalten AT bis AX konnten dann weiterkopiert werden. Für jeweils zwei Vitamine...und Sie können dieses Spiel so lange fortsetzen, bis Excel es nicht mehr zulässt...

Das war es.

Auswertung in Tabelle Style

Die Auswertung für die erlangten Daten auf dem Blatt style! müssen nur noch überflogen werden:

Nehmen wir stellvertretend für alle Auswertungsstränge den letzten in der Spalte CO. In CO159 bis 168 stehen in rot ohne Einheiten die addierten Werte aus dem Berechnungsblatt, die aus dem Datenblatt ausgelesen wurden.

In Stellvertretung Zelle CO159

=SUMME(Rechnung!CA3:CA84)

Das ist also die Summe der berechneten Werte des 1. Tages (also 4 Mahlzeiten) der Ernährungstabelle.

In Zelle co173 =SUMME(CO159:CO168) werden diese Werte addiert und die Gesamtmenge im Prüfungsraum ermittelt.

Zur Berechnung des täglichen Durchschnittes mußte der Sternenhimmelstuermer eine Abfrage an den User der Tabelle voranstellen, wieviel Tage überprüft wurden. Null Vitamine an einem Tag heißt ja nicht, dass automatisch keine Vitamine zu sich genommen wurden und der Tag damit aus der Rechnung herausfällt. Daher in Style!E155 erstmal ein leeres graues Kästchen für die Abfrage.

Trägt der User nichts ein, so wird von dem Maximalwert 10 der Tabelle ausgegangen, was sich in der Formel für Zelle C0170 schön widerspiegelt :

=WENN(ODER($E$155=0;$E$155="");10;$E$155)

Einfache wenn- Formel mit Oder-Formel kombiniert: Wenn in Zelle E155 (absolut gesetzt zum Kopieren) eine 0 oder überhaupt nichts steht (doppelte Anführungszeichen stehen für kein Inhalt), dann gib uns eine 10 (für Zehn Prüfungstage), sonst gib uns den Wert wieder, der in E155 steht (also einen Prüfungstag oder 2,3,4,5,6,7,8,9, je nachdem was der Benutzer eintrug).

Zu guter letzt wurde in Telle co158 die Gesamtmenge durch Prüfungstage geteilt, also der Durchschnittswert gebildet:

=CO173/CO170

Dieses wurde bei sämtlichen Auswertungen so gemacht und Sie können diese Auswertungstechnik gerne für andere Element übernehmen. Gesamtmenge und Durchschnittsmenge sind eigentlich für solche Auswertungen die relevanten Zahlen.

Wer es nicht so mit der Syntax hat - die Formeln wurden bis ins Detail mehrmals in dieser Abhandlung beschrieben.


Fazit

Das Projekt Ernährungstabelle wurde am 22.08.2011 abgeschlossen. Es ist das zweitgrößte Projekt auf dieser Homepage. Ein Grund stolz zu sein, obwohl viel anders lief, als es sich der Sternenhimmelstuermer vorstellte. Der Quellcode ist valide, aber während der Bearbeitung wurden mehrere Fehler begangen, die hier nochmal im Rahmen der Projektgestaltung kurz angerissen werden.

Der Grundaufbau von drei Seite und das Designhat sich bewährt. Da die erste Tabelle nur einen Prüfungszeitraum von sieben Tagen hatte, mußte vieles verändert werden.

Kritik: Es wäre im Sinne einer effektiveren Bearbeitung besser gewesen in der Tabelle style! die Prüfungstage untereinander zu schreiben, um bei den Berechnungen nicht so viel springen zu müssen. Umgekehrt ist das natürlich auch ein wenig anspruchsvoller und nicht 0815.

Da die Vitamine am Schluss eingebaut wurden, ist der Berechnungsstil uneinheitlich. Die ersten Berechnungen von Kohlenhydraten, Eiweißen, Fetten und Ballaststoffen waren auch durch die unterschiedliche Gestaltung bedingt nicht so einfach zu Kopieren, was einen höheren Arbeitsaufwand bedeutete. Erst bei den Vitaminen kam eine für die Bearbeitung optimierte Variante ins Spiel.

Wäre eine schriftliche Vorausplanung besser gewesen?

Njein, einerseits wären viele Änderungen erspart geblieben und vielleicht später einige Stunden gespart worden. Andererseits wären nach ein paar Stunden Vorbereitungsphase dem Sternenhimmelstuermer starke Zweifel an der Durchführbarkeit des Projektes aufgekommen...

Mit dem Rest der Projektbearbeitung ist der Sternenhimmelstuermer sehr zufrieden. Die Salamitaktik - das Aufspalten in kleinere Sinneinheiten - hat sich bewährt. Es wurde alles dokumentiert und wer sich ein wenig mit der Tabelle beschäftigt, bekommt Antworten auf die wichtigsten Schwerpunkte einer interaktiven Tabelle und deren Auswertung.

Anmerkung: Andere  Webmaster können die Tabelle selbstverständlich auf Ihren Webspace kopieren, sind aber verpflichtet einen validen Link auf diese Seite unmittelbar neben dem Downloadlink zu setzen - in der Art "Erstellt vom Sternenhimmelstuermer.eu".

Die kommerzielle Verbreitung ist untersagt. Die Tabelle bleibt Freeware. Natürlich können aber kommerzielle Seiten diese Tabelle zum freien Download mit beliebigen Veränderungen anbieten. Nach den o. a. Bedingungen. Schon allein für diesen Text lohnt sich für Sie die Verlinkung.

Sollte jemand valide und urheberrechtlich abgedeckte Daten in die Tabelle eintragen, dann setzt der Sternenhimmelstuermer auch gerne auf diese Seite einen Link zurück, in so weit sie den rechtlichen Normen der BRD entspricht. Ein Link von dieser Seite ist wertvoll...schauen Sie im Impressum dieser Seite nach der Adresse.

Ansonsten wünscht der Sternenhimmelstuermer viel Spaß bei der Verwendung der Tabelle!

Listenfelder 06.09.2011 Nachtrag


Der Sternenhimmelstuermer wollte die Tabelle nicht mehr verändern, da er aber nun frei verfügbare Daten über die USDA verfügt, lies er sich eine weitere nützliche Erfindung einfallen, die den Lien den Umgang erleichtert, aber dafür die Datenbank beschränkt, was Sie nach Lesen des Kapitels leicht nachvollziehen können. Sie können die Beschränkung auch einfach aufheben und das Listenfeld einfach löschen und dann Ihre Einträge frei nach Schnauze machen - was bei einer umfangreichen Datenbank schwerer ist, immer den buchstabengetreuen Eintrag im Kopf zu haben...

Zäumen wir das Pferd von hinten auf: In jeder Zelle bei den Mahlzeiten ist ein Listenfeld, aus dem man die Daten aus der Tabelle Daten! aufrufen kann. Die Bezugsfelder des Listenfeldes sind in der Reihe A des Tabellenblattes Daten!. Geschieht dort ein Eintrag, dann wird er im Listenfeld des Tabellenblattes Style! übernommen. Deshalb sind so viele Leerfelder im Listenfeld, weil dort eine 0 als Platzhalter für die leeren Datenbankfelder eingetragen ist.

Das wäre unproblematisch, aber die Getränke stehen ja mit Promilleangabe in den Reihen P (Name der Flüssigkeit) und Q (Volumengehalt bei alkoholischen Getränken, bei nichtalkoholischen Getränken eine 0 durch Ihren Eintrag!) des Tabellenblattes Daten! in einer quasi unabhängigen Datenbank. Dieses wird im Tabellenblatt style in den Listenfeldern jetzt nicht mehr berücksichtigt. Ohne Listenfeld konnten Sie z. B. ein Bier in das Tabellenblatt Style eintragen. Die Berechnungen würden unabhängig nach Nahrungsbestandteilen und Flüssigkeiten/Alkoholgehalt durchgeführt. Nun steht aber ein Listenfeld da und darin wäre kein Wort Bier im Dropdownmenü.

Also muss nun das Bier in beide Datenbanken übernommen werden, um bei der Berechnung berücksichtigt werden zu können. Kleines Problem: Der Sternenhimmelstuermer hat noch nicht Proteine, Kalorien....eingetragen, so dass diese von Flüssigkeiten zur Zeit nicht berücksichtigt werden. Sie müssen das also nachtragen oder warten , bis der Sternenhimmelstuermer die Datenbank weiter ergänzt.

Wie gesagt, wer will kann die Listenfelder löschen, muss aber dann  die Einträge im Kopf haben - besser ist das System mit Listenfelder...

Vielleicht verstehen Sie die eben gemachten Ausführungen besser, wenn Sie in den folgenden Beschreibungen für den Excelfan das nachvollziehen:

Zum Erzeugen eines Listenfeldes brauchen Sie eine Reihe mit Daten (in unserem Fall die Reihe A des Tabellenblattes Daten!, was nicht ganz der Wahrheit entspricht, wie Sie es in den folgenden Ausführungen sehen werden) und eine Zelle, in dem Sie das Listenfeld kreieren. Normalerweise  wäre es einfach.

In einer Zelle wird ein Listenfeld eingefügt und dann einfach eine beliebige Reihe in den Bestand des Listenfeldes aufgenommen.

Die Listenfelder beinhalten absolute Bezüge (die lustigen Dollarzeichen), so das ein Listenfeld mit dem kleinen Anfasser unten rechts blitzschnell kopiert werden kann. Es gibt aber ein Problem. Excel ist fies. Listenfelder können nur auf einem Tabellenblatt stehen.

Also ein kleiner Trick: Die Referenzwerte von dem Tabellenblatt Daten! wurden in das Tabellenblatt Style! in die Reihe DB gespiegelt.

In DB2 von Tabelle Style! steht deshalb: =Daten!A1

So, nach einem schnellen Kopieren der Reihe durch den kleinen Anfasser unten rechts haben Sie nun eine valide Reihe auf dem Tabellenblatt Style!.

Dort nahm der Sternenhimmelstuermer hier exemplarisch ausgeführt die Zelle F12 für die Eingabe eines Lebensmittelproduktes in der ersten Mahlzeit eines Tages (jede andere wäre natürlich auch möglich gewesen) und markierte diese Zelle.

Danach wechselte der Sternenhimmelstuermer im Ribbenmenü auf den Reiter Daten. In der Rubrik Datentools ist ganz oben rechts ein kleines Icon mit einem Dropdownmenü nebenan. Da gehen Sie drauf und wählen Datenüberprüfung aus. Dann im aufklappenden Dialogfenster in der Registerkarte Einstellungen unter Zulassen   wählen Sie im Dropdownmenü Listenfeld aus. Danach unter Quelle am besten das Icon rechts neben dem Eingabefeld anklicken.

Danach können Sie durch Markieren in der Tabelle die Datenreihe auswählen. Danach übernehmen sie die Dateneihe ( in unserem Fall =$DB$1:$DB$477 ). Das ist der Datenbereich.

Einschub: Besserwisser werden jetzt sagen, das man leere Felder im Dropdownmenü ausblenden kann, aber das geht leider nicht, da in unserem Fall eine 0 als Platzhalter für die gepiegelten Daten der Reihe steht. Diese kann man durch eine Wennformel zwar in ein Leerzeichen umwandeln, aber das klappt leider nicht, da in der Formel die Option für einen anderen Platzhalter steht. Wem das zu kompliziert war, ist nicht so wichtig....

Wie dem auch sei, der Sternenhimmelstuermer denkt, dass das Listenfeld eine sinnvolle Ergänzung ist. Man könnte das Listenfeld bei einer Eweiterung über 477 Daten in einer Zelle wieder auf 2400 Einträge erweitern und dann auf alle Eingabefelder für Lebensmittel wieder kopieren. Das dauert bei 40 Mahlzeiteneingabefeldern (10 Tage a 4 Mahlzeiten) vielleicht bis zu einer halben Stunde (schnelles Kopieren mit Anfasser für einen Block, dann die Reihe des  nächsten Block, wieder runterkopieren...). Dabei kann nicht viel schief gehen. Dann die Reihe mit den gespiegelten Einträge verlängern - fertig).

Ach ja, ehe ich es vergesse, dass löschen von Listenfeldern geht nicht mit der Löschtaste. Die Microft Corperation beschreibt zwar einen Weg, aber der Sternenhimmelstuermer kopiert einfach lieber den Inhalt einer anderen leeren Zelle (mit farblich selben Hintergrund) in die erste Zelle mit Listenfeld. Danach mit dem kleinen Anfasser unten rechts in die anderen Zellen kopieren. Auf diese Weise werden Sie die Listenfelder effektiver los, der Sternenhimmelstuermer ist Pragmatiker - die neben immer die einfachste Lösung...

Fazit: Schnelle Veränderung und auch die letzte, um die User nicht weiter zu verwirren. Es wurden keine grundlegenden Änderungen gemacht. Rechnungen im Hintergrund werden nicht verändert.

Der Sternenhimmelstuermer wird wie gesagt die Datebank der Tabelle erweitern, was jedoch dauert...

Außerdem wird die Tabelle noch übersetzt und dann können die User auf die vollständige Datenbank der UDSA zugreifen...ca. 8000 Einträge...

 



 
 





Impressum
Datenschutz