Excel ist ein mächtiges Werkzeug für die Datenanalyse und -verarbeitung. Oftmals stehen wir vor der Herausforderung, Berechnungen dynamisch zu gestalten, sodass sich Formeln automatisch anpassen, wenn sich Werte in bestimmten Zellen ändern. Eine besonders interessante und nützliche Technik ist die Verwendung von Zahlen und Operatoren, die in Zellen gespeichert sind, direkt in einer Formel. Dies ermöglicht hochflexible und anpassungsfähige Kalkulationen. In diesem Artikel werden wir verschiedene Methoden und Techniken erkunden, um dieses Ziel zu erreichen, inklusive Fallstricke und Best Practices.
Die Herausforderung: Dynamische Formeln gestalten
Excel-Formeln sind in der Regel statisch. Das bedeutet, dass die Operatoren (z.B. +, -, *, /) und die direkten Zahlenwerte fest in der Formel kodiert sind. Wenn wir eine Zahl oder einen Operator ändern wollen, müssen wir die Formel selbst bearbeiten. Was aber, wenn wir die Zahl oder den Operator aus einer Zelle beziehen wollen? Stellen Sie sich vor, Sie haben eine Zelle (z.B. A1) mit dem Wert „10” und eine andere Zelle (z.B. B1) mit dem Operator „+”. Wie können wir diese Werte nutzen, um eine dynamische Formel zu erstellen, die effektiv =irgendwas + 10 ausführt?
Methode 1: Die EVALUATE Funktion (Makro erforderlich)
Leider bietet Excel keine eingebaute Funktion namens EVALUATE direkt in der Benutzeroberfläche (zumindest nicht in allen Versionen ohne VBA). Die EVALUATE-Funktion erlaubt es, einen Textstring, der eine Excel-Formel darstellt, auszuwerten. Um sie nutzen zu können, müssen wir ein benutzerdefiniertes Makro (VBA-Code) erstellen. Dies ist die klassische, wenn auch etwas komplexere, Methode.
Schritt 1: VBA-Modul hinzufügen
- Öffnen Sie Excel und drücken Sie
Alt+F11, um den VBA-Editor zu öffnen. - Klicken Sie im VBA-Editor auf „Einfügen” -> „Modul”.
Schritt 2: Die EVALUATE Funktion definieren
Fügen Sie folgenden VBA-Code in das Modul ein:
Function EVALUATE(formel As String) As Variant
Application.Volatile
EVALUATE = Application.Evaluate(formel)
End Function
Dieser Code definiert eine benutzerdefinierte Funktion namens EVALUATE, die einen String als Argument entgegennimmt und diesen String als Excel-Formel auswertet. Die Zeile Application.Volatile sorgt dafür, dass die Funktion neu berechnet wird, wenn sich irgendwelche Zellen in der Arbeitsmappe ändern.
Schritt 3: Die Formel in der Zelle verwenden
Angenommen, Zelle A1 enthält die Zahl 10, Zelle B1 enthält den Operator „+”, und Zelle C1 enthält die Zahl 5. In Zelle D1 können Sie nun die folgende Formel verwenden:
=EVALUATE(C1&B1&A1)
Diese Formel verkettet die Werte aus den Zellen C1, B1 und A1 zu einem String („5+10”) und übergibt diesen String an die EVALUATE-Funktion. Die EVALUATE-Funktion wertet diesen String dann als Excel-Formel aus, was das Ergebnis 15 ergibt.
Wichtige Hinweise zur EVALUATE Funktion:
- Die Arbeitsmappe muss als Excel-Arbeitsmappe mit Makros (*.xlsm) gespeichert werden.
- Benutzer müssen Makros in Excel aktivieren, damit diese Funktion funktioniert.
- Die
EVALUATE-Funktion kann potenziell gefährlich sein, da sie beliebigen Code ausführen kann. Verwenden Sie sie mit Vorsicht und stellen Sie sicher, dass die Formeln, die ausgewertet werden, aus vertrauenswürdigen Quellen stammen. - Die
EVALUATE-Funktion ist nicht in allen Excel-Versionen standardmäßig verfügbar, selbst wenn sie via VBA definiert wurde. In einigen neueren Versionen ist sie aus Sicherheitsgründen eingeschränkt.
Methode 2: Indirekte Berechnung mit WÄHLEN und INDIREKT
Eine alternative Methode, die kein VBA erfordert, ist die Kombination der Funktionen WÄHLEN (oder CHOOSE im Englischen) und INDIREKT (oder INDIRECT). Diese Methode ist etwas komplexer, aber sie bietet eine sicherere Alternative, da sie keine benutzerdefinierten Makros benötigt.
Schritt 1: Operatoren zuordnen
Zunächst müssen wir jedem Operator eine Zahl zuordnen. Zum Beispiel:
- 1: „+”
- 2: „-„
- 3: „*”
- 4: „/”
Schritt 2: Die WÄHLEN Funktion verwenden
Die WÄHLEN Funktion gibt einen Wert aus einer Liste von Werten zurück, basierend auf einem Index. Angenommen, Zelle B1 enthält die Zahl, die den Operator repräsentiert (z.B. 1 für „+”). Dann können wir die WÄHLEN Funktion verwenden, um den tatsächlichen Operator zu ermitteln.
Schritt 3: Die INDIREKT Funktion (oder deren Alternativen)
Die INDIREKT Funktion wird hier weniger direkt gebraucht, sondern eher als Platzhalter, um die Formel dynamisch zu gestalten. Es geht darum, die Werte aus den Zellen A1, B1 und C1 (Zahl, Operator-Code, Zahl) in einer komplexeren Formel zusammenzufügen. Die Schwierigkeit liegt darin, dass Excel nicht einfach `A1 B1 C1` als Formel akzeptiert.
Hier ist ein Beispiel, wie die Formel aussehen könnte, angenommen Zelle A1 enthält die erste Zahl, Zelle B1 den Operator-Code (1-4), und Zelle C1 die zweite Zahl:
=WENN(B1=1,A1+C1,WENN(B1=2,A1-C1,WENN(B1=3,A1*C1,WENN(B1=4,A1/C1,"Ungültiger Operator"))))
Diese Formel verwendet eine verschachtelte WENN-Abfrage, um den richtigen Operator basierend auf dem Wert in Zelle B1 auszuwählen. Wenn B1 gleich 1 ist, wird A1 + C1 berechnet, wenn B1 gleich 2 ist, wird A1 – C1 berechnet, und so weiter. Wenn B1 einen ungültigen Wert enthält, wird die Meldung „Ungültiger Operator” angezeigt.
Vorteile und Nachteile dieser Methode:
- Vorteil: Keine Makros erforderlich, daher sicherer.
- Nachteil: Formel kann sehr lang und unübersichtlich werden, insbesondere bei vielen Operatoren. Weniger flexibel als die
EVALUATE-Funktion.
Methode 3: Die POWER QUERY (Get & Transform Data) Lösung
Eine modernere und oft elegantere Lösung ist die Verwendung von Power Query (in älteren Excel-Versionen „Get & Transform Data”). Power Query ermöglicht es, Daten aus verschiedenen Quellen zu importieren, zu transformieren und zu laden. Wir können diese Funktionalität nutzen, um die dynamische Berechnung zu erstellen.
Schritt 1: Daten in Power Query laden
- Wählen Sie den Datenbereich mit den Zahlen und Operatoren aus (z.B. A1:C1).
- Gehen Sie zu „Daten” -> „Aus Tabelle/Bereich”. Dies öffnet den Power Query Editor.
Schritt 2: Benutzerdefinierte Spalte hinzufügen
- Im Power Query Editor klicken Sie auf „Spalte hinzufügen” -> „Benutzerdefinierte Spalte”.
- Geben Sie einen Namen für die neue Spalte ein (z.B. „Ergebnis”).
- Im Feld „Benutzerdefinierte Spaltenformel” geben Sie die Formel ein, die die Berechnung durchführt. Dies erfordert etwas M-Code (die Power Query Formelsprache).
Die M-Code-Formel wird komplexer sein, da Power Query nicht direkt Text als Formel auswerten kann. Sie müssen eine ähnliche Logik wie bei der WÄHLEN Funktion implementieren, aber mit der if then else Syntax von M.
Beispiel für eine M-Code Formel (angenommen, die Spalten heißen „Zahl1”, „Operator”, „Zahl2”):
if [Operator] = "+" then [Zahl1] + [Zahl2]
else if [Operator] = "-" then [Zahl1] - [Zahl2]
else if [Operator] = "*" then [Zahl1] * [Zahl2]
else if [Operator] = "/" then [Zahl1] / [Zahl2]
else null
Schritt 3: Daten in die Excel-Arbeitsmappe laden
- Klicken Sie im Power Query Editor auf „Schließen & Laden” -> „Schließen & Laden in…”.
- Wählen Sie aus, wo die Ergebnisse in der Arbeitsmappe platziert werden sollen.
Vorteile und Nachteile dieser Methode:
- Vorteil: Sehr flexibel und leistungsstark, insbesondere bei komplexen Berechnungen und großen Datenmengen. Daten können leicht aktualisiert werden.
- Nachteil: Erfordert Kenntnisse der Power Query und der M-Code Formelsprache. Kann für einfache Berechnungen übertrieben sein.
Fazit
Die dynamische Berechnung in Excel mit Zahlen und Operatoren aus Zellen ist eine anspruchsvolle, aber lohnende Aufgabe. Die EVALUATE Funktion (mit VBA) ist die direkteste Methode, aber sie birgt Sicherheitsrisiken und funktioniert nicht in allen Excel-Versionen. Die Kombination von WÄHLEN und WENN bietet eine sicherere Alternative, ist aber weniger flexibel. Power Query ist eine moderne und leistungsstarke Lösung, die sich besonders für komplexe Berechnungen und große Datensätze eignet. Die Wahl der Methode hängt von Ihren spezifischen Anforderungen und Ihrem Kenntnisstand ab. Experimentieren Sie mit den verschiedenen Techniken, um die beste Lösung für Ihr Problem zu finden. Berücksichtigen Sie stets die Sicherheit und die Wartbarkeit Ihrer Formeln.