L'aritmetica a virgola mobile può fornire risultati imprecisi in Excel

Riepilogo

In questo articolo viene descritto come Microsoft Excel memorizza e calcola i numeri a virgola mobile. Questo può influire sui risultati di alcuni numeri o formule a causa dell'arrotondamento o del troncamento dei dati.

Panoramica

Microsoft Excel è stato creato in base allo standard IEEE 754 per determinare la memorizzazione e il calcolo dei numeri a virgola mobile. L'abbreviazione IEEE sta per Institute of Electrical and Electronics Engineers, un ente internazionale che, tra l'altro, determina gli standard per i software e gli hardware dei computer. Lo standard 754 è ampiamente utilizzato e spiega come memorizzare i numeri a virgola mobile in un computer a sistema binario. È molto diffuso perché consente di memorizzare numeri a virgola mobile in una quantità ragionevole di spazio e di eseguire calcoli in modo relativamente rapido. Lo standard 754 viene 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, inclusi i processori Intel, Motorola, Sun e MIPS.

Quando i numeri vengono memorizzati, un numero binario corrispondente può rappresentare ogni numero o numero frazionario. Ad esempio, la frazione 1/10 può essere rappresentata in un sistema di numeri decimali come 0,1. Tuttavia, lo stesso numero in formato binario diventa il decimale binario periodico seguente:

0001100110011100110011 (e così via)

Questo può essere ripetuto all'infinito. Questo numero non può essere rappresentato in una quantità limitata di spazio. Di conseguenza, questo numero viene arrotondato a circa -2,8E-17 quando viene memorizzato.

Tuttavia, esistono alcune limitazioni dello standard IEEE 754 che rientrano in tre categorie generali:

• Limitazioni massime/minime
• Precisione
• Numeri binari periodici

Ulteriori informazioni

Limitazioni massime/minime

Tutti i computer hanno un numero massimo e un numero minimo che può essere gestito. Poiché il numero di bit di memoria in cui è memorizzato il numero è limitato, ne consegue che è limitato anche il numero massimo o minimo che è possibile memorizzare. Per Excel, il numero massimo che è possibile memorizzare è 1,79769313486232E+308 mentre il numero positivo minimo è 2,2250738585072E-308.

Casi in cui ci atteniamo allo standard IEEE 754

• Underflow: l'underflow si verifica quando viene generato un numero troppo piccolo per essere rappresentato. Nell'IEEE e in Excel, il risultato è 0 (ad eccezione del fatto che nell'IEEE, diversamente da Excel, esiste il concetto di -0).
• Overflow: l'overflow si verifica quando un numero è troppo grande per essere rappresentato. Per questo numero, Excel usa la propria rappresentazione speciale (#NUM!).

Casi in cui non ci atteniamo allo standard IEEE 754

• Numeri denormalizzati: un numero denormalizzato è indicato da un esponente di 0. In tal caso, l'intero numero viene memorizzato nella mantissa che non è alcun 1 iniziale implicito. Di conseguenza, ne risente la precisione: tanto più piccolo è il numero, tanto maggiore è la perdita di precisione. I numeri all'estremità piccola di questo intervallo hanno una sola cifra di precisione.

  Esempio: un numero normalizzato ha un 1 iniziale implicito. Ad esempio, se la mantissa rappresenta 0011001, il numero normalizzato diventa 10011001 a causa dell'iniziale implicito 1. Un numero denormalizzato non ha un numero iniziale implicito, quindi prendendo l'esempio di 0011001, il numero denormalizzato rimane lo stesso. In questo caso, il numero normalizzato ha otto cifre significative (10011001), mentre il numero denormalizzato ha cinque cifre significative (11001) con degli zeri iniziali non significativi.

  Fondamentalmente, i numeri denormalizzati sono una soluzione alternativa che consente ai numeri più piccoli rispetto al limite inferiore normale di essere memorizzati. Microsoft non implementa questa parte facoltativa dello standard perché i numeri denormalizzati per loro stessa natura hanno un numero variabile di cifre significative. Ciò può consentire l'immissione di errori significativi nei calcoli.

• Quantità infinite positive/negative: le quantità infinite si verificano quando si divide per 0. Excel non supporta le quantità infinite, ma utilizza l'errore #DIV/0! in questi casi.

• Non un numero (Not-a-Number, NaN): NaN viene utilizzato per rappresentare operazioni non valide (ad esempio infinito/infinito, infinito-infinito o radice quadrata di -1). I NaN consentono a un programma di proseguire oltre un'operazione non valida. Excel genera immediatamente un errore, ad esempio #NUM! oppure #DIV/0!.

Precisione

Un numero a virgola mobile viene memorizzato in formato binario in tre parti in un intervallo a 65 bit: il segno, l'esponente e la mantissa.

Il segno memorizza il segno del numero (positivo o negativo), l'esponente memorizza la potenza di 2 a cui viene elevato o abbassato il numero (la potenza massima/minima di 2 è +1.023 e -1.022) e la mantissa memorizza il numero effettivo. Lo spazio di memorizzazione limitato per la mantissa limita la vicinanza di due numeri a virgola mobile adiacenti, ovvero la precisione.

La mantissa e l'esponente vengono entrambi memorizzati come componenti separati. Di conseguenza, la quantità di precisione possibile può variare a seconda delle dimensioni del numero (la mantissa) da gestire. Nel caso di Excel, anche se si possono archiviare numeri da 1,79769313486232E308 a 2.2250738585072E-308, lo può fare solo entro 15 cifre di precisione. Questa limitazione è il risultato diretto dello standard IEEE 754 e non rappresenta una limitazione di Excel. Questo livello di precisione si trova anche in altri programmi con fogli di calcolo.

I numeri a virgola mobile sono rappresentati nel formato seguente, in cui l'esponente corrisponde all'esponente binario:

X = frazione * 2^(esponente - distorsione)

La frazione corrisponde alla parte frazionaria normalizzata del numero, normalizzata perché l'esponente è regolato in modo che il bit iniziale sia sempre 1. In questo modo, non è necessario memorizzarla e si ottiene un altro bit di precisione. Ecco perché esiste un bit implicito. È simile alla notazione scientifica, in cui si modifica l'esponente in modo che abbia una cifra a sinistra della virgola decimale. Tranne che nel formato binario, è sempre possibile modificare l'esponente in modo che il primo bit sia 1, perché esistono solo numeri 1 e 0.

La distorsione è il valore di distorsione utilizzato per evitare di dover memorizzare gli esponenti negativi. La distorsione per i numeri a precisione singola è 127 e 1.023 (decimale) per i numeri a precisione doppia. In Excel i numeri vengono memorizzati utilizzando la precisione doppia.

Esempio in cui sono utilizzati numeri molto grandi

In una nuova cartella di lavoro, immettere quanto segue:

A1: 1.2E+200
B1: 1E+100
C1: =A1+B1 

Il valore risultante nella cella C1 sarà 1,2E+200, lo stesso valore della cella A1. Se infatti si confrontano le celle A1 e C1 utilizzando la funzione SE, ad esempio SE(A1=C1), il risultato sarà VERO. Ciò è dovuto allo standard IEEE che consente di archiviare solo 15 cifre significative di precisione. Per poter archiviare il calcolo precedente, Excel richiederebbe almeno 100 cifre di precisione.

Esempio in cui sono utilizzati numeri molto piccoli

In una nuova cartella di lavoro, immettere quanto segue:

A1: 0.000123456789012345
B1: 1
C1: =A1+B1 

Il valore risultante nella cella C1 sarà 1,00012345678901 anziché 1,000123456789012345. Ciò è dovuto allo standard IEEE che consente di archiviare solo 15 cifre significative di precisione. Per poter archiviare il calcolo precedente, Excel richiederebbe 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 della cartella di lavoro Precisione come visualizzata oppure Imposta precisione come visualizzata.

Metodo 1: funzione ARROTONDA

Utilizzando i dati precedenti, nell'esempio seguente viene utilizzata la funzione ARROTONDA per forzare un numero a cinque cifre. Ciò consente di confrontare correttamente il risultato con un altro valore.

A1: 1.2E+200
B1: 1E+100
C1: =ROUND(A1+B1,5) 

Il risultato è 1,2E+200.

D1: =IF(C1=1,2E+200, VERO, FALSO)

In questo modo viene restituito il valore VERO.

Metodo 2: precisione come visualizzata

In alcuni casi, è possibile evitare che gli errori di arrotondamento influiscano sul lavoro utilizzando l'opzione Precisione come visualizzata. Questa opzione forza il valore di ogni numero del foglio di lavoro come valore visualizzato. Per attivare questa opzione, attenersi alla seguente procedura.

1. Nel menu File, fare clic su Opzioni e successivamente sulla categoria Impostazioni avanzate.
2. Nella sezione Durante il calcolo della cartella di lavoro, selezionare la cartella di lavoro desiderata e quindi selezionare la casella di controllo Imposta precisione come visualizzata.

Ad esempio, se si sceglie un formato numerico che mostra due posizioni decimali e quindi si attiva l'opzione Precisione come visualizzata, quando si salva la cartella di lavoro viene persa tutta l'approssimazione oltre le due posizioni decimali. Questa opzione influisce sulla cartella di lavoro attiva, inclusi tutti i fogli di lavoro. Non è possibile annullare questa opzione e ripristinare i dati persi. Prima di abilitare questa opzione, è consigliabile salvare la cartella di lavoro.

Numeri binari periodici e calcoli con risultati prossimi allo zero

Un altro problema che crea confusione in relazione alla memorizzazione dei numeri a virgola mobile nel formato binario consiste nel fatto che alcuni numeri finiti e non periodici su base decimale sono e periodici nel sistema binario. L'esempio più comune è il valore 0,1 e le relative varianti. Anche se questi numeri possono essere rappresentati perfettamente in base 10, lo stesso numero in formato binario archiviato nella mantissa diventa il seguente numero binario periodico:

000110011001100110011 (e così via)

Lo standard IEEE 754 non prevede alcuna tolleranza speciale per qualsiasi numero. Memorizza ciò che è possibile nella mantissa e tronca il resto. Ciò comporta un errore di circa -2,8E-17 o 0,000000000000000028 durante la memorizzazione.

Anche frazioni decimali comuni, ad esempio i decimali 0,0001, non possono essere rappresentate esattamente nel sistema binario. (0,0001 è una frazione binaria ripetuta con un punto di 104 bit). Questo è simile al motivo per cui la frazione 1/3 non può essere rappresentata esattamente in numero decimale (0,33333333333333333333).

Ad esempio, se si prende in considerazione il seguente esempio semplice in Microsoft Visual Basic, Application Edition:

   Sub Main()
      MySum = 0
      For I% = 1 To 10000
         MySum = MySum + 0.0001
      Next I%
      Debug.Print MySum
   End Sub

Come risultato, verrà stampato 0,999999999999996. Il piccolo errore nella rappresentazione di 0,0001 nel sistema binario si estende alla somma.

Esempio: aggiunta di un numero negativo

1. In una nuova cartella di lavoro, immettere quanto segue:

   A1: =(43,1-43,2)+1

2. Fare clic con il pulsante destro del mouse sulla cella A1, quindi fare clic su Formato celle. Nella scheda Numero, fare clic su Scientifico nella Categoria. Impostare le Posizioni decimali su 15.

Invece di visualizzare 0,9, in Excel viene visualizzato 0,899999999999999. Poiché (43,1-43,2) viene calcolato per primo, -0,1 viene memorizzato temporaneamente e l'errore di memorizzazione -0,1 viene introdotto nel calcolo.

Esempio: quando un valore raggiunge zero

1. In Excel 95 o versioni precedenti, in una nuova cartella di lavoro immettere quanto segue:

   A1: =1,333+1,225-1,333-1,225

2. Fare clic con il pulsante destro del mouse sulla cella A1, quindi fare clic su Formato celle. Nella scheda Numero, fare clic su Scientifico nella Categoria. Impostare le Posizioni decimali su 15.

Invece di visualizzare 0, in Excel 95 viene visualizzato -2,22044604925031E-16.

Excel 97, tuttavia, ha introdotto un'ottimizzazione che cerca di correggere questo problema. Se un'operazione di addizione o sottrazione comporta un valore pari o molto vicino a zero, Excel 97 e versioni successive compenserà qualsiasi errore introdotto come risultato della conversione di un operando nel e dal sistema binario. Se l'esempio precedente viene eseguito in Excel 97 e versioni successive, viene visualizzato correttamente il numero 0 o 0,000000000000000E+00 in notazione scientifica.

Per ulteriori informazioni sui numeri a virgola mobile e sullo standard IEEE 754, consultare i seguenti siti Web:

• https://www.ieee.org
• https://steve.hollasch.net/cgindex/coding/ieeefloat.html