Alle Wege führen nach Rom… 7

Sechs unterschiedliche Lösungen für ein Problem: In Excel führen viele Wege zum Ziel.
 

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

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_fasch)
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

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

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 steht nur Office-365-Anwendern zur Verfügung. 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

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

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 oder auch bei Lukas Rohr.

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

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 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

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.

 

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

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.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

7 Gedanken zu “Alle Wege führen nach Rom…

  • Jackie251

    Ä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.

    • Martin Weiß Autor des Beitrags

      Hallo Jackie,

      ja, du hast vollkommen recht. Wer die Hilfefunktion richtig liest, ist klar im Vorteil…

      Danke für den Hinweis,
      Martin

  • Lorraine

    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 😉 )

  • Andreas

    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

  • Jörg

    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

    • Martin Weiß Autor des Beitrags

      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