Statistiche Excel avanzate - Funzione LINEST

Descrizione

La funzione LINEST calcola le statistiche per una linea utilizzando il metodo dei "minimi quadrati" per calcolare una linea retta che si adatta meglio ai dati e quindi restituisce una matrice che descrive la linea.

È inoltre possibile combinare LINEST con altre funzioni per calcolare le statistiche per altri tipi di modelli che sono lineari nei parametri sconosciuti, inclusi polinomio, logaritmico, esponenziale e serie di potenze.

Poiché questa funzione restituisce una matrice di valori, deve essere inserita come formula di matrice.

Sintassi

LINEST (known_y's, [known_x's], [const], [stats])

argomenti

Discussione Descrizione Obbligatorio / Facoltativo
conosciuto_y

L'insieme di valori y che già conosci nella relazione y = mx + b.

Se l'intervallo di known_y è in una singola colonna, ogni colonna di known_x viene interpretata come una variabile separata.

Se l'intervallo di known_y è contenuto in una singola riga, ogni riga di known_x viene interpretata come una variabile separata.

 necessario
note_x's

Un insieme di valori x che potresti già conoscere nella relazione y = mx + b.

L'intervallo di known_x può includere uno o più insiemi di variabili.

Se viene utilizzata una sola variabile, known_y's e known_x possono essere intervalli di qualsiasi forma, purché abbiano dimensioni uguali.

Se viene utilizzata più di una variabile, known_y's deve essere un vettore (ovvero un intervallo con un'altezza di una riga o una larghezza di una colonna).

Se noto_x viene omesso, si presume che sia l'array {1,2,3, ...} che ha la stessa dimensione di known_y.

 Opzionale
const

Un valore logico che specifica se forzare la costante b a essere uguale a 0.

Se const è TRUE o omesso, b viene calcolato normalmente.

Se const è FALSE, b è impostato uguale a 0 e i valori m vengono regolati per adattarsi a y = mx.

 Opzionale
statistiche

Un valore logico che specifica se restituire statistiche di regressione aggiuntive.

Se stats è TRUE, LINEST restituisce le statistiche di regressione aggiuntive. Di conseguenza, l'array restituito è {mn, mn-1, ..., m1, b; sen, sen-1, ..., se1, seb; r2, sey; F, df; ssreg, ssresid}.

Se stats è FALSE o è omesso, LINEST restituisce solo gli mcoefficienti e la costante b. Le statistiche di regressione aggiuntive sono fornite nella tabella seguente.

 Opzionale

Statistiche di regressione aggiuntive

Suor n Statistica e descrizione
1

se1,se2,...,sen

I valori di errore standard per i coefficienti m1, m2, ..., mn.
2

seb

Il valore di errore standard per la costante b (seb = # N / A quando const è FALSE).
3

r2

Il coefficiente di determinazione. Confronta i valori y stimati con quelli effettivi e il valore varia da 0 a 1. Se è 1, c'è una correlazione perfetta nel campione: non c'è differenza tra il valore y stimato e il valore y effettivo. All'altro estremo, se il coefficiente di determinazione è 0, l'equazione di regressione non è utile per prevedere un valore y. Per informazioni su come viene calcolato r2, vedere le note di seguito.
4

sey

L'errore standard per la stima y.
5

F

La statistica F o il valore F osservato. Utilizzare la statistica F per determinare se la relazione osservata tra le variabili dipendenti e indipendenti si verifica per caso.
6

df

I gradi di libertà. Usa i gradi di libertà per trovare i valori F-critici in una tabella statistica. Confronta i valori che trovi nella tabella con la statistica F restituita da LINEST per determinare un livello di confidenza per il modello. Per informazioni su come viene calcolato df, vedere le note di seguito.
7

ssreg

La somma di regressione dei quadrati.
8

ssreg

La somma residua dei quadrati. Per informazioni su come vengono calcolati ssreg e ssresid, vedere le note di seguito.

Appunti

  • L'equazione per la linea è -

    y = mx + b

    o

    y = m1x1 + m2x2 + ... + b

  • Se ci sono più intervalli di valori x, dove i valori y dipendenti sono una funzione dei valori x indipendenti, allora -

    • I valori m sono coefficienti corrispondenti a ciascun valore x e b è un valore costante.

    • Nota che y, x e m possono essere vettori.

  • L'array restituito dalla funzione LINEST è {mn, mn-1… m1, b}.

  • LINEST può anche restituire statistiche di regressione aggiuntive

  • Puoi descrivere qualsiasi linea retta con la pendenza e l'intercetta y -

    • Slope(m) -

      Per trovare la pendenza di una linea, spesso scritta come m, prendi due punti sulla linea, (x1, y1) e (x2, y2). La pendenza è uguale a

      (–2 - y1) / (- 2 - x1).

    • Y-intercept(b) -

      L'intercetta y di una linea, spesso scritta come b, è il valore di y nel punto in cui la linea incrocia l'asse y.

  • L'equazione di una linea retta è y = mx + b. Una volta che conosci i valori di me b, puoi calcolare qualsiasi punto sulla linea inserendo il valore y o x in quell'equazione. È inoltre possibile utilizzare la funzione TREND.

  • Quando si dispone di una sola variabile x indipendente, è possibile ottenere direttamente i valori di pendenza e intercetta y utilizzando le seguenti formule:

    • Slope -

      = INDEX (LINEST (known_y's, known_x's), 1)

    • Y-intercept -

      = INDICE (LINEST (known_y's, known_x's), 2)

  • La precisione della linea calcolata dalla funzione LINEST dipende dal grado di dispersione nei dati. Più i dati sono lineari, più accurato è il modello LINEST.

  • LINEST utilizza il metodo dei minimi quadrati per determinare il miglior adattamento per i dati. Quando si dispone di una sola variabile x indipendente, i calcoli per me b si basano sulle seguenti formule:

    $$ m = \ frac {\ sum \ left (x- \ bar {x} \ right) \ left (y- \ bar {y} \ right)} {\ sum \ left (x- \ bar {x} \ a destra) ^ 2} $$

    Dove x e y sono medie campionarie. cioè

    x = MEDIA (x note)

    y = AVERAGE (known_y's)

  • Le funzioni di adattamento di linee e curve LINEST e LOGEST possono calcolare la migliore linea retta o curva esponenziale che si adatta ai dati. Tuttavia, devi decidere quale dei due risultati si adatta meglio ai tuoi dati. Puoi calcolare TREND (known_y's, known_x's) per una linea retta o GROWTH (known_y's, known_x's) per una curva esponenziale. Queste funzioni, senza omettere l'argomento di known_x, restituiscono un array di valori y previsti lungo quella linea o curva nei punti dati effettivi. È quindi possibile confrontare i valori previsti con i valori effettivi. Puoi tracciarli entrambi per un confronto visivo.

  • Nell'analisi di regressione, Excel calcola per ogni punto la differenza al quadrato tra il valore y stimato per quel punto e il suo valore y effettivo. La somma di queste differenze al quadrato è chiamata somma residua dei quadrati, ssresid. Excel calcola quindi la somma totale dei quadrati, sstotal. Quando l'argomento const = TRUE o viene omesso, la somma totale dei quadrati è la somma delle differenze al quadrato tra i valori y effettivi e la media dei valori y.

  • Quando l'argomento const = FALSE, la somma totale dei quadrati è la somma dei quadrati dei valori y effettivi (senza sottrarre il valore y medio da ogni singolo valore y). Quindi la somma di regressione dei quadrati, ssreg, può essere trovata da: ssreg = sstotal - ssresid. Più piccola è la somma residua dei quadrati, rispetto alla somma totale dei quadrati, maggiore è il valore del coefficiente di determinazione, r2, che è un indicatore di quanto bene l'equazione risultante dall'analisi di regressione spiega la relazione tra le variabili. Il valore di r2 è uguale a ssreg / sstotal.

  • In alcuni casi, una o più delle X colonne (presumendo che Y e X siano nelle colonne) potrebbero non avere un valore predittivo aggiuntivo in presenza delle altre X colonne. ad esempio, l'eliminazione di una o più colonne X potrebbe portare a valori Y previsti ugualmente accurati. In tal caso, queste colonne X ridondanti dovrebbero 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 LINEST verifica la collinearità e rimuove eventuali colonne X ridondanti dal modello di regressione quando le identifica. Le colonne X rimosse possono essere riconosciute nell'output LINEST come aventi coefficienti 0 oltre ai valori 0 se. Se una o più colonne vengono rimosse come ridondanti, df ne risente perché df dipende dal numero di X colonne effettivamente utilizzate a scopo predittivo.

  • Se df viene modificato perché vengono rimosse le colonne X ridondanti, vengono influenzati anche i valori di 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 un membro di un particolare gruppo. Se const = TRUE o viene omesso, la funzione LINEST inserisce effettivamente una colonna X aggiuntiva di tutti i valori 1 per modellare l'intercetta

  • Il valore di df viene calcolato come segue, quando sono presenti k colonne di x_conosciute e nessuna colonna X viene rimossa dal modello a causa della collinearità -

    • Se const = TRUE o viene omesso, df = n - k - 1

    • Se const = FALSE, df = n - k

    In entrambi i casi, ogni colonna X rimossa a causa della collinearità aumenta il valore di df di 1.

  • Quando si immette una costante di matrice (come la x_nota) come argomento, utilizzare le virgole per separare i valori contenuti nella stessa riga e il punto e virgola per separare le righe. I caratteri separatori possono essere diversi a seconda delle impostazioni regionali.

  • Si noti che i valori y previsti dall'equazione di regressione potrebbero non essere validi se sono al di fuori dell'intervallo dei valori y utilizzato per determinare l'equazione.

  • L'algoritmo sottostante utilizzato nella funzione LINEST è diverso dall'algoritmo sottostante utilizzato nelle funzioni SLOPE e INTERCEPT. La differenza tra questi algoritmi può portare a risultati diversi quando i dati sono indeterminati e collineari.

  • Oltre a utilizzare LOGEST per calcolare le statistiche per altri tipi di regressione, è possibile utilizzare LINEST per calcolare un intervallo di altri tipi di regressione inserendo le funzioni delle variabili xey come serie xey per LINEST. Ad esempio, la seguente formula -

    = LINEA (valori y, valori x ^ COLONNA ($ A: $ C))

    Funziona quando hai una singola colonna di valori y e una singola colonna di valori x per calcolare l'approssimazione cubica (polinomio di ordine 3) del -

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

    È possibile modificare questa formula per calcolare altri tipi di regressione, ma in alcuni casi richiede la regolazione dei valori di output e altre statistiche.

  • Il valore F-test restituito dalla funzione LINEST è diverso dal valore F-test restituito dalla funzione FTEST. LINEA restituisce la statistica F, mentre FTEST restituisce la probabilità.

  • Se l'array di known_x's non ha la stessa lunghezza dell'array di known_y, LINEST restituisce #REF! valore di errore.

  • Se uno qualsiasi dei valori negli array known_x o known_y forniti non è numerico (questo può includere rappresentazioni testuali di numeri, poiché la funzione LINEST non li riconosce come numeri), LINEST restituisce #VALUE! valore di errore.

  • Se uno degli argomenti const o stats non può essere valutato come TRUE o FALSE, LINEST restituisce #VALUE! valore di errore.

Applicabilità

Excel 2007, Excel 2010, Excel 2013, Excel 2016

Esempio