„Override“-Technik mit Lambda Funktion
Themen/Keywords: Good-Practice in Excel: Formeln nicht überschreiben, LAMBDA: benutzerdefinierte Funktionen ohne VBA/Makros
Eine schlechte Wahl: Überschreiben von Formeln mit manuellen Eingaben
Ein typisches Problem in Excel Eingabemasken besteht darin, dass man dem Nutzer freistellen möchte, ob in einer weiteren Berechnung ein berechneter Wert oder eine manuelle Eingabe verwendet wird.
Klicke hier, um Ihren eigenen Text einzufügen
Der berechnete Wert 112,5 (1,5*75) wird mit einer manuellen Eingabe überschrieben.
Die einfachste Lösung wäre in letzterem Fall, die Zelle mit der Formel mit einer manuellen Eingabe zu überschreiben. Dieses Vorgehen zieht jedoch gleich mehrere Nachteile nach sich:
-
- Es ist an der Eingabe (im Normalfall) optisch nicht erkennbar, ob eine manuelle Eingabe oder ein berechneter Wert weiter verarbeitet wird. Hiergegen könnte durch eine bedingte Formatierung Abhilfe geschafft werden, das ist aber nicht ganz trivial (Tricks zu bedingter Formatierung). Natürlich kann man die Zelle auswählen (aktivieren), um zu sehen, ob sich eine Formel oder ein Wert darin befindet, aber das kann sehr umständlich werden, wenn es um viele Zellen geht. (Eine sehr hilfreiche Excelfunktion zur Überprüfung von Zellformeln wäre ansonsten noch „Formeln anzeigen“, im Menü „Formeln“.)
-
- Wenn es sich – wie so häufig – um eine Liste von mehreren Zeilen oder Datensätzen handelt, geht die Konsistenz der Spaltenformel verloren, da sich in der Spalte keine einheitliche Formel mehr befindet. Dieser Umstand erscheint zunächst nicht dramatisch, ist jedoch eine potentielle Fehlerquelle, vor der Excel daher standardmäßig durch eine kleine grüne Ecke warnt. (Mehr Informationen zu Spalteninkonsistenz)
-
- Wenn man sich umentscheidet und die Eingabe verwerfen möchte, genügt es nicht, den manuellen Wert zu entfernen, man muss dann die Zelle wieder mit der zuvor dort vorhandenen Formel befüllen (z.B. durch Copy-Paste einer Nachbarzelle).
Die bessere Alternative: Trennen von Eingaben und berechneten Werten in separate Zellen
Der bessere Weg für die gewünschte Option ist es, eine leere Zelle vorzuhalten, die bei Bedarf befüllt werden kann und in dem Fall, dass der berechnete Wert verwendet werden soll, einfach leer bleibt.
Beispieltabelle 2: Optionale Eingabezelle
Die Formel in Zelle D6 prüft über eine einfache WENN-Funktion, ob die Zelle C6 leer ist. Falls ja wird der berechnet Wert verwendet, falls jedoch eine Eingabe vorhanden ist, wird stattdessen dieser Wert zur weiteren Berechnung herangezogen. Die Berechnung wird dann durch die Eingabe „übersteuert“, die Zelle funktioniert dadurch also als ovverride.
Die Kür: eine benutzerdefinierte Funktion für das Override
Zelle D6 enhält als Formel:
=WENN(C6=““;A6*B6;C6)
Die quasi gleiche Formel (mit relativen Bezügen auf die Zellen in der gleichen Zeile) befindet sich in den Zellen D4 und D5.
Diese Formel ist relativ einfach und in der Funktionsweise leicht überschaubar. Dennoch könnte man sich bei sehr häufiger Verwendung eine Vereinfachung wünschen, um weniger Parameter einzugeben und die Funktion auch gleich benannt zu haben. Dafür empfiehlt sich eine „benutzerdefinierte Funktion“, welche man in Excel über die LAMBDA-Funktion einfach und ohne VBA oder Makros generieren kann.
Dazu ist nichts weiter nötig, als den gewünschten Namen der neuen Funktion als einen definierten Namen neu anzulegen, z.B. über den Namensmanager (STRG+F3) im Menü „Formeln“.
Die Syntax für diese Funktion ließe sich über die LAMBDA Funktion dann folgendermaßen definieren:

Die vorderen Parameter in der LAMBDA Funktion sind frei benennbare Variablennamen, die im letzten Parameter dann über eine Standard Excel Formel verrechnet werden.
WENN(override=““;default;override) entspricht funktional damit der Formel, die in Spalte D verwendet wurde.
In Zelle D6 sieht die angewendete neue Funktion dann so aus:

Kleiner Tipp: durch Markieren von Bezügen, oder Berechnungen innerhalb einer Formel (hier „A6*B6“), zeigt Excel inzwischen (wie in Google Sheets schon lange…) ein Zwischenergebnis als Tooltip an, hier der vorher berechnete Default-Wert: 112,5, der verwendet würde, wenn in Zelle C6 leer wäre, also kein manueller Preis vorgegeben würde.