18 settembre 2008

Calcolare i valori di best-fit usando la funzione REGR.LIN()

La funzione TENDENZA è la via più diretta per calcolare trend di valori, ma Excel offre un secondo metodo che calcola il coefficiente angolare (inclinazione della retta) e l’ intercetta y del trendline. Questi valori di m e b possono essere inseriti nell’equazione di regressione Y=mx+b per calcolare i valore di Y.
Calcoliamo m e b con la funzione REGR.LIN(y_nota;x_nota;cost;stat) la cui sintassi in inglese diventa: LINEST(known_y’s, known_x’s, const, stats)

Vediamo gli elementi della sintassi:
y_nota: è l'insieme dei valori y già noti dalla relazione y = mx + b.
x_nota: è un insieme facoltativo di valori x che possono essere già noti dalla relazione y = mx + b. Se x_nota è omesso, verrà considerato uguale alla matrice {1;2;3;...} che ha le stesse dimensioni di y_nota.
Cost: è un valore logico che specifica se la costante b deve essere uguale a 0. Se cost è VERO o è omesso, b verrà calcolata secondo la normale procedura. Se cost è FALSO, b verrà impostata su 0 è i valori m verranno corretti in modo che y = mx
Stat: è un valore logico che specifica se restituire statistiche aggiuntive di regressione oltre al coefficiente m e la costante b. Se stat è VERO, REGR.LIN restituirà le statistiche aggiuntive di regressione (descritto in guida Excel, tasto F1, voce formula REGR.LIN). Se stat è FALSO o è omesso, REGR.LIN restituirà solo i coefficienti m e la costante b.

Quando si usa REGR.LIN senza l’argomento cost, la funzione restituisce una matrice 1x2, dove il valore nella prima colonna è il coefficiente angolare del trendline e il valore nella seconda l’ intercetta (figura 1).
Inserire la seguente formula, come una formula matrice: REGR.LIN(C2:C13; B2:B13)
Vi ricordo che per fare questo, una volta creata la formula nella cella, premere in sostituzione del solo tasto Enter la combinazione Control+Shift+Enter per confermare la formula.
Premendo la combinazione dei tasti, Excel automaticamente aggiunge esternamente delle parentesi graffe, in quanto inserita come formula matriciale.

Figura 1

I valori restituiti sono inseriti in figura 1 nelle celle G2 e H2. Questo foglio di lavoro calcola, inoltre, i valori del trendline sostituendo nell’equazione della regressione lineare,colonna E, la cella $G$2 come valore di m e la cella $H$1 come valore di b. Quindi la seguente formula calcola il trend ad es. per il periodo 12: =$G$2 * B13 + $H$2 (figura 2).

Figura 2

Se nella sintassi della formula si aggiunge l’argomento VERO (figura3), la formula REGR.LIN restituisce 10 elementi statistici in una matrice 5x2 (per approfondire il loro significa, naviga all’interno della guida di Excel).

Figura 3

Nota che uno dei valori restituiti è R al quadrato che fornisce il valore della bontà dell’adattamento della regressione lineare stimata ai dati osservati (Fitting).
Se si vuole ottenere solo questo valore (Figura 4), inserire la formula INDICE(REGR.LIN(C2:C13; B2:B13; ; VERO); 3; 1) che in inglese diventa INDEX(LINEST(known_y’s, known_x’s,,TRUE),3,1)

Figura 4

Nota
Puoi anche calcolare il coefficiente angolare, l’ intercetta, e R al quadrato direttamente con delle funzioni:

PENDENZA(y_nota;x_nota) in inglese la sintassi diventa: SLOPE(known_y’s,known_x’s)
INTERCETTA(y_nota;x_nota) in inglese: INTERCEPT(known_y’s,known_x’s)
RQ(y_nota;x_nota) in inglese: RSQ(known_y’s,known_x’s)
Vi ricordo che la sintassi di queste formule per i primi due argomenti è la stessa della funzione TENDENZA() linkCalcolare i valori di best-fit usando la funzione TENDENZA() .L’ unica eccezione è che l’ elemento X-nota va inserito.