19 Tipps für verschachtelte IF-Formeln

Die WENN-Funktion ist eine der am häufigsten verwendeten Funktionen in Excel. IF ist eine einfache Funktion, und die Leute lieben IF, weil es ihnen die Möglichkeit gibt, Excel zu erstellen Antworten wie Informationen in eine Tabelle eingegeben werden. Mit IF können Sie Ihre Tabellenkalkulation zum Leben erwecken.

Aber ein IF führt oft zu einem anderen, und sobald Sie mehr als ein paar IFs kombinieren, können Ihre Formeln wie kleine Frankensteins aussehen :)

Sind verschachtelte IFs böse? Sind sie manchmal notwendig? Was sind die Alternativen?

Lesen Sie weiter, um die Antworten auf diese Fragen und mehr zu erfahren...

1. Basis-IF

Bevor wir über verschachtelte IF sprechen, schauen wir uns kurz die grundlegende IF-Struktur an:

 
= IF (test,[true],[false])

Die IF-Funktion führt einen Test aus und führt verschiedene Aktionen aus, je nachdem, ob das Ergebnis wahr oder falsch ist.

Beachten Sie die eckigen Klammern ... diese bedeuten, dass die Argumente optional sind. Sie müssen jedoch liefern entweder ein Wert für wahr oder ein Wert für falsch.

Zur Veranschaulichung verwenden wir hier IF, um die Punktzahl zu überprüfen und 'Bestanden' für Punktzahlen von mindestens 65 zu berechnen:

Zelle D3 im Beispiel enthält diese Formel:

 
= IF (C3>=65,'Pass')

Was so gelesen werden kann: Wenn die Punktzahl in C3 mindestens 65 beträgt, geben Sie 'Pass' zurück.

Beachten Sie jedoch, dass bei einem Score weniger als 65, IF gibt FALSE zurück, da wir bei false keinen Wert angegeben haben. Um 'Fail' für nicht bestandene Ergebnisse anzuzeigen, können wir 'Fail' als das falsche Argument wie folgt hinzufügen:

 
= IF (C3>=65,'Pass','Fail')

Video: So erstellen Sie logische Formeln .

2. Was Verschachtelung bedeutet

Verschachteln bedeutet einfach, Formeln ineinander zu kombinieren, sodass eine Formel das Ergebnis einer anderen verarbeitet. Hier ist beispielsweise eine Formel, bei der die Funktion HEUTE in der Funktion MONAT verschachtelt ist:

 
= MONTH ( TODAY ())

Die Funktion HEUTE gibt das aktuelle Datum innerhalb der Funktion MONAT zurück. Die Funktion MONTH nimmt dieses Datum und gibt den aktuellen Monat zurück. Selbst mäßig komplexe Formeln verwenden häufig Verschachtelungen, sodass Sie in komplexeren Formeln überall Verschachtelungen sehen.

3. Ein einfaches verschachteltes IF

Ein verschachteltes IF sind nur zwei weitere IF-Anweisungen in einer Formel, wobei eine IF-Anweisung in der anderen erscheint.

Zur Veranschaulichung habe ich unten die ursprüngliche Pass/Fail-Formel erweitert, um 'unvollständige' Ergebnisse zu verarbeiten, indem ich eine IF-Funktion hinzugefügt und eine IF in die andere verschachtelt habe:

 
= IF (C3='','Incomplete', IF (C3>=65,'Pass','Fail'))

Die äußere IF wird zuerst ausgeführt und überprüft, ob C3 leer ist. Wenn dies der Fall ist, gibt das äußere IF 'Unvollständig' zurück und das innere IF wird nie ausgeführt.

Wenn die Punktzahl ist nicht leer , gibt das äußere IF FALSE zurück und die ursprüngliche IF-Funktion wird ausgeführt.

4. Ein verschachteltes IF für Waagen

Sie werden oft verschachtelte IFs sehen, die so eingerichtet sind, dass sie 'Skalen' verarbeiten, z. B. um Noten, Versandkosten, Steuersätze oder andere Werte zuzuweisen, die auf einer Skala mit einer numerischen Eingabe variieren. Solange es nicht zu viele Ebenen in der Skala gibt, funktionieren verschachtelte IFs hier gut, aber Sie müssen die Formel organisiert halten, sonst wird sie schwer zu lesen.

Der Trick besteht darin, eine Richtung zu entscheiden (hoch zu niedrig oder niedrig zu hoch) und dann die Bedingungen entsprechend zu strukturieren. Um beispielsweise Noten in einer Reihenfolge von „niedrig nach hoch“ zu vergeben, können wir die benötigte Lösung in der folgenden Tabelle darstellen. Beachten Sie, dass es keine Bedingung für 'A' gibt, denn nachdem wir alle anderen Bedingungen durchlaufen haben, wissen wir, dass die Punktzahl größer als 95 sein muss und daher ein 'A' ist.

Wenn die Bedingungen klar verstanden sind, können wir die erste IF-Anweisung eingeben:

Welche der folgenden Optionen wird verwendet, um benachbarte Blätter auszuwählen?

 
= IF (C5<64,'F')

Dies kümmert sich um 'F'. Um nun 'D' zu behandeln, müssen wir eine weitere Bedingung hinzufügen:

 
= IF (C5<64,'F', IF (C5<73,'D'))

Beachten Sie, dass ich für das 'falsche' Ergebnis einfach eine andere IF in die erste IF eingefügt habe. Um die Formel um 'C' zu erweitern, wiederholen wir den Vorgang:

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C')))

Wir fahren auf diesem Weg fort, bis wir die letzte Klasse erreichen. Dann, anstatt ein weiteres IF hinzuzufügen, fügen Sie einfach die Endnote für falsch hinzu.

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C', IF (C5<95,'B','A'))))

Hier ist die letzte verschachtelte IF-Formel in Aktion:

Video: So erstellen Sie ein verschachteltes IF, um Noten zuzuweisen

5. Verschachtelte IFs haben einen logischen Fluss

Viele Formeln werden von innen nach außen gelöst, da zuerst 'innere' Funktionen oder Ausdrücke gelöst werden müssen, damit der Rest der Formel fortfahren kann.

Verschachtelte IFs haben einen eigenen logischen Fluss, da die 'äußeren' IFs wie ein Tor zu 'inneren' IFs fungieren. Das bedeutet, dass Ergebnisse von äußeren IFs bestimmen, ob innere IFs überhaupt laufen. Das Diagramm unten visualisiert den logischen Ablauf der obigen Notenformel.

6. Verwenden Sie Auswerten, um den logischen Fluss zu beobachten

Unter Windows können Sie die Funktion bewerten um zu sehen, wie Excel Ihre Formeln Schritt für Schritt löst. Dies ist eine großartige Möglichkeit, den logischen Ablauf komplexerer Formeln zu 'sehen' und Fehler zu beheben, wenn Dinge nicht wie erwartet funktionieren. Der folgende Bildschirm zeigt das geöffnete und einsatzbereite Fenster Auswerten. Jedes Mal, wenn Sie auf die Schaltfläche Auswerten klicken, wird der „nächste Schritt“ in der Formel gelöst. Sie finden Auswerten auf der Registerkarte Formeln des Menübands (Alt M, V).

Leider enthält die Mac-Version von Excel die Evaluate-Funktion nicht, aber Sie können trotzdem den F9-Trick unten verwenden.

7. Verwenden Sie F9, um die Ergebnisse stichprobenartig zu überprüfen

Wenn Sie einen Ausdruck in der Bearbeitungsleiste auswählen und die Taste F9 drücken, löst Excel nur den ausgewählten Teil. Dies ist eine leistungsstarke Methode, um zu bestätigen, was eine Formel wirklich tut. Im folgenden Bildschirm verwende ich die Bildschirmtipp-Fenster, um verschiedene Teile der Formel auszuwählen, und klicke dann auf F9, um diesen Teil gelöst zu sehen:

Verwenden Sie Strg + Z (Befehl + Z) auf einem Mac, um F9 rückgängig zu machen. Sie können auch Esc drücken, um den Formeleditor ohne Änderungen zu verlassen.

Video: So debuggen Sie eine Formel mit F9

8. Kenne deine Grenzen

Excel hat Grenzen, wie tief Sie IF-Funktionen verschachteln können. Bis Excel 2007 erlaubte Excel bis zu 7 Ebenen von verschachtelten IFs. In Excel 2007+ erlaubt Excel bis zu 64 Ebenen.

Aber nur weil du kann Verschachteln Sie viele IFs, das bedeutet nicht Sie sollen . Jede zusätzliche Ebene, die Sie hinzufügen, macht es schwieriger, die Formel zu verstehen und Fehler zu beheben. Wenn Sie feststellen, dass Sie mit einem verschachtelten IF arbeiten, das mehr als ein paar Ebenen tief ist, sollten Sie wahrscheinlich einen anderen Ansatz wählen – siehe unten für Alternativen.

9. Passen Sie Klammern wie ein Profi an

Eine der Herausforderungen bei verschachtelten IFs besteht darin, Klammern abzugleichen oder auszugleichen. Wenn Klammern nicht richtig übereinstimmen, ist Ihre Formel fehlerhaft. Glücklicherweise bietet Excel einige Tools, die Ihnen dabei helfen, sicherzustellen, dass Klammern beim Bearbeiten von Formeln 'ausgewogen' sind.

Sobald Sie mehr als einen Satz Klammern haben, werden die Klammern farbcodiert, sodass öffnende Klammern mit schließenden Klammern übereinstimmen. Diese Farben sind verdammt schwer zu erkennen, aber sie sind da, wenn Sie genau hinsehen:

Zweitens (und besser), wenn Sie eine Klammer schließen, wird Excel das passende Paar kurz fett formatieren. Sie können auch in die Formel klicken und die Pfeiltaste verwenden, um durch die Klammern zu navigieren, und Excel wird beide Klammern kurz fett formatieren, wenn ein übereinstimmendes Paar vorhanden ist. Wenn es keine Übereinstimmung gibt, wird keine Fettschrift angezeigt.

Leider ist die Fettdruck nur eine Windows-Funktion. Wenn Sie Excel auf einem Mac verwenden, um komplexe Formeln zu bearbeiten, ist es manchmal sinnvoll, die Formel zu kopieren und in einen guten Texteditor ( Textwrangler ist kostenlos und ausgezeichnet), um bessere Tools zum Abgleichen von Klammern zu erhalten. Text Wrangler blinkt, wenn Klammern übereinstimmen, und Sie können Befehl + B verwenden, um den gesamten Text auszuwählen, der in Klammern enthalten ist. Sie können die Formel wieder in Excel einfügen, nachdem Sie die Dinge geklärt haben.

10. Verwenden Sie das Bildschirmtipp-Fenster, um zu navigieren und auszuwählen

Wenn es um das Navigieren und Bearbeiten von verschachtelten IFs geht, ist der Funktions-Screen-Tipp Ihr ​​bester Freund. Damit können Sie alle Argumente in einem verschachtelten IF navigieren und präzise auswählen:

Sie können sehen, wie ich das Bildschirmtipp-Fenster in diesem Video häufig verwende: So erstellen Sie ein verschachteltes IF .

11. Passen Sie auf Text und Zahlen auf

Zur Erinnerung: Achten Sie bei der Arbeit mit der IF-Funktion darauf, dass Zahlen und Text richtig übereinstimmen. Ich sehe oft Formeln, WENN wie folgt:

 
= IF (A1='100','Pass','Fail')

Ist das Testergebnis in A1 Ja wirklich Text und keine Zahl? Nein? Verwenden Sie dann keine Anführungszeichen um die Zahl. Andernfalls gibt der logische Test FALSE zurück, auch wenn der Wert eine bestandene Punktzahl ist, da '100' nicht mit 100 identisch ist. Wenn die Testpunktzahl numerisch ist, verwenden Sie Folgendes:

 
= IF (A1=100,'Pass','Fail')

12. Fügen Sie Zeilenumbrüche hinzu, damit verschachtelte IFs leicht lesbar sind

Wenn Sie mit einer Formel arbeiten, die viele Ebenen verschachtelter IFs enthält, kann es schwierig sein, die Übersicht zu behalten. Da Excel sich nicht um 'Leerzeichen' in Formeln (d. h. zusätzliche Leerzeichen oder Zeilenumbrüche) kümmert, können Sie die Lesbarkeit von verschachtelten Wenns erheblich verbessern, indem Sie Zeilenumbrüche hinzufügen.

Der folgende Bildschirm zeigt beispielsweise eine verschachtelte IF, die einen Provisionssatz basierend auf einer Verkaufsnummer berechnet. Hier sieht man die typische verschachtelte IF-Struktur, die schwer zu entziffern ist:

Wenn ich jedoch vor jedem 'Wert wenn falsch' Zeilenumbrüche einfüge, springt die Logik der Formel deutlich heraus. Außerdem ist die Formel einfacher zu bearbeiten:

Sie können Zeilenumbrüche unter Windows mit Alt + Enter hinzufügen, auf einem Mac verwenden Sie Strg + Wahl + Return.

Video: So machen Sie ein verschachteltes IF leichter lesbar .

13. Begrenzen Sie IFs mit UND und ODER

Verschachtelte IFs sind leistungsstark, werden jedoch schnell kompliziert, wenn Sie weitere Ebenen hinzufügen. Eine Möglichkeit, mehr Ebenen zu vermeiden, besteht darin, IF in Kombination mit den UND- und ODER-Funktionen zu verwenden. Diese Funktionen geben ein einfaches TRUE/FALSE-Ergebnis zurück, das innerhalb von IF perfekt funktioniert, sodass Sie sie verwenden können, um die Logik eines einzelnen IF zu erweitern.

In der folgenden Aufgabe möchten wir beispielsweise ein 'x' in Spalte D einfügen, um Zeilen zu markieren, in denen die Farbe 'rot' und die Größe 'klein' ist.

Wir könnten die Formel mit zwei verschachtelten IFs wie folgt schreiben:

 
= IF (B6='red', IF (C6='small','x',''),'')

Indem wir jedoch den Test durch die UND-Funktion ersetzen, können wir die Formel vereinfachen:

 
= IF ( AND (B6='red',C6='small'),'x','')

Auf die gleiche Weise können wir diese Formel einfach um die ODER-Funktion erweitern, um nach Rot ODER Blau UND Klein zu suchen:

 
= IF ( AND ( OR (B4='red',B4='blue'),C4='small'),'x','')

Alles davon könnten mit verschachtelten IFs gemacht werden, aber die Formel würde schnell komplexer werden.

Video: WENN dies ODER das

14. Ersetzen Sie verschachtelte IFs durch SVERWEIS

Wenn eine verschachtelte IF einfach Werte basierend auf einer einzelnen Eingabe zuweist, kann sie leicht durch die . ersetzt werden SVERWEIS-Funktion . Diese verschachtelte IF weist beispielsweise Zahlen fünf verschiedenen Farben zu:

 
= IF (E3='red',100, IF (E3='blue',200, IF (E3='green',300, IF (E3='orange',400,500))))

Wir können es leicht durch dieses (viel einfachere) SVERWEIS ersetzen:

 
= VLOOKUP (E3,B3:C7,2,0)

Als Bonus behält SVERWEIS Werte auf dem Arbeitsblatt (wo sie leicht geändert werden können), anstatt sie in die Formel einzubetten.

Obwohl die obige Formel eine exakte Übereinstimmung verwendet, können Sie sie ganz einfach verwenden SVERWEIS für Noten sowie.

Siehe auch: 23 Wissenswertes über SVERWEIS

Video: So verwenden Sie SVERWEIS

Video: Warum SVERWEIS besser ist als verschachtelte IFs

15. Wählen Sie WÄHLEN

Die CHOOSE-Funktion kann eine elegante Lösung bieten, wenn Sie einfache, fortlaufende Zahlen (1, 2, 3 usw.) auf beliebige Werte abbilden müssen.

Im folgenden Beispiel wird CHOOSE verwendet, um benutzerdefinierte Abkürzungen für Wochentage zu erstellen:

Klar, du könnten Verwenden Sie ein langes und kompliziertes verschachteltes IF, um dasselbe zu tun, aber bitte nicht :)

16. Verwenden Sie IFS anstelle von verschachtelten IFs

Wenn Sie Excel 2016 über Office 365 verwenden, gibt es eine neue Funktion, die Sie anstelle von geschachtelten IFs verwenden können: die IFS-Funktion. Die IFS-Funktion bietet eine spezielle Struktur zur Auswertung mehrerer Bedingungen ohne Verschachtelung:

Die oben verwendete Formel sieht so aus:

 
= IFS (D5<60,'F',D5<70,'D',D5<80,'C',D5<90,'B',D5>=90,'A')

Beachten Sie, dass wir nur ein einziges Paar Klammern haben!

Was passiert, wenn Sie eine Tabelle öffnen, die die IFS-Funktion in einer älteren Excel-Version verwendet? In Excel 2013 und 2010 (und ich glaube Excel 2007, kann es aber nicht testen) sehen Sie '_xlfn'. an den IFS in der Zelle angehängt. Der zuvor berechnete Wert ist weiterhin vorhanden, aber wenn die Formel durch etwas neu berechnet wird, wird ein #NAME-Fehler angezeigt. Microsoft hat mehr Infos hier .

17. Max raus

Manchmal können Sie MAX oder MIN auf sehr clevere Weise verwenden, um eine IF-Anweisung zu vermeiden. Angenommen, Sie haben eine Berechnung, die zu einer positiven Zahl oder Null führen muss. Mit anderen Worten, wenn die Berechnung eine negative Zahl zurückgibt, möchten Sie nur Null anzeigen.

Die MAX-Funktion bietet Ihnen eine clevere Möglichkeit, dies zu tun, ohne dass irgendwo ein ZF in Sicht ist:

 
= MAX (calculation,0)

Dieses Verfahren gibt das Ergebnis der Berechnung zurück, wenn es positiv ist, andernfalls Null.

Ich liebe diese Konstruktion, weil sie ganz einfach . In diesem Artikel finden Sie eine vollständige Beschreibung .

18. Trap-Fehler mit IFERROR

Eine klassische Verwendung von IF besteht darin, Fehler abzufangen und ein anderes Ergebnis bereitzustellen, wenn ein Fehler ausgelöst wird, wie folgt:

 
= IF ( ISERROR (formula),error_result,formula)

Dies ist hässlich und überflüssig, da dieselbe Formel zweimal eingeht und Excel das gleiche Ergebnis zweimal berechnen muss, wenn kein Fehler vorliegt.

In Excel 2007 wurde die IFERROR-Funktion eingeführt, mit der Sie Fehler viel eleganter abfangen können:

 
= IFERROR (formula,error_result)

Wenn die Formel nun einen Fehler ausgibt, gibt IFERROR einfach den von Ihnen angegebenen Wert zurück.

19. Verwenden Sie boolesche Logik

Manchmal können Sie auch verschachtelte IFs vermeiden, indem Sie die sogenannte 'boolesche Logik' verwenden. Das Wort Boolean bezieht sich auf TRUE/FALSE-Werte. Obwohl Excel die Wörter TRUE und FALSE in Zellen anzeigt, behandelt Excel intern TRUE als 1 und FALSE als Null. Sie können diese Tatsache nutzen, um clevere und sehr schnelle Formeln zu schreiben. Im obigen SVERWEIS-Beispiel haben wir beispielsweise eine verschachtelte IF-Formel, die wie folgt aussieht:

 
= IF (E3='red',100, IF (E3='blue',200, IF (E3='green',300, IF (E3='orange',400,500))))

Mit boolescher Logik können Sie die Formel wie folgt umschreiben:

 
=(E3='red')*100+(E3='blue')*200+(E3='green')*300+(E3='orange')*400+(E3='purple')*500

Jeder Ausdruck führt einen Test durch und multipliziert dann das Ergebnis des Tests mit dem 'Wert, wenn wahr'. Da Tests entweder TRUE oder FALSE (1 oder 0) zurückgeben, heben die FALSE-Ergebnisse die Formel effektiv auf.

Für numerische Ergebnisse ist die boolesche Logik einfach und extrem schnell, da es keine Verzweigung gibt. Auf der anderen Seite kann boolesche Logik für Leute verwirrend sein, die nicht daran gewöhnt sind, sie zu sehen. Trotzdem ist es eine großartige Technik, die man kennen sollte.

Video: So verwenden Sie boolesche Logik in Excel-Formeln

Wann benötigen Sie ein verschachteltes IF?

Bei all diesen Möglichkeiten, verschachtelte IFs zu vermeiden, fragen Sie sich vielleicht, wann es sinnvoll ist, ein verschachteltes IF zu verwenden?

Ich denke, verschachtelte IFs sind sinnvoll, wenn Sie auswerten müssen mehrere verschiedene Eingänge eine Entscheidung treffen.

Angenommen, Sie möchten den Rechnungsstatus 'Bezahlt', 'Offen', 'Überfällig' usw. berechnen. Dazu müssen Sie sich das Rechnungsalter ansehen und offener Betrag:

In diesem Fall ist eine verschachtelte IF eine perfekte Lösung.

Ihre Gedanken?

Was ist mit dir? Sind Sie ein IF-ster? Vermeiden Sie verschachtelte IFs? Sind verschachtelte IFs böse? Teilen Sie Ihre Gedanken unten mit.