Fogli Elettronici: MS Excel utilizzo avanzato

(1)

1

Fogli Elettronici:

MS Excel

utilizzo avanzato

Ripasso

(2)

Informatica - A.A. 2010/2011 - Excel avanzato 3

Selezionare celle e gruppi di celle

Una cella in un foglio è individuata dall’incrocio tra la riga e la colonna (coordinate della cella)

• Es: F3 indica la terza cella nella colonna F del foglio di lavoro attivo

Una cella in una cartella di lavoro (file con più fogli di lavoro) è individata dal nome del foglio di lavoro, seguito dalle

coordinate della cella in quel foglio

• Es: Foglio1!F3 indica la terza cella nella colonna F del foglio di lavoro Foglio1

Un gruppo di celle può essere selezionato indicando le due celle alle estremità, separate da :

• F3:F16 seleziona 14 celle nella colonna F

• A3:F3 seleziona 6 celle nella riga 3

• A3:F16 seleziona un rettangolo di celle

Utilizzare semplici funzioni

Le funzioni consentono di:

• Inserire in una cella il risultato di una elaborazione

• Aggiornare automaticamente il risultato al variare delle celle che costituiscono gli input della funzione Struttura di una funzione in Excel:

=NOME(arg1;arg2;...;argN) Esempi:

• =SOMMA(A3:A16)

• =SE(A5 > B9;”SI”;”NO”)

(3)

Utilizzare riferimenti assoluti e relativi

Riferimenti relativi: vengono automaticamente aggiornati se la funzione che li utilizza viene spostata / copiata e incollata / estesa

• Es: =SOMMA(A3:A9)

Riferimenti assoluti: non subiscono variazioni se la funzione che li utilizza viene spostata / copiata e incollata / estesa

• Es: =SOMMA($A$3:$A$9)

Riferimenti misti: riferimenti in cui solo una delle due coordinate è relativa (riga o colonna)

• Es: =SOMMA($A3:A$9)

Importazione di dati

(4)

Importazione dei dati

I fogli di calcolo sono utilizzati per elaborare dati Tipicamente i dati da elaborare sono memorizzati in sorgenti esterne al foglio di calcolo

– Basi di dati

– File di vari formati

L’importazione dei dati è l’operazione che consente l’inserimento automatico di dati provenienti da sorgenti esterne in un foglio di calcolo

L’alternativa all’importazione dei dati è l’inserimento manuale dei dati all’interno del foglio di calcolo (impraticabile per insiemi di dati molto numerosi)

Importazione dei dati in Excel

MS Excel consente di importare dati da fonti eterogenee:

• File di testo

• Pagine WEB

• File XML

• Risultati di una query in un database

• …

Per accedere alla finestra di importazione dei dati selezionare Dati Da testo

Da qui si può selezionare il file e importare i dati

(5)

Formati dei dati

Il procedimento di importazione dei dati deve essere flessibile, per adattarsi a formati differenti

Tipi di dati originali:

• Delimitati, si usa un carattere particolare per separare tra loro i contenuti delle celle. In fase di importazione occorre indicare il carattere

separatore (solitamente , o TAB)

• A larghezza fissa, i dati sono incolonnati come in una tabella. In fase di importazione occorre

selezionare la larghezza delle colonne

Esportazione dei dati

• L’esportazione dei dati è l’operazione simmetrica all’importazione

• I dati contenuti in una tabella sono automaticamente salvati in un file di testo, secondo un particolare formato

• Excel consente di salvare i dati in vari formati, tra cui CSV (Comma Separated Values, valori separati da virgole) oppure in formato testuale con valori delimitati da spazi o da TAB

• Ovviamente il formato nativo di Excel (.xls) è più

espressivo dell’esportazione dei dati un un file di testo, in cui alcune informazioni possono essere perse

(funzioni, macro, fogli multipli, ...)

(6)

11

Filtrare i dati

Informatica - A.A. 2010/2011 - Excel avanzato

Il concetto di filtro

• In tabelle di grandi dimensioni (migliaia di righe) è difficile estrarre solo i dati di reale interesse

• In queste situazioni, è possibile utilizzare un filtro per nascondere i dati ininfluenti e visualizzare esclusivamente i valori interessanti

• Un filtro è una espressione booleana che viene calcolata a partire dai valori di una o più colonne di una tabella: filtri automatici o avanzati

• Quando un filtro viene applicato ad una tabella, vengono nascoste tutte le righe per cui

l’espressione booleana è falsa

(7)

Operatori booleani

• Operatori già visti per la costruzione di query per i motori di ricerca sul Web

• Prendono il nome dal matematico inglese George Boole

• Gli operatori booleani di base sono:

– AND – OR – NOT

• Vengono applicati a uno (nel caso del NOT) o due (nel caso di AND e OR) argomenti

(espressioni) e ritornano dei valori di verità (VERO o FALSO)

Operatori booleani (cont.)

Tabelle di verità

X Y X and Y

F F F

F V F

V F F

V V V

X Y X or Y

F F F

F V V

V F V

V V V

X not X

F V

V F

I valori di verità possono essere codificati con valori binari in modo molto semplice. Un’associazione standard è:

• V ↔1

• F ↔0

(8)

Operatori booleani: esempio

Espressioni (booleane):

• X=“Siamo a Modena”

• Y=“Questo è il corso di Laurea di Medicina”

– X AND Y è falso – X OR Y è vero

– Not X è falso, not Y è vero

Filtri automatici

• Un filtro automatico è una espressione booleana applicata ad una sola colonna della tabella

Esempio: Visualizza tutti gli studenti alti 190 cm Espressione booleana: ALTEZZA = 190

• Tutte le righe per cui il valore della cella corrispondente alla colonna ALTEZZA è 190

verificano l’espressione booleana (ALTEZZA = 190 è VERO) e vengono visualizzate

• Tutte le altre righe non verificano l’espressione booleana (ALTEZZA = 190 è FALSO) e vengono nascoste

(9)

Filtri automatici in MS Excel

Per applicare un filtro automatico su di una tabella in excel occorre:

• Selezionare una cella appartenente alla tabella che si vuole filtrare

• Selezionare Dati Filtro

• In ogni cella dell’intestazione della tabella

selezionata compare una freccia che apre un menu a tendina

• Il menu a tendina consente di selezionare quale tipo di filtri applicare Non è necessario scrivere esplicitamente i criteri del filtro

Limiti dei filtri automatici

I filtri automatici consentono di:

• Definire condizioni semplici su una colonna

• Definire condizioni composte su una colonna

• Comporre in AND logico condizioni semplici o composte su colonne diverse

– Es: visualizza tutte le righe in cui

((ALTEZZA>190) OR (ALTEZZA<150)) AND (ANNO=1988)

I filtri automatici non consentono di:

• Comporre in OR logico condizioni semplici o composte su colonne diverse

– Es: visualizza tutte le righe in cui (ALTEZZA = 190) OR (ANNO = 1988)

(10)

Filtri avanzati

Il principio di funzionamento è lo stesso dei filtri automatici (visualizzano solo le righe conformi a un determinato criterio)

PRO:

• Sono più potenti dei filtri automatici (consentono di esprimere condizioni più complesse)

CONTRO:

• Occorre scrivere manualmente i criteri di filtro

Criteri di filtro avanzati

I criteri di filtro avanzati devono essere scritti in una apposita tabella, chiamata tabella dei criteri

Regole di composizione della tabella dei criteri

• Colonne diverse della tabella dei criteri esprimono condizioni su colonne diverse della tabella dei dati

• Tutte le condizioni scritte sulla stessa riga della tabella dei criteri sono considerate in AND logico tra loro

• Righe diverse sono considerate in OR logico tra loro

(11)

Filtri avanzati: esempio

Es: Selezionare tutte le righe relative agli studenti iscritti nel 1988, fumatori e dal peso superiore a 80 Kg oppure agli studenti iscritti nel 1989, non fumatori e dal peso inferiore a 60 Kg

Espressione booleana corrispondente:

(ANNO=1988) AND (FUMO=1) AND (PESO > 80) OR

(ANNO=1989) AND (FUMO=0) AND (PESO < 60)

Tabella dei criteri: esempio

L’espressione precedente si traduce nella seguente tabella:

Righe diverse sono in OR logico tra loro, le celle della stessa riga sono in AND logico tra loro

ANNO FUMO PESO

1988 1 > 80

1989 0 < 60

(12)

Applicazione di filtri avanzati

• Scrivere la tabella dei criteri (al di fuori della tabella coi dati)

• Selezionare una cella all’interno della tabella contenente i dati da filtrare

• Selezionare Dati Avanzate nella scheda Ordina e Filtra

• Compare une finestra in cui occorre impostare:

– L’area della tabella dei dati – L’area della tabella dei criteri

Subtotali

(13)

Subtotali

• In tabelle di grandi dimensioni, spesso è necessario applicare determinate funzioni (SOMMA, MEDIA, CONTAVALORI, …) solo alle righe della tabella che soddisfano determinate condizioni

– Es: calcola l’altezza media delle studentesse iscritte nel 1988

• Operazioni di questo tipo possono essere effettuate con la funzione SUBTOTALE

La funzione SUBTOTALE

La funzione subtotale ha la seguente sintassi:

=SUBTOTALE(funzione,rif1[,rif2,…])

SUBTOTALE ha un numero variabile di parametri:

• Il primo parametro è un numero intero compreso tra 1 e 11 che indica quale funzione (selezionata tra l’elenco delle funzione disponibili) applicare all’intervallo di celle selezionato

• I parametri successivi indicano gli intervalli di celle a cui applicare la funzione indicata

precedentemente

• È necessario selezionare almeno un intervallo di celle

(14)

Selezione della funzione

Lista delle funzioni disponibili:

PRIMO PARAMETRO FUNZIONE

1 MEDIA

2 CONTA.NUMERI

3 CONTA.VALORI

4 MAX

5 MIN

6 PRODOTTO

7 DEV.ST

8 DEV.ST.POP

9 SOMMA

10 VAR

11 VAR.POP

Subtotali e filtri

• La funzione subtotale va usata in associazione con i filtri

• La funzione subtotale viene applicata esclusivamente alle celle che sono visibili!

• Tutte le celle non visibili vengono ignorate

Subtotali relativi a gruppi di celle diverse possono essere calcolati semplicemente

alterando le condizioni di filtro impostate sulla tabella dei dati

(15)

Subtotali: esempio

• Calcolare le altezze medie dei maschi, delle femmine e di tutti gli studenti iscritti all’anno accademico 1988

Soluzione:

• Utilizzare la funzione subtotale

• Specificare come primo parametro 1 (MEDIA)

• Specificare come secondo parametro l’intervallo di celle corrispondente alla colonna delle altezze

• Impostare i filtri per selezionare solo i gruppi di celle desiderati (condizioni su anno e sesso)

• Cambiando i filtri i subtotali vengono aggiornati automaticamente

Tabelle Pivot

(16)

Tabelle Pivot

Una tabella pivot è una tabella riassuntiva

interattiva che consente di riepilogare rapidamente grandi quantità di dati. Le tabelle pivot sono

estremamente utili quando si vogliono calcolare automaticamente e confrontare i subtotali relativi a una o più colonne di tabelle di grandi dimensioni

Creazione di tabelle pivot

Tabelle pivot possono essere create facilmente a partire da tabelle di dati in Excel

• Selezionare una cella appartenente alla tabella contenente i dati di origine

• Selezionare Inserisci Tabella Pivot

• Compare la finestra per la composizione guidata della tabella Pivot, che consente di selezionare i dati di origine

• Una volta selezionati i dati di origine occorre specificare il layout (la struttura) della tabella

(17)

Layout della tabella pivot

Una tabella pivot è composta da varie aree Le tre aree fondamentali sono:

• L’area delle righe

• L’area delle colonne

• L’area dei dati

• Nell’area delle righe e delle colonne occorre

mettere le caratteristiche che si intendono utilizzare per classificare i dati (nell’esempio precedente, gli sport e i trimestri)

• Nell’area dei dati vanno inseriti i dati che si

intendono aggregare (nell’esempio precedente, le vendite)

Creazione di un layout

Il layout di una tabella pivot può essere creato semplicemente trascinando i nomi delle colonne desiderate all’interno delle aree interessate

Esempio: calcolare le altezze medie degli studenti suddivisi per anno accademico e per sesso

• Trascinare ANNO nell’area delle righe

• Trascinare SESSO nell’area delle colonne

• Trascinare ALTEZZA nell’area dei dati

• Selezionare la funzione di aggregazione MEDIA

(18)

Interattività delle tabelle pivot

Le tabelle pivot sono interattive in quanto

facilmente modificabili in ogni momento (anche dopo la creazione)

• Il layout è sempre modificabile

– Si possono aggiungere/togliere colonne a tutte le aree della tabella pivot

• I dati visualizzati possono essere facilmente filtrati

– Accanto alle colonne nelle aree delle righe e delle colonne compaiono dei menu a tendina simili a quelli dei filtri automatici

Grafici Pivot

• A partire da una tabella pivot è possibile generare dei grafici pivot

– Da Strumenti tabella pivot Opzioni Grafico Pivot (Scheda Strumenti)

• Anche i grafici pivot sono interattivi, è possibile cambiare facilmente i dati rappresentati e le

funzioni di aggregazione utilizzate