Grafico di una serie di dati sperimentali in EXCEL · grafico può essere ottenuto semplicemente...

Grafico di una serie di dati sperimentali in EXCEL1. Inseriamo sulla prima riga il titolo che definisce il contenuto del foglio.

Poi inseriamo su un’altra riga i valori sperimentali della x e su quella successiva i valori

della y.

OSSERVAZIONE

Vedremo più avanti come inserire dei dati memorizzati precedentemente in un file.

2. Nel caso in cui i dati della x e della y siano stati immessi su due righe consecutive, il

grafico può essere ottenuto semplicemente selezionando dapprima i dati con il mouse

(oppure facendo clic sui numeri che contraddistinguono le due righe) e facendo poi clic

sull’icona GRAFICO presente sotto il menù principale. Icona GRAFICO

Grafico di una serie di dati sperimentali in

EXCEL (segue)3. Comparirà la seguente finestra che ci permetterà di scegliere il tipo di grafico .

Selezioniamo Dispersione (XY) , poi scegliamo di rappresentare i dati con dei simboli ed

infine facciamo clic su AVANTI..

Tipo di grafico

Diversi modi con cui unire

i punti

N.B. Nel caso di dati

sperimentali affetti da

errore i dati non vanno mai

uniti con delle linee bensì

solo rappresentati con dei

simboli

Grafico di una serie di dati sperimentali in

EXCEL (segue)4. Comparirà una nuova finestra che ci darà un’anteprima del grafico. Se tutto è OK

continuiamo a fare clic su AVANTI.

Grafico in Excel (segue)

5. Comparirà la seguente finestra che ci permetterà di inserire le informazioni relative a:

titolo del grafico, etichetta asse X ed etichetta asse Y.

Inoltre possiamo intervenire su diversi parametri (es. intervallo asse X o asse Y),

selezionando una delle opzioni presenti in alto. Per proseguire facciamo clic su AVANTI.

Grafico di una serie di dati sperimentali in EXCEL

(segue)6. Comparirà un’ultima finestra che ci permetterà di decidere se inserire il grafico in un

nuovo foglio a parte oppure all’interno dello stesso foglio di lavoro contenente i dati.

Siamo finalmente

arrivati in fondo !

Grafico di più serie di dati

Supponiamo di voler rappresentare contemporaneamente in un grafico più serie di

dati, tutte legate alla stessa variabile indipendente X. Ad esempio, il seguente foglio

contiene i dati relativi alle curve di distribuzione di H3PO4 ovvero come variano le

concentrazioni delle varie specie presenti in soluzione in funzione del pH:

Grafico di più serie di dati (segue)Per ottenere il grafico riportato nella seguente figura, sarà sufficiente selezionare col

mouse la zona rettangolare contenente tutti i dati e procedere in maniera analoga a

quanto visto precedentemente.

Curve di distribuzione H3PO4

0

0.2

0.4

0.6

0.8

1

1.2

0 5 10 15 20 25

concentrazioni

pH

[H3PO4]

[H2PO4-]

[HPO4--]

[PO43-]

Grafico di dati disposti su righe non contigue

1. Selezioniamo la prima riga (o colonna) contenente i dati, poi la seconda , tenendo

contemporaneamente premuto il tasto CTRL; successivamente facciamo clic

sull’icona GRAFICO presente sotto il menù principale oppure selezioniamo la voce

GRAFICO presente all’interno del menù INSERISCI. Comparirà la seguente finestra

che ci permetterà di scegliere il tipo di grafico.

Può accadere che, in seguito a dei calcoli eseguiti sui dati sperimentali i valori della x

e della y non siano su righe contigue. In tal caso bisogna procedere in modo diverso.

Supponiamo, ad esempio che tra le x e le y ci siano delle righe vuote come mostrato

nella seguente figura .

In tal caso è necessario eseguire la seguente procedura:

Grafico di dati disposti su righe non contigue (segue)

2. Dopo aver selezionato il tipo di grafico (a dispersione) facciamo di nuovo clic su AVANTI . Comparirà la seguente finestra che ci darà un’anteprima del grafico.


3. Nel caso in cui le due righe (o colonne) selezionate al punto 1) contengano, nell’ordine, prima i valori della X e poi quelli della Y, allora vi sarà una corrispondenza tra i dati relativi a X e l’asse X e i dati relativi a Y e l’asse delle Y , per cui il grafico rappresenterà correttamente i valori e potremo proseguire in maniera analoga a quanto visto precedentemente facendo clic su AVANTI.

4. Qualora l’ordine sia invertito, è necessario cliccare sulla linguetta in alto

denominata SERIE. Comparirà la seguente finestra di dialogo.


5. Le caselle denominate VALORI X e VALORI Y permettono di definire le righe

(o le colonne) del foglio in cui sono stati inseriti i dati relativi alle X e alle Y. E’

sufficiente fare clic sull’icona presente a destra di ciascuna casella e selezionare i

dati col mouse all’interno del foglio di lavoro. Possiamo poi proseguire in

maniera analoga a quanto visto precedentemente, facendo clic su AVANTI.

Come aggiungere una serie di dati a un grafico

Molto spesso, quando si elaborano dei dati sperimentali con EXCEL, la

procedura da seguire può essere riassunta così:

•Si inseriscono i dati sperimentali

•Si rappresentano graficamente i dati

•Si elaborano i dati sperimentali (es. con una regressione lineare)

•Si rappresentano sullo stesso grafico sia i dati sperimentali che quelli ottenuti

col calcolo, in modo da valutare graficamente la bontà del calcolo.

E’ possibile semplificare tutta la procedura creando dapprima il grafico

contenente i dati sperimentali e poi aggiungendo direttamente ad esso la serie di

dati ottenuta col calcolo. A tale scopo eseguiamo i seguenti passi:

1. Rappresentiamo in un grafico i dati sperimentali. Ad esempio, i seguenti

dati e il relativo grafico mostrano come varia la tensione di vapore di un

liquido puro al variare di T.

Come aggiungere una serie di dati a un grafico

(segue)

Misura tensione di vapore di un liquido

5

5.2

5.4

5.6

5.8

6

6.2

6.4

6.6

6.8

0.003 0.0031 0.0032 0.0033 0.0034 0.0035

1/T (K)

lnP

Serie1

Come aggiungere una serie di dati a un grafico (segue)

2. Supponiamo di aver elaborato i dati. La seguente figura mostra un foglio in cui i

dati calcolati si trovano su una riga in basso .

Selezioniamo il grafico facendo clic all’interno di esso, poi scegliamo

l’opzione DATI DI ORIGINE del menù GRAFICO. Comparirà la seguente

finestra di dialogo.

Come aggiungere una serie di dati a un grafico (segue)

Facendo clic qui definiamo il range di celle in cui si trovano le x

Facendo clic qui definiamo il range di celle in cui si trovano le y

Inseriamo qui il nome da dare alla nuova serie

di dati

Una volta che abbiamo terminato di inserire le informazioni

in questa finestra, facciamo clic su AVANTI e, proseguendo

in modo analogo a quanto visto precedentemente negli altri

casi, otterremo il grafico riportato di lato.

3. Facciamo clic su AGGIUNGI e, tramite le caselle VALORI X e VALORI Y,

definiamo l’intervallo di celle in cui si trovano le X e le Y.


5

5.2

5.4

5.6

5.8

6

6.2

6.4

6.6

6.8

0.003 0.0031 0.0032 0.0033 0.0034 0.0035

1/T (K)

lnP Serie1

calc

Come aggiungere le barre di errore a un grafico

Il modo migliore per visualizzare in un grafico le incertezze associate ai dati

sperimentali è quello di aggiungere le barre di errore .

La seguente figura riporta un foglio contenente i dati relativi alla misura della tensione di

vapore visti precedentemente. Sperimentalmente si osserva che i valori delle pressioni

hanno un’incertezza pari a 10 mmHg, mentre l’errore associato alla lettura della

temperatura è trascurabile.

Applicando la teoria della propagazione degli errori si ottengono le incertezze riportate

nella colonna E, relative a slnP, che risultano essere diverse, dato per dato, a causa della

propagazione degli errori.

Come aggiungere le barre di errore a un grafico

(segue)

Eseguiamo ora la seguente procedura per visualizzare le barre di errore associate

con le incertezze sperimentali ( slnP) legate ai valori della Y (lnP).

1. Dopo aver creato il grafico, facciamo dapprima clic sulla serie dei dati

all’interno del grafico, poi, tenendo premuto il pulsante destro del mouse e

senza spostarlo in modo da lasciare evidenziati i dati, scegliamo l’opzione

FORMATO SERIE DATI. Comparirà la seguente finestra di dialogo.

Come aggiungere le barre di errore a un grafico (segue)

2. Facendo clic sulla linguetta in alto denominata BARRE DI ERRORE Y, verrà

visualizzata la seguente finestra di dialogo, con cui possiamo definire sia l’aspetto

grafico con cui verranno visualizzate le barre, sia l’entità dell’errore.

Se i dati hanno tutti lo stesso errore, l’entità di quest’ultimo può

essere immessa in uno dei seguenti modi: valore fisso, in

percentuale oppure come deviazione standard.

Se invece i dati hanno incertezze diverse (come nel nostro

caso), allora è possibile utilizzare l’opzione PERSONALIZZA

per definire l’intervallo di celle contenenti i valori degli errori.

Tale opzione permette di impostare sia l’errore positivo che

quello negativo. Qualora fossero uguali, sarà sufficiente

impostare lo stesso intervallo di celle nelle due caselle + e - .

Fai clic qui per definire le celle che contengono

gli errori


5

5.2

5.4

5.6

5.8

6

6.2

6.4

6.6

6.8

0.003 0.0031 0.0032 0.0033 0.0034 0.0035

1/T (K)

lnP

Serie1

Inserimento di un file di dati in EXCELMolto spesso capita di dover rappresentare ed elaborare dei dati raccolti automaticamente

da uno strumento di laboratorio (es. dati spettroscopici, dati potenziometrici, ecc.).

In tal caso, invece di immettere a mano i dati in EXCEL, è possibile inserire velocemente i

dati sperimentali seguendo la seguente procedura:

1. Supponiamo che i dati siano stati memorizzati in un file dati e in formato solo testo (o

ASCII); inoltre supponiamo che sulla prima riga vi sia la x , in cui i singoli valori sono

separati con dei spazi (o da un TAB) e sulla riga successiva la y, ancora con valori separati

da spazi. La seguente figura mostra il contenuto del file dati all’interno di WORDPAD.

N.B.

Il separatore dei decimali può essere una virgola

oppure un punto a seconda delle impostazioni

internazionali con cui è settato il computer tramite

il pannello di controllo.

Inserimento di un file di dati in EXCEL (segue)

2. Dopo aver caricato EXCEL si fa clic su APRI del menù FILE e si seleziona il nome del

file dati precedentemente memorizzato, dopo aver scelto come tipo di file, File di testo.

Inserimento di un file di dati(segue)

2: Al comparire della seguente finestra di dialogo selezioniamo l’opzione Delimitati e

proseguiamo facendo clic su AVANTI.

Inserimento di un file di dati(segue)

3. Comparirà una nuova finestra che ci permetterà di modificare il tipo di delimitatore.

In basso vi è un’anteprima di come verranno inseriti i dati all’interno del foglio di

EXCEL. Se è tutto Ok, facciamo ancora clic su AVANTI, quindi , al comparire di

un’ennesima finestra su FINE.

ANALISI DI REGRESSIONELa tecnica di REGRESSIONE ci permette di ricavare la miglior curva passante attraverso i

punti sperimentali.

Un esempio tipico è la ricerca di una funzione partendo da dati sperimentali affetti da

errore e si vuole ricavare la funzione che meglio approssima l’andamento generale dei dati

(es. dati provenienti dallo studio cinetico di una reazione chimica, oppure da misure di

tensione di vapore di una sostanza in funzione della temperatura, oppure da dati di

calibrazione di uno strumento: es. spettrofotometro UV-Vis, rifrattometro di Abbe,

trasduttore di temperatura, ecc.).

Un metodo per ricavare la migliore curva che approssimi l’andamento dei dati è quello di

applicare il metodo dei minimi quadrati . Questa tecnica si basa sul principio che la

miglior curva-modello che interpola dei dati sperimentali, sia quella per la quale valga la

relazione :

imoyywi

n

i ii min)( 2

1

^

ANALISI DI REGRESSIONE(segue)

ii yy^

dove wi sono detti pesi dei dati sperimentali (tengono conto della maggiore o minore

incertezza con la quale i dati sperimentali stessi sono noti), e

è la differenza tra i valori sperimentali yi e quelli calcolati^

iy

REGRESSIONE LINEARE (SEMPLICE)

L’esempio più semplice di applicazione dei minimi quadrati è l’adattamento di una linea

retta a un insieme di punti definiti da coppie di dati (x1 , y1) , (x2 , y2) , ecc.

Per semplificare la nostra discussione supporremo che, sebbene le nostre misure di y siano

soggette a qualche incertezza, l’incertezza nelle nostre misure di x è trascurabile. Questa è

spesso un’ipotesi ragionevole, poiché le incertezze in una variabile sono spesso molto più

grandi di quelle nell’altra, che noi possiamo con sicurezza ignorare. (Tuttavia in

laboratorio può capitare che anche le x siano affette da errore.) Assumeremo inoltre che le

incertezze in y abbiano tutte lo stesso valore e siano dovute a errori casuali , che possono

essere descritti da una distribuzione gaussiana. Anche questa è un’ipotesi ragionevole in

molti esperimenti, ma se le incertezze sono diverse, allora la nostra analisi può essere

generalizzata “pesando” le misure appropriatamente, utilizzando , come vedremo più

avanti, i cosiddetti minimi quadrati pesati.

REGRESSIONE LINEARE (SEMPLICE)(segue)

L’espressione matematica che ci serve è la seguente:

bxay

dove a e b sono i coefficienti che rappresentano , rispettivamente, l’intercetta e la

pendenza della retta e è l’errore, o residuo, tra il modello e il dato osservato .

Riordinando i termini, l’errore può essere rappresentato come

bxay

Quindi l’errore, o residuo, è la differenza tra il vero valore di y corrispondente a un dato x

e l’approssimazione a + bx dato dall’equazione lineare.

La strategia adottata applicando il metodo dei minimi quadrati per determinare la retta che

meglio approssima l’andamento dei dati, consiste nel minimizzare la somma dei quadrati

degli errori Sr, data da

i i

iiir xbayS 22 )(

REGRESSIONE LINEARE (semplice)(segue)

in cui wi è stato posto uguale a 1 per tutti i valori di yi. Ciò è una conseguenza del

fatto che abbiamo assunto l’ipotesi che le yi abbiano tutte la stessa incertezza.

Questo criterio comporta una serie di vantaggi, ma la sua caratteristica più

importante è che consente di determinare una sola linea per ogni insieme di dati: in

altre parole, porta a un risultato univoco.

Vediamo ora come sia possibile col suddetto metodo determinare i valori di a e b

che corrispondono al minimo di Sr.

Volendo determinare i valori di a e b, deriviamo l’equazione di Sr, rispetto a

ciascuno dei coefficienti:

i

iir xbay

a

S)(2

i

iiir xxbay

b

S])[(2

I simboli di sommatoria sono stati semplificati; se non viene specificato diversamente,

si assume che tutte le sommatorie vengano eseguite per i che va da 1 a n, dove n è il n.

totale di coppie di dati.


Uguagliando a zero queste derivate, troviamo quei valori che rendono minima Sr: le

equazioni vengono allora espresse come

ii xbay0

20 iiii xbxaxy

(1)

Ora , tenendo conto che ana

dove n è il n. totale di coppie di dati, possiamo esprimere le equazioni come sistema

di due equazioni lineari nelle due incognite a e b:

ii ybxan

iiii yxbxax 2

Queste equazioni vengono dette EQUAZIONI NORMALI.


Risolvendo le equazioni rispetto a b, otteniamo

22 )( ii

iiii

xxn

yxyxnb

Questo risultato, utilizzato insieme alla (1), dà

xbya

dove y e x sono, rispettivamente, i valori medi di y e di x.

(2)

L’equazione (2) ci dice che la retta migliore passa per il punto __

),( yx

detto centroide o centro di gravità (valor medio dei valori di xi e yi).

REGRESSIONE LINEARE :stima delle incertezze

sui parametri a e b

Abbiamo visto come sia possibile ricavare la miglior retta passante per dei punti

sperimentali adottando il metodo dei minimi quadrati ovvero rendendo minima la

somma dei quadrati degli errori Sr. Qualsiasi altra retta calcolata non applicando la

suddetta relazione, senz’altro avrebbe dato luogo a un Sr maggiore.

Quello che vogliamo vedere è di quantificare come gli errori casuali presenti

prevalentemente sui valori sperimentali di yi influenzino l’incertezza dei valori

calcolati di y e dei parametri a e b.

La deviazione standard relativa alla regressione, s , (o deviazione standard residua

della stima di y su x, detto anche errore standard in EXCEL) coincide con la

deviazione standard della somma dei quadrati dei residui . Il suo valore ha le setesse

dimensioni della variabile dipendente y e viene ricavato tramite l’equazione :

2)(2

1ii xbay

ns

Più è piccolo il suo valore è più vicina è la retta calcolata ai dati sperimentali.

REGRESSIONE LINEARE :stima delle incertezze

sui parametri a e b (segue)

2

12

2

2

1

2

i i

ii

i

i

a

xxn

xs

s

Le incertezze sui parametri a e b sono ricavabili mediante le seguenti equazioni

ottenute applicando le regole per la propagazione degli errori (e supponendo sempre

che le incertezze su yi abbiano lo stesso valore):

2

12

2

2

1

i i

ii

b

xxn

nss

REGRESSIONE LINEARE :stima intervallo di

fiducia sui parametri a e b

La miglior stima dei valori di a e b, relativamente ad un certo livello di fiducia (es.

95%), può essere ottenuta ricavando l’intervallo di fiducia per ognuno di essi mediante

le seguenti relazioni:

bXtbb sn %, aXtaa sn %,

dove tX%,n è il parametro di Student corrispondente a un determinato livello di fiducia

X% e a un certo numero di gradi di libertà , nn-2.

REGRESSIONE LINEARE :stima di x0 per un dato y0

Una volta ricavati i parametri a e b è possibile determinare il valore incognito di x,

indicato con x0 , corrispondente a un determinato valore misurato di y, indicato con y0.

Supponiamo, ad esempio, di voler determinare la composizione di una soluzione a

concentrazione incognita, costituita da due liquidi organici (es. toluene – acetato

etile), esprimendola in funzione della frazione molare del toluene.

A tale scopo, dopo aver eseguito delle misure rifrattometriche con degli standard,

abbiamo calcolato la miglior retta passante per i dati sperimentali, supponendo che le

incertezze associate alla misura dell’indice di rifrazione siano:

•affette prevalentemente da errori casuali e abbiano tutte lo stesso valore

•L’incertezza associata con la preparazione delle soluzioni standard a concentrazione

nota è trascurabile rispetto a quella dell’indice di rifrazione.

Il valore della concentrazione della soluzione incognita,x0, per un dato valore

dell’indice di rifrazione, y0, sarà dato da

b

ayx

0

0


(segue)

0xs

i

i

x

xxb

yy

nmb2

_2

2_

0110

ss

Le incertezze ottenute sui parametri a e b faranno sì che vi sia anche un’incertezza

sul valore calcolato di x0, indicata con

Applicando la teoria di propagazione degli errori, tenendo presente che le incertezze

associate con i parametri a e b non sono tra loro indipendenti, si può dimostrare che

l’incertezza associata col valore calcolato di x0, è data da

in cui y0 è il valore sperimentale di y da cui si vuole determinare x0, s è la deviazione

standard residua, n è il n. totale di dati con cui si è costruita la retta, m è il n. di misure

replicate di y0 (m=1 per una singola misura) e __

yex

sono i valori medi di x e di y.

(3)


(segue)

La miglior stima di x0 , relativamente ad un certo livello di fiducia (es. 95%), può essere

ottenuta ricavando l’intervallo di fiducia tramite la seguente relazione:

0%,00 xXtxx sn

dove tX%,n è il parametro di Student corrispondente a un determinato livello di fiducia X% e

a un certo numero di gradi di libertà , n=n-2.

(4)

REGRESSIONE LINEARE :stima di y0 per un dato x0

00 xbay

2

2

2_

010

i

i

i

i

y

xxn

xxn

nss

Una volta noti i coefficienti a e b della retta, è possibile predire il valore di y0 per

un dato valore sperimentale della variabile indipendente x ovvero x0, tramite

l’equazione

Applicando la teoria di propagazione degli errori, tenendo presente che le incertezze

associate con i parametri a e b non sono tra loro indipendenti, si può dimostrare che

l’incertezza associata col valore calcolato di y0, è data da

La miglior stima di y0 sarà data da 0%,00 yXtyy sn

(5)

(6)

Regressione lineare semplice in EXCEL: un esempio di

elaborazione di dati sperimentali

Consideriamo le seguenti 3 serie di dati sperimentali e , prima di

applicare il metodo dei minimi quadrati, proviamo a rappresentare i dati

graficamente.

8 6,95 8 8,14 8 6,77

13 7,58 13 8,74 13 12,74

9 8,81 9 8,77 9 7,11

11 8,33 11 9,26 11 7,81

14 9,96 14 8,1 14 8,84

6 7,24 6 6,13 6 6,08

4 4,26 4 3,1 4 5,39

12 10,84 12 9,13 12 8,15

7 4,82 7 7,26 7 6,42

5 5,68 5 4,74 5 5,73

Serie N.1

0

2

4

6

8

10

12

0 5 10 15

Serie N.1

0

2

4

6

8

10

12

0 5 10 15

x

y y

Serie N.2

0

1

2

3

4

5

6

7

8

9

10

0 2 4 6 8 10 12 14 16

x

y y

Serie N.3

0

2

4

6

8

10

12

14

0 5 10 15

x

y y

I dati relativi alla serie N.1 possono essere

rappresentati da una retta

I dati relativi alla serie N.2 possono essere

rappresentati da una curva

A parte il penultimo dato, anche la serie N. 3

può essere rappresentata da una retta

Regressione in EXCEL (segue)

Se applichiamo i minimi quadrati alle 3 serie di dati non tenendo conto dei grafici visti

ed imponendo che tutte le serie di dati siano rappresentate da una retta, otterremo i

seguenti dati statistici:

serie 1 serie 2 serie 3

a0 3 3 3

a1 0,5 0,5 0,5

sigmay 1,237 1,237 1,236

sigma0 1,125 1,125 1,124

sigma1 0,118 0,118 0,119

In altri termini, basandosi soltanto su questi dati, possiamo affermare tranquillamente

che tutte le 3 serie di dati possono essere rappresentate da una retta di equazione

y=a + bx.

Tuttavia se oltre ai dati statistici osserviamo i grafici dei residui, possiamo dedurre

quanto segue:

residui

-2,500000

-2,000000

-1,500000

-1,000000

-0,500000

0,000000

0,500000

1,000000

1,500000

2,000000

2,500000

0 5 10 15

serie n. 1 residui

residui

-2,500000

-2,000000

-1,500000

-1,000000

-0,500000

0,000000

0,500000

1,000000

1,500000

0 5 10 15 serie n. 1 residui

residui

-1,500000

-1,000000

-0,500000

0,000000

0,500000

1,000000

1,500000

2,000000

2,500000

3,000000

3,500000

0 5 10 15

serie n. 1 residui

I dati sono sparpagliati attorno all’asse X

I dati non sono sparpagliati lungo l’asse delle

X bensì assumono un andamento particolare

(parabolico)

A parte il penultimo dato che si trova sopra l’asse

delle X, gli altri dati non sono distribuiti

casualmente attorno l’asse X ma si trovano tutti

sotto tale asse. (Il che fa pensare che sia presente

un errore sistematico nei dati.)

Minimi quadrati in EXCEL: metodi disponibili

EXCEL ci mette a disposizione 3 diversi modi di applicazione del metodo dei minimi

quadrati a dei dati sperimentali:

1) Operando direttamente sul grafico ed utilizzando il comando AGGIUNGI LINEA DI

TENDENZA. E’ sufficiente selezionare i dati sul grafico e , dopo aver premuto il pulsante

destro, scegliere l’opzione Aggiungi linea di tendenza. Infine si seleziona il tipo di

regressione.

Minimi quadrati : Aggiungi linea di tendenza

• Sul grafico viene tracciata

automaticamente la miglior retta

passante per i dati

• E’ possibile visualizzare

l’equazione della retta

• Come dato statistico si ha solo a

disposizione il coefficiente di

correlazione R

N.B. Per modificare il n. di cifre decimali con cui vengono visualizzati i parametri

all’interno dell’equazione è sufficiente fare dapprima clic col pulsante sinistro sulla

formula all’interno del grafico e poi , dopo aver premuto il pulsante destro,

selezionare la voce FORMATO ETICHETTE DATI.

Minimi quadrati : Funzione REGR.LIN

La funzione REGR.LIN è disponibile tramite la voce FUNZIONE del menù

INSERISCI.

A differenza di AGGIUNGI LINEA DI TENDENZA, tale funzione restituisce alcuni

parametri statistici.

UTILIZZAZIONE

1. Inseriamo sul foglio i dati sperimentali

2. Selezioniamo un blocco di celle vuote costituito da 5 righe e due

colonne

D:/excel/regressione serie 1.xls

Funzione REGR.LIN (segue)3. Facciamo clic su Funzione del menù INSERISCI e quindi selezioniamo la funzione

REGR.LIN appartenente alla categoria statistiche. Comparirà la seguente finestra che

ci chiederà di definire alcuni parametri.

• Y_nota : intervallo di celle contenenti la y sperimentale

• x_nota : intervallo di celle contenenti la x sperimentale

• Cost : fa riferimento all’intercetta a0 ovvero se la retta deve passare o meno per

l’origine . Immettere VERO se non passa per l’origine e FALSO se passa per

l’origine

• Stat : con VERO la funzione , oltre ai coefficienti della retta, restituisce alcuni dati

statistici; con FALSO restituisce solo i coefficienti della retta

Funzione REGR.LIN (segue)

4. Invece di fare clic su OK o di premere INVIO, si deve premere INVIO tenendo

contemporaneamente premuti i tasti CTRL e SHIFT.

Solo così verranno riportati, sul blocco di celle selezionate precedentemente , i risultati del

calcolo ovvero i seguenti dati relativi alla retta di equazione y= a + b x

0,500091 3,000091

0,117906 1,124747

0,666542 1,236603

17,98994 9

27,51 13,76269

b a

sb sa

R2 s

Statistica F Gradi di libertà

La somma residua dei quadratiLa somma della regressione dei

quadrati

Funzione REGR.LIN: grafico dei residui5. Con i dati statistici ottenuti con la funzione REGR.LIN possiamo ora tracciare il

grafico dei residui utilizzando la seguente procedura:

• Inserimento di due righe contenenti le

seguenti informazioni:

- yi,calc =a + bxi,sper

- yi,sper. - yi,calc

• Inserimento nel foglio del grafico dei

residui (x=xsper. Y=yi,sper. - yi,calc)

Minimi quadrati con lo strumento

REGRESSIONE

Lo strumento REGRESSIONE è disponibile tramite STRUMENTI -> ANALISI DATI -

> REGRESSIONE.

A differenza di AGGIUNGI LINEA DI TENDENZA, tale funzione restituisce diversi

parametri statistici. Inoltre permette anche di eseguire i minimi quadrati su una funzione

Y che dipende da più variabili indipendenti x (y=a0 + a1 x1 + a2 x2+ ……. come pure su

una funzione polinomiale di grado superiore a 1)

UTILIZZAZIONE

1. Inseriamo sul foglio i dati sperimentali

2. Selezioniamo la voce Analisi Dati del menù STRUMENTI. Se la voce non

dovesse essere presente nel menù, significa che il pacchetto Analisi Dati non è

stato ancora installato.

Strumento REGRESSIONE (segue)

3. Comparirà la seguente finestra che ci mette a disposizione numerosi strumenti di analisi.

Scegliamo l’opzione REGRESSIONE.

Strumento REGRESSIONE (segue)4. Comparirà la seguente finestra che ci chiederà di definire alcuni parametri.

Impostiamo solo i seguenti parametri di input e di output:

• Intervallo di input Y : intervallo di celle

contenenti la y sperimentale

• Intervallo di input X : intervallo di celle

contenenti la x sperimentale

•Livello di confidenza: livello di fiducia

con cui vogliamo vengano espressi i valori

dei coefficienti a e b

•Passa per l’origine: ovvero se vogliamo

imporre nel calcolo che la retta passi per

l’origine

PARAMETRI DI INPUT


PARAMETRI DI OUTPUT

• Intervallo di output : Intervallo di celle in cui verranno mostrati i dati calcolati dallo

strumento REGRESSIONE.

Conviene scegliere, come riferimento, un nuovo foglio di lavoro

•Residui e Tracciati dei residui: contrassegnare tali opzioni in modo da visualizzare

anche il grafico dei residui.

Strumento REGRESSIONE (segue)Esempio di dati ottenuti operando con lo strumento REGRESSIONE sulla prima

serie di dati

Strumento REGRESSIONE (segue)Per comprendere meglio il significato e l’uso dei dati statistici riportati nella diapositiva

precedente proviamo a considerare i seguenti esercizi.

ESERCIZIO 1

Una serie di soluzioni standard di fluoresceina sono state esaminate con uno

spettrofotometro di fluorescenza, ottenendo i dati riportati nella seguente tabella:

Intensità

(u.a.)

2.1 5.0 9.0 12.6 17.3 21.0 24.7

Concen-

trazione

(pg/ml)

0 2 4 6 8 10 12

a) Ricavare la miglior retta di taratura, supponendo che le incertezze su y siano di

tipo casuale e tutte uguali, mentre quelle su x siano trascurabili.

b) Esprimere i coefficienti a e b con un livello di fiducia del 95%

c) Riportare in un grafico l’intervallo di fiducia associato a ciascuna y

d) Calcolare la miglior stima della concentrazione corrispondente alle seguenti

intensità misurate : 2.9 ; 5.6 ; 9.5 ; 13.5 e 23.0 .


RISULTATI OTTENUTI


Analizzando i dati riportati nel foglio di lavoro restituito da EXCEL, possiamo

affermare quanto segue:

a) I valori dei coefficienti a (Intercetta) e b(variabile X1) sono, rispettivamente

1.517857 (cella B17) e 1.930357 (cella B18).

Pertanto la miglior retta di taratura è:

y= 1.52 + 1.93 x

b) L’errore standard s è uguale a 0.4328 (cella B7), mentre gli errori standard di a e

b sono, rispettivamente, 0.294936 (cella C17) e 0.04090 (cella C18).

Sul foglio viene inoltre riportata la miglior stima di a e b, relativamente al livello

di fiducia prescelto (95%) sotto forma di estremi di intervallo (inferiore 95%,

superiore 95% ). Per esprimere i valori dei parametri con associata l’incertezza

espressa come ±, basta sottrarre al valore corrispondente all’estremo superiore,

quello del coefficiente, ottenendo:

a = 1.52 ± 0.76 b=1.93 ± 0.11

N.B. Si poteva ottenere lo stesso risultato, applicando le formule


btbb s 5%,95ataa s 5%,95

N.B. Si poteva ottenere lo stesso risultato, applicando le formule

ricavando il parametro t95%,5 tramite le relative tabelle della distribuzione di

Student, oppure utilizzando la funzione di EXCEL

INV.T(probabilità;gradi di libertà)

dove probabilità=(100-X%)/100; =0.05 per un livello di fiducia X%=95%.

c) Per riportare in un grafico l’intervallo di fiducia associato a ciascun valore

calcolato di y0 partendo dai dati sperimentali di x0 , è stato dapprima ricavato il

valore di y0 utilizzando la retta ottenuta in a) ; successivamente, è stata utilizzata

l’equazione (6) per ricavare l’incertezza associata a ciascun valore di y0. Infine

è stata espressa ciascuna incertezza con un livello di fiducia del 95%,

utilizzando l’equazione 0%,00 yXtyy sn

La seguente figura riporta il grafico ottenuto.


d) La miglior stima della concentrazione corrispondente alle intensità misurate

di 2.9 ; 5.6 ; 9.5 ; 13.5 e 23.0, è stata ricavata dapprima calcolando

l’incertezza sx0 tramite l’equazione 3) vista precedentemente e

successivamente applicando la formula

0%,00 xXtxx sn

La seguente figura riassume i risultati dei calcoli relativi all’esercizio.


ELABORAZIONE DATI


La seguente figura riporta, infine le bande di fiducia per i vari valori di x.


ESERCIZIO 2

Uno spettrofotometro di assorbimento UV-VIS viene utilizzato per determinare la

concentrazione dell’arsenico in una soluzione. La seguente tabella riporta i dati

ottenuti con delle soluzioni di arsenico a concentrazione nota.

C(ppm) 2.151 9.561 16.878 23.476 30.337

A 0.0660 0.2108 0.3917 0.5441 0.6795

a) Calcolare la miglior retta con lo strumento REGRESSIONE.

b) Utilizzare la retta di calibrazione per stimare un valore di C (x0)

corrispondente a una assorbanza di y0=0.3520 , ottenuta replicando la misura

3 volte e facendone una media.


a) La relazione che lega l’assorbanza di un picco, misurata a una certa l e avente un

coefficiente di estinzione molare , l, alla concentrazione della specie responsabile

dell’assorbimento, è la Legge di Lambert Beer

lll ctA

dove t è lo spessore della celletta contenente la soluzione da misurare.

Ponendo y=Al , b=lt e x=cl, abbiamo che l’equazione, che potrebbe correlare i dati

riportati in tabella , è del tipo

y = b x

Tuttavia, osservando soltanto i dati riportati in tabella, non possiamo affermare con

certezza che, a concentrazione 0, l’assorbanza sia nulla. Potrebbe non esserlo a causa

di qualche influenza da parte della matrice ovvero della soluzione contenente

l’arsenico, oppure a un offset sistematico dovuto a una non perfetta calibrazione dello

strumento.

A tale scopo conviene dapprima supporre che la curva che rappresenta meglio i dati

sia una retta che non passi per l’origine, ovvero

y = a + b x


E poi discutere se è significativo affermare che il valore dell’intercetta ottenuto con il

calcolo sia nullo. Se sì, allora è giusto supporre che l’equazione che meglio riproduce i

dati è y=b x, per cui possiamo ripetere il calcolo imponendo che la retta passi per

l’origine.

La seguente figura mostra i risultati ottenuti con lo strumento REGRESSIONE

supponendo che la retta non passi per l’origine e che il livello di fiducia con cui

vogliamo avere la migliore stima dei coefficienti a e b sia del 95%.

Osservando i dati possiamo dedurre quanto segue:

1)

errore standard regressione s=0.01153

gradi di libertà = n. dati (o osservazioni) - 2 = 3

Intercetta a=0.0125787 errore standard sa=0.009983

Variabile X1 (o pendenza) b=0.0221983 errore standard sb=0.0005187


_

x

_

%, _infx

Xtx sn

_

%, _supx

Xtx sn

2)Il valore di a ottenuto è molto piccolo. E’ possibile che l’incertezza associata col dato

(sa) e dovuta alla presenza di soli errori casuali , sia l’unica responsabile del fatto che a

non è esattamente uguale a 0. In effetti, osservando i valori INFERIORE 95% e

SUPERIORE 95%, sembra proprio che il valore di a oscilli attorno allo 0.

Tuttavia, per essere ragionevolmente sicuri di affermare che a possa essere posto uguale

a 0, è necessario eseguire il cosiddetto test statistico di significatività, che va a vedere

se una data ipotesi (il porre a uguale a 0) sia significativamente probabile.

Quando in un processo di misurazione sono presenti solo degli errori casuali e la misura

viene replicata un numero limitato di volte per ottenere la migliore stima di una

grandezza relativamente a un certo livello di fiducia pari a X% (es. 95%), la dispersione

dei valori misurati attorno al valore medio può essere rappresentata mediante la curva di

distribuzione di Student , dove rappresenta la migliore stima del valore vero m,

ottenuta facendo la media dei valori e la coppia (inf, sup) rappresenta l’intervallo di

fiducia entro cui si ha una probabilità di X% che i valori misurati cadano dentro questo

intervallo. I valori di inf e sup vengono ricavati mediante le seguenti relazioni, dove

tX%,n è il parametro di Student per un certo livello di fiducia X% e per numero di gradi

di libertà n


Curva di distribuzione di Student

La seguente figura mostra la corrispondente curva di distribuzione normale di Student

in funzione del parametro

n

xt

s

m_

Dove s è la deviazione standard e n è il n. di repliche.


Il valore di t delimita l’area della curva entro cui vi è una probabilità di X% che il valore

misurato cada entro questo intervallo.

L’area in chiaro è pari a X%/100. D esempio, nel caso in cui X% sia uguale a 95%,

l’area è uguale a 0.95.

Se indichiamo con a/2 ciascuna delle due aree esterne all’intervallo (-t, t ) (si suppone

che la curva che rappresenti il campione di dati sperimentali sia simmetrica) e tenendo

presente che l’area totale è 1, avremo che

1100

%

22

Xa

Ad esempio, se X%=95% a+0.95=1 e a=0.05

Se esprimiamo la probabilità che un risultato cada all’interno di un certo

intervallo, oppure al di fuori, con il parametro a, denominato anche livello di

significatività, possiamo dedurre quanto segue:

se la misura viene replicata più volte, la probabilità che essa cada all’interno

dell’intervallo corrispondente al livello di fiducia pari a 95%, deve essere

maggiore di 0.05, mentre cadrà al di fuori di esso se minore di tale valore.


Oltre che per stabilire se la media di n misure cada all’interno di un certo intervallo, il

parametro a può anche essere utilizzato per controllare se l’intercetta a, ottenuta col

calcolo dei minimi quadrati , sia significativamente uguale a 0.

La procedura per eseguire il test può essere riassunta nel seguente modo:

•Stabiliamo l’ipotesi che vogliamo verificare: è a uguale a 0 ?

Indichiamo questa ipotesi con la simbologia : H0: a=0

•Stabiliamo l’ipotesi alternativa : H0 : a #0

•Definiamo il livello di significatività corrispondente ad un dato livello di fiducia X%

Quando le 2 ipotesi alternative corrispondono ad andare a vedere quanto è l’area

all’interno o al di fuori di un certo intervallo, si parla di test a 2 code e il valore di

significatività sarà uguale a

100

%1

xa

Se invece le 2 ipotesi consistono nel discutere se un certo risultato possa essere < o

> di un certo valore, allora dobbiamo considerare solo una delle 2 aree esterne, per

cui si parla di test a una coda e

100

%

2

11

xa


Nel nostro caso il test è a 2 code per cui a=0.05.

• Ricaviamo il valore di t corrispondente al valore ottenuto oggetto del test. Nel caso

di una retta, abbiamo

a

at

s

dove sa è l’errore standard su a ottenuta col calcolo, supponendo che la retta

non passi per l’origine. Nel nostro esempio si trova che t=1.26 .

• Dalle tabelle che riportano i valori critici di t andiamo a vedere quale è il livello di

significatività, tenendo presente se il test è a 1 coda oppure a 2 code e che n=n-2 .

Nel nostro caso n=3 e il valore di a corrispondente a t=1.26 è compreso tra 0.1 e

0.32 . Essendo tale valore maggiore di 0.05, significa che l’ipotesi di considerare

l’intercetta a uguale a 0 è significativa e va quindi accettata.

OSSERVAZIONE

Per avere un valore più preciso basta usare la funzione di EXCEL

DISTRIB.T(x;gradi_libertà ;coda)

in cui x è il valore di t ottenuto uguale a 1.26 , gradi di libertà=3 e coda=2 .

Strumento REGRESSIONE (segue)Si poteva giungere allo stesso risultato andando a confrontare il valore di t

ottenuto con quello critico riportato in tabella, corrispondente a un dato X% e

n. Avremmo avuto 2 possibilità:

criticott -

Dalla figura vista precedentemente, che riporta la curva di

distribuzione di Student in funzione di t, ciò significa che siamo dentro

l’area, per cui vi è una probabilità di X% che il risultato ovvero che a

sia uguale a 0

criticott -

Siamo al di fuori dell’area, per cui vi è una probabilità di 100-X% (5%

se X%=95) che a sia diversa da 0.


Si poteva giungere allo stesso risultato senza fare alcun calcolo, esaminando

semplicemente i valori restituiti da EXCEL mediante lo strumento

REGRESSIONE, riportati precedentemente. Infatti, nelle colonne denominate

Stat t e valore di significatività, relativamente alla riga intercetta, vengono già

riportati i valori di t e di a.

Ripetendo il calcolo con EXCEL, dopo aver imposto che la retta passi per

l’origine, otteniamo i risultati riportati nella seguente figura, da cui possiamo

dedurre quanto segue:

- Errore standard regressione : 0.012349

- Gradi di libertà : 3

- a= 0

- b = 0.022758 sb=0.000287

La migliore stima di b, con un livello di fiducia del 95% è

b=0.022758 ± 0.0007965 = (2.28 ± 0.08) x 10-2

REGRESSIONE POLINOMIALE

Nell’elaborare i dati sperimentali, vi sono delle situazioni in cui essi sono meglio rappresentati da

una curva piuttosto che da una retta. Ad esempio, la dipendenza di alcune grandezze fisiche da T

(es. calori molari (intervallo di T ristretto), resistenza di una lega , tensione di una termocoppia,

ecc.) può essere rappresentata meglio da una equazione polinomiale di 2° o 3° grado piuttosto che

da una retta.

La tecnica dei minimi quadrati, vista precedentemente per ricavare la migliore stima dei

coefficienti a e b di una retta, può essere utilizzata anche per ricavare i coefficienti di una

equazione polinomiale ovvero rendendo minima la relazione

n

i

iii imoyyw1

2^

min)(

iy^

m

iiiii mxdxcxbxay ....32^

in cui è uguale a

dove m è il grado del polinomio.

REGRESSIONE POLINOMIALE(segue)

Uguagliando a 0 le equazioni e riordinando, otteniamo il seguente sistema di equazioni:

iii yxcxbna 2

iiiii yxxcxbxa 32

iiiii yxxcxbxa 2432

dove tutte le sommatorie vengono eseguite per i che va da 1 al n. totale di dati n .

Excel permette di ricavare la miglior stima dei parametri a,b e c in modo semplice utilizzando

gli strumenti visti precedentemente per la retta , purchè le nostre misure soddisfino le seguenti

condizioni:

- Le incertezze su y siano tutte uguali

- le incertezze su y siano normalmente distribuite

- le incertezze sulle x siano trascurabili

- gli errori sistematici su x e y siano trascurabili

REGRESSIONE POLINOMIALE 2° grado in Excel

Supponiamo, ad esempio, di voler applicare lo strumento REGRESSIONE di ANALISI DATI di

Excel ai seguenti dati, che riportano i valori sperimentali della resistenza di un trasduttore di

temperatura RTD in funzione della temperatura:

REGRESSIONE POLINOMIALE 2° grado in ExcelProviamo ad inserire i dati in un foglio di lavoro di Excel, secondo quanto mostrato nella seguente

figura, in cui ai dati originali è stata aggiunta una nuova colonna contenente la variabile indipendente

x (ovvero T) elevata al quadrato.


Scegliamo l’opzione REGRESSIONE dello strumento ANALISI DATI. Comparirà la seguente

finestra di dialogo già discussa precedentemente nel caso della retta, in cui il parametro Intervallo

di input X questa volta dovrà fare riferimento alle celle contenenti i valori di x e di x2.

OSSERVAZIONE. Il parametro intervallo di input X dipende pertanto dal grado del polinomiale

utilizzato per elaborare i dati e lo strumento REGRESSIONE può essere usato per un polinomiale

di qualsiasi ordine. Basta inserire nel foglio le relative colonne.( x2, x3, ecc.).


Esempio di dati ottenuti eseguendo una regressione polinomiale di 2° grado sui dati

REGRESSIONE LINEARE PESATA

Abbiamo visto come , nel caso della regressione lineare semplice, le incertezze

associate alle yi abbiano tutte lo stesso valore.

Tuttavia possono verificarsi delle situazioni in cui, pur essendo l’errore su y

predominante su quello presente su x, l’incertezza su ciascun valore di y è diversa.

Ciò può, ad esempio, verificarsi quando:

-Le misure vengono ripetute ad un particolare valore di x, riducendo così

l’incertezza del corrispondente valore di y.

-I dati vengono trasformati in modo da avere una relazione lineare.

Ad es. , nel caso di misure di tensione di vapore, la pressione parziale p del liquido

dipende da T secondo

TR

Hap

vap

ln

Ponendo y=lnP , x=1/T e b=-Hvap/R, l’equazione della retta che meglio

soddisfa i dati è y=a + b x.

REGRESSIONE LINEARE PESATA (segue)

ppypp

yi

sss

1

I valori della pressione di vapore p misurati avranno tutti la stessa incertezza, se la

misurazione viene condotta sempre nello stesso modo. Tuttavia, quando si fa la

trasformazione logaritmica, le incertezza su y non sono più tutte uguali. Infatti,

applicando la teoria della propagazione degli errori avremo

Mentre le sp hanno tutte lo stesso valore, le incertezze syi diminuiranno

all’aumentare di p.

In questi casi il calcolo della regressione lineare deve essere fatta introducendo

dei fattori peso che tengono conto del fatto che si ottiene la miglior retta se

diamo più peso ai dati meno affetti da errore.

Si introduce pertanto un fattore peso wi inversamente proporzionale al quadrato

delle incertezze syi

2

1

iy

iws


2

2

p

i

pw

s

Nel caso della tensione di vapore avremo che

i i

ii

y

ir xbayS

i

2

2

2 )(1

s

dopodichè si deve minimizzare l’equazione

Qualora le incertezze sui valori misurati di y (es. sp), sono note, è possibile

ricavare il valore esatto dei pesi wi. Altrimenti il problema è ancora

risolvibile, in quanto noi siamo interessati, più che al valore assoluto di wi,

ad avere un valore relativo che ci permetta di stimare il diverso peso da dare

a ciascun dato, ma che non altera il rapporto tra i pesi dei dati.

Ad esempio, ponendo sp=1, avremo che i pesi wi saranno scalati tutti della

stessa quantità, per cui il rapporto tra i pesi relativi tra i vari dati non cambia.


D

yxwxwywxw

a i i i

iiiiiii

i

ii

2

D

ywxwyxww

b i i i

iiiiiii

i

i

Applicando una procedura simile a quella vista nel caso della regressione lineare

semplice, si ottengono le seguenti relazioni che ci permettono di ricavare la miglior

stima dei coefficienti a e b.

i i i

iiiii xwxwwD

2

2


Dn

xwwi i

iii

wa

2

ss

Dn

wi

iw

b

s

s

2

12

2

2

2

1

2

D

ywxwyxww

ywywww

n

n

i i i

iiiiiii

i

i

i

iiii

i

i

i

i

ws

Qualora non siano noti i singoli valori di syi , per cui i pesi wi sono stati scalati tutti di

una stessa quantità, l’errore standard sui parametri a e b , può essere ricavato

mediante le seguenti relazioni:

i i i

iiiii xwxwwD

2

2

dove

Minimi quadrati lineari pesatiApplicazione della macro Minimi qudrati pesati per il calcolo

della miglior retta, attribuendo a ciascun dato un peso

statistico diverso.kt

eyy

0

ktyy 0lnln

Esempio di funzione Y studiata :

L’introduzione dei pesi comporta una SRR (somma dei quadrati

dei residui) inferiore e anche una incertezza minore sui coefficienti.

Di fatto la funzione studiata è :

2

1

sw

Per calcolare i pesi usiamo quindi l’espressione 2

1

dy

dYw

Essendo Y=ln y, sy =d(lnY)/dy = 1/y e quindi w=y2.

Minimi quadrati lineari pesati

Utilizzazione della MACRO Minimi quadrati pesati

La macro Minimi quadrati pesati permette di eseguire i minimi quadrati

lineari pesati su una funzione polinomiale sia a una sola variabile indipendente

y=a0 + a1x + a2x2 + …., che a più variabili indipendenti

( y = a0 + a1 x1 + a2 x2+ …).

Per poterla utilizzare è necessario eseguire le seguenti operazioni:

kteyy

0

• Caricare in memoria il foglio di lavoro allegato minimipesati.xls

Tale foglio riporta i dati sperimentali relativi a una cinetica che segue la funzione

Minimi quadrati lineari pesatiUso della MACRO Minimi quadrati pesati

0,892061 -0,11422 0,795772 1

0,821173 -0,19702 0,674326 2

0,753583 -0,28292 0,567887 3

0,682304 -0,38228 0,465538 4

0,623862 -0,47183 0,389204 5

0,526976 -0,6406 0,277703 6

0,494243 -0,70473 0,244277 7

0,460279 -0,77592 0,211857 8

0,395703 -0,92709 0,156581 9

0,360977 -1,01894 0,130305 10

0,315967 -1,15212 0,099835 11

0,282725 -1,26328 0,079933 12

0,262755 -1,33653 0,06904 13

0,238862 -1,43187 0,057055 14

0,201951 -1,59973 0,040784 15

0,196217 -1,62853 0,038501 16

0,178643 -1,72237 0,031913 17

0,166647 -1,79187 0,027771 18

0,145914 -1,92474 0,021291 19

0,132065 -2,02446 0,017441 20

y ln y w =y2 x

0,118754 -2,1307 0,014103 21

0,12423 -2,08562 0,015433 22

0,099406 -2,30854 0,009882 23

0,088856 -2,42073 0,007895 24

0,076953 -2,56456 0,005922 25

0,093996 -2,36451 0,008835 26

0,075862 -2,57884 0,005755 27

0,084567 -2,47022 0,007152 28

0,048474 -3,02672 0,00235 29

0,066402 -2,71203 0,004409 30

0,028925 -3,54304 0,000837 31

0,046152 -3,07582 0,00213 32

0,045905 -3,08118 0,002107 33

0,052562 -2,94575 0,002763 34

0,029352 -3,52839 0,000862 35

0,022086 -3,81282 0,000488 36

0,031475 -3,45856 0,000991 37

0,018558 -3,98688 0,000344 38

0,027818 -3,58207 0,000774 39

0,003874 -5,55353 1,5E-05 40

0,0081 -4,81585 6,56E-05 41

0,00994 -4,61121 9,88E-05 43

0,011953 -4,42681 0,000143 44

0,01139 -4,475 0,00013 45

0,006825 -4,98721 4,66E-05 46

0,03104 -3,47247 0,000963 47

y ln y w =y2 x

Uso della MACRO Minimi quadrati pesati (continua)

• Selezionare il comando Minimi quadrati pesati presente all’interno del menù STRUMENTI. Tale comando viene aggiunto automaticamente al menù quando viene caricato un foglio che contiene al suo interno la macro.Comparirà una finestra di dialogo che ci chiede di inserire le seguenti informazioni:

– L’intervallo di celle che contengono i valori della X.

– L’intervallo di celle che contengono i valori della Y

– L’intervallo di celle che contengono i valori calcolati dei pesi W

– In che modo sono disposti i dati (tutti su righe oppure tutti su colonne)

– Se la curva deve passare per l’origine oppure no

Dopo aver inserito le informazioni e aver fatto clic su IMMETTI, comparirà in basso sotto i dati, il risultato del calcolo ovvero i valori ottimizzati dei parametri inclusa la relativa deviazione standard ed inoltre la deviazione standard su Y , sy

OSSERVAZIONE

E’ possibile utilizzare la MACRO per eseguire i calcoli su altri dati, semplicente cancellando il contenuto del foglio ed immettendo i nuovi dati da elaborare.

La voce Minimi quadrati pesati scomparirà automaticamente dal menù STRUMENTI, una volta che abbiamo chiuso il foglio di lavoro minimipesati.xls.

Minimi quadrati pesati:

Elaborazione dati tensione di vaporeLa seguente figura mostra il foglio di lavoro relativo all’elaborazione dei dati con i

minimi quadrati lineari pesati. I pesi sono stati ricavati in maniera analoga al caso

precedente.

Elaborazione dati tensione di vapore (segue)


5

5.2

5.4

5.6

5.8

6

6.2

6.4

6.6

6.8

0.003 0.0031 0.0032 0.0033 0.0034 0.0035

1/T (K)

lnP sper

calc

Grafico ottenuto aggiungendo la linea

ottenuta col calcolo.

Tale linea cade dentro l’area delimitata dalle

barre di errore ottenute tramite sy

Residui

-0.02

-0.015

-0.01

-0.005

0

0.005

0.01

0.015

0.02

0.003 0.0031 0.0032 0.0033 0.0034 0.0035

1/T

res

idu

i

Serie1

Utilizzazione dello strumento RISOLUTORE

(solver) per l’applicazione del metodo dei

minimi quadrati a funzioni diverse da quelle

polinomiali

• Negli esempi visti precedentemente, la funzione che rispondeva meglio ai dati

sperimentali era lineare sia nei coefficienti che nella variabile indipendente X,

ovvero la funzione veniva espressa come una somma di termini ciascuno

moltiplicato solo per un coefficiente

Esempi:

- y=a0+a1x y è lineare nei coefficienti a0 e a1

- y=a0+a1x+a2x2 y è lineare nei coefficienti a0 , a1 e a2

- y= a0 + a1x1 + a2x2 + a3x3 y è lineare nei coefficienti a0 , a1 e a2

- Y=ln y = log a + (log b) x Y è lineare nei coeff. log a e log b

• L’applicazione del metodo dei minimi quadrati a tali funzioni consisteva

semplicemente nel risolvere un sistema di equazioni lineari. Tale sistema

veniva risolto automaticamente con gli strumenti visti precedentemente :

REGR.LIN, AGGIUNGI LINEA DI TENDENZA e REGRESSIONE.

Strumento Risolutore (segue)

• Tuttavia esistono diverse situazioni in cui la y può:

– Non essere più lineare nella variabile X, bensì solo nei coefficienti(es. Eq. Van Deemter : y= Ax+B/x+c)

– Non essere più lineare nei coefficienti (tutti o in parte)(es.

- y = a1 sen( a2x) la y è lineare in a1 ma non in a2

- y = a1 e – k1 t + a2 e–k2 t la y è lineare in a1 e a2 ma non in k1 e k2

- y = a1 e -0.5((x-a2)/a3)^2) + a4 + a5x + a6 x2

• Nel caso in cui la funzione sia lineare nei coefficienti ma non nella variabile X,l’applicazione dei minimi quadrati che rende minima la somma dei quadrati dei residui

SRR = c2 = S ( wi (yi – yicalc))2

si riduce ancora nella risoluzione di un sistema di equazioni lineari, i cui termini però sono diversi da quelli che si ottengono nel caso di funzioni polinomiali. Pertanto la relativa applicazione può essere fatta , in EXCEL, soltanto utilizzando le matrici.

• Nel caso in cui la funzione non sia più lineare nei coefficienti, la minimizzazione di SRR rispetto ai coefficienti non può più essere risolta con un sistema di equazioni lineari, bensì con dei metodi matematici iterativi.

• Lo strumento RISOLUTORE di Excel ci permette , con semplicità, di ricavare i migliori coefficienti di una funzione che non è risolvibile con i metodi standard visti precedentemente.


• La funzione c2 dipende dai coefficienti a1 , a2 , a3, ecc. Nel caso di due

coefficienti, la rappresentazione grafica della dipendenza di c2 da a1 e a2 ,

può essere descritta da una superficie a 3 dimensioni, come mostra la

seguente figura:

• Le coordinate del punto di minimo di tale superficie corrispondono ai valori dei

coefficienti che rendono minima c2 , ovvero quelli che permettono di rappresentare

, al meglio, i dati sperimentali.


• In pratica, gli algoritmi usati per risolvere il metodo dei minimi quadrati applicato a funzioni non lineari, consiste nell’eseguire i seguenti passaggi:

– Si parte con una stima iniziale dei coefficienti e si calcola c2 .

– Si variano i coefficienti sino a che c2 è minimo.

OSSERVAZIONINell’applicare i suddetti passaggi può verificarsi che il minimo trovato sia un minimo relativo e non assoluto. (La superficie multidimensionale riportata nella precedente figura può avere diversi minimi relativi, di cui solo uno è quello assoluto.)Per essere sicuri di non arrivare a un minimo locale, una volta raggiunto il primo valore più basso di c2 , conviene modificare di poco i valori di uno dei coefficienti e provare a vedere se c2

diventa ancora più piccolo.In altri casi conviene dapprima ottimizzare c2 intervenendo solo su alcuni parametri mentre si mantengono fissi gli altri ; dopodichè si ottimizza rispetto a tutti i parametri . L’individuazione del minimo assoluto può richiedere più o meno tempo, a seconda della stima iniziale dei parametri.


• Esistono diversi algoritmi matematici più o meno complessi che permettono di

applicare i minimi quadrati a funzioni non lineari. Tali algoritmi sono formati

da una serie di istruzioni che devono essere eseguite in modo iterativo, per cui

sono di difficile applicazione all’interno di un foglietto elettronico, a meno che

non vengano sviluppate sotto forma di MACRO.

• Lo strumento RISOLUTORE di Excel implementa uno dei suddetti algoritmi

matematici e permette di applicare il metodo dei minimi quadrati, in modo

estremamente semplice.

Ottimizzazione dei parametri dell’equazione di

Van Deemter con lo strumento RISOLUTORE

• L’equazione di Van Deemter permette di correlare l’altezza di un piatto teorico di una

colonna gascromatografica (e quindi l’efficienza nella risoluzione dei picchi) con la

velocità di flusso della fase mobile gassosa :

Cx

BxAy

dove

y è l’altezza del piatto teorico in mm

X è la velocità di flusso della fase gassosa in ml/min

A, B, C sono costanti da ottimizzare col calcolo.

Eq. Van Deemter (segue)

La seguente tabella riporta i dati sperimentali relativi a un campione di 2-butanone, usando

l’elio come fase gassosa:

3,4000 9,59

7,1 5,29

16,1 3,63

20 3,42

23,1 3,46

34,4 3,06

40 3,25

44,7 3,31

65,9 3,5

78,9 3,86

96,8 4,24

115,4 4,62

120 4,67

Velocità

(ml/min)

Altezza piatto

(mm)

Eq. Van Deemter in EXCEL (segue)


Per poter utilizzare lo strumento RISOLUTORE, sono necessarie le seguenti

operazioni:

• Porre in una o più celle la stima iniziale dei parametri

• Inserire accanto alle colonne contenenti i valori sperimentali di x e di y due

nuove colonne di cui una contenente la y calcolata e l’altra il quadrato dei

residui

• Porre in una cella il risultato della somma dei quadrati dei residui

• Invocare lo strumento RISOLUTORE (STRUMENTI -> RISOLUTORE)

Comparirà la seguente finestra di dialogo:


• Nella casella Imposta cella ci va messo il riferimento alla cella contenente la somma

dei quadrati dei residui

•Visto che vogliamo rendere minima tale somma, scegliamo l’opzione Uguale a Min

•La minimizzazione va fatta rispetto ai parametri (A, B , C) contenuti nelle relative celle.

Pertanto nel campo Cambiando le celle ci va un riferimento alle celle contenenti i

parametri suddetti.

•In altri termini abbiamo indicato al programma di rendere minima la somma dei

quadrati dei residui variando i valori dei parametri A ,B e C.

E’ sufficiente fare clic su RISOLVI per ottenere i valori ottimali dei parametri.


• Una volta eseguito il calcolo, conviene visualizzare anche il grafico dei residui

e quello contenente sia i dati sperimentali che quelli calcolati.

• Se vogliamo avere anche una stima delle incertezze sui parametri, possiamo

usare la MACRO Solver Aid disponibile tramite l’esempio allegato

solveraid.xls .

Una volta caricato il foglio solveraid.xls, selezioniamo la voce Solver Aid

presente all’interno del menù STRUMENTI e immettiamo le informazioni che

ci vengono richieste dal programma. Alla fine sul foglio di lavoro, accanto ai

parametri verranno aggiunte le deviazioni standard associate a ciascun

parametro; inoltre verrà visualizzata la deviazione standard sy . accanto alla

cella SSR (somma dei quadrati dei residui).

APPLICAZIONE DELLO STRUMENTO RISOLUTORE

PER IL CALCOLO DEI MINIMI QUADRATI DI UNA

FUNZIONE NON LINEARE SIA NEI COEFFICIENTI

CHE NELLA VARIABILE X

Vogliamo ora applicare lo strumento RISOLUTORE per trovare la miglior stima

dei parametri relativamente alla seguente funzione :

tktk eaeay 21

21

Tale funzione può, ad esempio, rappresentare il decadimento radioattivo

contemporaneo di due sostanze, di cui una ha un tempo di dimezzamento molto

più piccolo rispetto a quello dell’altra sostanza (k2 << k1).

Decadimento radioattivo di due sostanze (segue)

La colonna yspe è stata ricavata utilizzando per i coefficienti a1 , k1, a2 e k2 i valori

riportati nella colonna coeff. Esatti. La colonna coeff. iniziali contiene invece una stima

iniziale dei parametri.

Prima di applicare lo strumento RISOLUTORE, aggiungiamo anche le colonne relative

ai valori calcolati di y (yfit) e ai quadrati dei residui ed infine la cella contenente la

somma dei quadrati dei residui.

Decadimento radioattivo di due sostanze

(segue)

• Dopo aver attribuito ai coefficienti

dei valori pari a 0, applichiamo lo

strumento RISOLUTORE. Al

termine del calcolo comparirà la

seguente finestra che ci indica che

è stata trovata una prima soluzione.

• Se facciamo un grafico contenente

contemporaneamente sia i dati

sperimentali di y che quelli calcolati

(yfit) in funzione di x, ovvero del tempo

t, osserviamo che il fit è buono solo nel

primo tratto del grafico.N.B. Abbiamo assunto per l’asse delle y una scala

logaritmica

Decadimento radioattivo di due sostanze (segue)• La prima parte del grafico è relativa al tempo di decadimento della prima

sostanza, mentre il secondo tratto a quello della seconda sostanza. Essendo k2

minore di k1 , il contributo alla somma dei quadrati dei residui del primo tratto è

maggiore di quello relativo al secondo tratto, per cui il programma tende a

fittare meglio la prima parte del grafico.

• In questi casi conviene eseguire il fitting ottimizzando dapprima solo rispetto ai

parametri relativi al secondo tratto, poi ai parametri relativi al primo tratto ed

infine rispetto a tutti e 4 i parametri. Naturalmente nella somma dei quadrati dei

residui vanno conteggiati solo i dati relativi ai tratti considerati.

10 stadio :

E’ stato eseguito il fitting

soltanto rispetto alla seconda

parte del grafico, ricavando

dei valori solo per a2 e k2 e

lasciando nulli a1 e k1.

Decadimento radioattivo di due sostanze

(segue)

20 stadio :


soltanto rispetto alla prima

parte del grafico, ricavando

dei valori solo per a1 e k1 e

lasciando invariati i valori

ricavati precedentemente di

a1 e k1.

30 stadio :


rispetto a tutti e quattro i

parametri, i cui valori sono

stati ricavati

precedentemente.

Decadimento radioattivo di due sostanze (segue)

• La seguente tabella riporta i valori dei coefficienti ottenuti dopo il terzo stadio

con la relativa incertezza, ricavata mediante la MACRO Solver Aid.

I risultati ottenuti sono ottimi.

coeff. Esatti coeff iniziali incertezze

a1 9 8,999943784 5,845E-06

k1 2 2,000011418 1,57037E-06

a2 0,02 0,020054365 6,15801E-06

k2 0,5 0,500834105 9,47348E-05

Determinazione delle costanti di acidità

mediante titolazione per via potenziometrica

Un altro esempio di applicazione del metodo RISOLUTORE consiste nel ricavare le

costanti di acidità di un acido poliprotico (es. H3PO4) , elaborando con i minimi

quadrati non lineari i dati ottenuti sperimentalmente titolando l’acido con una base

forte (es. NaOH).

Indichiamo con Ca , Va la concentrazione dell’acido e il relativo volume, mentre

indichiamo con Cb e Vb la concentrazione della base e il volume aggiunto durante la

titolazione. L’espressione che mostra come varia Vb in funzione del pH è la seguente

(vedi libro De Levie , Excel in Analytical Chemistry p.152):

b

aab

C

CVV

)32( 012 aaa

Determinazione delle costanti di acidità mediante

titolazione per via potenziometrica (segue)

321211

23

3210

211

1

2

2

][][][

][

][

][]][

aaaaaa

aaa

aa

a

kkkkkHkHHD

e

D

kkk

D

kkH

D

kH

e

OHH

a

a

a

dove

Determinazione delle costanti di acidità mediante

titolazione per via potenziometrica (segue)

La seguente figura mostra i dati ottenuti sperimentalmente in laboratorio titolando

50 ml di H3PO4 0.1M con NaOH 0.1M e il relativo grafico.

Titolazione di H3PO4 con NaOH

0

2

4

6

8

10

12

14

0 50 100 150 200 250 300

Vb (ml)

pH spe

Determinazione delle costanti di acidità mediante titolazione per via

potenziometrica (segue)

Prepariamo ora il seguente foglio per applicare lo strumento RISOLUTORE ai dati, in

modo da determinare ka1, ka2 e ka3 . Inoltre rappresentiamo sullo stesso grafico sia i

valori sperimentali di Vb, che quelli calcolati attribuendo una stima iniziale ai parametri

ka1, ka2 e ka3 . Naturalmente le due curve non saranno sovrapposte.


0

2

4

6

8

10

12

14

0 50 100 150 200 250 300

Vb (ml)

pH spe

calc

Determinazione delle costanti di acidità mediante titolazione

per via potenziometrica (segue)Proviamo ora ad applicare lo strumento risolutore cercando di minimizzare SSR,

variando i parametri ka1, ka2 e ka3 . Otterremo la seguente situazione.

k1= 0.1

k2=0.001

k3=5.74e-13

SSR=46173.47


0

2

4

6

8

10

12

14

0 50 100 150 200 250 300

Vb (ml)

pH spe

calc

Purtroppo il programma non riesce ad ottimizzare i dati. Il motivo di tutto ciò

dipende dal fatto che i parametri da ottimizzare hanno ordini di grandezza molto

diversi e l’algoritmo usato incrementa i parametri basandosi sul coefficiente che ha

il valore più grande, per cui non riesce a trovare il minimo , in quanto sovrastima

l’incremento da dare ai parametri.

Determinazione delle costanti di acidità mediante titolazione per via

potenziometrica (segue)

E’ possibile aggirare l’ostacolo se, come parametri da ottimizzare, utilizziamo

pka1, pka2 e pka3 invece di ka1, ka2 e ka3; infatti tali parametri assumono, in questo

modo lo stesso ordine di grandezza e lo strumento risolutore riesce ad ottimizzare

il fit in maniera egregia, come mostrano le seguenti figure, in cui abbiamo usato

la macro “Solver Aid” per ricavare le incertezze sui parametri ska1, ska2 ,ska3 e la

deviazione standard sy


0

2

4

6

8

10

12

14

0 50 100 150 200 250 300

Vb (ml)

pH spe

calc

Determinazione della curva di calibrazione di uno

spettrometro atomico a fiamma e calcolo della

concentrazione di un campione incognito mediante

lo strumento RICERCA OBIETTIVO• La seguente tabella riporta i dati sperimentali ottenuti utilizzando uno spettrometro

atomico a fiamma ed eseguendo delle misure su dei campioni di Na a concentrazione

nota

I (intensità relativa) conc.(p.p.m.)

0 0

62 5

115 10

160 15

200 20

233 25

Ricerca Obiettivo (segue)

• L’equazione che permette di ottenere una curva di calibrazione ottimale è la

seguente:

I = a + b * conc + c * conc2 + d * conc3

• Per ricavare i coefficienti a , b, c e d possiamo utilizzare lo strumento

REGRESSIONE di EXCEL, dopo aver preparato un foglio di lavoro simile a

quello mostrato nella seguente figura.

Ricerca Obiettivo (segue)• Una volta ricavata la curva di calibrazione è possibile eseguire delle misure

spettrofotometriche su campioni a concentrazione incognita e ricavare la relativa concentrazione utilizzando la suddetta curva di taratura.Se, ad esempio, l’intensità relativa sperimentale è I=80, dovrà essere che

a + b * conc + c * conc2 + d * conc3 – 80 =0 (1)

• In altri termini dobbiamo ricavare quel valore di conc. che annulla la suddetta equazione. A tale scopo ci viene in aiuto lo strumento di Excel RICERCA OBIETTIVO del menù STRUMENTI, che permette di variare il valore contenuto in una cella sino a che il risultato di una formula contenuta in un’altra cella, si avvicini il più possibile a un valore prestabilito. Se il valore contenuto in una cella corrisponde a una stima iniziale della concentrazione incognita e una seconda cella contiene la formula a + b * conc + c * conc2 + d * conc3 – 80 , allora l’applicazione dello strumento RICERCA OBIETTIVO ci permette di ricavare la concentrazione ottimale , variando il valore iniziale sino a soddisfare l’equazione (1).Nel foglio di lavoro mostrato nella precedente figura , la cella B15 contiene il valore sperimentale di I, mentre la cella B16 contiene , all’inizio , la stima iniziale di conc. La cella B17 contiene la formula sopra menzionata. Una volta immessi tali dati sarà sufficiente invocare lo strumento RICERCA OBIETTIVO. Comparirà la finestra di dialogo mostrata nella seguente figura:


Dopo aver fatto clic su OK , otterremo una nuova finestra di dialogo, che ci indicherà il valore trovato per la

formula, che, nel nostro caso, dovrà essere il più possibile, vicino a 0.

La cella B16, che conteneva la stima iniziale della concentrazione, ora riporta il valore ottimale della concentrazione

incognita del campione. Se il valore ricavato per la formula non dovesse essere uguale al valore prestabilito (nel

nostro caso 0), allora si può istruire Excel ad eseguire più iterazioni di calcolo, precisando anche quale deve essere

lo scarto massimo consentito tra il valore calcolato della formula e quello ricercato. A tale scopo è sufficiente

selezionare la voce OPZIONI del menù STRUMENTI e intervenire sui parametri N. MASSIMO ITERAZIONI e

SCARTO CONSENTITO dell’opzione CALCOLO.

•Il parametro imposta cella dovrà fare riferimento alla cella

contenente la formula

•Il parametro al valore dovrà contenere il valore che dovrà

assumere la formula al termine del calcolo

•Il parametro cambiando la cella dovrà fare riferimento alla

cella contenente la stima iniziale della variabile X (o conc.).

Grafico di una serie di dati sperimentali in EXCEL · grafico può essere ottenuto semplicemente...

Documents

Transcript of Grafico di una serie di dati sperimentali in EXCEL · grafico può essere ottenuto semplicemente...