IF-Funktion – verschachtelte Formeln und Fallstricke vermeiden
Die IF-Funktion ermöglicht Ihnen einen logischen Vergleich zwischen einem Wert und dem, was Sie erwarten, indem Sie auf eine Bedingung testen und ein Ergebnis zurückgeben, wenn es wahr oder falsch ist.
=IF(Etwas ist wahr, dann tue etwas, sonst tue etwas anderes)
Eine IF-Anweisung kann also zwei Ergebnisse haben. Das erste Ergebnis ist, wenn Ihr Vergleich wahr ist, das zweite, wenn Ihr Vergleich falsch ist.
IF-Anweisungen sind unglaublich robust und bilden die Grundlage vieler Tabellenkalkulationsmodelle, aber sie sind auch die Hauptursache vieler Tabellenkalkulationsprobleme. Idealerweise sollte eine IF-Anweisung auf minimale Bedingungen zutreffen, wie z. B. Männlich/Weiblich, Ja/Nein/Vielleicht, um nur einige zu nennen, aber manchmal müssen Sie möglicherweise komplexere Szenarien auswerten, die eine Verschachtelung* von mehr als 3 IF-Funktionen erfordern.
* „Verschachtelung" bezieht sich auf die Praxis, mehrere Funktionen in einer Formel zusammenzuführen.
Verwenden Sie die IF-Funktion, eine der logischen Funktionen , um einen Wert zurückzugeben, wenn eine Bedingung wahr ist, und einen anderen Wert, wenn sie falsch ist.
Syntax
IF(logischer_Test, Wert_wenn_wahr, [Wert_wenn_falsch])
Zum Beispiel:
=IF(A2>B2,"Budget überschritten","OK")
=IF(A2=B2,B4-A4,"")
Argumentname | Beschreibung |
Logik Test (erforderlich) | Die Bedingung, die Sie testen möchten. |
value_if_true (erforderlich) | Der Wert, der zurückgegeben werden soll, wenn das Ergebnis von logical_test TRUE ist. |
value_if_false (Optional) | Der Wert, der zurückgegeben werden soll, wenn das Ergebnis von logical_test FALSE ist. |
Bemerkungen
Während Excel es Ihnen erlaubt, bis zu 64 verschiedene IF-Funktionen zu verschachteln, ist es überhaupt nicht ratsam, dies zu tun. Wieso den?
Mehrere IF-Anweisungen erfordern viel Überlegung, um richtig aufgebaut zu werden und sicherzustellen, dass ihre Logik jede Bedingung bis zum Ende richtig berechnen kann. Wenn Sie Ihre Formel nicht zu 100 % genau verschachteln, funktioniert sie möglicherweise in 75 % der Fälle, gibt aber in 25 % der Fälle unerwartete Ergebnisse zurück. Leider sind die Chancen, dass Sie die 25 % erwischen, gering.
Mehrere IF-Anweisungen können unglaublich schwierig zu pflegen sein, besonders wenn Sie einige Zeit später wiederkommen und versuchen herauszufinden, was Sie oder schlimmer noch jemand anderes versucht hat zu tun.
Wenn Sie sich mit einer IF-Anweisung wiederfinden, die einfach weiter zu wachsen scheint, ohne dass ein Ende in Sicht ist, ist es an der Zeit, die Maus niederzulegen und Ihre Strategie zu überdenken.
Schauen wir uns an, wie Sie eine komplexe verschachtelte IF-Anweisung mit mehreren IFs richtig erstellen und wann Sie erkennen, dass es an der Zeit ist, ein anderes Tool in Ihrem Excel-Arsenal zu verwenden.
Beispiele
Im Folgenden finden Sie ein Beispiel für eine relativ standardmäßige verschachtelte IF-Anweisung, mit der die Testergebnisse der Schüler in ihre Notenäquivalente umgewandelt werden.
=WENN(D2>89,"A",WENN(D2>79,"B",WENN(D2>69,"C",WENN(D2>59,"D","F"))))
Diese komplexe verschachtelte IF-Anweisung folgt einer einfachen Logik:
Wenn das Testergebnis (in Zelle D2) größer als 89 ist, erhält der Schüler eine Eins
Wenn das Testergebnis größer als 79 ist, erhält der Student ein B
Wenn das Testergebnis größer als 69 ist, erhält der Schüler ein C
Wenn das Testergebnis größer als 59 ist, erhält der Student ein D
Andernfalls erhält der Student eine F
Dieses spezielle Beispiel ist relativ sicher, da es unwahrscheinlich ist, dass sich die Korrelation zwischen Testergebnissen und Buchstabennoten ändert, sodass es nicht viel Wartung erfordert. Aber hier ist ein Gedanke – was ist, wenn Sie die Noten zwischen A+, A und A- (und so weiter) segmentieren müssen? Jetzt muss Ihre IF-Anweisung mit vier Bedingungen neu geschrieben werden, um 12 Bedingungen zu haben! So würde deine Formel jetzt aussehen:
=WENN(B2>97,"A+",WENN(B2>93,"A",WENN(B2>89,"A-",WENN(B2>87,"B+",WENN(B2>83,"B ",IF(B2>79,"B-",IF(B2>77,"C+",IF(B2>73,"C",IF(B2>69,"C-",IF(B2>57, "D+",IF(B2>53,"D",IF(B2>49,"D-","F"))))))))))))
Es ist immer noch funktional genau und funktioniert wie erwartet, aber es dauert lange, es zu schreiben und länger zu testen, um sicherzustellen, dass es das tut, was Sie wollen. Ein weiteres eklatantes Problem ist, dass Sie die Punktzahlen und die entsprechenden Buchstabennoten von Hand eingeben mussten. Wie hoch ist die Wahrscheinlichkeit, dass Sie versehentlich einen Tippfehler machen? Stellen Sie sich nun vor, Sie versuchen dies 64 Mal mit komplexeren Bedingungen! Sicher, es ist möglich, aber wollen Sie sich wirklich dieser Art von Aufwand und wahrscheinlichen Fehlern aussetzen, die wirklich schwer zu erkennen sind?
Tipp: Jede Funktion in Excel benötigt eine öffnende und schließende Klammer (). Excel wird versuchen, Ihnen dabei zu helfen, herauszufinden, was wo hingehört, indem es verschiedene Teile Ihrer Formel einfärbt, wenn Sie sie bearbeiten. Wenn Sie beispielsweise die obige Formel bearbeiten und den Cursor an jeder der abschließenden Klammern „)" vorbeibewegen, ändert sich die entsprechende öffnende Klammer in dieselbe Farbe. Dies kann besonders in komplexen verschachtelten Formeln nützlich sein, wenn Sie herausfinden möchten, ob Sie genügend übereinstimmende Klammern haben.
Weitere Beispiele
Im Folgenden finden Sie ein sehr häufiges Beispiel für die Berechnung der Verkaufsprovision auf der Grundlage der erzielten Einnahmen.
=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, WENN (C9 größer als 15.000 ist, dann 20 % zurückgeben, WENN (C9 größer als 12.500 ist, dann 17,5 % zurückgeben, und so weiter ...
Diese Formel ist zwar bemerkenswert ähnlich wie das frühere Beispiel für Grades, aber sie ist ein großartiges Beispiel dafür, wie schwierig es sein kann, große IF-Aussagen zu pflegen – was müssten Sie tun, wenn Ihre Organisation beschließt, neue Vergütungsstufen hinzuzufügen und möglicherweise sogar den bestehenden Dollar zu ändern oder Prozentwerte? Sie hätten viel Arbeit vor sich!
Tipp: Sie können in der Formelleiste Zeilenumbrüche einfügen, um lange Formeln besser lesbar zu machen. Drücken Sie einfach ALT+EINGABETASTE vor dem Text, den Sie in eine neue Zeile umbrechen möchten.
Hier ist ein Beispiel für das Provisionsszenario mit der fehlerhaften Logik:
Kannst du sehen, was falsch ist? Vergleichen Sie die Reihenfolge der Umsatzvergleiche mit dem vorherigen Beispiel. In welche Richtung geht dieser? Das ist richtig, es geht von unten nach oben (5.000 $ bis 15.000 $), nicht umgekehrt. Aber warum sollte das so eine große Sache sein? Es ist eine große Sache, weil die Formel die erste Bewertung für einen Wert über 5.000 $ nicht bestehen kann. Nehmen wir an, Sie haben einen Umsatz von 12.500 US-Dollar – die IF-Anweisung gibt 10 % zurück, weil sie größer als 5.000 US-Dollar ist, und endet dort. Dies kann unglaublich problematisch sein, da diese Art von Fehlern in vielen Situationen unbemerkt bleiben, bis sie sich negativ ausgewirkt haben. Was können Sie also tun, wenn Sie wissen, dass es bei komplexen verschachtelten IF-Anweisungen einige schwerwiegende Fallstricke gibt? In den meisten Fällen können Sie die VLOOKUP-Funktion verwenden, anstatt eine komplexe Formel mit der IF-Funktion zu erstellen. Mit SVERWEIS müssen Sie zunächst eine Referenztabelle erstellen:
=SVERWEIS(C2,C5:D17,2,WAHR)
Diese Formel besagt, dass der Wert in C2 im Bereich C5:C17 gesucht werden soll. Wenn der Wert gefunden wird, geben Sie den entsprechenden Wert aus derselben Zeile in Spalte D zurück.
=SVERWEIS(B9,B2:C6,2,WAHR)
Entsprechend sucht diese Formel nach dem Wert in Zelle B9 im Bereich B2:B22. Wenn der Wert gefunden wird, geben Sie den entsprechenden Wert aus derselben Zeile in Spalte C zurück.
Hinweis: Diese beiden VLOOKUPs verwenden das TRUE-Argument am Ende der Formeln, was bedeutet, dass wir möchten, dass sie nach einer ungefähren Übereinstimmung suchen. Mit anderen Worten, es stimmt mit den genauen Werten in der Nachschlagetabelle sowie mit allen Werten, die dazwischen liegen, überein. In diesem Fall müssen die Nachschlagetabellen in aufsteigender Reihenfolge vom kleinsten zum größten sortiert werden.
SVERWEIS wird hier viel detaillierter behandelt, aber das ist sicher viel einfacher als eine komplex verschachtelte IF-Anweisung mit 12 Ebenen! Es gibt auch andere, weniger offensichtliche Vorteile:
VLOOKUP-Referenztabellen sind offen und leicht zu sehen.
Tabellenwerte können einfach aktualisiert werden und Sie müssen die Formel nie anfassen, wenn sich Ihre Bedingungen ändern.
Wenn Sie nicht möchten, dass andere Ihre Referenztabelle sehen oder stören, legen Sie sie einfach auf ein anderes Arbeitsblatt.
Hast Du gewusst?
Es gibt jetzt eine IFS -Funktion, die mehrere verschachtelte IF-Anweisungen durch eine einzige Funktion ersetzen kann. Anstelle unseres ersten Notenbeispiels, das 4 verschachtelte IF-Funktionen hat:
=WENN(D2>89,"A",WENN(D2>79,"B",WENN(D2>69,"C",WENN(D2>59,"D","F"))))
Es kann mit einer einzigen IFS-Funktion viel einfacher gemacht werden:
=IFS(D2>89,"A",D2>79,"B",D2>69,"C",D2>59,"D",WAHR,"F")
Die IFS-Funktion ist großartig, weil Sie sich nicht um all diese IF-Anweisungen und Klammern kümmern müssen.
Hinweis: Diese Funktion ist nur verfügbar, wenn Sie über ein Microsoft 365-Abonnement verfügen. Wenn Sie ein Microsoft 365-Abonnent sind, stellen Sie sicher, dass Sie über die neueste Version von Office verfügen .
Kaufen oder testen Sie Microsoft 365
Benötigen Sie weitere Hilfe?
Sie können jederzeit einen Experten in der Excel Tech Community fragen oder Unterstützung in der Answers-Community erhalten.
Verwandte Themen
Video: Erweiterte ZF-Funktionen
IFS-Funktion (Microsoft 365, Excel 2016 und höher)
Die COUNTIF-Funktion zählt Werte basierend auf einem einzigen Kriterium
Die COUNTIFS-Funktion zählt Werte basierend auf mehreren Kriterien
Die SUMMEWENN-Funktion summiert Werte basierend auf einem einzigen Kriterium
Die SUMIFS-Funktion summiert Werte basierend auf mehreren Kriterien
UND-Funktion
ODER-Funktion
SVERWEIS-Funktion
Übersicht über Formeln in Excel
So vermeiden Sie fehlerhafte Formeln
Fehler in Formeln erkennen
Logische Funktionen
Excel-Funktionen (alphabetisch)
Excel-Funktionen (nach Kategorie)
No comments:
Post a Comment