Ausweg aus verschachtelten WENN-Abfragen 9

Artikelbild-344
Eine ziemlich unbekannte Funktion hilft unnötige Verschachtelungen zu vermeiden
 

Früher oder später kommt fast jeder Excel-Anwender in diese Situation: Für eine Berechnung müssen eine Reihe von Bedingungen geprüft werden, von denen das jeweilige Ergebnis abhängig ist. Und ehe man sich’s versieht, hat man sich in einem undurchdringlichen Labyrinth aus verschachtelten WENN-Formeln verloren und weiß vor lauter Klammern nicht mehr, wo einem der Kopf steht.

Dabei gibt es verschiedene Alternativen zur klassischen WENN-Funktion. Eine relativ unbekannte Variante stelle ich dir im heutigen Artikel vor.

Und so geht’s:

Beispieldatei herunterladen
Beispieldatei herunterladen

Das Problem

Mit der einfache WENN-Funktion wird eine Bedingung oder ein Wahrheitswert geprüft und davon abhängig ein Wert ausgeben oder eine Berechnung ausgeführt:

=WENN(Wahrheitstest;Wert_wenn_WAHR;Wert_wenn_FALSCH)

Oftmals gibt es aber keine einfache Entweder/Oder-Frage, sondern es gibt weitere Verzweigungen:

  • Falls der heutige Tag ein Montag ist, denn stelle die Restmülltonne raus.
  • Falls es der Dienstag ist, stelle die Biotonne raus.
  • Falls Mittwoch, dann die Papiertonne.
  • Falls Donnerstag, dann gelber Sack.
  • Und an allen anderen Tagen wird kein Müll abgeholt.

Um das mit einer WENN-Funktion auszudrücken, sind mehrere Verschachtelungen notwendig.

Beispiel für verschachtelte WENN-Funktionen

Beispiel für verschachtelte WENN-Funktionen

Wie du siehst, wird das sehr schnell sehr unübersichtlich.

Eine deutlich übersichtlichere Alternative dafür wäre die WENNS-Funktion, die ich vor langer Zeit in diesem Artikel vorgestellt habe.

Bessere Variante mit WENNS

Bessere Variante mit WENNS

Wenn du noch Excel 2016 oder älter einsetzt, dann ist hier leider schon Schluss. Falls du aber Excel 2019 oder neuer im Einsatz hast, gibt es noch eine andere Funktion, die völlig unberechtigterweise ein ziemliches Mauerblümchen-Dasein fristet.

Die Funktion ERSTERWERT

Seit Excel 2019 (und natürlich in Microsoft 365) ist diese relativ unbekannte Funktion verfügbar.
=ERSTERWERT(Ausdruck;Wert1;Ergebnis1;[Standard_oder_Wert2;Ergebnis2...])

Sie funktioniert sehr ähnlich wie die WENNS-Funktion, nur mit dem Unterschied, dass nur ein einziger Ausdruck geprüft wird. Danach folgen beliebige viele (genau genommen sind es insgesamt maximal 126) optionale Paare aus Wert und Ergebnis. Als letztes Argument kann auch ein Standardausdruck festgelegt werden, der in allen anderen Fällen ausgegeben wird. Falls der Standardausdruck weggelassen wird und es bis dahin zu keiner Übereinstimmung gekommen ist, liefert die Funktion einen #NV!-Fehler.

Bezogen auf mein Beispiel mit der Abfallentsorgung sieht das dann folgendermaßen aus:

Noch übersichtlicher und kürzer mit ERSTERWERT

Noch übersichtlicher und kürzer mit ERSTERWERT

Prüfung von Wertebereichen

Was ist, wenn man mit dem Ausdruck nicht einen einzelnen Wert prüfen möchte, sondern einen ganzen Wertebereich? Nehmen wir an, wir wollen eine kleine Staffelpreisliste erstellen. Je höher die Verkaufsmenge, desto günstiger wird der Einzelpreis:

  • ab 50 Stück: 10 Euro
  • ab 25 Stück: 12 Euro
  • ab 10 Stück: 14 Euro
  • unter 10 Stück: 15 Euro

Mit der ERSTERWERT-Funktion könnte das so aussehen:

Prüfung von Wertebereichen mit dem Ausdruck WAHR

Prüfung von Wertebereichen mit dem Ausdruck WAHR

Der Trick dabei ist, dass als erstes Argument WAHR angegeben wird. Bei den darauf folgenden Paaren aus Wert und Ergebnis wird anstelle eines Wert-Arguments einfach der Wertebereich angegeben. Damit wird in meinem Beispiel geprüft, ob der Wert in B1 größer oder gleich 50 ist. Wenn diese Bedingung wahr ist (erstes Argument lautet ja WAHR), dann greift der Preis von 10 Euro. Falls nicht, werden die Mengen mit mindestens 25 oder 10 Stück geprüft. Trifft das auch nicht zu, dann greift der letzte Preis von 15 Euro.

Wichtig ist, dass die Paare in der richtigen Reihenfolge angegeben werden. Denn beim ersten Treffer wird das Ergebnis ausgegeben und die anderen Paare werden nicht mehr geprüft.

Hätten wir also mit der niedrigsten Menge begonnen, käme in manchen Fällen ein falsches Ergebnis heraus, denn nach der ersten Übereinstimmung ist Schluss:

Falsche Reihenfolge bei den Paaren für Werte und Ergebnisse

Falsche Reihenfolge bei den Paaren für Werte und Ergebnisse

Natürlich gäbe es für das Beispiel mit der Staffelpreisliste bessere und flexiblere Alternativen mit Hilfe von Verweisfunktionen und entsprechenden Referenztabellen. Aber ich wollte dir zeigen, wie die ERSTERWERT-Funktion auch genutzt werden könnte.

Vielleicht hast du ja andere Ideen, die sich mit dieser Funktion jetzt einfacher umsetzen lassen. Dann schreib es in die Kommentare!

 

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

9 Gedanken zu “Ausweg aus verschachtelten WENN-Abfragen

  • Avatar-Foto
    Hans Aal

    Guten Morgen,
    habe soeben die neue Ausgabe „Wenn-Labyrinth“ bekommen und gelesen.
    Vielen, vielen Dank für die Funktion „Ersterwert“.
    Diese Funktion werde ich gleich einbauen.
    Bis jetzt habe ich rumgeklüngelt mit der „wenn- und wenns-Funktion“, beide gaben denn regelmässig nicht die richtigen Ergebnisse…

    Euch einen schönen Tag und noch einmal vielen Dank!

    • Avatar-Foto
      Martin Weiß Autor des Beitrags

      Hallo Hans,

      freut mich zu hören, dass der Artikel ein paar neue Anregungen gibt. Vielleicht bringt diese Funktion ja das gewünschte Ergebnis.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Andreas Neumann

    Guten Morgen,
    eine solche Funktion hätte ich schon längst gebraucht! Danke, dass sie nun aus dem Versteck hervorgeholt wurde!
    Gruß Andreas

  • Avatar-Foto
    Matthias

    Hallo,

    super klasse, hab den Blog erst vor kurzem gefunden und freue mich über das Entdecken der Formel so sehr, dass ich einen Kommentar dalassen muss.

    Besten Dank!

    • Avatar-Foto
      Martin Weiß Autor des Beitrags

      Hallo Matthias,

      vielen Dank für dein tolles Feedback. Dann wünsche ich auch weiterhin viel Spaß auf dem Blog!

      Schöne Grüße,
      Martin