09. May 2019

MS Excel als Inhaltsverzeichnis von Arbeitspapieren nutzen

Hans-Willi Jackmuth

In meiner Projektarbeit erhalte ich oft eine hohe Stückzahl von Dokumenten, die ich nicht nur lesen, sondern auch analysieren muss. Häufig ist dabei notwendig, zwischen Dokumenten hin und her zu „springen“. Auf konventionelle Art lässt sich die Vorgehensweise über die Windows-Dateiverzeichnisse umsetzen. Ich benutze dazu ein kleines Excel, dass ich im Folgenden erkläre und Ihnen als neutrale Datei zum Download zur Verfügung stelle. Dieses Excel nutzt Basisfunktionalitäten und bewusst kein VBA.

Mit verlinkten Dokumenten arbeiten

1. Erläuterung der Grundprinzipien der eingesetzten Formeln:

=ZELLE("dateiname")

Dieser Befehl listet Ihnen den Pfad, die Datei und das Tabellenblatt auf, welches aktuell genutzt wird. Das kann allerdings nur funktionieren, wenn die Datei vorher gespeichert wurde – ansonsten weiß die Maschine natürlich nicht, wohin die neue Datei geschrieben werden soll. Testen Sie doch bitte einmal den Befehl, in dem Sie in eine neue Datei (abspeichern!) diesen Befehl schreiben.

=LINKS/RECHTS("ZELLE") und =SUCHEN("ZEICHEN";ZELLE)

Wir benötigen aus dem Datenstrang in A1 Informationen über den Pfad, dort werden im Endausbau des kleinen Tools die Dateien zu platzieren sein. Wenn Sie mit der Methodik vertraut sind, können Sie auch die Dateien über mehrere Ordner anlegen – da die Formeln dynamisch laufen, kann auch die gesamte Ordnerstruktur inklusive aller Basisdokumente weitergegeben werden, ohne einen Funktionsverlust zu generieren. Diese Vorgehensweise nutze ich regelmäßig zur Weitergabe an Ermittlungsbehörden.

VALUFY - Best Practices für Excel

Best Practices für Excel
Sie möchten auf einen Schlag geballtes Wissen tanken, das Ihr nächstes Audit von 0 auf 100 bringt?

Hier anmelden!

=SUCHEN("["; A1)

Diese Formel sucht die Stelle, an welcher das Zeichen auftritt und gibt als Wert eine Zahl des x. Zeichens (im Beispiel 34) zurück. Wenn wir dies jetzt kombinieren mit links(A1;….) erhalten wir den Pfad.

Zum besseren Verständnis =LINKS(A1;34-1) oder dynamisch =LINKS(A1;SUCHEN("["; A1)-1)

Die Subtraktion mit 1 dient dazu, die Zahl auf 33 zu reduzieren, damit das Zeichen "[" nicht im Pfad angezeigt wird.

Damit kennen wir jetzt sauber den Pfad und können diesen um weitere Angaben ergänzen. Mit der Formel LINKS() können Sie dann auch den Datei- oder Blattnamen bestimmen.

Bestimmen des Datei- oder Blattnamens mit der Formel "LINKS"

Integriert finden Sie alle Formeln dann hier:

2. Aufbereitung als Datei

In meinem Beratungsalltag hat es sich als praktisch erwiesen, die ersten sechs Zeilen frei zu lassen, da man dort neben dem Revisionsauftrag noch optische Dinge wie Unternehmensfarben und Logos etc. gut ergänzen kann. Letztendlich ist das aber eine Frage der Vorgehensweise, die in jedem Unternehmen unterschiedlich ist. Die Beispieldatei enthält diese sechs Zeilen Freiraum, in der 6. Zeile stehen die Überschriften, wobei man den darunter stehenden Bereich wunderbar sortieren, filtern oder anders anordnen kann. Nur die Spalten A-C sind „Pflichtbereiche“.

Beispielhafte Ansicht des Inhaltsverzeichnisses in MS Excel

Zur Erfassung der Daten nutzen wir ein kleines kostenpflichtiges Tool – abbyy Screenshot Reader. Im 10er Pack kostet die Lizenz für das Mini-Tool unter 10 Euro. Dies sollte für alle Professionals zu verschmerzen sein.

Damit können Sie die Dokumentenbezeichnungen „abfotografieren“ und die Maschine liest die Daten per OCR-Scanner in die Zwischenablage. Das funktioniert, sofern keine extrem hohe Auflösung auf dem Bildschirm eingestellt ist, recht zuverlässig. Und man kann das Tool im Prüfungsverlauf bei anderen Tätigkeiten wunderbar zusätzlich benutzen.

3. Verlinkungen herstellen

Für das Verlinken nutze ich einfach das Wort „LINK“. Dynamischere Lösungen (freigewählter Name oder Dateibezeichnung) wären sicher auch denkbar, sind aber für meine Anwendung nicht erforderlich.

=HYPERLINK("DATEIPFAD"&“DOKUMENTENNAME“;"LINK")

Der erste Teil ist die Kombination aus Pfad und Dokumentname – die Formel des Pfads analog Ziffer 1 verkettet mit dem manuell oder toolunterstützt erfassten Dateinamen in Spalte C.

Das Wort „Link“ schreibt sich anschließend in die Zelle, kann angeklickt werden und öffnet das Dokument. Voraussetzung ist natürlich, dass der Pfad und der Name korrekt sind.

Das sieht dann in der Gesamtformel am Beispiel der Zelle B7 des Musterblattes wie folgt aus:

=HYPERLINK(LINKS(ZELLE("dateiname");SUCHEN("["; ZELLE("dateiname"))-1)&C7;"LINK")

Der fett markierte Abschnitt stellt den Dateipfad her, das &C7 verkettet den Dateinamen, die Bezeichnung „LINK“ bildet die Information in der Zelle ab.

Das können Sie dann noch ein wenig perfektionieren – die Maschine soll „LINK“ nur anzeigen, wenn in Spalte C überhaupt erst ein Dokument erfasst ist. Auch diese Funktionalität können Sie auf andere Spalten erweitern.

=WENN(C7 ist nicht leer;“zeige etwas an“;“zeige nichts an“)

=WENN(C7<>““;[obige Hyperlink-Formel];““)

Mit dieser Funktion können Sie eine Zeile schreiben und diese bis ins Unendliche fortschreiben. Aber die Dateinamen sollten so benannt werden, wie Sie sie im konkreten Audit benötigen.

4. Mehrere Ordner bestücken

Gerade in komplexen Fraud Ermittlungen finde ich häufig Themenkomplexe vor, die eine strukturierte Ablage nach Prüfkomplexen in mehreren Ordnern erfordern.

Die Musterformel sollten Sie in diesem Fall (beispielsweise um die Information der Zelle A5) ergänzen und die Blätter anschließend kopieren. Sie können manuell den Ordnernamen dort einfügen und sich die Information aus der Zelle mit einem zusätzlichen „Ordnername\“ ergänzen.

&$A$5&"\"

Das sieht dann in der Formel wie folgt aus:

=WENN(C7<>"";HYPERLINK(LINKS(ZELLE("dateiname");SUCHEN("[";ZELLE("dateiname"))-1) &$A$5&"\"&C7;"LINK");"")

Diese kleine Ergänzung finden Sie im Musterblatt (2) auf Spalte B. Der Pfad ist mit einer absoluten Beziehung eingefroren – dies erleichtert das Kopieren, ohne dass der Zellbezug verloren. Anstelle der Funktion VERKETTEN nutze ich in meiner Projektarbeit häufig das kaufmännische „&“.

Ebenso kann ich natürlich, mit den unter Ziffer 1 gezeigten Methoden, den Blattnamen als Kriterium nutzen. Denken Sie immer daran, die Information muss dann aber gespeichert sein – d.h. Umbenennen des Blattnamens und das Benutzen der Formel sollte einhergehen mit der direkten Speicherung des neuen Blattnamens – ein String S hilft hierbei als Shortcut.

=RECHTS(ZELLE("dateiname");LÄNGE(ZELLE("dateiname"))-SUCHEN("]";ZELLE("dateiname")))

Die Formel liest sich abstrakt wie folgt:

Suche die Position der schließenden Klammer im Datenstrang.

=SUCHEN("]";ZELLE("dateiname"))

Da der Pfadname dynamisch variabel und somit von der Länge nicht fix einzuschätzen ist, nutzt man die Funktion LÄNGE, um die Gesamtlänge des Pfads mit allen Informationen zu ermitteln.

=LÄNGE(ZELLE("dateiname"))

In Kombination mit der SUCHEN-Funktion können Sie nun die Länge des Blattnamens „weg“subtrahieren:

Gesamtlänge minus Position des „]“-Zeichen ergibt den rechten Rand, also den Blattnamen.

5. Einsatz dieser Informationen

Falls Sie die Formeln im Unternehmen einsetzen möchten, empfehle ich Ihnen das beigefügte Excel als Mustervorlage einmalig zu adaptieren.

Ich wünsche Ihnen nun viel Spaß beim Ausprobieren und stehe Ihnen gerne für Fragen oder Anmerkungen zur Verfügung: info@addresults.de