Opzioni di Borsa in laboratorio – 19 – Soluzione dei Minimi Quadrati: Excel PREVISIONE

rubrica: 

La volta scorsa abbiamo detto che a fronte di un esperimento di osservazioni di coppie di valori campione (il tempo e la velocità di una biglia) è possibile tracciare una retta che rappresenta la soluzione ‘approssimata migliore’.

Poiché qualsiasi retta del piano è rappresentata sempre da una equazione del tipo Y=AX+B, il compito di oggi sarà quello di calcolare A e B tramite il metodo scoperto da Gauss / Laplace a fine 700.
Diversamente dalle notazioni standard di matematica in cui X e Y sono le incognite, in questo caso X e Y sono solo coppie di valori campione mentre le nostre incognite saranno A e B.
Mi trovo così a risolvere Y=AX+B  che presenta due incognite (A e B) e non una soltanto.
Come è possibile? In geometria analitica non ci si riesce, a meno che non si abbiano altre informazioni in più.
Esatto: non basta una sola informazioni, ne servono 2. Con 2 informazioni (ovviamente diverse) vedrete che ce la faremo.
Pare dunque che con 2 equazioni (di primo grado) messe a ‘Sistema’ si possa trovare la quadra.
Per nostra fortuna, a risolvere la questione ci ha già pensato Gauss con il suo Sistema (ove per Sistema si intende una coppia di equazioni) dei Minimi Quadrati.
 
Calma: partiamo dalla tabella dei casi della volta scorsa:

Poi, a destra, attacchiamo due colonne grigie nuove di zecca:


Nella prima (molt), abbiamo moltiplicato X per Y, nella seconda (pot) abbiamo messo le potenze di X.
 
A questo punto creiamo una riga di totali in cui ho evidenziato alcune celle con colori diversi.

Ecco fatto:

Vediamo i singoli colori:
 
Verde (8) è il numero delle prove
Azzurro (20) è la somma dei punti X di rilevazione dell’esperimento (il tempo nell’esempio)
Arancio (37) è la somma dei punti Y di rilevazione dell’esperimento (la velocità nell’esempio)
Blu (25) è la somma delle moltiplicazioni dei vari X e Y
Rosso (92) è la somma delle potenze di X
 
Ora Gauss afferma:

Per risolvere il Sistema e trovare A e B sono possibili due strade:

  1. Metodo di Cramer http://it.wikipedia.org/wiki/Regola_di_Cramer
  2. Metodo di Sostituzione

Scegliamo la seconda, un po’ più lunga ma più comprensibile:

Il ‘trucco’ <per così dire> sta nella partenza, cioè di ricavare A da ciascuna delle 2 equazioni (Rossa e Verde) e di porle uguali.
Con un po’ di calcoli otteniamo finalmente la soluzione: y = -1,6071 X + 8,6428
Bene!
Noi siamo arrivati fin qui con la teoria, vediamo se i risultati di Excel coincidono.
Per farlo, proviamo a interpolare X tra due valori qualsiasi, per esempio tra 6 e 7.
 
Cerchiamo dunque il corrispondente Y nella retta di regressione lineare del punto X = 6,3.
Sostituiamo così:
Y=(-1,6071*6,3)+8,6428 = -1,48
Anche Excel ci deve restituire -1,48.
Vediamo:


Perfetto !   Bravo Excel !  Risultati perfettamente coincidenti !
La funzione Excel di questo foglio è:   =PREVISIONE(6,3;E63:E70;D63:D70)
 
Nota:
La sintassi di questa funzione è: PREVISIONE(x;y_nota;x_nota)
Attenzione a non scambiare l’ordine di y_nota con x_nota  … prima sempre le Y !!!


Per oggi abbiamo finito.
Abbiamo capito cosa si intende per regressione lineare, interpolazione ed estrapolazione.
Attenzione però: non montiamoci subito la testa! Sì, perché qualcuno potrebbe osservare: che bello! Abbiamo trovato la formula magica della felicita !!! Evviva !!!
Eh già!   Se faccio divorare alla funzione PREVISIONE di Excel l’intero FTSEMIB sono a posto per sempre perché sarà Excel a dirmi come andrà la Borsa domattina!
Eh no, piano Signori!
E sapete perché?
Ma semplicemente perché una delle nostre premesse era la parola ‘lineare’ , cioè ‘assimilabile a una linea’ . E sennò perché si chiamerebbe Regressione LINEARE?
Ecco allora che se faccio divorare a “PREVISIONE” l’excel del FTSEMIB commetto uno sbaglio clamoroso, perché l’andamento della Borsa non è lineare affatto. Magari fosse! La Borsa va a zig-zag, è una random walk, come si suol dire.
E allora che si fa?  Delusione totale?
 
No, tranquilli, faremo una piccola variante, il trucco c’è ma non si vede.
Curiosi?  Peccato, dovete aspettare il prossimo contributo smiley.
Vi lascio in meditazione.
 
Francesco Caranti