Verschachtelte IF() in Excel: Eine schnelle Lösung für viele Bedingungen

Verschachtelte WENN()-Funktionen mit anderen Excel-Funktionen kombinieren

Verschachtelte IF() Funktionen sind schon für sich genommen vielseitig, aber wenn du sie mit anderen Excel-Funktionen kombinierst, werden sie noch hilfreicher. Ich zeige dir anhand einiger Beispiele, wie das geht.

IF() mit VLOOKUP()

Wenn du VLOOKUP() mit IF() kombinierst, kannst duDaten auf der Grundlage bestimmter Kriterien bedingt abrufen und manipulieren. Du hast zum Beispiel eine Preisliste, die je nach Region unterschiedlich ist. Wenn die Region ist Nordist, möchtest du einen Rabatt von 10% gewähren - andernfalls wird der Standardpreis angezeigt.

Verwende dazu die folgende Formel:

=IF(C2="North",VLOOKUP(B2,B2:D7,3,FALSE)*0.9,VLOOKUP(B2,B2:D7,3,FALSE))

In der obigen Formel prüft die Funktion IF(), ob die Region in C2 nördlichist . Wenn das der Fall ist, wird der mit VLOOKUP() ermittelte Preis mit 0,9 multipliziert, um den 10%igen Rabatt anzuwenden. Wenn das nicht der Fall ist, ruft VLOOKUP() den Standardpreis auf.

Kombiniere IF() mit VLOOKUP(). Bild vom Autor.

IF() mit SUMIF()

Die Funktion SUMIF() fügt Werte auf der Grundlage bestimmter Kriterien hinzu. Wenn du sie mit IF() kombinierst, kannst du Daten noch besser analysieren, indem du Zahlen addierst, die bestimmte Bedingungen erfüllen. Wenn ich zum Beispiel den Gesamtumsatz für den Nord berechnen möchte, verwende ich die folgende Formel:

=IF(B2="North",(SUMIF(B2:C10,B2,C2:C10)),"")

In der obigen Formel prüft die Funktion IF(), ob die Region in B2Norden ist . Wenn TRUE, SUMIF() alle Werte in der Spalte C addiert, die mit Nord übereinstimmen . Wenn FALSE, wird ein leerer String zurückgegeben.

Kombiniere IF() mit SUMIF(). Bild vom Autor.

Dinge, die ich beim Kombinieren von Formeln doppelt überprüfe

Es war knifflig, als ich anfing, mehrere Funktionen zu kombinieren. Ich habe diese komplexen Formeln geschrieben, nur um sie dann wegen etwas Einfachem kaputt zu machen. Hier sind ein paar Lektionen, die ich auf die harte Tour gelernt habe:

• Behalte den Lernpfad für Klammern im Auge: Hier sind die meisten meiner Fehler passiert. Ich würde eine schließende Klammer übersehen und ewig damit verbringen, herauszufinden, warum meine Formel nicht funktioniert. Ich überprüfe immer, dass jede öffnende Klammer eine passende schließende Klammer hat. Das spart mir so viel Zeit.

• Behandle Fehler: Wenn du schon einmal einen hässlichen Fehler in einer Formel hattest, weil ein Wert fehlte, weißt du, wie frustrierend das sein kann. Ich verwende jetzt immer IFERROR(), um diese Probleme zu erkennen und sie durch etwas Hilfreicheres zu ersetzen, z. B. eine benutzerdefinierte Nachricht.

• Verwende benannte Bereiche: Bevor ich angefangen habe, meine Lernpfade zu benennen, habe ich in langen Formeln oft den Überblick verloren, auf welchen Bereich ich mich beziehe. Dann habe ich angefangen, ihnen eindeutige Namen zu geben, damit meine Formeln leichter zu lesen sind und die Wahrscheinlichkeit, eine Referenz zu verwechseln, geringer ist.

Alternativen zu Nested IF()

Um gründlich zu sein, wollen wir uns einige Optionen ansehen. 

IFS()-Funktion

Excel erlaubt zwar bis zu 64 verschachtelte IF() Funktionen, aber seien wir mal ehrlich: Wenn du so viele Bedingungen stapelst, entstehen fehleranfällige Formeln, die niemand mehr debuggen will. Wenn du Excel 2016 oder höher verwendest, kannst du die Funktion IFS() nutzen. Es ist eine saubere Alternative, um mehrere Bedingungen ohne Verschachtelung zu behandeln.

Hier ist die Syntax:

=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2])

Ich zeige dir, wie es funktioniert. Angenommen, ich muss die Steuersätze auf der Grundlage der Gehälter der Mitarbeiter berechnen, dann wende ich die folgende Formel an.

=IFS(C2<=30000, "10%", C2<=60000, "20%", C2<=100000, "30%", C2>100000, "40%")

Wenn das Gehalt in C2 30.000 $ oder weniger beträgt, gibt er 10%. Wenn es zwischen 30.001 und 60.000 $ liegt, gibt es 20%. Wenn es zwischen 60.001 und 100.000 $ liegt, bringt es 30%. Alles, was über 100.000 $ liegt, bringt 40%.

Wie bei der Funktion IF() ist die Reihenfolge der Bedingungen wichtig. Excel bricht die Auswertung ab, sobald es die erste Bedingung findet, die TRUE lautet. Wenn also keine der Bedingungen erfüllt ist, bekommst du eine Fehlermeldung. Um das zu vermeiden, kannst du am Ende eine Standardbedingung wie diese hinzufügen:

=IFS(C2<=30000, "10%", C2<=60000, "20%", C2<=100000, "30%", C2>100000, "40%", TRUE, "No Tax")

Jetzt wird Excel zurückgeben Keine Steuer zurück, wenn keine der Gehaltsbedingungen erfüllt ist, anstatt einen #N/A Fehler.

Verwendung der Funktion IFS(). Bild vom Autor.

IF() kombiniert mit IF() mit &

Optional kannst du IF() mit Funktionen wie & oder CONCATENATE() kombinieren. Hier ist ein Beispiel, in dem ich das Rabattangebot für jede Region angezeigt habe:

=(IF(C2="North", 10%, "") & IF(C2="South",15%, "") & IF(C2="East", 5%, "") & IF(C2="West", 20%, ""))*1

In der obigen Formel prüft jede IF() Anweisung die Region und fügt den Rabattprozentsatz als Text an. Die *1 am Ende wandelt das Ergebnis in eine Zahl um. Wenn du eine Textausgabe möchtest, kannst du sie überspringen.

Verkette mehrere IF()-Funktionen. Bild vom Autor.

CHOOSE() oder SWITCH() Funktionen

Wenn du nach Alternativen suchst, sind CHOOSE() und SWITCH() eine Überlegung wert. Sie eignen sich hervorragend für einfache Szenarien, in denen du aus vordefinierten Optionen auswählen oder einen einzelnen Wert mit mehreren Ergebnissen abgleichen musst. Schau dir den DataCamp-Kurs "Datenanalyse in Excel" an, in dem du mit einer Vielzahl von Funktionen üben kannst.

Tipps zur Fehlerbehebung für verschachtelte IF()

Als ich anfing, verschachtelte IF() Formeln zu verwenden, stieß ich auf eine Menge Fehler. Es schien unmöglich, sie zu lösen. Aber mit der Zeit und den Fehlern habe ich Lektionen gelernt, von denen ich wünschte, ich hätte sie früher gewusst. Hier sind einige von ihnen: 

• Datentyp-Verwechslung: Das Mischen von Zahlen und Text in Vergleichen kann zu unerwarteten Ergebnissen führen. Wenn du Text vergleichst, achte darauf, dass er als Text formatiert ist. Das Gleiche gilt für Zahlen.

• Die Grenze erreicht: Excel begrenzt verschachtelte IF() auf 64 Ebenen. Wenn dein Mittel nicht mehr funktioniert, überprüfe, ob du das Limit erreicht hast. An diesem Punkt ist es normalerweise an der Zeit, den Ansatz zu überdenken oder zu einer anderen Funktion wie IFS() oder SWITCH() zu wechseln.

Wenn sich deine Formel nicht wie erwartet verhält, kannst du Folgendes tun:

1. Brich es auf: Versuche nicht, eine riesige Formel auf einmal zu beheben. Zerlege es in kleinere Stücke und prüfe jedes Teil. So kannst du genau feststellen, wo etwas schief läuft.
2. Nutze die Excel-Tools zur Formelüberprüfung: Diese Tools sind ein Lebensretter bei der Fehlersuche. Verwende Formel auswerten , um deine Formel Stück für Stück durchzugehen oder verfolge Vorgänger/Abhängigkeiten, um zu sehen, wie Zellen miteinander verbunden sind.
3. Überprüfe das Format deiner Zelldaten: Manchmal ist es einfach nur ein Formatierungsproblem. Wenn eine Zelle zum Beispiel Text enthält, aber wie eine Zahl aussieht, funktioniert deine Formel vielleicht nicht wie erwartet. Überprüfe die Formatierung und passe sie bei Bedarf an.

Eine herausfordernde Frage

Hier ist eine einfache Herausforderung, um das Gelernte anzuwenden. Angenommen, du berechnest die Prämien für deine Mitarbeiter auf der Grundlage ihrer Leistung und ihrer Dienstjahre. Du willst geben:

• A 10% Bonus wenn die Leistung Ausgezeichnet und Dienstjahre über 5.
• A 5% Bonus wenn die Leistung Gut und mehr Dienstjahren 3.
• Sonst kein Bonus.

So könntest du die Formel schreiben:

=IF(B2="Excellent", IF(C2>5, D2*10%, "No Bonus"), IF(B2="Good", IF(C2>3, D2*5%, "No Bonus"), "No Bonus"))

Probiere das aus und schau, ob du es an deine Szenarien anpassen kannst.

Schlussgedanken

Als ich anfing, verschachtelte IF() Anweisungen zu verwenden, war ich erstaunt, wie viel Zeit ich sparen konnte und wie viele neue Möglichkeiten sich mir eröffneten. Wenn du mitverfolgt hast, wirst du das gleiche Potenzial bemerkt haben.

Der Schlüssel zur Beherrschung der verschachtelten IF() ist wirklich Übung. Je mehr du experimentierst und diese Techniken in deinen eigenen Projekten anwendest, desto schneller werden sie dir zur zweiten Natur. Wenn du deine Fähigkeiten noch weiter ausbauen möchtest, empfehle ich dir den Kurs Finanzmodellierung in Excel oder den Lernpfad Datenanalyse mit Excel Power Tools auf DataCamp. Sie helfen dir, die Kluft zwischen Formeln zu überbrücken und Excel als echtesGeschäftswerkzeug zu nutzen.

Excel hat so viel zu bieten, und verschachtelte IF() ist ein Teil davon. Sobald du anfängst, diese Formeln kreativ einzusetzen, wirst du sehen, wie hilfreich Excel sein kann.