Ho discusso regressione lineare su questo blog prima, ma molto spesso una linea retta non è il modo migliore per rappresentare i dati. Per queste situazioni specifiche, possiamo sfruttare alcuni degli strumenti disponibili per eseguire regressione non lineare o adattamento della curva in Excel.
Ricordi il nostro vecchio amico LINEST? Sebbene LINEST sia l’abbreviazione di” stima lineare”, possiamo anche usarlo per dati non lineari con alcune semplici modifiche.
Diciamo che abbiamo alcuni dati di caduta di pressione vs. portata attraverso una valvola dell’acqua, e dopo aver tracciato i dati su un grafico vediamo che i dati sono quadratici.
Anche se questi dati non sono lineari, la funzione LINEST può anche essere utilizzata qui per trovare la curva di adattamento migliore per questi dati. Per un’equazione polinomiale, lo facciamo usando costanti di array.
Un vantaggio nell’usare LINEST per ottenere i coefficienti che definiscono l’equazione polinomiale è che possiamo restituire i coefficienti direttamente alle celle. In questo modo non dobbiamo trasferirli manualmente fuori dal grafico.
Poiché l’equazione è quadratica, o un polinomio del secondo ordine, ci sono tre coefficienti, uno per x al quadrato, uno per x e una costante. Quindi dovremo iniziare creando uno spazio per memorizzare i tre coefficienti per l’equazione.
Utilizzo di LINEST per la regressione non lineare in Excel
La funzione LINEST restituisce un array di coefficienti e statistiche di regressione opzionali. Quindi dovremo inserirlo come formula di matrice selezionando tutte e tre le celle per i coefficienti prima di inserire la formula.
Se le celle contenenti il flusso dei dati di pressione e il nome di “flusso” e “pressione”, la formula sarà simile a questa:
=REGR(pressione, flusso^{1,2},TRUE, FALSE)
Il noto y in questo caso sono le misurazioni di pressione, e il noto x sono le misure di flusso elevato a potenza prima e la seconda. Le parentesi graffe, ” {“e”}”, indicano una costante di matrice in Excel. Fondamentalmente, stiamo dicendo a Excel di creare due array: uno di flusso e un altro di flusso-quadrato, e di adattare la pressione a entrambi gli array insieme.
Infine, gli argomenti TRUE e FALSE dicono alla funzione LINEST di calcolare normalmente l’intercetta y (piuttosto che forzarla a zero) e di non restituire statistiche di regressione aggiuntive, rispettivamente.
Poiché si tratta di una formula array, dobbiamo inserirla digitando Ctrl+Maiusc+Invio.
La funzione restituisce quindi i coefficienti di x2 e x e una costante (perché abbiamo scelto di consentire a LINEST di calcolare l’intercetta y).
I coefficienti sono identici a quelli generati dallo strumento linea di tendenza del grafico, ma ora sono nelle celle, il che li rende molto più facili da usare nei calcoli successivi.
Per qualsiasi equazione polinomiale, LINEST restituisce il coefficiente per l’ordine più alto della variabile indipendente sul lato sinistro, seguito dal successivo più alto e così via, e infine la costante.
Una tecnica simile può essere utilizzata anche per il montaggio della curva esponenziale, logaritmica e della funzione di potenza in Excel.
il Montaggio di una Curva Logaritmica dei Dati
Una funzione logaritmica ha la forma:
Possiamo ancora utilizzare la funzione REGR per trovare il coefficiente m, e costante, b, per questa equazione inserendo ln(x) come argomento per la x_nota:
=LINEST(y_values,ln(x_values),TRUE,FALSE)
Naturalmente, questo metodo si applica a qualsiasi equazione logaritmica, indipendentemente dal numero di base. Quindi potrebbe essere applicato a un’equazione contenente log10 o log2 altrettanto facilmente.
Trovare i coefficienti di una curva esponenziale più adatta
Una funzione esponenziale ha la forma:
È un po ‘più complicato ottenere i coefficienti, a e b, per questa equazione perché prima dobbiamo fare un po’ di algebra per far sì che l’equazione assuma una forma “lineare”. Per prima cosa, prendi il log naturale di entrambi i lati dell’equazione per ottenere quanto segue:
Ora possiamo usare LINEST per ottenere ln(a) e b inserendo ln (y) come argomento per i y_values:
=REGR(ln(y_values),x_values,TRUE,FALSE)
Il secondo valore restituito da questa formula in forma di matrice è ln(a), in modo da ottenere “una”, ci sarebbe semplicemente utilizzare la funzione esponenziale:
Che, in Excel, si traduce in:
=EXP(numero)
il Montaggio di una Funzione di Alimentazione di Dati
Una funzione di potere curva può essere in forma di dati utilizzando la funzione REGR nello stesso modo in cui lo facciamo per una funzione esponenziale. Una funzione di alimentazione ha la forma:
di Nuovo, siamo in grado di “linearizzare” prendendo il logaritmo in base 10 di entrambi i lati dell’equazione per ottenere:
Con l’equazione in questa forma, la funzione REGR per il ritorno b e log10(a) può essere impostato come segue:
=REGR(LOG10(yvalues),LOG10(xvalues),TRUE,FALSE)
Dal momento che la funzione REGR restituisce b e log10(a), dovremo trovare una con la seguente formula:
In Excel, che la formula è:
=10^(numero)
Questo è tutto per ora. Come puoi vedere, ci sono un certo numero di modi per utilizzare la funzione LINEST per il fitting della curva non lineare in Excel.