Frage von LG239, 56

Tabellenkalkulations-Hilfe (WENN-,SONST-,UND-Funktionen)?

Hallo an alle Tabellenkalkulations-Cracks ;), Ich versuche eine tabellenkalkulation (egal welches programm) zu erstellen, weiß aber überhaupt nicht wie das funktioniert :|. Ich habs schon gefühlte 1000 mal probiert, aber ich bekomme es einfach nicht hin. Die Tabellenkalkulation die ich erstellen möchte geht um berechnung und anschließender früfung von BMI's. Sie soll so aufgebaut sein, wie im bild unten zu sehen. In Spalte B muss der BMI beim eingeben von Körpermasse und Körpergröße erscheinen. Die formel dafür habe ich schon. (B1/(B2*B2)). Bloß jetzt muss das noch alles in Abhängingkeit vom Alter auf Untergewicht, Übergewicht oder "wünschenswert"(also passender BMI). Die wünschenswerten BMI Werte in Abhängigkeit vom Alter sind wie unten. Großes Danke an alle die sich die mühe machen eine Funktion für mich zu machen :) LG

Antwort
von Havenari, 33

Man kann das mit (geschachtelten) WENN-Funktionen machen - das ist grausig viel Aufwand und völlig unübersichtlich. Macht man dabei irgendeinen Fehler, wird es äußerst mühselig, ihn zu korrigieren und zu beheben.

Sinnvoller erscheint es mir, das mit einem SVERWEIS zu machen. Dazu legst du ein weiteres Tabellenblatt an, in dem du in den Zeilen das Alter angibst und in den Spalten den BMI. Dann gibst du in den Feldern der dadurch festgelegten Matrix an, ob der jeweilige BMI für das jeweilige Alter ok ist oder unter- bzw. übergewichtig.

SVERWEIS sucht dann in der ersten Spalte nach dem Alter. Wenn der passende Wert gefunden wird, gibt die Funktion den Wert aus der n-ten Spalte aus. Für n definierst du in irgendeiner Zelle =[Alter-x], je nachdem, wie du deine Tabelle angelegt hast.

In meinem Beispiel habe ich das in LibreOffice ausprobiert. Bei Excel oder anderen Programmen mag die Syntax etwas anders aussehen, müsste aber ähnlich sein. Die Funktion SVERWEIS sucht sich den in Zelle B13 eingegebenen Wert (der natürlich auch aus deiner ursprünglichen Tabelle abgefragt werden kann) und sucht dann innerhalb der Matrix A3:Q9 den Wert der n-ten Spalte. n habe ich in der Zelle B15 definiert, das ist einfach der BMI (aus Zelle B12) minus 13 (weil Alter 15 in der zweiten Spalte steht usw).

Das Bild zeigt nur eine kleine Tabelle, die ich zur Veranschaulichung angelegt habe. Du kannst das natürlich beliebig erweitern. Die eigentliche Funktion, der SVERWEIS, bleibt davon unberührt - nur die dort definierte Matrix musst du ggf. anpassen.

Für Irrtümer übernehme ich weder Gewehr noch Pistole. Ich wollte hier einen Weg aufzeigen, solche Aufgaben einfacher zu lösen, auch wenn man sich dazu zunächst mit einer ziemlich kompliziert aussehenden Funktion plagen muss. Es lohnt den Aufwand - wenn man bei der WENN-Funktion an die zehnte Schachtelungsebene gelangt, ist nämlich Feierabend (falls bis dahin nicht bereits die Jungs mit den weißen Kitteln angerückt sind).

Kommentar von LG239 ,

Hey danke :), das ist mir bewusst das es mühselig und aufwändig ist aber leider muss ich das machen :/, eben genau mit den verschachtelten funktionen. Wäre echt der wahnsinn wenn du das für mich machen könntest! Aber wenn es dir zu viel aufwand ist, ist es egal. LG :)

Kommentar von Ninombre ,

aber leider muss ich das machen

dann tu das doch mal, anstatt hier wirklich gute Hilfestellungen wegzuwischen und auf dreiste Art zu fordern, dass andere die Arbeit für Dich erledigen sollen. 

Kommentar von Iamiam ,

@Havenari: xl erlaubt inzwischen viel mehr Verschachtelungsebenen (vermutlich 64?), und ich nehme an, dass LO nachgezogen hat, auch wenn das kein Mensch mehr überblicken kann (dabei hat LO die eindeutig bessere Ebenen-Darstellung, aber bei derartigen Komplexen nützt das dann auch nichts mehr!)

Kommentar von Iamiam ,

ich hab in B1 "Alter bis" und in B2:B8 die Zahlen 19; 24; 34; 44; 54; 64; 100 stehen,
in C1 BMI Ugr, C2:C8: 0; 19; 20; 21; 22; 23; 24,
in D1: BMI Ogr, D2:D8: 0; 24; 25; 26; 27; 28; 29
Das Alter wird in E1 eingetragen, das Gewicht in E3
in F3 die Untergrenze fürs Alter
=WAHL((E1>B2)+(E1>B3)+(E1>B4)+(E1>B5)+(E1>B6)+(E1>B7)+(E1>B8);C3;C4;C5;C6;C7;C8)
in G3 die Obergrenze fürs Alter:
=WAHL((E1◄B3)+(E1◄B4)+(E1◄B5)+(E1◄B6)+(E1◄B7)+(E1◄B8);D8;D7;D6;D5;D4;D3
dann i'wo die Alibi-Wennformel:
=WENN(E3◄F3;"zu leicht";WENN(E3>G3;"zu schwer";"i.O."))

Anm: es ist ein ständiges Ärgernis, dass der GF-Editor blockiert, wenn man < -haltige Texte/Formeln aus der Zwischenablage einfügen will. also alle ◄ durch < rückersetzen (mach ich jetzt nicht mehr, ist mir zu spät geworden)

Ich bringe diese Formel nur deshalb, um zu zeigen, dass man nicht alles miit dem SVerweis machen muss/kann und dass es zT wesentlich bessere Alternativen gibt.

Mit Wenn-Formeln sehe ich auch kein Land!

Kommentar von Iamiam ,

@ Ninombre: Na ja, er/sie weiß sich offenbar nicht mehr zu helfen und klammert sich an den Strohhalm, dass das ein anderer für ihn/sie macht - dreist würde ich das nicht nennen (es sei denn, das wäre eine Masche, dann schon).

Kommentar von Iamiam ,

ach so, ich hab die Langformel vergessen, die die Hilfszellen F3 und G3 nicht braucht(und damit gar keine Hilfszellen)::

=WENN(E3◄WAHL((E1>B2)+(E1>B3)+(E1>B4)+(E1>B5)+(E1>B6)+(E1>B7)+(E1>B8);C3;C4;C5;C6;C7;C8);"zu leicht";WENN(E3>WAHL((E1◄B3)+(E1◄B4)+(E1◄B5)+(E1◄B6)+(E1◄B7)+(E1◄B8);D8;D7;D6;D5;D4;D3);"zu schwer";"i.O."))

Expertenantwort
von Ninombre, Community-Experte für Excel, 33

Ist die Anforderung tatsächlich, wortwörtlich, dass nur eine Wenn-Formel verwendet werden soll? Da man sowohl beim Alter, als auch beim BMI nicht nur einen festen Wert, sondern einen Wertebereich prüfen muss, macht man sich mit einer verschachtelten Wenn-Formel ja tot.

Es gibt noch andere Funktionen zur Variante von Oubyi, aber das Prinzip wäre das gleiche: Erstmal die vom Alter abhängige Ober- und Untergrenze ermitteln und dann den Abgleich mit dem tatsächlichen BMI. Da steckt schon eine Wenn-Formel drin, die sollte man aber wirklich nicht für alles nutzen.

Nur mit einer Wenn-Formel brauchst Du alleine für die erste Altersgruppe:

=WENN(UND(B4<25;B3>=19;B3<=24);"OK";WENN(UND(B4<25;B3>24);"Übergewicht";WENN(UND(B4<25;B3<19);"Untergewicht";"")))

Kommentar von LG239 ,

Danke. An sich hast du verstanden was ich will aber es soll entschieden werden ob unter oder übergewicht bzw ein wünschenswerter bmi anhand des alters zutrifft.

Kommentar von Ninombre ,

Das ist schön, dass ich wohl kapiert habe, was Du willst und weniger schön, dass DU nicht kapiert hast, dass ich genau dafür die Formel erstellt habe - für die Altersgruppe bis 24. Das könntest Du dann entsprechend für die weiteren Altersgruppen fortführen. Betonung: DU, denn sorry, aber ich bin doch nicht Dein Depp, der das zusammenstückelt.

Kommentar von Iamiam ,

...macht man sich mit einer verschachtelten Wenn-Formel ja tot...:

ich nehme an, dass genau das bewusst gemacht werden soll, um die Akzeptanz fürs Erlernen anderer Formeln zu erreichen?

Oder es ist eine Übung für hochverschachtelte Wenn-Formeln, dann wäre es aber arg hochgestochen!

Expertenantwort
von Oubyi, Community-Experte für Excel, 41

Am besten machst Du Dir dazu ein Liste, wie in meinem Screenshot im Bereich A10:C16.
Dann ist die Formel für B5:

=WENN(B3<VERWEIS(B4;$A$11:$A$16;$B$11:$B$16);"Untergewicht";WENN(B3>VERWEIS(B4;$A$11:$A$16;$C$11:$C$16);"Übergewicht";"wünschenswert"))

Teste das mal genau.
Klappt es?

Kommentar von LG239 ,

Hi, Danke für die Arbeit! Leider funktioniert es bei mir nicht. Fehler bei prüfung; #NV, aber ist es auch möglich das ganze nur mit wenn und dann sonst funktionen zu machen? Und ohne die $ zeichen?

Kommentar von Oubyi ,

Wie Du im Screenshot siehst, klappt es bei mir, also hast Du wohl etwas falsch gemacht.
Man kann das natürlich auch nur mit WENN usw machen, dann werden aus den gut zwei Zeilen Formel allerdings geschätzte 20 Zeilen. Kannst es gerne versuchen.
Wenn Du die Formel nur in B5 brauchst, kannst Du die absoluten Bezüge ($) auch gerne weglassen:

=WENN(B3<VERWEIS(B4;A11:A16;B11:B16);"Untergewicht";WENN(B3>VERWEIS(B4;A11:A16;C11:C16);"Übergewicht";"wünschenswert"))

Keine passende Antwort gefunden?

Fragen Sie die Community