Risolvere i problemi
con le formule e le funzioni dei fogli elettronici
Prof. Alessandra Musolino a.a. 2020/2021
Prima parte
Perché i fogli elettronici?
Quando ci troveremo di fronte ad un problema da risolvere dovremo avere una tale padronanza dello strumento informatico da:
A. Scegliere lo strumento (software applicativo) più adatto tra più alternative
B. Rispondere alle esigenze specifiche con l’utilizzo ottimale delle funzionalità disponibili
C. Raggiungere un risultato rispondente agli standard di qualità attesi.
D. Impiegare un tempo ragionevolmente compatibile con i risultati attesi Questi semplici passi sono possibili solo se l’esperienza acquisita
permette di operare le scelte di base in maniera rapida e automatica.
Lo studio non si limiterà alle sole funzionalità attualmente presenti nella versione utilizzata, ma dovrà estendersi alla comprensione degli elementi strutturali delle applicazioni stesse così da permettere in un secondo
tempo un autonomo utilizzo degli upgrade di Excel o utilizzo di software di gestione di fogli elettronici diversi (Es. Calc di OpenOffice, plug-in per office su Google Drive).
Faremo riferimento alla guida in linea, che associata alla
contestualizzazione delle funzionalità rappresenta sicuramente il modo più comodo, sicuro e rapido per lavorare.
L’utilizzo dell’Help in linea (attivabile anche tramite in tasto funzione F1) può far risparmiare tempo ed energie nell’apprendimento di un nuovo software, ma non può mai sostituire la capacità personale di operare secondo i quattro semplici passi (A,B,C,D) prima citati.
Sapere dove andare a cercare un’informazione non è cultura, ma solo il primo passo per far propria un’ulteriore conoscenza da integrare con le altre già possedute, per una successiva personale elaborazione.
Metodo di apprendimento
LUMSA - Abilità Informatiche – Dispensa a 4 23 ottobre 2020
Interfaccia utente
6
Input da tastiera
Input Spostamento
Home (↖) Colonna A della riga corrente Ctrl + Home (↖) Cella A1
ALT + Pag↓ A destra di una pagina ALT + Pag↑ A sinistra di una pagina Ctrl + Pag↓ Foglio successivo
Ctrl + Pag↑ Foglio precedente
Ctrl↓ Ultima cella in basso,stessa colonna, con contenuto simile (se colonna vuota va all’ultima riga del foglio)
Ctrl→ Ultima cella a destra, stessa riga, con contenuto simile (se riga vuota va all’ultima colonna del foglio)
Ctrl↑ Prima cella in alto,stessa colonna, con contenuto simile (se colonna vuota va alla prima riga del foglio)
Ctrl← Prima cella a sinistra, stessa riga, con contenuto simile (se riga vuota va alla prima colonna del foglio)
LUMSA - Abilità Informatiche – Dispensa a 23 ottobre 2020
Input da tastiera
È possibile selezionare contemporaneamente più celle contigue trascinando il mouse mentre si tiene premuto il tasto sinistro.
Per la selezione di più celle non contigue bisogna seguire la modalità prima descritta tenendo premuto il tasto Ctrl
Per la selezione di più celle contigue da tastiera basta posizionarsi sulla prima e, tenendo premuto il tasto
Maiuscolo, spostarsi con le frecce nelle direzioni desiderata
8
Input da tastiera
Premendo il tasto ALT (a sinistra della barra spaziatrice) compaiono delle lettere maiuscole sul menù file, sull’intestazione delle schede e sui comandi della barra di accesso rapido (gruppetto di comandi a sinistra del pulsante)
LUMSA - Abilità Informatiche – Dispensa a 23 ottobre 2020
Menu di scelta rapida
Esiste anche un modo più rapido per attivare i comandi più comuni ed evitare la ricerca sulla barra multifunzione
Con un click del tasto destro del
mouse si attiva un menu di scelta
rapida accompagnata da una mini
barra di formattazione (solo se
l’oggetto sul quale è posizionato il
mouse può essere oggetto di
formattazione).
10
Inserimento dei dati
LUMSA - Abilità Informatiche – Dispensa a 23 ottobre 2020
Quando si digita un dato in una cella, Excel individua il tipo di dato ed attribuisce la formattazione più appropriata, sempre che la cella non abbia già una propria
formattazione.
Si provi a digitare in una cella vuota una data, dare invio, poi modificare il contenuto della cella scrivendo 1, la cella si trasformerà in 01/01/1900 perché pur conservando
ovviamente il nuovo valore digitato (01/01/1900 è il giorno 1 in Excel) eredita il formato acquisito originariamente dalla cella.
Per modificare il formato di una cella si agirà tramite il comando appropriato di formattazione [Scheda Home, Gruppo numeri, Rif. 1, oppure tramite il tasto destro del Mouse che attiverà il menu di scelta rapida , Rif. 2,
scegliendo la voce “formato celle…..” si aprirà la finestra di dialogo Formato celle Rif. 3].
(1)
(2)
(3)
Esempio di modello di calcolo -1.1
Elementi chiave:
❑ Le celle sono identificate da Colonna&Riga , es. C5
❑ Gli intervalli di celle sono identificati tramite cella iniziale e cella finale es. C5:G18
❑ Per indicare che si sta inserendo in una cella una formula o una funzione si deve anteporre il simbolo =
❑ possibile scegliere più modalità per giungere al medesimo risultato (utilizzare i comandi contenuti nella scheda o scrivere direttamente nelle celle la formula o funzione senza passare per il comando)
❑ Una cella può contenere solo etichette,
numeri e formule con un certo formato
12
Modello di calcolo -1.2
LUMSA - Abilità Informatiche – Dispensa a 23 ottobre 2020
1) Somma entrate – Uscite (prima riga)
Escludiamo la scelta di digitare nella cella F7 =45-36 perché non solo utilizzeremmo Excel come una semplice calcolatrice, ma non potremmo ottimizzare la costruzione del modello stesso né tantomeno potremmo rendere variabile il risultato al variare dei dati di input
Posizioniamoci nella cella F7 e digitiamo =D7-E7
Nella cella F7 sarà visualizzato il risultato dell’operazione (€ 9).
La cella attiva adesso sarà F8
Si torni alla cella F7, si noterà che nella barra della formula è visualizzato il reale contenuto della cella, cioè =D7-E7
Per visualizzare nella cella corrente il reale contenuto si potrà anche utilizzare il tasto Funzione F2 o il doppio click
Possiamo costruire la formula precedente anche spostandoci tra le celle con le freccette, da tastiera, per scrivere i riferimenti alle celle
Per scrivere =D7-E7 in F7 1. Cella corrente F7 2. Digitiamo =
3. Ci spostiamo con la freccetta
sulla cella D7 4. Digitiamo -5. Ci spostiamo con la freccetta
sulla cella E76.
con il simbolosi intenderà nel seguito “premere il tasto Invio”
Modello di calcolo 1.3
1) Somma entrate – Uscite
(righe successive)Considerando che l’operazione da fare nelle righe successive è identica, solo riferita a righe diverse
usufruiamo del meccanismo automatico del foglio elettronico relativo a “copia di formule”
Copiamo la cella F7, con il comando copia o tramite Ctrl+C
Ci spostiamo in F8
Incolliamo con il comando incolla o tramite Ctrl+V
La cella attiva adesso sarà F9
Si torni alla cella F8, si noterà che il contenuto della cella F8 è =D8+E8; ha la stessa forma sintattica di quella originale ma i contenuti sono stati riadattati alla nuova posizione
I riferimenti contenuti in una formula sono considerati relativi rispetto alla posizione della formula
La formula può essere copiata nell’intero intervallo F8:F18
14
Le funzioni
LUMSA - Abilità Informatiche – Dispensa a 23 ottobre 2020
▪ Le funzioni predefinite permettono di effettuare calcoli, semplici o
complessi, ricevendo in input valori variabili o costanti e restituendo in output un risultato.
▪ Sono identificate da un nome e utilizzano uno o più argomenti posti secondo un ordine prestabilito
▪ La lista di argomenti è dipendente dalla funzione e potrebbe anche essere vuota es: =ADESSO()
▪ Tutte le funzioni in Excel hanno delle regole comuni
▪ È introdotta dal simbolo = come nelle formule = SOMMA(A1:A3)
▪ È caratterizzata dal nome della funzione =SOMMA(A1:A3)
▪ Possiede uno o più argomenti =SOMMA(A1:A3)
▪ Gli argomenti sono racchiusi tra parentesi
e separati dal segno; =SOMMA(A3:A4;A1)
Le funzioni
▪ In Excel 2007, quando inseriamo una nuova formula, possiamo usufruire di vari aiuti
▪ Quando si comincia a digitare il nome della formula compare una lista di funzioni il cui nome inizia con la radice della parola digitata;
con le frecce di direzione ↑o↓ si scorre la lista, con il tasto TAB si conferma la scelta.
▪ La comparsa dell’aiuto permette, facendo clic sul nome della funzione sottolineata, di accedere alla guida, alla voce relativa all’argomento desiderato, per avere informazioni aggiuntive in particolar modo sulle proprietà degli argomenti.
▪ Le funzioni sono raggruppate in categorie, Scheda Formule della
barra multifunzione, gruppo di comandi “Libreria di funzioni”
16
Modello di calcolo -1.4
LUMSA - Abilità Informatiche – Dispensa a 23 ottobre 2020
Riprendiamo il nostro esempio
Per effettuare in D19 la somma delle entrate possiamo usare la funzione Somma
Cominciamo a digitare il nome della funzione Possiamo poi selezionarla dall’elenco proposto
Tra parentesi sono elencati gli argomenti;
per la funzione somma il numero di argomenti è variabile, possiamo cioè indicare intervalli, celle, risultati di altre funzioni annidate, costanti.
In questo caso indichiamo come unico argomento l’intervallo D7:D18
Ma lo stesso risultato poteva essere raggiunto anche seguendo strade diverse, ma ovviamente dovrà essere preferibile quella che ottimizza la procedura
Modello di calcolo -1.5
completiamo il nostro esempio 1.
Per effettuare in D20 la media delle entrate possiamo usare la funzione Media che “Restituisce la media aritmetica degli argomenti (numeri, nomi o riferimenti contenenti numeri)”
Cominciamo con il digitare il nome della funzione
Anche per la funzione media gli argomenti sono in numero variabile , come indicato dai ….. che
completano la lista; notare l’argomento tra parentesi quadre [num2] che sta ad indicare un argomento opzionale
Anche in questo caso indichiamo come unico argomento un intervallo (
D7:D18)
Completiamo il modello di calcolo copiando D19 in D20 per avere il totale delle uscite, inserendo in F22 la differenza tra Entrate e Uscite (=D19-E19), sommando poi questo risultato al saldo anno precedente (=F22+G6)
18
Modello di calcolo -1.6
LUMSA - Abilità Informatiche – Dispensa a 23 ottobre 2020
In Scheda Formule, gruppo di comandi Verifica formule troviamo i comandi per visualizzare:
▪ Quali celle hanno effetto sul valore della cella selezionata (Individua precedenti)
▪ La formula di ogni cella anziché il valore risultante (Mostra formule)
(Individua precedenti) (Mostra formule)
Modello di calcolo -2.1
20
Modello di calcolo -2.2
LUMSA - Abilità Informatiche – Dispensa a 23 ottobre 2020
Al variare delle informazioni contenute nell’intervallo di celle B7:D12 si dovranno costruire le formule risolutive del problema
Comincio con il costruire in C4 una stringa che indichi le squadre oggetto di analisi il cui nome è valorizzato nelle celle C7 e D7
▪ Userò due metodi equivalenti:
1. =C7 & " e " & D7 che concatena per mezzo dell’operatore di concatenazione & il contenuto della cella C7, la stringa “ e ” ed il contenuto della cella D7
2. =CONCATENA(C7;" e ";D7) che utilizza la funzione CONCATENA dove il contenuto della cella C7, la stringa “ e ” ed il contenuto della cella D7 sono forniti come argomenti
C13 e C14 conterranno =SOMMA(C8:C10) e =C11-C12
D13 e D14 conterranno =SOMMA(D8:D10) e =D11-D12 e possono essere
scritte copiando le formule precedentemente costruite in C13 e C14
Modello di calcolo -2.3
Riferimenti assoluti e relativi
In colonna F le celle in azzurro conterranno il nome della prima squadra, contenuto in C7 In colonna H le celle in arancio conterranno il nome della seconda squadra, contenuto in D7 Comincio con digitare in F8 =C7, ma estendendo il calcolo per copia alle celle in F9:F10 noto che il riferimento alla cella C7 viene adattato da Excel che interpreterà a suo modo il
contenuto della cella ed effettuerà delle trasformazioni legate alle posizioni relative delle celle di origine e di destinazione. (Lo stesso vale per i dati in colonna H con riferimento a D7)
Dobbiamo sostituire al riferimento relativo alla cella C7, il riferimento assoluto anteponendo il simbolo di $ sia alla colonna che alla riga, dando ad Excel l’informazione che, in caso di copia della formula in una cella diversa, deve rimanere inalterato il riferimento alla cella C7 tanto nella parte relativa alla colonna $C.., quanto nella parte relativa alla riga ..$7
Avremmo potuto avere la necessità di utilizzare un RIFERIMENTO MISTO, che può contenere una colonna assoluta ed una riga relativa (es. $C7) oppure una colonna relativa ed una riga
assoluta (es. C$7). Nella copia il riferimento relativo verrebbe adattato, mentre quello assoluto rimarrebbe inalterato.
Suggerimento: Per modificare rapidamente il tipo di riferimento ad una cella, all’interno di una formula o
22
Operatori di confronto e Funzione SE
LUMSA - Abilità Informatiche – Dispensa a 23 ottobre 2020
Possiamo utilizzare gli Operatori di Confronto per digitare formule il cui risultato è il valore logico VERO o FALSO
Funzione SE: Restituisce un valore se una condizione specificata da come risultato VERO e un altro valore se da come risultato FALSO
La sintassi è:
SE(cond;esprV;[esprF])
Il terzo argomento è opzionale, se non viene specificato viene assunto uguale a FALSO
Operazione Operatore
Uguale =
Diverso <>
Minore <
Maggiore >
Minore o uguale >=
Maggiore o uguale >=
La funzione SE
Inserimento tramite il comando Inserisci funzione
Se scriviamo la funzione SE direttamente nella cella potremo utilizzare →
24
Modello di calcolo -2.4
LUMSA - Abilità Informatiche – Dispensa a 23 ottobre 2020
Adesso abbiamo tutti i riferimenti teorici per completare il modello di calcolo n. 2
Modello di calcolo -2.5
Esempi 1 e 2 contenuti in Informatica di Base
26
Tabella Pitagorica
LUMSA - Abilità Informatiche – Dispensa a 23 ottobre 2020
Questo breve esercizio viene svolto per apprendere il corretto uso dei riferimenti misti, ovvero quelli dove è necessario bloccare solamente la riga o la colonna (ma non entrambe come nei caso dei riferimenti assoluti). Il risultato che si vuole ottenere è quello in figura
A B C D E F G H I J K
1 Costruzione della tabella pitagorica
2
3 1 2 3 4 5 6 7 8 9 10
4 1 1 2 3 4 5 6 7 8 9 10
5 2 2 4 6 8 10 12 14 16 18 20
6 3 3 6 9 12 15 18 21 24 27 30
7 4 4 8 12 16 20 24 28 32 36 40
8 5 5 10 15 20 25 30 35 40 45 50
9 6 6 12 18 24 30 36 42 48 54 60
10 7 7 14 21 28 35 42 49 56 63 70
11 8 8 16 24 32 40 48 56 64 72 80
12 9 9 18 27 36 45 54 63 72 81 90
13 10 10 20 30 40 50 60 70 80 90 100
Ogni cella deve contenere il prodotto dei fattori che si leggono alla riga 3 ed alla colonna A.
Potremmo provare a scrivere le prime nove formule per i fattori 1,2 e 3 ottenendo:
A questo punto ci apparirà evidente che in tutte le formule rimangono “bloccati” i
riferimenti alla riga 3 (che contiene i primi fattori) e la colonna A (che contiene i secondi fattori).
Quindi sarà sufficiente scrivere in B4 la formula
=B$3*$A4
e poi copiarla ed incollarla su tutto l’intervallo B4:K13
Tabelle strutturate
Ogni nuova versione di Excel ha aggiunto funzionalità per ottimizzare la gestione di intervalli di dati strutturati
Tratteremo pertanto solo i principi generali della gestione delle tabelle presenti dalla versione 2003 e seguenti.
Poniamo un insieme di dati come quelli in figura
(foglio Tabella della cartella allegata) Tramite il comando Tabella della scheda inserisci possiamo trasformare un semplice intervallo di celle, gruppo compatto senza interruzioni e con etichette di intestazione, in una tabella strutturataDopo aver selezionato una cella qualunque all’interno dell’intervallo ed aver scelto il comando Inserisci/Tabella/Tabella si ottiene la finestra di dialogo in figura con una
ipotesi di intervallo della potenziale tabella.
La conferma tramite click su OK trasforma
28
Tabelle strutturate
LUMSA - Abilità Informatiche – Dispensa a 23 ottobre 2020
Accanto alle intestazioni sono visibili le caratteristiche frecce a discesa che aprono i menu del Filtro
Se una tabella di questo tipo è stata appena creata o se è selezionata una esistente, nella Barra multifunzione si inserisce la scheda
Strumenti tabella/Progettazione
Tramite il comando Rimuovi duplicati è possibile eliminare righe duplicate , anche solo per dati uguali in alcune colonne
Tramite il comando Converti in intervallo rimuove le caratteristiche di tabella e il nome attribuito automaticamente, o modificato. Rimane solo la formattazione righe alterne.
Tabelle strutturate
Mettiamo in evidenza il diverso comportamento del comando Σ automatica (scheda Home e Funzioni), se attivato quando l’intervallo dati è strutturalmente una tabella oppure no
Nel primo caso la funzione sarà Subtotale la cui sintassi è SUBTOTALE(num_funzione;rif1[;rif2;rif3;…])
ed il risultato sarà riferito alle sole righe provenienti da eventuali filtri applicati
Con num_funzione si indica quale operazione si richiede, ad esempio 109 significa somma, 101 media (v. elenco nella guida in linea)
Con rif1, rif2 ecc si intendono uno o più intervalli oggetto di elaborazione
Esistono due possibilità , specificate con num_funzione diversi,di considerare nel calcolo le righe nascoste (nascoste e non filtrate) 1. Da 1 ad 11 si tiene conto dei valori nascosti
2. Da 101 a 111 si ignorano i valori nascosti
Nel secondo caso la funzione sarà SOMMA, MEDIA ecc. ed il risultato sarà riferito a tutte le righe appartenenti all’intervallo indicato negli argomenti, indipendentemente da eventuali filtri applicati
30
Ordinamenti
LUMSA - Abilità Informatiche – Dispensa a 23 ottobre 2020
Per ordinare i dati in ordine crescente posizionarsi su una cella della colonna in base alla quale si devono ordinare i dati e fare clic sul pulsante
Per l’ordinamento decrescente ovviamente sarà il pulsante .
Per ordinare in base a più colonne, clic su , si dovranno definire le colonne (Ordina per), il carattere da trattare (Ordina in base a), i criteri (Ordine)
tramite la finestra di dialogo
Filtri
Sono utilizzati per evidenziare solo una parte dei dati oggetto di analisi, secondo criteri specificati, nascondendo le righe che non soddisfano tali criteri posti alla base del filtro.
Posizionandosi su una qualsiasi delle celle e attivando il comando
compaiono nelle celle contenenti le intestazioni le caratteristiche frecce a discesa, come visto per le tabelle strutturate
Clic sulla freccia della colonna in base alla quale si vuole costruire il filtro
Nella finestra sono proposti tutti i dati contenuti nella colonna in oggetto;
sarà possibile, selezionando le opportune caselle di controllo, relative ai dati che si vogliono estrarre, definire il criterio sulla base dei valori.
Sarà altresì possibile, tramite la voce di menu “Filtri per numeri” accedere alla scelta ulteriore (con gli stessi criteri della formattazione condizionale).
32
Filtri
LUMSA - Abilità Informatiche – Dispensa a 23 ottobre 2020
Scegliendo filtro personalizzato
Si indicherà l’operatore di confronto ed il valore da utilizzare per il confronto
Se si vuole aggiungere un secondo criterio si dovrà anche indicare se i due criteri devono essere
soddisfatti contemporaneamente, si selezionerà operatore logico booleano AND, se possono essere soddisfatti o solo l’uno, o solo l’altro o anche
ambedue si selezionerà OR
Se la colonna nella quale si applica il
filtro contiene dati diversi come ad
esempio alfanumerici, data, ecc la
finestra di dialogo conterrà criteri e
specifiche compatibili con essi
Formattazione condizionale
È possibile cambiare l’aspetto delle celle sulla base dei dati in esse contenuti
Nella figura precedente è riportato il menu principale con la visualizzazione del sottomenu relativo alla voce “Regole evidenziazione celle”. Si può vedere come sia semplice indicare le regole in base alle quali attribuire un formato scelto tra i tanti a disposizione.
34
Formattazione condizionale
LUMSA - Abilità Informatiche – Dispensa a 23 ottobre 2020