Grafico di una serie di dati sperimentali in EXCEL
1. 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
1.2
1
[H3PO4]
[H2PO4-]
[HPO4--]
[PO43-]
pH
0.8
0.6
0.4
0.2
0
0
5
10
15
concentrazioni
20
25
Grafico di dati disposti su righe non contigue
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:
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.
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.
Grafico di dati disposti su righe non contigue (segue)
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.
Grafico di dati disposti su righe non contigue (segue)
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)
lnP
Misura tensione di vapore di un liquido
6.8
6.6
6.4
6.2
6
5.8
5.6
5.4
5.2
5
0.003
Serie1
0.0031
0.0032
0.0033
1/T (K)
0.0034
0.0035
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)
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.
Inseriamo qui il nome da dare alla nuova serie
di dati
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
lnP
Misura tensione di vapore di un liquido
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.
6.8
6.6
6.4
6.2
6
5.8
5.6
5.4
5.2
5
0.003
Serie1
calc
0.0031
0.0032
0.0033
1/T (K)
0.0034
0.0035
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 - .
Misura tensione di vapore di un liquido
6.8
6.6
6.4
lnP
6.2
6
Serie1
5.8
5.6
5.4
Fai clic qui per definire le celle che contengono
gli errori
5.2
5
0.003
0.0031
0.0032
0.0033
1/T (K)
0.0034
0.0035
Inserimento di un file di dati in EXCEL
Molto 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 REGRESSIONE
La 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 :
^
2
w

(
y

y
)
i1 i i i  min imo
n
ANALISI DI REGRESSIONE(segue)
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
^
yi  y i
^
è la differenza tra i valori sperimentali yi e quelli calcolati
yi
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:
y  a  bx  
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
  y  a  bx
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
Sr    i2   ( yi  a  b  xi )2
i
i
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:
Sr
 2   ( yi  a  b  xi )
a
i
Sr
 2  [( yi  a  b  xi )  xi ]
b
i
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.
REGRESSIONE LINEARE (semplice)(segue)
Uguagliando a zero queste derivate, troviamo quei valori che rendono minima Sr: le
equazioni vengono allora espresse come
0   yi   a   b  xi
0   yi  xi   a  xi   b  xi2
Ora , tenendo conto che
(1)
a  n  a
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:
n  a   xi b   yi
x a  x
i
2
i
 b   xi  yi
Queste equazioni vengono dette EQUAZIONI NORMALI.
REGRESSIONE LINEARE (semplice)(segue)
Risolvendo le equazioni rispetto a b, otteniamo
b
n   xi  yi   xi   yi
n   xi2  (  xi )2
Questo risultato, utilizzato insieme alla (1), dà
a  y b x
dove y e x
(2)
sono, rispettivamente, i valori medi di y e di x.
_
_
L’equazione (2) ci dice che la retta migliore passa per il punto ( x, y )
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 :
s
1
  ( yi  a  b  xi )2
n2
Più è piccolo il suo valore è più vicina è la retta calcolata ai dati sperimentali.
REGRESSIONE LINEARE :stima delle incertezze
sui parametri a e b (segue)
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):

sa 

s    xi2 

i
1
2

1
2 2


 
2
n

x

x

  i

i 
 i  
 i
sb 
s n
1
2



2
n   xi    xi 
 i 
 i
1
2 2



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:
b  b  t X %,n  s b
a  a  t X %,n  s a
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.
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
x0 
y0  a
b
REGRESSIONE LINEARE :stima di x0 per un dato y0
(segue)
Le incertezze ottenute sui parametri a e b faranno sì che vi sia anche un’incertezza
sul valore calcolato di x0, indicata con s x0
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
2
_


 y0  y 
s
1 1

s x0  
  
_ 2
b m n


2
b    xi  x 

i 
(3)
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 x e y
sono i valori medi di x e di y.
REGRESSIONE LINEARE :stima di x0 per un dato y0
(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:
x0  x0  t X %,n  s x0
(4)
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.
REGRESSIONE LINEARE :stima di y0 per un dato x0
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
y0  a  b  x0
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
sy
0

_ 2



n   x0  x 
 1


 s 

2
 n n  x 2   x 
i i  i i 


La miglior stima di y0 sarà data da







y0  y0  t X %,n  s y0
(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
13
9
11
14
6
4
12
7
5
6,95
7,58
8,81
8,33
9,96
7,24
4,26
10,84
4,82
5,68
8
13
9
11
14
6
4
12
7
5
8,14
8,74
8,77
9,26
8,1
6,13
3,1
9,13
7,26
4,74
Serie N.1
12
10
8
8
13
9
11
14
6
4
12
7
5
6,77
12,74
7,11
7,81
8,84
6,08
5,39
8,15
6,42
5,73
y
Serie N.1
12
10
8
6
4
2
0
y
0
5
10
I dati relativi alla serie N.1 possono essere
rappresentati da una retta
15
x
y
Serie N.2
10
9
8
7
6
5
4
3
2
1
0
y
0
2
4
6
8
10
12
14
I dati relativi alla serie N.2 possono essere
rappresentati da una curva
16
x
Serie N.3
14
12
10
8
y
y
6
4
2
0
0
5
10
x
15
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
a0
a1
sigmay
sigma0
sigma1
3
0,5
1,237
1,125
0,118
serie 2
3
0,5
1,237
1,125
0,118
serie 3
3
0,5
1,236
1,124
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 0
-1,000000
-1,500000
-2,000000
-2,500000
I dati sono sparpagliati attorno all’asse X
serie n. 1 residui
5
10
15
residui
1,500000
1,000000
0,500000
0,000000
-0,500000 0
5
10
15
serie n. 1 residui
-1,000000
I dati non sono sparpagliati lungo l’asse delle
X bensì assumono un andamento particolare
(parabolico)
-1,500000
-2,000000
-2,500000
residui
3,500000
3,000000
2,500000
2,000000
1,500000
1,000000
0,500000
0,000000
-0,500000 0
-1,000000
-1,500000
serie n. 1 residui
5
10
15
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
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
b
sb
R2
Statistica F
La somma della regressione dei
quadrati
0,500091
0,117906
0,666542
17,98994
27,51
a
3,000091
1,124747
1,236603
9
13,76269
sa
s
Gradi di libertà
La somma residua dei quadrati
Funzione REGR.LIN: grafico dei residui
5. 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:
PARAMETRI DI INPUT
• 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
Strumento REGRESSIONE (segue)
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à 2.1
(u.a.)
5.0
9.0
12.6
17.3
21.0
24.7
Concen- 0
trazione
(pg/ml)
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 .
Strumento REGRESSIONE (segue)
RISULTATI OTTENUTI
Strumento REGRESSIONE (segue)
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
Strumento REGRESSIONE (segue)
N.B. Si poteva ottenere lo stesso risultato, applicando le formule
a  a  t95%,5  s a
b  b  t95%,5  s b
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 y0  y0  t X %,n  s y0
La seguente figura riporta il grafico ottenuto.
Strumento REGRESSIONE (segue)
Strumento REGRESSIONE (segue)
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
x0  x0  t X %,n  s x0
La seguente figura riassume i risultati dei calcoli relativi all’esercizio.
Strumento REGRESSIONE (segue)
ELABORAZIONE DATI
Strumento REGRESSIONE (segue)
La seguente figura riporta, infine le bande di fiducia per i vari valori di x.
Strumento REGRESSIONE (segue)
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.
Strumento REGRESSIONE (segue)
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
Al   l  t  cl
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=bx
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+bx
Strumento REGRESSIONE (segue)
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
Strumento REGRESSIONE (segue)
Strumento REGRESSIONE (segue)
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 x
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
_
_
inf  x  t X %,n  s _
x
_
sup  x  t X %,n  s _
x
Strumento REGRESSIONE (segue)
Curva di distribuzione di Student
La seguente figura mostra la corrispondente curva di distribuzione normale di Student
_
in funzione del parametro
t
x m
s n
Dove s è la deviazione standard e n è il n. di repliche.
Strumento REGRESSIONE (segue)
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
2
a
2

X%
1
100
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.
Strumento REGRESSIONE (segue)
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
x%
a  1
100
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
1 x%
a  1 
2 100
Strumento REGRESSIONE (segue)
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
t
sa
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à:
-
t  tcritico
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
-
t  tcritico
Siamo al di fuori dell’area, per cui vi è una probabilità di 100-X% (5%
se X%=95) che a sia diversa da 0.
Strumento REGRESSIONE (segue)
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
Strumento REGRESSIONE (segue)
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
^
 wi  ( yi  yi ) 2  min imo
i 1
^
in cui
yi
^
è uguale a
y i  a  bxi  cx i2  dxi3  ....  mxim
dove m è il grado del polinomio.
REGRESSIONE POLINOMIALE(segue)
Uguagliando a 0 le equazioni e riordinando, otteniamo il seguente sistema di equazioni:
a  n  b   xi  c   xi2   yi
a   xi  b   xi2  c   xi3   xi  yi
a   xi2  b   xi3  c   xi4   xi2  yi
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 Excel
Proviamo 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.
REGRESSIONE POLINOMIALE 2° grado in Excel
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 x 2.
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.( x 2, x3, ecc.).
REGRESSIONE POLINOMIALE 2° grado in Excel
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
ln p  a 
H vap
R T
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)
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
 y 
1
s y     s p   s p
p
 p 
i
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
wi 
1
sy 2
i
REGRESSIONE LINEARE PESATA (segue)
Nel caso della tensione di vapore avremo che
p2
wi 
s p2
dopodichè si deve minimizzare l’equazione
Sr    i2  
i
i
1
sy
2
(
y

a

b

x
)
i
i
2
i
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.
REGRESSIONE LINEARE PESATA (segue)
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.
a
w  x w  y  w  x w  x  y
i
i
2
i
i
i
i
i
i
D
i
i
i
i
i
b
w w  x  y  w  x w  y
i
i
i
i
i
i
i


D   wi   wi  xi2    wi  xi 
i
i
 i

i
D
2
i
i
i
i
REGRESSIONE LINEARE PESATA (segue)
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
mediante le seguenti relazioni:
  wi   wi  xi2 


i
sa  sw  i

nD




, può essere ricavato
sb 
s w  wi
i
nD
dove


 n  
  wi   wi  xi  yi   wi  xi   wi  yi 
2

 


n2 
i
i
i

sw  
  wi   wi  yi2    wi  yi    i

w
D
 i

i i  i

1
2


D   wi   wi  xi2    wi  xi 
i
i
 i

2
2






1
2
Minimi quadrati lineari pesati
Applicazione della macro Minimi qudrati pesati per il calcolo
della miglior retta, attribuendo a ciascun dato un peso
statistico diverso.
Esempio di funzione Y studiata :
L’introduzione dei pesi w 
1
s2
y  y0e
 kt
comporta una SRR (somma dei quadrati
dei residui) inferiore e anche una incertezza minore sui coefficienti.
Di fatto la funzione studiata è :
ln y  ln y0  kt
Per calcolare i pesi usiamo quindi l’espressione w 
1
 dY 


 dy 
Essendo Y=ln y, sy =d(lnY)/dy = 1/y e quindi w=y2.
2
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:
• Caricare in memoria il foglio di lavoro allegato minimipesati.xls
Tale foglio riporta i dati sperimentali relativi a una cinetica che segue la funzione
y  y0e
 kt
Minimi quadrati lineari pesati
Uso della MACRO Minimi quadrati pesati
y
ln y
w =y 2
x
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 =y 2
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
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 vapore
La 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)
Misura tensione di vapore di un liquido
Residui
6.6
0.02
6.4
0.015
6.2
0.01
6
0.005
sper
calc
5.8
5.6
residui
lnP
6.8
0
-0.0050.003
Serie1
0.0031
0.0032
0.0033
-0.01
5.4
-0.015
5.2
-0.02
5
0.003
1/T
0.0031
0.0032
0.0033
0.0034
0.0035
1/T (K)
Grafico ottenuto aggiungendo la linea
ottenuta col calcolo.
Tale linea cade dentro l’area delimitata dalle
barre di errore ottenute tramite sy
0.0034
0.0035
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.
Strumento Risolutore (segue)
•
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.
Strumento Risolutore (segue)
• 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.
OSSERVAZIONI
Nell’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.
Strumento Risolutore (segue)
•
•
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 :
B
y  A x   C
x
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:
Velocità
(ml/min)
Altezza piatto
(mm)
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
Eq. Van Deemter in EXCEL (segue)
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:
Eq. Van Deemter in EXCEL (segue)
• 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.
Eq. Van Deemter in EXCEL (segue)
•
•
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 :
y  a1e k1t  a2e k2t
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 k 2
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 :
E’ stato eseguito il fitting
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 :
E’ stato eseguito il fitting
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):
Ca  (a2  2a1  3a0 )  
Vb  Va 
Cb  
Determinazione delle costanti di acidità mediante
titolazione per via potenziometrica (segue)
dove
  [ H ] ]  [OH  ]
e
[ H  ]2  ka1
a2 
D

[ H ]  k a1  k a 2
a1 
D
k k k
a 0  a1 a 2 a 3
D
e
D  [ H  ]3  [ H  ]2  ka1  [ H  ]  ka1  ka 2  ka1  ka 2  ka 3
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
14
12
pH
10
8
spe
6
4
2
0
0
50
100
150
Vb (ml)
200
250
300
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.
Titolazione di H3PO4 con NaOH
14
12
pH
10
8
spe
calc
6
4
2
0
0
50
100
150
Vb (ml)
200
250
300
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.
Titolazione di H3PO4 con NaOH
14
12
10
pH
k1= 0.1
k2=0.001
k3=5.74e-13
8
spe
calc
6
4
SSR=46173.47
2
0
0
50
100
150
200
250
300
Vb (ml)
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
Titolazione di H3PO4 con NaOH
14
12
10
8
pH
spe
calc
6
4
2
0
0
50
100
150
Vb (ml)
200
250
300
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)
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:
Ricerca Obiettivo (segue)
•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.).
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.