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!Am Ende der Lektion gibt es Übungsdateien.
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. Microsoft schreibt aber selbst dazu auf der eigenen Webseite, dass es nicht ratsam ist diese 64 möglichen Verschachtelungsebenen auch zu verwenden. Es ist eine sehr hohe Konzentration erforderlich, um dabei keine Fehler zu machen. Diese Fehler sind später sehr schwer zu kontrollieren bzw. zu finden. Oftmals bleiben sie über Jahre unentdeckt. Vor allem, wenn ganz bestimmte Bedingungen nur ganz selten zutreffen. Oftmals gibt es dann andere Möglichkeiten, die Berechnung einfacher durchzuführen.
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 Klammerpaare 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.
Übungsaufgaben für die verschachtelte WENN-Funktion:
Aufgabe 1:
Im Feld C2 soll unterschiedliche Umsätze eingegeben werden. Auf diesen Umsatz gibt es unter bestimmten Umständen eine Verkäuferprovision. Ist der Umsatz kleiner als 5000 €, dann soll es drei Prozent auf den Umsatz geben. Bei einem Umsatz von 5.000 bis 20.000 Euro soll es 10 Prozent geben und bei über 20.000 Euro 20 Prozent.
Erstelle im Feld C8 eine verschachtelte WENN-Funktion, welche den Umsatz auf die Bedingungen hin überprüft und die zu zahlende Provision in Euro ausgibt.
Überprüfe die erstellte Formel mit den folgenden Umsätzen:
3.580,00 Euro - zu zahlende Provision: 107,40 Euro
5.000,00 Euro - zu zahlende Provision: 500,00 Euro
8.570,00 Euro - zu zahlende Provision: 857,00 Euro
20.000,00 Euro - zu zahlende Provision: 2.000,00 Euro
20.001,00 Euro - zu zahlende Provision: 4.000,20 Euro
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.
Aufgabe 2:
Im Feld C2 soll ein beliebiges Datum eingegeben werden. Im Feld C6 soll zu dem Datum der entsprechende Wochentag ausgegeben werden. In der Zelle C4 soll über die Funktion =WOCHENTAG() der Wochentag als Zahl, per Zwischenschritt ermittelt werden. Diese Funktion gibt die Zahlen 1 bis 7 für den Wochentag wieder. Die Zuordnung ist wie folgt:
1 - Sonntag
2 - Montag
3 - Dienstag
4 - Mittwoch
5 - Donnerstag
6 - Freitag
7 - Samstag
Erstelle in dem Feld C6 eine verschachtelte WENN-Funktion, welche den Wochentag als Text ausgibt, also z. B. Mittwoch. Hinweis: Die Formel muss sechsmal die WENN-Funktion enthalten.
Überprüfe die folgenden Datumswerte:
30.03.2011 - Mittwoch
12.01.1966 - Mittwoch
08.05.1945 - Dienstag
15.07.2032 - Donnerstag
01.01.1870 - #Wert!
Zusatzaufgabe: Warum gibt Excel beim letzten Datum die Fehlermeldung #WERT! aus?
Die Tabellen zu den Aufgaben 1 und 2 findest Du unter dem nachfolgenden Link zusammen mit den Lösungen.
Weitere Aufgaben:
Aufgabe 1: Schulnoten Berechnung
Ziel: Berechnen Sie die Schulnote basierend auf der Punktzahl eines Schülers.
Details:
- Gegeben sind die Punktzahlen in Spalte A.
- Die Notenskala ist wie folgt:
- Punktzahl ≥ 90: Note 1
- Punktzahl ≥ 80: Note 2
- Punktzahl ≥ 70: Note 3
- Punktzahl ≥ 60: Note 4
- Punktzahl < 60: Note 5
Aufgabe: Verwenden Sie eine verschachtelte Wenn-Funktion, um in Spalte B die entsprechende Note zu berechnen.
Lösung:
=WENN(A2>=90; 1; WENN(A2>=80; 2; WENN(A2>=70; 3; WENN(A2>=60; 4; 5))))
Aufgabe 2: Mitarbeitereinstufung nach Dienstjahren
Ziel: Einstufung der Mitarbeiter nach Anzahl der Dienstjahre.
Details:
- Gegeben sind die Dienstjahre in Spalte A.
- Die Einstufung erfolgt wie folgt:
- Dienstjahre ≥ 20: Senior
- Dienstjahre ≥ 10: Mittel
- Dienstjahre < 10: Junior
Aufgabe: Verwenden Sie eine verschachtelte Wenn-Funktion, um in Spalte B die Einstufung der Mitarbeiter zu berechnen.
Lösung:
=WENN(A2>=20;"Senior"; WENN(A2>=10; "Mittel"; "Junior"))
Aufgabe 3: Verkaufsprovision Berechnung
Ziel: Berechnen Sie die Verkaufsprovision basierend auf dem Umsatz.
Details:
- Gegeben ist der Umsatz in Spalte A.
- Die Provisionssätze sind wie folgt:
- Umsatz ≥ 10000: 10%
- Umsatz ≥ 5000: 7%
- Umsatz ≥ 2000: 5%
- Umsatz < 2000: 3%
Aufgabe: Verwenden Sie eine verschachtelte Wenn-Funktion, um in Spalte B die entsprechende Provision zu berechnen.
Lösung:
=WENN(A2>=10000; A2*0,1;WENN(A2>=5000; A2*0,07; WENN(A2>=2000; A2*0,05;A2*0,03)))
Aufgabe 4: Leistungsbewertung von Angestellten
Ziel: Leistungsbewertung basierend auf den Bewertungspunkten.
Details:
- Gegeben sind die Bewertungspunkte in Spalte A.
- Die Bewertung erfolgt wie folgt:
- Punkte ≥ 90: Hervorragend
- Punkte ≥ 75: Gut
- Punkte ≥ 50: Befriedigend
- Punkte < 50: Verbesserungsbedürftig
Aufgabe: Verwenden Sie eine verschachtelte Wenn-Funktion, um in Spalte B die Leistungsbewertung der Angestellten zu berechnen.
Lösung:
=WENN(A2>=90;"Hervorragend";WENN(A2>=75;"Gut";WENN(A2>=50;"Befriedigend";"Verbesserungsbedürftig")))
Aufgabe 5: Rabattberechnung für Kunden
Ziel: Berechnen Sie den Rabatt basierend auf der Menge der bestellten Artikel.
Details:
- Gegeben ist die Anzahl der bestellten Artikel in Spalte A.
- Die Rabattstaffelung ist wie folgt:
- Artikel ≥ 100: 20%
- Artikel ≥ 50: 15%
- Artikel ≥ 20: 10%
- Artikel < 20: 5%
Aufgabe: Verwenden Sie eine verschachtelte Wenn-Funktion, um in Spalte B den entsprechenden Rabatt zu berechnen.
Lösung:
=WENN(A2>=100;0,2;WENN(A2>=50;0,15;WENN(A2>=20;0,1;0,05)))
Diese Aufgaben decken verschiedene Szenarien ab und ermöglichen es, den Umgang mit verschachtelten Wenn-Funktionen in Excel zu üben.