Analizzare i Dati

Giorgio Porcu - Excel Avanzato 1

© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11

ANALIZZARE I DATIExcel Avanzato


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

SOMMARIOANALIZZARE I DATI

• Scenari• Ricerca obiettivo• Risolutore• Tabella pivot• Grafico pivot• Funzioni di ricerca e riferimento• Funzioni di database• Verifica formule• Finestra controllo celle


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

Scenari

• 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


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

ScenariInserire uno scenario

Per inserire uno scenario:1. Inserisci in una cella una formula con dati variabili2. Scheda Dati > Strumenti dati >

Analisi di simulazione > Gestione scenari…

Appare la Finestra di dialogoGestione Scenari


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

ScenariInserire uno scenario

3. Premi Aggiungi4. Nella nuova Finestra di dialogo

Aggiungi Scenario inserisci iparametri desiderati

Nome Scenario

Dati variabili


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

ScenariGestire uno scenario

Per simulare uno o più scenari:1. Inserisci in una cella una formula con dati variabili2. Inserisci gli scenari3. Dalla Finestra di dialogo

Gestione Scenariseleziona Riepilogo…o Mostra


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

Ricerca obiettivo

• 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


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

Ricerca obiettivoUtilizzare la ricerca obiettivo

Per utilizzare la ricerca obiettivo:1. Inserisci in una cella una formula con dati variabili,

di cui uno almeno uno incognito2. Scheda Dati > Strumenti dati >

Analisi di simulazione > Ricerca obiettivo…


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

Ricerca obiettivoUtilizzare la ricerca obiettivo

Appare la Finestra di dialogoRicerca Obiettivo

3. Inserisci nella finestra di dialogo i dati richiesti:

Cella contenente la Formula

Valore noto della Formula

Dato incognito


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

Risolutore

• 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


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

Risolutore

Installare il risolutore• Per installare il risolutore:1. Pulsante Office > Opzioni di Excel > Componenti

aggiuntivi2. Nella casella Gestisci seleziona Componenti

aggiuntivi di Excel e fai clic su Vai.


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

Risolutore

Installare il risolutore3. Nella casella Componenti aggiuntivi disponibili

seleziona Componente aggiuntivo Risolutore e fai clic su OK.

A installazione avvenuta il comando Risolutore sarà disponibile nel gruppo Analisi della Scheda Dati.


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

Risolutore

Utilizzare il risolutore• Per utilizzare il risolutore:1. Inserisci in una cella la Funzione Obiettivo da ottimizzare2. Scheda Dati > Analisi > Risolutore3. Nella finestra Parametri del Risolutore seleziona le opzioni

desiderate

Cella contenentela Funzione Obiettivo

Dati variabili

Vincoli


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

Tabella pivot

• Per visualizzare in modo più chiaro dati e risultati relativi a una tabella può essere utile ridisporre i vari campiAd 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

• Le tabelle pivot consentono di riposizionare i campi e riorganizzare automaticamente i dati per ottenere una differente vista sulla tabella


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

Tabelle pivot

Creare una tabella pivotPer creare una tabella pivot:1. Inserisci una tabella dati o posizionati su una già

esistente2. Scheda Inserisci > Tabelle > Tabella pivot

> Tabella pivot


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

Grafico pivot

• 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


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

Grafico pivot

Creare un grafico pivotPer creare una tabella pivot accompagnata da un grafico pivot:1. Inserisci una tabella dati o posizionati su una già

esistente2. Scheda Inserisci > Tabelle > Tabella pivot

> Grafico pivot


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

Funzioni di ricerca e riferimento

Funzioni in Excel• 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 Segue una precisa sintassi


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI


Funzioni di ricerca• Le funzioni di ricerca e riferimento:

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 comandistandard di ricerca disponibili inScheda Home > Modifica > Trova e seleziona


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI


Funzioni di ricercaLe principali funzioni di ricerca sono:• 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

Ciascuna di esse restituisce un’opportuno risultato per la ricerca


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI


Inserire funzioni di ricercaPer inserire una funzione di ricerca:1. Seleziona la cella desiderata2. Scheda Formule > Libreria di funzioni

> Ricerca e riferimento


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI


VettoriLe funzioni di ricerca operano su gruppi di dati detti vettori.• Un vettore è un insieme di celle consecutive

contenenti dati (elementi). Esistono: Vettori riga Vettori colonna

• Un vettore è ordinato se i suoi elementi sono in ordine per la tipologia di dati che rappresentano

2 44 11 44 58

2 4 11 44 58

ADEF


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI


MatriciAlcune funzioni di ricerca sfruttano gruppi di dati detti matrici.• Una matrice è un insieme di vettori (riga/colonna)

contigui della stessa dimensione

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

Nome Cognome DataNascita Sesso Residenza

Mario Rossi 23/01/1965 M Milano

Carla Bianchi 08/05/1978 F Roma


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI


Avvertenze d’uso• In pratica in un foglio Excel:

Dati su righe o colonne sono vettori Le tabelle sono matrici

• Per ottenere risultati corretti le funzioni di ricerca devono operare su vettori: Preventivamente ordinati Contenenti dati dello stesso tipo


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI


CERCA()• CERCA(valore;vettore)

Cerca valore in vettore riga o colonnaRestituisce: 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

2 4 11 44 58

44


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI


CERCA.ORIZZ()• CERCA.ORIZZ(valore;matrice;indice;esatta)

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


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI


CERCA.VERT()• CERCA.VERT(valore;matrice;indice;esatta)

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


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

Funzioni di database

• Le funzioni di database: Consentono di eseguire calcoli di base sui soli dati

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 comandidi Filtro e Somma automaticadelle schede Home e Formule


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI


Funzioni di databaseLe 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)

Ciascuna di esse restituisce un’opportunorisultato per la ricerca


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI


Inserire funzioni di databaseLe funzioni di database non sonoaccessibili direttamente daScheda Home > Libreria di funzioniPer inserire le funzioni di database:1. Barra della formula > Inserisci funzione2. Nella Finestra di dialogo Inserisci funzione scegli

Database dal Menu a tendina Categoria


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI


DB.SOMMA()• DB.SOMMA(tabella;campo;criteri)

Calcola la somma dei soli valori nella colonna campo di tabella che soddisfano i criteri impostati.Restituisce la somma calcolata

• E’ utile per sommare i soli valori di un campo aventi specifiche caratteristiche


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI


DB.MIN()• DB.MIN(tabella;campo;criteri)

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


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI


DB.MAX()• DB.MAX(tabella;campo;criteri)

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


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI


DB.CONTA.VALORI()• DB.CONTA.VALORI(tabella;campo;criteri)

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


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI


DB.MEDIA()• DB.MEDIA(tabella;campo;criteri)

Calcola la media dei soli valori nella colonna campo di tabella che soddisfano i criteri impostati.Restituisce la media calcolata

• E’ utile per determinare il valor medio dei soli valori di un campo con specifiche caratteristiche


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

Verifica formule

• Può accadere che una formula non restituisca il risultato desiderato.Ciò è solitamente dovuto a un errore di tipo: 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


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

Verifica formuleIndividuare i precedenti

• 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


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

Verifica formuleIndividuare i precedenti

Per individuare le celle da cui una formula dipende:1. Seleziona la cella contenente la formula2. Scheda Formule > Verifica Formule

> Individua precedenti


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

Verifica formuleIndividuare i dipendenti

• 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


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

Verifica formuleIndividuare i dipendenti

Per individuare le celle dipendenti da una formula:1. Seleziona la cella contenente la formula2. Scheda Formule > Verifica Formule

> Individua dipendenti


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

Verifica formuleIndividuare e fissare errori

• Excel individua automaticamente alcuni errori (o presunti tali) durante l’inserimento dati

• Le celle contenenti potenziali errorisono evidenziate con untriangolino verde in alto a sx

• Selezionando la cella appare inoltreun rombo giallo con punto esclamativo

• Per provare a fissare tali errori è disponibile un apposita funzione di controllo errori


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

Verifica formuleIndividuare e fissare errori

Per individuare e fissare errori:1. Seleziona il foglio di lavoro desiderato2. Scheda Formule > Verifica Formule

> Controllo errori

In alternativa:1. Muovi il mouse sul rombo giallo2. Click sx > Menu a tendina


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

Verifica formuleValutare formule

• 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


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

Verifica formuleValutare formule

Per valutare passo-passo i calcoli in una formula:1. Seleziona il foglio di lavoro desiderato2. Scheda Formule > Verifica Formule

> Valuta formula


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

Verifica formuleMostrare formule

• 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


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

Verifica formuleMostrare formule

Per mostrare tutte le formule di un foglio nelle stesse celle in cui sono inserite:1. Seleziona il foglio di lavoro desiderato2. Scheda Formule > Verifica Formule

> Mostra formule


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

Finestra controllo celle

• 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


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

Finestra controllo celleMonitorare celle scelte

Per monitorare in una finestra di dialogo il contenuto di alcune celle scelte:1. Scheda Formule > Verifica Formule

> Finestra controllo celle

Appare la Finestra controllo celle


© 2

011

Gio

rgio

Por

cu -

Aggi

orna

men

nto

06/1

2/20

11AN

ALIZ

ZARE

DATI

Finestra controllo celleMonitorare celle scelte

3. Sulla Finestra controllo celle premi il pulsante Aggiungi controllo cella… e seleziona le celle da monitorare

La Finestra controllo celle può essere riposizionata e ridimensionata a piacimento dell’utente

Analizzare i Dati

Documents

Transcript of Analizzare i Dati