fx | =WENN(ODER(B5=0;B6=0);"Bitte Wert eingeben";B6/(B5*B5)) | |||
A | B | C | D | |
5 | Körpergröße | 1,74 | m | |
6 | Gewicht | 80 | Kg |
|
7 | Lebensalter | 41 | Jahre | |
8 | Index | 26,42 =WENN(ODER(B5=0;B6=0);"Bitte Wert eingeben";B6/(B5*B5)) | ||
9 | Auswertung | Prä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! |