Problemi con il calcolo delle formule in Microsoft Excel. Modalità pagina del foglio di lavoro in Excel. Informazioni nei commenti

Una delle funzionalità più richieste di Excel è lavorare con le formule. Grazie a questa funzione, il programma esegue autonomamente vari tipi di calcoli nelle tabelle. Ma a volte capita che l'utente inserisca una formula in una cella, ma non soddisfa il suo scopo diretto: calcolare il risultato. Scopriamo a cosa potrebbe essere correlato e come risolvere questo problema.

Le ragioni dei problemi con il calcolo delle formule in Excel possono essere completamente diverse. Possono essere causati sia dalle impostazioni di un particolare libro o anche da un intervallo separato di celle, sia da vari errori di sintassi.

Metodo 1: cambia il formato delle celle

Uno dei motivi più comuni per cui Excel non calcola affatto o non calcola correttamente le formule è il formato delle celle impostato in modo errato. Se la gamma ha formato di testo, quindi il calcolo delle espressioni in esso non viene eseguito affatto, ovvero vengono visualizzate come testo normale. In altri casi, se il formato non corrisponde all'essenza dei dati calcolati, il risultato visualizzato nella cella potrebbe essere visualizzato in modo errato. Scopriamo come risolvere questo problema.



Ora la formula verrà calcolata nell'ordine standard con l'output del risultato nella cella specificata.


Metodo 2: disattiva la modalità "Mostra formule"

Ma forse il motivo per cui invece dei risultati del calcolo vengono visualizzate le espressioni è che il programma ha abilitato la modalità "Mostra formule".




Metodo 3: correggere un errore di sintassi

Una formula può anche essere visualizzata come testo se sono stati commessi errori nella sua sintassi, ad esempio una lettera mancante o modificata. Se l'hai inserito manualmente e non tramite Procedura guidata, allora è molto probabile. Un errore molto comune quando si visualizza un'espressione come testo è la presenza di uno spazio prima del segno «=» .


In tali casi, è necessario rivedere attentamente la sintassi di quelle formule che vengono visualizzate in modo errato e apportare le modifiche appropriate.

Metodo 4: abilitare il ricalcolo della formula

Succede anche che la formula sembri visualizzare il valore, ma quando le celle ad essa associate cambiano, non cambia se stessa, cioè il risultato non viene ricalcolato. Ciò significa che hai configurato male i parametri di calcolo in questo libro.




Tutte le espressioni in questa cartella di lavoro verranno ora ricalcolate automaticamente ogni volta che cambia un valore associato.

Metodo 5: errore nella formula

Se il programma esegue ancora i calcoli, ma di conseguenza mostra un errore, è probabile che l'utente abbia semplicemente commesso un errore durante l'immissione dell'espressione. Le formule errate sono quelle, quando si calcola che nella cella compaiono i seguenti valori:

  • #NUMERO!;
  • #VALORE !;
  • # VUOTO !;
  • # DIV/0!;
  • # N / A.

In questo caso, è necessario verificare se i dati nelle celle a cui fa riferimento l'espressione sono scritti correttamente, se sono presenti errori di sintassi o se la formula stessa contiene azioni errate (ad esempio, divisione per 0).


Se la funzione è complessa, con un numero elevato di celle collegate, è più facile tracciare i calcoli utilizzando uno strumento speciale.




Come puoi vedere, i motivi per cui Excel non considera o non calcola correttamente le formule possono essere completamente diversi. Se, invece di calcolare, l'utente visualizza la funzione stessa, in questo caso, molto probabilmente, la cella è formattata per il testo o è abilitata la modalità di visualizzazione dell'espressione. Inoltre, è possibile un errore di sintassi (ad esempio, la presenza di uno spazio prima del segno «=» ). Se, dopo aver modificato i dati nelle celle collegate, il risultato non viene aggiornato, qui è necessario vedere come è configurato l'aggiornamento automatico nei parametri del libro. Inoltre, molto spesso, invece del risultato corretto, viene visualizzato un errore nella cella. Qui è necessario visualizzare tutti i valori a cui fa riferimento la funzione. Se viene rilevato un errore, dovrebbe essere eliminato.

I lettori di Lifehacker hanno già familiarità con Denis Batyanov che ha condiviso con noi. Oggi Denis parlerà di come evitare i problemi più comuni con Excel, che spesso creiamo per noi stessi.

Farò subito una prenotazione che il materiale dell'articolo è destinato agli utenti principianti di Excel. Gli utenti esperti hanno già ballato in modo incendiario su questo rastrello più di una volta, quindi il mio compito è proteggere i "ballerini" giovani e inesperti da questo.

Non dai titoli alle colonne della tabella

Molti strumenti di Excel, come l'ordinamento, il filtro, le tabelle intelligenti, le tabelle pivot, presuppongono che i dati contengano intestazioni di colonna. Altrimenti, non sarai in grado di usarli affatto o non funzioneranno correttamente. Assicurati sempre che le tue tabelle contengano intestazioni di colonna.

Colonne e righe vuote all'interno delle tabelle

Questo è fonte di confusione per Excel. Quando incontra una riga o una colonna vuota all'interno della tua tabella, inizia a pensare di avere 2 tabelle, non una. Dovrai correggerlo costantemente. Inoltre, non nascondere le righe/colonne che non ti servono all'interno della tabella, è meglio eliminarle.

Diverse tabelle si trovano su un foglio

Se queste non sono piccole tabelle contenenti valori di riferimento, non dovresti farlo.

Sarà scomodo per te lavorare completamente con più di una tabella per foglio. Ad esempio, se una tabella è a sinistra e la seconda a destra, il filtro su una tabella avrà effetto sull'altra. Se le tabelle si trovano una sotto l'altra, è impossibile utilizzare il blocco delle aree e una delle tabelle dovrà costantemente cercare ed eseguire manipolazioni non necessarie per posizionarsi su di essa con un cursore della tabella. Ne hai bisogno?

I dati dello stesso tipo sono disposti artificialmente in colonne diverse

Molto spesso, gli utenti che conoscono Excel in modo piuttosto superficiale preferiscono questo formato di tabella:

Sembrerebbe che ci troviamo di fronte a un formato innocuo per accumulare informazioni sulle vendite degli agenti e sulle loro multe. Una tale disposizione del tavolo è ben percepita visivamente da una persona, poiché è compatta. Tuttavia, credetemi, è un vero incubo cercare di estrarre dati da tali tabelle e ottenere subtotali (informazioni aggregate).

Il fatto è che questo formato contiene 2 dimensioni: quindi devi decidere sulla linea, smistando una filiale, un gruppo e un agente. Quando trovi lo stock giusto, dovrai cercare la colonna richiesta, poiché ce ne sono molti qui. E questa "bidimensionalità" complica notevolmente il lavoro con una tale tabella per gli strumenti standard di Excel: formule e tabelle pivot.

Se crei una tabella pivot, scoprirai che non c'è modo di ottenere facilmente i dati per anno o trimestre, poiché gli indicatori sono suddivisi in campi diversi. Non hai un campo di vendita che può essere comodamente manipolato, ma ci sono 12 campi separati. Dovrai creare a mano campi calcolati separati per trimestri e anni, anche se, sia tutto in una colonna, tabella pivot lo farebbe per te.

Se desideri utilizzare formule di sommatoria standard come SUMIF, SUMIFS, SUMPRODUCT, scoprirai anche che non funzioneranno in modo efficace con questo layout di tabella.

Distribuzione di informazioni su diversi fogli del libro "per comodità"

Un altro errore comune è, avere una sorta di formato di tabella standard e aver bisogno di analisi basate su questi dati, distribuirlo su fogli separati della cartella di lavoro di Excel. Ad esempio, spesso creano fogli separati per ogni mese o anno. Di conseguenza, la quantità di lavoro di analisi dei dati viene effettivamente moltiplicata per il numero di fogli creati. Non farlo. Raccogli informazioni su UN foglio.

Informazioni nei commenti

Spesso gli utenti aggiungono Informazioni importanti potrebbero aver bisogno nel commento della cella. Tieni presente che ciò che è nei commenti, puoi solo guardare (se lo trovi). Estrarre questo nella cella è difficile. Raccomando che è meglio avere una colonna separata per i commenti.

Un pasticcio con la formattazione

Sicuramente non aggiungerà nulla di buono alla tua tavola. Questo sembra scoraggiante per le persone che usano i tuoi fogli di calcolo. Nel migliore dei casi, non daranno importanza a questo, nel peggiore dei casi - penseranno che non sei organizzato e sciatto negli affari. Impegnati per quanto segue:

Unione di celle

Usa la concatenazione di celle solo quando non puoi farne a meno. Le celle unite rendono molto difficile manipolare gli intervalli in cui rientrano. Si verificano problemi quando si spostano le celle, si inseriscono celle, ecc.

Combina testo e numeri in una cella

Un'impressione dolorosa è prodotta da una cella contenente un numero, completata dalla costante di testo "RUB". o "USD" inserito manualmente. Soprattutto se non è un modulo stampato, ma una tabella normale. Le operazioni aritmetiche con tali celle sono naturalmente impossibili.

Numeri come testo in una cella

Evitare di memorizzare dati numerici in una cella in formato testo. Nel tempo, alcune delle celle in tale colonna avranno un formato di testo e alcune ne avranno uno normale. Ci saranno problemi con le formule a causa di questo.

Se la tua tavola sarà presentata attraverso un proiettore LCD

Scegli le combinazioni più contrastanti di colore e sfondo. Sembra buono su un proiettore sfondo scuro e lettere leggere. L'impressione più terribile è fatta dal rosso sul nero e viceversa. Questa combinazione ha un contrasto estremamente basso sul proiettore: evitatela.

Foglio di lavoro in modalità pagina in Excel

Questa è la stessa modalità in cui Excel mostra come verrà impaginato il foglio una volta stampato. I bordi della pagina sono evidenziati in blu. Non consiglio di lavorare costantemente in questa modalità, cosa che molti fanno, poiché il driver della stampante è coinvolto nel processo di visualizzazione dei dati sullo schermo e questo, a seconda di molti motivi (ad esempio, una stampante di rete non è al momento disponibile ), è pieno di blocchi nel processo di rendering e nel ricalcolo delle formule. Lavora come al solito.

Per informazioni ancora più utili su Excel, visita

Abbastanza spesso i dati da programmi di terze parti vengono scaricati in Excel per la loro ulteriore elaborazione, e spesso l'ulteriore utilizzo di questi dati nelle formule dà un risultato imprevedibile: i numeri non vengono sommati, è impossibile calcolare il numero di giorni tra le date, ecc.

Questo articolo discute le cause di tali problemi e diversi modi la loro eliminazione

Motivo uno ... Numero salvato come testo

In questo caso, puoi vedere che numeri o date sono premuti sul bordo sinistro della cella (come testo) e, di regola, nell'angolo in alto a sinistra della cella c'è un indicatore di errore (triangolo verde) e un tag che, quando passi il mouse, spiega che il numero viene salvato come testo.

Nessuna modifica al formato in Numerico, Generale o Data non corregge la situazione, ma se si fa clic nella barra della formula (o si preme F2) e poi Invio, il numero diventa un numero e la data diventa una data. Con un gran numero di tali numeri, l'opzione, vedi, è inaccettabile.

Esistono diversi modi per risolvere questo problema.

  • Utilizzo di un contrassegno e di un tag di errore... Se vedi un indicatore di errore ( triangolo verde) e tag, quindi seleziona le celle, fai clic sul tag e seleziona l'opzione Converti in numero
  • Utilizzo dell'operazione Trova/Sostituisci... Supponiamo che ci siano numeri con un punto decimale nella tabella, memorizzati come testo. Seleziona un intervallo con i numeri - fai clic Ctrl + h(o trova nella scheda casa o nel menu Modificare per le versioni precedenti al 2007 il comando Sostituire) -- nel campo Trova introdurre , (virgola) - nel campo Sostituito da vi presentiamo anche , (virgola) - Sostituisci tutto... Quindi, sostituendo la virgola con la virgola, simuliamo la modifica delle celle allo stesso modo F2 - Invio


Un'operazione simile può essere eseguita con le date, con l'unica differenza che è necessario modificare da punto a punto.

Inoltre, i programmi di terze parti possono scaricare i numeri con un punto come separatore decimale, quindi sarà utile sostituire un punto con una virgola.

Una sostituzione simile può essere eseguita con la formula (vedi sotto) utilizzando la funzione SOSTITUISCI ()

  • Usando Incolla speciale... Questo metodo è più versatile, poiché funziona con numeri frazionari, interi e date. Seleziona una cella vuota - esegui il comando copia- seleziona l'intervallo con i numeri di problema - Incolla speciale -- Piegare-- ok... Pertanto, aggiungiamo 0 ai numeri (o alle date), che non influisce in alcun modo sul loro valore, ma converte in un formato numerico


Una variante di questa tecnica potrebbe essere quella di moltiplicare l'intervallo per 1

  • Utilizzo dello strumento Testo per colonne... Questa tecnica è comoda da usare se è necessario trasformare una colonna, poiché se ci sono più colonne, i passaggi dovranno essere ripetuti separatamente per ciascuna colonna. Quindi, seleziona la colonna con numeri o date salvate come testo, imposta il formato della cella Generale(per i numeri è possibile impostare, ad esempio, Numerico o Finanziario). Successivamente, eseguiamo il comando Dati-- Testo della colonna -- Pronto


  • Usando le formule... Se la tabella consente colonne aggiuntive, puoi utilizzare le formule per convertire in un numero. Per convertire un valore di testo in un numero, è possibile utilizzare il doppio meno, l'aggiunta con zero, la moltiplicazione per uno, le funzioni VALORE (), SOSTITUTO (). Puoi leggere più in dettaglio. Dopo la trasformazione, la colonna risultante può essere copiata e incollata come valori al posto dei dati originali


  • Usando... In realtà, uno qualsiasi dei metodi elencati può essere eseguito con una macro. Se devi eseguire frequentemente una tale conversione, ha senso scrivere una macro ed eseguirla secondo necessità.

Ecco due esempi di macro:

1) moltiplicare per 1

Sub conv () Dim c As Range For Each c In Selection If IsNumeric (c.Value) Then c.Value = c.Value * 1 c.NumberFormat = "#, ## 0.00" End If Next End Sub

2) testo per colonne

Sub conv1 () Selection.TextToColumns Selection.NumberFormat = "#, ## 0.00" End Sub

Motivo due ... Il numero contiene caratteri estranei.

Molto spesso, questi caratteri estranei sono spazi. Possono essere posizionati sia all'interno del numero come separatore di cifre, sia prima/dopo il numero. In questo caso, naturalmente, il numero diventa testo.

Mettere via spazi extra può essere fatto anche usando l'operazione Trova/Sostituisci... In campo Trova inserisci uno spazio e il campo Sostituito da lascia vuoto, allora Sostituisci tutto... Se ci fossero spazi ordinari nel numero, queste azioni saranno sufficienti. Ma il numero può contenere i cosiddetti spazi unificatori (simbolo con codice 160). Tale spazio dovrà essere copiato direttamente dalla cella e quindi incollato nel campo Trova la finestra di dialogo Trova/Sostituisci... Oppure puoi sul campo Trova premi la scorciatoia da tastiera Alt + 0160(i numeri vengono digitati sul tastierino numerico).

Gli spazi possono anche essere rimossi con una formula. Le opzioni sono:

Per gli spazi regolari: = - SOSTITUTO (B4; ""; "")

Per spazi infrangibili:= - SOSTITUTO (B4, SIMBOLO (160), "")

Subito per questi e altri spazi: = - SOSTITUTO (SOSTITUTO (B4; SIMBOLO (160); ""); ""; "")

A volte, per ottenere il risultato desiderato, è necessario combinare i metodi elencati. Ad esempio, rimuovi prima gli spazi e poi converti il ​​formato della cella



Consigliato da leggere