- LINEST Panoramica della funzione
- Funzione LINEST Sintassi e input
- Cos'è LINEST?
- Come usare LINEST
- Previsione con REGR.LIN (Regressione semplice)
- Previsione con REGR.LIN (regressione lineare multipla)
- LINEST Suggerimenti
- Interessato a più previsioni?
- LINEST funzione in Fogli Google
- LINEST Esempi in VBA
Scarica la cartella di lavoro di esempio
Questo tutorial mostra come usare il Funzione REGR.LIN di Excel in Excel per calcolare le statistiche su una linea di tendenza.
LINEST Panoramica della funzione
La funzione LINEST Calcola le statistiche su una linea di tendenza adattata a punti dati noti utilizzando il metodo dei minimi quadrati.
Per utilizzare la funzione del foglio di lavoro Excel REGR.LIN, selezionare una cella e digitare:
(Notare come appaiono gli input della formula)
Funzione LINEST Sintassi e input
| 1 | =LINEST(ys_nota,x_nota,const,statistiche) |
conosciuto_y's - Un array di valori Y noti.
x_nota - Un array di valori X noti.
cost - FACOLTATIVO. Valore logico che indica se calcolare B (l'intercetta in y = mx + b) utilizzando il metodo dei minimi quadrati (VERO O Omesso) o impostare manualmente B = 0 (FALSO).
statistiche - FACOLTATIVO. Restituisce statistiche aggiuntive (VERO) o restituisce solo m (pendenza) e b (intercetta) (FALSO o Omesso)
Cos'è LINEST?
La funzione REGR.LIN in Excel è una funzione utilizzata per generare statistiche di regressione per un modello di regressione lineare. REGR.LIN è una formula di matrice e può essere utilizzata da sola o con altre funzioni per calcolare statistiche specifiche sul modello.
La regressione lineare è un metodo nelle statistiche utilizzato per prevedere i dati seguendo una linea retta utilizzando dati noti. La regressione viene utilizzata per prevedere valori quali crescita delle vendite, requisiti di inventario o semplici tendenze di mercato.
LINEST è come FORECAST in quanto ottiene un risultato simile, ma con molte più informazioni sul modello di regressione e l'opzione per adattare più di una variabile indipendente.
Supponiamo di avere una tabella di dati con X e sì valori dove X è la variabile indipendente e sì è la variabile dipendente:
Voglio sapere qual è l'equazione di regressione dei dati di cui sopra. Utilizzo di REGR.LIN:
| 1 | =LINEST(LA3:SI7,DO3:DO7,VERO,FALSO) |
Il valore dell'intercetta y qui è equivalente a 0, in notazione scientifica.
L'equazione della retta è y= 2x + 0 . Nota che LINEST restituisce entrambi la pendenza e l'intercetta della linea. Per restituire entrambi i valori, la formula deve essere immessa come formula di matrice. Più avanti sulle formule di matrice.
Come usare LINEST
La funzione REGR.LIN accetta quattro argomenti:
| 1 | =LINEST(y_nota, x_nota, const, statistiche) |
In cui si,
| Discussione | Descrizione |
| conosciuto_y's e x_nota | È il X e sì dati nella tua tabella dati |
| cost | VERO/FALSO opzione per stabilire se l'intercetta y deve essere forzata a 0 o calcolata normalmente |
| statistiche | Opzione VERO/FALSO se devono essere restituite ulteriori statistiche di regressione |
Usando il nostro primo esempio, la funzione è scritta come:
| 1 | =LINEST(LA3:SI7,DO3:DO7,VERO,FALSO) |
Quando il statistiche l'opzione è impostata su TRUE, l'organizzazione delle statistiche di regressione è la seguente:
Forse ti starai chiedendo cosa significa ogni variabile.
| statistica | Descrizione |
| mn | Coefficienti di pendenza per x variabili |
| B | y-intercetta |
| sin | Errore standard per ogni coefficiente di pendenza |
| siB | Errore standard per l'intercetta y |
| R2 | Coefficiente di determinazione |
| sisì | Errore standard per il sì stima |
| F | La statistica F (per determinare se la relazione delle variabili avviene per caso) |
| DF | Gradi di libertà |
| ssreg | Somma dei quadrati di regressione |
| ssrisiedere | Somma residua dei quadrati |
Le principali statistiche da comprendere sono i coefficienti di pendenza, l'intercetta y e il coefficiente di determinazione o r2 valore del modello.
Usando l'esempio sopra e selezionando TRUE per il statistiche parametro:
Le celle evidenziate mostrano la pendenza = 2, intercetta = 0 e r2 = 1.
il r2 il valore è un indicatore della forza della correlazione del modello. Può essere considerato un indicatore di adattamento. Un basso r2 valore significherebbe una scarsa correlazione tra le variabili dipendenti e indipendenti, e il contrario è vero per r high alto2 valori, con r2 = 1 è una misura perfetta.
Nelle versioni successive a gennaio 2022 di Excel in Microsoft 365 (in precedenza Office 365), le matrici dinamiche hanno modificato il modo in cui vengono valutate le formule di matrice. Non è più necessario utilizzare CTRL + MAIUSC + INVIO o evidenziare l'area di celle che occuperà l'array. Basta inserire la formula e fare clic su invio e le celle risultanti "verranno fuori" nell'array.
Per il resto di questo articolo faremo riferimento all'utilizzo di REGR.LIN rispetto agli array dinamici in Microsoft 365 Excel.
Previsione con REGR.LIN (Regressione semplice)
La combinazione delle funzioni REGR.LIN e SOMMA può essere utilizzata per prevedere il valore di una variabile dipendente sì, dato noto X e sì dati. Di seguito è riportato un esempio che mostra cosa sì il valore sarà quando x=14.
| 1 | =SOMMA(LINEST(C3:C7,B3:B7)*{14,1}) |
Il modello si presenta nella forma y = mx + b . Questo è lo stesso di y = a+ bx, solo un modo diverso di rappresentare l'equazione. Un consiglio da tenere a mente per le equazioni lineari è la variabile accanto a X è sempre la pendenza e la variabile che segue un segno più o meno è sempre l'intercetta, indipendentemente dalle lettere utilizzate nell'equazione.
Utilizzando la formula: =SUM(LINEST(C3:C7,B3:B7)*{14,1}) restituisce il risultato di 28. Poiché si tratta di un singolo risultato, non è necessario inserirlo come matrice.
La parte finale della formula precedente *{14,1} specifica la variabile indipendente da utilizzare per prevedere la variabile dipendente, in questo caso 14.
Possiamo verificarlo inserendo x= 14 nell'equazione della retta, y = 2x + 0.
Previsione con REGR.LIN (regressione lineare multipla)
La seguente tabella di dati proviene dalla pagina REGR.LIN. del sito Web del supporto Microsoft.
In alcuni casi, c'è più di una variabile indipendente che dovrebbe essere considerata quando si crea un modello di regressione lineare. Questa è chiamata regressione lineare multipla (cioè più variabili indipendenti). Se voglio stimare il costo di un edificio per uffici, cose come la superficie, il numero di ingressi dell'edificio, l'età dell'edificio e il numero di uffici farebbero tutti parte dell'equazione. Vediamo un esempio.
Digitando la formula REGR.LIN nella cella G29 ed eseguendola, otteniamo:
| 1 | =LINEST(E3:E13,A3:D13,VERO,VERO) |
Il modello si presenta nella forma:
Ricorda che l'array dei risultati di REGR.LIN è in ordine inverso rispetto all'equazione. Nell'esempio sopra, 52.317,8 è la nostra intercetta, b, e 27,6 è il nostro m1 o il valore della pendenza per la variabile Spazio al suolo, X1.
Utilizzando la funzione REGR.LIN con i dati forniti, il nostro modello di regressione è:
Con un r2 valore di 0,997, che indica un modello forte o altamente correlato. Utilizzando il modello, è ora possibile prevedere quale sarà il valore stimato di un edificio per uffici in base a qualsiasi combinazione delle variabili indipendenti di cui sopra.
LINEST Suggerimenti
- Assicurati di disporre della versione più aggiornata di Microsoft 365 per utilizzare REGR.LIN con gli array dinamici. Potrebbe essere necessario abilitare il canale corrente di Office Insider (anteprima) per utilizzare le funzioni dell'array dinamico. Nella pagina dell'account:
- Se utilizzi una versione non Microsoft 365, dovrai utilizzare il metodo legacy CTRL + MAIUSC + INVIO (CSE) per valutare le formule di matrice.
- Se si utilizza il metodo legacy, il numero di colonne da evidenziare quando si immette una funzione di matrice REGR.LIN è sempre il numero di X variabili nei dati più 1. Il numero di righe da selezionare per l'array è 5.
- Se condividerai la versione di Excel abilitata per l'array dinamico con qualcuno che utilizza una versione non Microsoft 365, usa il metodo CSE legacy per evitare problemi di compatibilità.
Interessato a più previsioni?
Vedi i nostri altri articoli sulle previsioni con le funzioni di livellamento esponenziale, TREND, GROWTH e LOGEST.
LINEST funzione in Fogli Google
La funzione REGR.LIN funziona esattamente allo stesso modo in Fogli Google come in Excel.
LINEST Esempi in VBA
Puoi anche usare la funzione REGR.LIN in VBA. Tipo:application.worksheetfunction.linest(known_ys,known_xs,const,stats)
Esecuzione della seguente istruzione VBA
| 1 | Range("D2") = Application.WorksheetFunction.LinEst(Range("A2:A8"), Range("B2:B8")) |
produrrà i seguenti risultati
Per gli argomenti della funzione (y_nota, ecc.), puoi inserirli direttamente nella funzione o definire le variabili da utilizzare invece.
Torna all'elenco di tutte le funzioni in Excel
