Curve di adattamento ai minimi quadrati non lineari con il solutore di microsoft excel
AbstractDescriviamo una procedura intuitiva e rapida per l’analisi dei dati sperimentali mediante fitting ai minimi quadrati non lineare (NLSF) nel più diffuso programma di foglio elettronico. I dati sperimentali in forma x/y e i dati calcolati da un’equazione di regressione vengono inseriti e tracciati in un foglio di lavoro di Microsoft Excel, e la somma dei residui al quadrato viene calcolata e minimizzata utilizzando l’add-in Solver per ottenere l’insieme dei valori dei parametri che meglio descrivono i dati sperimentali. La confidenza dei valori di best-fit viene quindi visualizzata e valutata in un modo generalmente applicabile e facilmente comprensibile. Ogni utente che abbia familiarità con le funzioni più elementari di Excel sarà in grado di implementare questo protocollo, senza precedenti esperienze di data fitting o di programmazione e senza costi aggiuntivi per software specialistici. L’applicazione di questo strumento è esemplificata utilizzando la nota equazione di Michaelis-Menten che caratterizza una semplice cinetica enzimatica. Sono necessarie solo piccole modifiche per adattare il protocollo a qualsiasi altro tipo di set di dati o equazione di regressione. L’intero protocollo richiede ∼1 h.
Come si calcolano i minimi quadrati in Excel?
Per utilizzare il metodo dei minimi quadrati per adattare una retta di regressione in Excel, è possibile utilizzare la funzione =LINEST().
I minimi quadrati sono la stessa cosa della regressione lineare?
La regressione lineare ai minimi quadrati è di gran lunga il metodo di modellazione più utilizzato. È quello che la maggior parte delle persone intende quando dice di aver usato “regressione”, “regressione lineare” o “minimi quadrati” per adattare un modello ai propri dati.
Adattamento della curva in excel
La regressione lineare è il tipo più comune di analisi di regressione ed è uno strumento incredibilmente potente. Nei progetti più piccoli o nei casi d’uso orientati al business, un semplice modello di regressione lineare con Excel potrebbe essere lo strumento perfetto per completare rapidamente l’analisi.
L’analisi di regressione consente di esaminare la relazione tra due o più variabili. Utilizziamo y per rappresentare la variabile dipendente e X per rappresentare la variabile indipendente. La variabile dipendente X è quella fissata in natura o inserita nel modello, mentre la variabile y è quella che si prevede con il modello.
Con un semplice modello di regressione lineare è anche comune utilizzare il metodo dei minimi quadrati ordinari (OLS) per l’adattamento del modello. Nel metodo OLS, l’accuratezza del modello è misurata dalla somma dei quadrati dei residui di ciascun punto previsto. Il residuo è la distanza ortogonale tra il punto del set di dati e la retta applicata.
Oggi, il nostro esempio illustrerà la semplice relazione tra il numero di utenti di un sistema e il nostro Costo del venduto (COGS). Attraverso questa analisi, non solo saremo in grado di vedere la forte correlazione tra le due variabili, ma anche di utilizzare i nostri coefficienti per prevedere il COGS per un determinato numero di utenti.
Excel linest
La regressione lineare ai minimi quadrati in Excel è facile. È quello che fanno le funzioni Linest e Trend. Trovano cioè i coefficienti di una retta (o di una forma di dimensione superiore) in modo che la somma dei quadrati delle distanze di ciascun punto di dati dalla retta sia minima. Tuttavia, se vogliamo utilizzare dati ponderati (dare ai valori di alcuni punti più importanza di altri), non ci sono funzioni integrate per svolgere questo compito. Questo post esamina varie opzioni, tra cui l’uso di Linest con input modificato, le funzioni definite dall’utente (UDFS) di VBA e le UDF che utilizzano le librerie Alglib e Python Scipy. Tutto il codice utilizzato è libero e open source e può essere scaricato dal sito:
I primi due risultati utilizzano la funzione Linest senza ponderazione. Nella prima formula, il valore opzionale Const viene omesso, quindi Const viene impostato su True e viene calcolato il valore costante dell’equazione (A). In questo caso, l’intervallo X richiede solo i valori X1 e X2 (colonne B e C). Nella seconda formula il valore Const è impostato su False, quindi è necessaria un’ulteriore colonna con X = 1.
Adattamento esponenziale in excel
La funzione LINEST calcola le statistiche di una retta utilizzando il metodo dei “minimi quadrati” per calcolare la retta che meglio si adatta ai dati e restituisce un array che descrive la retta. È inoltre possibile combinare LINEST con altre funzioni per calcolare le statistiche di altri tipi di modelli che sono lineari nei parametri sconosciuti, tra cui polinomiali, logaritmici, esponenziali e serie di potenze. Poiché questa funzione restituisce una matrice di valori, deve essere inserita come formula di matrice. Le istruzioni seguono gli esempi di questo articolo.
se ci sono più intervalli di valori x, dove i valori y dipendenti sono una funzione dei valori x indipendenti. I valori m sono i coefficienti corrispondenti a ciascun valore x e b è un valore costante. Si noti che y, x e m possono essere vettori. La matrice restituita dalla funzione LINEST è {mn,mn-1,…,m1,b}. LINEST può anche restituire ulteriori statistiche di regressione.
Il coefficiente di determinazione. Mette a confronto i valori y stimati e quelli effettivi e varia da 0 a 1. Se è pari a 1, nel campione esiste una correlazione perfetta: non c’è differenza tra il valore y stimato e quello effettivo. All’estremo opposto, se il coefficiente di determinazione è pari a 0, l’equazione di regressione non è utile per prevedere un valore y. Per informazioni su come viene calcolato l’r2, vedere “Osservazioni”, più avanti in questo argomento.