WEnn Wert zwischen Zwei Werten dann
Überblick
Um zu prüfen, ob ein Wert zwischen zwei Werten liegt, benötigen wir die WENN und UND-Funktion, sowie die Vergleichsoperatoren. Wenn ein Wert mit mehreren Wertebereichen verglichen werden soll, kommt die WENNS oder XVERWEIS-Funktion zum Einsatz. Dies wäre zum Beispiel der Fall, wenn wir einer Punktzahl eine bestimmte Note zuordnen wollen.
Formel: Wenn Wert zwischen zwei Werten
=WENN(UND(Ausgangswert>=Von_Wert;Ausgangswert<=Bis_Wert);"Ja";"Nein")
Funktionsweise der Formel
Wir haben einen Ausgangswert, den wir mit einem Wertebereich vergleichen. Dieser Wertebereich wird durch eine Untergrenze und Obergrenze definiert. Einmal durch den Von_Wert, bei dem der Wertebereich beginnt, und dem Bis_Wert, bis dem der Wertebereich endet. Also z.B. von 0 bis 10.
Wenn unser Ausgangswert in diesem Wertebereich liegt, soll etwas passieren. Um dies zu erreichen, müssen wir den Ausgangswert mit den Grenzen des Wertebereichs vergleichen. Unser Ausgangswert muss sowohl größer/gleich dem Von_Wert und kleiner/gleich dem Bis_Wert sein.
Für diesen Verglieich kommen die Vergleichsoperatoren zum Einsatz:
Ausgangswert<=Bis_Wert
Ausgangswert>=Von_Wert
Da beide Vergleiche WAHR sein sollen, nutzen wir hier die UND-Funktion:
UND(Ausgangswert>=Von_Wert;Ausgangswert<=Bis_Wert)
Wenn beide Bedingungen erfüllt sind, wird der Ausdruck WAHR. Wenn unser Ausgangswert außerhalb des Wertebereichen liegt, wird eine der beiden Vergleiche FALSCH und somit der ganze Ausdruck FALSCH.
Jetzt soll noch ein bestimmtes Ergebnis für beide Fälle zurückgegeben werden.
WENN(...;"Ja";"Nein")
Wenn der Ausgangswert im Bereich liegt, wird "Ja", ansonsten "Nein" ausgegeben.
Formel: Wenn WERT innerhalb eines bestimmten Wertebereichs
=WENNS
(
UND(Ausgangswert>=Von_Wert1;Ausgangswert<=Bis_Wert1);Ergebnis_1;
UND(Ausgangswert>=Von_Wert2;Ausgangswert<=Bis_Wert2);Ergebnis_2;
UND(Ausgangswert>=Von_Wert3;Ausgangswert<=Bis_Wert3);Ergebnis_3
)
Funktionsweise der Formel
In der Regel wollen wir einen Ausgangswert nicht nur mit einem, sondern mit mehreren Wertebereichen vergleichen und einen bestimmten Wert zurückgeben. Z.B., wenn ein Schüler eine bestimmte Punktzahl erreicht hat und abhängig davon eine Note bekommen soll.
Hier könnte man jetzt theoretisch mit verschachtelten WENN-Funktionen arbeiten. Diese werden aber schnell unübersichtlich, daher bedienen wir uns der WENNS-Funktion.
Genauso wie in dem Beispiel zuvor vergleichen wir einen Ausgangswert mit den Grenzen eines Wertebereichs, können dies jedoch mehrfach wiederholen und verschiedene Ergebniswerte definieren.
UND(Ausgangswert>=Von_Wert1;Ausgangswert<=Bis_Wert1);Ergebnis_1;
...
Wenn der Ausgangswert mit vielen Wertebereichen verglichen wird, empfiehlt es sich in der Bearbeitungsleiste, wo die Formel steht, mit ALT+ENTER zu arbeiten, um Teile der Formel in neuen Zeilen zu platzieren und somit die Formel übersichtlicher zu gestalten.
Zu beachten
Wenn der Ausgangswert in diesem Beispiel außerhalb der Wertebereiche liegt, wird der #NV-Fehler produziert. Dies kann man umgehen, indem man die gesamte WENNS-Formel noch mit der WENNFEHLER-Funktion umschließt.
=WENNFEHLER(WENNS(...);"außerhalb des Bereichs")
Alternativ kann man auch den ersten und letzten Vergleichsbereich anpassen. Beim ersten Vergleichsbereich entfernen wir einfach die erste Bedingung in der UND-Funktion.
UND(Ausgangswert>=Von_Wert1;Ausgangswert<=Bis_Wert1);Ergebnis_1 ->
Ausgangswert<=Bis_Wert1;Ergebnis_1
Wenn wir dies auf unser Bespiel beziehen werden, jetzt allen Werten kleiner/gleich 10 die Note 6 zugeordnet. Auch wenn die Punktzahl negativ ist.
Beim letzten Vergleichswert entfernen wir die letzte Bedingung in der UND-Funktion.
UND(Ausgangswert>=Von_Wert3;Ausgangswert<=Bis_Wert3);Ergebnis_3 ->
Ausgangswert>=Von_Wert3;Ergebnis_3
Wenn wir dies auf unser Bespiel beziehen werden, jetzt allen Werten größer/gleich 51 die Note 1 zugeordnet.
Sortierte und angrenzende Wertebereiche
Für den Fall, dass die Wertebereiche angrenzend und sortiert sind, kann man auch mit der XVERWEIS-Funktion arbeiten und die "VON"-Einträge weglassen.
=XVERWEIS(Ausgangswert;BIS;DANN;wenn_nicht_gefunden;Vergleichsmodus1)
Normalerweise nutzen wir den XVERWEIS und suchen nach einer genauen Übereinstimmung, hier setzen wir jedoch den Vergleichsmodus auf 1 (exakte Übereinstimmung oder nächst größeres Element). Der XVERWEIS geht jetzt die "BIS"-Einträge von oben nach unten durch und wenn der Wert kleiner ist, wird das nächst höhere Element aus dem "DANN"-Bereich zurückgegeben. Bei einer exakten Übereinstimmung wird das Element aus der gleichen Zeile zurückgegeben.
Ist die Punktzahl z.B. 0 bis 10, also kleiner als oder gleich dem ersten Eintrag 10, wird die Note 6 zugeordnet. Das Ganze funktioniert bis zum Wert 60. Ab 61 wird dann der #NV-Fehler produziert. Diesen Fehlern kann man jedoch bei Bedarf auch durch den wenn_nicht_gefunden Parameter in der Funktion abfangen.
EXCEL, Power BI, ChatGPT
ONLINE TRAINING
