Trattamento e Analisi statistica dei dati sperimentali

(1)

Trattamento e Analisi statistica dei dati sperimentali

Modulo I: Il foglio elettronico

L5: Istruzioni e funzionalità avanzate II

Prof. Carlo Meneghini

dip. di Scienze Università Roma Tre e-mail: carlo.meneghini@uniroma3.it

(2)

Istruzioni in forma di matrice

In EXCEL (come in CALC-LO / OO) è possibile effettuare calcoli in forma matriciale usando funzioni di due tipi:

1. istruzioni (formule) che operano su matrici fornendo un risultato unico

2. istruzioni (formule) che operano su matrici fornendo una matrice di risultati.

(3)

Esempi^:

calcolo della somma dei prodotti ∑ xiNi

4

D

6

E

La formula in forma di matrice si inserisce digitando

contemporaneamente CTRL+SHFT+RETURN

invece di ENTER

(4)

Esempi^:

calcolo della media pesata

∑

i i i

N N x

4

D

6

E

La formula in forma di matrice si inserisce digitando

contemporaneamente CTRL+SHFT+RETURN

invece di ENTER

(5)

I risultati di alcune istruzioni complesse forniscono più di un valore, devono quindi essere allocati in una matrice.

Vediamo, ad esempio, le istruzioni per il calcolo della regressione lineare o di una tabella di frequenze.

REGR.LIN: calcola i parametri della regressione lineare, le barre di errore e le statistiche aggiuntive

FREQUENZA: In forma di matrice calcola le frequenze assolute per classi di dati ordinali. In forma «normale»

calcola le frequenze assolute integrate (distribuzione)

(6)

=INDICE(MATRICE;riga;col)

A2:D12 = matrice dati

nota: gli indici riga e colonna sono valori interi (non lettere) a partire da 1,1 nell’angolo in alto a sinistra della matrice

Lettura di una matrice

(7)

Problema:

calcolare i parametri (m, c) e le

statistiche (incertezza, etc...) per la retta di regressione

c mx

y = +

a. la retta di regressione sul grafico

non fornisce valori utilizzabili da altre celle

non fornisce le incertezze

b. le funzioni PENDENZA e INTERCETTA sono poco versatili:

non consentono di imporre il passaggio per l’origine

non riportano le incertezze sui parametri

(8)

Problema:

c mx

y = +

REGR.LIN(y_nota; [x_nota]; [cost]; [stat])

m σm

c σc

N_lib R²

Coefficiente di determinazione

σy

La funzione REGR.LIN fornisce

dettagli importanti quali incertezze e informazioni statistiche aggiuntive.

(9)

Funzioni in forma di matrice

Problema:

c mx

y = +

REGR.LIN(y_nota; [x_nota]; [cost]; [stat])

0 c

1 cost

0 c

0 cost

⇒ ≠

=

⇒ =

=

e regression dela

e statistich le

calcola

0 stat

e regression dela

e statistich le

calcola

1 stat

⇒ non

=

⇒

=

(10)

=INDICE(regr.lin(y;x;1;1);1;1) => m coeff. angolare

=INDICE(regr.lin(y;x;1;1);2;1) => σ_m incertezza su m

=INDICE(regr.lin(y;x;1;1);1;2) => c intercetta

=INDICE(regr.lin(y;x;1;1);1;2) => σ_C incertezza su c

= INDICE...

m σm

c σc

N_lib R²

Coefficiente di determinazione

σy

La funzione REGR.LIN alloca le informazioni un una

matrice interna 5_rx2_c. La funzione INDICE legge il dato contenuto in una determinata posizione di memoria

(11)

Formula in forma di matrice

Nota: nella barra della formula questa appare tra parentesi { } indicando che il calcolo è effettuato per una matrice di celle.

Nota: per modificare un formula in forma di matrice digitare sempre CRTL+SHFT+INVIO oppure premere ESC per uscire senza salvare.

1. seleziona una regione 5x2 celle

2. inserisci la formula, in questo caso:

=REGR.LIN(P2:P26;O2:O26;1;1) 2. premi insieme: CRTL+SHFT+INVIO

(12)

Funzioni TESTO

= TESTO (Valore;formato)

Trasforma un valore numerico (Valore) costante o contenuto in una cella, in una stringa di testo con un numero di cifre significative

stabilite dal Formato

(13)

Funzioni TESTO

= CONCATENA(Str1; Srt2;...) Concatena stringhe di testo

0 <0.0 10 0.0-10.0 20 10.0-20.0 30 20.0-30.0 40 30.0-40.0 50 40.0-50.0 60 50.0-60.0 70 60.0-70.0

=CONCATENA("<";TESTO(H3;"0.0"))

H

=CONCATENA(TESTO(H7;"0.0");"-";TESTO(H8;"0.0"))

Nota: può essere utilizzato per definire etichette di istogrammi

(14)

Indirizzamento indiretto

Problema: calcolare la media di un gruppo di dati definendo di volta in volta gli estremi

=MEDIA( INDIRETTO(B13):INDIRETTO(B14) )

(15)

Indirizzamento indiretto

=INDIRETTO(RIF)

=INDIRETTO(D9) #RIF!

=INDIRETTO("D9") =0.93611

Nota: RIF deve essere una stringa di testo o

indirizzare ad una cella che contenga una stringa di testo con il riferimento da usare

(16)

Controllo di flusso

(17)

Programmazione di Fogli

Utilità per fogli da far gestire agli utenti

1. commenti

2. Convalida dati

3. protezione delle celle

(18)

Nota: il tasto mostra tutti i commenti (Revisione) permette di visualizzare tutti i

commenti del foglio.

Commenti

E’ utile inserire commenti e istruzioni soprattutto se i fogli devono essere usati da altri.

(19)

Strumenti: Convalida dati

Consentono di controllare che i dati inseriti siano coerenti con l’utilizzo delle celle.

E’ possibile definire il tipo di dato richiesto, il messaggio di input e un eventuale messaggio di errore.

(20)

(21)

(22)

Protezione celle e fogli

1) definisci le regole per le celle protette

2) Operazioni consentite

Nota: di default le celle non possono essere modificate se la cartella

è protetta.

(23)

Protezione celle e fogli

3) Se la cella è protetta un messaggio di errore avvisa che è necessario inserire una

password per sboloccarle.

4) è possibile sbloccare i fogli usando la password

preimpostata.

Nota: può essere utile usare la protezione per evitare modifiche involontarie ma sarebbe conveniente lasciare la password

evidente per consentire modifiche necessarie.