4.6 Qualitätssicherung und Fehlervermeidung 

Dieser Abschnitt geht zunächst auf einige Qualitätsmerkmale ein, die beim Aufbau von Kalkulationsmodellen im Blick bleiben sollten und stellt verschiedene Instrumente vor, die Excel für die Fehlervermeidung anbietet.

Überprüfbarkeit 

Bei der Fülle der Befehle und Funktionen lässt sich von niemandem voraussagen, welche Ideen Anwender zur Lösung ihrer Aufgaben entwickeln. Generell kann allerdings gefordert werden, dass die Art und Weise, wie in einem Arbeitsblatt bestimmte Resultate produziert werden, durchsichtig und damit überprüfbar bleibt.

Ein Formelsystem, das der Entwickler selbst nach einem halben Jahr nicht mehr durchschaut, ist sicher nicht wünschenswert. Aber auch andere Benutzer einer Tabelle, z. B. die vorgesetzte Stelle oder die Kollegen, die im Urlaub die Arbeit übernehmen sollen, haben ein Recht auf Durchblick. Bei Tabellen, die steuerrelevante Daten enthalten, müssen zudem die entsprechenden Vorschriften beachtet werden.

Die Möglichkeit, Zellinhalte zu kommentieren, kann genutzt werden, wenn komplexe Berechnungen notwendig sind. Oft ist es auch sinnvoll, direkte Hinweise in der Tabelle abzulegen. Anstatt z. B. einen bestimmten Umrechnungsfaktor in irgendeiner Zelle gleichsam wortlos zu platzieren, sollte davor oder darüber wenigstens der Name des Faktors angegeben werden. Sonst könnte vielleicht jemand auf die Idee kommen, die herrenlos in der Tabelle auftauchende Zahl als überflüssig zu löschen, worauf dann im Extremfall möglicherweise ganze Formelsysteme kollabieren.

Flexibilität 

Neben der Übersichtlichkeit spielt der Grundsatz der Flexibilität eine wichtige Rolle. Anstatt z. B. in Formeln mit einem konstanten Mehrwertsteuersatz zu rechnen, ist es sinnvoller, mit der Adresse einer Zelle zu arbeiten, die den aktuellen Mehrwertsteuersatz enthält. Ändert sich dieser, müssen nicht alle Formeln geändert werden, die diesen Wert benutzen, sondern nur eine Zelle.

Fehlerfreiheit 

Der entscheidende Qualitätskriterium bei einer Kalkulationstabelle ist natürlich die Fehlerfreiheit. Dies betrifft einerseits die Korrektheit der Daten, die in ein Modell einfließen, insbesondere aber die Frage, ob die verwendeten Formeln und Funktionen richtig arbeiten und einwandfreie Ergebnisse liefern.

Fehler in Formeln vermeiden 

Gerade in puncto Fehlervermeidung haben sich die Entwickler besonders angestrengt. Die Instrumente zur Fehlerprüfung sind immer wieder erweitert worden und geben dem Anwender eine größere Sicherheit, zu korrekten Ergebnissen zu gelangen.

Es gibt ganz unterschiedliche Arten von Fehlern, die bei der Arbeit mit Formeln vorkommen. Die unangenehmste Art ist sicher die der logischen Fehler, weil Excel solchen Fehlern von sich aus nichts entgegenzusetzen hat. Wenn Sie z. B. in einer Bedingung UND(PLZ<50000;PLZ>60000) eintragen statt UND(PLZ> 49999;PLZ<60000), wird Excel daran nichts auszusetzen haben, aber Sie wundern sich dann vielleicht, warum das Programm dazu keine Daten findet.

Fehler bei der Formeleingabe 

Günstiger ist die Situation, wenn Sie die vorgeschriebene Schreibweise von Formeln nicht beachten. Excel bietet in vielen Fällen die automatische Korrektur von Fehlern bei der Formeleingabe an. Wenn Sie z. B. einen Ausdruck mit einer öffnenden Klammer eingeben und dann die abschließende Klammer vergessen, schlägt Excel von selbst eine Korrektur mit einer schließenden Klammer vor. Sie können den Vorschlag annehmen oder ablehnen, falls eine Klammer an der falschen Stelle steht. Die folgende Abbildung zeigt ein kleines Beispiel:

Korrekturvorschlag nach einem Fehler in einer Formel

Wird der Korrekturvorschlag nicht angenommen, folgt eine ausführlichere Beschreibung des Fehlers.

Für ein gutes Dutzend von immer wieder vorkommenden Fehlern bietet Excel solche Korrekturvorschläge an. Das gilt z. B. wenn Sie bei Zelladressen die Zeilennummer vor den Spaltenbuchstaben setzen und beispielsweise statt »B6« »6B« eingeben. Auch Leerzeichen an der falschen Stelle werden erkannt, z. B. zwischen Funktionsnamen und erster Klammer, ebenso fehlende Anführungszeichen oder Doppelpunkte an der falschen Stelle.

Zwar kann Excel in einfachen Fällen für fehlende Klammern brauchbare Korrekturvorschläge machen, nicht aber da, wo es mehrere Möglichkeiten gibt, die Klammer zu setzen. Um Ihnen bei der Suche nach der Stelle, an der eine Klammer fehlt oder zu viel ist, zu helfen, zeigt Excel zusammengehörende Klammerpaare vorübergehend in Fettschrift an, wenn Sie mit den Richtungstasten darüberstreichen. Wird etwa eine öffnende Klammer nicht fett angezeigt, heißt das, es fehlt die entsprechende schließende Klammer.

Korrektur von Bezügen mit der Maus 

Bezieht sich eine Formel auf eine falsche Zelle oder einen falschen Zellbereich, lassen sich die Bezüge nachträglich mit der Maus ändern. Klicken Sie doppelt auf die Formelzelle. Excel ordnet den verschiedenen Zell- oder Bereichsbezügen, die in der Formel verwendet werden, unterschiedliche Farben zu. Die entsprechenden Zellen und Bereiche werden in der Tabelle selbst mit entsprechend farbigen Rahmen gekennzeichnet (s. die Abbildung unten). Diese Markierungen lassen sich mit der Maus bewegen, sodass ein falscher Zellbezug oder eine falsche Bereichsangabe für eine Summenformel durch Ziehen und Verschieben korrigiert werden kann. Hier zwei kleine Beispiele:

Markierte Zellbezüge bei der Formelbearbeitung

Die erste Formel multipliziert den VKPreis mit dem Rabattsatz 1 von 70  %. Es soll aber der andere Rabattsatz 2 von 80  % verwendet werden.

2 Ziehen Sie den farbigen Rahmen um die Zelle mit den 70  % auf die Zelle mit den 80  %.

3 Beenden Sie die Korrektur mit .

Korrektur eines Bereichs durch Ziehen mit der Maus

Zweites Beispiel: Sie haben eine Quersumme für eine Zeile gebildet und der Bereich ist nicht korrekt. Es sind nicht alle Zellen in den Bereich aufgenommen worden, die summiert werden sollen.

2 Setzen Sie den Mauszeiger auf die Markierung in der rechten unteren Ecke der farbigen Bereichsmarkierung.

3 Ziehen Sie das Kästchen so weit nach rechts, bis alle Zellen eingeschlossen sind, die summiert werden sollen.

Syntaxprüfung 

Wenn Sie eine Formel eingeben, prüft Excel automatisch, ob die Formel von der Syntax her korrekt ist, bevor das Ergebnis in der Zelle akzeptiert wird. Ist z. B. bei der Funktion GDA ein Argument vergessen worden, erscheint die Fehlermeldung, dass Argumente fehlen. Quittieren Sie die Meldung, verbleibt das Programm in der Bearbeitungsleiste und markiert die Stelle, wo der Fehler liegt.

Durch die schon angesprochenen QuickInfos, die bei der Bearbeitung von Funktionen normalerweise eingeblendet werden, ist die Kontrolle über die Argumente der Funktion einfach geworden. Wenn Sie den Mauszeiger auf ein Argument in einer Funktion setzen oder die Einfügestelle mit den Pfeiltasten bewegen, wird immer der Platzhalter im Quickinfo angezeigt, dem das berührte Argument entspricht.

Kontrolle der Argumente mit Hilfe der Quickinfos

Geben Sie irrtümlich ein Argument zu viel ein, erhalten Sie eine entsprechende Meldung und Sie werden nicht wieder aus der Bearbeitungsleiste losgelassen.

Die Syntaxprüfung überlisten 

Trotz aller Hilfen kann es bei komplexen Formeln, insbesondere bei verschachtelten Funktionen, dazu kommen, dass Sie den Fehler im Augenblick partout nicht entdecken können. Da Excel Sie aber nicht aus der Bearbeitungsleiste freilässt, es sei denn, Sie drücken und verlieren den gesamten Eintrag, sollten Sie Excel überlisten. Gehen Sie mit an den Anfang des Eintrags und tippen Sie einfaches Anführungszeichen ein. Dadurch wandeln Sie die Formel in einen Texteintrag um, der in der Zelle angezeigt wird. Nun haben Sie wenigstens die Chance, sich eine Denkpause zu gönnen, oder Zeit zur Beratung mit einem hilfsbereiten Kollegen – das soll es ja geben. Wenn Sie die Lösung kennen, müssen Sie nur das Anführungszeichen und den Fehler entfernen.

Typische Syntaxfehler: 

• Der Funktionsname ist nicht korrekt geschrieben.

• Die Formel enthält Leerzeichen an Stellen, an denen sie nicht erlaubt sind. Zwischen dem Funktionsnamen und der ersten Klammer darf kein Leerzeichen stehen. Sonst sind Leerzeichen erlaubt.

• Ein logischer Ausdruck ist unvollständig. Es ist z. B. nicht erlaubt, einen Vergleich wie

=UND(B1>C1;>D1)

• • zu schreiben, Sie müssen

=UND(B1>C1;B1>D1)

• • schreiben, weil sonst der zweite Teil des Vergleichs kein gültiger logischer Ausdruck ist.

• Die Anzahl der Argumente für eine Funktion stimmt nicht, es sind zu wenige oder zu viele Argumente.

• Ein Argumenttrennzeichen fehlt zwischen zwei Argumenten.

• Eine Klammer fehlt. Der letzte Fehler taucht vor allem bei verschachtelten Funktionen häufig auf.

Diese Fehler lassen sich weitgehend vermeiden, wenn der Dialog Funktion einfügen zur Eingabe verwendet wird.

Fehler durch Werte 

Excel kann neben den Syntaxfehlern auch Fehler finden, die mit den Werten bzw. Argumenten zu tun haben, mit denen die Formel arbeiten soll. Sind diese für die Formel nicht brauchbar, wird in der Zelle ein Fehlerwert ausgegeben. Excel gibt Ihnen gleich einen erläuternden Hinweis, wenn Sie den Mauszeiger auf die eingeblendete Hinweis-Schaltfläche rücken. In der abgebildeten Formel wird z. B. ein nicht definierter Name verwendet.

Hinweis zum Fehlerwert #Name?

In der folgenden Liste sind die Fehlerwerte und ihre Bedeutung zusammengestellt:

Fehlerüberprüfung im Hintergrund 

Seit Excel 2002 ist eine automatische Fehlerüberprüfung eingebaut, die im Hintergrund abläuft und über das Register Formeln im Excel-Optionen-Dialog gesteuert und bei Bedarf auch deaktiviert werden kann.

Unter Regeln für die Fehlerüberprüfung können Sie festlegen, in welchen Fällen die Fehlerüberprüfung den Fehlerindikator einblenden soll. Die Überprüfung kann nicht nur auf Fehlerwerte in einer Zelle reagieren, sondern auch bei Formeln mit Eigenschaften warnen, die vermuten lassen, dass etwas nicht in Ordnung sein könnte. Das gilt z. B. für Summenformeln, die sich nicht auf alle Zellen im Bereich darüber beziehen. Wählen Sie selbst, wie streng die Prüfung sein soll. Stimmen Sie die Kriterien auch auf Ihre Arbeitsweise ab. Wenn Sie z. B. immer zuerst die Formeln entwickeln, bevor Sie Werte eingeben, sollten Sie das Kriterium Formeln, die sich auf leere Zellen beziehen abwählen.

Einstellungen für die Fehlerüberprüfung

Solange die Fehlerüberprüfung im Hintergrund aktiviert ist, erscheinen bei einer Zelle, die einen Fehlerwert liefert oder eine der hier abgehakten Bedingungen erfüllt, kleine, farbige Fehlerindikatoren. Die Farbe dafür kann über die Schaltfläche unter Fehlerüberprüfung ausgewählt werden. Wird die Zelle markiert, erscheint ein Fehlersymbol. Wird es vom Mauszeiger berührt, finden Sie einen Hinweis zum Fehler und über den Pfeil ein Menü für mögliche Reaktionen auf ihn.

Optionen beim Auftauchen eines Regelverstoßes

Die erste Zeile enthält immer eine kurze Problembeschreibung. Mit Hilfe für diesen Fehler anzeigen finden Sie Details zu dem monierten Tatbestand. Wenn möglich, wird eine Option angeboten, mit der der Fehler direkt korrigiert werden kann. In der Abbildung wird z. B. angeboten, den Bereich der Summenfunktion zu erweitern.

Das Beispiel in der Abbildung ist aber zugleich ein Fall von falschem Alarm. Die Warnung bezieht sich darauf, dass die Zelle mit der Jahreszahl nicht mit in den Summenbereich aufgenommen wird. Da Excel die Bedeutung der Zahl als Jahreszahl nicht automatisch erkennen kann, kommt es zur Warnung. In diesem Fall können Sie mit der Option Fehler ignorieren den Fehlerhinweis abschalten. (Mit der Schaltfläche Zu ignorierende Fehler zurücksetzen unter Fehlerüberprüfung im Excel-Optionen-Dialog lässt sich das auch wieder rückgängig machen.)

4.6.1 Formelüberwachung 

Für die Kontrolle von Formeln stellt Excel 2007 neben der gerade beschriebenen Hintergrundüberprüfung noch eine ganze Reihe von Werkzeugen zur Verfügung, die auf dem Register Formeln in der Gruppe Formelüberwachung zusammengestellt sind.

Werkzeuge für die Kontrolle von Formeln

Manuelle Fehlerprüfung 

Die Fehlerüberprüfung kann hier für jedes Arbeitsblatt auch manuell über die Schaltfläche Fehlerüberprüfung aufgerufen werden, unabhängig davon, ob die Fehlerüberprüfung im Hintergrund aktiviert ist oder nicht. Mit Weiter und Zurück lassen sich die Zellen mit Fehlerwerten oder Warnindikatoren einzeln ansteuern, der Dialog zeigt dann jeweils eine kurze Beschreibung des Fehlers und bietet Schaltflächen für das weitere Vorgehen an.

Schrittweise Fehlerüberprüfung im Blatt

Spuren verfolgen 

Am unangenehmsten sind Fehler, die nicht auffallen, beispielsweise logische Fehler oder Bezüge auf die falsche Zelle. Wenn ein Kalkulationsmodell wächst, wenn mit den Ergebnissen von Formeln in anderen Formeln weitergerechnet wird, kann manchmal die Übersicht verloren gehen. Hier helfen Schaltflächen, die vorübergehend optische Verknüpfungen zwischen Zellbereichen erzeugen.

Wollen Sie prüfen, von welchen Zellen eine gerade ausgewählte Formel abhängig ist, klicken Sie in der Gruppe Formelüberwachung das Symbol Spur zum Vorgänger an. Excel macht die Verbindungen zwischen Zellen mit Pfeilen sichtbar. Das Pfeilende wird jeweils durch einen Punkt gekennzeichnet. Hat der Vorgänger selbst noch einen Vorgänger, klicken Sie erneut auf das Symbol oder wiederholen den Befehl. Sie können dieses Verfahren für mehrere Zellen wiederholen. Es hilft aber nichts, einen Bereich von Zellen zu markieren, die Überwachung arbeitet immer nur für die aktive Zelle.

Wenn Sie die Zelle am Pfeilende markieren wollen, klicken Sie doppelt auf den Spurpfeil. Ein erneuter Doppelklick markiert wieder die Zelle an der Pfeilspitze. Das ist praktisch, wenn Pfeilspitze und Pfeilende weit auseinander liegen.

Wenn Sie eine Zelle mit einem Fehlerwert auswählen, klicken Sie ebenfalls das Symbol Spur zum Vorgänger an, um die Zellen zu finden, die für den Fehler verantwortlich sein können.

Anzeige der Zellen, von denen die Formel in F6 abhängig ist

Wollen Sie umgekehrt wissen, welche Zellen die markierte Zelle beeinflussen, klicken Sie auf das Symbol Spur zum Nachfolger.

Etwas anders sieht es aus, wenn eine Spur in ein anderes Tabellenblatt oder in eine andere Arbeitsmappe führt. (Das zweite ist nur möglich, wenn diese Mappe ebenfalls geöffnet ist.) In beiden Fällen wird ein Pfeil zu oder von einem kleinen Tabellenmuster angezeigt. Wird der Pfeil doppelt angeklickt, erscheint das Dialogfeld Gehe zu mit der Adresse der Nachfolger- bzw. Vorgängerzelle. Ein Doppelklick auf diese Adresse führt zu der entsprechenden Zelle.

Anzeige der Zellen, die von B10 beeinflusst werden

Spuren entfernen 

Werden die Spurpfeile nicht mehr benötigt, können sie über die Schaltfläche Pfeile entfernen in der Gruppe Formelüberwachung entfernt werden. Anstatt alle Pfeile gleichzeitig zu entfernen, können die beiden Spurtypen auch getrennt über das Menü der Schaltfläche entfernt werden.

4.6.2 Werteprüfung im Überwachungsfenster 

In Excel 2007 gibt es auch ein Überwachungsfenster, in dem gezielt die Entwicklung der Werte ausgewählter Zellen verfolgt werden kann. Es wird über die Schaltfläche Formeln/Formelüberwachung/Überwachungsfenster eingeblendet. Zunächst lassen sich dann über die dortige Schaltfläche Überwachung hinzufügen beliebige Zellen oder Zellbereiche auswählen, deren jeweils aktuelle Werte im Überwachungsfenster kontrolliert werden sollen.

Werteprüfung im Überwachungsfenster

Jede Änderung von Werten wird anschließend sofort in diesem Fenster angezeigt. Das ist insbesondere dann vorteilhaft, wenn Zellen überprüft werden, die im Arbeitsblatt selbst weit auseinander liegen oder auf verschiedenen Blättern. Ein Doppelklick auf einen Listeneintrag markiert sofort die betreffende Zelle.

4.6.3 Zirkuläre Formeln 

Unterstützt werden Sie von Excel auch, wenn eine Formel zirkulär ist. Hier ein einfaches Beispiel. In der Zelle B10 ist folgende Funktion eingetragen:

=SUMME(B6:B10)

Die Zelle B10, die die Summe anzeigen soll, gehört selbst zu der Kolonne, die summiert wird. Wenn Sie versuchen, die Summenformel zu bestätigen, erhalten Sie folgende Fehlermeldung:

Fehlermeldung bei Zirkelbezug

In der Statusleiste wird die Zelle angezeigt, die den Zirkelbezug verursacht. Die Zelle, die die zirkuläre Formel enthält, wird auf den Wert Null gesetzt. Auf den Umgang mit zirkulären Formeln wird in Abschnitt 4.9 noch näher eingegangen.

4.6.4 Formeln schrittweise prüfen 

Angenommen, Sie haben eine komplizierte Formel eingegeben und bekommen auch keine Fehlermeldung angezeigt, aber das Ergebnis kann trotzdem nicht stimmen. Wie ist der Fehler zu finden? Eine Möglichkeit, den Fehler einzukreisen, besteht darin, sich für einzelne Teile der Formel jeweils das Ergebnis anzeigen zu lassen. Markieren Sie mit der Maus oder Tastatur den betreffenden Teil der Formel und benutzen Sie dann . Excel zeigt das Ergebnis für diesen Teil der Formel an. Benutzen Sie , um die Formel wieder in den alten Zustand zu versetzen.

Excel 2007 bietet alternativ dazu den Befehl Formelauswertung, den Sie über Formeln/Formelüberwachung erreichen. Im Dialog wird die vorher markierte Formel angezeigt. Mit der Schaltfläche Auswerten lässt sich der jeweils unterstrichene Teil einer Formel berechnen. Einzelschritt zeigt den Wert der markierten Stelle, Prozedurschritt setzt diesen Wert in die Formel ein. Die Formel in der Zelle selbst bleibt bei dieser Prüfung aber im Unterschied zu dem Verfahren mit unverändert.

Schrittweise Auswertung einer verschachtelten Funktion

Ihre Meinung

Wie hat Ihnen das Openbook gefallen? Wir freuen uns immer über Ihre Rückmeldung. Schreiben Sie uns gerne Ihr Feedback als E-Mail an kommunikation@rheinwerk-verlag.de.