Undurchschaubare Formeln? Probier’s hiermit: Advanced Formular Environment 17

Artikelbild-319-gross
Ein sehr cooles Add-In hilft beim Verstehen komplexer Formeln
 

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

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

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ü

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

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

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

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

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

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

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!

 

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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

17 Gedanken zu “Undurchschaubare Formeln? Probier’s hiermit: Advanced Formular Environment

  • Avatar-Foto
    Michael Pintscher

    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.

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Andreas Unkelbach

    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.

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Rudolf Perkams

    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.

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Christian Maure

    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..

  • Avatar-Foto
    Andreas Neumann

    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.

    • Avatar-Foto
      Martin Weiß

      Hallo Andreas,

      ja, das ist auch meine Hoffnung (und ich denke, die Chancen stehen gar nicht so schlecht).

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Anett B.

    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.

  • Avatar-Foto
    Dirk Wagner

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Gerald Wallner

    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“))

  • Avatar-Foto
    Gerald Wallner

    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

    • Avatar-Foto
      Martin Weiß

      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