Excel avanzato - Guida Computer

USO DI EXCEL COME DATABASE
• Le funzionalità di calcolo intrinseche di un
foglio di lavoro ne fanno uno strumento
eccellente per registrarvi pochi dati essenziali,
elaborarli con formule di vario tipo e ricavarne
molte informazioni.
• DATABASE: Con questo nome si caratterizza
una qualsiasi raccolta di dati omogenei,
conservati su un supporto di registrazione in
una forma strutturata.
1
COM’È FORMATO UN DATABASE
• In un database i dati sono organizzati secondo
una gerarchia di contenitori che si chiamano
campi, record e tabelle.
• Mettendo insieme i campi di una singola riga si
ottiene un record, che corrisponde alla riga
stampata. L’insieme delle righe o record forma
la tabella.
• Un altro elemento qualificante è l’omogeneità
dei contenuti.
2
• I creatori di Excel hanno messo a punto una
serie di strumenti specializzati per agevolare
l’uso di questo programma come gestore di
database. Vedremo in questo capitolo quali
sono questi strumenti e come si possono
utilizzare in concreto.
3
DATI → ORDINA
Ordinare i record
• Supponiamo di voler riorganizzare i record
ordinati secondo il nome del cliente.
Selezioniamo con un clic il campo Cliente di
un qualunque record del database e
selezioniamo il comando Ordina dal menu
Dati.
• L’intera area del foglio di lavoro che contiene
il database viene selezionata
automaticamente e su essa si visualizza una
finestra di dialogo.
• Questa finestra di dialogo permette di
definire i criteri per riordinare i record che
compongono il database.
4
• Quando viene attivata
nella prima casella di
selezione, intitolata
Ordina per, compare il
nome del campo che era
selezionato quando è stato
dato il comando Ordina.
• A fianco, due pulsanti di
opzione, denominati
Crescente e Decrescente,
consentono di scegliere se
l’elenco dei record andrà
ordinato disponendo i
record secondo Crescente
o Decrescente.
5
• La finestra di dialogo Ordina consente di
concatenare in questo modo fino a tre
criteri di ordinamento, attivandoli in un
colpo solo. Per farlo si utilizzano (dopo la
casella di selezione a discesa Ordina per)
una o entrambe le caselle di selezione
che vengono subito sotto e sono
denominati Quindi per.
6
DATI → FILTRO…
Filtrare i record
• Quando un database contiene centinaia o
migliaia di record, l’ordinamento non risolve
sempre tutti i problemi di selezione.
Sarebbe utile poter evidenziare soltanto i
record che hanno certi valori in determinati
campi.
• Per soddisfare esigenze di selezione di
questo genere è disponibile un altro
comando specifico per i database, che si
trova sempre nel menu Dati: il comando
Filtro, dal quale si possono selezionare
Filtro automatico e Filtro avanzato.
7
• Quando si attiva Filtro automatico si nota
che le celle che contengono i nomi dei campi
sono state trasformate in altrettante caselle
di selezione a discesa, più o meno in questo
modo:
• Facendo clic sul pulsante a freccia si fa
scendere un elenco di criteri di selezione
associabili al campo stesso. Un clic su uno di
questi criteri lo attiva e vengono visualizzati
soltanto i record del database che
soddisfano il criterio selezionato per quel
campo.
8
• I criteri disponibili sono numerosi e
articolati. Cominciamo dai più semplici:
◙ tutto, seleziona tutti i record;
◙ vuote, seleziona i record nei quali il
campo selezionato è vuoto;
◙ non vuote, seleziona i record nei quali il
campo selezionato non è vuoto.
9
• Quando un database viene filtrato, i
numeri delle righe corrispondenti ai record
che soddisfano le condizioni del filtro
compaiono scritti in azzurro e vengono
nascoste le altre righe. Questo
accorgimento segnala che ciò che si sta
osservando è soltanto una parte del
database. Per tornare a vedere tutti i
record si seleziona il criterio Tutto
nell’elenco a discesa.
10
• “Primi 10…”
• È un criterio potentissimo, di notevole
effetto: infatti ordina in memoria i record,
senza spostarli fisicamente nel foglio di
lavoro, usando come chiave di
ordinamento il campo selezionato e
visualizza soltanto i record che in
quell’ordinamento occupano le prime
dieci posizioni.
11
• Il criterio Primi 10… serve in realtà
per filtrare non i primi 10 record,
ma quanti se ne vuole (1 o n,
purché siano meno del totale dei
record del database) visualizzando
quelli che – se fossero messi in
ordine in base al campo selezionato
– occuperebbero le prime (n)
posizioni o le ultime (n).
12
• Molto più a misura d’uomo si presenta
l’opzione Personalizza, che permette di
definire un criterio di filtro personalizzato.
Il criterio si costruisce attraverso una
finestra di dialogo intitolata Personalizza
filtro automatico, che vediamo qui di
seguito:
13
• In questa finestra di dialogo si possono
indicare uno o due criteri di filtro,
collegandoli, se sono due, con un operatore
logico And (E) oppure Or (O). L’operatore di
confronto per il campo selezionato può
essere scelto fra quelli elencati nella casella
di selezione a sinistra, che presenta i simboli
=, >, <, >=, <=, e <> quando si fa clic sul
pulsante a freccia.
• Il termine di confronto per il contenuto del
campo sul quale si basa il filtro può essere
immesso direttamente nella casella di
selezione a destra, o scelto fra quelli elencati
nella casella stessa facendo scendere la lista
con il pulsante freccia.
14
DATI → MODULO
Gestire i record
• Quando in un foglio di lavoro esiste una
struttura caratterizzata come database,
Excel mette a disposizione un ottimo e
comodo strumento che si chiama Modulo
(menu Dati).
• Come dice il suo nome, con questo
comando si crea un modulo, cioè un insieme
strutturato di caselle di modifica, che
presenta in una forma chiara e organizzata
tutti i campi di record, allo scopo di
agevolare la gestione del database: creare
nuovi record, eliminare record esistenti o
modificare il contenuto dei campi di un
record determinato.
15
• Il modulo si presenta
come una finestra di
dialogo, intitolata col
nome del foglio in cui si
trova il database.
• Come si può notare, non
a tutti i nomi corrisponde
una casella di modifica.
Per i campi calcolati,
quelli cioè che
contengono formule,
viene visualizzato il
risultato della formula,
ma non è previsto che vi
si acceda per modificarli.
16
DATI → SUBTOTALI
Analizzare un database
• Oltre a agli strumenti per la gestione ne
esistono altri, finalizzati all’analisi dei dati
contenuti in un database. Ne esaminiamo
uno, chiamato Subtotali, di notevole potenza
e semplicità d’uso.
• Quando si lavora su un database i cui record
contengono campi con valori numerici, come
un database di fatture, può sicuramente far
comodo calcolare uno o più totali parziali e,
perché no, anche un totale generale.
• È esattamente quello che si può ottenere con
il comando Subtotali, che si trova nel menu
Dati.
17
• Per poter usare questo comodo strumento,
bisogna prima ordinare i record in base al
campo di riferimento per i totali parziali da
calcolare: se vogliamo ottenere il totale
degli imponibili per ciascun cliente bisogna
ordinare il database sul campo Cliente e
cosi via.
18
• Le opzioni disponibili in questa finestra di
dialogo sono molto semplici e intuitive:
◙ nella prima casella di selezione a discesa si
sceglie il campo di riferimento, fra i presenti
nei record;
◙ il campo che contiene i dati da aggregare si
sceglie nella terza casella di selezione a
discesa, quella intitolata Aggiungi subtotali a
(è possibile scegliere più di un campo
impostando con un clic il segno di spunta
accanto al nome del campo);
◙ la casella di selezione che sta al centro,
intitolata Usa la funzione, presenta un elenco
di funzioni matematiche utilizzabili per
aggregare i dati.
19
…I vantaggi della struttura
• Probabilmente, l’aspetto più
interessante del foglio di lavoro, dopo
l’esecuzione del comando Subtotali,
non sta tanto nei risultati aritmetici
quanto nel modo in cui vengono
presentati.
20
STRUMENTI → RICERCA OBIETTIVO
Ricerca di un obiettivo
• Excel prevede diversi strumenti per
l'analisi dei dati contenuti nei fogli di
lavoro. Tra essi assumono importanza
fondamentale il comando Strumenti,
Ricerca obiettivo. Il comando Ricerca
obiettivo permette di trovare il valore
che genera il risultato desiderato in una
formula.
• È disponibile la Ricerca obiettivo sul
foglio di lavoro e la Ricerca obiettivo
con i grafici.
21
• Quando si desidera ottenere un
determinato risultato da una formula,
modificando uno dei valori che la formula
usa, ricorriamo alla funzione Ricerca
obiettivo, specificando il risultato che si
intende ottenere e quindi la cella che deve
essere modificata per ottenere quel
risultato.
• Quando si sceglie il comando Strumenti,
Ricerca obiettivo, la cella da variare deve
contenere un valore (non una formula) e
questo valore deve essere usato dalla
cella in cui si desidera ottenere il
Particolare risultato
22
La funzione Ricerca Obiettivo:
Una volta trovata una soluzione, cliccare su
OK per sostituire i valori originali con i nuovi
valori o cliccare su Annulla per mantenere gli
originali.
23
STRUMENTI → RICERCA OBIETTIVO
Ricerca obiettivo nei Grafici
• Anche da un grafico è possibile trovare una
soluzione del tipo descritto sopra. Si deve
trattare di un grafico bidimensionale a
barre, lineare o istogramma. Se si trascina
un indicatore del grafico (rappresentate
una formula) in una nuova posizione,
affinché assuma un valore diverso,
compare la finestra di dialogo Ricerca
obiettivo, si apre il foglio di lavoro
contenente i dati del grafico ed è possibile
indicare quali celle modificare per ottenere
il nuovo valore definito modificando
l'indicatore.
24
• Per trovare una soluzione definita
graficamente, procedere come segue:
1. Aprire il foglio di lavoro ed il grafico su cui
intervenire. Attivare il grafico.
2. Cliccare sull'indicatore di cui variare il
valore (colonna, barra o simbolo di linea),
per selezionare l'intera serie. Quindi,
cliccare di nuovo sull'indicatore, per
selezionare quell'indicatore specifico.
Compaiono alcune maniglie nere.
3. Trascinare le maniglie nere per spostare
l'indicatore, assegnando ad esso un nuovo
valore.
25
• 4. Variare eventualmente il numero nella
casella Al valore. La casella Imposta la cella
contiene la cella del foglio di lavoro
rappresentata dall'indicatore del grafico.
• 5. Nella casella di testo Cambiando la cella,
digitare il riferimento della cella da variare.
26
INSERISCI → FUNZIONE…
FUNZIONI CONDIZIONALI
• Si intende per funzione condizionale una
funzione il cui risultato sia dipendente dal
verificarsi o meno di una o più condizioni.
• Le funzioni condizionali che tratteremo sono:
◙ SE (visualizzare un valore che dipende da
criteri)
◙ SOMMA.SE (sommare tutti i valori che
soddisfano criteri specifici)
◙ CONTA.SE (contare tutti i valori che
soddisfano criteri specifici)
◙ E, O (per abbinare in AND e OR più condizioni).
27
SE(test; se_vero; se_falso)
• SE un'affermazione è vera, ALLORA restituisci
questo primo valore (se_vero). ALTRIMENTI
restituisci questo secondo valore (se_falso).
• La funzione SE può essere nidificata, vale a
dire inserita all'interno di altre funzioni SE.
Ogni funzione può essere nidificata in
un'altra, fino a sette livelli di profondità.
•
L’esempio seguente mostra una formula che
restituisce, nella cella stessa, “POSITIVO” o “NON
POSITIVO” a seconda del valore contenuto in A1.
=SE(A1>0;”POSITIVO”;”NON
POSITIVO”)
28
SOMMA.SE(interv; criter; som)
• Somma le celle specificate secondo un criterio
assegnato.
• interv è l'intervallo di celle che si desidera
calcolare.
• criter è il criterio in forma di numero,
espressione o testo che determina le celle che
verranno sommate. Ad esempio, criterio può
essere espresso come 32, "32", ">32",
"mele".
• som sono le celle da sommare. Le celle in
somma vengono sommate solo se le celle
corrispondenti in intervallo soddisfano i criteri.
Se somma è omesso, verranno sommate le
celle in intervallo.
29
Esempio
• Si supponga che l'intervallo A1:A4
contenga rispettivamente i seguenti valori
patrimoniali relativi a quattro abitazioni: €
50.000, € 100.000, € 150.000, € 200.000.
• L'intervallo B1:B4 contiene le seguenti
commissioni sulle vendite su ciascuno dei
corrispondenti valori patrimoniali: € 3.500,
€ 7.000, € 10.500, € 14.000.
=SOMMA.SE(A1:A4; ">80000";
B1:B4)
è uguale a € 31500
30
CONTA.SE(interval; criteri)
• Conta il numero di celle in un intervallo che
soddisfano i criteri specificati.
• interval è l'intervallo di celle a partire dal
quale si desidera contare le celle.
• criteri sono i criteri in forma di numeri,
espressioni o testo che determinano quali
celle verranno contate. Ad esempio, criteri
può essere espresso come 32, "32", ">32",
"mele".
31
Esempi
• Si supponga che l'intervallo A3:A6 contenga
rispettivamente la voci "mele", "arance",
"pesche", "mele".
• CONTA.SE(A3:A6;"mele") è uguale a
2
• Si supponga che l'intervallo B3:B6 contenga
rispettivamente i valori 32, 54, 75, 86.
• CONTA.SE(B3:B6;">55") è uguale a 2
32
E, O
E
• Restituisce VERO se tutti gli argomenti hanno
valore VERO e restituisce FALSO se uno o più
argomenti hanno valore FALSO.
•
•
•
•
•
•
Esempi
E(2+2=4; 2+3=5) è uguale a VERO
Se l'intervallo B1:B3 contiene VERO, FALSO e VERO:
E(B1:B3) è uguale a FALSO
Se B4 contiene un numero compreso tra 1 e 100:
E(1<B4; B4<100) è uguale a VERO
33
O
• Restituisce VERO se uno o più argomenti
hanno valore VERO e restituisce FALSO se
tutti gli argomenti hanno valore FALSO.
• Esempi
• O(1+1=1;2+2=5) è uguale a FALSO
• Se A1:A3 contiene i valori VERO, FALSO e VERO:
O(A1:A3) è uguale a VERO
• È possibile combinare più criteri in E e in O. Ciò si
realizza tramite le funzioni E(Condiz1, … ) e
O(Condiz1, …):
=SE( E(A1>0;A1<=10); ”OK”; ”FUORI DAL RANGE”)
34
TABELLE PIVOT
• Le tabelle pivot sono strumenti analitici
e di reporting per creare tabelle
riassuntive, riorganizzare dati tramite
trascinamento, filtrare e raggruppare i
dati, rappresentare meglio i dati
graficamente. Una tabella pivot è uno
strumento di organizzazione dei dati, la
cui struttura viene determinata
dall’utente con la scelta dei campi e
degli elementi che devono apparire
nella tabella.
35
DATI → RAPPORTO TABELLA PIVOT
AUTOCOMPOSIZIONE
• Le tabelle pivot sono un “riassunto” di
una tabella esistente, quindi prima si
deve creare la tabella iniziale, in seguito
si crea il “riassunto”. Per creare una
tabella pivot si deve selezionare la
tabella di partenza e lanciare il
comando RAPPORTO TABELLA PIVOT
nel menu DATI, inizia una creazione
guidata divisa in più finestre di dialogo,
che verranno analizzate nell’esempio
seguente.
36
• Aprire il file pivot.xls e analizzare la
tabella. La tabella è troppo lunga per
poter stabilire qual è il prodotto più
venduto, quale la categoria migliore
o per fare qualsiasi altro tipo di
analisi. Lo scopo delle tabelle pivot è
proprio quello di fare analisi in
tabelle di grandi dimensioni.
Selezionare l’intera tabella; per
comodità è stato dato un nome alla
tabella, cosicché è possibile
selezionarla attraverso la casella del
nome: pivot.
37
• Dopo aver selezionato la tabella, lanciare il
comando DATI – RAPPORTO TABELLA PIVOT,
si apre la finestra di dialogo visualizzata nella
figura.
38
• Nella parte superiore si deve indicare dove
si trova la tabella di partenza, di solito
“Elenco o database Microsoft Excel”, valore
di default.
• Nella parte inferiore si deve indicare se si
intende creare una tabella riassuntiva,
“Tabella pivot” valore predefinito, o un
grafico riassuntivo.
Fare un clic sul pulsante AVANTI, si apre la
seconda finestra della creazione guidata,
figura:
39
• In questo passaggio si devono indicare le
celle della tabella di partenza, che nel
nostro caso sono state selezionate
all’inizio, quindi è sufficiente fare un clic sul
pulsante AVANTI, si apre la terza finestra
dell’autocomposizione, figura:
40
• In questa fase si deve scegliere se creare
la tabella pivot in un nuovo foglio di
lavoro o nel foglio corrente. Di solito si
preferisce un nuovo foglio di lavoro.
Fare un clic sul pulsante FINE, viene
creato un nuovo foglio di lavoro, diverso
dagli altri, figura:
41
• Questo foglio di lavoro non è la tabella
pivot, ma è lo schema che serve per
crearla. Si devono trascinare i nomi dei
campi negli appositi spazi.
• Portare il puntatore del mouse sopra la
barra degli strumenti “Tabella pivot”, in
corrispondenza del nome del campo di
interesse. Premere e tenere premuto il
pulsante sinistro del mouse e trascinare il
campo all’interno dell’area “Rilasciare
qui…”. Mollare il pulsante sinistro del
mouse, si vedrà il nome del campo scritto
all’interno dell’area utilizzata.
42
• Quello che si è ottenuto è il riassunto della
tabella iniziale. Nella tabella pivot è molto più
semplice analizzare i dati.
È possibile trascinare più campi in ogni area
del foglio, con una sola limitazione: nell’area
“Rilasciare qui i dati” si devono trascinare solo
campi che contengono valori numerici.
43
…alternativa con il layout
• Nella Creazione Guidata Tabella Pivot fare un
clic sul pulsante LAYOUT, si apre la finestra di
dialogo visualizzata nella figura:
44
DATI → ! AGGIORNA DATI
• In alto ci sono le spiegazioni del
programma.
Nella parte inferiore c’è la stessa
struttura del foglio tabella pivot, visto
prima.
• Modificando i valori della tabella iniziale
non si cambiano anche i valori della
tabella pivot. Le tabelle pivot sono una
“fotografia” di una situazione in una certa
data. Quindi, per aggiornare i dati della
tabella pivot si deve utilizzare il pulsante
AGGIORNA della barra degli strumenti
PIVOT.
45