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

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 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

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. 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: 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: 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: 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.

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: 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 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: Per ulteriori informazioni sui numeri a virgola mobile e la specifica IEEE 754, visitare i seguenti siti Web (informazioni in lingua inglese):

Per ulteriori informazioni su come ovviare a questi errori, fare clic sul numero dell'articolo della Microsoft Knowledge Base riportato di seguito: