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
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
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
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
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
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
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!
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.
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!
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
Guten Morgen,
eine solche Funktion hätte ich schon längst gebraucht! Danke, dass sie nun aus dem Versteck hervorgeholt wurde!
Gruß Andreas
Sehr gerne geschehen 🙂
Schöne Grüße,
Martin
Ich mache will mit WENN und das verschachtelt.
Der Tipp ist einfach genial. Danke
Gruß Jürgen
Soll viel heißen
Hallo Jürgen,
freut mich, wenn der Tipp weiterhilft.
Schöne Grüße,
Martin
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!
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
Hallo Martin,
vielen Dank für das Vorstellen dieser neuen Funktion. Ich hab versucht sie gleich einzusetzen und scheitere leider.
Ich versuche verschiedene technische Daten zu einer sogenannten Kurzbeschreibung zusammenzufassen. Je nach Form des Artikels benötige ich hier verschiedene Maße. Mit „wenn, wenn“ war das ein Alptraum.
Bisher hier hat alles super funktioniert:
=ERSTERWERT(WAHR;IM9;“F“;TEXTVERKETTEN(„/“;WAHR;“L“&IM17;“W“&IM16;“T“&IM15;“Max“&IM14;IM18;IM20);“RS“;TEXTVERKETTEN(„/“;WAHR;“D“&IM16;“L“&IM17;“T“&IM15;“Max“&IM14;IM18;IM20);“v-cut“;TEXTVERKETTEN(„/“;WAHR;“L“&IM17;“W“&IM16;“T“&IM15;“Max“&IM14;IM20))
Seit ich hierzu erweitert habe, funktioniert es nicht mehr:
=ERSTERWERT(WAHR;IM9;“F“;TEXTVERKETTEN(„/“;WAHR;“L“&IM17;“W“&IM16;“T“&IM15;“Max“&IM14;IM18;IM20);“RS“;TEXTVERKETTEN(„/“;WAHR;“D“&IM16;“L“&IM17;“T“&IM15;“Max“&IM14;IM18;IM20);“v-cut“;TEXTVERKETTEN(„/“;WAHR;“L“&IM17;“W“&IM16;“T“&IM15;“Max“&IM14;IM20);“Rotation“;TEXTVERKETTEN(„/“;WAHR;“D“&IM17;“T“&IM15;“Bore“&IM22;“rotary“;IM11;IM20))
Jetzt macht er aus allem ein „Rotation“ egal was in Feld „IM9“ eingetragen ist.
Kannst du mir sagen, was ich falsch mache? Oder hast du für dieses Problem vielleicht noch eine viel bessere Lösung in Petto?
Ich wäre für Hilfe wirklich dankbar.
MFG Andrea
Hallo Andrea,
die Formel ist für mich gerade etwas schwer zu durchschauen, da ich nicht weiß, was hier für wilde Texte miteinander verkettet werden.
Aber meiner Ansicht nach ist da ein genereller Wurm drin. In der ERSTERWERT-Funktion werden nach dem ersten Parameter (hier: WAHR) immer Paare aus Wert und Ergebnis erwartet:
=ERSTERWERT(Ausdruck;
Wert1;Ergebnis1;
Wert2;Ergebnis2;
Wert3;Ergebnis3;
Standardwert)
In deiner Formel sieht das im Moment so aus:
=ERSTERWERT(WAHR;
IM9;“F“;
TEXTVERKETTEN("/";WAHR;"L"&IM17;"W"&IM16;"T"&IM15;"Max"&IM14;IM18;IM20);"RS";
TEXTVERKETTEN("/";WAHR;"D"&IM16;"L"&IM17;"T"&IM15;"Max"&IM14;IM18;IM20);"v-cut";
TEXTVERKETTEN("/";WAHR;"L"&IM17;"W"&IM16;"T"&IM15;"Max"&IM14;IM20);"Rotation";
TEXTVERKETTEN("/";WAHR;"D"&IM17;"T"&IM15;"Bore"&IM22;"rotary";IM11;IM20))
Diese Paare erscheinen mir nicht logisch.
Wie gesagt, da ich nicht weiß, was mit der Formel genau bezweckt werden soll, kann ich dir keinen besseren Tipp geben, als dir diese Paare nochmal anzusehen.
Schöne Grüße,
Martin
Hallo Martin,
danke für die schnelle Antwort, ohne die Tabelle ist das natürlich schwer nachzuvollziehen.
Es handelt sich um Industriemesser mit verschiedenen Geometrien, und je nach Geometrie soll die Tabelle die unterschiedlichen Maße zusammenfassen.
Vielleicht wird es mit einem Beispiel verständlicher:
„Rotation“ steht für ein Rotationsmesser und bei diesen Messern soll die Tabelle die Maße D(Durchmesser)/T(Messerstärke)/Bore(Aufnahme um das Messer in die Maschine einzuspannen)/rotary/Feld11(Form des Messers)/Feld20(Besonderheiten) zu einem Text zusammenschreiben.
Bei unserem Messer mit der Artikelnummer CE165313 soll die Tabelle dieses Ergebnis ausspucken:
D25/T0,6/Bore6/rotary/round/asym
Es gibt Rotationsmesser, Flachmesser (F), Rundschaftmesser (RS), V-Cut Messer (v-cut)
Zusätzlich HSS Messer, Hochlagen Messer und Sondermesser, die hatte ich noch gar nicht in die Formel eingebaut.
Entweder mache ich mit ERSTERWERT etwas falsch, oder die Funktion ist hier leider auch nicht die richtige. Vielleicht hast du mir eine gute Idee?
Herzliche Grüße
Andrea
Hallo Andrea,
wie ich schon sagte, die Paare in der Funktion erscheinen mir nicht logisch. Wenn ich es nicht falsch verstanden habe, müsste es eher so aussehen:
=ERSTERWERT(WAHR;
IM9=“F“;TEXTVERKETTEN(„/“;WAHR;“L“&IM17;“W“&IM16;“T“&IM15;“Max“&IM14;IM18;IM20);
IM9=“RS“;TEXTVERKETTEN(„/“;WAHR;“D“&IM16;“L“&IM17;“T“&IM15;“Max“&IM14;IM18;IM20);
IM9=“v-cut“;TEXTVERKETTEN(„/“;WAHR;“L“&IM17;“W“&IM16;“T“&IM15;“Max“&IM14;IM20);
IM9=“Rotation“;TEXTVERKETTEN(„/“;WAHR;“D“&IM17;“T“&IM15;“Bore“&IM22;“rotary“;IM11;IM20))
Schöne Grüße,
Martin
Hallo Martin,
danke für deine Antwort, hatte ich ausprobiert, funktioniert leider nicht. Vermutlich, weil Zelle IM9 nicht nur das „F“ sondern noch weitere Daten enthält. Ich konnte mein Problem mit dieser Kombination lösen:
=WENN(ISTZAHL(FINDEN(„F“;IM9));TEXTVERKETTEN(„/“;WAHR;“L“&IM17;“W“&IM16;“T“&IM15;“Max“&IM14;IM18;IM20);WENN(ISTZAHL(FINDEN(„RS“;IM9));TEXTVERKETTEN(„/“;WAHR;“D“&IM16;“L“&IM17;“T“&IM15;“Max“&IM14;IM18;IM20);WENN(ISTZAHL(FINDEN(„Ro“;IM9));TEXTVERKETTEN(„/“;WAHR;“D“&IM17;“T“&IM15;“Bore“&IM22;“rotary“;IM20);WENN(ISTZAHL(FINDEN(„Spe“;IM9));IM20))))
Hallo Andrea,
dann bleibt es wohl bei einer verschachtelten WENN-Formel. Aber am Ende zählt schließlich nur das Ergebnis, und viele Wege führen nach Rom…
Schöne Grüße,
Martin