schema riassuntivo del
corso di
excel avanzato
Materiale prodotto da Domenico Saggese unicamente come supporto per
i suoi corsi. E’ lecito trattenerne una copia per uso personale; non è
autorizzato alcun uso commerciale o in corsi non tenuti od organizzati da
Domenico Saggese salvo autorizzazione scritta dell’autore. In ogni caso
ogni copia deve essere riportare il nome dell’autore e questa nota.
schema riassuntivo del
corso di excel avanzato
rev. 1.2
Cosa c’è da imparare
Prima di affrontare la gestione dei dati in excel, che offre funzionalità simili a quelle di un database, occorre avere delle
buone basi in materia di: riferimenti assoluti e relativi, nomi degli intervalli, e uso delle funzioni.
www.saggese.it
www.domenicosaggese.it
Pagina 2 di 19
schema riassuntivo del
corso di excel avanzato
rev. 1.2
Riferimenti assoluti e relativi
Excel offre una importante comodità: la possibilità di generare formule simili, ma non uguali, ad una già esistente.
Questo è indispensabile per evitare errori e per non perdere tempo (scrivere 500 formule occuperebbe lo stesso tempo
che inserire 500 operazioni in una calcolatrice da tavolo)
Nell’esempio di sinistra, la formula A3 * B3 diventa correttamente A4 * B4 , A5* B5 e così via
Nell’esempio di destra, la formula = f3 * i1 , che sarebbe giusta, darebbe i “discendenti” sbagliati :
=f4*i2,
=f5*i3... Etc
Per spiegare ad EXCEL che nel riferimento i1 la cifra 1 non deve mai cambiare, la scriveremo:
=F3 * i$1.
I “discendenti” saranno tutti corretti
Il riferimento A1 può essere scritto:
• A1 (relativo)
• $A$1 (assoluto)
• A$1 o $A1 (misto)
www.saggese.it
www.domenicosaggese.it
Pagina 3 di 19
schema riassuntivo del
corso di excel avanzato
rev. 1.2
Il caso più difficile....
Per quanto possa sembrare astruso, il modello della “tavola pitagorica”, che si risolve con due riferimenti misti, uno con
$<lettera> e l’altro con $<numero> non è così improbabile.
www.saggese.it
www.domenicosaggese.it
Pagina 4 di 19
schema riassuntivo del
corso di excel avanzato
rev. 1.2
Nomi degli intervalli
E’ possibile “battezzare” un intervallo per non dovere poi utilizzare il suo riferimento assoluto:
AREA è più facile da ricordare e da leggere di $C$3:$C$14
!
PROVARE:
!
!
!
selezionare un gruppo di celle
cliccare nella casella del nome
digitare un nome (es.:area) e premere invio
Per modificare i nomi già inseriti, andare a inserisci"nome" definisci. Apparirà una maschera con tutti i nomi già
definiti, e si potranno cancellare, rinominare, modificare.
!
PROVARE:
! aprire il file del budget familiare
! selezionare l’intera tabella delle uscite
! andare a inserisci " nome " crea
Se avete selezionato correttamente, trovere gli intervalli “marzo”, “Aprile”.... ma anche “affitto”, “vitto”....
In Excel 2007:
scheda formule gruppo nomi definiti
comando crea da selezione .
www.saggese.it
www.domenicosaggese.it
Pagina 5 di 19
schema riassuntivo del
corso di excel avanzato
rev. 1.2
Funzioni
Le funzioni offerte da excel sono centinaia, e spaziano da semplici funzioni logiche, alla finanza, alla trigonometria.
Di volta in volta cercheremo nell’help di access se ci sia una funzione alla nostra bisogna e ne impareremo la sintassi
Per usare una funzione dobbiamo imparare la sua sintassi. La sintassi di una funzione si riassume in due domande:
1.
2.
Quanti e quali parametri vuole la funzione?
Che cosa restituisce?
Per esempio la funzione arrotonda vuole due parametri:
! il primo, il numero da arrotondare,
! il secondo, il numero dei decimali richiesti
La funzione restituisce il numero arrotondato. Restituisce vuol dire che sarà come se al suo posto qualcuno avesse
scritto il suo risultato.
Perciò scrivere: =arrotonda(b2;2), se b2 vale 0,774686, sarà come avere scritto 0,77
Lo stesso risultato per due vie
•
•
In cella b1 calcoliamo a1/1936,27
In cella b2 usiamo la funzione arrotonda per
arrotondare il risultato della divisione in B1
Questa forma è preferibile all’inizio, quando si sta
creando il foglio e si vuole vedere se funziona
www.saggese.it
www.domenicosaggese.it
•
La formula esegue prima la divisione, poi ne dà il risultato
in pasto alla funzione arrotonda,
Quando avete visto che funziona, scrivete una formula sola per
guadagnare spazio e chiarezza
Pagina 6 di 19
schema riassuntivo del
corso di excel avanzato
rev. 1.2
Formule con gli operatori = < >
Per capire le funzioni, occorre osservare prima queste strane formule:
!
!
!
=3=5
=3<5
=3>5
provate a digitarle in tre celle: cosa succede?
la formula =A1=”” serve a vedere ce la cella è vuota
!
PROVARE:
!
!
!
!
trovate una formula che verifica se una cella è = 0
trovate una formula che verifica se una cella è vuota
fate una tabella di divisioni in colonna
modificate affianco alla divisione, un’altra formula controlli se il divisore è zero. Darà errore comunque ma
saremo sulla buona strada.....
www.saggese.it
www.domenicosaggese.it
Pagina 7 di 19
schema riassuntivo del
corso di excel avanzato
rev. 1.2
Funzioni richiamate da funzioni
Utilizzando più funzioni, o comunque formule complesse, occorre ricordare l’algebra delle medie:
Partire dalle parentesi più interne, precedenza alle moltiplicazioni e divisioni.... E ogni funzione risolta, va sostituita con
il suo risultato!
Questa formula arrotonda i minuti al quarto d’ora inferiore:
Vediamo come ragiona:
•
• Minuto(a2)
• 40 / 15
• Int(2,666)
• 2* 15
" 40
" 2,6666
"2
" 30
int(minuto(a2))/15)*15
Int(40/15)*15
Int(2,6666)*15
2*15
INT(minuto(A2/15)*15 = 30
www.saggese.it
www.domenicosaggese.it
Pagina 8 di 19
schema riassuntivo del
corso di excel avanzato
rev. 1.2
Funzioni di ricerca
Inserendo articolo e quantità mi
escono l’unità di misura e il prezzo.
Il totale viene poi calcolato dalle
formule in colonna E
Questo intervallo si
chiama tab_articoli
La funzione cerca.vert e la funzione cerca.orizz trovano un valore in una tabella a partire da un altro
Nel nostro esempio, scrivendo “tubo grande” la funzione in C3:
! cerca “tubo grande” in tab_arti,
! si sposta alla seconda colonna di tab_arti,
! e vi trova “mt”.
L’ultimo parametro, “FALSO” significa che non deve fermarsi ad un valore simile a “tubo grande”, ma proprio al valore
ESATTO.
L’ultimo parametro può essere VERO nel caso di certi valori numerici come per esempio cercando scaglioni di reddito
per calcolare l’IRPEF....
La formula dell’esempio scriverà un “N/D” per ogni riga senza articolo, per evitare questo si può modificarla , da
=CERCA.VERT(A3;tab_articoli;2;FALSO) a:
= SE(a3=“”;””; CERCA.VERT(A3;tab_articoli;2;FALSO)).
!
PROVARE:
!
!
!
!
!
!
copia in un foglio di excel i dati della figura
“battezza” la tabella di destra (G2:I14) con il nome tab_articoli
inserisci la formula = cerca.vert(A3;tab_articoli;2;FALSO) nella cella c3
inserire la formula =b3*D3 nella cella E3
riempire le due colonne C ed E delle formule appena inserite
digitare un articolo in colonna A e una quantità in colonna B
www.saggese.it
www.domenicosaggese.it
Pagina 9 di 19
schema riassuntivo del
corso di excel avanzato
rev. 1.2
Excel e i dati
Excel tiene conto delle nostre tabelle ordinate:
!
!
!
!
Le riconosce come tali (quasi sempre):
capisce se c’è una riga con i nomi dei campi
ed è disponibile ad ordinare la tabella per i “campi” definiti da noi;
svolge altre funzioni degne di un database ....
Ordinamento
Si può chiedere ad excel di ordinare il nostro elenco: con i bottoni di ordinamento
dati" ordina
!
oppure con il comando
PROVARE:
•
creare un elenco basato su tre colonne: nome,
cognome, città
•
riempirlo con una decina di nomi a fantasia
•
ordinarlo per nome o per cognome o per città
usando i bottoni :
•
poi provare con il comando dati" ordina
In Excel 2007:
scheda dati gruppo ordina e filtra
comandi: A-Z / Z-A / ordina .
Inoltre in Excel 2007 si può ordinare per più
di tre campi..
www.saggese.it
www.domenicosaggese.it
Pagina 10 di 19
schema riassuntivo del
corso di excel avanzato
rev. 1.2
Normalizzazione
La “normalizzazione” è un concetto fondamentale per un database (excel NON è un database, ma si comporta in modo
simile); esistono complicate teorie e regole della normalizzazione, ma per noi basta questa definizione:
Normalizzazione: Scrivere ogni informazione in un solo modo, digitandola una sola volta,e possibilmente in un solo
posto,
Es: NON si dovrebbero vedere nella stessa tabella:
! Mandello del Lario
! Mandello Lario
! Mandello
Lario
! Mandello L.
Se io ho una struttura normalizzata, scriverò una sola volta il nome di questo paese, poi lo potrò “richiamare” e non
digitare io interamente. Come avverrà questo “richiamare”? in excel ho due possibilità: il riempimento automatico e la
convalida.
Excel non è un database soprattutto perché non lavora direttamente su disco, record per record, ma lavora in ram e poi
salva successivamente
www.saggese.it
www.domenicosaggese.it
Pagina 11 di 19
schema riassuntivo del
corso di excel avanzato
rev. 1.2
Riempimento automatico
Se scriviamo un nome che è già apparso nella colonna, ci aiuta a completarlo, per facilitare la normalizzazione;
(appena premuta la S....)
Convalida
La funzione “convalida” (menù dati " convalida) offre una normalizzazione ancora più stretta: si può pretendere che
siano inseriti solo:
1. numeri,
In Excel 2007:
2. un numero entro certi valori
scheda dati gruppo strumenti dati
3. scegliendo “elenco” si può forzare ad accettare solo
comando convalida dati .
valori presenti in un’altra tabella (= area rettangolare)
purché dello stesso foglio
Scelgo “elenco” dal box di
comando “convalida”....
Seleziono l’area con i nomi
degli autori...
Il risultato.....
www.saggese.it
www.domenicosaggese.it
Pagina 12 di 19
schema riassuntivo del
corso di excel avanzato
rev. 1.2
Filtro automatico e filtro automatico personalizzato
!
PROVARE:
In Excel 2007:
scheda dati gruppo ordina e filtra
comando filtro (icona imbuto).
•
creare un elenco in un foglio di excel
•
cliccare in una delle celle dell’elenco
•
, cliccare DATI" filtro automatico
•
provare a filtrare la tabella in vari modi
•
•
Mentre la cella attiva è una delle celle di una tabella
Nei menù che ne risultano, è possibile scegliere personalizza, e da qui indicare dei criteri più flessibili:
maggiore di, minore di.... inoltre si possono combinare due criteri sullo stesso campo sia con l’AND che con
l’OR
www.saggese.it
www.domenicosaggese.it
Pagina 13 di 19
schema riassuntivo del
corso di excel avanzato
rev. 1.2
filtro avanzato
In Excel 2007:
scheda dati gruppo ordina e filtra
comando avanzate .
Il filtro avanzato permette di:
! Conservare i criteri sul foglio stesso
! Utilizzare criteri multipli con l’or su più campi
! Scrivere i record filtrati in un’altra posizione (scegliendo copia in un’altra posizione)
www.saggese.it
www.domenicosaggese.it
Pagina 14 di 19
schema riassuntivo del
corso di excel avanzato
rev. 1.2
Funzione subtotale
Calcoli su dati filtrati
Uno degli utilizzi più comodi della funzione subtotale è con i filtri: consultare i filtri e vedere un totale (o una media...)
dei soli dati filtrati è particolarmente comodo.
www.saggese.it
www.domenicosaggese.it
Pagina 15 di 19
schema riassuntivo del
corso di excel avanzato
rev. 1.2
Raggruppa e struttura
Possiamo decidere manualmente quali righe o colonne raggruppare:
Nel nostro esempio, selezionando le tre colonne Nord, centro e Sud, e scegliendo Dati" raggruppa e struttura "
struttura, possiamo fare scomparire le tre colonne, lasciando solo il totale
In Excel 2007:
scheda dati gruppo struttura
comando raggruppa .
www.saggese.it
www.domenicosaggese.it
Pagina 16 di 19
schema riassuntivo del
corso di excel avanzato
rev. 1.2
Subtotali
Con il comando subtotali, (non è necessario selezionare tutta la tabella, ma è sufficiente selezionarne una cella) si
impostano automaticamente:
1. I subtotali
2. Una struttura raggruppata
In Excel 2007:
scheda dati gruppo struttura
comando subtotale .
www.saggese.it
www.domenicosaggese.it
Pagina 17 di 19
schema riassuntivo del
corso di excel avanzato
rev. 1.2
Dati esterni: query su database
E’ possibile importare dati in excel da veri e propri database per analisi, statistiche e report.
Per importare dati occorre:
! scegliere il comando dati" importa dati esterni" nuova query su database
! indicare ad excel qual è il database
! indicare qual è la tabella
! scegliere se importare tutto o solo alcuni campi
! confermare l’inserimento in excel
L’insegnante vi mostrerà alcuni esempi
Dati esterni: query web
•
•
•
•
•
•
In Excel 2007:
scheda dati gruppo carica dati esterni
comando da altre origini per la query su DB
da web per la query web .
Andare a il comando dati" importa dati esterni" nuova query web
Appare una finestra di excel che contiene un browser internet:
aprite un sito che contenga una tabella (nell’esempio, un negozio di vendita online)
cercate la tabella che vi interessa: (le tabelle sono marcate con una freccia nera e gialla come questa #, ma non
tutte le “tabelle” di una pagina web sono tabelle di dati)
cliccate sulla freccia ( # ) della tabella che volete importare, diventerà un segno di spunta $
e poi cliccate importa
www.saggese.it
www.domenicosaggese.it
Pagina 18 di 19
schema riassuntivo del
corso di excel avanzato
rev. 1.2
Tabella pivot
Immaginiamo di avere una tabella ordinata e incolonnata, del tipo:
articolo
vite
bullone
chiodo
vite
bullone
produttore
ALFA
BETA
BETA
BETA
ALFA
Prezzo
Etc
etc
E immaginiamo che lo stesso articolo sia fornito da più produttori: vorremmo avere una tabella diversa con gli stessi
dati, per esempio:
articolo
ALFA
BETA
vite
(prezzo di alfa....)
(Prezzo di BETA)
bullone
(prezzo di alfa....)
(Prezzo di BETA)
Questo si può fare con lo strumento tabella pivot di excel.
! cliccare in una cella della tabella
! scegliere dati" rapporto tabella pivot e grafico pivot
! nella creazione guidata, confermare l’origine dati
! nella creazione guidata, indicare se volete la tabella in un nuovo foglio o in uno esistente (nel dubbio, indicate
nuovo)
! trascinate con il mouse i campi dalla finestrina elenco campi tabella pivot all’area della tabella pivot: scegliere
per ogni campo se volete che si tratti di:
o un campo colonna
In Excel 2007:
o un campo riga
scheda inserisci gruppo tabelle
o un campo dati
comando tabella pivot .
o un campo pagina
L’insegnante vi mostrerà diversi esempi di tabella pivot, e di come una volta creata possa essere modificata.
www.saggese.it
www.domenicosaggese.it
Pagina 19 di 19