Wenn wir Finanzmodelle, oder auch jedes andere komplexe Modell das in Excel erstellte wurde, auf dessen Richtigkeit prüfen wollen, so müssen wir uns zwangsläufig die zu Grunde liegenden Formeln zu Gemüte führen.

Anders ausgedrückt: Wir müssen Excel Formeln auditieren!

Excel stellt uns verschieden Werkzeuge zur Verfügung um Formeln zu verstehen. In diesem Beitrag zeige ich die 7 besten Werkzeuge um Excel Modelle zu auditieren.

Werkzeug #1: Spur zum Vorgänger und Nachfolger um Formelbezüge zu visualisieren

Unter dem Formeln Menü findet man die Formelüberwachung Werkzeuge. Hier befinden sich auch die Spur zum Vorgänger und Spur zum Nachfolger Optionen.

Mit der entsprechenden Auswahl kann man nun die relevanten Zellbezüge markieren lassen. In dem man z.B. “Spur zum Vorgänger” ein weiteres mal drückt, werden sogar die Zellen in der erweiterten Beziehungshierarchie angezeigt (siehe auch Werkzeug #6 hierfür).

Werkzeug #2: F2 Taste um Formelbezüge zu visualisieren

Die F2 Taste ermöglicht es uns eine Formel visuelle darzustellen. Diese Technik ist am nützlichsten wenn die Formel auf Zellen verweist die sich auf demselben Arbeitsblatt befinden.

Einfach auf die Zelle klicken in der die Formel steht die man anschauen möchte und F2 drücken. Resultat:

Werkzeug #3: F9 Taste um (Matrix-) Formeln auszuwerten

Markiert man einen Teil einer Formel, z.B. einen Zellenbezug oder eine ganze Formel mit der Maus oder dem Cursor, so kann man F9 drücken und Excel zeigt einem die berechneten Werte.

Diese Technik ist gerade bei Matrix Formeln jeder Art sehr nützlich. Bei Formeln die extrem komplex sind, kommt man ohne die F9 Taste fast nicht weiter!

ESC Benutzen um F9 Auswertung zu verlassen

Wenn Ihr eine Formel mit der F9 Taste auswertet in der Formel-Toolbar, so müsst Ihr achtgeben dass Ihr die Formel durch die ESC Taste verlässt. Wenn Ihr Return drückt, so bleibt der Teil der Formel den Ihr mit F9 ausgewertet habt als Werte in der Formel stehen. Glücklicherweise lässt sich dieser Fehler in den meisten Fällen jedoch mit “Rückgängig machen” wieder korrigieren.

Einschränkung & Workaround

Eine Einschränkung hat diese Technik leider. Will man sehr grosse Matrizen auswerten, so stösst man schnell an die Grenze der maximalen Anzahl Zeichen die im Formelfenster angezeigt werden können.

In diesen Fällen benutze ich einen kleinen Trick: Ich kopiere die Formel in eine andere Zelle, und verkleinere dann die Formelverweise so das nur ein Teilbereich der Originalformel ausgewertet wird. Dies genügt oft schon um zu verstehen was in der Formel passiert.

Werkzeug #4: F5 Taste um schnell zu einer Vorgänger Zelle zu springen

Nicht alle Zellenbezüge in einer Formel befinden sich auf demselben Arbeitsblatt. Zwischen Arbeitsblätter hin und her zu klicken kann mühsam werden, gerade wenn es sich um eine Arbeitsmappe mit vielen Blätter handelt. Auch viel Scrollen bei grosse Arbeitsblätter geht irgendwann auf die Nerven.

F5 hilft weiter!

Man markiert den Zellenbezug in der Formel und drückt F5.

Das “Gehe zu…” Fenster öffnet sich, jedoch mit dem markierten Zellenbezug im Eingabefeld, so dass man lediglich noch…

…Return drücken muss. Und Zack! Wir sind in der neuen Zelle angekommen!

Werkzeug #5: Arbeitsblattbeziehungen verstehen

Dieses Werkzeug, wie auch das in #6, gibt es Standardmässig in Excel 2013 in Office Professional Plus and Office 365 Professional Plus (man muss den INQUIRE Add-In jedoch zuerst laden). Für Excel 2010 oder vorherige Versionen steht dieses Add-In nicht zur Verfügung.

Das Tool funktioniert denkbar einfach. Man geht in die Arbeitsmappe die man auswerten möchte und drückt die Schaltfläche “Arbeitsblattbeziehung”. Es wird ein Beziehungsdiagramm erstellt.

Arbeitsblattbeziehungen Fenster

Hier die Ansicht einer Auswertung auf eine Beispiel Arbeitsmappe mit dem Arbeitsblattbeziehung Werkzeug.

Werkzeug #6: Zellbeziehungen verstehen

Mit dem Zellbeziehungen Werkzeug kann man Zellbezüge auf eine weitere Art und Weise darstellen. Das Tool verhält sich ähnlich wie das “Spur zu Vorgänger und Nachfolger” Werkzeuge, hat jedoch den Vorteil dass es den Sachverhalt auch Blattübergreifend, und sogar Arbeitsmappenübergreifend darstellen kann (im Screenshot hier nicht dargestellt).

Das Diagramm das erstellt wird im Beispielsfall sieht so aus:

Achtung: Excel Absturzgefahr!

Das hier abgebildete Modell ist ein eher einfaches Modell. Jedoch hat Excel bereits hierfür mehrere Sekunden rechnen müssen. Es ist absehbar dass Excel für grosse und komplexe Modelle sehr lange Rechnen muss oder sogar abstürzen kann wenn man “Alle” Beziehungen anschauen möchte. Ich empfehle daher ein wenig zu Testen wie es sich mit der Berechnungszeit auf Eurem Computer verhält.

Werkzeug #7: Dein Intellekt!

Ja ich weiss schon… das ist ein fauler Trick.

Aber es stimmt trotzdem.

Wir fokussieren und verlassen uns oft zu stark auf Werkezuge um uns das Denken abzunehmen! Macht den Fehler nicht dass Ihr Eure Modele nur auf einer Technischen Ebene auditiert. Stellt Euch immer auch noch die Frage ob es überhaupt Sinn macht:

• Was wird hier berechnet?
• Weshalb wird dieser Wert gebraucht?
• Macht es Sinn den Wert mit dieser Formel zu berechnen?
• Etc…