Excel Data Analysis - Formula Auditing

Potresti voler controllare l'accuratezza delle formule o trovare l'origine di un errore. I comandi di controllo delle formule di Excel forniscono un modo semplice per trovare

  • Quali celle stanno contribuendo al calcolo di una formula nella cella attiva.
  • Quali formule si riferiscono alla cella attiva.

Questi risultati sono mostrati graficamente da linee di freccia che rendono facile la visualizzazione. È possibile visualizzare tutte le formule nel foglio di lavoro attivo con un unico comando. Se le tue formule fanno riferimento a celle in una cartella di lavoro diversa, apri anche quella cartella di lavoro. Excel non può accedere a una cella in una cartella di lavoro che non è aperta.

Impostazione delle opzioni di visualizzazione

È necessario verificare se le opzioni di visualizzazione per le cartelle di lavoro in uso sono impostate correttamente.

  • Clic FILE > Options.
  • Nella finestra di dialogo Opzioni di Excel fare clic su Avanzate.
  • In Opzioni di visualizzazione per la cartella di lavoro -
    • Seleziona la cartella di lavoro.
    • Verificare che in Per gli oggetti, mostra, sia selezionato Tutto.
  • Ripeti questo passaggio per tutte le cartelle di lavoro che stai controllando.

Rintracciare precedenti

Le celle precedenti sono quelle celle a cui fa riferimento una formula nella cella attiva.

Nell'esempio seguente, la cella attiva è C2. In C2, hai la formula=B2*C4.

B2 e C4 sono celle precedenti per C2.

Per tracciare i precedenti della cella C2,

  • Fare clic nella cella C2.
  • Fare clic sulla scheda Formule.
  • Fare clic su Traccia precedenti nel gruppo Controllo formula.

Verranno visualizzate due frecce, una da B2 a C2 e un'altra da C4 a C2, tracciando le precedenti.

Nota che per tracciare i precedenti di una cella, la cella dovrebbe avere una formula con riferimenti validi. In caso contrario, riceverai un messaggio di errore.

  • Fare clic in una cella che non contiene una formula o fare clic in una cella vuota.
  • Fare clic su Traccia precedenti nel gruppo Controllo formula.

Riceverai un messaggio.

Rimozione delle frecce

Fare clic su Rimuovi frecce nel gruppo Controllo formula.

Tutte le frecce nel foglio di lavoro scompariranno.

Tracing Dependents

Le celle dipendenti contengono formule che fanno riferimento ad altre celle. Ciò significa che se la cella attiva contribuisce a una formula in un'altra cella, l'altra cella è una cella dipendente dalla cella attiva.

Nell'esempio seguente, C2 ha la formula =B2*C4. Pertanto, C2 è una cella dipendente dalle celle B2 e C4

Per tracciare i dipendenti della cella B2,

  • Fare clic nella cella B2.
  • Fare clic sulla scheda Formule.
  • Fare clic su Trace Dependents nel gruppo Formula Auditing.

Viene visualizzata una freccia da B2 a C2, che mostra che C2 dipende da B2.

Per tracciare i dipendenti della cella C4 -

  • Fare clic nella cella C4.
  • Fare clic sulla scheda Formula> Traccia dipendenti nel gruppo Controllo formula.

Un'altra freccia appare da C4 a C2, mostrando che anche C2 dipende da C4.

Clic Remove Arrowsnel gruppo Formula Auditing. Tutte le frecce nel foglio di lavoro scompariranno.

Note- Per tracciare i dipendenti di una cella, la cella deve essere referenziata da una formula in un'altra cella. In caso contrario, riceverai un messaggio di errore.

  • Fare clic nella cella B6 non è referenziato da alcuna formula o fare clic in nessuna cella vuota.
  • Fare clic su Trace Dependents nel gruppo Formula Auditing. Riceverai un messaggio.

Lavorare con le formule

Hai capito il concetto di precedenti e dipendenti. Ora, considera un foglio di lavoro con diverse formule.

  • Fare clic in una cella sotto Categoria superata nella tabella Risultati esame.
  • Fare clic su Trace Precedents. La cella alla sua sinistra (segni) e l'intervallo E4: F8 verranno mappati come precedenti.
  • Ripetere per tutte le celle in Categoria superata nella tabella Risultati esame.

  • Fare clic in una cella sotto Categoria di passaggio nella tabella Voti degli studenti.

  • Fare clic su Trace Dependents. Tutte le celle in Categoria superata nella tabella Risultati esame verranno mappate come dipendenti.

Mostrando le formule

Il foglio di lavoro seguente contiene il riepilogo delle vendite dei venditori nelle regioni Est, Nord, Sud e Ovest.

  • Fare clic sulla scheda FORMULE sulla barra multifunzione.

  • Fare clic su Mostra formule nel gruppo Controllo formule. Appariranno le formule nel foglio di lavoro, così saprai quali celle contengono formule e quali sono le formule.

  • Fare clic in una cella sotto TotalSales.

  • Fare clic su Trace Precedents. Alla fine della freccia viene visualizzata un'icona del foglio di lavoro. L'icona del foglio di lavoro indica che i precedenti si trovano in un foglio di lavoro diverso.

Fare doppio clic sulla freccia. UNGo TO appare la finestra di dialogo che mostra i precedenti.

Come osservi, ci sono quattro precedenti, su quattro diversi fogli di lavoro.

  • Fare clic su un riferimento di uno dei precedenti.
  • Il riferimento viene visualizzato nella casella Riferimento.
  • Fare clic su OK. Viene visualizzato il foglio di lavoro contenente tale precedente.

Valutazione di una formula

Per scoprire passo dopo passo come funziona una formula complessa in una cella, puoi utilizzare il comando Valuta formula.

Considera la formula NPV (anno centrale) nella cella C14. La formula è

=SQRT (1 + C2)*C10

  • Fare clic nella cella C14.
  • Fare clic sulla scheda FORMULE sulla barra multifunzione.
  • Fare clic su Valuta formula nel gruppo Controllo formula. Viene visualizzata la finestra di dialogo Valuta formula.

Nel Evaluate Formulafinestra di dialogo, la formula viene visualizzata nella casella sotto Valutazione. Facendo clic suEvaluatepiù volte, la formula viene valutata passo passo. L'espressione con una sottolineatura verrà sempre eseguita successivamente.

Qui, C2 è sottolineato nella formula. Quindi, viene valutato nel passaggio successivo. ClicEvaluate.

La cella C2 ha valore 0,2. Quindi, C2 sarà valutato come 0,2.1+0.2è sottolineato mostrandolo come passaggio successivo. ClicEvaluate.

1 + 0,2 sarà valutato come 1.2. SQRT(1.2)è sottolineato mostrandolo come passaggio successivo. ClicEvaluate.

SQRT (1.2) sarà valutato come 1.09544511501033. C10è sottolineato mostrandolo come passaggio successivo. ClicEvaluate.

C10 sarà valutato come 4976.8518518515.

1.09544511501033 * 4976.8518518515 è sottolineato e lo mostra come passaggio successivo. ClicEvaluate.

1.09544511501033 * 4976.8518518515 sarà valutato come 5.451.87.

Non ci sono più espressioni da valutare e questa è la risposta. IlEvaluate il pulsante verrà modificato in Restart pulsante, che indica il completamento della valutazione.

Controllo degli errori

È buona norma eseguire un controllo degli errori una volta che il foglio di lavoro e / o la cartella di lavoro sono pronti con i calcoli.

Considera i seguenti semplici calcoli.

Il calcolo nella cella ha prodotto l'errore # DIV / 0 !.

  • Fare clic nella cella C5.

  • Fare clic sulla scheda FORMULE sulla barra multifunzione.

  • Fare clic sulla freccia accanto a Controllo errori nel gruppo Controllo formula. Nell'elenco a discesa, lo troveraiCircular References è disattivato, a indicare che il foglio di lavoro non ha riferimenti circolari.

  • Selezionare Trace Error dall'elenco a discesa.

Le celle necessarie per calcolare la cella attiva sono indicate da frecce blu.

  • Fare clic su Rimuovi frecce.
  • Fare clic sulla freccia accanto a Controllo errori.
  • Seleziona Controllo errori dall'elenco a discesa.

Il Error Checking appare la finestra di dialogo.

Osservare quanto segue:

  • Se fai clic su Help on this error, Verrà visualizzata la guida di Excel sull'errore.

  • Se fai clic su Show Calculation Steps, Viene visualizzata la finestra di dialogo Valuta formula.

  • Se fai clic su Ignore Error, la finestra di dialogo Controllo errori si chiude e se si fa clic su Error Checking di nuovo, ignora questo errore.

  • Se fai clic su Edit in Formula Bar, verrai indirizzato alla formula nella barra della formula, in modo da poter modificare la formula nella cella.