Wer schon einmal komplexe und verschachtelte Formeln in Excel geschrieben hat, kennt das Problem: Sehr schnell geht die Übersicht verloren, wo Klammern fehlen, welche Argumente zu welcher Teilfunktion gehören und so weiter.
Fortgeschrittene Anwender haben in der Vergangenheit schon Wege gefunden, verschachtelte Funktionen auf manuelle Weise etwas übersichtlicher zu gestalten. Die Betonung liegt hier auf „Fortgeschritten“ und „manuell“.
In Microsoft 365 gibt es hier jedoch eine sehr einfache und hilfreiche Alternative!
Verschachtelte Formeln manuell strukturieren
Wenn man mehrere Funktionen in einer Formel verschachtelt, lässt sich durch das manuelle Einfügen von Zeilenumbrüchen etwas mehr Übersicht schaffen.
Dazu bewegt man den Cursor in der Bearbeitungsleiste an die Stelle, an der man den Umbruch einfügen möchte, und drückt dann die Tastenkombination Alt+Eingabe:
Formeln manuell strukturieren
Da nun unter Umständen nicht mehr die ganze Formel vollständig zu erkennen ist, kann man bei Bedarf die Bearbeitungsleiste durch einen Klick auf das Pfeilsymbol ganz rechts einfach vergrößern.
Wem das noch nicht reicht, der kann das Eingabefeld jetzt auch noch weiter nach unten ziehen, indem man die Maus an den unteren Rand der Eingabezeile bewegt, bis sich der Mauszeiger in zwei nach oben und unten zeigende weiße Pfeile ändert.
Für noch mehr Struktur kann man einzelne Zeilen auch durch das Eintippen von Leerzeichen einrücken (nein, die Tabulatortaste funktioniert hier leider nicht).
Aber du siehst schon, es ist Handarbeit nötig. Und bei vielen Formeln heißt das unter Umständen viel Handarbeit.
Verschachtelte Formeln automatisch strukturieren
Glücklich kann sich schätzen, wer Microsoft 365 nutzt. Denn hier lässt sich ein wirklich praktisches Add-In von Microsoft nachinstallieren (sofern diese Möglichkeit nicht von der IT-Abteilung unterbunden wurde).
Öffne dazu das Menü „Einfügen“ und klicke dort die Schaltfläche „Add-Ins abrufen“. Es öffnet sich ein Fenster mit allen möglichen Office-Add-Ins. Am schnellsten findet man das hier Gesuchte, indem man „Excel Labs“ in das Suchfeld tippt:
Add-Ins abrufen: Excel Labs
Ein Klick auf „Hinzufügen“ installiert dann das Add-In.
Nach der Installation findet man im Start-Menü ganz rechts außen eine neue Schaltfläche „Excel Labs“:
Neue Schaltfläche im Start-Menü
Ein Klick darauf öffnet einen neuen Arbeitsbereich mit zwei Einträgen. Der erste davon heißt „Advanced Formula Environment“ und ist genau das, was wir brauchen. Wenn du vor dem Klick auf „Open“ ganz unten noch den Haken setzt „Remember this choice“, dann wird beim nächsten Mal das Advanced Formula Environment (oder kurz: AFE) direkt geöffnet, wenn man auf das Symbol im Menüband klickt.
Der neue Arbeitsbereich
Was kann man nun damit machen?
Nun, eine ganze Menge. Heute soll uns aber nur der, wie ich es nenne, „Formel-Aufhübscher“ interessieren. Dort befindet man sich nach dem Start automatisch, zu erkennen an dem Eintrag „Grid“ oben im Fenster. In der schwarzen Zeile darunter wird angezeigt, in welchem Arbeitsblatt und an welcher Adresse sich die aktive Zelle gerade befindet. In meinem ist das beispielsweise die Zelle A1 im Blatt Tabelle1
Eine leere Zelle
Da es sich hier um eine leere Zelle handelt, ist der weiße Bereich unterhalb der Adressenangabe ebenfalls leer.
Interessant wird es jedoch, wenn sich in der Zelle eine Formel befindet, so wie im folgenden Bild. Diese wird dann automatisch „aufgehübscht“, das heißt in besser lesbare Zeilen umgebrochen und auch formatiert.
Formelzelle wird automatisch strukturiert
Funktionsnamen werden in Blau, Zellenbezüge in Schwarz und Konstanten in Grün dargestellt. Und das passiert vollautomatisch ohne jegliches weitere Zutun. Die ursprüngliche Formel in der Zelle bleibt dabei völlig unverändert. Gegebenenfalls musst du den Arbeitsbereich mit der Maus etwas breiter aufziehen, um ein besser lesbares Ergebnis zu bekommen. Je komplexer oder verschachtelter die Formel ist, desto größer wird der Nutzen des „Formel-Aufhübschers“:
Je komplexer die Formel, desto hilfreicher der Editor
Man muss es aber nicht bei der reinen Anzeige belassen. Vielmehr können Formeln auch direkt im Advanced Formular Environment-Fenster geändert werden. Aber erst wenn man auf das Speichern-Symbol in der schwarzen Adressenleiste klickt, werden die Änderungen auch in die Zelle im Arbeitsblatt zurückgeschrieben.
Formeln können auch geändert werden
Schwächen und Grenzen des Advanced Formular Environments
Natürlich wird eine superkomplexe und verschachtelte Formel nicht weniger komplex, wenn man sie im AFE anzeigen lässt. Aber der Editor hilft zumindest dabei, die einzelnen Bestandteile einer Formel besser auseinanderhalten zu können, sorgt damit für mehr Überblick und hilft, die Formel besser zu verstehen.
Eine echte Schwäche zeigt sich, wenn der AFE auf alte Matrix- oder Array-Formeln angewendet wird. Also solche, die mit Strg+Umschalt+Eingabe abgeschlossen wurden (zu erkennen an den umschließenden geschweiften Klammern). Damit kann der Formel Editor offensichtlich nicht umgehen, denn die Formel wird in einem Stück angezeigt:
Matrix-/Array-Formel wird nicht strukturiert
Der in meinen Augen größte Nachteil ist, dass es sich um ein Add-In handelt. Wer aus welchen Gründen auch immer keine Add-Ins installieren mag, kann oder darf, schaut leider mit dem Ofenrohr ins Gebirge. Dann bleibt nur die manuelle Formatierung, wie zu Beginn des Beitrags beschrieben.
Außerdem handelt es sich um ein sogenanntes „Microsoft Garage Project“, was bedeutet, dass es sich noch um ein experimentelles Feature handelt, welches Änderungen und Entwicklungen unterliegt und zwar hoffentlich irgendwann seinen Einzug in Excel finden wird, aber vielleicht eben auch nicht.
Auch wäre es gerade bei sehr komplexen Formeln hilfreich, wenn man hier zusätzlich erläuternde Kommentare einfügen könnte, die nur im Advanced Formula Environment angezeigt werden. Dies ist (bisher?) leider noch nicht möglich.
Ungeachtet dieser Nachteile handelt es sich in meinen Augen um ein wirklich hilfreiches Instrument, das neben der einfachen Darstellung von Formeln einige weitere Funktionen bietet (auf die ich vielleicht in einem anderen Artikel eingehen werde). Und das ich dir einfach nicht vorenthalten wollte.
Was ist deine Meinung zu diesem Werkzeug? Lass es uns in den Kommentaren wissen!
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.
Tja … wenn das Wörtchen „wenn“ nicht wär …
Wäre sicher was tolles … aber ich (privat hab ich keine Excels mit wirklich komplexen Formeln) gehöre sicher nicht zu den einzigen, die mit dem „Ofenrohr ins Gebirge“ schauen. Weil unserer IT-Security scheint ja manchmal schon das alleinige Hochfahren des Rechners ein suspektes und unternehmensgefährdendes Unterfangen zu sein.
Hallo Michael,
ich verstehe. Manchmal nimmt es die IT sehr genau mit der Security (was grundsätzlich auch nicht verkehrt ist…). Aber für den Anwender ist das halt leider schade.
Schöne Grüße,
Martin
Auf mich wirkt das AFE für die eigene Übersicht bei Formeln hilfreich jedoch frage ich mich, wie es sich bei Fehlern in der Formel verhält (wird hier auch die Zeilennummer angegeben?) und ob es auch eine Assistenzfunktion beim Aufbau von Formeln gibt. Ebenso wäre es toll, wenn sich hier Namensbezüge im AFE auflösen lassen so dass die Fehlersuche erleichtert wird.
Insgesamt bin ich gespannt, ob dies irgendwann aus der Garage doch in den Vorgarten kommt und bin ohnehin auf künftige Entwicklugen gespannt.
Danke auf jeden Fall für die Beschreibung und Blick in die Zukunft.
Hallo Andreas,
ich könnte mir schon vorstellen, dass AFE früher oder später in den regulären Funktionsumfang von Excel übernommen und vielleicht auch um Assistenzfunktionen erweitert wird. Mal sehen, was da noch kommt.
Schöne Grüße,
Martin
Hallo Martin,
als Dein Leser werde ich durch die Tipps immer schlauer! Wie oft habe ich bei der Gestaltung von verschachtelten Formeln erlebt, dass schließende Klammern an falscher Stelle gesetzt wurden und es überraschende Ergebnisse gab.
So ist Deine manuelle Beschreibung der Formel-Übersicht schon praktisch. Die mir bislang unbekannte AFE macht es noch einfacher!
Interessant ist es übrigens: Wenn ich bei bestimmten Excel-Fragen die KI bemühe, wird Deine Excel-Website oftmals als Quelle genannt! Danke daher für Deine Leistungen.
Hallo Rudolf,
danke, freut mich zu hören! Und sehr interessant finde ich deinen Hinweis auf die KI, die kann offensichtlich auch noch etwas lernen 😉
Schöne Grüße,
Martin
Die Funktion finde ich klasse. Mindestens ebenso klasse finde ich, dass es jemanden gibt, der einen über solche Neuerungen informiert. Danke für diesen und deine anderen Artikel..
Hallo Christian,
danke für das tolle Feedback!
Schöne Grüße,
Martin
Auf solch eine Funktion habe ich schon lange gewartet. Bisher habe ich mir bei komplexen Formeln auch nur mit der manuellen Möglichkeit geholfen. Ein Werkzeug wie AFE wäre sehr hilfreich. Leider is auch be mir die Situation: IT hat geblockt. Somit wäre es sehr wünschenswert, wenn die Funktion möglichst bald offiziell zur Verfügung stehen würde.
Hallo Andreas,
ja, das ist auch meine Hoffnung (und ich denke, die Chancen stehen gar nicht so schlecht).
Schöne Grüße,
Martin
Danke Martin für diesen wertvollen Hinweis. Auch bei mir im Geschäft ist das Add-In nicht verfügbar. Ich habe jedoch einen Antrag gestellt und hoffe nun auf einen Bearbeiter, welcher in diesem Add-In einen sinnvollen Nutzen erkennt und dann die weiteren Schritte in die Wege leitet.
Hallo Anett,
dann drücke ich mal die Daumen, wäre ansonsten echt schade.
Schöne Grüße,
Martin
Hallo Martin,
sehr cooles Feature. Leider nicht für Office 2019.
Gibt es den noch andere Alternativen? Ggf. auch ein Externes Programm, welches eine gewisse Strukturierung erlaubt. So ähnlich wie mancher HTML Editor.
Momentan erarbeite ich mir komplexere Formeln im Notepad++. Zwar gibt es dort keine Logik, aber Verschachtelungen kann ich mir ziemlich gut darstellen.
Viele Grüße, Dirk
Hallo Dirk,
nein, mir ist ansonsten kein Programm bekannt, mit dem man Excel-Formeln besser strukturieren kann. Bleibt vermutlich nur die manuelle Variante mit Alt+Eingabe-Taste.
Schöne Grüße,
Martin
schaut schon einmal sehr gut aus, bessere Struktur macht das Bearbeiten von Verschachtelten Excel-Formeln eindeutig einfacher.
Interessant wäre noch, ob die Funktion „Formelauswertung“ auch enthalten ist. Vor allem da die derzeitige Formelauswertung teilweise bei komplexen Formeln irgendwann Schritte überspringt.
Bsp. konnte diese Formel irgendwann nicht mehr „aufgelöst“ werden:
=WENN(UND(ISTFEHLER(SVERWEIS(AN31256;md_Auftr.Gr.!$B:$B;1;0));AN31256″“);“no – unerlaub. Auftr.Gr.“;WENN(AN31256″“;WENN(TEIL(AN31256;2;1)<="1";WENN((S31256-CA31256-CB31256)0;“no – kein KL -> falsch.Auftr.Gr.?“;“ok“);WENN(LINKS(AN31256;2)=“Y2″;WENN(AS31256″RE“;“no – kein RE -> falsch.Auftr.Gr.?“;“ok“);WENN(AN31256=“Y47″;WENN(UND(AQ31256″DIEN“;AX31256″3024″);“no – Mat.Art.DIEN -> Auftr.Gr. falsch“;“ok“);WENN(TEIL(AN31256;2;1)=“4″;WENN(UND(LINKS(AS31256;1)“G“;LINKS(AS31256;1)“L“);“no – keine GS/LS -> falsch.Auftr.Gr.?“;“ok“)))));“ok“))
Hallo Martin,
sorry für eine Unhöflichkeit, wollte mich natürlich auch für deinen hilfreichen Beitrag bedanken – cool.
und einen Fehler meinerseits berichten: die vorhin gepostete Formel hat die „Formelüberwachung“ noch tadellos „geschluckt“, nur mit mehreren verschachtelten „WENNFEHLER“-Anweisungen scheint es Probleme zu geben.
=WENNFEHLER(WENN(SVERWEIS(O54384&“-„&AJ54384;’S:\Abteilungen\KSC\Allgemein\Invoicing\FAKT_CHK\[md_ke24_fakt_chk – 24-06-11.xlsx]ytqtc_local_bu‘!$A:$D;4;0)=AK54384;“ok – TVB/KG“;WENNFEHLER(WENN(SVERWEIS(O54384&“-„&AJ54384&“-„&A54384;’ausn_tvb-kd-kg‘!$A:$H;8;0)=AK54384;“ok – TVB/KG Ausn. TVB/KD“;“no – TVB/KG falsch Ausn. TVB/KD“);WENNFEHLER(WENN(ODER(SVERWEIS(O54384&“-„&AJ54384&“-„&BP54384;’ausn_tvb-lph6-kg‘!A:H;7;0)=AK54384;SVERWEIS(O54384&“-„&AJ54384&“-„&BP54384;’ausn_tvb-lph6-kg‘!A:H;8;0)=AK54384);“ok – TVB/KG Ausn. TVB/LPH6″;“no – TVB/KG falsch Ausn. TVB/LPH6″);“no – TVB/KG falsch“)));“no – TVB/Sparte n. vorh.“)
und natürlich ist das kleine nicht vergrößerbare Fenster in der „Formelüberwachung“ sehr hinderlich.
Schöne Grüße Gerald
Hallo Gerald,
danke, freut mich sehr, wenn dir der Artikel gefallen hat. Das hier vorgestellte AFE hat mittlerweile ein Upgrade bekommen, genauer gesagt eine sogenannte Debug-Funktion. Das entspricht ein wenig der alten Formelauswertung, denn man sieht dann die tatsächlichen Inhalte der jeweiligen Schritte. Und da sich der komplette Arbeitsbereich vom rechten Fensterrand auch lösen lässt, kann man den AFE praktisch beliebig vergrößern und frei auf dem Bildschirm positionieren oder sogar auf einen zweiten Bildschirm legen und gleichzeitig weiter in der Arbeitsmappe manövrieren.
Aber es ist letztendlich auch hier so, dass es ab einem gewissen Punkt einfach unübersichtlich wird.
Schöne Grüße,
Martin