Come eseguire una curva di calibrazione lineare in Excel

Excel dispone di funzionalità integrate che è possibile utilizzare per visualizzare i dati di calibrazione e calcolare una linea di adattamento ottimale. Questo può essere utile quando si scrive un rapporto di un laboratorio di chimica o si programma un fattore di correzione in un pezzo di equipaggiamento.

In questo articolo, vedremo come utilizzare Excel per creare un grafico, tracciare una curva di calibrazione lineare, visualizzare la formula della curva di calibrazione e quindi impostare semplici formule con le funzioni SLOPE e INTERCEPT per utilizzare l'equazione di calibrazione in Excel.

Che cos'è una curva di calibrazione e come Excel è utile quando si crea uno?

Per eseguire una calibrazione, si confrontano le letture di un dispositivo (come la temperatura visualizzata da un termometro) a valori noti denominati standard (come i punti di congelamento e di ebollizione dell'acqua). Ciò consente di creare una serie di coppie di dati che verranno quindi utilizzate per sviluppare una curva di calibrazione.

Una calibrazione a due punti di un termometro che utilizza i punti di congelamento e di ebollizione dell'acqua avrebbe due coppie di dati: una da quando il termometro è posto in acqua ghiacciata (32 ° F o 0 ° C) e uno in acqua bollente (212 ° F o 100 ° C). Quando si tracciano queste due coppie di dati come punti e si disegna una linea tra di loro (la curva di calibrazione), quindi assumendo che la risposta del termometro sia lineare, è possibile selezionare qualsiasi punto sulla linea corrispondente al valore visualizzato dal termometro e potrebbe trovare la corrispondente "vera" temperatura.

Quindi, la linea essenzialmente sta riempiendo le informazioni tra i due punti noti per voi in modo da poter essere ragionevolmente certi quando si stima la temperatura effettiva quando il termometro sta leggendo 57,2 gradi, ma quando non si è mai misurato uno "standard" che corrisponde a quella lettura.

Excel dispone di funzionalità che consentono di tracciare graficamente le coppie di dati in un grafico, aggiungere una linea di tendenza (curva di calibrazione) e visualizzare l'equazione della curva di calibrazione sul grafico. Ciò è utile per una visualizzazione visiva, ma è anche possibile calcolare la formula della linea utilizzando le funzioni SLOPE e INTERCEPT di Excel. Quando inserisci questi valori in formule semplici, sarai in grado di calcolare automaticamente il valore "vero" in base a qualsiasi misurazione.

Diamo un'occhiata ad un esempio

Per questo esempio, svilupperemo una curva di calibrazione da una serie di dieci coppie di dati, ciascuna costituita da un valore X e un valore Y. I valori X saranno i nostri "standard" e potrebbero rappresentare qualsiasi cosa, dalla concentrazione di una soluzione chimica che stiamo misurando utilizzando uno strumento scientifico alla variabile di input di un programma che controlla un lanciamissili di marmo.

I valori Y saranno le "risposte" e rappresenterebbero la lettura dello strumento fornito durante la misurazione di ciascuna soluzione chimica o la distanza misurata di quanto lontano dal lanciatore il marmo è atterrato utilizzando ciascun valore di input.

Dopo aver rappresentato graficamente la curva di calibrazione, utilizzeremo le funzioni SLOPE e INTERCEPT per calcolare la formula della linea di calibrazione e determinare la concentrazione di una soluzione chimica "sconosciuta" in base alla lettura dello strumento o decidere quale input dovremmo dare al programma in modo che il marmo atterra a una certa distanza dal lanciatore.

Il nostro semplice foglio di calcolo di esempio è composto da due colonne: X-Value e Y-Value.

Iniziamo selezionando i dati da tracciare nel grafico.

Innanzitutto, seleziona le celle della colonna "Valore X".

Ora premere il tasto Ctrl e quindi fare clic sulle celle della colonna Valore Y.

Vai alla scheda "Inserisci".

Vai al menu "Grafici" e seleziona la prima opzione nel menu a discesa "Scatter".

Apparirà un grafico contenente i punti dati delle due colonne.

Seleziona la serie facendo clic su uno dei punti blu. Una volta selezionato, Excel delinea i punti che saranno delineati.

Fare clic con il tasto destro su uno dei punti e selezionare l'opzione "Aggiungi linea di tendenza".

Una linea dritta apparirà sul grafico.

Sul lato destro dello schermo comparirà il menu "Format Trendline". Seleziona le caselle accanto a "Visualizza equazione su grafico" e "Visualizza valore R quadrato su grafico". Il valore R al quadrato è una statistica che indica quanto la linea si adatta ai dati. Il valore R quadrato migliore è 1.000, il che significa che ogni punto dati tocca la linea. Man mano che le differenze tra i punti dati e la linea crescono, il valore r-quadro cala, con 0.000 che è il valore più basso possibile.

L'equazione e la statistica del quadrato R della linea di tendenza appariranno sul grafico. Si noti che la correlazione dei dati è molto buona nel nostro esempio, con un valore R al quadrato di 0.988.

L'equazione è nella forma "Y = Mx + B", dove M è la pendenza e B è l'intercetta sull'asse y della retta.

Ora che la calibrazione è completa, lavoriamo sulla personalizzazione del grafico modificando il titolo e aggiungendo titoli degli assi.

Per modificare il titolo del grafico, fare clic su di esso per selezionare il testo.

Ora digita un nuovo titolo che descrive il grafico.

Per aggiungere titoli all'asse xe all'asse y, per prima cosa vai a Strumenti grafico & gt; Design.

Fai clic sul menu a discesa "Aggiungi un elemento del grafico".

Ora vai a Axis Titles & gt; Orizzontale primario.

Apparirà il titolo di un asse.

Per rinominare il titolo dell'asse, selezionare innanzitutto il testo, quindi digitare un nuovo titolo.

Adesso vai su Axis Titles & gt; Verticale principale.

Apparirà il titolo di un asse.

Rinominare questo titolo selezionando il testo e digitando un nuovo titolo.

Il tuo grafico è ora completo.

Calcoliamo ora l'equazione di linea e la statistica del quadrato R usando le funzioni incorporate di SLOPE, INTERCEPT e CORREL di Excel.

Al nostro foglio (nella riga 14) abbiamo aggiunto titoli per queste tre funzioni. Eseguiremo i calcoli effettivi nelle celle al di sotto di questi titoli.

Innanzitutto, calcoleremo la PENDENZA. Seleziona la cella A15.

Vai a Formule & gt; Altre funzioni & gt; Statistico & gt; PENDENZA.

Viene visualizzata la finestra Argomenti della funzione. Nel campo "Known_ys", seleziona o digita le celle della colonna Valore Y.

Nel campo "Known_xs", seleziona o digita le celle della colonna X-Value. L'ordine dei campi "Known_ys" e "Known_xs" è importante nella funzione SLOPE.

Fai clic su "OK". La formula finale nella barra della formula dovrebbe apparire così:

= PENDENZA (C3: C12, B3: B12)

Si noti che il valore restituito dalla funzione SLOPE nella cella A15 corrisponde al valore visualizzato nel grafico.

Quindi, seleziona la cella B15 e poi vai a Formule & gt; Altre funzioni & gt; Statistico & gt; INTERCETTARE.

Viene visualizzata la finestra Argomenti della funzione. Seleziona o digita le celle della colonna Valore Y per il campo "Known_ys".

Seleziona o digita le celle della colonna X-Value per il campo "Known_xs". Anche l'ordine dei campi "Known_ys" e "Known_xs" è importante nella funzione INTERCETTA.

Fai clic su "OK". La formula finale nella barra della formula dovrebbe apparire così:

= INTERCETTA (C3: C12, B3: B12)

Si noti che il valore restituito dalla funzione INTERCETTA corrisponde all'intersezione y visualizzata nel grafico.

Quindi, seleziona la cella C15 e vai a Formule & gt; Altre funzioni & gt; Statistico & gt; CORRELAZIONE.

Viene visualizzata la finestra Argomenti della funzione. Seleziona o digita uno dei due intervalli di celle per il campo "Array1". A differenza di SLOPE e INTERCEPT, l'ordine non influisce sul risultato della funzione CORREL.

Seleziona o digita l'altro dei due intervalli di celle per il campo "Array2".

Fai clic su "OK". La formula dovrebbe apparire così nella barra della formula:

= CORRELAZIONE (B3: B12, C3: C12)

Si noti che il valore restituito dalla funzione CORREL non corrisponde al valore "r-quadrato" sul grafico. La funzione CORREL restituisce "R", quindi dobbiamo quadrarlo per calcolare "R-quadrato".

Fare clic all'interno della barra delle funzioni e aggiungere "^ 2" alla fine della formula per quadrare il valore restituito dalla funzione CORREL. La formula completata dovrebbe ora apparire come questa:

= CORRELAZIONE (B3: B12, C3: C12) ^ 2

Premere Invio.

Dopo aver modificato la formula, il valore "R-quadrato" corrisponde ora a quello visualizzato nel grafico.

Ora possiamo usare questi valori in semplici formule per determinare la concentrazione di quella soluzione "sconosciuta" o quale input dovremmo inserire nel codice in modo che il marmo voli una certa distanza.

Questi passaggi configureranno le formule necessarie affinché tu possa inserire un valore X o un valore Y e ottenere il valore corrispondente in base alla curva di calibrazione.

L'equazione della linea di miglior adattamento è nella forma "Valore-Y = PENDENZA * Valore-X + INTERCETTA", così la risoluzione per il "valore Y" viene eseguita moltiplicando il valore X e PENDENZA e quindi aggiungendo l'INTERCETTA.

Ad esempio, inseriamo zero come valore X. Il valore Y restituito dovrebbe essere uguale all'intercetta della linea di miglior adattamento. Corrisponde, quindi sappiamo che la formula funziona correttamente.

La risoluzione del valore X in base a un valore Y viene eseguita sottraendo INTERCEPT dal valore Y e dividendo il risultato per SLOPE:

X-value=(Y-value-INTERCEPT)/SLOPE

Ad esempio, abbiamo usato INTERCEPT come valore Y. Il valore X restituito dovrebbe essere uguale a zero, ma il valore restituito è 3.14934E-06. Il valore restituito non è zero perché abbiamo inavvertitamente troncato il risultato INTERCETTA quando si digita il valore. La formula funziona correttamente, tuttavia, poiché il risultato della formula è 0.00000314934, che è essenzialmente zero.

Puoi inserire qualsiasi valore X che desideri nella prima cella con bordi spessi e Excel calcolerà automaticamente il valore Y corrispondente.

Immettendo un qualsiasi valore Y nella seconda cella con bordi spessi si otterrà il valore X corrispondente. Questa formula è ciò che useresti per calcolare la concentrazione di quella soluzione o quale input è necessario per lanciare il marmo a una certa distanza.

In questo caso, lo strumento legge "5", quindi la calibrazione suggerirebbe una concentrazione di 4.94 o vogliamo che il marmo percorra cinque unità di distanza, quindi la calibrazione suggerisce di inserire 4.94 come variabile di input per il programma che controlla il lanciatore di marmo. Possiamo essere ragionevolmente fiduciosi in questi risultati a causa dell'alto valore del quadrato R in questo esempio.