Come trovare l'equazione di una linea su Excel se avete solo i punti di dati

Excel ha quattro modi per trovare l'equazione di una linea: Funzione LINEST (o LOGEST), linea di tendenza su un grafico, analisi di regressione in Data...Analysis ToolPak, e Solver. Ognuno di questi metodi usa il metodo dei minimi quadrati per trovare i coefficienti dell'equazione.

Suggerisco di guardare la statistica R-squared quando fate questo per assicurarvi che l'equazione stia spiegando la maggior parte della variazione nei vostri dati. L'R-squared varia tra 0 e 1, dove 0 significa che i vostri dati sono completamente casuali e 1 significa che l'equazione spiega completamente tutta la variazione. Per scopi scientifici e ingegneristici, mi piace vedere un R-squared superiore a 0,9.

LINEST

Io normalmente uso LINEST. Se avete n variabili sconosciute, restituisce una serie di risultati su 5 righe per n+1 colonne, con i coefficienti in ordine inverso nella prima riga (cioè la costante all'estrema destra). La quarta riga contiene la statistica R-squared. LINEST è progettato per equazioni lineari, ma funziona abbastanza bene anche con i polinomi.

Se avete valori y nella colonna A e valori x nella colonna B, potete trovare i coefficienti di un polinomio cubico selezionando un intervallo di celle di 5 righe per 4 colonne e inserendo una formula come mostrato sotto. Note the use of the array constant in the curly braces to avoid the need for three columns of x values (one for x, one for x squared, and one for x cubed).

1. =LINEST(A2:A30,B2:B30^{3,2,1},TRUE,TRUE) 

Array-entering means to hold the Control and Shift keys down, then hitting Enter. Excel risponderà aggiungendo le parentesi graffe { } che circondano la tua formula e popolando ogni cella nell'intervallo di celle 5x4 con valori diversi.

Trendlines su un grafico a dispersione

Plottare i tuoi dati su un grafico a dispersione, clicca con il tasto destro su uno dei punti dei dati, poi scegli Trendline dalla finestra di dialogo risultante. Puoi scegliere tra diversi tipi di linee di tendenza, tra cui lineare, polinomiale, esponenziale e logaritmica. Mi piace selezionare le caselle per visualizzare sia l'equazione che la statistica R-squared.

L'equazione sul grafico spesso non mostra abbastanza cifre significative. Puoi aumentare il numero di cifre decimali selezionando l'equazione e poi usando la voce di menu Strumenti grafico...Formato...Formato selezione per visualizzare una barra delle attività con le opzioni di formattazione. Clicca sull'icona del grafico a colonne sulla barra delle attività, poi cambia la categoria del formato del numero in Numero e specifica il numero di cifre decimali.

Analysis ToolPak

Assicurati che l'add-in Analysis ToolPak sia caricato cercando la voce di menu Data...Data Analysis. Se non lo vedi all'estrema destra della barra multifunzione Dati, usa la voce di menu Sviluppo...Add-Ins di Excel per spuntare la sua casella e caricarlo. Non selezionare Analysis ToolPak - VBA, perché è qualcosa di diverso.

Una volta caricato, usa la voce di menu Data...Data Analysis...Regression per selezionare i tuoi dati e dire dove visualizzare i risultati.

Solver Add-In

Il Solver add-in è il metodo più generale per ottenere coefficienti di regressione e può adattarsi a qualsiasi tipo di equazione che puoi immaginare. Non si ottengono valori di R-squared, solo i coefficienti.

Assicurati che l'add-in Solver sia caricato. Dovresti vederlo nella barra multifunzione Dati all'estrema destra. In caso contrario, spuntate la sua casella nella voce di menu Sviluppo...Excel Add-ins.

Per impostare il problema per Solver, dovrete elencare i vostri dati x e y in colonne insieme a una colonna per i valori y calcolati. Questi valori y calcolati dovrebbero fare riferimento a celle contenenti ipotesi per i coefficienti di regressione. Solver will then vary these guesses to minimize the sum of the squares of the difference between actual y and calculated y. I like to use the SUMSQ function to add up these squares in an array-entered formula like:

1. =SUMSQ(ActualYvalues - CalculatedYvalues) 

Select the cell with the SUMSQ formula. Aprite la voce di menu Data...Solver, scegliete Minimize, e specificate le celle con le ipotesi dei coefficienti come celle Changing. Solver ora varierà i coefficienti per trovare l'adattamento ai minimi quadrati.