Operazioni aritmetiche dei valori in virgola mobile potrebbe produrre risultati non accurati in Excel

Traduzione articoli Traduzione articoli
Identificativo articolo: 78113 - Visualizza i prodotti a cui si riferisce l?articolo.
Espandi tutto | Chiudi tutto

In questa pagina

Sommario

In questo articolo viene descritta la modalitÓ di memorizzazione e di calcolo dei numeri a virgola mobile in Microsoft Excel. Il criterio utilizzato potrebbe incidere su alcuni numeri o formule a causa dell'arrotondamento e/o del troncamento dei dati.

Cenni preliminari

Microsoft Excel Ŕ stato progettato tenendo conto della specifica IEEE 754 relativamente alla memorizzazione e al calcolo dei numeri a virgola mobile. L'IEEE (Institute of Electrical and Electronics Engineers) Ŕ un'associazione internazionale che, tra le altre attivitÓ, determina gli standard in campo software e hardware. La specifica 754 Ŕ molto diffusa e descrive il modo in cui i numeri a virgola mobile vanno memorizzati in un computer binario. La sua notorietÓ Ŕ dovuta al fatto che consente la memorizzazione dei numeri a virgola mobile in uno spazio ragionevole e l'esecuzione dei calcoli in tempi abbastanza rapidi. Lo standard 754 Ŕ utilizzato nelle unitÓ a virgola mobile e nei processori di dati numerici di quasi tutti i microprocessori basati su PC di oggi che implementano la matematica a virgola mobile, compresi i processori Intel, Motorola, Sun e MIPS.

Durante la memorizzazione dei numeri, un numero binario corrispondente pu˛ rappresentare ogni numero o frazione di numero. La frazione 1/10, ad esempio, pu˛ essere rappresentata nel sistema decimale come 0,1. ma lo stesso numero in formato binario diventa un decimale binario ripetitivo
0001100110011100110011 (e cosý via)
e pu˛ essere ripetuto all'infinito. Questo numero non pu˛ essere rappresentato in uno spazio finito (limitato). Di conseguenza, questo numero viene arrotondato da circa - 2.8E - 17 quando Ŕ memorizzato.

La specifica IEEE 754 presenta tuttavia dei limiti che rientrano in tre categorie principali:
  • limiti massimi/minimi
  • precisione
  • numeri binari periodici

Informazioni

limiti massimi/minimi

Tutti i computer dispongono di un numero massimo e minimo che pu˛ essere gestito. PoichÚ il numero di bit di memoria in cui il numero viene memorizzato Ŕ limitato, il numero massimo o minimo memorizzabile Ŕ anch'esso limitato. Per Excel il numero massimo memorizzabile Ŕ 1,79769313486232E+308 e il numero minimo positivo memorizzabile Ŕ 2,2250738585072E-308.

Casi di adesione alla specifica IEEE 754

  • Underflow: Underflow si verifica quando viene generato un numero troppo piccolo per essere rappresentato. In IEEE e in Excel, il risultato Ŕ 0 (con l'eccezione che IEEE ha un concetto di 0 e non Excel).
  • Overflow: Overflow si verifica quando un numero Ŕ troppo grande per essere rappresentato. In questo caso in Excel si utilizza un tipo di rappresentazione specifico (#NUM!).

Casi di mancata adesione alla specifica IEEE 754

  • Denormalizzate numeri: numero denormalizzato Ŕ indicato da un esponente 0. In tal caso, il numero intero viene memorizzato nella mantissa e la mantissa non Ŕ nessun 1 iniziale implicito. Di conseguenza ne risente la precisione: tanto pi¨ piccolo Ŕ il numero, tanto maggiore Ŕ la perdita di precisione. I numeri alla fine di questo intervallo hanno solo una cifra di precisione.
    Esempio: Numero normalizzato ha un 1 iniziale implicito. Ad esempio, se la mantissa rappresenta 0011001, il numero normalizzato diventa 10011001 a causa del numero 1 iniziale implicito. I numeri denormalizzati non presentano l'1 implicito iniziale, quindi il numero dell'esempio, 0011001, rimane tale come numero denormalizzato. In questo caso il numero normalizzato ha 8 cifre significative (10011001) mentre il numero denormalizzato ha 5 cifre significative con degli zeri iniziali non significativi.

    I numeri denormalizzati sono in sostanza una soluzione che consente ai numeri pi¨ piccoli rispetto al limite inferiore normale di essere memorizzati. Microsoft non implementa questa parte facoltativa della specifica perchÚ i numeri denormalizzati, per loro stessa natura, hanno un numero variabile di cifre significative. Ci˛ potrebbe favorire il verificarsi di errori rilevanti nei calcoli.
  • Infiniti positivi/negativi: Infiniti si verificano quando la divisione per 0. Excel non supporta numeri infiniti, quanto piuttosto che viene offerta una # DIV/0! Errore in questi casi.
  • Non numerico (NaN): NaN viene utilizzato per rappresentare operazioni non valide (come infinito/infinito, infinito / infinito o la radice quadrata di -1). I non numeri consentono a un programma di procedere nonostante un'operazione non valida. In Excel invece viene generato immediatamente un errore, ad esempio #NUM! o # DIV/0!.

Precisione

Un numero a virgola mobile viene memorizzato in formato binario in tre parti all'interno di un intervallo a 65 bit: il segno dell'esponente e la mantissa.
Riduci questa tabellaEspandi questa tabella
1 Bit segno11 bit esponente1 bit implicitoBit 52 mantissa
Il segno memorizza il segno di numero (positivo o negativo), l'esponente memorizza la potenza di 2 a cui il numero Ŕ generato o abbassato (la potenza massima/minima di 2 Ŕ +1,023 e-1,022), e la mantissa memorizza il numero effettivo. Lo spazio di memorizzazione finito relativo alla mantissa definisce la vicinanza di due numeri a virgola mobile adiacenti, vale a dire la precisione.

La mantissa e l'esponente sono entrambi memorizzati come componenti separati. Di conseguenza il livello di precisione possibile pu˛ dipendere della dimensione del numero (la mantissa) da gestire. Nel caso di Excel, anche se in questo programma Ŕ possibile memorizzare numeri da 1,79769313486232E308 a 2,2250738585072E-308, ci˛ Ŕ fattibile solo entro 15 cifre di precisione. Tale limite deriva dall'adesione completa alla specifica IEEE 754 e non dipende direttamente da Excel. Questo livello di precisione si riscontra anche in altri programmi di fogli di calcolo.

I numeri a virgola mobile vengono rappresentati in formato riportato di seguito, dove l'esponente Ŕ l'esponente binario:
X = frazione * ^(exponent-bias) 2
Frazione Ŕ la parte frazionaria normalizzata del numero normalizzata perchÚ l'esponente Ŕ modificato in modo che il bit iniziale sia sempre 1. In tal modo non deve essere memorizzato e si ottiene un bit in pi¨ di precisione. Ci˛ giustifica l'esistenza di un bit implicito. Il criterio Ŕ simile alla notazione scientifica, in cui si manipola l'esponente in modo da avere una cifra a sinistra della virgola decimale; ad eccezione del formato binario, perchÚ ci sono solo i numeri 1 e 0, Ŕ sempre possibile manipolare l'esponente in modo che il primo bit sia 1.

Differenza Ŕ il valore utilizzato per non dover memorizzare esponenti negativi. La differenza per numeri a precisione singola Ŕ 127 e 1023 (decimale) per i numeri a precisione doppia. In Excel i numeri sono memorizzati con la precisione doppia.

Esempio con numeri molto grandi

Immettere quanto segue in una nuova cartella di lavoro:
   A1: 1.2E+200
   B1: 1E+100
   C1: =A1+B1
il valore risultante nella cella C1 dovrebbe essere 1.2E + 200, lo stesso valore della cella A1. Infatti se si confrontano celle A1 e C1 utilizzando la funzione se, ad esempio IF(A1=C1), il risultato sarÓ TRUE. Ci˛ Ŕ dovuto alla specifica IEEE che consente di memorizzare solo 15 cifre significative di precisione. Per poter memorizzare il calcolo riportato sopra sarebbero necessarie almeno 100 cifre di precisione.

Esempio con numeri molto piccoli

Immettere quanto segue in una nuova cartella di lavoro:
   A1: 0.000123456789012345
   B1: 1
   C1: =A1+B1
il valore risultante nella cella C1 dovrebbe essere 1.00012345678901 invece di 1,000123456789012345. Ci˛ Ŕ dovuto alla specifica IEEE che consente di memorizzare solo 15 cifre significative di precisione. Per essere in grado di memorizzare il calcolo riportato sopra, Excel deve essere almeno 19 cifre di precisione.

Correzione degli errori di precisione

Excel offre due metodi di base per compensare gli errori di arrotondamento: la funzione ARROTONDA e l'opzione Approssimazione come visualizzata o set precisione come visualizzato della cartella di lavoro.

Metodo 1: La funzione ARROTONDA

L'esempio seguente, in cui sono utilizzati i dati riportati sopra, impiega la funzione ARROTONDA per limitare un numero a 5 cifre. In tal modo Ŕ possibile confrontare il risultato con un altro valore.
   A1: 1.2E+200
   B1: 1E+100
   C1: =ROUND(A1+B1,5)
risultati in 1.2E + 200.
   D1: =IF(C1=1.2E+200, TRUE, FALSE)
Genera il valore VERO.

Metodo 2: Approssimazione come visualizzata

In alcuni casi, potrebbe essere in grado di impedire di danneggiare il proprio lavoro utilizzando l' Approssimazione come visualizzata errori di arrotondamento opzione. In base a questa opzione il valore di ogni numero del foglio di lavoro diventa obbligatoriamente il valore visualizzato. Per attivare questa opzione, attenersi alla seguente procedura:
  1. In Excel 2003 e nelle versioni precedenti, scegliere Opzioni dal menu Strumenti.
  2. Nella scheda calcolo casella di controllo fare clic per selezionare la precisione come visualizzato.
  1. In Excel 2007, fare clic sul pulsante Microsoft Office, fare clic su Opzioni di Excel e quindi fare clic su Avanzate categoria.
  2. Nel durante il calcolo della cartella di lavoro della sezione, selezionare la cartella di lavoro desiderata e quindi selezionare la casella di controllo Imposta precisione come visualizzato.
Ad esempio, se si sceglie un formato numero con due posizioni decimali, quindi attivare l'opzione Approssimazione come visualizzata tutti oltre a due cifre decimali sarÓ precisione persi quando si salva una cartella di lavoro. Questa opzione incide sulla cartella di lavoro attiva, compresi tutti i fogli. Non Ŕ possibile annullare l'effetto dell'opzione e recuperare i dati persi. Si consiglia di salvare la cartella di lavoro prima di attivare l'opzione.

Numeri binari periodici e calcoli con risultati prossimi allo zero

Un altro problema che crea confusione relativamente alla memorizzazione dei numeri a virgola mobile nel sistema binario consiste nel fatto che alcuni numeri, finiti e non periodici su base decimale, sono infiniti e periodici nel sistema binario. L'esempio pi¨ comune Ŕ il valore 0,1 e le sue varianti. Per quanto tale numero possa essere rappresentato perfettamente nel sistema decimale, lo stesso nel formato binario diventa il seguente numero binario periodico nel momento in cui viene memorizzato nella mantissa:
000110011001100110011 (e cosý via)
La specifica IEEE 754 non contiene disposizioni particolari per alcun numero; memorizza il possibile nella mantissa e tronca il resto. Durante la memorizzazione viene provocato un errore di circa -2,8E-17, o 0,000000000000000028.

Persino le frazioni decimali pi¨ comuni, come 0,0001, non possono essere rappresentate esattamente nel sistema binario. (0,0001 Ŕ una frazione binaria periodica con un intervallo periodico di 104 bit). Il caso Ŕ simile alla ragione per cui la frazione 1/3 non pu˛ essere rappresentata esattamente nel formato decimale (valore periodico 0,33333333333333333333).

Questo spiega perchÚ un semplice esempio in Visual Basic, Applications Edition
   Sub Main()
      MySum = 0
      For I% = 1 To 10000
         MySum = MySum + 0.0001
      Next I%
      Debug.Print MySum
   End Sub
verrÓ PRINT 0.999999999999996 come output. Il piccolo errore di rappresentazione di 0,0001 nel sistema binario si estende alla somma.

Esempio con l'aggiunta di un numero negativo

  1. Immettere quanto segue in una nuova cartella di lavoro:
       A1: =(43.1-43.2)+1
  2. Fare clic con il pulsante destro del mouse sulla cella A1 e quindi fare clic su Format Cells. Nella scheda numero nella casella categoria fare clic su Scientifico. Impostato su 15 posizioni decimali.
Invece di 0,9 verrÓ visualizzato 0,899999999999999. PoichÚ (43,1-43,2) viene calcolato per primo, -0,1 viene temporaneamente memorizzato e nel calcolo viene poi inserito l'errore dovuto alla memorizzazione di -0,1.

Esempio in cui un valore raggiunge lo zero

  1. In Excel 95 o versioni precedenti, immettere quanto segue in una nuova cartella di lavoro:
       A1: =1.333+1.225-1.333-1.225
  2. Fare clic con il pulsante destro del mouse sulla cella A1 e quindi scegliere Formato celle. Nella scheda numero nella casella categoria fare clic su Scientifico. Impostato su 15 posizioni decimali.
Invece di 0 verrÓ visualizzato -2,22044604925031E-16.

In Excel 97, tuttavia, Ŕ stata introdotta una funzione che tenta di risolvere questo problema. Un'operazione di addizione o sottrazione comporterÓ il valore in o molto vicino a zero, Excel 97 e in seguito verrÓ compensare eventuali errori di conversione di un operando da e in formato binario. Se l'esempio citato sopra viene eseguito in Excel 97 e nelle versioni successive, viene correttamente visualizzato 0 o 0,000000000000000E+00 nella notazione scientifica. Per ulteriori informazioni, fare clic sui numeri degli articoli della Microsoft Knowledge Base riportato di seguito:
172911Risultato errato generazione 10 alla potenza molto grandi o molto piccola
214373Risultato errato generazione 10 alla potenza molto grandi o molto piccola
Per ulteriori informazioni sui numeri a virgola mobile e la specifica IEEE 754, visitare i seguenti siti Web (informazioni in lingua inglese):
http://www.ieee.org

http://stevehollasch.com/cgindex/coding/ieeefloat.html

Riferimenti

Per ulteriori informazioni su come ovviare a questi errori, fare clic sul numero dell'articolo della Microsoft Knowledge Base riportato di seguito:
214118Come correggere gli errori di arrotondamento nelle operazioni aritmetiche dei valori in virgola mobile

ProprietÓ

Identificativo articolo: 78113 - Ultima modifica: giovedý 13 maggio 2010 - Revisione: 7.0
Le informazioni in questo articolo si applicano a:
  • Microsoft Excel 2010
  • Microsoft Office Excel 2008 for Mac
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 2004 for Mac
  • Microsoft Excel X per Mac
  • Microsoft Excel 2001 per Mac
  • Microsoft Excel 98 per Macintosh
Chiavi:á
kbmt kbinfo KB78113 KbMtit
Traduzione automatica articoli
Il presente articolo Ŕ stato tradotto tramite il software di traduzione automatica di Microsoft e non da una persona. Microsoft offre sia articoli tradotti da persone fisiche sia articoli tradotti automaticamente da un software, in modo da rendere disponibili tutti gli articoli presenti nella nostra Knowledge Base nella lingua madre dell?utente. Tuttavia, un articolo tradotto in modo automatico non Ŕ sempre perfetto. Potrebbe contenere errori di sintassi, di grammatica o di utilizzo dei vocaboli, pi¨ o meno allo stesso modo di come una persona straniera potrebbe commettere degli errori parlando una lingua che non Ŕ la sua. Microsoft non Ŕ responsabile di alcuna imprecisione, errore o danno cagionato da qualsiasi traduzione non corretta dei contenuti o dell?utilizzo degli stessi fatto dai propri clienti. Microsoft, inoltre, aggiorna frequentemente il software di traduzione automatica.
Clicca qui per visualizzare la versione originale in inglese dell?articolo: 78113
LE INFORMAZIONI CONTENUTE NELLA MICROSOFT KNOWLEDGE BASE SONO FORNITE SENZA GARANZIA DI ALCUN TIPO, IMPLICITA OD ESPLICITA, COMPRESA QUELLA RIGUARDO ALLA COMMERCIALIZZAZIONE E/O COMPATIBILITA' IN IMPIEGHI PARTICOLARI. L'UTENTE SI ASSUME L'INTERA RESPONSABILITA' PER L'UTILIZZO DI QUESTE INFORMAZIONI. IN NESSUN CASO MICROSOFT CORPORATION E I SUOI FORNITORI SI RENDONO RESPONSABILI PER DANNI DIRETTI, INDIRETTI O ACCIDENTALI CHE POSSANO PROVOCARE PERDITA DI DENARO O DI DATI, ANCHE SE MICROSOFT O I SUOI FORNITORI FOSSERO STATI AVVISATI. IL DOCUMENTO PUO' ESSERE COPIATO E DISTRIBUITO ALLE SEGUENTI CONDIZIONI: 1) IL TESTO DEVE ESSERE COPIATO INTEGRALMENTE E TUTTE LE PAGINE DEVONO ESSERE INCLUSE. 2) I PROGRAMMI SE PRESENTI, DEVONO ESSERE COPIATI SENZA MODIFICHE, 3) IL DOCUMENTO DEVE ESSERE DISTRIBUITO INTERAMENTE IN OGNI SUA PARTE. 4) IL DOCUMENTO NON PUO' ESSERE DISTRIBUITO A SCOPO DI LUCRO.

Invia suggerimenti

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com