Wer die Formel aus einer Zelle sichtbar machen möchte, kann dafür die sehr praktische FORMELTEXT-Funktion nutzen. Die macht genau, was ihr Name andeutet: Die Formel in der angegebenen Zelle wird einfach als Text angezeigt. Leider klappt das immer nur mit einer einzelnen Formelzelle. Was aber, wenn ich eine Übersicht sämtlicher Formeln in einem Arbeitsblatt möchte?
Hierfür gibt es keine Standard-Lösung. Heute zeige ich dir, wie man mit Microsoft 365 (oder Excel 2024) eine Lösung für dieses Problem basteln kann.
Und so geht’s:
Beispieldatei herunterladen
Die Ausgangslage
Meine kleine Beispieldatei enthält 2 Listen und 3 Formeln, alles im Bereich von A1:E22. Die Formeln habe ich hellblau markiert, so dass man das Beispiel leichter nachvollziehen kann:
Einfaches Beispiel mit 3 Formelzellen
Die folgende Lösung für das Problem habe ich vor längerer Zeit bei Mike Girvin gesehen, einem amerikanischen Excel-MVP. Ich fand diesen Trick so beeindruckend, dass ich ihn dir nicht vorenthalten möchte. Ruhm und Ehre gebühren aber Mike Girvin!
Schritt 1: Die FORMELTEXT-Funktion
Die FORMELTEXT-Funktion zeigt also im Klartext die Formel an, die sich in der angegebenen Zelle befindet. Wer beispielsweise Schulungsunterlagen erstellt, kann damit ganz leicht einzelne Formeln für die Schulungsteilnehmer sichtbar machen.
=FORMELTEXT(Zellbezug)
In Excel aus Microsoft 365 kann man sogar einen Zellenbereich angeben (in älteren Excel-Versionen funktioniert das leider nicht). Als Ergebnis erhält man eine Matrix, die genauso groß ist, wie der in der Funktion angegebene Bereich:
=FORMELTEXT(A1:E22)
Hilfstabelle 1 mit der FORMELTEXT-Funktion
Das ist zumindest schon mal ein Anfang. Dort, wo in der Datenquelle Formeln enthalten sind, wird die Formel angezeigt, alle anderen Zellen enthalten einen #NV-Fehler Aber das ist natürlich sehr unübersichtlich. Du kannst dir sicherlich vorstellen, je größer der ausgewählte Zellenbereich ist, desto größer (und unübersichtlicher) wird natürlich auch diese Ausgabetabelle. Außerdem man kann nicht erkennen, in welcher Zelle sich die Formel angezeigte Formel befindet.
Schritt 2: Die Zelladressen bestimmen
Rechts neben meine erste Ausgabematrix erstelle ich jetzt mit Hilfe der ADRESSE-Funktion eine Tabelle mit allen Zelladressen des Quellbereichs. Die Funktion erwartet folgende Argumente:
=ADRESSE(Zeilennummer; Spaltennummer; [Absolut/Relativ]; [Adressenformat]; [Tabellenblatt])
Da ich keine einzelne Zelle verarbeiten möchte, sondern einen größeren Bereich, muss ich für die ersten beiden Argumente auf zwei weitere Funktionen zurückgreifen: ZEILE und SPALTE (nochmal der Hinweis: das funktioniert in dieser Form nur in Microsoft 365!)
=ADRESSE(ZEILE(A1:E22);SPALTE(A1:E22);4)
Hilfstabelle 2 zum Erzeugen aller Zelladressen
Das dritte Argument legt die Art der Adressierung fest: Relativ, absolut oder gemischt. Der Wert 4 steht für relative Bezüge.
Schritt 3: Adressen und Formeln kombinieren
Als nächstes kombiniere ich die beiden erzeugten Ausgabetabellen, damit vor den Formeln die jeweilige Zelladresse steht. Zur besseren Lesbarkeit wird die Adresse durch einen Doppelpunkt von der Formel getrennt:
=M1#&": "&G1#
Hilfstabelle 4 kombiniert die Adressen mit den Formeln
Da es sich dabei um dynamische Bereiche handelt, spreche ich nur die jeweils erste Zelle an, gefolgt von dem #-Zeichen. Dadurch werden die kompletten Tabellenbereiche dynamisch erfasst.
Die Raute: Ein kleines Zeichen mit großer Wirkung!
Schritt 4: Die Matrix in eine einzelne Spalte umwandeln
Kommen wir zum Finale, der Umwandlung der Matrix in eine einspaltige Liste. Dafür brauchen wir die Funktion ZUSPALTE:
=ZUSPALTE(S1#;2)
Die Matrix wird in eine einspaltige Liste umgewandelt
Der Wert 2 in der Funktion besagt, dass Fehler ignoriert werden sollen. Damit sind wir ganz elegant alle #NV-Fehler losgeworden!
Sieht doch gar nicht schlecht aus, oder?
Selbstverständlich können wir auf die ganzen Hilfstabellen verzichten und alles in eine einzige Formel packen:
=ZUSPALTE(ADRESSE(ZEILE(A1:E22);SPALTE(A1:E22);4)&": "&FORMELTEXT(A1:E22);2)
Es geht auch ohne Hilfstabellen
Fazit
Mit ein paar clever kombinierten Funktionen lässt sich in Excel aus Microsoft 365 eine übersichtliche Liste aller im jeweiligen Arbeitsblatt verwendeten Formeln erzeugen. In älteren Excel-Versionen ist das leider nicht möglich.
Zwei weitere Einschränkungen sollen aber auch nicht unerwähnt bleiben:
Hat man mehrere Arbeitsblätter im Einsatz, muss diese Formel auch für jedes Blatt erstellt werden. Außerdem sollte man sich bei der Auswahl des Datenbereichs auch auf den tatsächlich verwendeten Zellbereich beschränken, und nicht einfach das komplette Arbeitsblatt auswählen. Denn das ist zu viel des Guten und wird mit einer Fehlermeldung abgestraft.
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.
Hallo Martin,
das Beispiel für ein strukturiertes Herangehen an eine komplexe Formel ist dir wieder sehr schön gelungen.
In der Praxis benutze ich dafür aber gerne das Add-In ‚Inquire‘, das man sich in den Excel-Optionen als COM-Addin einbinden kann.
Das bietet einem außer der Formelübersicht noch sehr viele weitere Daten zu der Arbeitsmappe.
Hallo,
der Vollständigkeit halber sei auf die Funktion: „Formeln > Formeln anzeigen“ hingewiesen.
Oft ausreichend und nur Klicks, nur die Spaltenbreiten muss man leider anpassen.