Analizzare i dati con Excel

Articolo pubblicato il 10/05/2019 Da Donata Petrelli e rilasciato con licenza CC BY-NC-ND 3.0 IT (Creative Common – Attribuzione – Non commerciale – Non opere derivate 3.0 Italia)

Ottenere informazioni con la Gaussiana

Analizzare i dati, capirne il loro significato intrinseco, è il primo passo verso la conoscenza e la possibilità di prendere delle decisioni ottimali in tutti gli ambiti della nostra vita. Per poter far questo occorro innanzitutto dei modelli matematici al fine di dare loro una struttura logica in grado di descrivere il fenomeno rappresentato dai dati osservati. Utilizzando questi modelli poi l’utente ha la possibilità di trovare la risposta ottimale all’attività che deve accingersi a compiere.

Tra tutti i modelli statistici oramai noti, quello di uso più comune è la distribuzione di Gauss, detta anche distribuzione “Normale” dato che, di norma, è utilizzata come prima approssimazione per descrivere il comportamento di valori reali causali che tendono a concentrarsi attorno a un singolo valor medio. Anche se il concetto sottostante possa risultare sofisticato e, a volte, di difficile comprensione, l’uso del modello gaussiano per l’interpretazione dei dati è in realtà molto diffuso ed applicato in moltissimi campi, dalla medicina alla sociologia all’economia e finanza.

Questo articolo non vuole essere una spiegazione accademica della distribuzione di Gauss, non sarebbe neanche il posto giusto per farlo 🙂 ma uno spunto per capirne l’enorme portata in ambito decisionale e come poter utilizzare questo potente strumento tramite Excel.

La Media e la Deviazione Standard

In generale, i modelli matematici nascono come “contenitori” di una formula, più o meno complessa, in grado di spiegare la logica del fenomeno reale da rappresentare. E’ importante dunque conoscere la funzione di Gauss sottostante il modello ma, prima ancora, due concetti fondamentali su cui si basa la stessa: la media e la deviazione standard.

Tutti noi utilizziamo la MEDIA nel linguaggio comune per descrivere attraverso un solo numero il comportamento di un fenomeno che varia nell’arco di un certo periodo di tempo. Ad esempio esprimiamo con un costo medio mensile la spesa che sosteniamo per la nostra auto e che varia nell’arco dell’anno. Il valore medio è quindi una indicazione della spesa sostenuta mensilmente tenendo conto che ci sono mesi in cui i consumi sono maggiori ed altri in cui invece sono minori. La formula della media è semplice, basta sommare tutti i valori osservati nel periodo di tempo e dividere il risultato per il numero totale delle osservazioni effettuate. Utilizzando un foglio di calcolo la formula è ancora più semplice perché è presente nelle librerie di Microsoft e reperibile dal menu ‘Formule’ alla categoria ‘Altre funzioni/Statistica’.

Spesso un solo numero però non è sufficiente a riassumere il comportamento di un fenomeno che si evolve nel tempo ma occorre conoscere anche un altro aspetto che ne descriva la sua variabilità, ovvero la sua tendenza a manifestarsi in modalità tra loro differenti e distanti nel periodo di tempoA misurare questa variabilità è preposto un altro indicatore statistico molto importante, di cui facciamo un uso abituale spesso inconsapevolmente, la DEVIAZIONE STANDARDQuesto indicatore misura la propensione di un certo fenomeno ad ‘allontanarsi’ nel tempo da un suo valore preso a riferimento, il valore medio nel nostro caso. Può essere chiamato anche ‘scarto quadratico medio’ per via della sua formula: la radice quadrata della media aritmetica delle differenze elevate al quadrato tra i valori osservati della distribuzione {x1, x2, … , xN} ed il valore medio (μ).

Il calcolo della funzione con Excel risulta semplice in quanto è delegato alla funzione DEV.ST.P, richiamabile sempre dal menu ‘Formule/Altre funzioni/Statistica’.

Una volta ottenuti i due indicatori, valor medio e deviazione standard, possiamo farci già una idea del ‘carattere’ del fenomeno in oggetto:

  1. sappiamo infatti a quale valore tende nel tempo (la media) e quanto è volatile attorno ad esso.
  2. Se la deviazione standard (σ) è una misura grande, i valori della distribuzione sono dispersi rispetto al loro valor medio di riferimento. Viceversa, se la deviazione standard è piccola, i valori sono concentrati vicino alla media.

Il modello Gaussiano (la campana)

Il modello di Gauss non fa che interpretare il comportamento di una distribuzione di valori attorno ad un valore di riferimento con una certa variabilità che ne misura la loro dispersione intorno ad esso. In particolare si verifica spesso la situazione in cui i valori si distribuiscano in modo tale che la loro densità, misurata tramite la deviazione standard, risulti crescente a mano a mano che si raggiunge il centro, la media dei valori, qui toccano il punto massimo, quindi ricomincino a decrescere fino ad arrivare allo zero. In questo caso si parla di valori che si distribuiscono ‘normalmente’ seguendo un grafico caratteristico a forma di campana.


Fig. 1 – Distribuzione gaussiana o normale

Tutte le distribuzioni normali hanno la stessa tipica forma a campana, ciò che cambia sono due aspetti:

  • la loro altezza
  • la loro larghezza

La forma, caratterizzata da una altezza e una larghezza, dipende dal valore della varianza, cioè dalla loro ‘densità’ o variabilità. Ciò significa che distribuzioni con forte densità avranno una rappresentazione Gaussiana con la campana di ampiezza maggiore rispetto a quelle con minor densità. Inoltre, tenendo in considerazione che la curva è una funzione di probabilità, per cui la probabilità che comunque si verifichi un valore tra -∞ e +∞ sarà sempre uguale ad 1, ne deriva che l’area sottesa dalla campana è sempre uguale a 1. Per questo motivo variando la larghezza, con la variazione del valore di σ, conseguentemente varia anche l’altezza. L’altro aspetto da considerare è la posizione della campana nell’asse delle ordinate, e questo dipende dal valore medio μ. Più è grande e più si troverà spostato verso destra dell’asse x.


Fig. 2 – Esempi di funzioni di Gauss

Da un primo esame grafico della curva a campana possiamo già seguire una analisi di tipo grafico della distribuzione dei valori ed intuire il tipo di distribuzione delle probabilità.

La funzione di Gauss

Dalla teoria della distribuzione normale di probabilità di variabili causali nasce il modello di Gauss, rappresentato tramite una ‘campana’. La formula sottostante il modello è la funzione della distribuzione normale che, dipendente esclusivamente dai due parametri μ e σ, si può semplicemente indicare con 

N(μ , σ)

Una variabile x che si distribuisca come la funzione Gaussiana si indica con: x ~ N(μ , σ)

Per i più curiosi scriviamo anche la formula della funzione matematica:

Formula di Gauss

con x ϵ Ɍ e dove:

  • μ è la media aritmetica dei valori
  • σ è la deviazione standard dei valori
  • σ2 è la varianza

La VARIANZA è un’altra variabile statistica atta a misurare la variabilità dei valori ed è la media aritmetica dei quadrati delle distanze dei valori dal valor medio μ. Si usa al posto della deviazione standard perché elimina il problema dell’uso della radice quadrata trattandosi del quadrato della deviazione standard.

Significato

Ma qual è il significato di questa curva a campana? Come va letta? Che informazioni possiamo ottenere da essa? … dobbiamo tirar fuori un po’ di matematica 🙂

Dal momento che si tratta di una funzione di probabilità l’area sottesa alla curva è uguale ad 1. Pertanto l’area a destra del valore medio μ misura 0,5 come pure l’area a sinistra di μ misura 0,5, data la simmetria della ‘campana’ rispetto all’asse x = μ. Esistono tuttavia altre aree caratteristiche sottese alla curva che sono individuate dalla deviazione standard σ, ovvero dalla distanza dal valore medio, e che corrispondono a delle probabilità ben definite. Queste aree caratteristiche rappresentano le diverse probabilità che i valori x della distribuzione assumono in relazione al centro μ della distribuzione.


Fig. 3 – Intervalli di confidenza

Dall’immagine riportata si deduce che il 68,2% (ovvero 34,1% + 34,1%) dei dati di una distribuzione normale cade in un preciso intervallo che ha centro nel valor medio e raggio uguale a σ. 

Questa lettura della gaussiana ha un’enorme importanza e una vastissima applicazione in diversi ambiti. Nel caso di modelli statistici predittivi per la finanza, se troviamo ad esempio che una distribuzione di valori di prezzi segua una distribuzione normale con centro μ = 10,5 e deviazione standard σ = 0,16, avremo una probabilità pari al 68,26% che un nuovo prezzo assuma un valore compreso tra (10,5 – 0,16) e (10,5 + 0,16) ovvero sia maggiore di 10,34 e minore di 10,66.

Gauss con Excel

Utilizzando Excel il nome della funzione è DISTRIB.NORM.N, dal menu ‘Formule/Altre funzioni/Statistica’. La sua sintassi è:

DISTRIB.NORM.N(x; media; dev_standard; cumulativo)

dove:

  • x è il valore per il quale si vuole calcolare la distribuzione
  • media è la media aritmetica della distribuzione
  • dev_standard è la deviazione standard della distribuzione 
  • cumulativo è un valore logico che determina la forma assunta dalla funzione. Se VERO, DISTRIB.NORM.N restituirà la funzione distribuzione cumulativa, se è FALSO restituirà la funzione massa di probabilità. 

Per il suo utilizzo e la creazione del relativo grafico procediamo dapprima calcolando la media e la deviazione standard con le funzioni descritte sopra. Quindi a partire da un elenco di valori disposti su di una colonna, a partire ad esempio da A2, che fungono da variabile x della funzione possiamo trovare il relativo valore y grazie alla funzione DISTRIB.NORM.N. Pertanto in B calcoliamo i relativi valori y nel modo seguente:

B2= DISTRIB.NORM.N.(A2,media,deviaizione standard, FALSO)

Trasciniamo con il mouse il valore nelle celle sottostanti la B2 grazie a al carattere di riempimento +. Ora selezioniamo le nostre due colonne ‘A’ e ‘B’ ed inseriamo in grafico dal menu ‘Inserisci/ Grafici consigliati’. Quello che più mi piace per il grafico della campana è un grafico a dispersione.


Fig. 4 – Grafico Excel della distribuzione normale

Cosa abbiamo visto

In questo articolo abbiamo parlato di:

  • Excel
  • Modelli statistici
  • Analisi statistica 
  • Funzioni di probabilità gaussiana
  • Indicatori statistici

Approfondimenti

La distribuzione Gaussiana è tradizionalmente il metodo più diffuso per descrivere il comportamento dei possibili valori di un eventoTuttavia non sempre è facile poter stimare parametri essenziali come il valor medio o la deviazione standard. Esistono pertanto metodi avanzati per la modellazione statistica offerti dall’Intelligenza Artificiale le cui applicazioni permettono di delegare al modello sottostante di AI la distribuzione dei dati e la relativa classificazione. 

Questo breve articolo non ha la pretesa di descrivere questi metodi per cui invece consiglio la lettura di testi e manuali specifici. Tra questi ultimi libri mi permetto di suggerire “Excel e Intelligenza Artificiale per il Trading”, non solo perché sono anche una degli autori, ma perché contiene la descrizione di un modello previsionale per i mercati finanziari che si avvale sia di metodi avanzati, come la clusterizzazione, che tradizionali come la distribuzione gaussiana. Potete trovarlo a questo indirizzo:

Se ti è piaciuto questo articolo sarò felice se vorrai condividerlo con chi pensi possa interessargli

Buon Excel 🙂

Torna su