Lezione 8 (Fogli Elettronici - Operazioni Avanzate) PDF

LEZIONE 8 I FOGLI ELETTRONICI - OPERAZIONI AVANZATE 8.0. LE OPERAZIONI CON I DATI. 1 8.0.1. ESECUZIONE DELLE OPERAZIONI ARITMETICHE. Lo scopo principale di Excel è quello di eseguire calcoli, ma in modo diverso rispetto ad una semplice calcolatric...

LEZIONE 8 I FOGLI ELETTRONICI - OPERAZIONI AVANZATE 8.0. LE OPERAZIONI CON I DATI. 1 8.0.1. ESECUZIONE DELLE OPERAZIONI ARITMETICHE. Lo scopo principale di Excel è quello di eseguire calcoli, ma in modo diverso rispetto ad una semplice calcolatrice. Abbiamo visto che i dati sono inseriti in Excel nella Griglia, cioè nell'insieme di celle presenti, ciascuna delle quali è identificabile tramite una lettera per la colonna ed un numero per la riga (in questo ordine, PRIMA la colonna e POI la riga). Per eseguire un calcolo in Excel dobbiamo inserire il rispettivo “comando”: ad esempio, per eseguire la somma del contenuto delle celle A1, A2, A3, sarà del tipo "esegui A1+A2+A3". Ovviamente è necessario tradurre questa frase in modo che sia comprensibile ad Excel. PRIMA DI TUTTO occorre chiedersi DOVE si vuole che venga visualizzato il risultato di una certa operazione (o sequenza di operazioni), cioè è necessario individuare in quale cella il risultato deve comparire. Una volta individuata la cella, supponiamo la cella A4, si deve selezionare la cella A4, scriverci "=A1+A2+A3" e premere Invio: il risultato di tale operazione viene visualizzato, appunto, nella cella A4. Quindi per inserire una formula, una QUALSIASI formula, è necessario che questa sia preceduta dall'uguale (=), così Excel comprende che quello che segue l'uguale è una formula e deve essere visualizzato solo il risultato. Per cambiare operazione basta cambiare il simbolo: ad esempio per la sottrazione si usa "-", per la divisione si usa "/", per la moltiplicazione si usa "*", per l'elevamento a potenza si usa "^". Ovviamente in una stessa formula ci possono essere più operazioni. Valgono le precedenze classiche: prima vengono eseguite moltiplicazioni e divisioni e POI somme e sottrazioni, entrambe nell'ordine in cui si presentano. Tali precedenze possono essere modificate usando le parentesi tonde: prima di tutto viene valutato ciò che è scritto tra parentesi. È possibile nidificare le parentesi tonde una dentro l'altra, basta non confondersi. Si comprende perciò anche la differenza che esiste fra ciò che è scritto in una certa cella e ciò che è scritto nella barra della formula quando una cella è selezionata: nella cella viene visualizzato il risultato dell'operazione che è scritta nella barra della formula: quindi per modificare una formula si deve agire sulla barra della formula. Notare che TUTTI i risultati delle formule cambiano non appena si cambiano i dati contenuti nelle celle, senza bisogno di fare altre operazioni. Suggerimento: per inserire più velocemente la lettera e il numero di una certa cella, dopo essersi assicurati della presenza del cursore di inserimento nella barra della formula, basta cliccare sulla cella della quale si vogliono inserire i riferimenti ed Excel li inserisce automaticamente. 2 8.0.2. RICONOSCERE I MESSAGGI DI ERRORE PIÙ COMUNI ASSOCIATI ALL'USO DELLE FORMULE. I messaggi di errore associati all'uso delle formule vengono scritti nella cella in cui dovrebbe venir scritto il risultato dell'operazione, generalmente iniziano con il carattere cancelletto (#). Vediamo i più comuni:  #DIV/0 significa che si sta cercando di fare una divisione per zero;  ####### significa che il risultato non entra nella cella: allargare la colonna, oppure significa che la cella contiene una formula che produce una data o un’ora negativa;  #N/D significa che c'è un valore che non è disponibile per una certa formula;  #NULLO! significa che è stata specificata l'intersezione di due aree che non si intersecano;  #NUM! significa che c'è un problema con uno dei numeri di una formula o una funzione (ad esempio il numero è troppo grande o troppo piccolo, oppure è inaccettabile per una determinata funzione, ad esempio se si tenta di fare la radice quadrata di un numero negativo);  #RIF! significa che il riferimento di una cella non è valido;  #VALORE! significa che è stato utilizzato un argomento di tipo sbagliato;  #NOME? significa che Excel non riconosce una parte del testo di una formula; Una guida molto più dettagliata sugli errori si può ottenere consultando la Guida in linea, digitando "#" nel testo da cercare. 8.0.3. RIFERIMENTI RELATIVI, MISTI E ASSOLUTI. Il riferimento ad una cella è la coppia lettera e numero che individua una cella. Quando si effettua la copia di una cella che contiene una formula, a meno che i riferimenti delle celle contenute nella formula non siano stati forniti in modo assoluto, Excel aggiorna automaticamente tutti i riferimenti. Questa funzione è molto utile: ad esempio, se nella cella A3 ho scritto "=A1+A2", cioè "esegui la somma di A1 e A2" e copio la cella A3 nella cella B3 perché in B1 e B2 ho altri dati dei quali voglio conoscere la somma, Excel (a meno di aver specificato i riferimenti come assoluti) aggiorna da solo il contenuto copiato in B3, in base a quante righe e colonne dista B3 da A3. In questo caso B3 si trova una colonna a destra di A3 e quindi la formula che Excel scrive in B3 sarà "=B1+B2", che è proprio quello che volevamo fare. A volte però può essere necessario bloccare una delle componenti di una formula, in modo che non venga aggiornata automaticamente durante gli spostamenti, perché magari abbiamo una componente fissa sul foglio di lavoro che deve essere utilizzata in tutti i calcoli. Per bloccare la cella basta anteporre il simbolo del dollaro ($) sia alla lettera della colonna sia al numero della riga nel riferimento della cella che desideriamo venga bloccata, ad esempio ottenendo, in A4, "=$A$1*(A2+A3)" e, copiando il contenuto in B4, "=$A$1*(B2+B3)". È possibile anche bloccare solo il riferimento di riga o solo il riferimento di colonna di una certa cella, mettendo il simbolo del dollaro solo davanti alla lettera o solo davanti al numero, a seconda dei casi. 8.0.4. LE FUNZIONI DI SOMMA, MEDIA, MASSIMO, MINIMO, CONTEGGIO. Al posto di alcune formule si possono usare le funzioni. Partiamo da un esempio di funzione: per eseguire la somma delle celle A1, A2, A3 e A4 si può scrivere, usando le normali formule, nella cella A5, "=A1+A2+A3+A4"; se invece si vuole usare una funzione si dovrà scrivere in questo caso, sempre nella cella A5, "=somma(A1:A4)", ottenendo lo STESSO risultato. 3 Analizziamo questo esempio. “Somma" è il nome della funzione che stiamo utilizzando; le cose contenute fra le parentesi tonde sono l'argomento della funzione; i due punti, all'interno dell'argomento della funzione, indicano che si vuole prendere tutte le celle comprese fra la cella che sta a sinistra e quella che sta a destra dei due punti (in questo caso fra la cella A1 e la cella A4). Invece, per specificare SOLO due celle, le si deve separare da un punto e virgola ";". Per esempio, per sommare SOLO il contenuto delle celle A1 e A4 si deve mettere il punto e virgola al posto dei due punti, in questo modo: "=somma(a1;a4)". La funzione "somma" esegue quindi la somma algebrica del contenuto delle celle indicate nell'argomento. La funzione "media" esegue la media aritmetica del contenuto delle celle indicate nell'argomento (NB: la media fra i numeri X1, X2, X3,..., Xn è il numero (X1+...+Xn)/n); ad esempio, la media fra i numeri 2, 4 e 9 è (2+4+9)/3=5). La funzione che restituisce il valore massimo fra quelli contenuti nelle celle indicate nell'argomento si chiama "max". La funzione che restituisce il valore minimo fra quelli contenuti nelle celle indicate nell'argomento si chiama "min". Per effettuare dei conteggi si possono usare le funzioni "conta.numeri", "conta.se", "conta.valori", "conta.vuote" che, rispettivamente, restituiscono il numero di celle contenenti numeri, il numero di celle che corrispondono al criterio dato, il numero di celle non vuote, il numero di celle vuote. L'argomento di tutte le funzioni presentate in questo punto, tranne la funzione "conta.se", è costituito dall'intervallo di celle alle quali applicare la funzione. Per la funzione "conta.se" l'argomento invece è (intervallo_di_valori; "criterio"), dove "criterio" è un’espressione del tipo ">2" oppure "=B5": per ogni cella inclusa nell'intervallo viene valutata la condizione e, se la condizione risulta vera, la cella viene compresa nel conteggio. NOTA: l'elenco completo delle funzioni presenti in Excel, con una breve descrizione e con gli argomenti richiesti è disponibile nel menù Formule-Inserisci Funzione, che apre una finestra in cui sono presenti tutte le funzioni divise per categoria. Scegliendone una si apre una finestra che guida l’utente nell'inserimento degli argomenti della funzione. 8.0.5. LA FUNZIONE "SE". La funzione "se" è molto utile nel caso in cui si debbano prendere delle decisioni. La struttura della funzione "se" è la seguente: "=se(condizione; se_condizione_è_vera; se_condizione_è_falsa)". “Condizione" è un'espressione che può essere o vera o falsa, costruita usando gli operatori di confronto (cioè = uguale, < minore, > maggiore, = maggiore o uguale, diverso da). 4 Un esempio di condizione è “A1>=A2" che significa “A1 maggiore o uguale ad A2". Al posto di "se_condizione_è_vera" si deve inserire cosa fare nel caso in cui la "condizione" sia vera. Al posto di "se_condizione_è_falsa" si deve inserire cosa fare nel caso opposto, in cui "condizione" sia falsa. Esempio di funzione "se": nella cella A2 scrivo "=se(A1>=0; "Ho un utile"; "Ho una perdita")". Questa funzione mostra nella cella A2 la frase "Ho un utile" nel caso in cui il contenuto di A1 sia maggiore o uguale a zero, altrimenti (cioè se A1 è minore di zero) mostra la frase "Ho una perdita". Al posto delle frasi si può mettere una qualunque altra espressione o funzione. È possibile anche nidificare più funzioni "se" l'una dentro l'altra. Ad esempio nella cella A2 si può scrivere "=se(A1>=6; se(A1>=7; "Hai preso un voto alto!"; "Hai preso un voto sufficiente..."); "Hai preso un voto non sufficiente...")“. 5 8.1. IMPOSTAZIONE E FORMATTAZIONE DEI DATI. 8.1.1. IMPOSTARE IL TIPO DI DATI DI UNA CELLA. I dati che si possono inserire nelle celle possono essere di vario tipo: caratteri di testo (stringhe), numeri, date. Excel riconosce automaticamente il tipo di dato inserito in una certa cella, ma per non creare ambiguità e per aver un maggior livello di personalizzazione è possibile scegliere quale tipo di dati una determinata cella o gruppo di celle deve contenere. Per fare ciò cliccare con il tasto destro del mouse sulla cella (o sull'insieme di celle selezionate) in questione e scegliere Home-Celle-Formato-Formato celle… (come mostrato nell’immagine successiva)e selezionare dall'elenco il tipo di dati desiderato, specificando eventuali opzioni sulla sinistra. Le opzioni più importanti sono, per il formato “Numero", il numero di cifre decimali che devono essere presenti dopo la virgola e se usare o meno il punto di separazione delle migliaia; per il formato “Data" è possibile scegliere come visualizzare la data inserita. Il formato “Valuta" consente di visualizzare, nella stessa cella di un numero, il simbolo dell'euro o di un'altra moneta; il formato “Percentuale" consente di visualizzare il simbolo della percentuale vicino al numero inserito. 6 8.1.2. FORMATTARE I DATI. Per cambiare le opzioni di formattazione di un testo inserito si deve selezionare il testo (o tutta la cella) da formattare e scegliere il menù Formato celle dalla scheda “Carattere" (oppure cliccare con il tasto destro del mouse sulla cella e scegliere Formato celle): in tale finestra(mostrata nell’immagine successiva), analogamente a quanto visto in Word, si può cambiare il tipo di carattere, la dimensione, applicare il grassetto, il corsivo, la sottolineatura singola e doppia, il colore del testo. Quasi tutte queste cose si possono fare anche attraverso le varie barre di accesso rapido (in genere si usano queste, per fare prima), che sono identiche a quelle usate in Word. Per cambiare lo sfondo di una o più celle è necessario selezionarle e poi aprire nuovamente la finestra Formato celle (dal menù o con il tasto destro del mouse), questa volta però si usa la scheda “Motivo", nella quale si può selezionare il colore dello sfondo da impostare. Anche il colore di sfondo si può cambiare dalla barra degli strumenti usando il “Secchiello". Anche in Excel è possibile, come visto in Word, copiare la sola formattazione di una parte di testo: si devono prima selezionare le celle dalle quali riprendere la formattazione e poi premere sul pulsante “Copia formato" presente sulla barra degli strumenti ed infine cliccare sul punto in cui incollare la formattazione copiata. 8.1.3. ALLINEAMENTO E BORDI. Per allineare il contenuto di una o più celle è necessario selezionarla e poi aprire la solita finestra Formato celle, scheda Allineamento (vedi immagine precedente): in tale finestra si può impostare l'allineamento a destra, a sinistra, al centro, in alto, in basso, agendo sulle due caselle combinate presenti. L'allineamento orizzontale si può impostare anche dalla barra degli strumenti, con i soliti pulsanti usati in Word. Se si vuole centrare un titolo su un insieme di celle, basta selezionare le celle in cui si desidera scrivere e poi premere sul pulsante “Unisci e allinea al centro" presente sulla barra degli strumenti(come mostrato nell’immagine successiva) oppure si può agire, nuovamente, tramite Formato celle, scheda Allineamento, selezionando il centrato e selezionando la casella “Unione celle". 7 Per modificare l'orientamento del contenuto di una cella si seleziona la cella (o le celle) della quale si vuole modificare l'orientamento del testo e poi di nuovo, con la finestra Formato celle, scheda Allineamento, si agisce questa volta sulla sezione “Orientamento", impostando i gradi oppure cliccando sul testo in verticale o sulla zona di anteprima per impostare graficamente come dovrà essere orientato il testo. Per aggiungere bordi ad una cella o ad un gruppo di celle si deve selezionare la o le celle sulla quale applicare il bordo e poi aprire la finestra Formato celle, scheda Bordo: in tale finestra, cliccando sul riquadro di anteprima o sui pulsanti che lo circondano, è possibile applicare il bordo alla singola cella selezionata o al gruppo di celle selezionato. Il funzionamento è analogo a quello della finestra di impostazione dei bordi per le tabelle di Word. Per poter andare a capo nel contenuto di una cella si deve selezionare la cella, aprire Formato celle e cliccare su “Testo a capo": in questo modo, quando il testo non entra nella cella a causa della ridotta larghezza, viene mandato automaticamente a capo. 8.1.4. LA FORMATTAZIONE CONDIZIONALE. Con lo strumento formattazione condizionale in Excel è possibile decidere di assegnare automaticamente un tipo di formato ad una cella a seconda del suo valore. Ad esempio è possibile fare in modo che una determinata cella:  assuma il colore rosso se il suo valore è uguale a 100;  assuma il colore verde se il suo valore è inferiore a 50; 8   assumere il formato grassetto e colore blu, se il suo valore è superiore. Poniamo ad esempio di voler impostare una regola sulla cella N6 riferita al totale delle spese sostenute per il consumo al Pub: la cella N6 dovrà assumere un colore rosso chiaro quando supera il valore di euro 300come mostrato nell’immagine successiva. Per impostare la formattazione condizionale dell'esempio descritto, basta selezionare la cella N6, dalla scheda "Home" cliccare sul comando "Formattazione condizionale", scegliere la voce "Regole evidenziazione celle", poi cliccare su "Maggiore di..."come mostra l’immagine successiva. A questo punto occorre cancellare il contenuto della prima casella (se esistente) e digitare 300; poi scegliere il tipo di formato dal menu a discesa, posto a destra, e cliccare su OK come mostra l’immagine successiva. Quando la cella N6 assumerà un valore superiore a 300, verrà automaticamente formattata con un colore diverso. Per ottenere invece un formato personalizzato, è sufficiente selezionare la cella dove applicare la formattazione condizionale (N6 ad esempio), dalla scheda "Home" cliccare sul comando "Formattazione condizionale", scegliere la voce "Regole evidenziazione celle", cliccare sulla regola desiderata (ad esempio: "Minore di..."), digitare il valore nella prima casella (ad esempio 100), cliccare sul menu a discesa della casella a fianco, infine cliccare su "Formato personalizzato". 9 Dalla scheda Carattere è possibile scegliere lo stile (ad es.: grassetto): mentre dalla scheda Riempimento è possibile scegliere il colore di sfondo (ad es.: verde): Cliccare su OK e poi ancora su OK: quando la cella N6 assumerà un valore inferiore a 100, verrà automaticamente formattata in grassetto e con lo sfondo verde. 10 8.2. ESERCITAZIONE. 8.2. ESERCITAZIONE. In MS Excel: 1) verificare che A1+A2*A3 è diverso da (A1+A2)*A3. 2) Testare la funzionalità dei riferimenti relativi, assoluti e misti inserendo opportune tabelle di dati e copiano le formule da una cella all'altra. 3) Inserire una formula per il calcolo della media aritmetica e poi fare la stessa cosa usando la funzione “Media". 4) Inserire una formula che segua tale logica: se il valore di una certa cella è maggiore di zero scrivere "sì", altrimenti scrivere "no". 5) Scrivere una formula che trasforma un voto espresso in decimi, da inserire in una specifica cella, in un voto a parole che compare in un’altra cella, in modo che un voto fino a 4 compreso corrisponda a "gravemente insufficiente", dal 5 al 6 escluso "insufficiente", dal 6 al 7 escluso "sufficiente", dal 7 all'8 escluso "buono", dall'8 al 9 escluso "molto buono", dal 9 in poi "ottimo". 11 8.3. ESERCITAZIONE. 8.3 ESERCITAZIONE Si riporti la seguente tabella in un nuovo foglio di lavoro. 1) Si inseriscano le formule (colonna G) per il calcolo dei totali per mese. 2) Si inseriscano le formule nelle colonne I, J, K, L basate su quella del calcolo di H4. 12