Excel-Datenanalyse - Formelprüfung

Möglicherweise möchten Sie Formeln auf ihre Richtigkeit überprüfen oder die Fehlerquelle ermitteln. Excel Formula Auditing-Befehle bieten Ihnen eine einfache Möglichkeit, sie zu finden

• Welche Zellen tragen zur Berechnung einer Formel in der aktiven Zelle bei?
• Welche Formeln beziehen sich auf die aktive Zelle?

Diese Ergebnisse werden grafisch durch Pfeillinien dargestellt, die die Visualisierung vereinfachen. Sie können alle Formeln im aktiven Arbeitsblatt mit einem einzigen Befehl anzeigen. Wenn sich Ihre Formeln auf Zellen in einer anderen Arbeitsmappe beziehen, öffnen Sie auch diese Arbeitsmappe. Excel kann nicht zu einer Zelle in einer Arbeitsmappe wechseln, die nicht geöffnet ist.

Einstellen der Anzeigeoptionen

Sie müssen überprüfen, ob die Anzeigeoptionen für die von Ihnen verwendeten Arbeitsmappen richtig eingestellt sind.

• Klicken FILE > Options.
• Klicken Sie im Dialogfeld Excel-Optionen auf Erweitert.
• In Anzeigeoptionen für die Arbeitsmappe -
  • Wählen Sie die Arbeitsmappe aus.
  • Überprüfen Sie, ob unter Für Objekte anzeigen die Option Alle ausgewählt ist.
• Wiederholen Sie diesen Schritt für alle Arbeitsmappen, die Sie überwachen.

Präzedenzfälle verfolgen

Präzedenzzellen sind solche Zellen, auf die in der aktiven Zelle durch eine Formel Bezug genommen wird.

Im folgenden Beispiel ist die aktive Zelle C2. In C2 haben Sie die Formel=B2*C4.

B2 und C4 sind Präzedenzfälle für C2.

Um die Präzedenzfälle der Zelle C2 zu verfolgen,

• Klicken Sie in die Zelle C2.
• Klicken Sie auf die Registerkarte Formeln.
• Klicken Sie in der Gruppe Formelüberwachung auf Präzedenzfälle verfolgen.

Es werden zwei Pfeile angezeigt, einer von B2 bis C2 und einer von C4 bis C2, die die Präzedenzfälle verfolgen.

Beachten Sie, dass die Zelle zum Verfolgen von Präzedenzfällen einer Zelle eine Formel mit gültigen Referenzen haben sollte. Andernfalls erhalten Sie eine Fehlermeldung.

• Klicken Sie in eine Zelle, die keine Formel enthält, oder klicken Sie in eine leere Zelle.
• Klicken Sie in der Gruppe Formelüberwachung auf Präzedenzfälle verfolgen.

Sie erhalten eine Nachricht.

Pfeile entfernen

Klicken Sie in der Gruppe Formelüberwachung auf Pfeile entfernen.

Alle Pfeile im Arbeitsblatt verschwinden.

Abhängige aufspüren

Abhängige Zellen enthalten Formeln, die sich auf andere Zellen beziehen. Das heißt, wenn die aktive Zelle zu einer Formel in einer anderen Zelle beiträgt, ist die andere Zelle eine von der aktiven Zelle abhängige Zelle.

Im folgenden Beispiel hat C2 die Formel =B2*C4. Daher ist C2 eine von den Zellen B2 und C4 abhängige Zelle

Um die Abhängigen der Zelle B2 zu verfolgen,

• Klicken Sie in die Zelle B2.
• Klicken Sie auf die Registerkarte Formeln.
• Klicken Sie in der Gruppe Formelüberwachung auf Abhängigkeiten verfolgen.

Von B2 nach C2 wird ein Pfeil angezeigt, der anzeigt, dass C2 von B2 abhängig ist.

Um die Abhängigen der Zelle C4 zu verfolgen -

• Klicken Sie in die Zelle C4.
• Klicken Sie in der Gruppe Formelüberwachung auf die Registerkarte Formel> Abhängigkeiten verfolgen.

Ein weiterer Pfeil erscheint von C4 nach C2 und zeigt, dass C2 auch von C4 abhängig ist.

Klicken Remove Arrowsin der Gruppe Formelüberwachung. Alle Pfeile im Arbeitsblatt verschwinden.

Note- Um Abhängigkeiten einer Zelle zu verfolgen, sollte die Zelle durch eine Formel in einer anderen Zelle referenziert werden. Andernfalls erhalten Sie eine Fehlermeldung.

• Klicken Sie in die Zelle B6 wird von keiner Formel verwiesen oder klicken Sie in eine leere Zelle.
• Klicken Sie in der Gruppe Formelüberwachung auf Abhängigkeiten verfolgen. Sie erhalten eine Nachricht.

Arbeiten mit Formeln

Sie haben das Konzept der Präzedenzfälle und Abhängigen verstanden. Betrachten Sie nun ein Arbeitsblatt mit mehreren Formeln.

• Klicken Sie in der Tabelle Prüfungsergebnisse in eine Zelle unter Bestanden-Kategorie.
• Klicken Sie auf Präzedenzfälle verfolgen. Die Zelle links (Markierungen) und der Bereich E4: F8 werden als Präzedenzfälle zugeordnet.
• Wiederholen Sie diesen Vorgang für alle Zellen unter Bestanden in der Tabelle mit den Prüfungsergebnissen.

• Klicken Sie in eine Zelle unter Pass Category in der Tabelle Student Grades.

• Klicken Sie auf Abhängigkeiten verfolgen. Alle Zellen unter Bestanden der Kategorie in der Tabelle mit den Prüfungsergebnissen werden als abhängige Zellen zugeordnet.

Formeln anzeigen

Das folgende Arbeitsblatt enthält eine Zusammenfassung der Verkäufe der Verkäufer in den Regionen Ost, Nord, Süd und West.

• Klicken Sie auf der Multifunktionsleiste auf die Registerkarte FORMELN.

• Klicken Sie in der Gruppe Formelüberwachung auf Formeln anzeigen. Die Formeln im Arbeitsblatt werden angezeigt, damit Sie wissen, welche Zellen Formeln enthalten und wie die Formeln lauten.

• Klicken Sie in eine Zelle unter TotalSales.

• Klicken Sie auf Präzedenzfälle verfolgen. Am Ende des Pfeils wird ein Arbeitsblattsymbol angezeigt. Das Arbeitsblattsymbol zeigt an, dass sich die Präzedenzfälle in einem anderen Arbeitsblatt befinden.

Doppelklicken Sie auf den Pfeil. EINGo TO Das Dialogfeld mit den Präzedenzfällen wird angezeigt.

Wie Sie sehen, gibt es vier Präzedenzfälle auf vier verschiedenen Arbeitsblättern.

• Klicken Sie auf eine Referenz eines der Präzedenzfälle.
• Die Referenz wird im Feld Referenz angezeigt.
• OK klicken. Das Arbeitsblatt mit diesem Präzedenzfall wird angezeigt.

Eine Formel auswerten

Um herauszufinden, wie eine komplexe Formel in einer Zelle Schritt für Schritt funktioniert, können Sie den Befehl Formel auswerten verwenden.

Betrachten Sie die Formel NPV (Middle Year) in der Zelle C14. Die Formel lautet

=SQRT (1 + C2)*C10

• Klicken Sie in die Zelle C14.
• Klicken Sie auf der Multifunktionsleiste auf die Registerkarte FORMELN.
• Klicken Sie in der Gruppe Formelüberwachung auf Formel auswerten. Das Dialogfeld Formel auswerten wird angezeigt.

In dem Evaluate FormulaIm Dialogfeld wird die Formel im Feld unter Auswertung angezeigt. Durch Klicken auf dieEvaluateWenn Sie die Taste mehrmals drücken, wird die Formel schrittweise ausgewertet. Der Ausdruck mit einer Unterstreichung wird immer als nächstes ausgeführt.

Hier ist C2 in der Formel unterstrichen. Es wird also im nächsten Schritt ausgewertet. KlickenEvaluate.

Zelle C2 hat den Wert 0,2. Daher wird C2 mit 0,2 bewertet.1+0.2wird unterstrichen und als nächster Schritt angezeigt. KlickenEvaluate.

1 + 0,2 wird mit 1,2 bewertet. SQRT(1.2)wird unterstrichen und als nächster Schritt angezeigt. KlickenEvaluate.

SQRT (1.2) wird als 1.09544511501033 bewertet. C10wird unterstrichen und als nächster Schritt angezeigt. KlickenEvaluate.

C10 wird als 4976.8518518515 bewertet.

1.09544511501033 * 4976.8518518515 ist unterstrichen und wird als nächster Schritt angezeigt. KlickenEvaluate.

1.09544511501033 * 4976.8518518515 wird mit 5.451,87 bewertet.

Es sind keine Ausdrücke mehr zu bewerten und dies ist die Antwort. DasEvaluate Schaltfläche wird in geändert Restart Schaltfläche, die den Abschluss der Auswertung anzeigt.

Fehlerüberprüfung

Es wird empfohlen, eine Fehlerprüfung durchzuführen, sobald Ihr Arbeitsblatt und / oder Ihre Arbeitsmappe mit Berechnungen fertig sind.

Betrachten Sie die folgenden einfachen Berechnungen.

Die Berechnung in der Zelle hat zum Fehler # DIV / 0! Geführt.

• Klicken Sie in die Zelle C5.

• Klicken Sie auf der Multifunktionsleiste auf die Registerkarte FORMELN.

• Klicken Sie in der Gruppe Formelüberwachung auf den Pfeil neben Fehlerprüfung. In der Dropdown-Liste finden Sie dasCircular References ist deaktiviert, um anzuzeigen, dass Ihr Arbeitsblatt keine Zirkelverweise enthält.

• Wählen Trace Error aus der Dropdown-Liste.

Die zur Berechnung der aktiven Zelle benötigten Zellen sind durch blaue Pfeile gekennzeichnet.

• Klicken Sie auf Pfeile entfernen.
• Klicken Sie auf den Pfeil neben Fehlerprüfung.
• Wählen Sie in der Dropdown-Liste die Option Fehlerprüfung aus.

Das Error Checking Das Dialogfeld wird angezeigt.

Beachten Sie Folgendes -

• Wenn Sie klicken Help on this errorDie Excel-Hilfe zum Fehler wird angezeigt.

• Wenn Sie klicken Show Calculation StepsDas Dialogfeld Formel auswerten wird angezeigt.

• Wenn Sie klicken Ignore Errorwird das Dialogfeld Fehlerprüfung geschlossen und wenn Sie auf klicken Error Checking Befehl erneut, ignoriert es diesen Fehler.

• Wenn Sie klicken Edit in Formula Barwerden Sie zur Formel in der Formelleiste weitergeleitet, damit Sie die Formel in der Zelle bearbeiten können.