OPERAZIONI DI BASE PER L’UTILIZZO DI MICROSOFT EXCEL®

Formattazione

L’utilizzo di Excel® è molto comodo per fare calcoli poiché permette di integrare numeri e variabili con testo, immagini, grafici e disegni in modo da spiegare il procedimento utilizzato nello stesso foglio dove vengono svolti i calcoli. In ogni cella è possibile inserire numeri, testi e formule.
Il comportamento del software Microsoft dipende molto dalla versione e dalla formattazione usata per il sistema operativo. Se per la prima non è possibile far niente se non reinstallare il pacchetto Office®, per la seconda è possibile impostare quella preferita.
Per impostare la formattazione del sistema operativo è necessario entrare nel “Pannello di controllo” (il percorso più veloce è utilizzando il Menù dello “Start”), cliccare sulla dicitura “Paese e lingua” e modificare il formato secondo la preferenza. Il formato consigliato da utilizzare è “inglese (Regno Unito)”. Con questa formattazione il programma considera valido solo il punto come separatore delle cifre decimali di un numero.
Infatti scrivendo ad esempio in una cella 345,23 si noterà che il contenuto sarà spostato sul margine sinistro della cella (significa che Excel® considera il contenuto come uno stralcio di testo e non come un numero). All’opposto scrivendo 345.23 si otterrà lo spostamento del contenuto verso il margine destro della cella (significa che Excel® considera il contenuto come un numero utile per svolgere calcoli).
Excel® per come è stato programmato non distingue le maiuscole dalle minuscole, quindi non è possibile differenziare i nomi delle celle utilizzando solo queste, ma è necessario usare lettere diverse.

Risoluzione di una formula matematica

Selezionare la cella nella quale si desidera che il risultato venga visualizzato e scrivere la formula preceduta dal simbolo “=”. È possibile inserire nella formula dati contenuti in altre celle del foglio di calcolo, semplicemente cliccando sulle celle stesse.
È possibile nominare le celle nelle quali sono contenuti i dati che la formula elabora con un nome che indica la natura del dato stesso. In questo modo si facilita la comprensione delle formule qualora le si volesse visualizzare o modificare successivamente (vedi confronto in Figura 1).

Figura 1: Confronto tra formule.

Come nominare una cella:

Selezionare la cella da nominare, sostituire il valore del riferimento cartesiano contenuto nel riquadro indicato dalla freccia rossa (vedi Figura 2) con il nome desiderato.

Figura 2: nomina di una cella.

Operazione “copia-incolla” di celle: puntamento relativo

Si vuole copiare la cella C5, contenente la formula per calcolare l’area di un rettangolo, nelle celle C6 e C7. Per farlo è sufficiente selezionare la cella e trascinare la crocetta nelle celle sottostanti come mostrato in Figura 3.

Figura 3: copia-incolla.

In questo modo nelle celle C6 e C7 è stata automaticamente inserita la stessa formula, ma con i dati delle righe rispettivamente 6 e 7.

Operazione “copia-incolla” di celle: puntamento assoluto

In alcuni casi può essere necessario copiare una formula in una cella e mantenere i riferimenti ai dati originali. Per fare ciò bisogna inserire nella formula originale il simbolo “$” davanti alla coordinata numerica (se si vuole mantenere il riferimento alla riga) e o letterale (se si vuole mantenere il riferimento alla colonna) dei dati di cui si vuole mantenere il riferimento assoluto.
Es: E10→$E$10.
Il simbolo “$” può essere velocemente inserito premendo il tasto F4 quando si è selezionato un dato durante la digitazione della formula.
È possibile inoltre bloccare solo la coordinata letterale, e non quella numerica, di un dato, o viceversa. Questa operazione è utile, ad esempio, se si vuole creare una matrice che abbia come elementi le ipotenuse di tutti i triangoli rettangoli che si ottengono combinando cateti di lunghezza intera compresa tra 1 e 7 metri.

Figura 4: creare una matrice.

Una volta scritta la formula (Figura 4), per copiarla in tutte le altre celle costituenti la matrice è sufficiente selezionare la cella contenente la formula, premere Ctrl+C (percorso veloce per la funzione “Copia”), poi selezionare tutte le celle della matrice e premere Ctrl+V (percorso veloce per la funzione “Incolla”) (vedi Figura 5).

Figura 5: copia-incolla in più celle di una matrice.

La stessa operazione di “Copia-Incolla” può esser velocemente attuata anche trascinando, sulle celle in cui si vuole copiare il contenuto, il quadratino nero che appare sullo spigolo inferiore a destra della cella da copiare (una volta selezionatala).

Operazione “copia-incolla” di celle: puntamento per nome

Per semplificare la scrittura delle formule con puntamento assoluto è comodo utilizzare la nominazione delle celle in cui si inseriscono i dati o le variabili. In questo modo scrivendo le formule usando i nuovi nomi delle celle è possibile mantenere il puntamento assoluto su queste senza dover aggiungere “$”.

Nominare una matrice

Per nominare una matrice bisogna selezionarla completamente e sostituire il testo come fatto in precedenza per nominare una cella.

Inversa di una matrice

Per prima cosa è utile nominare la matrice della quale si vuole calcolare l’inversa. Nell’esempio la matrice è stata chiamata “matr”. Selezionare le celle nelle quali Excel® scriverà gli elementi della matrice inversa, poi scrivere la formula: =MATR.INVERSA(Matr). Infine premere Shift+Ctrl+Invio per attuare la formula sull’intera matrice.
La formula “MATR.INVERSA” è in genere tipica solo delle versioni in italiano del pacchetto Office®, quindi nel caso si utilizzasse un pacchetto inglese la formula sarebbe “MINVERS”.
In ogni caso è possibile visualizzare l’elenco delle formule preimpostate di Excel® cliccando su  a sinistra della barra in cui vengono scritte le formule (evidenziata in Figura 6).

Figura 7: calcolare l'inversa di una matrice.

Prodotto di matrici

Per verificare che la matrice inversa appena scritta sia corretta si può effettuare il prodotto matriciale della matrice originale con l’inversa stessa. Il risultato dev’essere la matrice identità. Per prima cosa è utile nominare la matrice inversa, in questo caso è stato preso “Matrinv”. Selezionare poi le celle nelle quali Excel® scriverà gli elementi della matrice prodotto e scrivere la formula: =MATR.PRODOTTO(Matr;Matrinv). Infine premere Shift+Ctrl+Invio.
Ovviamente il risultato che deriverà da questa operazione non sarà una perfetta matrice identità se non approssimando ad un numero finito di cifre decimali. Questo accade perché ogni calcolo gestito da un calcolatore è puramente numerico e quindi è sempre derivato da un’approssimazione che per quanto piccola non può mai essere inesistente.

Figura 8: calcolare il prodotto di due matrici.

Grafici di dati sperimentali

Partendo da dati sperimentali è possibile creare grafici che ne visualizzino l’andamento. Il grafico di una spezzata che collega i punti si ottiene selezionando i dati (vedi Figura 9) , seguendo il percorso “Inserisci/Grafico a linee” e inserendo le etichette degli assi cartesiani.
È possibile interpolare i punti del grafico con una curva che ne approssima l’andamento. Si può dapprima interpolare i dati sperimentali con una retta selezionando col tasto destro del mouse la spezzata precedentemente creata e scegliendo la voce “aggiungi linea di tendenza”; spuntare le caselle relative alla visualizzazione sul grafico dell’equazione della curva e del valore del quadrato dell’errore di approssimazione R. Il risultato di questa operazione è visibile in Figura 10. L’obiettivo risulta quindi essere il calcolo dello scarto quadratico medio o deviazione standard degli errori di approssimazione delle curve di approssimazione, in modo da visualizzare numericamente quanto la curva considerata si discosta da quella dei dati sperimentali.
La deviazione standard o scarto quadratico medio misura la dispersione dei dati attorno al valore atteso ed è calcolata come di seguito:

dove:
è la deviazione o lo scarto quadratico medio (SQM);
 sono i valori della grandezza considerata;
 è la media aritmetica degli ;
n è il numero di dati presi in considerazione.

Figura 9: grafico di una spezzata.

Figura 10: Fitting di dati sperimentali con una retta.

All’equazione della retta calcolata da Excel® e visualizzata sul grafico si sostituisce il valore di x con i valori della distanza (m) e si calcola successivamente R2, cioè l’errore di approssimazione che individua quanto l’approssimazione lineare si discosta dalla curva dei dati sperimentali. In seguito si calcola con la differenza tra i valori dell’approssimazione lineare e quelli della velocità, come riportato in Figura 11.

Figura 12: Calcolo dell’errore di approssimazione.

Inoltre si procede a calcolare, come spiegato in precedenza, lo scarto quadratico medio o deviazione standard scrivendo nella cella scelta “=radq(media(R2))” , dove per “R2” si intende la selezione di tutti i valori di R2 calcolati.

Figura 13: Calcolo dello scarto quadratico medio.

L’approssimazione lineare non fitta perfettamente i dati sperimentali.
Una polinomiale potrebbe approssimare meglio i dati.
Partendo dall’equazione generica di una parabola  e da valori casuali dei coefficienti, si approssimano i dati sperimentali con una parabola come mostra la Figura 14.

Figura 15: Approssimazione dei dati sperimentali con una parabola.

Anche in questo caso si procede calcolando l’errore di approssimazione R2 e successivamente lo scarto quadratico medio come nel caso precedente. Utilizzando lo strumento “Solver” o “Risolutore”  si possono risolvere problemi di ottimizzazione, in questo caso lo si può utilizzare per minimizzare lo scarto quadratico medio. Un modello di ottimizzazione è composto da tre parti: la cella obiettivo, le celle variabili e i vincoli. La cella obiettivo in questo caso è lo scarto quadratico medio, le celle variabili sono i coefficienti della parabola (ovvero a, d ed e) ed il vincolo è . Si procede a utilizzare risolutore accedendo da Dati/Risolutore come da Figura 16.

Figura 17: Visualizzazione dello strumento Solver.

In questo caso si trova la parabola che approssima meglio i dati sperimentali. Successivamente si procede costruendo il grafico della parabola come riportato nella Figura 18.

Figura 18: Individuazione della cella obiettivo e costruzione della parabola che approssima meglio i dati sperimentali.