La Precisione di Calcolo con Excel

Calcolo numerico Vs Calcolo digitale

Articolo pubblicato il 30/11/2019 Da Fabrizio Cesarini e rilasciato con licenza CC BY-NC-ND 3.0 IT (Creative Common – Attribuzione – Non commerciale – Non opere derivate 3.0 Italia)

Chi non ha mai giocato a “Dune II” o qualsiasi altro videogioco fino ad arrivare al punto tale da esserne assorbito quasi totalmente? Superare il muro e passare al livello successivo diventavano veri obiettivi personali! E per fare questo spesso si arrivava a “truccare” il nostro PC (la Vespa era già sistemata!). Questo perché più aumentava la difficoltà del gioco più la nostra abilità non era più sufficiente per vincere, occorreva anche un computer adeguato per la situazione, quindi con prestazioni maggiori, un processore o una scheda video o audio più potenti.

Così accade ora (da grandi!) quando dobbiamo eseguire calcoli al computer. Più il calcolo che dobbiamo fare deve essere preciso, maggiore è la precisione richiesta allo strumento di calcolo per eseguirli. A seconda poi dell’obiettivo e degli standard richiesti, ognuno di noi valuta ed utilizza il software adeguato alle esigenze contingenti. Senza spingerci troppo avanti con software di calcolo elaborati e nati per settori di nicchia, come la ricerca scientifica, sicuramente Excel è il più diffuso strumento di calcolo al mondo e, come tale, il suo impiego è diventato orami uno standard. In questo senso possiamo dire che Excel è diventata la moderna calcolatrice! (non me ne voglia nessuno in Microsoft … so che è molto di più!).

Se anche voi siete come me dunque e, quale sia la vostra professione o ruolo nel mondo, quando bisogna fare qualsiasi tipo di conto, utilizzate sempre come primo strumento di calcolo Excel, allora vi sarà capitato di notare alcune volte delle stranezze in certi risultati … questo è dovuto alla rappresentazione dei numeri nel foglio di calcolo.

Il problema di fondo in tutto questo è che il mondo reale è molto differente da quello simulato e riprodotto da un calcolatore. Mentre noi ragioniamo cercando di tradurre il mondo reale in numeri, questi numeri sono ottenuti implementando degli algoritmi che verranno elaborati da un calcolatore. I risultati dei calcoli fatti con elaboratori, come un foglio di calcolo, sono dunque dei “compromessi”.

In questo articolo voglio parlare della differenza tra il calcolo numerico e la sua rappresentazione nel foglio di calcolo e di come possiamo superare questo divario attraverso l’applicazione di tecniche di programmazione in Excel che risolvono brillantemente il problema senza farci cambiare il software di calcolo

… e passare così al muro successivo usando lo stesso PC come nei videogiochi!

Rappresentazione dei dati numerici

In generale, quando facciamo dei calcoli intervengono:

  • dei dati iniziali
  • delle operazioni ammesse su questi dati
  • dei dati finali o risultati

Tutto ciò, dati ed operazioni permesse su di essi, lo chiamiamo tipi di dati.

Nel mondo reale i tipi di dati numerici sono gli insiemi di numeri della matematica N, Z, Q, R ovvero i numeri naturali, interi, razionali e reali (non approfondiamo questo argomento … già tanto noioso a scuola) mentre quando affidiamo questi conti ad un elaboratore, come con un normale foglio di calcolo, disponiamo di un numero ristretto di rappresentazioni numeriche principalmente numeri interi (senza virgola) e numeri decimali (con la virgola) declinati in vari modi.

I numeri in virgola mobile sono una rappresentazione dei corrispondenti numeri reali della matematica, una sorta di notazione scientifica in cui il numero è espresso sempre come prodotto tra un numero con la virgola, dotato di segno, con una sola cifra a parte intera ed una potenza con esponente relativo ma di base non 10 bensì 2! Ovvero così:

N = ± (0.a1a2…an) × 2^p

dove:

± è il segno del numero (positivo o negativo)

a1a2…an, chiamato mantissa, è un numero in valore assoluto e rappresenta le cifre essenziali

p, l’esponente, è un numero relativo ed esprime la precisione della rappresentazione

Poiché il calcolatore ha uno spazio limitato, ovviamente anche la rappresentazione dei numeri lo è. Precisamente la rappresentazione dei numeri in virgola mobile avviene solitamente su 32 bit così suddivisi: 1 bit per il segno, 8 bit per l’esponente e 23 bit per la mantissa.

Qui nascono gli intoppi. Nel calcolo digitale dobbiamo necessariamente far i conti tenendo presente che la rappresentazione dei numeri deve essere contenuta all’interno di uno spazio limitato!

I calcoli

Il nodo gordiano di tutto è che mentre noi ragioniamo in un sistema di numerazione decimale, il calcolatore lavora con un sistema di numerazione binario, lavora cioè con bit, byte e multipli di essi. Per far colloquiare il mondo reale (decimale) con quello digitale (binario) si è dovuto trovare un modo per cui ad ogni numero decimale corrispondesse in modo unico un numero binario. Questa corrispondenza biunivoca tra i due mondi ha inevitabilmente portato a delle conseguenze limitanti.

In primo luogo, poiché ogni dato deve essere memorizzato in un gruppo di bit ne segue che i dati numerici devono essere limitati, hanno cioè un minimo ed un massimo rappresentabile … cosa non da poco!

Il secondo aspetto importante è che la rappresentazione digitale dei numeri reali, i numeri in virgola mobile o floating point, offre un numero finito di cifre significative di precisione, il che diventa un grosso limite nel caso un numero reale abbia infinite cifre decimali. Ciò comporta un primo errore di arrotondamento che viene poi iterato ed amplificato nell’esecuzione di calcoli che poi vedremo.

Infine, Il fatto che il numero di cifre in base 2 è notevolmente maggiore di quello del numero in base 10 porta spesso a grosse incongruenze. Può capitare ad esempio che numeri che nella realtà hanno una rappresentazione finita, come ad esempio i numeri razionali non periodici, poi nella rappresentazione digitale non lo sono.

Come vediamo i limiti dovuti alla rappresentazione digitale dei numeri reali è la causa di quelle anomalie che si verificano a volte quando facciamo dei calcoli con software come il foglio di Excel.

I dati in Excel

Excel utilizza la rappresentazione dei numeri in virgola mobile a precisione doppia, chiamati Double, che ricoprono tutto l’intervallo dei numeri decimali che va da -1,79769313486231E308 a -4,94065645841247E-324 per i numeri negativi e da 4,94065645841247E-324 a 1,79769313486232E308 per i numeri positivi (la E si utilizza per convenzione invece di indicare la base 10). In questo caso si usano 64bit per la rappresentazione di un numero reale definito appunto numero reale “lungo”.

Se abbiamo bisogno pertanto di particolare accuratezza per i nostri calcoli, Excel rappresenta un buon strumento dal momento che utilizza il tipo double, il più comune fra i linguaggi di programmazione e che soddisfa la maggior parte dei casi.

Tuttavia ci sono situazioni in cui questa accuratezza non si dimostra sufficiente perché si utilizzano dati ad alta precisone. Basti pensare a numeri come la massa della terra (5973600000000000000000000 kg  circa) o la massa dell’elettrone (0.000000000000000000000000000000091 Kg circa) … come possiamo gestire numeri di questo tipo?

Esempio con Excel

Senza disturbare l’astronomia o la fisica nucleare, basti pensare che Excel viene utilizzato abitualmente per i calcoli nelle aziende finanziarie. In questo ambito, dovendo elaborare quantità numeriche con molti decimali, se si utilizza il tipo double potrebbero verificarsi anomalie (dovute appunto agli arrotondamenti) e, quindi, alla fine pericolosi errori nei risultati dei calcoli finanziari.

Se ad esempio vogliamo calcolare il valore futuro di un capitale che vorremmo investire oggi, questa operazione di capitalizzazione si traduce in matematica finanziaria con una successione ricorsiva. E spesso, proprio nel calcolo delle successioni numeriche, Excel commette vere e proprie ‘stranezze’.

Un esempio. Prendiamo la successione numerica:

a0 = 1/3

an+1 = 4an − 1

Si tratta della successione costante di valore 1/3, ma che secondo Excel diverge a −∞. Provate voi stessi a riportare il semplice conto su un foglio di Excel e vi accorgerete che già al terzo passo i valori della successione an cominciano a diminuire, passando dal valore costante 0,333333333333333 (=1/3) al valore nullo corrispondente alla ventisettesima iterazione (a27). Da quel punto, la successione assume valori negativi via via minori tendenti a −∞.

Si tratta questo di un tipico esempio di come l’utilizzo di un tipo di dati a più bassa precisione causi errori colossali nei calcoli.

Probabilmente starete pensando che a voi questo problema non riguarda. I vostri calcoli non necessitano di tutta questa precisione. Sicuri?

Vi faccio un altro esempio preso dal sito di supporto di Microsoft.

Provate ad inserire in una cella una semplice formula come questa:

=( 43,1 – 43,2 ) + 1

Non sono numeri particolarmente grandi e strani. A tutti potrebbe capitare di fare un calcolo come questo.

Il risultato dovrebbe essere 0,9 ed effettivamente il nostro fido Excel riporterà diligentemente un bel 0,9.

E allora dove sta il problema ? Provate ad aumentare il numero di decimali visualizzati nella cella fino a 15 e noterete che il numero diventerà 0,8999999999999990000. Non è proprio uguale. Pensate se il vostro commercialista facesse i conti con le vostre tasse senza tenere conto di questo fatto.

La soluzione

A questo punto? No panic … potremmo adottare una diversa rappresentazione dei numeri reali che ne estenda l’intervallo e la modalità di rappresentazione. In questi casi infatti si può utilizzare un tipo di dati più appropriato per i calcoli che richiedono un numero elevato di cifre significative e senza errori di arrotondamento.

Il tipo Decimal, dichiarato come Variant, rappresenta i numeri decimali compresi tra 79.228.162.514.264.337.593.543.950.335 positivo e 79.228.162.514.264.337.593.543.950.335 negativo e, anche se non ne elimina la necessità, riduce al minimo gli errori dovuti all’ arrotondamento.

A tale proposito, per comprendere la differenza tra i due tipi di dati ed il loro utilizzo nei calcoli, facciamo un semplice esempio. Realizziamo con linguaggio VBA (Visual Basic for Aplication) due funzioni che sommano 10.000 volte il numero 0,0001 e la cui somma deve restituire 1. La prima funzione, ConteggioDouble, utilizza il tipo di dati Double, la seconda funzione, ConteggioDecimal, il tipo di dati Decimal. Eccone il codice:

Funzione 1 (con i numeri di tipo Double)

Public Function ConteggioDouble() As Double

            Dim X As Double

            Dim I As Integer

X=0#

For I = 1 To 10000

     X = X + CDbl(0.0001)

Next I

ConteggioDouble = X

End Function

Funzione 2 (con I numeri di tipo Decimal (Variant))

Public Function ConteggioDecimal() As Variant

Dim X As Variant

            Dim I As Integer

X=0#

For I = 1 To 10000

                 X = X + CDec(0.0001)

Next I

ConteggioDecimal = X

End Function

Il risultato delle due funzioni è sorprendente.

Mentre la prima funzione con i double restituisce il valore 0,999999999999906, la seconda funzione con i decimal (variant) il valore corretto 1!

A fronte di una maggiore occupazione di memoria e di richiesta di potenza computazionale, adottare il tipo di dati Decimal si dimostra più preciso nei calcoli in Excel

come per i videogiochi potenziavamo il PC per passare al muro successivo così abbiamo fatto con Excel per utilizzare lo stesso strumento per calcoli che richiedono una precisione maggiore.

Cosa abbiamo visto

In questo articolo abbiamo parlato di:

  • rappresentazione numerica
  • tipi di dati
  • calcolo numerico
  • precisione dei calcoli
  • Excel
  • Funzioni in VBA

Note e Approfondimenti

Questo vuole essere solo un articolo che mette l’accento sull’importanza della precisione di calcolo in generale ma che, da solo, non ha certo la pretesa di esaurire tutti i casi che si possono riscontrare nelle varie discipline e che riguardano diversi tipi di calcoli. D’altronde si sa, la matematica è ovunque e la precisone del risultato è una condizione richiesta a qualsiasi professionista.

È difficile trovare testi che trattano nello specifico questo argomento, la precisione di calcolo. Semmai è più facile trovare riferimenti in testi e manuali che trattano di argomenti specifici. Durante la spiegazione è stato utilizzato un codice VBA tratto dal libro “Excel e intelligenza artificiale per il trading”, in cui è presente un intero capitolo dedicato alla programmazione in VBA per Excel.

Il libro lo potete trovare a questa pagina

Oppure su Amazon a questo indirizzo :

In questo caso si tratta di un testo settoriale che tratta anche concetti base di programmazione per chi non è programmatore ed affronta il problema della precisione di calcolo in campo finanziario.

Anche se dovrei spingervi ad acquistare il testo, dal momento che ne sono uno degli autori, mi sento però di chiedervi se vi interessa questo argomento. Se la risposta è affermativa allora sarò felice di dedicare le prossime festività a scrivere articoli su questi argomenti ed affrontare molti altri casi matematici … anziché accompagnare la famiglia per shopping pre-natalizio (vi prego salvatemi dallo shopping pre-natalizio!!)

Torna su