14 settembre 2008

Calcolare i valori di best-fit usando la funzione TENDENZA()

In questo articolo ci sono dei riferimenti al precedente: “Usare la regressione lineare semplice ”.
Per questo articolo usiamo la stessa equazione di regressione di quello precedente: y=1407,6x+259800.
Il problema di utilizzare un grafico per il best-fit trendline è che non si ottengono valori actual per poterci lavorare.
Se si vogliono calcolare, bisogna farlo per ogni singola y utilizzando l’equazione di regressione y=mx+b (nel caso specifico y=1407,6x+259800).
Cosa succede, comunque, se cambiano i dati fonte del grafico?
Ad esempio, questi valori possono essere delle stime o possono cambiare nel momento in cui vengono forniti dati più accurati.
In quel caso, bisogna cancellare l’esistente trendline, aggiungerne uno nuovo e poi ricalcolare i valori del trendline basati sulla nuova equazione.

Se avete bisogno di lavorare con valori di trend sul foglio di lavoro, potete evitare le ripetute analisi di trendline usando la funzione di Excel : TENDENZA (y_nota;x_nota;nuova_x;cost).
La sintassi in inglese diventa TREND(known_y’, known_x’s, new_x’s, const).
La funzione restituisce i valori lungo una tendenza lineare, utilizzando il metodo dei minimi quadrati.
Essa calcola una retta che coincide con le matrici y_nota e x_nota e restituisce i valori y lungo la retta per la matrice di nuova_x specificata.

Vediamo gli elementi della sintassi della formula:
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. Un range di valori delle variabili x associate ad y. Se si omette, si sottintende (1,2,3….n) con le stesse dimensioni di y_nota.
nuova_x: sono i nuovi valori x per i quali TENDENZA restituirà i valori y corrispondenti. Un range di nuovi valori di x per i quali si vogliono determinare i valori corrispondenti di y
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, sulla base di x_nota. Se cost è FALSO, b verrà impostata a 0 e i valori m verranno corretti in modo che y = mx (l’ intercetta y verrà piazzata a 0).

Per generare i valori del best-fit trend, dovetei soltanto specificare l’argomento y_nota e opzionale l’argomento x_nota.
Nell’esempio dei trimestri, l’argomento y_nota è costituito dai valori actual di vendite, nel range C2:C13. I valori x_nota si trovano nel range B2:B13.
Per calcolare i valori del best-fit trend, inserite la formula seguente: =TENDENZA(C2:C13,B2:B13) come in figura 1.

Figura 1

I risultati della formula TREND() sono in colonna E.
Per una comparazione, il foglio include anche i valori di trend generati usando l’equazione di regressione del grafico. (Notate che alcuni valori sono leggermente diversi, dovuto a dei piccoli arrotondamenti per i valori dell’inclinazione e dell’intercetta).