Appunti per una guida all’uso di Excel. Prof. Gianna Dosi

1

Alcune istruzioni su Excel (XLS per gli amici)

Diamo per buono che siate in grado di scrivere una tabella come quella sottostante. Ricordo solo che XLS non riconosce come numeri scritture del tipo 58±1: non è riconosciuto come il valore 58 con un Δ (un’incertezza) che vale 1. Le incertezze di risultati di misure vanno inserite in una cella diversa (magari adiacente) da quella della grandezza. Dobbiamo cercare la legge che lega l’intensità luminosa I alla distanza d dalla sorgente.

Procediamo al solito modo: correlazione negativa, ipotizziamo una proporzionalità inversa? Uhm, vediamo: quando la I dimezza (300 lux – 150 lux) la distanza è ben lungi dal raddoppiarsi. Non va bene neanche una dipendenza lineare

col k negativo: mentre I decresce con regolarità di 50 in 50 lux la distanza non cresce con regolarità: prima di 2, poi di 4, poi 8 fino a 16! Meglio allora

disegnare un grafico cartesiano con Excel (2010)

Quello che appare così:

Intensità distanza (lux±5) (cm±1) 300 24 250 26 200 30 150 34 100 42 50 58

Appunti per una guida all’uso di Excel. Prof. Gianna Dosi

2

Andiamo su Inserisci

Attivate Grafico a dispersione

Appunti per una guida all’uso di Excel. Prof. Gianna Dosi

3

E fra le proposte scegliete quella senza segmenti, curve o altro: vogliamo solo i punti sperimentali:

Cliccate e poi attivate Progettazione

Qualcuno dirà che ci sono modi più rapidi per fare grafici (è vero, ma a volte non viene fuori quello che vogliamo e allora o vi esercitate oppure seguiamo la nostra procedura classica: come se dovessimo fare il grafico noi a mano).

Appunti per una guida all’uso di Excel. Prof. Gianna Dosi

4

Cliccate su Seleziona dati e poi su Aggiungi

La finestra che si apre è chiarissima: cosa vuoi sull’asse x? E sull’asse y? Dunque sull’asse x la distanza d e su y l’intensità I. Bene: cliccate sul bottone dell’asse x e si attiverà una finestrina; andate col puntatore sulla colonna delle distanze e scrollate tutti i valori e ENTER; ripetete la manovra per l’asse y, poi OK due volte e:

Il grafico non è male, però incompleto: mancano le intestazioni degli assi e tanto altro: andate in Layout e, attivando i bottoni necessari, titolate, titolate gli assi, scegliete le dimensioni dei

Appunti per una guida all’uso di Excel. Prof. Gianna Dosi

5

caratteri, togliete griglie e scritte che appaiono di default ma che non vogliamo: basta puntare sopra l’oggetto indesiderato, CLIC e pigiare CANC:

Non è male, però la scala che comincia da (0;0) non è il massimo, allora ridimensioniamo le scale del grafico (oh, potete darvi da fare voi con la guida in linea di Excel…). A grafico attivato, basta un clic su un punto qualsiasi, si riattivano le opzioni del grafico. La scala y può partire da 0, ma quella x ci fa più comodo che parta da 20, per esempio: clic sul bottone Assi, clic su Asse orizzontale principale, clic su Altre opzioni ecc:

E lavorate sulle opzioni che la finestra vi propone:

Appunti per una guida all’uso di Excel. Prof. Gianna Dosi

6

Devo dire che sta venendo bene. Ah, mancano le barre di incertezza. A grafico attivo in Layout cliccate su Barre di errore, altre opzioni ecc e rispondete alla finestra. Se non si apre l’opzione per l’errore orizzontale, provate a puntare su una barra di errore orizzontale nel grafico (una qualsiasi) e clic: si dovrebbe aprire la finestra anche per i Δ orizzontali. Provate: prima o poi succede.

Manca solo un po’ di manicure sulle dimensioni del carattere poi siamo a posto. Puntate i numeri dell’asse x: si attiva il rettangolo dei numeri, andate su home e scegliete la dimensione del carattere, così per l’altro asse. Vi siete accorti che è sparito il marcatore dei punti: basta andare

Appunti per una guida all’uso di Excel. Prof. Gianna Dosi

7

sul grafico, puntare il simbolino col puntatore, clic e da Layout, formato selezione, opzioni indicatore scegliere Nessuno.

Già fatto? Sì, ma serve a poco perché noi stavamo cercando la legge e allora:

Dal grafico dati alla formulazione della legge Abbiamo la tabella dati e il grafico. Guardando questo sembra che i punti siano disposti su un’iperbole, proporzionalità inversa et cetera. Ma discutendo i dati in tabella abbiamo visto che questa ipotesi è da scartare. Guardando meglio mentre l’intensità si riduce a un terzo e alla fine ad un sesto del valore iniziale, la distanza cresce più lentamente: arriva solo a poco più del doppio del valore iniziale, invece delle sei volte (si dirà sestuplo?).

La facciamo corta, proviamo a elevare al quadrato la distanza e guardiamo (non sarebbe male che provaste a impostare la formula di d2 e anche di Δd2 . Allora, parentesi (metaforiche) per le formule e i calcoli.

Come impostare formule con XLS (e farglielo calcolare ripetutamente)

Dalla tabella iniziale voglio calcolare d2 per tutti i valori: 1. scelgo la cella adiacente a quella col primo valore di d 2. clic, poi digito = (è un segnale fra noi e XLS: attenzione che sto per scrivere una formula e

tu, XLS, la devi calcolare) clic su 24 poi su * poi su 24 e:

Poi ENTER e appare il valore di 242 . Se non ci credete prendete la vostra macchinetta e verificate. Voglio che il calcolo venga ripetuto:

Appunti per una guida all’uso di Excel. Prof. Gianna Dosi

8

1. punto col puntatore nell’angolo in basso a destra della cella dove è scritto 576: il puntatore cambia aspetto e diventa una crocetta

2. scrollo per tutta la colonna della distanza e:

Guardo i dati: quando I va da 300 a 150 il quadrato della distanza sembra raddoppiato: allora la proporzionalità inversa c’è fra I e d2 ! E quindi I* d2 deve essere costante. Provate con le conoscenze che avete ora, ovviamente confrontando i diversi prodotti tenendo conto delle incertezze. Si fa presto:, formula,puntatore e scrolling. Ipotizziamo quindi una proporzionalità inversa al quadrato. Sembra una di quelle cose da insegnanti che servono solo a mettere in difficoltà gli studenti: non è così. La natura usa spessissimo questa legge. Per verificarla occorre linearizzare e quindi calcolarsi 1/ d2 , farlo per tutti i valori e fare il grafico con I e 1/ d2 . Adesso si fa presto:

Appunti per una guida all’uso di Excel. Prof. Gianna Dosi

9

I punti sembrano allineati. Ma quanto? Molto, poco. Come posso fare: i kbest, kmin e kmax?

Andate sul grafico, puntate su un dato qualsiasi, cliccate, si attiva la finestra layout, andate su linea di tendenza, altre opzioni linea di tendenza e spuntate come in figura, in basso guardate bene.

Apparirà la retta che meglio passa fra i punti, la sua equazione e altro:

Appunti per una guida all’uso di Excel. Prof. Gianna Dosi

10

Altro è rappresentato da R2 : questo coefficiente (di correlazione) è una specie di controllo di qualità. Ci dice quanto i dati sono allineati. Per capirci: se questo numero è 0,98 l’allineamento non è buono affatto, siamo in curva. E’ un parametro oggettivo (per informazioni rivolgersi insegnanti di matematica).

Per i nostri lavori potremmo cominciare ad accettare un R2 che valga almeno 0,99. Un valore pari a 1 rappresenta la “perfezione”. Attenzione, però R2 viene calcolato con tante cifre decimali e il calcolatore arrotonda alla quarta/sesta cifra. Ultimo: se la retta ha un k negativo allora R2 è negativo.

C’è tutto quello che cercavamo: la legge con i parametri numerici. Ah mancano i Δ, sia di q che di k. Dal grafico non possiamo fare altro ma per avere quello che ci manca dobbiamo fare la

regressione lineare

Abbiamo il file xls che si presenta così:

Il grafico non serve, ci servono i dati e, notate nella tabella, non ci sono Δ. XLS non sa che si tratta di misure, dirette o indirette, nonostante ciò….

Dobbiamo eseguire la regressione lineare per avere R2 , K±ΔK e q±Δq.

Se siete col grafico attivo disattivatelo

In alto sulla barra degli strumenti cercare Dati, clic su Analisi dati (può capitare che nella vostra versione non ci sia Analisi dati, eppure avete una regolare licenza di Excel. Andate alla guida in linea e cercate Strumenti di calcolo e/o componenti aggiuntivi, seguite le indicazioni) e:

Appunti per una guida all’uso di Excel. Prof. Gianna Dosi

11

Clic su Regressione. Si attiva una finestra simile a quella per i grafici: puntate sul bottone per i dati che vanno messi in x, scrollate e entrate; fate lo stesso per l’asse y, poi OK. Si apre questa videata: è piena di dati.

Le informazioni che ci servono sono evidenziate.

Appunti per una guida all’uso di Excel. Prof. Gianna Dosi

12

Cancellate il resto, date una sistemata alle celle, spostate, allargate e trasferite la parte che interessa sulla vostra relazione: avrete incollato qualcosa di simile:

Statistica della regressione

R al quadrato 0,997769 Osservazioni 6

Coefficienti

Errore standard Intercetta

1,83502

4,563774

Variabile X 1 171569,4

4056,334 Cerchiamo di capire il significato di quelle voci:

1. R al quadrato è il controllo di qualità;

2. Osservazioni indica il numero di dati controllati (verificare se ci sono tutti)

3. Intercetta riguarda la nostra q. Coefficienti è q, mentre errore standard moltiplicato per tre rappresenta la nostra Δq

4. Variabile X 1 rappresenta il valore di K±ΔK (e il resto come per intercetta)

Ricapitolando: avete eseguito il grafico linearizzato con la linea di tendenza e l’equazione scritta a suo modo; sotto al grafico scriverete qualcosa tipo: “il valore del coefficiente di correlazione è accettabile (buono, inspiegabilmente ottimo, et cetera), eseguiamo quindi la regressione lineare

Appunti per una guida all’uso di Excel. Prof. Gianna Dosi

13

per trovare i parametri numerici della legge cercata. Qui sotto riportiamo l’output della regressione” (è quella roba in alto). E poi, importante, scrivete qualcosa ± simile a:

“dai parametri della regressione otteniamo

k = (171569,4±12168) lux*cm2 e q = (1,835±13,68) lux.

(naturalmente scriverete k e q col corretto numero di cifre certe e incerte e, magari, in notazione scientifica)

Nella relazione metterete qualcosa tipo: “La legge cercata sarà quindi:

I = (1,7±0,1)*105* 1/d2 + (2±10) [lux ; cm ]”

Ovviamente discuterete sul peso di q, se stimarlo a 0,…, la precisione e, ove possibile, l’accuratezza.

Le solite cose, insomma. Solo che da adesso grafici con le tre rette, i calcoli, le coordinate dei punti scelti e tutto il resto li fa xls in pochissimo tempo. E bene, se VOI lo comandate bene.

Dimenticavo, dovreste tornare sul grafico “finito”, puntare l’equazione e cliccarci per sistemare le cifre di K, di q, di R2 e, se volete, mettere anche i Δ.