Wenn es bei der Lieferung oder Überprüfung eines Modells schnell gehen muss, hast du keine Zeit, alle Daten und Berechnungen manuell zu überprüfen, benötigst aber dennoch eine gewisse Sicherheit. Mit PerfectXL kannst du schnell nach größeren Risiken suchen, wie unterbrochenen Formeln, unvollständigen Verweisen und falscher Nutzung externer Quellen. Du kannst den Scan erweitern oder einschränken, je nach Bedeutung des Modells.
- Lade die Beispieldatei herunter, um alle Schritte nachzuvollziehen.
- Arbeitest du bereits mit PerfectXL? Gehe dann direkt zur Lösung mit PerfectXL.
Schritte ohne PerfectXL
Schritt 1a: Risiken identifizieren
Wenn Sie Zweifel an Berechnungen in einem Modell haben oder mehrere Modelle in kurzer Zeit überprüfen müssen, hilft es, klare Prüfbereiche zu haben. Ohne PerfectXL ist es extrem schwierig, schnell ausreichend Kontrolle über ein Excel-Modell zu erlangen, um Kernrisiken zu identifizieren. Zu prüfende Risiken umfassen unter anderem:
- Approximate Lookup
- Manueller Berechnungsmodus
- Zirkuläre Verweise
- Excel-Fehler
- Nicht befolgte Datenvalidierungsregeln
- Doppelte Zählungen und Operatoren
- Formeln, die durch Daten unterbrochen sind
- Formeln, die durch andere Formeln unterbrochen sind
- Iterative Berechnungen
- Verweis auf leere Zellen
- Numerische Textberechnung
- Unnötiger Leerraum
Schritt 1b: Alle Formeln anzeigen
Klicken Sie auf „Formeln anzeigen“ im Menü „Formeln“, um alle Formeln sichtbar zu machen.
Schritt 1c: Formeln sichtbar machen
Nach dem Aktivieren dieses Modus zeigen die Zellen anstelle des Berechnungsergebnisses nun die Formel an.
Schritt 2: Auf Unregelmäßigkeiten scannen
Sobald die Formeln sichtbar sind, können Sie visuell nach Unregelmäßigkeiten suchen. Wiederholen Sie diesen Prozess für jedes Arbeitsblatt.
Im Beispielmodell mit fünf Arbeitsblättern sind die Zellen mit Formeln zur besseren Übersichtlichkeit gelb markiert.
Auf dem „Summary“-Tab sehen wir neun Formeln, von denen vier auf Zellen aus anderen Arbeitsblättern verweisen und die restlichen fünf einfache Subtraktionen sind. Hier gibt es nichts, das sofortige Aufmerksamkeit erfordert.
Auf dem „Summary“-Tab sehen wir neun Formeln, von denen vier auf Zellen aus anderen Arbeitsblättern verweisen und die restlichen fünf einfache Subtraktionen sind. Hier gibt es nichts, das sofortige Aufmerksamkeit erfordert.
Ähnlich wie bei den nächsten beiden Tabs gibt es keine Unregelmäßigkeiten zu bemerken.
Auf dem letzten Tab – „Operating“ – bemerken wir jedoch etwas, das unsere Aufmerksamkeit erfordert. Spalte D enthält eine Formel, die anscheinend erweitert werden soll, aber sie ist unterbrochen, da die Subtraktion =C[ ]-B[ ] in allen Zellen erscheint, außer in Zelle D8, die eine „0“ enthält. Es scheint, dass die erweiterte Formel durch die Eingabe einer fest codierten Zahl unterbrochen wurde. Das ist wahrscheinlich ein Fehler!
Außerdem gibt es in Zelle D23 einen weiteren Typ der Unterbrechung. Während alle anderen Zellen in der Spalte dem Muster „=C[ ]-B[ ]“ folgen, enthält Zelle D23 die Formel „=C23-B23+30“. Wieder ist eine fest codierte Zahl vorhanden, aber diesmal wird sie als Wert zur Formel hinzugefügt.
Schritt 3: Bewerten und verbessern
Sie als Nutzer müssen entscheiden, ob dieses „+30“ dort sein sollte oder nicht. In unserem Fall stellt das „+30“ unvorhergesehene Kosten dar, aber die Darstellung dieser Kosten auf diese Weise ist unklar. Es wäre besser, eine neue Zeile für unvorhergesehene Kosten hinzuzufügen und die Formel entsprechend zu erweitern.
Da nun eine Zeile in die Tabelle eingefügt wurde, ist es wichtig, in der letzten Zeile, „Total operating expenses“, zu überprüfen, ob der Zellbereich für die Summen noch korrekt ist (d. h. ob die neue Zeile in der Gesamtsumme enthalten ist). Glücklicherweise wurde dies in unserem Beispiel korrekt durchgeführt.
Fazit
Unser Beispiel ist nur ein kleines und sehr überschaubares Modell, aber man kann sich vorstellen, dass eine solche Untersuchung in umfangreicheren Modellen äußerst zeitaufwendig ist. Es dauert Tage, ein Excel-Blatt nach dem anderen, Zelle für Zelle durchzugehen, und oft hat man diese Zeit nicht. Zudem gibt es verschiedene Probleme, die man nicht so einfach sichtbar machen kann, wie zum Beispiel die Lage eines zirkulären Verweises, der Ort von defekten externen Links, versteckte Elemente oder unbeabsichtigte Leerzeichen.
Schritt-für-Schritt PerfectXL:
Step 1: Öffnen des Modells in „PerfectXL Explore“
Prüfen Sie
- Verbindungen zwischen den Arbeitsblättern, die Ihrer Meinung nach notwendig sind
- Ob die richtigen externen Quellen aufgerufen werden
- Ob die Diagramme relevante Informationen enthalten
Die Visualisierung unseres übersichtlichen Beispielmodells zeigt uns, dass sowohl die Arbeitsblätter „Personnel“ als auch „Operating“ auf „Expenses“ verweisen und dass sowohl „Expenses“, als auch „Income“ auf „Summary“ verweisen.
Außerdem sehen wir, dass die externe Quelle „Invoices.xlsx“ vom „Income“-Blatt aufgerufen wird. Das sieht alles gut und richtig aus.
Außerdem sehen wir, dass die externe Quelle „Invoices.xlsx“ vom Blatt „Income“ aufgerufen wird. Das sieht alles in Ordnung aus.
Die Visualisierung unseres übersichtlichen Beispielmodells zeigt uns, dass sowohl die Arbeitsblätter „Personnel“ als auch „Operating“ auf „Expenses“ verweisen und dass sowohl „Expenses“, als auch „Income“ auf „Summary“ verweisen.
Außerdem sehen wir, dass die externe Quelle „Invoices.xlsx“ vom „Income“-Blatt aufgerufen wird. Das sieht alles gut und richtig aus.
Sobald Sie auf „OK“ klicken, führt Sie die Software direkt zu dem Arbeitsblatt, das dann nicht mehr verborgen ist.
Das Arbeitsblatt entpuppt sich als Inhaltsverzeichnis des Modells – was nicht problematisch ist, solange es unverborgen bleibt (also bitte nicht wieder ausblenden!).
Schritt 2: Öffne das Modell im PerfectXL Risk Finder
Filtern Sie unten links nach Risikostufe: „Risk“ und gehen Sie die Risiken nacheinander durch.
Die meisten der Risiken, die Sie antreffen, können direkt Auswirkungen auf das Ergebnis des Modells haben. In einigen Fällen handelt es sich jedoch um Risiken, die nicht sofort fehlerhaft sind.
Sie sollten vor allem Ergebnisse überprüfen, die das Potenzial für einen direkten Fehler haben. „Excel errors“, „Longer Cell Reference“ und „Formula interrupted by data“ sind in diesem Fall sehr relevant.
Wie Sie sehen können, wurde nur ein Fall von „Formula interrupted by data“ in unserem Modell gefunden: In Zelle D8 auf dem Arbeitsblatt „Operating“.
Wenn Sie mehr über diesen Fall erfahren möchten, klicken Sie auf den Fall, um weitere Details anzuzeigen: Ein Ausschnitt des relevanten Arbeitsblatts mit der hervorgehobenen Zelle D8 wird nun im Rahmen angezeigt.
Dank des visuellen Ausschnitts und der Beschreibung ist es einfacher zu erkennen, dass hier ein ernsthaftes Risiko entdeckt wurde. Diese Formel hätte über den gesamten Bereich D4 hinweg erweitert werden müssen!
Elemente wie „Hard Coded Number in Formula“ oder „Many operations“ sind ebenfalls wichtig, aber wenn Sie wirklich wenig Zeit haben, können Sie diese ignorieren. Sie beeinflussen zwar die Qualität des Modells, aber nicht das konkrete Ergebnis einer spezifischen Formel.
Im „PerfectXL Risk Finder“ finden Sie auch Erklärungen, wie mit jedem identifizierten Risiko umzugehen ist. Außerdem gibt es ein Handbuch (Link), das eine detaillierte Beschreibung jedes Elements enthält.