Pagina 1 di 2
Per trovare la posizione dell'errore, è possibile utilizzare varie tecniche. Pertanto, per verificare la correttezza delle formule e cercare errori logici, è possibile visualizzare le dipendenze tra le celle. Questo può essere fatto utilizzando i comandi nel sottomenu a discesa Dipendenze formula (Strumenti). Se posizioni il cursore della tabella su una cella, quindi sotto forma di frecce puoi visualizzare sullo schermo: connessioni con celle, i cui indirizzi sono utilizzati nella formula scritta nella cella selezionata (cella C2 in Fig. 7.17 ), - comando Celle influenti", collegamenti con celle , in cui vengono inserite formule contenenti l'indirizzo della cella selezionata (cella C7 in Fig.
7.17), - comando Celle dipendenti; » connessioni con celle che potrebbero contenere dati errati che portano a un errore nella cella selezionata (in questo caso si tratta di un errore evidente) - il comando Origine errore. Per rimuovere le frecce di relazione dallo schermo, eseguire il comando Dipendenze formula. Rimuovere tutte le frecce (Servizio). Calcolo passo passo delle formule Per un'analisi più approfondita delle formule al fine di trovare errori logici in formule complesse, è possibile utilizzare i comandi Valuta formula e Mostra finestra valore di controllo del sottomenu a discesa Dipendenze formula (Strumenti). Dopo aver eseguito il comando Calcola formula, viene visualizzata una finestra di dialogo tramite la quale è possibile eseguire un calcolo passo passo utilizzando la formula. Per passare a ogni passaggio successivo, fare clic sul pulsante Calcola. In questo caso viene calcolato il valore sottolineato (cella A2 in Fig. 7.18). Nell'esempio mostrato in Fig. 7.18, dopo il primo clic sul pulsante Calcola, verrà determinato il valore della cella A2, dopo il secondo - la somma del numero 100 e il valore della cella A2, dopo il terzo - il valore medio delle celle G2:G13, dopo il quarto - il risultato finale. Se il valore sottolineato è l'indirizzo di una cella e la cella contiene un'altra formula, puoi calcolare il valore da quella formula nidificata immediatamente o gradualmente. Per calcolare “immediatamente”, fare clic sul pulsante Calcola. Per calcolare "passo dopo passo", è necessario fare clic sul pulsante Passo dopo passo (questo inserirà la formula nidificata), quindi continuare a fare clic sul pulsante Calcola. Se, dopo aver inserito una formula nidificata, si fa clic sul pulsante Passo con uscita, il calcolo tornerà al livello superiore, ovvero il calcolo passo passo della formula nidificata verrà completato prima del previsto. Nell'esempio mostrato in Fig. 7.18, se al primo passaggio si fa clic sul pulsante Passaggio con immissione, verrà inserita la formula scritta nella cella A2 (l'immissione verrà eseguita anche se lì non è presente una formula, ma il valore inserito). Se esegui il comando Dipendenze formula - Mostra finestra valore di controllo (Strumenti), viene visualizzata una finestra di dialogo in cui puoi osservare come cambiano i valori calcolati dalle formule nelle diverse celle. In questo caso, le celle osservate possono trovarsi in parti diverse della tabella, su fogli diversi e persino in cartelle di lavoro diverse. Tutte le modifiche al contenuto delle celle verranno riflesse nella finestra immediatamente dopo la modifica dei dati nelle celle interessate. Per aggiungere una cella alla finestra di monitoraggio, è necessario fare clic sul pulsante Aggiungi valore di controllo... e nella finestra di dialogo che appare, specificare l'indirizzo di questa cella. Come puoi immaginare, se, dopo aver selezionato una riga, fai clic sul pulsante Elimina valore di controllo, questa riga verrà eliminata dalla finestra di monitoraggio. I comandi sopra menzionati, così come alcune azioni aggiuntive, possono essere eseguiti utilizzando gli strumenti nel pannello Dipendenze. Per visualizzarlo è possibile abilitare il comando Dipendenze formule - Pannello delle dipendenze (Strumenti) oppure eseguire il comando Mostra pannello di controllo formula nel menu di gestione degli errori. Esempio 27. Ricerca di errori nelle formule Passaggio 1 Aprire il documento Primo Libro. Esegui il comando Opzioni... (Strumenti), nella finestra di dialogo visualizzata, nella scheda Controllo errori, assicurati che l'opzione Abilita controllo errori in background sia abilitata. Se questo interruttore è disattivato, attivarlo e fare clic su OK. Azione 2 Nel Foglio1, nella formula della cella D1, apportare una modifica che porti a un errore, ad esempio =ROOT(MAX(100,A2:A7, E3) -10000). Successivamente, posiziona il cursore della tabella su questa cella, sposta il cursore del mouse sul pulsante del menu di gestione degli errori e fai clic su di esso, quindi nel menu che si apre, esegui il comando Mostra passaggi di calcolo... (Fig. 7.21). Nella finestra di dialogo Calcola formula visualizzata, fare clic sul pulsante Calcola, quindi sul pulsante Riprendi visualizzato al suo posto e, dopo l'inizio del calcolo della formula, fare clic tre volte sul pulsante Calcola.
Assicurarsi che il calcolo passo passo della formula renda relativamente semplice determinare la causa e la posizione dell'errore. Chiudere la finestra Valuta formula. Riporta la formula nella cella D1 al suo stato originale Azione 3 Sul foglio Foglio1 del documento Primo Libro, esegui il comando Opzioni... (Strumenti).
Nella finestra di dialogo visualizzata, nella scheda Visualizza, attiva il pulsante di opzione della formula e fai clic su OK. Assicurati che invece dei valori determinati dalle formule, le formule stesse vengano visualizzate sullo schermo. Premi la combinazione Ctrl*" (il tasto con questo simbolo si trova a sinistra del tasto con il numero 1). Assicurati che i valori da essi determinati appaiano nuovamente sullo schermo al posto delle formule. Passaggio 4 Posiziona la tabella posizionare il cursore sulla cella A5 ed eseguire il comando Dipendenze formula Celle influenti (Strumenti) Assicurarsi che appaiano delle frecce che puntano alle celle i cui indirizzi sono contenuti nella formula della cella A5. Rimuovere le connessioni visualizzate eseguendo il comando Dipendenze formula - Rimuovi tutte le frecce ( Utensili).
Esiste anche il gruppo Dipendenze formula della scheda Formule per semplificarti la vita (Fig. 3.21).
Il comando Influenza sulle celle ti mostrerà con le frecce esattamente quali celle influenzano il risultato del calcolo della formula nella cella che hai selezionato (Fig. 3.22).
Il comando Celle dipendenti ti mostrerà quali celle dipendono da quella selezionata (Figura 3.23).
Queste frecce sono necessarie per chiarezza e per controllare le formule. All'improvviso hai commesso un errore e hai inserito accidentalmente l'indirizzo sbagliato nella formula. E le frecce ti mostreranno tutto. Quando sei sicuro di aver fatto tutto correttamente, le frecce possono essere rimosse. Il comando Rimuovi frecce consente di rimuovere tutte le frecce o separatamente le frecce per influenzare le celle e separatamente le frecce per le celle dipendenti. L'opzione desiderata può essere selezionata dall'elenco dei pulsanti.
Il pulsante Mostra formule rimuoverà il risultato del calcolo dalle celle e te lo mostrerà
formule. Questo è utile anche per controllarsi durante il lavoro. Il pulsante Verifica errori inizierà a cercare gli errori più comuni in tutte le formule nel foglio di lavoro aperto. Il pulsante Calcola formula ti aiuterà a seguire il processo di calcolo in formule complesse. Permette di calcolare la formula in parti (Fig. 3.24).
Nella fig. 3.24a vedi la formula = 1000-Acquisto_Importo. Se si fa clic sul pulsante Calcola, il programma calcolerà l'azione sottolineata, ovvero calcolerà cosa c'è nella cella Importo_Acquisto. Nella fig. 3.24b Importo_acquisto è già stato calcolato e ora quando si fa clic sul pulsante Calcola, il programma calcolerà la seconda azione, che in questo esempio è anche l'ultima. Nella fig. 3.24, in si vede il risultato dei calcoli.
Questo calcolo passo-passo è molto utile per formule grandi e ingombranti, soprattutto nel momento in cui cerchi un errore in esse. Ad esempio, hai ricevuto un prezzo negativo per un prodotto e stai pensando intensamente a come ciò potrebbe accadere. In quale fase del calcolo si trova l'errore? Ora sai che puoi controllare tutte le formule passo dopo passo e scoprirai rapidamente qual è il motivo. A proposito, se guardate la Fig. 3.24, c, vedrai due pulsanti in basso: Rinnova e Chiudi. Premi il primo se non capisci immediatamente qual è il problema e il secondo se lo capisci.
Nel gruppo Dipendenze formula della scheda Formule (vedi Fig. 3.21), abbiamo ancora un pulsante non esaminato: Finestra valore di controllo. Nella finestra che appare dopo aver cliccato su questo pulsante, puoi inserire i risultati che sono significativi per te, cioè quelli che vuoi avere davanti ai tuoi occhi e confrontare (Fig. 3.25).
In questa finestra è necessario fare clic sul pulsante Aggiungi valore di controllo (Fig. 3.26).
Nella finestra Aggiunta di un valore di controllo che si apre verrà visualizzato l'indirizzo assoluto della cella evidenziata nella tabella. Dopo aver selezionato quello che merita di essere nella finestra dei valori di controllo, fai clic sul pulsante Aggiungi. È quindi possibile eliminare i dati non necessari nella finestra Valore di controllo utilizzando il comando Elimina valore di controllo.
24.10.2012
A volte tutti commettiamo errori, anche quando utilizziamo le formule in MS Excel. Tuttavia, i nostri errori non sono sempre esattamente errori, ovvero forniscono anche raccomandazioni su come affrontare il problema che si è presentato. È molto peggio quando non viene visualizzato alcun avviso di errore e vediamo chiaramente che il risultato è completamente incoerente con la realtà.
Fortunatamente, abbiamo a nostra disposizione diversi strumenti eccellenti per trovare errori complicati nelle formule di MS Excel.
Celle influenzanti e dipendenti in MS Excel
In genere, le righe finali di una tabella visualizzano un riepilogo dei dati posizionati altrove nella tabella o anche il risultato di alcuni calcoli intermedi. Se ci sono molti di questi calcoli, diventa facile commettere un errore in uno di essi. Tuttavia, anche correggere un errore del genere è molto semplice... ma solo se sai dove cercare.
È da questo punto di vista che sono divise tutte le celle di MS Excel influenzando E dipendente. È facile distinguerli e ricordarli:
- Le cellule influenti lo sono celle a cui fa riferimento la formula(ovvero se la formula è A+B, i dati nelle celle A e B sono i dati che influenzano il risultato del calcolo della formula).
- Dipendenti - contenere una formula che influisce sul contenuto della cella(cioè se la formula B + D prende dati su C da una cella contenente non un numero, ma il risultato del calcolo A + B, allora la cella con la formula B + D dipenderà da essa, perché dalla correttezza del lavoro di A+B dipende dal risultato del calcolo in C+D).
Sapendo quali cellule influenzano il risultato e da cosa dipende, possiamo facilmente individuare la causa del problema.
A scopo illustrativo, ho preparato una semplice targhetta dati. Ha due indicatori condizionali e un coefficiente e il calcolo finale viene effettuato semplicemente sommando entrambi gli indicatori e quindi moltiplicando per il risultato: (Indicatore 1 + Indicatore 2) x Coefficiente.
Inoltre ne ho creato un altro formula semplice: moltiplica il nostro “Totale” per una certa correzione costante, che imposto manualmente direttamente nella formula: Totale x 0,6.
Andiamo avanti alla scheda "Formule". E nel gruppo "Dipendenze formula". Consideriamo due strumenti estremamente utili: “Celle Influenti” e “Celle Dipendenti”.
Determinazione delle celle influenzanti in Excel. Influenzano naturalmente i calcoli che si verificano in una determinata cella
Seleziono la cella risultante "Totale" e premo il pulsante "Cellule influenti". Oh, e sul foglio MS Excel appaiono delle frecce blu che portano dalle tre celle utilizzate nei calcoli alla formula finale. D'accordo, è impossibile immaginare il concetto di "cellula influente" in modo più chiaro.
E ora le celle dipendenti. L'intero foglio è ora a portata di mano
Ora premo (senza spostare il cursore dalla cella “totali”) il pulsante "Celle dipendenti" e sullo schermo appare un'altra freccia. Porta alla cella “risultato corretto”, cioè quella in cui il risultato del calcolo dipende da quello corrente.
Ora "rovino" deliberatamente la tabella introducendo un errore nei dati originali, sostituendo una lettera anziché un numero. Un attimo e so già esattamente da dove viene questo errore. Non ho dovuto nemmeno cercare nulla: tutto era abbastanza chiaro e graficamente bello.
Si è verificato un errore dovuto alla sostituzione di un numero con una lettera. Excel ha evidenziato il calcolo “errato” con una freccia rossa
Puoi disattivare la grafica in qualsiasi momento facendo clic sul pulsante. "Rimuovi frecce".
Correzione degli errori che si verificano in MS Excel
Non sempre però l’errore ci appare così evidente; a volte dobbiamo ancora capire quale dei calcoli intermedi porta ad un fallimento. Anche per questo MS Excel dispone di un eccellente assistente.
Selezioniamo nuovamente il nostro famigerato “totale” e clicchiamo sul pulsante "Calcola la formula". La finestra che si apre contiene un campo in cui è scritta la formula del “problema”. Niente di straordinario per ora, vero? Fare clic sul pulsante "Calcolare" di seguito e presta attenzione a come la formula nella finestra inizia a cambiare: al posto degli identificatori di cella, appariranno i numeri delle celle corrispondenti.
Ed ecco l'errore: come puoi vedere, il programma chiarisce che il problema si pone anche prima della moltiplicazione, cioè nella fase di aggiunta degli indicatori
Un clic su un pulsante: un passaggio nei calcoli. Già nel terzo passaggio vedremo chiaramente in quale parte della formula si verificano i calcoli che generano l'errore, e ora potremo correggerli facilmente.
È tutto. Usa questi semplici metodi e potrai facilmente risolvere qualsiasi errore che potrebbe verificarsi durante i calcoli in MS Excel.
Potresti anche essere interessato.
È facile commettere errori quando si annidano una formula all'interno di un'altra. Aiuterà a evitare questo Strumento Excel, che consente di tracciare la dipendenza dei valori in alcune celle dalle formule e dai valori trovati in altre celle.
Per definire le dipendenze, posizionare il cursore della tabella nella cella in questione e richiamare il comando Servizio/Dipendenze/Celle dipendenti O Cellule influenti(Fig. 6.25). Successivamente, appariranno delle frecce tra le celle dipendenti. Mostrano l'influenza diretta del contenuto di alcune celle sulla formazione del risultato in altre celle.
Riso. 6.25.
Durante l'esecuzione del comando Cellule influenti Le frecce di dipendenza puntano alle celle i cui valori influenzano quella cella.
Se usi il comando Celle dipendenti, quindi le frecce punteranno alle celle i cui valori dipendono da questa cella.
Nel caso sia necessario monitorare gran numero dipendenze, è conveniente utilizzare il pannello Dipendenze(Fig. 6.26).
Riso. 6.26.
Questo pannello contiene i cinque pulsanti di cui abbiamo bisogno: Celle influenzanti, Rimuovi frecce per celle influenzanti, Celle dipendenti, Rimuovi frecce per celle dipendenti E Rimuovi tutte le frecce(primi cinque pulsanti a sinistra).
Nella fig. La Figura 6.27 mostra le frecce che appaiono quando viene eseguito un comando Cellule influenti. Indicano quali formule cellulari hanno un impatto diretto sulle formule nella cella analizzata. Ad esempio, i calcoli nella cella 3 sono influenzati dal valore della cella A1. La cella C4 è influenzata dai valori nelle celle C3 e B3, che a loro volta sono influenzate dalla cella A1.
Riso. 6.27.
Nella fig. La Figura 6.28 mostra le frecce che appaiono quando si esegue il comando Celle dipendenti. Indicano quali formule di cella sono influenzate dalle formule o dai valori nella cella originale. Pertanto, la cella A1 influisce sui calcoli nelle celle A1, B1, C1, ecc., mentre la cella V3 influisce sui calcoli nelle celle C4 e B4.
La frase "celle dipendenti" viene utilizzata nel contesto dell'utilizzo di formule su fogli Excel. Per definizione, si tratta di celle il cui valore viene utilizzato per calcolare altre celle. Spesso devi lavorare con un documento creato da qualcun altro. Il suo proprietario inserisce le formule secondo la sua idea, ma in alcune celle risultanti viene visualizzato un errore e non riesci a trovare il motivo. Per fare questo, c’è uno strumento nell’applicazione Excel chiamato “Dipendenze”
Trova celle dipendenti in Excel 2003
Per chiarezza, creiamo una piccola piastra (Fig. 1)
Fig. 1
Per capire, chiamiamo le colonne (la prima riga della tabella) secondo le formule contenute nelle celle.
C'è un errore nella cella F7. Per determinare cosa l'ha causato, aggiungi il blocco “ Dipendenze"(cerchiato in blu).
La prima icona rappresenta le celle che influenzano la formula. Usandolo, otteniamo l'aspetto di Fig. 2
Fig.2
I punti contrassegnano le celle che influiscono sul valore calcolato dalla formula nella cella con un errore.
È più difficile trovare un errore quando la formula in una cella include non solo celle contenenti valori, ma anche formule. Nel nostro esempio, questa è la colonna H.
Excel offre la possibilità di visualizzare le celle dipendenti. Si può fare:
- Tramite tasto F2 (Fig. 3)
- Utilizzando la barra degli strumenti, il pulsante “celle dipendenti” Fig. 4
- Tramite combinazione di tasti Ctrl+] o Ctrl+Shift+]
Essendo nella cella H4 e utilizzando la funzione F2, otteniamo le celle utilizzate nelle formule (celle che influenzano) evidenziate in blu e le celle che influenzano e dipendenti sono evidenziate in lilla.
Fig.3
Fig.4
Per utilizzare l'opzione di ricerca 3, devi posizionarti sulla cella desiderata e premere la combinazione di tasti. Di conseguenza, il cursore si sposterà sulla cella i cui dati dipendono da quello che stai cercando.
Nella fig. Viene mostrato il risultato per la cella B4.
riso. 5
Cerca celle dipendenti in Excel, versioni dal 2007
Nelle versioni successive dell'applicazione Excel, il lavoro con le formule viene inserito in un blocco separato (evidenziato da un rettangolo blu in Fig. 5)
Fig.6
Per comodità, le dipendenze delle formule sono evidenziate in un sottoblocco separato (Fig. 6).
Fig.7
Se le formule sono collegate in più cartelle di lavoro di Excel, per visualizzare le dipendenze è necessario che tutte queste cartelle di lavoro siano aperte.