Funzione REGR.LIN

Descrizione

La funzione REGR.LIN calcola le statistiche per una linea utilizzando il metodo dei minimi quadrati per calcolare la retta che meglio rappresenta i dati e restituisce una matrice che descrive la retta. È inoltre possibile combinare REGR.LIN con altre funzioni per calcolare le statistiche per altri tipi di modelli con parametri sconosciuti lineari, come le serie polinomiali, logaritmiche, esponenziali e di potenze. Dal momento che questa funzione restituisce una matrice di valori, deve essere immessa come formula in forma di matrice. Le istruzioni sono riportate dopo gli esempi di questo articolo.

L'equazione della retta è:

y = mx + b

–oppure–

y = m1x1 + m2x2 + ... + b

in presenza di più intervalli di valori di x, dove i valori della variabile dipendente y sono una funzione dei valori della variabile indipendente x. I valori m sono coefficienti che corrispondono a ogni valore di x, mentre b è una costante. Si noti che y, x e m possono essere vettori. Il tipo di matrice restituito dalla funzione REGR.LIN è {mn;mn-1;...;m1;b}. REGR.LIN può inoltre restituire statistiche aggiuntive di regressione.

Sintassi

REGR.LIN(y_nota; [x_nota]; [cost]; [stat])

Gli argomenti della sintassi della funzione REGR.LIN sono i seguenti:

Sintassi

• y_nota    Obbligatorio. Insieme dei valori y già noti nella relazione y = mx + b.

  • Se l'intervallo di y_nota è in una singola colonna, ogni colonna di x_nota verrà interpretata come una variabile distinta.

  • Se l'intervallo di y_nota è in una singola riga, ogni riga di x_nota verrà interpretata come una variabile distinta.

• x_nota    Facoltativo. Insieme dei valori x che possono essere già noti nella relazione y = mx + b.

  • L'intervallo di x_nota può includere uno o più insiemi di variabili. Se viene utilizzata una sola variabile, y_nota e x_nota potranno essere intervalli di forma qualsiasi, purché con dimensioni uguali. Se vengono utilizzate più variabili, y_nota dovrà essere un vettore, ovvero un intervallo con altezza di una riga o larghezza di una colonna.

  • Se x_nota è omesso, verrà considerato uguale alla matrice {1;2;3;...} che ha le stesse dimensioni di y_nota.

• cost    Facoltativo. 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 e i valori m verranno corretti in modo che y = mx.

• stat    Facoltativo. Valore logico che specifica se restituire statistiche aggiuntive di regressione.

  • Se stat è VERO, REGR.LIN restituirà le statistiche aggiuntive di regressione; di conseguenza, la matrice restituita è {mn,mn-1,...,m1,b; sen,sen-1,...,se1,seb; r², sey; F;gdl; ssreg,ssresid}.

  • Se stat è FALSO o è omesso, REGR.LIN restituirà solo i coefficienti m e la costante b.

    Le statistiche aggiuntive di regressione sono le seguenti:

Nell'illustrazione seguente viene mostrato l'ordine in cui vengono restituite le statistiche aggiuntive di regressione.

Osservazioni

• È possibile descrivere una qualsiasi retta tramite i valori di pendenza e di intercetta di y:

  Pendenza (m):
  Per trovare la pendenza di una retta, spesso scritta come m, prendere due punti sulla retta (x1,y1) e (x2,y2); la pendenza è uguale a (y2 - y1)/(x2 - x1).

  Intercetta di Y (b):
  L'intercetta y di una retta, spesso scritta come b, è il valore di y nel punto in cui la retta interseca l'asse y.

  L'equazione di una retta è y = mx + b. Se sono noti i valori di m e di b, è possibile calcolare qualsiasi punto sulla retta inserendo il valore di y o di x nell'equazione. È inoltre possibile utilizzare la funzione TENDENZA.

• Quando è disponibile solo una variabile indipendente x, è possibile ricavare direttamente i valori di pendenza e di intercetta di y utilizzando le seguenti formule:

  Pendenza:
  =INDICE(REGR.LIN(known_y;known_x);1)

  Intercetta di Y:
  =INDICE(REGR.LIN(known_y;known_x);2)

• La precisione della retta calcolata dalla funzione REGR.LIN dipende dal grado di dispersione nei dati. Più i dati sono lineari, più il modello di REGR.LIN risulterà accurato. REGR.LIN utilizza il metodo dei minimi quadrati per determinare la retta che meglio rappresenti i dati. Quando si dispone solo di una variabile indipendente x, i calcoli per m e b vengono basati sulle formule seguenti:

  

  

  dove x e y sono medie campione, ovvero x = MEDIA(x_nota) e y = MEDIA(y_nota).

• Le funzioni di adattamento a linee e curve REGR.LIN e REGR.LOG consentono di calcolare la migliore retta o curva esponenziale adatta ai dati. Tuttavia, è necessario decidere quale dei due risultati meglio si adatta ai dati. È possibile calcolare TENDENZA(known_y;known_x) per una retta oppure CRESCITA(known_y, known_x) per una curva esponenziale. Queste funzioni, senza l'argomento della new_x , restituiscono una matrice di valori y previsti lungo la linea o la curva in corrispondenza dei punti dati effettivi. È quindi possibile confrontare i valori previsti con i valori effettivi. È consigliabile creare un grafico per entrambi per un confronto visivo.

• Nell'analisi della regressione, per ogni punto viene calcolato il quadrato della differenza tra il valore di y stimato per quel punto e il valore effettivo di y corrispondente. La somma dei quadrati delle differenze viene denominata somma residua dei quadrati, sqres. Viene quindi calcolata la somma totale dei quadrati, sqtot. Se cost = VERO o è omesso, la somma totale dei quadrati è la somma del quadrato delle differenze tra i valori effettivi di y e la media dei valori y. Se cost = FALSO, la somma totale dei quadrati è la somma dei quadrati dei valori effettivi di y senza la sottrazione della media dei valori y da ogni valore y. La somma della regressione dei quadrati, sqreg, si ottiene da sqreg = sqtot - sqres. Più piccola è la somma residua dei quadrati, confrontata con la somma totale dei quadrati, maggiore è il valore del coefficiente di determinazione, r², che è un indicatore di quanto bene l'equazione derivante dall'analisi di regressione spiega la relazione tra le variabili. Il valore di r² è uguale a sreg/sstotale.

• In alcuni casi, una o più colonne X (si supponga che Y e X siano in colonne) potrebbero non avere alcun valore predittivo aggiuntivo in presenza delle altre colonne X. In altre parole, l'eliminazione di una o più colonne X potrebbe portare a valori Y previsti altrettanto accurati. In questo caso queste colonne X ridondanti devono essere omesse dal modello di regressione. Questo fenomeno è chiamato "collinearità" perché qualsiasi colonna X ridondante può essere espressa come somma di multipli delle colonne X non ridondanti. La funzione REGR.LIN verifica la collinearità e rimuove tutte le colonne X ridondanti dal modello di regressione quando le identifica. Le colonne X rimosse possono essere riconosciute nell'output REGR.LIN come con coefficienti 0 oltre ai valori 0 se. Se una o più colonne vengono rimosse come ridondanti, la funzione ddl dipende dal numero di colonne X effettivamente utilizzate per scopi predittivi. Per informazioni dettagliate sul calcolo del formato df, vedere l'esempio 4. Se il formato df viene modificato perché vengono rimosse colonne X ridondanti, vengono interessati anche i valori sey e F. La collinearità dovrebbe essere relativamente rara nella pratica. Tuttavia, un caso in cui è più probabile che si verifichi è quando alcune colonne X contengono solo valori 0 e 1 come indicatori del fatto che un soggetto in un esperimento sia o meno membro di un particolare gruppo. Se cost = VERO o viene omesso, la funzione REGR.LIN inserisce di fatto una colonna X aggiuntiva di tutti gli 1 valori per modellare l'intercetta. Se si ha una colonna con 1 per ogni argomento se maschile o 0 in caso contrario, e si ha anche una colonna con 1 per ogni argomento se femminile o 0 se non, quest'ultima colonna è ridondante perché le voci in essa contenute possono essere ottenute sottraendo la voce nella colonna "indicatore maschile" dalla voce nella colonna aggiuntiva di tutti gli 1 valori aggiunti dalla funzione REGR.LIN .

• Il valore di gdl viene calcolato nel modo seguente, quando nessuna colonna X viene rimossa dal modello a causa della collinearità: se sono presenti k colonne di known_x e cost = VERO o viene omesso, gdl = n – k – 1. Se cost = FALSO, gdl = n - k. In entrambi i casi, ogni colonna X rimossa a causa della collinearità aumenta il valore di df di 1.

• Quando si immette come argomento una costante di matrice, ad esempio x_nota, utilizzare il punto e virgola (;) per separare i valori nella stessa riga e la barra rovesciata (\) per separare le righe. I caratteri separatori possono variare in base alle impostazioni internazionali.

• Si noti che i valori y stimati dall'equazione di regressione possono non essere validi qualora siano al di fuori dell'intervallo dei valori y utilizzati per determinare l'equazione.

• L'algoritmo sottostante utilizzato nella funzione REGR.LIN è diverso da quello utilizzato nelle funzioni PENDENZA e INTERCETTA. La differenza tra questi algoritmi può generare risultati diversi quando i dati sono indeterminati e collineari. Se ad esempio le coordinate dell'argomento y_nota sono 0 e le coordinate dell'argomento x_nota sono 1:

  • REGR.LIN restituisce il valore 0. L'algoritmo di tale funzione è stato progettato per restituire risultati accettabili per i dati collineari e in questo caso è possibile trovare almeno una risposta.

  • PENDENZA e INTERCETTA restituiscono un #DIV/0! . L'algoritmo delle funzioni PENDENZA e INTERCETTA è progettato per cercare una sola risposta e in questo caso possono esserci più risposte.

• Oltre a utilizzare REGR.LOG per calcolare le statistiche per altri tipi di regressione, è possibile utilizzare REGR.LIN per calcolare un intervallo di altri tipi di regressione immettendo funzioni delle variabili x e y come serie x e y di REGR.LIN. La formula seguente ad esempio:

  =REGR.LIN(valoriy, valorix^RIF.COLONNA($A:$C))

  funziona quando si dispone di una sola colonna di valori y e di una sola colonna di valori x per calcolare l'approssimazione cubica (polinomiale di ordine 3) della forma:

  y = m1*x + m2*x^2 + m3*x^3 + b

  È possibile modificare questa formula per creare altri tipi di regressione, ma in alcuni casi sarà necessario modificare i valori di output e altre statistiche.

• Il valore test F restituito dalla funzione REGR.LIN è diverso dal valore test F restituito dalla funzione TEST.F. REGR.LIN restituisce la statistica F, mentre TEST.F restituisce la probabilità.

Esempi