Excel Modelle auditieren: 7 Tricks um komplexe Modelle besser zu verstehen

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…

Comments

  • Hallo, in Office 365 (das ja aktuell auf Office 2013 entspricht) finde ich kein INQUIRE Add-In.

    XLariumNovember 20, 2014
    • Sorry… mein Versehen. Es ist nur bei den Professional Versionen dabei. Siehe auch Antwort die ich zu Andreas’ Kommentar geschrieben habe.

      Lukas RohrNovember 20, 2014
  • Guten Morgen,

    vielen Dank für die Erinnerung an F9. Damit dürften auch hier einige verschachtelte Funktionen wesentlich leichter sein. Die Beschränkung auf eine maximalen Anzahl Zeichen im Formelfeld war mir gar nicht so bewust, allerdings bin ich auch erst vor kurzen auf die Einschränkung von 255 Datenreihen in Diagrammen gestossen :-((. Ich sollte mich vielleicht doch einmal intensiver mit den Limationen der einzelnen Excel-Versionen auseinandersetzen….

    Kleiner Nachtrag:
    Das Inquire-Add-In ist erst ab Office Professional Plus 2013 enthalten.

    Viele Grüße
    Andreas

    Andreas UnkelbachNovember 20, 2014
    • Hi Andreas,
      Recht hast Du… es ist die Professional Version. Sowohl für Office 2013 wie auch Office 365. :-/

      Werde ich im Beitrag gleich korrigieren!

      Gruss, Lukas

      Lukas RohrNovember 20, 2014
  • Schönen guten Morgen,
    ich vermisse in 2013 die Funktionalität der F9-Taste. Sind irgendwo irgendwelche Einstellungen vorzunehmen? Ich raufe mir die Haare und trauere dem guten, alten 2003 nach!
    Ich ersuche um Erleuchtung!

    Mit bestem Dank im Voraus – beste Grüße und schönen Sonntag noch!
    GS

    Gernot SchifflerApril 19, 2015
  • Habe den Hund gefunden – ein aktives “Babylon” hat F9 okkupiert; da habe ich ja doch noch einen schönen Sonntag vor mir!
    Beste Grüße
    GS

    Gernot SchifflerApril 19, 2015
  • Babylon? Meinst Du das hier: http://en.wikipedia.org/wiki/Babylon_%28software%29

    Lukas RohrApril 19, 2015

Leave a Reply