„Wenn Du artig bist, darfst Du ein Eis essen.“ „Wenn Du nicht artig bist, darfst Du kein Eis essen.“
Solche oder ähnliche Mahnungen hat sicherlich schon jeder von uns in seiner Kindheit zu hören bekommen. Das nachfolgende Handeln wird bestimmt durch die Erfüllung einer Vorgabe.
Was aber hat das mit Excel zu tun?
Bei der Erstellung von Planungen, von Kalkulationen oder von Berichten mit dem Tabellenkalkulationsprogramm Excel wird es immer wieder Situationen geben, in denen eine Entscheidung getroffen werden muss, soll es in diese Richtung oder in eine andere weitergehen?
Excel ist auf diese Situationen vorbereitet und bietet mit seinen Funktionen zahlreiche Möglichkeiten, Entscheidungen zu treffen und darauf aufbauend weiterzuarbeiten.
Dieser Beitrag wird sich mit der Nutzung der Excel-Funktionen WENN und WENNS in unterschiedlichsten Konstellationen beschäftigen. Du wirst kennenlernen, wie verschiedene Problemstellungen in Excel-Arbeitsmappen umgesetzt werden können.
1. Die WENN-Funktion
1.1 Beschreibung und Syntax
Die WENN-Funktion macht einen logischen Vergleich zwischen einem aktuellen Wert und einem erwarteten Wert möglich.
Das Ausführen einer WENN-Anweisung kann zu zwei Ergebnissen führen. Das erste Ergebnis wird ausgegeben, wenn der Vergleich wahr ist, das zweite, wenn der Vergleich falsch ist.
Die Syntax der WENN-Funktion lautet:
WENN(Prüfung;Dann_Wert;[Sonst_Wert])
Das Argument „Prüfung“ ist erforderlich und enthält die Bedingung, die geprüft werden soll.
Der „Dann_Wert“ ist erforderlich. Er ist der Wert, der zurückgegeben werden soll, wenn die Bedingung erfüllt ist.
Mit dem Argument „Sonst_Wert“ wird der Wert definiert, der zurückgegeben werden soll, wenn die Bedingung nicht erfüllt ist. Der „Sonst_Wert“ ist optional. Wird beim Erstellen der Formel darauf verzichtet, passiert gar nichts, wenn die Bedingung nicht erfüllt ist.
1.2 Beispiele
Beispiel 1
In der Einführung hast Du bereits eine einfache Problemstellung sehen können.
In der ersten wurde einer Person angedroht, kein Eis essen zu dürfen, wenn sie nicht artig ist.
Mit der WENN-Funktion umgesetzt, stellt sich das so dar:
Die Bedingung, die es in der Funktion zu prüfen gilt, muss stets Inhalt einer Zelle sein. Das Wort „artig“ steht in B4, „nicht artig“ in B6.
Die Funktion prüft im ersten Fall, ob in B4 das Wort „artig“ steht. Wenn ja, kommt das Argument „Dann_Wert“ zum Zuge, „Du darfst ein Eis essen“. Das Argument „Sonst_Wert“ wird nicht benutzt.
Im zweiten Fall wird geprüft, ob in B6 die Zeichenfolge „nicht artig“ steht. Ist dies zutreffend, gibt der „Dann_Wert“ die Anweisung „Du darfst kein Eis essen“. Auf den „Sonst_Wert“ wird wieder verzichtet. Einfacher lässt sich das Problem darstellen, wenn der „Sonst_Wert“ ebenfalls eingesetzt wird.
Die Funktion prüft hier ebenfalls, ob in B4 das Wort „artig“ steht.
Wenn ja, das ist der Fall, gibt der „Dann_Wert“ die Anweisung „Du darfst ein Eis essen“ aus.
Wenn nein, gibt der „Sonst_Wert“ die Anweisung „Du darfst kein Eis essen“ aus.
Ändern Sie nun den Eintrag in B4 auf „nicht artig“. Der „Sonst_Wert“ gibt nun „Du darfst kein Eis essen“ aus, da die gestellte Bedingung nicht wahr ist.
Beispiel 2
Es gibt eine Liste mit den Namen von Personen und ihren Einnahmen in einer Verkaufsaktion.
Die Aufgabe ist nun, in einer weiteren Spalte mit der WENN-Funktion zu empfehlen, ob die Person Geld ausgeben darf oder erst sparen muss. Die Empfehlung soll sich an der Wertgrenze 800 orientieren.
Geprüft wird im ersten Argument, ob die Einnahme in Spalte C >= 800 ist. Wenn ja, kommt der Dann_Wert zum Zug, das Ergebnis lautet „Geld ausgeben“. Wenn nein, gilt der Sonst_Wert, „Erst sparen“.
Beispiel 3
Die WENN-Funktion wird gern verwendet, wenn es darum geht, festzustellen, ob einem Angestellten ein Bonus zusteht. In diesem Beispiel hängt das vom Arbeitsvertrag ab, ob ein Bonus zusteht.
Schreibe dazu in E4 die Formel
=WENN(D4=“Ja“;C4*$E$3;0)
und ziehe sie bis E9 herunter. ziehe sie bis E9 herunter. In E3 lässt sich der Bonussatz variabel eingeben.
In Spalte F addierst Du die Werte aus den Spalten C und E.
Beispiel 4
Bei der Auswertung von Prüfungsergebnissen wird die WENN-Funktion gern mit den Funktionen UND sowie ODER kombiniert. Dieses Beispiel zeigt die Kombination mit UND.
Bestanden haben die Schüler, die in Test 1 eine Punktzahl größer 50 und in Test 2 größer 70 erreicht haben. Wichtig ist, dass beide Bedingungen erfüllt sind.
Somit lautet die Formel in E3:
=WENN(UND(C3>50;D3>70);“bestanden“;“nicht bestanden“)
Ziehe sie bis E8 herunter.
Beispiel 5
Dieses Beispiel zeigt die Kombination der WENN-Funktion mit der ODER-Funktion zur Feststellung der Prüfungsergebnisse.
Die Formel in E3 lautet:
=WENN(ODER(C3>50;D3>50);“bestanden“;“nicht bestanden“)
Ziehe auch sie bis E8 herunter.
Wichtig ist hier, dass eine Bedingung erfüllt sein muss.
Paula in Zeile 5 hat Pech gehabt. In Test 1 hat sie nur 48 Punkte erreicht, in Test 2 fehlt ihr gerade mal ein Punkt, damit die Bedingung „>50“ erfüllt ist. Somit ist das Ergebnis „nicht bestanden“.
Wilhelm in Zeile 8 hat zwar Test 2 völlig verhauen, aber in Test 1 mehr als 50 Punkte, nämlich 65, erreicht. Somit hat er „bestanden“.
Beispiel 6
In der Praxis treten immer wieder Fälle auf, bei denen eine WENN-Abfrage nicht genügt, sondern mehrere Abfragen nötig sind.
Das kann z.B. bei einer umsatzabhängigen Bonuszahlung der Fall sein. Bei einem Umsatz größer oder gleich 50000 soll die Filiale 5% Bonus erhalten, bei einem Umsatz größer gleich 100000 sollen es 10% sein. Die Tabelle stellt die monatlichen Umsätze von neun Filialen in vier Ländern dar.
Eine Möglichkeit ist, mehrere WENN-Abfragen zu verschachteln.
Schreibe in F3 diese Formel:
=WENN(E3>=100000;10%;WENN(E3>=50000;5%;0%))
Ziehe die Formel bis F11 herunter.
Was rechnet die Formel? Zuerst fragt sie, ob der Umsatz in E3 größer oder gleich 100000 ist. Wenn ja, sind 10% einzutragen. Soweit das nicht zutrifft, wird gefragt, ob der Umsatz dann wenigstens größer oder gleich 50000 ist. Wenn ja, gibt es 5% Bonus. Wenn das alles nicht zutrifft, ist laut Sonst_Wert 0% einzutragen.
2. Die WENNS-Funktion
2.1 Beschreibung und Syntax
Die Funktion WENNS überprüft, ob eine oder mehrere Bedingungen erfüllt sind, und gibt einen Wert zurück, der der ersten Bedingung, die WAHR ist, entspricht. Die WENNS-Funktion kannst Du anstatt zahlreicher geschachtelter WENN-Anweisungen verwenden. Sie ist einfacher zu lesen, wenn mehrere Bedingungen verwendet werden.
Die WENNS-Funktion unterstützt bis zu 127 Bedingungen.
Zu empfehlen ist, nicht zu viele Bedingungen mit WENN- oder WENNS-Anweisungen zu verschachteln. Mehrere Bedingungen müssen in der richtigen Reihenfolge eingegeben werden und sind schwierig zu erstellen, zu testen und zu aktualisieren.
Die Syntax der WENNS-Funktion lautet:
WENNS([Etwas ist wahr1; Wert wenn Wahr1;Etwas ist wahr2;Wert, wenn Wahr2;Etwas ist wahr3;Wert, wenn Wahr3)
Das Argument „Etwas ist wahr1“ ist erforderlich und enthält die erste Bedingung, die geprüft werden soll.
„Wert wenn Wahr1“ zeigt, was geschehen soll, wenn die erste Bedingung wahr ist.
Das Argument „Etwas ist wahr2“ und weitere „Etwas ist wahr“-Werte sind optional.
Alle weiteren Argumente „Wert wenn Wahr“ sind ebenfalls optional und richten sich nach den optionalen „Etwas ist wahr“-Argumenten.
Soweit die „Etwas ist wahr“-Argumente nicht die ganze Spannbreite der Daten umfassen, wird als letztes „Etwas ist wahr“-Argument einfach „WAHR“ verwendet und die entsprechende Anweisung „Wert wenn Wahr“ gegeben.
2.2 Beispiele
Beispiel 7
In Beispiel 6 wurde eine Mehrfachabfrage mit einer verschachtelten WENN-Funktion vorgenommen.
Einfacher geht es mit der WENNS-Funktion.
Schreibe dazu in F3 diese Formel:
=WENNS(E3>=100000;10%;E3>=50000;5%;WAHR;0%)
Ziehe die Formel auch hier bis F11 herunter.
Zuerst wird geprüft, ob der Umsatz größer oder gleich 100000 ist. Dafür gibt es 10% Bonus. Ergibt die Prüfung „FALSCH“ wird geprüft, ob der Umsatz größer oder gleich 50000 ist. Ergibt die Prüfung WAHR, gibt es 5% Bonus.
Beispiel 8
In der Tabelle sind sechs Vertreter und ihre Umsätze abgebildet.
Rechts daneben befindet sich eine Übersicht über die umsatzabhängigen Provisionsraten.
In D6:D11 soll nun abgeglichen werden, welcher Vertreter welche Provision bekommt.
Dazu gibst Du in D6 folgende Formel ein:
=WENNS(C6>$F$10;$G$10;C6>$F$9;$G$9;C6>$F$8;$G$8;C6>$F$7;$G$7;WAHR;$G$6)
Ziehe die Formel bis D11 herunter.
Nach der Berechnung sieht die Tabelle so aus:
Wie hat die Formel gearbeitet?
Zuerst wurde der Umsatz in C6 mit dem Grenzwert in F10 (12500 €) abgeglichen. Das ergab FALSCH. Dann wurde der Umsatz in C6 mit dem Wert in F9 (10000 € verglichen. Wieder FALSCH. Die nächste Prüfung, C6>7500 €? Ergibt WAHR, Deshalb wird in D6 die Provisionsrate mit 3% festgesetzt.
Analog werden die Umsätze in C7:C11 mit den Grenzwerten in F6:F10 abgeglichen Im Fall von WAHR werden in D7:D11 die entsprechenden Raten eingetragen.
Als letztes Argument wird für alle bisher nicht beachteten Gegebenheiten „WAHR“ und „$G$6“ angegeben.
Beispiel 9
Ein letztes Beispiel. Bei der Bewältigung der Corona-Krise gab es verschiedene Versuche, mögliche Ladenöffnungsoptionen zu ordnen.
Der Handelsverband NRW hatte dazu eine „Öffnungs-Matrix für eine gerechte und verantwortbare Ladenöffnung“ [1] vorgeschlagen, hier ein Auszug davon:
In der Abbildung wird von einer Belegung der Intensivstationen von > 12% ausgegangen.
Die Tabelle zeigt fünf Orte mit den tagesaktuellen Inzidenzwerten. In Abhängigkeit von diesen Werten soll in Spalte J eingetragen werden, was zulässig ist.
Dazu eignet sich die WENNS-Funktion. In J12 wird die Formel
=WENNS(I12>$B$8;$B$9;I12>$C$8;$C$9;I12>$D$8;$D$9;I12>$E$8;$E$9;I12>$F$8;$F$9)
eingetragen und bis J16 heruntergezogen.
Die Tabelle vervollständigt sich nun. Bei Änderung der Inzidenzwerte werden die zulässigen Maßnahmen jeweils angepasst.
Quellen:
Entdecke mehr von Clevercalcul
Melde dich für ein Abonnement an, um die neuesten Beiträge per E-Mail zu erhalten.