MS EXCEL - die verschachtelte WENN-Funktion
Inhalt: In dieser Lektion lernen Sie, mithilfe der verschachtelten WENN-Funktion mehr als zwei mögliche Ergebnisse darzustellen. Voraussetzung dafür: Die einfache Wenn-Funktion- siehe hier!Die mehrfach verschachtelte WENN-Funktion
Wenn Sie sich mit der normalen WENN Funktion vertraut gemacht haben, ist es an der Zeit sich damit ein wenig vertiefender zu beschäftigen. Bei der einfachen WENN Funktion erhalten wir maximal zwei mögliche Ergebnisse. Es kann aber sein, dass mehr als zwei Ergebnisse zur Auswahl stehen. Dann kann die WENN-Funktion in sich verschachtelt werden. Das ist eigentlich überhaupt nicht schwierig. Man muss nur darauf aufpassen, dass man beim Erstellen der verschachtelten WENN-Funktion nicht durcheinander kommt. Das gilt insbesondere dann, wenn als DANN_Wert längere Formeln verwendet werden müssen. Bis zur Excel Version 2003 gab es die Möglichkeit, die WENN Funktion insgesamt siebenmal in sich zu verschachteln (Mehr Verschachtelungen nur mittels der Trickkiste durch das Verbinden mit einem Plus-Zeichen möglich). Seit der Excel Version 2007 stehen 64 Verschachtelungsebenen zur Verfügung.Wie wird das Ganze nun in Excel umgesetzt?
Wir benötigen für den Einsatz in Excel die entsprechende WENN Funktion, wie bereits in der Lektion über die einfache WENN-Funktion besprochen. Der einzige Unterschied ist der, dass anstelle des Sonst_Wertes eine weitere WENN Funktion geschrieben wird. Dann wird in dieser weiteren WENN-Funktion der Sonst_WERT abermals durch eine neue WENN-Funktion ersetzt usw. je nachdem wie viele Verschachtelungen man für die Lösung der Aufgabe benötigt.Hier die Syntax für eine zweifach verschachtelte WENN-Funktion:
=WENN(Bedingung;Dann_Wert;WENN(Bedingung;Dann_Wert;Sonst_Wert))Wichtig ist hier die korrekte Klammersetzung. Zu zwei öffnenden Klammern gehören auch zwei schließende Klammern.
Nun die Syntax für eine siebenfach verschachtelte WENN Funktion:
=WENN(Bedingung;Dann_Wert;WENN(Bedingung;Dann_Wert;WENN(Bedingung;Dann_Wert;WENN(Bedingung;Dann_Wert;WENN(Bedingung;Dann_Wert;WENN(Bedingung;Dann_Wert;WENN(Bedingung;Dann_Wert;Sonst_Wert)))))))Vor das WENN innerhalb der Verschachtelung wird dann kein Gleichheitszeichen geschrieben. Bei den schließenden Klammern leuchten immer die dazugehörigen Klammernpaare kurz auf, zudem sind sie farblich gekennzeichnet.
Beispiel 1 für eine einfache verschachtelte WENN-Funktion:
Es soll die Provision für einen Verkäufer berechnet werden. Dazu gibt es eine Provisonsstaffel mit drei möglichen Werten. Ist der Umsatz kleiner als 5.000 €, dann gibt es 3 % Provision vom Umsatz, liegt der Umsatz zwischen 5.000 € und 20.000 € (inklusive 5.000 und 20.000 EUR), dann gibt es 10 % Provision vom Umsatz und wenn der Umsatz über 20.000 € liegt, dann gibt es sogar 20 % Provision. In Feld C2 soll der Umsatz eingegeben und in Feld C8 soll die entsprechende Provision in Euro ausgegeben werden. Für die Lösung dieser Aufgabe klicken wir zuerst in die Zelle C8, dort wo das Ergebnis stehen soll. Dann geben wir den Anfang der Funktion ein:
=WENN(
Also ist gleich WENN und dann eine runde öffnende Klammer.
Danach formulieren wir die erste Prüfung. Überprüfen möchten wir den Umsatz und der steht in Zelle C2. Klicken Sie nun also auf die Zelle C2. In Excel steht jetzt:
=WENN(C2
Jetzt müssen wir den Vergleichsoperator kleiner als benutzen, da der erste Umsatz ja kleiner als 5.000 EUR ist. Daher schreiben wir weiter:
=WENN(C2<5000;
Danach folgt ein Semikolon, da wir nun die erste Prüfung abgeschlossen haben.
Jetzt folgt der erste Dann_Wert. Wenn der Umsatz kleiner als 5.000 € ist, dann gibt es 3 % und wenn das Ganze in Euro ausgegeben werden soll, dann müssen wir die 3 % mal dem Umsatz in Zelle C2 multiplizieren. Unsere Formel lautet jetzt:
=WENN(C2<5000;C2*D4;
Nun käme der Sonst_Wert, aber anstelle des Sonst_Wertes schreiben wir jetzt erneut die WENN Funktion und prüfen jetzt die Grenze von 20.000 EUR ab. Ist diese Bedingung erfüllt, gibt es 10 % Provision. Unsere Formel lautet jetzt:
=WENN(C2<5000;C2*D4;WENN(C2<=20000;C2*D5;
Da die 20.000 € im Bereich eingeschlossen sind, müssen wir kleiner gleich schreiben. Nun haben wir zwei Bedingungen definiert, mit zwei Dann_Werten. Bleibt nur noch eine Möglichkeit offen, welche wir mit dem Sonst_Wert erfassen können. Wir schreiben:
=WENN(C2<5000;C2*D4;WENN(C2<=20000;C2*D5;C2*D6))
Am Ende machen wir zwei schließende Klammern, da wir auch zwei öffnende Klammern gesetzt haben. Nun können wir die ENTER-Taste drücken und es erscheint die entsprechende Provision. Zur Sicherheit prüfen wir jetzt die Funktion für verschiedene Umsätze, indem wir vor allem die Grenzwerte prüfen.
Provision bei 1.000 € - 30 €; Provision bei 5.000 € - 500 €; Provision bei 20.000 € - 2.000 €; Provision bei 21.000 € - 4.200 €
Wenn diese Werte stimmen, dann ist die erstellte WENN-Funktion richtig.
Beispiel 2 für eine mehrfach verschachtelte WENN-Funktion
In diesem Beispiel soll aus dem Datum in Zelle C2 der Wochentag ermittelt und in Zelle C6 ausgegeben werden. Anmerkung: Das geht natürlich auch einfacher und soll hier nur zum Zwecke des Übens der verschachtelten WENN-Funktion dienen!
Es gibt eine weitere Funktion mit dem Namen und der Syntax =WOCHENTAG(Zahl;[Typ]) Da diese Funktion nicht Gegenstand unserer Lernbemühungen sein soll, sei hier nur gesagt, dass diese standardmäßig, ohne Eingabe des optionalen Typs eine Zahl als Ergebnis bringt. Die 1 steht für Sonntag, die zwei für Montag usw. Die Zuordnung sehen Sie oben in der Abbildung. Gemäß dieser Zuordnung wollen wir jetzt eine verschachtelte WENN-Funktion erstellen, die als Ergebnis den korrekten Wochentag ausgibt. Klicken wir dazu zuerst in die Zelle C4 und schreiben:
=WOCHENTAG(C2)
Drücken Sie die ENTER-Taste und es wird die Zahl 4 ausgegeben. Nun klicken Sie in die Zelle C6 und formulieren die entsprechen de verschachtelte WENN-Funktion. Diese muss dann wie folgt lauten:
=WENN(C4=1;"Sonntag";WENN(C4=2;"Montag";WENN(C4=3;"Dienstag";WENN(C4=4;"Mittwoch";WENN(C4=5;"Donnerstag";WENN(C4=6;"Freitag";"Samstag"))))))
Vergessen Sie zum Schluss nicht die schließenden Klammern. Sie sehen also, die verschachtelte WENN-Funktion ist im Grunde leicht. Schwierigkeiten gibt es meist, wenn bei den Dann und Sonst Werten umfangreiche Berechnungen notwendig sind und dadurch die Übersicht schnell verloren geht. Erstellen Sie in solchen Fällen erst die WENN-Funktion ohne Berechnung und fügen Sie die Berechnungen erst danach ein. Sie können sich die Schritte auch gerne in meinem Video nochmals anschauen. Einfach Video starten und auf Vollbild stellen.
Hier können Sie die Übungsdatei zum Lehrvideo kostenlos herunterladen. Es sind insgesamt zwei Übungen. Jede ist separat ausdruckbar und in jeweils einem anderen Tabellenblatt. Die Lösungen befinden sich immer im Tabellenblatt daneben.