REGR.LIN
La funzione REGR.LIN restituisce una matrice della statistica della retta che meglio si adatta ai dati specificati, utilizzando il metodo dei quadrati minori.
REGR.LIN(valori_y_noti; valori_x_noti; intercetta_y_non_zero; più_stat)
valori_y_noti: un insieme che contiene i valori y noti. valori_y_noti deve contenere valori numerici o valori data/ora. Se vi è solo un insieme di valori x noti, valori_y_noti può presentare qualsiasi misura. Se vi è più di un insieme di valori x noti, valori_y_noti può essere una colonna o una riga contenente i valori, ma non entrambe le cose.
valori_x_noti: insieme facoltativo che contiene i valori x noti. valori_x_noti deve contenere valori numerici o valori data/ora. Se viene omesso, si presuppone che sia un insieme della stessa dimensione come valori_y_noti che inizia con 1, ad esempio 1, 2, 3 nel caso in cui esistono tre valori_y_noti. Se vi è solo una serie di valori_x noti, se specificata valori_x_noti deve essere della stessa misura di valori_y_noti. Se vi è più di una serie di valori x noti, ogni riga/colonna di valori_x_noti viene considerata come una serie e la misura di ciascuna riga/colonna deve essere uguale alla misura della riga/colonna di valori_y_noti.
intercetta_y_non_zero: un valore modale facoltativo che specifica come calcolare l'intersezione y (costante b).
normale (1, VERO oppure omesso): valore dell'intersezione y (costante b) va calcolato normalmente.
forza valore 0 (0, FALSO): il valore dell'intersezione y (costante b) va forzato a 0.
più_stat: valore modale facoltativo che specifica se vanno restituite informazioni statistiche aggiuntive.
nessuna statistica aggiuntiva (0, FALSO oppure omesso): non restituisce statistiche di regressione aggiuntive nella matrice restituita.
statistiche aggiuntive (1, VERO): calcola statistiche di regressione aggiuntive nella matrice restituita.
Note
I valori calcolati dalla funzione sono inseriti in una matrice. Per leggere tali valori dalla matrice è possibile utilizzare la funzione INDICE. Puoi utilizzare la funzione REGR.LIN all'interno della funzione INDICE: =INDICE(REGR.LIN(valori_y_noti; valori_x_noti; intercetta_y; più_stat); y; x) dove y e x sono l'indice di colonna e di riga del valore desiderato.
Se non vengono calcolate statistiche aggiuntive (più_stat è FALSO), la matrice restituita comprende una riga. Il numero di colonne è pari al numero di serie di valori_x_noti più 1. Contiene le inclinazioni della linea (un valore per ciascuna riga/colonna di valori x) in ordine inverso, con il primo valore che si riferisce all'ultima riga/colonna dei valori x, quindi il valore dell'intersezione b.
Se vengono calcolate statistiche aggiuntive (più_stat è VERO), la matrice comprende cinque righe. Consulta le informazioni aggiuntive su questa matrice subito accanto agli esempi.
Esempi |
|---|
Data la seguente tabella di valori_x_noti (celle A2:A6) e valori_y_noti (celle B2:B6): |
A | B | |
|---|---|---|
1 | X | Y |
2 | 0 | -1 |
3 | 8 | 10 |
4 | 9 | 12 |
5 | 4 | 5 |
6 | 1 | 3 |
=INDICE(REGR.LIN(A2:A6; B2:B6; 1; 0); 1) restituisce circa 0,752707581227437, dato un valore (1) normale per intercetta_y_non_zero. Questa è la pendenza della retta perché abbiamo specificato che volevamo il primo valore dalla matrice restituita da INDICE e abbiamo solo indicato una serie di valori_x_noti. =INDICE(REGR.LIN(A2:A6; B2:B6; 1; 0); 2) restituisce circa 0,0342960288808646, cioè il punto di intersecazione della retta. L'intersecazione è stata restituita perché abbiamo specificato che volevamo il secondo valore della matrice restituita da INDICE, che sarebbe il secondo valore, perché abbiamo solo indicato una serie di valori_x_noti. |
Contenuto della matrice di statistiche aggiuntive
REGR.LIN può comprendere informazioni statistiche aggiuntive nella matrice restituita dalla funzione. Per l'analisi che segue, supponi di avere cinque serie di valori x noti oltre ai valori y noti. Supponi inoltre che i valori_x_noti si trovino in cinque righe o in cinque colonne della tabella. Su questa base, la matrice restituita da REGR.LIN dovrebbe contenere i seguenti valori.
1 | 2 | 3 | 4 | 5 | 6 | |
|---|---|---|---|---|---|---|
1 | S5 | S4 | S3 | S2 | S1 | B |
2 | SE5 | SE4 | SE3 | SE2 | SE1 | SEb |
3 | C | SEy | ||||
4 | F | DF | ||||
5 | R1 | R2 |
Riga 1, colonna 1 contiene S5 (la pendenza per la quinta serie di valori_x_noti) continuando con la colonna 5, che dovrebbe contenere S1 (la pendenza per la prima serie di valori_x_noti). Nota che la pendenza correlata a ciascuna serie di valori_x_noti è stata restituita in ordine inverso.
L'ultima cella nella riga 1 contiene b, l'intersezione y per i valori x noti. Nel nostro esempio, questo dovrebbe essere riga 1 colonna 6.
Riga 2, colonna 1 contiene SE5 (l'errore standard per il coefficiente associato con la quinta serie di valori_x_noti) continuando con la colonna 5, che dovrebbe contenere SE1 (il coefficiente di errore standard per la prima serie di valori_x_noti). Questi valori vengono restituiti in ordine inverso, vale a dire che se vi sono cinque serie di valori x noti, il valore della quinta serie viene restituito per primo nella matrice. Si tratta dello stesso modo in cui vengono restituiti i valori delle pendenze.
L'ultima cella nella riga 2 contiene SEb, l'errore standard associato con il valore intercetta_y (b). Nel nostro esempio, questo dovrebbe essere riga 2 colonna 6.
Riga 3, colonna 1 contiene C, il coefficiente di determinazione. Questa statistica mette a confronto valori y stimati ed effettivi. Se è 1, non vi è differenza tra il valore y stimato e il valore y effettivo. Ciò è noto come correlazione perfetta. Se il coefficiente di determinazione è 0, non vi è correlazione e l'equazione di regressione specificata non è utile a predire un valore y.
Riga 3, colonna 2 contiene SEy, l'errore standard associato con il valore y stimato.
Riga 4, colonna 1 contiene F, il valore F osservato. È possibile usare il valore F osservato per determinare se il rapporto osservato tra le variabili dipendenti e quelle indipendenti è casuale.
Riga 4, colonna 2 contiene DF, i gradi di libertà. Permettono di determinare il livello di confidenza.
Riga 5, colonna 1 contiene R1, la somma della regressione dei quadrati.
Riga 5, colonna 2 contiene R2, la somma residua dei quadrati.
Di seguito alcune cose importanti da tenere a mente riguardo la matrice di statistiche aggiuntive:
Non è importante se i valori x noti e i valori y noti si trovano in righe o in colonne. In entrambi i casi, la matrice restituita viene ordinata su righe come illustrato nella tabella.
Nell'esempio si presume la presenza di cinque serie di valori x noti. Se ve ne fossero più o meno di cinque, il numero di colonne della matrice restituita cambierebbe di conseguenza, rimanendo sempre uguale al numero di serie di valori x noti più 1, ma il numero di righe rimarrebbe costante.
Se negli argomenti di REGR.LIN non vengono specificate statistiche aggiuntive, la matrice restituita è uguale solo alla prima riga.