Über 88% aller Tabellen mit mehr als 150 Zeilen weisen Fehler auf. Zu diesem Ergebnis kam der Professor Ray Panko an der University of Hawaii bei einer Studie zu fehlerhaften Tabellen. Damit Eure Excle Files nicht zu diesen 88% gehören, möchte ich Euch heute ein paar Tipps und Tricks zeigen, mit denen Ihr Eure Fehlerquote reduzieren und eine gute Qualität der Daten sicherstellen könnt.

 

Mit Kontrollsummen auf Nummer sicher gehen

Kontrollsummen sind eine einfaches Hilfsmittel um die Richtigkeit der Daten fortlaufend zu überprüfen. Hierbei werden selbst definierte Kriterien und Bereiche berechnet und als Vergleichswert herangezogen.

Die Kontrollsumme bilden, bietet sich besonders in Excel Dateien an, in denen diverse Berechnungen z. B. mittels «summewenn» Funktion stattfinden. Als Beispiel: Wenn der Umsatz das Vergleichskriterium ist und dieser im Jahr 2022 bei 100 Mio. CHF lag, dann muss dieser egal, wie das Excel File bearbeitet wird, immer 100 Mio. CHF ergeben. Sollte er abweichen, kann man davon ausgehen, dass sich ein Fehler eingeschlichen hat.

Hilfreich ist es sich direkt bei der Erstellung einer Tabelle die grobe Struktur zu überlegen und auch an welchen Stellen es sinnvoll sein könnte, die besagten Kontrollsummen zu bilden. Auch wenn das ein aufwendiger Schritt ist, wird er sich jedoch schnell auszahlen, sollte man auf Fehlersuche innerhalb der Datei sein.

 

Farbliche Markierung von Feldern mit Bezügen

In grossen Tabellen nicht den Überblick zu verlieren, ist eine Kunst für sich.

Mit einem Farbschema kann man die Tabelle besser ordnen und Zusammenhänge und Abhängigkeiten sofort erkennen.

Zu diesem Zweck fügt man oberhalb der Überschriftszeile (Abbildung 1) einige leere Zeilen ein. Bei jeder Formel, die einen Matrixbezug enthält, wie beispielsweise ein Sverweis (Links Sverweis), wird nun der Bereich oberhalb der Martix farblich markiert, sowie die Überschrift (Abbildung 2) der Formelspalte selbst.

 

In Abbildung 1 sind die Spalten B-F grün markiert, in Abbildung 2 ist die Überschrift der Formel ebenfalls grün markiert. Man sieht auf einen Blick, dass diese Teile zusammengehören. Zusätzlich gibt es eine kleine Denkstütze, die Formel anzupassen, sollten zusätzliche Spalten zwischen B und F eingefügt worden sein, welche das Ergebnis der Formel verändern würden.

 

Datenüberpüfung

Verschiedene Zellenformate können bei der Summenbildung zu Fehlern führen, was den Outcome der Datei beeinflussen wird.

In der Registerkarte Daten im Bereich Datentool findet man ein eher unscheinbares Symbol (siehe Abbildung 3). Dahinter verbirgt sich das Datenüberprüfungs-Tool. Mit dieser Funktion können wir die Zellenformate festlegen, um Fehleingaben auf Grund von Format auszuschliessen. Hat man zum Beispiel Dezimalzahl als Kriterium festgelegt, können keine Text Werte eingegeben werden. Summenbildungen werden nun nicht mehr gestört durch Textfelder.

 

Auf der Spur der Daten und Formeln

Wem das Farbschema, das weiter oben beschrieben wurde, zu anspruchsvoll ist, kann auch mit der Funktion «Spur zum Vorgänger» oder «Spur zum Nachfolger» die Zusammenhänge anzeigen. Mit «Spur zum Vorgänger»  (Abbildung 5) kann man ein Feld mit Formel anwählen und bekommt mit Pfeilen angezeigt, auf welchen Bereich die Formel verweist.  «Spur zum Nachfolger» (Abbildung 6) stellt Pfeile zwischen der ausgewählten Zelle und den Formeln her, welche auf diese Zelle verweisen.

 

Formelauswertung

Je komplexer und verschachtelter die Formeln werden, desto schwieriger wird, es herauszufinden, warum am Ende der Formel ein #NV oder #Wert als Ergebnis ausgegeben wird.

Auch für diese Situation bietet Excel eine hilfreiche Funktion mit dem Namen «Formelauswertung» Abbildung 7, welche direkt neben dem Button «Spur zum Vorgänger/Nachfolger» zu finden ist. Mit dieser Funktion lässt sich eine Formel Schritt für Schritt berechnen. So können wir identifizieren, ob ein Fehler vorliegt oder ein Wert fehlerhaft ist. Zum Ausführen muss die Zelle mit der Formel angewählt sein und anschliessend wird der Button Formelauswertung gedrückt. In dem erscheinenden Dialogfeld kann die Formel nun Schritt für Schritt berechnet und analysiert werden.

 

 

Fun-Fakts

Für alle, die bis jetzt durchgehalten haben, habe ich noch ein einige Beispiele, was passieren kann, wenn sich Fehler in Excel einschleichen. Wie Ihr sehen könnt, machen selbst die Profis Fehler mit zum Teil weitreichenden Konsequenzen.

Die schlimmsten Excel Fehler aller Zeiten