WENN-Funktion – Geschachtelte Formeln und das Vermeiden von Fallgruben

WENN-Funktion – Geschachtelte Formeln und das Vermeiden von Fallgruben

Wichtig :  Dieser Artikel wurde maschinell übersetzt. Bitte beachten Sie den Haftungsausschluss. Die englische Version des Artikels ist als Referenz hier verfügbar: hier.

Die WENN-Funktion ermöglicht einen logischen Vergleich zwischen einem aktuellen Wert und einem erwarteten Wert. Dazu wird auf eine Bedingung geprüft und ein Ergebnis zurückgegeben, wenn der Wert wahr oder falsch ist.

  • WENN(ein Wert wahr ist, tue dieses, andernfalls tue etwas anderes)

Daher kann eine WENN-Anweisung zwei Ergebnisse haben. Das erste Ergebnis wird ausgegeben, wenn der Vergleich wahr ist, das zweite, wenn der Vergleich falsch ist.

WENN-Anweisungen sind unglaublich robust und bilden die Basis vieler Kalkulationstabellenmodelle, sind aber auch die Ursache vieler Probleme mit Kalkulationstabellen. Im Idealfall sollte eine WENN-Anweisung für minimale Bedingungen gelten (z. B. Männlich/Weiblich, Ja/Nein/Vielleicht, um nur einige zu nennen), doch müssen manchmal komplexere Szenarien ausgewertet werden, die das Schachteln* von mehr als 3 WENN-Funktionen erfordern.

* Als "Schachteln" wird das Zusammenführen mehrerer Funktionen in einer Formel bezeichnet.

Verwenden Sie die Funktion WENN, eine der logischen Funktionen, um einen Wert zurückzugeben, wenn eine Bedingung erfüllt ist, und ein anderen Wert, wenn die Bedingung nicht erfüllt ist.

Syntax

WENN(Prüfung;Dann_Wert;[Sonst_Wert])

Beispiel:

  • =WENN(A2>B2;"Budget überschritten";"OK")

  • =WENN(A2=B2;B4-A4;"")

Argumentname

Beschreibung

Prüfung   

(erforderlich)

Die zu prüfende Bedingung.

Dann_Wert   

(erforderlich)

Der Wert, der zurückgegeben werden soll, wenn das Ergebnis der Prüfung WAHR ist.

Sonst_Wert   

(optional)

Der Wert, der zurückgegeben werden soll, wenn das Ergebnis der Prüfung FALSCH ist.

Hinweise

Excel ermöglicht zwar das Schachteln von bis zu 64 unterschiedlichen WENN-Funktionen, dennoch ist das nicht ratsam. Warum?

  • Mehrere WENN-Anweisungen erfordern ein hohes Maß an Konzentration, um korrekt erstellt zu werden und um sicherzustellen, dass deren Logik über jede Bedingung bis zum Schluss ordnungsgemäß berechnet wird. Wird die Formel nicht zu 100 % korrekt geschachtelt, funktioniert sie möglicherweise nur zu 75 % und gibt in 25 % der Fälle unerwartete Ergebnisse zurück. Unglücklicherweise ist es fast unmöglich, diese problematischen 25 % abzufangen.

  • Mehrere WENN-Anweisungen sind ggf. unglaublich schwierig zu verwalten, besonders, wenn Sie später noch einmal darauf zurückkommen und versuchen herauszufinden, was Sie oder – noch schlimmer – eine andere Person hiermit erreichen wollten.

Wenn Sie eine WENN-Anweisung haben, die einfach immer weiter anzuwachsen scheint und bei der kein Ende in Sicht ist, sollten Sie die Maus aus der Hand legen und Ihre Strategie überdenken.

Sehen wir uns einmal an, wie eine komplexe geschachtelte WENN-Anweisung mit mehreren WENN-Funktionen ordnungsgemäß erstellt wird, und wann abzusehen ist, dass ein anderes Tool aus dem Excel-Arsenal zu verwenden ist.

Beispiele

Es folgt ein Beispiel für eine recht übliche geschachtelte WENN-Anweisung, mit der Testergebnisse von Schülern in die mit Buchstaben bezeichneten Einstufungen konvertiert werden.

Komplexe geschachtelte WENN-Anweisung – Die Formel in E2 lautet =WENN(B2>97;"A+":WENN(B2>93;"A";WENN(B2>89;"A-";WENN(B2>87;"B+";IF(B2>83;"B";WENN(B2>79;"B-";WENN(B2>77;"C+";WENN(B2>73;"C";WENN(B2>69;"C-";WENN(B2>57;"D+";WENN(B2>53;"D";WENN(B2>49;"D-";"F"))))))))))))
  • =WENN(D2>89;"A";WENN(D2>79;"B";WENN(D2>69;"C";WENN(D2>59;"D";"F"))))

    Diese komplexe geschachtelte WENN-Anweisung folgt einer einfachen Logik:

  1. Wenn das Testergebnis (in Zelle D2) höher als 89 ist, erhält der Schüler ein A.

  2. Wenn das Testergebnis höher als 79 ist, erhält der Schüler ein B.

  3. Wenn das Testergebnis höher als 69 ist, erhält der Schüler ein C.

  4. Wenn das Testergebnis höher als 59 ist, erhält der Schüler ein D.

  5. Andernfalls erhält der Schüler ein F.

Dies ist ein recht sicheres Beispiel, da es unwahrscheinlich ist, dass sich der Zusammenhang zwischen Testergebnissen und Einstufungen ändert, sodass keine aufwendige Verwaltung erforderlich ist. Doch was wäre, wenn die Einstufungen in A+, A und A- (usw.) unterteilt werden müssen? In diesem Fall muss die WENN-Anweisung für vier Bedingungen so umgeschrieben werden, dass sie 12 Bedingungen umfasst. Die Formel würde dann folgendermaßen aussehen:

  • =WENN(B2>97;"A+";WENN(B2>93;"A";WENN(B2>89;"A-";WENN(B2>87;"B+";WENN(B2>83;"B";WENN(B2>79;"B-";WENN(B2>77;"C+";WENN(B2>73;"C";WENN(B2>69;"C-";WENN(B2>57;"D+";WENN(B2>53;"D";WENN(B2>49;"D-";"F"))))))))))))

Die Formel funktioniert immer noch korrekt und erwartungsgemäß, doch nimmt das Schreiben und das Testen zur Sicherstellung der ordnungsgemäßen Ausführung mehr Zeit in Anspruch. Ein weiteres offensichtliches Problem ist, dass Sie die Punktzahlen und die entsprechenden Buchstaben von Hand eingeben müssten. Wie wahrscheinlich ist es, dass Sie versehentlich einen Tippfehler machen? Stellen Sie sich nun vor, Sie würden das bei noch komplexeren Bedingungen 64-mal machen! Natürlich ist das möglich, doch möchten Sie wirklich solchen Aufwand treiben und Fehler riskieren, die nur sehr schwer erkennen sind?

Tipp : Jede Funktion in Excel erfordert eine öffnende und eine schließende Klammer (). Excel unterstützt Sie beim Zusammenstellen der Formel, indem verschiedene Teile während der Bearbeitung farblich gekennzeichnet werden. Wenn Sie beispielsweise die oben angegebene Formel bearbeiten, wird beim Bewegen des Cursors hinter die einzelnen schließenden Klammern ")" die jeweils entsprechende öffnende Klammer in der gleichen Farbe angezeigt. Dies kann bei komplexen geschachtelten Formeln besonders hilfreich sein, wenn Sie herauszufinden versuchen, ob Sie über genügend Klammernpaare verfügen.

Weitere Beispiele

Es folgt ein typisches Beispiel der Berechnung einer Vertriebsprovision basierend auf der Umsatzhöhe.

Die Formel in Zelle D9 lautet WENN(C9>15000;20%;WENN(C9>12500;17,5%;WENN(C9>10000;15%;WENN(C9>7500;12,5%;WENN(C9>5000;10%;0)))))
  • =WENN(C9>15000;20%;WENN(C9>12500;17,5%;WENN(C9>10000;15%;WENN(C9>7500;12,5%;WENN(C9>5000;10%;0)))))

Diese Formel besagt Folgendes: WENN(C9 größer ist als 15.000, wird 20% zurückgegeben; WENN(C9 größer ist als 12.500, wird 17,5% zurückgegeben usw.

Obwohl dies dem vorherigen Beispiel mit Einstufungen sehr ähnlich ist, stellt diese Formel ein gutes Beispiel dafür dar, wie schwierig es sein kann, umfangreiche WENN-Anweisungen zu verwalten. Welche Arbeit wäre erforderlich, wenn Ihre Organisation sich entschließen würde, neue Vergütungsstufen einzuführen und möglicherweise auch noch die vorhandenen Euro- oder Prozentwerte zu ändern? Da hätten Sie ganz schön viel zu tun!

Tipp : Sie können in der Bearbeitungsleiste Zeilenumbrüche einfügen, damit lange Formeln leichter zu lesen sind. Drücken Sie einfach ALT+EINGABETASTE vor dem Text, den Sie in eine neue Zeile umbrechen möchten.

Es folgt ein Beispiel für das Provisionsszenario mit fehlerhafter Logik:

Die Formel in D9 ist fehlerhaft und lautet =WENN(C9>5000;10%;WENN(C9>7500;12,5%;WENN(C9>10000;15%;WENN(C9>12500;17,5%;WENN(C9>15000;20%;0)))))

Können Sie sehen, wo der Fehler liegt? Vergleichen Sie die Reihenfolge der Umsätze mit dem vorherigen Beispiel. Welche Reihenfolge liegt hier vor? Wie Sie sehen, verläuft die Reihenfolge vom niedrigsten zum höchsten Wert (von 5.000 € bis 15.000 €) und nicht umgekehrt. Aber warum ist das so eine große Sache? Der Grund ist, dass die Formel die erste Auswertung nicht für einen Wert über 5.000 € durchführen kann. Wenn Sie beispielsweise einen Umsatz von 12.500 € haben, gibt die WENN-Anweisung 10 % zurück, da der Wert größer als 5.000 € ist, und stoppt dann. Dies kann besonders problematisch sein, da diese Art von Fehlern in vielen Situationen so lange unbemerkt bleibt, bis sie eine negative Auswirkung hat. Sie wissen also, dass bei komplexen geschachtelten WENN-Anweisungen schwerwiegende Probleme auftreten können, doch was können Sie tun? In den meisten Fällen können Sie die Funktion SVERWEIS verwenden, statt eine komplexe Formel mit der WENN-Funktion zu erstellen. Für die Funktion SVERWEIS müssen Sie zuerst eine Bezugstabelle erstellen:

Die Formel in Zelle D2 lautet =SVERWEIS(C2;C5:D17;2;WAHR)
  • =SVERWEIS(C2;C5:D17;2;WAHR)

Diese Formel besagt, dass nach dem Wert in C2 im Bereich C5:C17 gesucht werden soll. Wird der Wert gefunden, wird der entsprechende Wert aus der gleichen Zeile in Spalte D zurückgegeben.

Die Formel in Zelle C9 lautet =SVERWEIS(B9;B2:C6;2;WAHR)
  • =SVERWEIS(B9;B2:C6;2;WAHR)

Bei dieser Formel wird nach dem Wert in Zelle B9 im Bereich B2:B22 gesucht. Wird der Wert gefunden, wird der entsprechende Wert aus der gleichen Zeile in Spalte C zurückgegeben.

Hinweis : Bei beiden SVERWEIS-Funktionen wird das Argument WAHR am Ende der Formel verwendet. Das bedeutet, dass nach einer ungefähren Entsprechung gesucht werden soll. In anderen Worten: Es wird nach den genauen Werten in der Nachschlagetabelle gesucht sowie nach allen Werten, die dazwischen liegen. In diesem Fall müssen die Nachschlagetabellen in aufsteigender Reihenfolge vom kleinsten zum größten Wert sortiert werden.

SVERWEIS wird hier ausführlicher behandelt, doch ist dies bestimmt wesentlich einfacher, als eine komplexe verschachtelte WENN-Anweisung mit 12 Ebenen zu erstellen! Es gibt auch noch andere, weniger offensichtliche Vorteile:

  • SVERWEIS-Bezugstabellen stehen offen bereit und sind leicht zu sehen.

  • Tabellenwerte können problemlos aktualisiert werden, und Sie müssen die Formel nicht bearbeiten, wenn sich die Bedingungen ändern.

  • Wenn Sie nicht möchten, dass andere Personen die Bezugstabelle sehen oder ändern können, speichern Sie sie einfach in einem anderen Arbeitsblatt.

Wussten Sie schon?

Es gibt jetzt eine WENNS-Funktion, die mehrere geschachtelte WENN-Anweisung durch eine einzelne Funktion ersetzen kann. Das anfängliche Beispiel mit den Einstufungen, bei dem vier geschachtelte WENN-Funktionen verwendet werden:

  • =WENN(D2>89;"A";WENN(D2>79;"B";WENN(D2>69;"C";WENN(D2>59;"D";"F"))))

kann durch eine einzelne WENNS-Funktion wesentlich vereinfacht werden:

  • =WENNS(D2>89;"A";D2>79;"B";D2>69;"C";D2>59;"D";WAHR;"F")

Der Vorteil der WENNS-Funktion besteht darin, dass Sie sich keine Gedanken über alle diese WENN-Anweisungen und Klammern machen müssen.

Wenn Sie nicht über ein Office 365-Abonnement oder Excel 2016 oder höher verfügen, können Sie eine der kostenlosen Testversionen ausprobieren!

Testen Sie Office 365 oder die aktuelle Version von Excel

Haben Sie eine Frage zu einer bestimmten Funktion?

Bereitstellen einer Frage in das Excel-Community-forum

Helfen Sie uns, Excel zu verbessern

Haben Sie Vorschläge zu wie die nächste Version von Excel verbessert werden können? In diesem Fall Bitte lesen Sie die Themen am Excel Benutzer VoIP.

Hinweis : Haftungsausschluss für maschinelle Übersetzungen: Dieser Artikel wurde mithilfe eines Computersystems und ohne jegliche Bearbeitung durch Personen übersetzt. Microsoft bietet solche maschinellen Übersetzungen als Hilfestellung für Benutzer ohne Englischkenntnisse an, damit Sie von den Informationen zu Produkten, Diensten und Technologien von Microsoft profitieren können. Da es sich bei diesem Artikel um eine maschinelle Übersetzung handelt, enthält er möglicherweise Fehler in Bezug auf (Fach-)Terminologie, Syntax und/oder Grammatik.

Siehe auch

Video zum Verwenden der WENN-Funktion

WENNS-Funktion (Office 365, Excel 2016 und höher)

Die Funktion ZÄHLENWENN zählt Werte basierend auf einem einzigen Kriterium

Die Funktion ZÄHLENWENNS zählt Werte basierend auf mehreren Kriterien

Die Funktion SUMMEWENN addiert Werte basierend auf einem einzigen Kriterium

Die Funktion SUMMEWENNS addiert Werte basierend auf mehreren Kriterien

UND (Funktion)

ODER (Funktion)

SVERWEIS-Funktion

Übersicht über Formeln in Excel

Vermeiden defekter Formeln

Beheben häufig auftretender Fehler in Formeln mit der Fehlerüberprüfung

Logische Funktionen

Excel-Funktionen (alphabetisch)

Excel-Funktionen (nach Kategorie)

Teilen Facebook Facebook Twitter Twitter E-Mail E-Mail

War diese Information hilfreich?

Sehr gut. Noch anderes Feedback?

Was können wir verbessern?

Vielen Dank für Ihr Feedback!

×