Wie so oft im Leben trifft der Spruch „Alle Wege führen nach Rom“ in ganz besonderer Weise auch auf Excel zu. Bei den allermeisten Problemen führt nämlich nicht nur ein Weg zur Lösung. Der Fantasie und Kreativität des Anwenders sind dabei nur selten Grenzen gesetzt.
Heute will ich anhand eines einfachen Beispiels demonstrieren, wie eine Lösung auf sechs unterschiedlichen Wegen erreicht werden kann. Dabei gibt es nicht „Richtig“ oder „Falsch“. Dieses Beispiel soll dich dazu inspirieren, auch einmal etwas um die Ecke zu denken, gewohntes Terrain zu verlassen und die für dich geeignete Variante zu finden.
Und los geht’s:
Die Schule hat letztlich auch in Bayern wieder begonnen. Daher möchte ich als Beispiel für den heutigen Artikel das deutsche Notensystem verwenden, das den meisten von uns (zumindest auszugsweise) bekannt sein sollte:
Die Notentabelle
Ziel der Aufgabe ist es, zu einer eingegebenen Note die entsprechende Umschreibung auszuwerfen. Dafür zeige ich heute 6 verschieden Wege.
Möglichkeit 1: Die Funktion WENN
Eine naheliegende Möglichkeit bietet die WENN-Funktion. Hier wird eine Bedingung geprüft („Wahrheitstest“). Danach gibt es immer zwei Möglichkeiten. Da wir insgesamt sechs Noten berücksichtigen müssen, wird die WENN-Funktion entsprechend oft verschachtelt:
=WENN(Wahrheitstest;Wert_Wenn_Wahr;Wert_Wenn_falsch)
Auf unser Beispiel angewendet heißt das
WENN der Wert in Zelle A1 = 1, DANN gib „Sehr gut“ aus, SONST
WENN der Wert in Zelle A2 = 2, DANN gib „Gut“ aus, SONST
WENN der Wert in Zelle A3 = 3 …..
Die WENN-Funktion
Da insgesamt nur 6 Noten zur Auswahl stehen, wäre eine solche verschachtelte WENN-Funktion ein möglicher Ansatz. Wie aber leicht zu erkennen ist, werden solche Verschachtelungen schnell unübersichtlich und machen die Formel schwer lesbar.
Möglichkeit 2: Die Funktion WENNS
Für die Anwender von Office-365 gibt es seit geraumer Zeit eine verbesserte WENN-Funktion, die solche Verschachtelungen deutlich vereinfacht:
=WENNS(Wahrheitstest1;Wert_wenn_wahr1;Wahrheitstest2;Wert_wenn_wahr2;...)
Damit sieht unsere Notenabfrage wie folgt aus:
Die WENNS-Funktion
Wie man sieht, ist das Ganze hier deutlich besser lesbar, als die erste Variante mit der normalen WENN-Funktion. Leider gibt es auch ein paar Nachteile:
Der größte Nachteil ist, dass alle Excel-Anwender, nicht kein Office-365 nutzen, mit dem Ofenrohr ins Gebirge schauen. Ihnen bleibt diese Funktion leider vorenthalten. Wie bei allen neuen Funktionen sollten sich Office-365-Anwender überlegen, ob sie ihre Tabellen mit anderen Benutzern austauschen müssen, die dann möglicherweise Probleme haben.
Ein weiterer Nachteil ist, dass man keinen Wert definieren kann, der ausgegeben wird, wenn es überhaupt keine Übereinstimmung gibt. Wird also ein ungültiger Wert gesucht, liefert diese Funktion einen #NV-Fehler zurück.
Möglichkeit 3: Die Funktion ERSTERWERT
Auch diese Funktion gibt es erst ab Excel 2019 (und natürlich in Microsoft 365). Die allgemeine Syntax lautet:
=ERSTERWERT(Ausdruck;Wert1;Ergebnis1;Wert2;Ergebnis2;...;Standardwert)
Damit wird ein Ausdruck ausgewertet (hier also die eingegebene Note) und mit einer Ergebnisliste verglichen. Der erste übereinstimmende Ergebniswert wird dann ausgegeben.
Die ERSTERWERT-Funktion
Nochmal besser lesbar als mit der WENNS-Funktion. Darüber hinaus lässt sich auch ein optionaler Standardwert festlegen, der immer dann ausgegeben wird, wenn es keine Übereinstimmung gibt.
Aber auch hier gilt die oben erwähnte Vorsicht in Bezug auf Datenaustausch mit älteren Excel-Versionen.
Möglichkeit 4: Die Funktion WAHL
In allen Excel-Versionen (zumindest seit Excel 2007) enthalten ist hingegen die WAHL-Funktion. Sich bietet sich für unser Notenbeispiel besonders gut an:
=WAHL(Index;Wert1;Wert2;...)
Es wird also aus einer Liste von Werten derjenige ausgegeben, der dem Index entspricht. Was bei Noten ja sehr praktisch ist, da diese ja einem Index entsprechen.
Die WAHL-Funktion
Das ist aber gleichzeitig auch die größte Einschränkung: Es kann nur mit numerischen, ganzzahligen Werten gearbeitet werden. Damit sind die Anwendungsmöglichkeiten etwas eingeschränkt. Denkbar wären z.B. die Auswertung von Wochentagen (1-7) oder Monaten (1-12).
Einen Artikel mit weiteren Beispielen dazu findest du hier.
Möglichkeit 5: Die Funktion SVERWEIS
Den Klassiker für solche Fälle habe ich mir bis (fast) zum Schluss aufgehoben. Die SVERWEIS-Funktion darf in dieser Aufzählung natürlich nicht fehlen, bietet sie doch die größte Kompatibilität und Flexibilität.
Die SVERWEIS-Funktion
Gerade wenn es um sehr viele Vergleichswerte geht, wird diese Funktion vermutlich die erste Wahl sein.
Einer der wenigen Nachteile:
Der gesuchte Wert muss sich immer in der ersten Spalte der zu durchsuchenden Tabelle befinden. Dadurch ist man im Aufbau der Tabellen etwas eingeschränkt.
Möglichkeit 6: Die Funktion INDEX
Der letzte Kandidat, den ich als Lösungsmöglichkeit vorstelle, ist die INDEX-Funktion. Die Syntax in ihrer einfachsten Form lautet:
=INDEX(Matrix;Zeile)
Es wird also der Wert einer Matrix zurückgegeben, der in der angegebenen Zeile liegt. Bei Noten also sehr praktisch: Note 1 entspricht Zeile 1, Note 2 Zeile 2 usw.
Die SVERWEIS-Funktion
Die INDEX-Funktion für sich alleine kann allerdings nur mit numerischen Werten umgehen. Klar, es wird ja ein Index benötigt. Allerdings bildet diese Funktion zusammen mit VERGLEICH ein echtes Dream-Team, welches sogar den SVERWEIS blass aussehen lässt, wie du hier nachlesen kannst.
Zusammenfassung
Hier nochmal die 6 vorgestellten Funktionen im Vergleich:
Zusammenfassung
Ja nach konkretem Anwendungsfall, persönlichen Vorlieben und natürlich der eingesetzten Excel-Version lässt sich so die geeignete Variante auswählen.
Kennst du noch andere Varianten, um das Notenbeispiel zu lösen? Dann lass es uns unten in den Kommentaren wissen.
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Ähm zu 2. Wenns
Wieso sollte es nicht möglich sein Fehler abzufangen?
Man muss doch lediglich, nachdem man alle abzudeckenden Varianten durchgearbeitet hat, noch eine „Prüfung“ ranhängen die schlicht immer wahr war, der gar keine Übereinstimmung prüft…
Dannach kann man jeden beliebigen Dann-Wert ausgeben.
Dieser Wahrheitswert ist einfach 1.
Dies Wenns Funktion würde also so schießen:
=wenns(……;A1=6;“ungenügend“;1;“Note nicht definiert“)
Wann immer also nun keine 1,2,3,4,5,6 abgefragt wird, kommt der Hinweis.
Hallo Jackie,
ja, du hast vollkommen recht. Wer die Hilfefunktion richtig liest, ist klar im Vorteil…
Danke für den Hinweis,
Martin
Möglich wäre hier auch die „Indirekt-Funktion“.
Bezogen auf die Zusammenfassung wäre das dann:
=INDIREKT(„I“&A1+9)
(Nicht die beste Lösung, aber das Ergbnis stimmt 😉 )
Hallo Lorraine,
auch nicht schlecht, danke für diese sehr kreative Variante!
Schöne Grüße,
Martin
Hallo Martin,
wirklich ein sehr schöner Beitrag (Alle Wege führen nach Rom)!
Der Vollständigkeit halber möchte ich noch BEREICH.VERSCHIEBEN vorstellen, das sich hier auch sehr gut eignet:
=BEREICH.VERSCHIEBEN(H3;A1;0)
Und für nicht-numerische Suchkriterien gäbe es da noch die Kombination aus VERGLEICH und INDEX:
=INDEX(I4:I10;VERGLEICH(B1;H4:H10;0))
Um keine Referenzliste anlegen zu müssen, könnte man die Liste auch als Namen definieren und in den Formeln auf den Namen referenzieren.
Lg
Andreas
Hallo Martin,
deine Seite ist super!
Habe eine Frage zur WENN Funktion.
Ich möchte Arbeitszeiten addieren: Stunden bis 40 Stunden und die Überstunden.
Formel: =WENN(F9>=“40:00″;“40:00″-F9;F9-„40:00“)
Wenn ich über 40:00 komme zeigt er mir keinen Wert mehr an. Den letzten Teil wird nicht erkannt. > und < haben Auswirkungen, jedoch nicht im Negativ-Bereich. Kann man das Schönen?
Danke vorab!
Grüße
Jörg
Hallo Jörg,
freut mich, wenn Dir die Seite gefällt!
Das Rechnen mit Uhrzeiten kann in Excel sehr problematisch/nervig sein und unterliegt einigen Einschränkungen: Es gibt z.B. keine negativen Uhrzeiten, daher kann Excel standardmäßig auch keinen negativen Zeit-Wert zurückliefern. Ein möglicher Ausweg ist die Umstellung auf die 1904-Datumswerte. Zu finden in den Excel-Optionen, Kategorie „Erweitert“. Dort gibt es im Bereich „Beim Berechnen der Arbeitsmappe“ die Option „1904-Datumswerte verwenden“. Damit werden dann auch negative Zeiten dargestellt.
Achtung, diese Einstellung ist mit Vorsicht zu genießen: Denn dadurch verändern sich bereits eingegebene Datumswerte um 4 Jahre! Die Einstellung sollte also geändert werden, bevor man Datumswerte eingibt.
Aber für mich ist diese Einstellung ohnehin nur eine Krücke und ich würde generell nach Möglichkeit die Finger davon lassen. Einen besseren Tipp kann ich leider auch nicht bieten.
Schöne Grüße,
Martin
Moin,
mittlerweile kann man als weitere Funktion auch den Xverweis verwenden,
aber nur bei den aktuellen Office 365 Version nutzbar
freundliche Grüße, Arno
Hallo Arno,
stimmt, danke für die Ergänzung!
Schöne Grüße,
Martin
Hallo Martin,
ich würde gern eine Wenn-Funktion mit von-bis Werten bauen, aber es funktioniert nicht.
Bestimmt ist die Lösung ganz einfach und das Problem sitzt wie immer vor dem Bildschirm 🙂
Also
Wenn der Wert in A1 (eine Summe) zwischen 120 und 179 ist, soll .0 angezeigt werden; wenn der Wert zwischen 180 und 239 ist, soll .1 und alles ab 240 mit .2. Wenn es unter 120 ist „nicht erfüllt“.
Ich bekomme die von bis Werte nicht in die Formel.
Vielen Dank für Tipps.
LG Dagmar aus Chemnitz
Hallo Dagmar,
du von..bis-Werte kannst du mit Hilfe der UND-Funktion abbilden:
=WENN(UND(A1>=120;A1<=179);".0";WENN(UND(A1>=180;A1<=239);".1";WENN(A1>240;“.2″;“nicht erfüllt“)))
Schöne Grüße,
Martin
Lieber Martin,
vielen Dank.
Leider funktioniert es unter 120 und über 240 nicht. Da wird #NAME? angezeigt.
LG Dagmar
Hallo Dagmar,
wenn du die Formel oben per Copy&Paste eingefügt hast, liegt es vermutlich an den Anführungszeichen. Die werden hier im Blog manchmal nicht korrekt dargestellt. Ersetze also in der Formel in Excel einmal die kopierten Anführungszeichen durch manuell eingefügte, dann sollte es hoffentlich funktionieren.
Schöne Grüße,
Martin
Super – vielen Dank!
Dagmar