© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Excel Avanzato
ANALIZZARE I DATI
Giorgio Porcu - Excel Avanzato
1
SOMMARIO
ANALIZZARE I DATI
Giorgio Porcu - Excel Avanzato
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Scenari
Ricerca obiettivo
Risolutore
Tabella pivot
Grafico pivot
Funzioni di ricerca e riferimento
Funzioni di database
Verifica formule
Finestra controllo celle
2
ANALIZZARE
DATI
•
•
•
•
•
•
•
•
•
Giorgio Porcu - Excel Avanzato
3
ANALIZZARE
DATI
• Quando si imposta una formula basata su una
serie di dati variabili, può essere utile
valutarne il risultato a seconda dei possibili
valori assunti da questi ultimi
• Un insieme di ipotetici valori di dati in una
formula di Excel si chiama scenario
• Excel consente di gestire differenti scenari,
simulandone il comportamento
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Scenari
Scenari
Inserire uno scenario
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Per inserire uno scenario:
1. Inserisci in una cella una formula con dati variabili
2. Scheda Dati > Strumenti dati >
Analisi di simulazione > Gestione scenari…
Giorgio Porcu - Excel Avanzato
4
ANALIZZARE
DATI
Appare la Finestra di dialogo
Gestione Scenari
Scenari
Inserire uno scenario
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
3. Premi Aggiungi
4. Nella nuova Finestra di dialogo
Aggiungi Scenario inserisci i
parametri desiderati
Nome Scenario
Giorgio Porcu - Excel Avanzato
5
ANALIZZARE
DATI
Dati variabili
Scenari
Gestire uno scenario
1. Inserisci in una cella una formula con dati variabili
2. Inserisci gli scenari
3. Dalla Finestra di dialogo
Gestione Scenari
seleziona Riepilogo…
o Mostra
Giorgio Porcu - Excel Avanzato
6
ANALIZZARE
DATI
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Per simulare uno o più scenari:
Giorgio Porcu - Excel Avanzato
7
ANALIZZARE
DATI
• Di solito le formule in Excel rappresentano
calcoli di cui cerchiamo il risultato al variare
dei dati
• Talvolta conosciamo già in partenza il risultato
di un calcolo e vorremmo risalire al valore
incognito di un dato
• La ricerca obiettivo consente di trovare i dati
che realizzano un certo risultato
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Ricerca obiettivo
Ricerca obiettivo
Utilizzare la ricerca obiettivo
1. Inserisci in una cella una formula con dati variabili,
di cui uno almeno uno incognito
2. Scheda Dati > Strumenti dati >
Analisi di simulazione > Ricerca obiettivo…
Giorgio Porcu - Excel Avanzato
8
ANALIZZARE
DATI
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Per utilizzare la ricerca obiettivo:
Ricerca obiettivo
Utilizzare la ricerca obiettivo
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Appare la Finestra di dialogo
Ricerca Obiettivo
3. Inserisci nella finestra di dialogo i dati richiesti:
Cella contenente la Formula
Valore noto della Formula
Giorgio Porcu - Excel Avanzato
9
ANALIZZARE
DATI
Dato incognito
Giorgio Porcu - Excel Avanzato
10
ANALIZZARE
DATI
• Per risolvere problemi di ottimizzazione, Excel
mette a disposizione un componente aggiuntivo
detto risolutore (solver)
• L’ ottimizzazione è una branca della matematica
che studia la soluzione ottimale (migliore o
peggiore) a problemi con una serie di vincoli
• Il risolutore sfrutta in modo automatico un
algoritmo di ottimizzazione per produrre la
soluzione ottimale
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Risolutore
Risolutore
Installare il risolutore
1. Pulsante Office > Opzioni di Excel > Componenti
aggiuntivi
2. Nella casella Gestisci seleziona Componenti
aggiuntivi di Excel e fai clic su Vai.
Giorgio Porcu - Excel Avanzato
11
ANALIZZARE
DATI
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
• Per installare il risolutore:
Risolutore
A installazione avvenuta il comando Risolutore sarà
disponibile nel gruppo Analisi della Scheda Dati.
Giorgio Porcu - Excel Avanzato
12
ANALIZZARE
DATI
3. Nella casella Componenti aggiuntivi disponibili
seleziona Componente aggiuntivo Risolutore e fai
clic su OK.
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Installare il risolutore
Risolutore
Utilizzare il risolutore
1. Inserisci in una cella la Funzione Obiettivo da ottimizzare
2. Scheda Dati > Analisi > Risolutore
3. Nella finestra Parametri del Risolutore seleziona le opzioni
desiderate
Cella contenente
la Funzione Obiettivo
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
• Per utilizzare il risolutore:
Dati variabili
Giorgio Porcu - Excel Avanzato
13
ANALIZZARE
DATI
Vincoli
• Per visualizzare in modo più chiaro dati e risultati
relativi a una tabella può essere utile ridisporre i
vari campi
Ad esempio:
 Campi colonna potrebbero divenire righe
 Dati in riga potrebbero ridisporsi in colonna
 Dati in riga/colonna potrebbero diventare incroci di
riga/colonna nella nuova visualizzazione
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Tabella pivot
Giorgio Porcu - Excel Avanzato
14
ANALIZZARE
DATI
• Le tabelle pivot consentono di riposizionare i
campi e riorganizzare automaticamente i dati per
ottenere una differente vista sulla tabella
Tabelle pivot
Creare una tabella pivot
1. Inserisci una tabella dati o posizionati su una già
esistente
2. Scheda Inserisci > Tabelle > Tabella pivot
> Tabella pivot
Giorgio Porcu - Excel Avanzato
15
ANALIZZARE
DATI
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Per creare una tabella pivot:
Giorgio Porcu - Excel Avanzato
16
ANALIZZARE
DATI
• Excel consente di creare grafici basati sui dati
delle tabelle pivot, disegnandoli in parallelo
alla creazione della nuova tabella
• Un grafico con queste caratteristiche è detto
grafico pivot
• Come le tabelle cui sono collegati, i grafici
pivot consentono una vista (in tal caso grafica)
personalizzata sui dati della tabella originaria
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Grafico pivot
Grafico pivot
Creare un grafico pivot
1. Inserisci una tabella dati o posizionati su una già
esistente
2. Scheda Inserisci > Tabelle > Tabella pivot
> Grafico pivot
Giorgio Porcu - Excel Avanzato
17
ANALIZZARE
DATI
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Per creare una tabella pivot accompagnata da
un grafico pivot:
Funzioni di ricerca e riferimento
Funzioni in Excel
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
• Excel dispone di un’ampia libreria di funzioni
suddivise in categorie
• Una funzione è un comando che esegue uno
specifico lavoro
 Utilizza una serie di dati iniziali (argomenti)
 Restituisce un valore (risultato)
 E’ inserita all’interno di una formula
Giorgio Porcu - Excel Avanzato
18
ANALIZZARE
DATI
 Segue una precisa sintassi
Funzioni di ricerca e riferimento
Funzioni di ricerca
 Forniscono strumenti utili per localizzare e
analizzare dati
 Consentono di automatizzare e personalizzare la
ricerca di dati in tabelle
L’uso delle funzioni di ricerca è
un’alternativa avanzata ai comandi
standard di ricerca disponibili in
Scheda Home > Modifica > Trova e seleziona
Giorgio Porcu - Excel Avanzato
19
ANALIZZARE
DATI
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
• Le funzioni di ricerca e riferimento:
Funzioni di ricerca e riferimento
Funzioni di ricerca
Le principali funzioni di ricerca sono:
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
• CERCA(valore;vettore)
Cerca valore in un vettore riga o colonna
• CERCA.ORIZZ(valore;matrice;indice;esatta)
Cerca valore in un vettore riga
• CERCA.VERT(valore;matrice;indice;esatta)
Cerca valore in un vettore colonna
Giorgio Porcu - Excel Avanzato
20
ANALIZZARE
DATI
Ciascuna di esse restituisce un’opportuno risultato
per la ricerca
Funzioni di ricerca e riferimento
Inserire funzioni di ricerca
1. Seleziona la cella desiderata
2. Scheda Formule > Libreria di funzioni
> Ricerca e riferimento
Giorgio Porcu - Excel Avanzato
21
ANALIZZARE
DATI
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Per inserire una funzione di ricerca:
Funzioni di ricerca e riferimento
Le funzioni di ricerca operano su gruppi di dati detti
vettori.
• Un vettore è un insieme di celle consecutive
A
contenenti dati (elementi). Esistono:
2
44
11
44
58
D
E
 Vettori colonna
F
• Un vettore è ordinato se i suoi elementi sono in
ordine per la tipologia di dati che rappresentano
2
4
11
44
Giorgio Porcu - Excel Avanzato
58
22
ANALIZZARE
DATI
 Vettori riga
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Vettori
Funzioni di ricerca e riferimento
Alcune funzioni di ricerca sfruttano gruppi di dati detti
matrici.
• Una matrice è un insieme di vettori (riga/colonna)
contigui della stessa dimensione
Nome
Cognome
DataNascita
Sesso
Residenza
Mario
Rossi
23/01/1965
M
Milano
Carla
Bianchi
08/05/1978
F
Roma
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Matrici
Giorgio Porcu - Excel Avanzato
23
ANALIZZARE
DATI
La tabella è un tipico esempio di matrice
• Una matrice si dice ordinata rispetto a un campo se i
suoi elementi in quel campo sono in ordine
Funzioni di ricerca e riferimento
Avvertenze d’uso
 Le tabelle sono matrici
• Per ottenere risultati corretti le funzioni di ricerca
devono operare su vettori:
 Preventivamente ordinati
 Contenenti dati dello stesso tipo
Giorgio Porcu - Excel Avanzato
24
ANALIZZARE
DATI
 Dati su righe o colonne sono vettori
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
• In pratica in un foglio Excel:
Funzioni di ricerca e riferimento
CERCA()
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
• CERCA(valore;vettore)
Cerca valore in vettore riga o colonna
Restituisce:
 valore se la ricerca ha esito positivo
 Il primo dato in vettore più piccolo di valore in caso
contrario
• E’ utile per ricercare valori in riga o in colonna
44
4
11
44
58
Giorgio Porcu - Excel Avanzato
25
ANALIZZARE
DATI
2
Funzioni di ricerca e riferimento
CERCA.ORIZZ()
Giorgio Porcu - Excel Avanzato
26
ANALIZZARE
DATI
Cerca valore nella prima riga di matrice.
Se la ricerca ha esito positivo, restituisce il dato della
riga indice di matrice posto sulla stessa colonna di
valore
• E’ utile per ricercare valori in riga
• Consente di restituire dati correlati a quello cercato
appartenenti a righe attigue
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
• CERCA.ORIZZ(valore;matrice;indice;esatta)
Funzioni di ricerca e riferimento
CERCA.VERT()
Giorgio Porcu - Excel Avanzato
27
ANALIZZARE
DATI
Cerca valore nella prima colonna di matrice.
Se la ricerca ha esito positivo, restituisce il dato della
colonna indice di matrice posto sulla stessa riga di
valore
• E’ utile per ricercare valori in colonna
• E’ indicata per ricerche in colonna su tabelle standard
• Consente di restituire dati correlati a quello cercato
appartenenti ai campi attigui
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
• CERCA.VERT(valore;matrice;indice;esatta)
Funzioni di database
che rispondono a certi criteri
 Supportano criteri multipli legati dai connettivi
logici AND, OR, NOT
 Sono particolarmente utili per le tabelle
L’uso delle funzioni di database è
un’alternativa avanzata ai comandi
di Filtro e Somma automatica
delle schede Home e Formule
Giorgio Porcu - Excel Avanzato
28
ANALIZZARE
DATI
 Consentono di eseguire calcoli di base sui soli dati
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
• Le funzioni di database:
Funzioni di database
Funzioni di database
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Le principali funzioni di database sono:
• DB.SOMMA(tabella;campo;criteri)
• DB.MIN(tabella;campo;criteri)
• DB.MAX(tabella;campo;criteri)
• DB.CONTA.VALORI(tabella;campo;criteri)
• DB.MEDIA(tabella;campo;criteri)
Giorgio Porcu - Excel Avanzato
29
ANALIZZARE
DATI
Ciascuna di esse restituisce un’opportuno
risultato per la ricerca
Funzioni di database
Inserire funzioni di database
Per inserire le funzioni di database:
1. Barra della formula > Inserisci funzione
2. Nella Finestra di dialogo Inserisci funzione scegli
Database dal Menu a tendina Categoria
Giorgio Porcu - Excel Avanzato
30
ANALIZZARE
DATI
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Le funzioni di database non sono
accessibili direttamente da
Scheda Home > Libreria di funzioni
Funzioni di database
DB.SOMMA()
Restituisce la somma calcolata
• E’ utile per sommare i soli valori di un campo
aventi specifiche caratteristiche
Giorgio Porcu - Excel Avanzato
31
ANALIZZARE
DATI
Calcola la somma dei soli valori nella colonna campo
di tabella che soddisfano i criteri impostati.
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
• DB.SOMMA(tabella;campo;criteri)
Funzioni di database
DB.MIN()
Trova il minimo tra i valori nella colonna campo di
tabella che soddisfano i criteri impostati.
Restituisce il valore minimo trovato
• E’ utile per determinare il minimo valore di un
campo avente specifiche caratteristiche
Giorgio Porcu - Excel Avanzato
32
ANALIZZARE
DATI
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
• DB.MIN(tabella;campo;criteri)
Funzioni di database
DB.MAX()
Trova il massimo tra i valori nella colonna campo di
tabella che soddisfano i criteri impostati.
Restituisce il valore massimo trovato
• E’ utile per determinare il massimo valore di un
campo avente specifiche caratteristiche
Giorgio Porcu - Excel Avanzato
33
ANALIZZARE
DATI
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
• DB.MAX(tabella;campo;criteri)
Funzioni di database
DB.CONTA.VALORI()
Conta quanti valori nella colonna campo di tabella
soddisfano i criteri impostati.
Restituisce il numero di valori trovato
• E’ utile per verificare quanti valori o celle in un
campo presentano specifiche caratteristiche
Giorgio Porcu - Excel Avanzato
34
ANALIZZARE
DATI
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
• DB.CONTA.VALORI(tabella;campo;criteri)
Funzioni di database
DB.MEDIA()
Restituisce la media calcolata
• E’ utile per determinare il valor medio dei soli
valori di un campo con specifiche caratteristiche
Giorgio Porcu - Excel Avanzato
35
ANALIZZARE
DATI
Calcola la media dei soli valori nella colonna campo
di tabella che soddisfano i criteri impostati.
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
• DB.MEDIA(tabella;campo;criteri)
 Logico: la formula non è quella corretta per risolvere il
problema
 Sintattico: la sintassi utilizzata nella formula è errata
• Excel dispone di opportuni strumenti di verifica
formule che ne analizzano il funzionamento
aiutando a individuare e risolvere gli errori
Giorgio Porcu - Excel Avanzato
36
ANALIZZARE
DATI
• Può accadere che una formula non restituisca il
risultato desiderato.
Ciò è solitamente dovuto a un errore di tipo:
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Verifica formule
Verifica formule
Giorgio Porcu - Excel Avanzato
37
ANALIZZARE
DATI
• Lo strumento individua i precedenti consente
di visualizzare graficamente sul foglio le
relazioni con le celle dal cui valore dipende il
risultato di una formula:
celle precedenti  formula
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Individuare i precedenti
Verifica formule
Individuare i precedenti
1. Seleziona la cella contenente la formula
2. Scheda Formule > Verifica Formule
> Individua precedenti
Giorgio Porcu - Excel Avanzato
38
ANALIZZARE
DATI
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Per individuare le celle da cui una formula dipende:
Verifica formule
Giorgio Porcu - Excel Avanzato
39
ANALIZZARE
DATI
• Lo strumento individua i dipendenti consente
di visualizzare graficamente sul foglio le
relazioni con le celle il cui valore dipende dal
risultato di una formula:
formula  celle dipendenti
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Individuare i dipendenti
Verifica formule
Individuare i dipendenti
1. Seleziona la cella contenente la formula
2. Scheda Formule > Verifica Formule
> Individua dipendenti
Giorgio Porcu - Excel Avanzato
40
ANALIZZARE
DATI
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Per individuare le celle dipendenti da una formula:
Verifica formule
Giorgio Porcu - Excel Avanzato
41
ANALIZZARE
DATI
• Excel individua automaticamente alcuni errori (o
presunti tali) durante l’inserimento dati
• Le celle contenenti potenziali errori
sono evidenziate con un
triangolino verde in alto a sx
• Selezionando la cella appare inoltre
un rombo giallo con punto esclamativo
• Per provare a fissare tali errori è disponibile un
apposita funzione di controllo errori
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Individuare e fissare errori
Verifica formule
Individuare e fissare errori
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Per individuare e fissare errori:
1. Seleziona il foglio di lavoro desiderato
2. Scheda Formule > Verifica Formule
> Controllo errori
Giorgio Porcu - Excel Avanzato
42
ANALIZZARE
DATI
In alternativa:
1. Muovi il mouse sul rombo giallo
2. Click sx > Menu a tendina
Verifica formule
Valutare formule
43
ANALIZZARE
DATI
Giorgio Porcu - Excel Avanzato
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
• Il modo migliore per verificare il corretto
funzionamento di una formula è valutare passo
passo l’esecuzione dei singoli calcoli che la
compongono e che portano al risultato finale
• Questa operazione (debug) individua eventuali
errori annidati all’interno di calcoli intermedi
• E’ eseguibile con l’opzione Valuta formula
Verifica formule
Valutare formule
1. Seleziona il foglio di lavoro desiderato
2. Scheda Formule > Verifica Formule
> Valuta formula
Giorgio Porcu - Excel Avanzato
44
ANALIZZARE
DATI
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Per valutare passo-passo i calcoli in una formula:
Verifica formule
Giorgio Porcu - Excel Avanzato
45
ANALIZZARE
DATI
• Per impostazione predefinita Excel non mostra
nelle celle le formule inserite, ma il loro risultato
• Quando in un foglio sono presenti molte formule,
unite magari a dati e a testo, è difficile
riconoscerle e analizzarle a colpo d’occhio
• L’opzione Mostra formule consente di
visualizzarle tutte direttamente nelle celle in cui
sono inserite
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Mostrare formule
Verifica formule
Mostrare formule
1. Seleziona il foglio di lavoro desiderato
2. Scheda Formule > Verifica Formule
> Mostra formule
Giorgio Porcu - Excel Avanzato
46
ANALIZZARE
DATI
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Per mostrare tutte le formule di un foglio nelle
stesse celle in cui sono inserite:
Giorgio Porcu - Excel Avanzato
47
ANALIZZARE
DATI
• Può essere utile avere a portata di mano in una
finestra riposizionabile valori e formule contenute
in alcune specifiche celle del foglio di lavoro
• Se il foglio contiene molti dati, si eviterebbe così
di scorrerlo ripetutamente per ricercare e
analizzare ciò che ci serve
• La finestra controllo celle consente di monitorare
il contenuto di celle scelte dall’utente
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Finestra controllo celle
Finestra controllo celle
Monitorare celle scelte
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Per monitorare in una finestra di dialogo il
contenuto di alcune celle scelte:
1. Scheda Formule > Verifica Formule
> Finestra controllo celle
Giorgio Porcu - Excel Avanzato
48
ANALIZZARE
DATI
Appare la Finestra controllo celle
Finestra controllo celle
La Finestra controllo celle può essere riposizionata
e ridimensionata a piacimento dell’utente
Giorgio Porcu - Excel Avanzato
49
ANALIZZARE
DATI
3. Sulla Finestra controllo celle premi il pulsante
Aggiungi controllo cella… e seleziona le celle da
monitorare
© 2011 Giorgio Porcu - Aggiornamennto 06/12/2011
Monitorare celle scelte