capitolo - Mondadori Informatica

CAPITOLO 2
Utilizzare le funzioni e PowerPivot
Utilizzarelefunzionididataeora ..................41
Utilizzarelefunzionistatistiche.....................67
IlsistemadidatadiExcel..........................42
Utilizzarelefunzionididatabase...................73
Utilizzarelefunzioniditestoedati..................50
Utilizzarelefunzionicubo........................ 80
Utilizzarelefunzionilogiche .......................58
Utilizzarelefunzionimatematico-finanziarie ........ 84
Utilizzarelefunzionidiricercaeriferimento..........61
Utilizzarelefunzionimatematicheetrigonometriche. 90
Utilizzarelefunzioniinformative .................. 64
PowerPivot......................................93
Q
uesto capitolo descrive molte funzioni importanti di Microsoft Excel, comprese funzioni che possono essere utilizzate per manipolare i valori data e ora. È possibile
utilizzare tali funzioni insieme all’ora di sistema del proprio computer, ma occorre
essere sicuri che l’ora del sistema sia corretta.
Utilizzare le funzioni di data e ora
Quando Excel riconosce un valore data, la cella corrispondente viene formattata automaticamente come una data. Per esempio, il valore 7-10 viene interpretato come 7 ottobre dell’anno
corrente e la cella formattata di conseguenza. Tutte le immissioni successivamente inserite in
quella cella verranno visualizzate come valori data. Quindi, per esempio, inserendo il numero
123456 verrà visualizzato 03/01/2238 (per ulteriori informazioni su questi numeri, consulta
la sezione “Il sistema di data di Excel” più avanti in questo capitolo).
APPROFONDIMENTO
Immettere valori data
Quando vengono inserite date, Excel riconosce una barra (/) o un segno meno (–) come
separatore. Per esempio, Excel riconosce 13/8/08 o 26-4-11 come date. Questi due caratteri si trovano sul tastierino numerico e sono facili da trovare e utilizzare.
41
02 Ex Fun.indd 41
19/04/12 12.02
42
Capitolo2 UtilizzarelefunzioniePowerPivot
Il sistema di data di Excel
Microsoft Excel salva le date come numeri progressivi (o seriali) in modo che possano essere
utilizzate nei calcoli. Per impostazione predefinita, Excel determina una data basandosi sui
numeri progressivi che iniziano con 1, che è il 1° gennaio 1900, e finiscono con il numero
2.958.465, che è il 31 dicembre 9999. Ciò significa che Excel può calcolare solo i valori data
compresi tra queste due date.
ATTENZIONE
Capitolo 2
!
Microsoft Excel per Mac utilizza un sistema di data diverso. Il calendario inizia il
01/01/1904. Per motivi di compatibilità, la versione Windows di Excel offre un’opzione
per l’utilizzo del sistema di data a iniziare dal 1904. Seleziona questa opzione solo se devi
utilizzare gli stessi fogli di lavoro su computer Windows e Mac. Ma stai attento: questa
impostazione si applica al foglio di lavoro attivo e le date già inserite verranno modificate!
Per visualizzare il numero progressivo di un valore data od ora, ripristina il formato Generale
della cella. Nelle versioni di Excel precedenti Excel 2003, puoi selezionare l’opzione Elimina
nel menu Modifica e fare clic su Formati per ripristinare il formato Generale. In Excel 2007 o
Excel 2010, basta fare clic sul formato numero sulla scheda Home (Figura 2.1).
Figura 2.1 La selezione del formato numero sulla scheda Home in Excel 2007 ed Excel 2010.
02 Ex Fun.indd 42
19/04/12 12.02
Il sistema di data di Excel 43
Ecco il modo in cui date e orari insieme vengono visualizzati come numeri progressivi: il
numero prima del punto è la data. Per esempio, il numero 39448 indica il numero di giorni
trascorsi dal 01/01/1900. Il risultato è il 1° gennaio 2008. I numeri dopo il punto indicano
l’ora. Se dividi questo valore per il numero di ore in un giorno, ottieni la frazione decimale
per un’ora: 1/24 = 0,04166667. Il numero 0,5 indica che è passata mezza giornata ed è mezzogiorno. Il numero 0,25 indica le 6 del mattino, mentre 0,75 indica le 18,00.
Formati numerici di data e ora
Tabella 2.1 Formati numerici per date e orari
02 Ex Fun.indd 43
Formato
Descrizione/Risultato
Numero
=SOMMA(numero1;numero2...)
Testo
=CONCATENA(Testo1;Testo2;...)
G
Data senza zero iniziale
GG
Data con zero iniziale per i numeri con una sola cifra
GGG
Nome abbreviato del giorno (Lun, Mar, Mer, Gio, Ven, Sab, Dom)
GGGG
Nome del giorno (Lunedì, Martedì e così via)
M
Mese senza zero iniziale
MM
Mese con zero iniziale per i numeri con una sola cifra
MMM
Nome abbreviato del mese (Gen, Feb, Mar e così via)
MMMM
Nome del mese (Gennaio, Febbraio, Marzo e così via)
A o AA
Anno a due cifre
AAA o AAAA
Anno a quattro cifre
h
Ora senza zero iniziale
hh
Ora con zero iniziale
m
Minuto senza zero iniziale
mm
Minuto con zero iniziale
[h] o [hh]
Ore per più di 24 ore
[m] o [mm]
Minuti per più di 60 minuti
[s] o [ss]
Secondi per più di 60 secondi
h.mm AM
Formato dodici ore (A.M.): ante meridiem (latino per “prima di
mezzogiorno”, le ore tra mezzanotte e mezzogiorno)
h.mm PM
Formato dodici ore (P.M.): post meridiem (latino per “dopo
mezzogiorno”, le ore tra mezzogiorno e mezzanotte)
Capitolo 2
Puoi visualizzare la data e l’ora utilizzando i formati elencati nella Tabella 2.1.
19/04/12 12.02
44
Capitolo2 UtilizzarelefunzioniePowerPivot
Anni bisestili
Quando programmi calcoli con le date, devi considerare gli anni bisestili. Nel calendario
Gregoriano, ogni quattro anni c’è un anno bisestile in cui il mese di febbraio è di 29 giorni. Se
l’anno può essere diviso per 100 senza resto, non è un anno bisestile, con un’unica eccezione: se l’anno è divisibile per 400 senza resto, è comunque un anno bisestile. Se quest’ultima
regola non viene rispettata, ci saranno altri errori invece di un 29 febbraio.
Funzioni di analisi
Capitolo 2
Sebbene la maggior parte di queste funzioni di data e ora sia disponibile con l’installazione
standard di Excel 2003 (e versioni precedenti), alcune devono essere attivate. Puoi attivarle
selezionando Strumenti/Componenti aggiuntivi ed effettuando una scelta nella finestra di
dialogo Componenti aggiuntivi (Figura 2.2).
Figura 2.2 In Excel 2003 e versioni precedenti, alcune funzioni di analisi devono essere attivate
nella finestra di dialogo Componenti aggiuntivi.
In Excel 2007 ed Excel 2010, puoi utilizzare tutte le funzioni senza dover attivare il componente aggiuntivo.
Funzioni di data e ora in pratica
I seguenti esempi pratici mostrano calcoli tipici che utilizzano le funzioni di data e ora di Excel.
File di esempio
Utilizza il foglio di lavoro Prassi del file di esempio Data_ora.xls o Data_ora.xlsx nella
cartella Capitolo02. Per ulteriori informazioni sui file di esempio, vedi la sezione “Utilizzare i file di esempio” nell’introduzione.
02 Ex Fun.indd 44
19/04/12 12.02
Il sistema di data di Excel 45
Calcolare l’inizio e la fine dell’ora legale
Supponi che l’ora legale inizi l’ultima domenica di marzo e finisca l’ultima domenica di ottobre.
L’inizio dell’ora legale nell’anno corrente viene calcolato con la formula seguente:
=DATA(ANNO(OGGI());4;)-GIORNO.SETTIMANA(DATA(ANNO(OGGI());4;))+1
r e s tituis ce 31 mar zo de ll ’anno cor r ente. G I O R N O .
restituisce il numero del giorno della settimana dell’ultimo giorno di marzo dell’anno corrente. L’argomento 1 alla fine della formula fa riferimento
al primo giorno della settimana, cioè lunedì. Quindi, sottraendo questo valore dall’ultimo
giorno del mese e aggiungendo un giorno, si ottiene la data dell’ultima domenica di marzo.
DATA(ANNO(OGGI());4;)
La formula seguente restituisce la fine dell’ora legale nell’anno corrente (l’ultima domenica
di ottobre):
Capitolo 2
SETTIMANA(DATA(ANNO(OGGI());4;)
=DATA(ANNO(OGGI());11;)-GIORNO.SETTIMANA(DATA(ANNO(OGGI());11;))+1
Identificare che giorno dell’anno è oggi
Puoi utilizzare una funzione di data nidificata per scoprire il numero progressivo corrispondente alla data odierna:
=OGGI()-DATA(ANNO(OGGI())-1;12;31)
Questa formula sottrae il 31 dicembre dell’anno scorso dalla data odierna. Il risultato è la
differenza tra le date in giorni, in questo caso il numero del giorno odierno dell’anno corrente.
Esegiore calcoli con l’ora
Immetti l’ora corrente premendo Ctrl+Maiusc+. (punto). L’ora viene visualizzata nel formato
hh.mm. Per esempio, la cella potrebbe visualizzare 14.25, così come la barra della formula.
Se selezioni il formato numero Generale, viene visualizzato il valore (in questo esempio
0,60069444). Se stai calcolando le ore, devi ricordare che un orario è sempre un valore
compreso tra 0 e 1.
Di solito non devi preoccuparti della conversione, poiché Excel converte automaticamente
date e orari. Per calcolare la differenza tra due valori di data e ora, puoi sottrarre la data/ora
d’inizio dalla data/ora di fine. In questo modo, puoi creare una tabella con l’orario di lavoro
per calcolare le ore di ogni giorno lavorativo, come nell’esempio che segue nella sezione
successiva.
02 Ex Fun.indd 45
19/04/12 12.02
46
Capitolo2 UtilizzarelefunzioniePowerPivot
Calcolare l’orario di lavoro
Supponi di voler creare un foglio di lavoro con gli orari dei turni di un’azienda. Alcuni dipendenti lavorano dalle 10.00 alle 18.00. Come si fa a calcolare le ore di lavoro corrette?
Capitolo 2
Se i dipendenti lavorano dalle 10.00 alle 18.00 e calcoli la differenza, il risultato è ######.
Anche se cambi la larghezza della colonna, il problema non si risolve. Se, invece, modifichi il
formato di questo numero portandolo su Generale, il valore viene visualizzato. È interessante
notare come Excel può calcolare l’orario quando imposti il formato numero su Generale ma
non quando questo è impostato su un formato ora. Come puoi vedere, il formato ora di Excel
non funziona con i numeri negativi.
Per risolvere questo problema esistono due opzioni: cambiare l’impostazione generale al
sistema di data 1904 (Figura 2.3) oppure utilizzare una formula.
Figura 2.3 Passare al sistema data 1904 in Excel 2007 ed Excel 2010.
ATTENZIONE
!
Utilizza il comando di menu Strumenti/Opzioni (Excel 2003 o versioni precedenti) per
selezionare la casella di controllo del sistema di data 1904 sulla scheda Calcolo. Attivando questa opzione, Excel può eseguire calcoli con orari negativi. Tuttavia, questa
soluzione presenta qualche svantaggio: l’impostazione si applica solo al foglio di lavoro
attivo. Se ci sono altri fogli di lavoro che fanno riferimento a quei valori ora, occorre
modificare anche quei fogli di lavoro. Tutti i valori data immessi si modificano di quattro
anni e allora bisogna cambiare tutte le date esistenti. Perciò, questa opzione non è una
soluzione affidabile.
Eseguire calcoli oltre il limite di data
Per calcolare un intervallo di tempo che sconfina in un altro giorno, puoi immettere gli orari e
includere la data. Se il lavoro inizia alle 22.00 del 4/8/2008 e termina alle 06.20 del 5/8/2008
02 Ex Fun.indd 46
19/04/12 12.02
Il sistema di data di Excel 47
Capitolo 2
puoi facilmente calcolare la differenza. Ciò è possibile con la formula =Fine-Inizio. Comunque, devi visualizzare il formato numero della cella risultato nel formato hh.mm (Figura 2.4).
Figura 2.4 Quando includi la data insieme all’ora, il calcolo della differenza non pone problemi.
Un altro metodo consiste nel controllare il valore ora maggiore se si dispone degli orari sia
di inizio che di fine. Con la funzione logica SE(test;se_vero;se_falso), si ottiene il risultato con
la formula
=SE(Inizio>Fine;1-(Inizio-Fine);(Fine-Inizio)
Questa formula può essere utilizzata per calcolare una differenza di massimo 24 ore. La
Figura 2.5 mostra un esempio.
Figura 2.5 Quando è specificata solo l’ora, bisogna considerare il possibile sconfinamento in un
altro giorno.
Se gli intervalli di tempo sono inferiori a 24 ore e l’orario iniziale è maggiore di quello finale,
puoi considerare la differenza assoluta tra i due orari e poi sottrarla da 1.
=SE(Inizio>Fine;1-(Inizio-Fine);Fine–Inizio)
Un’altra opzione consiste nell’inserire un confronto Inizio>Fine. Il risultato è uno dei due
valori logici VERO o FALSO. Se Excel trova un valore logico in un calcolo, il valore viene
convertito in 1 o 0. Poiché un giorno intero corrisponde al valore 1, puoi aggiungere 1 a un
orario (quando l’inizio è maggiore della fine) e ottenere il risultato nel giorno successivo.
Utilizza questa formula:
=(Inizio>Fine)+Fine-Inizio
02 Ex Fun.indd 47
19/04/12 12.02
48
Capitolo2 UtilizzarelefunzioniePowerPivot
Sommare intervalli di tempo
Quando esegui somma fra orari, devi considerare alcuni aspetti. In Excel, la somma di due
orari non supererà le 24 ore; il risultato di 15 ore più 12 ore è 3 ore. Puoi risolvere questo
problema utilizzando il formato numero corretto:
1. Seleziona la cella risultato e fai clic sull’opzione di menu Formato/Celle (Excel 2003 e
versioni precedenti) oppure fai clic sulla freccia nell’angolo inferiore destro del gruppo
Numeri sulla scheda Home (Excel 2007 ed Excel 2010), come si vede nella Figura 2.6.
Capitolo 2
Figura 2.6 In Excel 2007 ed Excel 2010, fai clic sulla freccia in fondo al gruppo Numeri sulla
scheda Home per aprire la finestra di dialogo Formato celle.
2. Fai clic sulla scheda Numero della finestra di dialogo Formato celle e seleziona Perso-
3. Seleziona il formato numero [h].mm. Attento alle parentesi. Se non riesci a trovare il
nalizzato nel campo dell’elenco Categoria.
formato esatto, modifica una voce simile.
Il risultato viene visualizzato nel formato 24.00.
Calcolare lo stipendio in base all’orario di lavoro
Dal momento che Excel tratta le ore come frazioni di un giorno, bisogna essere molto attenti
quando si calcolano valori basati sul numero delle ore di lavoro. Per esempio, se un dipendente lavora 6,25 ore e viene pagato €21 l’ora, moltiplicando semplicemente questi valori il
risultato sarà di €5,61. Il dipendente non sarebbe troppo contento!
Per calcolare il risultato corretto, devi moltiplicare il numero delle ore per 24. Il calcolo corretto sarebbe =(“6.25”*24)*21, con un risultato pari a €134,75 (Figura 2.7).
Figura 2.7 Moltiplicando le ore per la paga oraria si ottiene un risultato errato in D21.
02 Ex Fun.indd 48
19/04/12 12.02
Il sistema di data di Excel 49
Arrotondare i valori ora
Supponi, per esempio, che la cella B42 contenga l’orario 08.52.36 nel formato numero hh.mm.
ss. La formula =ARROTONDA.MULTIPLO(B42;1/24) dà come risultato 09.00.00, mentre la
formula =ARROTONDA.MULTIPLO(B42;1/1440) dà 08.53.00.
Identificare il trimestre
Supponi di voler identificare il trimestre dell’anno in cui ricade una determinata data. Per
questo esempio, il primo trimestre va da gennaio a marzo, il secondo da aprile a giugno e
così via.
Capitolo 2
Dal momento che il minuto o addirittura il secondo esatto non sono sempre necessari, è
possibile arrotondare i valori ora. Ricorda che Excel utilizza il valore 1 per un giorno. Un’ora
è una frazione di un giorno, ossia un 1/24, mentre un minuto è un 1/1440. Se imposti questi
valori come argomenti della funzione tabella ARROTONDA.MULTIPLO(), l’orario viene arrotondato di conseguenza.
Dividi il numero del mese per 3 e arrotonda il risultato al numero intero successivo con la
formula =ARROTONDA.PER.ECC(MESE(valore data)/3;0) come si vede nella Figura 2.8.
Figura 2.8 Identificare il trimestre dell’anno.
Convertire ore normali in ore decimali (ore industriali)
È possibile convertire un orario in ore e minuti decimali. Se le ore e i minuti sono numeri
interi, utilizza la formula seguente per calcolare le ore decimali:
Orario = Ora(valore data) + Minuto(valore data)/60
Se l’orario appare come frazione, moltiplica questo valore per 24. Ecco alcuni esempi:
02 Ex Fun.indd 49
●
06.30 corrisponde alle ore decimali 6,5.
●
07.15 corrisponde alle ore decimali 7,25.
●
09.45 corrisponde alle ore decimali 9,75.
19/04/12 12.02
50
Capitolo2 UtilizzarelefunzioniePowerPivot
File di esempio
Utilizza il foglio di lavoro Prassi del file di esempio Data_ora.xls o Data_ora.xlsx nella
cartella Capitolo02. Per ulteriori informazioni sui file di esempio, vedi la sezione “Utilizzare i file di esempio” nell’introduzione.
Capitolo 2
Utilizzare le funzioni di testo e dati
Con le funzioni di testo e dati di Excel, puoi visualizzare i dati in una varietà di formati nonché
convertire dati o eseguire calcoli in combinazione con altre funzioni. Troverai altre soluzioni
negli esempi pratici che seguono.
File di esempio
Utilizza il foglio di lavoro che trovi nel file di esempio Dati_testo.xls o Dati_testo.xlsx
nella cartella Capitolo02. Per ulteriori informazioni sui file di esempio, vedi la sezione
“Utilizzare i file di esempio” nell’introduzione.
Separare stringhe di testo in CAP e località
Supponi di avere un elenco di indirizzi in cui una colonna contiene una località che consiste in uno stato e nel CAP a cinque cifre. Poiché non hai bisogno dello stato, vuoi estrarre
semplicemente il CAP a cinque cifre. Quindi, devi eseguire una query per cercare gli ultimi
cinque caratteri dell’indirizzo completo.
Supponendo che l’indirizzo si trovi nella cella A38, la formula è la seguente:
=DESTRA(A38;5)
In questo caso, la funzione testo DESTRA() restituisce i cinque caratteri all’estremità destra
della stringa (Figura 2.9).
02 Ex Fun.indd 50
19/04/12 12.02
Utilizzare le funzioni di testo e dati 51
Capitolo 2
Figura 2.9 Il CAP a cinque cifre viene estratto con la funzione di tabella DESTRA().
Separare i nomi dai cognomi
Supponi che un elenco di nominativi contenga sia il nome sia il cognome. Separare i nomi
dai cognomi è più complesso dell’estrazione del CAP a cinque cifre, poiché i nomi non hanno
una lunghezza standard.
Supponendo che la cella A21 contenga il nome e il cognome separati da uno spazio, immetti
la formula seguente in B21 per estrarre il nome (Figura 2.10):
=SINISTRA(A21;TROVA(" ";A21)-1)
Figura 2.10 Il nome di battesimo viene estratto specificando il separatore.
Poiché i nomi non hanno tutti la stessa lunghezza, devi utilizzare la funzione TROVA() per
trovare il primo spazio. Questa funzione restituisce la posizione dello spazio nel testo sotto
forma di numero. Tale numero viene diminuito di 1 e utilizzato nella funzione SINISTRA()
come argomento del numero di caratteri per restituire il nome di battesimo.
Per estrarre il cognome, immetti la seguente formula nella cella C21:
=DESTRA(A21;LUNGHEZZA(A21)-TROVA(" ";A21))
La funzione DESTRA() estrae il cognome. Per calcolare il numero di caratteri da estrarre da
destra, si utilizza la lunghezza dell’intero nome e si sottrae da questa il numero di caratteri
della stringa fino al primo spazio, utilizzando di nuovo la funzione TROVA() per trovare la
posizione dello spazio. Ma ciò non basta. Sarebbe una mera coincidenza se lo spazio si trovasse nella stessa posizione del numero di caratteri letto da destra.
02 Ex Fun.indd 51
19/04/12 12.02
52
Capitolo 2 Utilizzare le funzioni e PowerPivot
Perciò, si utilizza la funzione LUNGHEZZA(), la quale identifica il numero complessivo di
caratteri del testo. Se sottrai il numero della posizione dello spazio dal numero totale dei
caratteri, come in LUNGHEZZA(A21)-TROVA(“ “;A21), ottieni la lunghezza del cognome.
Invertire l’ordine di nomi e cognomi
Se in una colonna il cognome è posizionato davanti al nome di battesimo, potresti voler
invertire l’ordine. Supponiamo che la cella A5 contenga il nome Jayne, Darcy. A condizione
che l’ordine di cognomi e nomi sia uguale e il separatore sia una virgola più uno spazio, puoi
utilizzare la formula seguente per invertire l’ordine di nomi e cognomi:
Capitolo 2
=DESTRA(A5;LUNGHEZZA(A5)-TROVA(",";A5)-1)&" "&SINISTRA(A5;TROVA(",";A5)-1)
Il risultato è Darcy Jayne (Figura 2.11).
Il nome di battesimo viene estratto tramite la funzione
=DESTRA(A5;LUNGHEZZA(A5)-TROVA(",";A5)-1
Nota che la funzione TROVA individua la posizione della virgola e occorre poi sottrarre un
ulteriore carattere per calcolare lo spazio dopo la virgola. Il cognome all’inizio della stringa
di testo utilizza la funzione
= SINISTRA(A5;TROVA(",";A5)-1)
I due nomi vengono poi concatenati, con uno spazio come separatore, utilizzando
& " " &
Figura 2.11 Nidificato ma efficace: invertire nome e cognome con una formula.
02 Ex Fun.indd 52
19/04/12 12.02
Utilizzare le funzioni di testo e dati 53
Scomporre l’IBAN
1. Codice paese DE (due caratteri).
2. Cifra di controllo (due caratteri).
3. Codice banca (otto caratteri).
4. Numero di conto (dieci caratteri). Se il numero di conto è più breve, si aggiungono zeri
iniziali.
Capitolo 2
L’IBAN (International Bank Account Number) è richiesto per le transizioni monetarie internazionali. Poiché questo numero è composto di parti con una lunghezza fissa, puoi estrarre
i singoli componenti dell’IBAN. L’IBAN può contenere fino a 34 caratteri. Un IBAN tedesco è
composto da 22 caratteri nell’ordine seguente (Figura 2.12):
Figura 2.12 Scomporre l’IBAN nelle sue componenti con la funzione STRINGA.ESTRAI().
Per separare le parti che compongono l’IBAN nella cella B4, utilizza la formula seguente:
●
Codice paese: =SINISTRA(B4;2)
●
Cifra di controllo: =STRINGA.ESTRAI(B4;3;2)
●
Codice banca: =STRINGA.ESTRAI(B4;5;8)
●
Numero di conto: =STRINGA.ESTRAI(B4;13;10) o =DESTRA(B4;10)
Hai già utilizzato le funzioni SINISTRA() e DESTRA() negli esempi precedenti. Per estrarre stringhe dal centro di una stringa di testo, utilizza la funzione STRINGA.ESTRAI(testo;
inizio;num_caratt).
02 Ex Fun.indd 53
19/04/12 12.02
54
Capitolo 2 Utilizzare le funzioni e PowerPivot
Calcolare la frequenza di un carattere in una stringa
Per attività di tipo giornalistico e per l’analisi di un testo, potrebbe essere necessario stabilire
quanto spesso un determinato carattere o stringa appare in un blocco di testo. Con il trucco
che segue, puoi farlo in Excel: se la cella B3 contiene la stringa e la cella A7 contiene il testo
da analizzare, utilizza la formula seguente:
=(LUNGHEZZA(A7)-LUNGHEZZA(SOSTITUISCI(A7;$B$3;"")))/LUNGHEZZA($B$3)
Capitolo 2
La funzione LUNGHEZZA(A7) calcola il numero di caratteri nella cella A7. La funzione SOSTITUISCI() nella seconda parte della formula sostituisce tutte le occorrenze dei caratteri nella
stringa di testo con niente (“”) e calcola la nuova lunghezza. Se sottrai questa lunghezza dalla
lunghezza originale della stringa e la dividi per la lunghezza della stringa di testo, troverai il
numero delle occorrenze (Figura 2.13).
Figura 2.13 Il trucco per contare la frequenza dei caratteri.
Rimuovere tutti gli spazi
In alcuni casi, potresti voler rimuovere gli spazi vuoti da una stringa di testo. Per eseguire
questa operazione, poi utilizzare la funzione SOSTITUISCI() (Figura 2.14). Se vuoi rimuovere
tutti gli spazi dal testo della cella A15, per esempio, puoi utilizzare la formula seguente:
=SOSTITUISCI(A15;" ";"")
Figura 2.14 La funzione SOSTITUISCI() sostituisce le stringhe.
02 Ex Fun.indd 54
19/04/12 12.02
Utilizzarelefunzioniditestoedati
55
Questa è la funzione con la sua sintassi:
SOSTITUISCI(testo;testo_prec;nuovo_testo;ricorrenza)
Questa funzione sostituisce il vecchio testo con il nuovo testo in una stringa. In questo esempio, lo spazio (“ “) è sostituito da una stringa vuota (“”). New York diventa NewYork. Puoi
utilizzare la funzione SOSTITUISCI() anche per sostituire alcuni caratteri in una stringa di testo.
Capitolo 2
Suggerimento
Per eliminare gli spazi in eccesso prima e dopo il testo, utilizza la funzione ANNULLA.
SPAZI().
Correggere la posizione dei segni
Quando si importano dati, talvolta il segno meno dei numeri negativi appare dopo il valore. Tuttavia, questa espressione non può essere utilizzata nei calcoli in quanto Excel non
la riconosce come numero. Infatti, la posizione del segno meno fa sì che il numero venga
interpretato come un valore testuale.
Per convertire il valore della cella A4 in un numero, utilizza la formula seguente (Figura 2.15):
=SE(DESTRA(A4;1)="-";SINISTRA(A4;LUNGHEZZA(A4)-1)*(-1);A4)
Figura 2.15 Convertire un numero modificando la posizione del segno negativo.
La funzione SE() viene utilizzata per verificare il contenuto di parte di una cella. La funzione
DESTRA() controlla la presenza del segno meno. La funzione SINISTRA() separa la parte
numerica e la converte nel valore negativo del numero moltiplicandola per –1.
Visualizzare il nome del file e del foglio di lavoro
La formula seguente visualizza il nome del file corrente. Immetti la formula seguente nella
cella A21:
=CELLA("nomefile")
02 Ex Fun.indd 55
19/04/12 12.02
56
Capitolo 2 Utilizzare le funzioni e PowerPivot
Questa funzione restituisce il nome del file del foglio di lavoro corrente. Vengono visualizzati
il percorso completo del file e il nome del foglio di lavoro. Se il foglio di lavoro corrente non
è ancora stato salvato, la funzione restituisce una stringa vuota.
All’interno della stringa il nome del file è racchiuso tra parentesi quadre, cosa che può essere
utile per estrarre il nome dalla stringa. La formula seguente estrae il nome del file dal risultato:
=STRINGA.ESTRAI(A21;TROVA("[";A21;1)+1;TROVA("]";A21;1)-TROVA("[";A21;1)-1)
Capitolo 2
La funzione STRINGA.ESTRAI() calcola il numero di caratteri dalla posizione iniziale. L’espressione TROVA(“[“;A21;1)+1 determina la posizione iniziale dopo la parentesi sinistra. L’espressione TROVA(“]”;A21;1)-TROVA(“[“;A21;1)-1 calcola il numero di caratteri compreso tra le
parentesi sinistra e destra.
Puoi utilizzare questa funzione anche per visualizzare il nome del foglio di lavoro. Per fare
ciò, utilizza la formula seguente:
=STRINGA.ESTRAI(A21;TROVA("]";A21;1)+1;LUNGHEZZA(A21))
Concatenare il contenuto delle celle
La Figura 2.16 mostra un tipico elenco di indirizzi. Per concatenare i dati dell’elenco, utilizza
la formula seguente:
=A4&" "&C4&" "&B4&" vive a "&D4&" "&E4&"."
Figura 2.16 L’operatore di concatenazione & combina testo e valori in espressioni più complesse.
02 Ex Fun.indd 56
19/04/12 12.02
Utilizzarelefunzioniditestoedati
57
Con l’operatore di concatenazione & puoi concatenare testo e riferimenti. Puoi utilizzare
anche la funzione di testo CONCATENA(), che restituisce lo stesso identico risultato.
=CONCATENA(A4;" ";C4;" ";B4;" vive a ";D4;" ";E4;".")
Per alcune concatenazioni di testo, potresti voler inserire un’interruzione di riga in una posizione diversa dal margine destro, per esempio dopo un certo numero di caratteri o dopo una
determinata parola. Puoi premere Alt+Invio per inserire l’interruzione in qualunque posizione.
Appena premi il tasto Invio, l’interruzione di riga diventa visibile nella cella.
Puoi anche collegare due stringhe con un carattere di fine riga (Figura 2.17). Il carattere di fine
riga ha un codice carattere pari a 10 e può essere aggiunto con la funzione CODICE.CARATT():
Capitolo 2
Inserire interruzioni di riga nel testo concatenato
=CONCATENA(A4;" ";C4;" ";B4;CODICE.CARATT(10);"vive a ";D4;" ";E4;".")
Figura 2.17 Le concatenazioni di testo possono contenere interruzioni di riga e altri caratteri
speciali.
APPROFONDIMENTO
Visualizzare le interruzioni di riga nelle celle
Affinché un’interruzione di riga sia visibile, la cella deve avere l’opzione di a capo automatico attivata. Nel menu di scelta rapida della cella, seleziona Formato celle e poi, sulla
scheda Allineamento, seleziona la casella di controllo Testo a capo.
02 Ex Fun.indd 57
19/04/12 12.02
58
Capitolo2 UtilizzarelefunzioniePowerPivot
Visualizzare i dati
Probabilmente, per presentare i numeri utilizzi spesso i grafici di Excel. Tuttavia, per visualizzare i dati puoi utilizzare anche grafica interna alla cella invece dei grafici. A tale scopo,
puoi servirti della funzione RIPETI(), come si vede nella Figura 2.18.
Capitolo 2
Figura 2.18 Creare grafica con la funzione RIPETI().
In questo esempio, le chiamate evase dal personale addetto all’assistenza sono inserite in
un elenco. La formula
=RIPETI("(";B4)
converte il numero 12 in B4 in dodici icone telefono. Affinché la cella della formula visualizzi
la parentesi iniziale come icona telefono, dovrai selezionare il font Wingdings.
File di esempio
Utilizza il foglio di lavoro Chiamate assistenza del file di esempio Dati_testo.xls o Dati_testo.xlsx nella cartella Capitolo02. Per ulteriori informazioni sui file di esempio, vedi la
sezione “Utilizzare i file di esempio” nell’introduzione.
Utilizzare le funzioni logiche
Le funzioni logiche sono tra gli strumenti più versatili di Excel. Possono essere utilizzate per
automatizzare calcoli, personalizzare visualizzazioni e fornire opzioni di decisione. L’esempio
di questa sezione presenta un’istruzione decisionale di base. In questo esempio, una società
ha una procedura per generare i prezzi delle merci. La Figura 2.19 mostra il calcolo su un
foglio di lavoro.
02 Ex Fun.indd 58
19/04/12 12.02
Utilizzare le funzioni logiche
59
Capitolo 2
Figura 2.19 Un calcolo differenziale tipico.
Un rivenditore compra merci da un fornitore e ottiene uno sconto. Ottiene, inoltre, anche
uno sconto per il pagamento in contanti. Le spese di consegna vengono aggiunte al prezzo di acquisto e, dopo l’aggiunta di altri costi (come l’affitto e gli stipendi), il costo diretto
ammonta a €4.482,82.
Il prezzo di vendita al cliente deve includere uno sconto e, inoltre, occorre pagare il venditore.
La differenza tra il prezzo di vendita in contanti e i costi originali è il profitto. Il profitto viene
calcolato come percentuale sul costo originale.
Ogni fase del calcolo viene eseguita in Excel. Il profitto viene calcolato con
=D14-D12
e la percentuale con
=D13/D12
Calcolare la redditività
Ma come fa Excel a sapere se il profitto richiesto va bene? Supponi che il rivenditore si accontenti del 20%. Egli immette la formula seguente nella cella C21 (nella figura è unita alla
cella a destra successiva):
=SE(C13>=20%;"OK";"troppo basso")
02 Ex Fun.indd 59
19/04/12 12.02
60
Capitolo 2 Utilizzare le funzioni e PowerPivot
La funzione SE() controlla la percentuale ed esegue un calcolo basandosi sul risultato. La
funzione controlla se il valore della cella C13 è maggiore o uguale al 20%. Se il valore è
maggiore o uguale al 20%, viene visualizzato “OK”. Altrimenti, viene visualizzato il testo
alternativo “Troppo basso”.
Capitolo 2
Questo esempio verifica una sola condizione, ma può essere esteso a condizioni più complesse con funzioni logiche aggiuntive, come la funzione E() e altre istruzioni SE(). Per esempio,
magari il rivenditore non vuole ridurre il proprio profitto ma deve considerare la concorrenza
nonché il livello massimo dei prezzi. Allora, potrebbe voler evidenziare visivamente la cella
che contiene la percentuale di profitto. In Excel 2007 ed Excel 2010, può aprire la finestra
di dialogo che appare nella Figura 2.20. L’opzione Nuova regola può essere raggiunta dalla
scheda Home facendo clic su Formattazione condizionale nel gruppo Stili.
Se stai utilizzando una versione più vecchia di Excel, seleziona Formato/Formattazione condizionale.
La formula
=E(($C$13>=20%);($D$19<6500))
verifica se vengono soddisfatte le due condizioni seguenti:
●
Il valore della cella C13 è maggiore o uguale al 20%.
●
Il prezzo di vendita immesso è inferiore a €6.500.
Se entrambe le condizioni sono soddisfatte, la cella si colora di verde.
Figura 2.20 Formattazione condizionale e funzioni logiche.
02 Ex Fun.indd 60
19/04/12 12.02
Utilizzarelefunzionidiricercaeriferimento
61
File di esempio
Utilizza il foglio di lavoro Calcolo differenziale del file di esempio CalcoloDifferenziale.
xls o CalcoloDifferenziale.xlsx nella cartella Capitolo02. Per ulteriori informazioni sui file
di esempio, vedi la sezione “Utilizzare i file di esempio” nell’introduzione.
Capitolo 2
Utilizzare le funzioni di ricerca e riferimento
La struttura a tabella dei fogli lavoro di Excel consente all’utente di eseguire ricerche di informazioni all’interno dei fogli di lavoro in modo sistematico.
Eseguire ricerche tramite una tabella incrociata
Supponi di disporre di informazioni sui prezzi di un articolo in forma di tabella (in questo
esempio, magliette di taglie e colori diversi). Dopo avere selezionato un colore e una taglia,
vuoi ricercare il prezzo e utilizzarlo per ulteriori calcoli (Figura 2.21). Per eseguire questa
operazione, si utilizza una tipica tabella a campi incrociati.
Figura 2.21 Prezzi basati su taglia e colore.
02 Ex Fun.indd 61
19/04/12 12.02
62
Capitolo 2 Utilizzare le funzioni e PowerPivot
Excel offre una varietà di funzioni di ricerca che è possibile utilizzare per questo tipo di
operazione. Esistono tre funzioni principali: CERCA(), CERCA.VERT() e CERCA.ORIZZ(), oltre
alle funzioni CONFRONTA() e INDICE(), che offrono funzionalità simili. Tutte queste funzioni
cercano informazioni in un’area rettangolare in base a determinati criteri e restituiscono le
informazioni oppure la posizione dell’informazione. Gli esempi che seguono dimostrano
come utilizzare alcune di tali funzioni.
Capitolo 2
La funzione CERCA.VERT() prende un valore e cerca di trovare una corrispondenza con i valori
della prima colonna di una tabella. Quando viene trovata una corrispondenza, la funzione
restituisce tutte le informazioni associate all’immissione corrispondente nella tabella. La ricerca
può essere effettuata in due modi diversi:
●
●
Cercare una corrispondenza esatta dell’elemento nella prima colonna della tabella.
Cercare un valore, sia il valore esatto sia quello più prossimo ma inferiore a quello
ricercato. In questo caso, la tabella di ricerca deve essere ordinata sulla prima colonna.
La funzione CONFRONTA() ricerca una matrice di valori e restituisce la posizione della prima
corrispondenza in quella matrice, cioè la colonna oppure la riga. Le corrispondenze possono
essere esatte oppure il valore più alto o quello più basso.
La Figura 2.21 mostra i prezzi di magliette di colori diversi e di varie taglie. Vuoi cercare le
informazioni non solo in una determinata riga ma anche in una determinata colonna. Procedi un passo per volta. Per prima cosa, cerca la colonna che contiene la taglia desiderata.
La terza colonna contiene la taglia M. Se vuoi che Excel riconosca questo valore, premi F10
per immettere 3 in una cella ausiliaria. La formula =CONFRONTA(C10;C4:H4;1) trova il valore
della cella C10 (taglia M) nell’intestazione delle celle comprese tra C4 e H4 e restituisce il
numero della colonna.
Al secondo passo si giunge tramite la funzione CERCA.VERT(). Immetti la formula seguente
nella cella C12:
=CERCA.VERT(C9;B5:H7;F10+1;FALSO)
Questa formula cerca il contenuto della cella C9 (giallo) nella prima colonna delle celle comprese tra B5 e H7. Se viene trovato un valore, la formula lo restituisce nella quarta colonna
e il lavoro è fatto!
Per rendere questa operazione ancora più raffinata, ci sono svariate altre cose che puoi fare.
Invece di utilizzare due formule, puoi incorporare la funzione CONFRONTA() nella funzione
CERCA.VERT() per ottenere il risultato da un’unica formula. La formattazione condizionale
potrebbe essere utile per evidenziare la cella selezionata.
02 Ex Fun.indd 62
19/04/12 12.02
Utilizzarelefunzionidiricercaeriferimento
63
File di esempio
Un’altra funzione comune di questa categoria è la funzione SCARTO(), che si concentra su
un intervallo specifico e ti consente di regolarlo dinamicamente definendo lo spostamento
di righe e colonne.
Dà uno sguardo all’esempio seguente (Figura 2.22), in cui vengono riportate su una tabella
le temperature diurne relative a un periodo di cinque giorni. Supponi di voler visualizzare le
temperature diurne di un giorno selezionato.
Capitolo 2
Utilizza il foglio di lavoro Camicie del file di esempio Ricerca.xls o Ricerca.xlsx nella cartella Capitolo02. Per ulteriori informazioni sui file di esempio, vedi la sezione “Utilizzare
i file di esempio” nell’introduzione.
Figura 2.22 Grafici dinamici.
La soluzione consiste nel collegare dinamicamente la colonna utilizzata dal grafico sulla
temperatura a un valore di input sul foglio di lavoro. Inizia creando un grafico a linee per
visualizzare le temperature nella giornata di lunedì.
Ci sono alcuni trucchi di cui puoi servirti. Innanzitutto, è possibile denominare non sono gli
intervalli ma anche le formule. In Excel 2007 ed Excel 2010, sulla scheda Formule, seleziona
Nomi definiti/Definisci nome, mentre nelle versioni precedenti del programma selezionerai
Inserisci/Nomi/Definisci per denominare le formule. Utilizza queste opzioni per definire intervalli dinamici per la legenda e la serie di dati per il grafico. Definisci il nome Giorno come:
=SCARTO(Grafico!$C$4;0;Grafico!$B$23)
Il riferimento nella cella C4 del foglio di lavoro Grafico viene spostato in basso di zero righe
e verso destra del numero di colonne indicato nella cella B23 (Figura 2.22).
02 Ex Fun.indd 63
19/04/12 12.02
64
Capitolo2 UtilizzarelefunzioniePowerPivot
Fai la stessa cosa per i dati del grafico. Utilizza il nome Dati e collegalo con la formula
=SCARTO(Grafico!$C$5:$C$19;0;Grafico!$B$23)
Questa formula si applica a un’intera colonna invece che a una sola riga. Il riferimento viene
spostato allo stesso modo. Dopo aver creato il grafico, fai clic sulla linea tracciata. Nella casella
di immissione appare qualcosa di simile a ciò che segue:
=SERIE(Grafico!$C$4;Grafico!$B$5:$B$19;Grafico!$C$5:$C$19;1)
Capitolo 2
Modifica questa immissione sostituendo i riferimenti assoluti di cella con i tuoi riferimenti
dinamici:
=SERIE(Ricerca.xlsx!Giorno;Grafico!$B$5:$B$19;Ricerca.xlsx!Dati;1)
Devi utilizzare il nome della cartella di lavoro e i nomi devono essere separati da un punto
esclamativo. Per ottenere una soluzione perfetta, dovresti aggiungere anche una casella di
selezione per selezionare il valore nella cella B23.
File di esempio
Utilizza il foglio di lavoro Grafico del file di esempio Ricerca.xls o Ricerca.xlsx nella cartella
Capitolo02. Per ulteriori informazioni sui file di esempio, vedi la sezione “Utilizzare i file
di esempio” nell’introduzione.
Utilizzare le funzioni informative
Le funzioni informative sono simili alle funzioni logiche e servono a integrare e supportare
altre funzioni e formule.
Al cuore delle funzioni informative ci sono le cosiddette funzioni VALORE (Tabella 2.2), che
forniscono informazioni sui tipi di espressione o sul contenuto della cella. Queste informazioni possono essere visualizzate o, meglio ancora, utilizzate per altri calcoli. I nomi di queste
funzioni sono di chiara interpretazione e i valori restituiti sono sempre valori logici. Il valore
logico conferma se il valore della cella specificato nell’argomento è del tipo richiesto. Di solito,
l’argomento per le funzioni è un riferimento di cella, ma talvolta, per esempio nel caso della
funzione VAL.RIF(), viene fornito un valore specifico a scopo di prova.
02 Ex Fun.indd 64
19/04/12 12.02
Utilizzare le funzioni informative 65
Funzione
Definizione
VAL.VUOTO()
La cella è vuota.
VAL.TESTO(), VAL.NON.TESTO()
Il valore della cella è testo o non è testo.
VAL.NUMERO()
Il valore della cella è un numero.
VAL.LOGICO()
Il valore della cella è un valore logico.
VAL.ERR(), VAL.ERRORE()
La cella contiene un errore.
VAL.NON.DISP()
La cella contiene un errore #N/D.
VAL.PARI(),VAL.DISPARI()
Il valore della cella è un numero pari o dispari.
Quando ti servi di intervalli denominati e li utilizzi come riferimento, ottieni un errore #RIF!
se cancelli accidentalmente il nome. Per esempio, supponi che le celle comprese tra B2 e D5
siano state denominate Fatturato e che la cella F7 contenga
Capitolo 2
Tabella 2.2 Panoramica delle funzioni VALORE
=SOMMA(Fatturato)
Questa formula funziona solo se il nome Fatturato esiste veramente. Tuttavia, la formula
=SE(VAL.RIF(Fatturato);SOMMA(Fatturato);"Il nome di intervallo Fatturato è stato cancellato.")
è in qualche modo più sicura. La funzione VAL.RIF(Fatturato) restituisce FALSO se il nome
Fatturato è stato cancellato. Invece di calcolare la somma dell’intervallo sconosciuto e di
generare un errore, Excel visualizza un messaggio che riporta la causa dell’errore. Se il nome
dell’intervallo esiste, viene calcolato il totale.
Possono facilmente verificarsi errori quando si utilizza la funzione CERCA.VERT(), specialmente
quando si cerca un elemento non presente nell’elenco. Nella Figura 2.23, la formula
=CERCA.VERT(B13;D13:E15;2;FALSO)
genera un errore #N/D (N/D=non disponibile) per indicare che l‘elemento Camicetta non è
presente nell’elenco. La formula
=SE(VAL.ERRORE(CERCA.VERT(B13;D13:E15;2;FALSO));"non trovato";CERCA.
VERT(B13;D13:E15;2;FALSO))
è più lunga, ma verifica prima se CERCA.VERT() ha dato un esito positivo. In caso contrario,
appare il messaggio “non trovato”; se la funzione ha dato un esito positivo, viene visualizzato
il prezzo.
Figura 2.23 Cercare informazioni negli elenchi.
02 Ex Fun.indd 65
19/04/12 12.02
66
Capitolo2 UtilizzarelefunzioniePowerPivot
Spesso, calcoli semplici possono determinare errori; considera, per esempio, l’utilizzo di una
funzione come 1/x (x non può essere zero) oppure LOG(x) (x deve essere un numero positivo).
Se vuoi creare un grafico da una serie di numeri, puoi manipolare i dati per essere sicuro che
i valori errati “scompaiano” dall’intervallo di origine del grafico.
Capitolo 2
Figura 2.24 Questa presentazione di 1/x è errata poiché i valori di errore vengono mostrati
come zeri.
Per evitare la presentazione errata della Figura 2.24, immetti
=SE(VAL.ERRORE(1/B13);NON.DISP();1/B13)
In tal modo, si intercetta l’errore #DIV/0! e lo si sostituisce con la funzione NON.DISP() che
indica che il valore non esiste. Fai la stessa cosa per gli altri valori di errore.
Nota
Puoi anche cancellare la formula dalla cella che contiene l’errore e selezionare l’opzione
grafico che ti consente di non rappresentare celle vuote.
02 Ex Fun.indd 66
19/04/12 12.02
Utilizzare le funzioni statistiche 67
Utilizzare le funzioni statistiche
La statistica è la scienza dell’utilizzo di metodi per gestire informazioni quantitative (dati).
Tuttavia, la maggior parte delle persone associano la parola statistica a grosse tabelle e grafici
che illustrano (o talvolta nascondono) dati diversi relativi, per esempio, a gruppi demografici,
attività economiche, malattie o impatti ambientali.
I dati vengono raccolti per scopi molteplici. Spesso consistono di campioni casuali prelevati da
una popolazione più ampia (reale o ipotetica) e vengono utilizzati per formulare enunciazioni
generalizzate che riguardano l’intera popolazione, prendendo in considerazione variazioni
casuali nei valori campione.
Capitolo 2
Ma questa è solo una parte della scienza statistica, chiamata anche statistica descrittiva. La
statistica viene utilizzata per illustrare dati su vasta scala ma anche per riepilogare grosse
quantità di informazioni con poche semplici misure, quali la media e la varianza.
La statistica analitica si basa su modelli di probabilità e principi derivati dalla teoria della
probabilità. Supponiamo di gestire un’azienda e di voler scoprire se sia più vantaggioso
investire in una massiccia campagna pubblicitaria o ridurre i costi. Vogliamo esaminare con
cura gli introiti basati su una strategia prescelta. Inoltre, vogliamo anche conoscere i mesi
con le entrate più basse e più alte e la correlazione tra costi e vendite.
Ecco un altro esempio relativo a qualcosa che prima o poi avrà riguardato tutti noi: quanto
alta è la probabilità di vincere la lotteria e di passare il resto della vita ai Caraibi?
Tutte queste sono domande statistiche ed Excel può aiutarti a trovare le risposte.
Panoramica
Per una migliore comprensione, questo libro suddivide le funzioni statistiche in sei aree. Nelle
sezioni seguenti vengono presentate le varie aree con le funzioni corrispondenti.
Il percorso verso la giusta via di mezzo
Excel offre svariate funzioni per calcolare valori medi diversi. I valori medi forniscono un
resoconto veloce dei dati disponibili e sono il primo passo dell’analisi. Questi valori ti aiutano
a trovare il valore medio in un elenco di valori.
La media aritmetica è una delle misure di confronto più comuni. Grazie alla semplicità nel
calcolarla e alla sua diffusione, la media aritmetica piace anche a chi non sia un professionista
della statistica. Esistono, comunque, molte altre misure della media che è possibile utilizzare,
come mostrato nella Tabella 2.3.
02 Ex Fun.indd 67
19/04/12 12.02
68
Capitolo 2 Utilizzare le funzioni e PowerPivot
Tabella 2.3 Funzioni del tipo valore media
ESC.PERCENT.RANGO()
MEDIA()
MEDIA.VALORI()
ESC.PERCENTILE()
MEDIA.ARMONICA()
MEDIANA()
ESC.QUARTILE()
MEDIA.GEOMETRICA()
MODA.MULT()
INC.PERCENT.RANGO()
MEDIA.PIÙ.SE()
MODA.SNGL()
INC.PERCENTILE()
MEDIA.SE()
INC.QUARTILE()
MEDIA.TRONCATA()
Capitolo 2
Correlazione e regressione
La correlazione ti permette di considerare il rapporto tra due variabili. Per esempio, un’azienda
produttrice di beni potrebbe voler valutare se è opportuno effettuare ulteriori test di qualità.
Oppure, un’azienda potrebbe voler conoscere la relazione tra vendite e pubblicità. In Excel
sono disponibili svariate misure e test di correlazione e regressione (Tabella 2.4).
Tabella 2.4 Funzioni di regressione
CORRELAZIONE()
INTERCETTA()
REGR.LOG()
COVARIANZA.P()
PEARSON()
RQ()
COVARIANZA.S()
PENDENZA()
TENDENZA()
CRESCITA()
PREVISIONE()
DEV.Q()
REGR.LIN()
Simmetria
Sono disponibili due funzioni statistiche di simmetria (Tabella 2.5):
●
●
La funzione ASIMMETRIA() caratterizza il livello di asimmetria intorno alla media. Se i
dati sono distribuiti simmetricamente, la media, la moda e la mediana coincidono.
La curtosi di un gruppo di dati misura la forma e la dispersione della distribuzione in
confronto a una distribuzione normale.
Tabella 2.5 Funzioni di simmetria
CURTOSI()
ASIMMETRIA()
Excel e la dispersione
Quanto più i valori dei singoli dati deviano dalla media, tanto meno rappresentativa della
distribuzione sarà la media. Il valore media da solo non è sufficiente a caratterizzare una
distribuzione di frequenza. Occorre considerare anche in che misura i valori deviano dal
valore media. Per questo, si calcola la dispersione. Le funzioni nella Tabella 2.6 mostrano
tutti i tipi di varianza.
02 Ex Fun.indd 68
19/04/12 12.02
Utilizzare le funzioni statistiche 69
Tabella 2.6 Funzioni di varianza
DEV.ST.C()
DEV.ST.VALORI()
VAR.P()
DEV.ST.P()
MEDIA.DEV()
VAR.POP.VALORI()
DEV.ST.POP.VALORI()
VAR.C()
VAR.VALORI()
Il calcolo probabilistico costituisce una parte essenziale della statistica. Poiché spesso non si
lavora su popolazioni complete ma solamente su campioni più piccoli, occorre comprendere
la probabilità e le conclusioni derivate. I calcoli di probabilità vengono utilizzati per pronosticare risultati di studi basati su determinati presupposti.
La statistica analitica sovente confronta i risultati per stabilire se i valori statistici di dati
raccolti empiricamente siano diversi. Un esempio consiste nella valutazione dell’efficacia dei
farmaci. La frequenza relativa degli eventi osservati in esperimenti casuali viene analizzata
e utilizzata per determinare la probabilità degli eventi. La Tabella 2.7 mostra le funzioni di
probabilità disponibili in Excel.
Capitolo 2
Tendenze e previsioni
Tabella 2.7 Funzioni di probabilità
CONFIDENZA.T()
DISTRIB.T.DS()
INV.NORM()
DISTRIB.BINOM.NEG()
DISTRIB.WEIBULL()
INV.NORM.ST()
DISTRIB.EXP()
FISHER()
PERMUTAZIONE()
DISTRIB.LOGNORM()
INV.F()
PROBABILITÀ()
DISTRIB.NORM.ST()
INV.F.DS()
TEST.F()
DISTRIB.T()
INV.GAMMA()
DISTRIB.T.2T()
INV.LOGNORM()
Uno più uno
L’analisi di dati statistici comporta molti calcoli. Per esempio, se si analizza un sondaggio,
occorre sapere innanzitutto quanti questionari sono stati restituiti. Poi bisogna contare la
frequenza con cui appaiono i singoli risultati, vale a dire singole caratteristiche con lo stesso
valore. Di solito, ciò viene definita analisi dei dati univariata. Per contare le combinazioni di
caratteristiche, per esempio le vendite in una determinata provincia, occorre considerare una
distribuzione bivariata. Se vengono aggiunte più variabili, si parla di distribuzione multivariata
o multidimensionale.
Sono disponibili vari strumenti per raggruppare, consolidare e analizzare i dati. La Tabella
2.8 mostra le funzioni di analisi di Excel.
02 Ex Fun.indd 69
19/04/12 12.02
70
Capitolo2 UtilizzarelefunzioniePowerPivot
Tabella 2.8 Funzioni di conteggio
CONTA.NUMERI()
FREQUENZA()
MIN.VALORI()
CONTA.PIÙ.SE()
GRANDE()
PICCOLO()
CONTA.SE()
MAX()
RANGO.MEDIA()
CONTA.VALORI()
MAX.VALORI()
RANGO.UG()
CONTA.VUOTE()
MIN()
Capitolo 2
La gamma delle funzioni e delle analisi statistiche è molto ampia ed Excel offre un set di
strumenti completo per analizzare la maggior parte dei dati. Alcuni strumenti generano,
oltre alle tabelle, anche grafici.
Novità di Excel 2010
Excel 2010 offre alcune funzioni statistiche nuove. Per esempio, la funzione INVT() è
stata sostituita dalle funzioni INV.T() e INV.T.2T() per accrescere l’accuratezza dei risultati. Inoltre, le distribuzioni T destra e sinistra sono ora mostrate separatamente. Per
garantire la compatibilità con le versioni precedenti di INV.T() e INV.T.2T(), la funzione
INVT() è ancora disponibile.
Le funzioni statistiche in pratica
Gli esempi pratici seguenti mostrano calcoli tipici che utilizzano le funzioni statistiche di Excel.
File di esempio
Utilizza il foglio di lavoro Elenco del file di esempio Statistica_pratica.xls o Statistica_pratica.xlsx nella cartella Capitolo02. Per ulteriori informazioni sui file di esempio, vedi la
sezione “Utilizzare i file di esempio” nell’introduzione.
Un centro di formazione ha condotto una ricerca sui partecipanti a corsi su Excel, Microsoft
PowerPoint e Microsoft Outlook in ciascun giorno del mese di luglio 2008, e sono state
raccolte le informazioni mostrate nella Figura 2.25. Sono stati distribuiti 184 questionari.
02 Ex Fun.indd 70
19/04/12 12.02
Utilizzare le funzioni statistiche 71
Capitolo 2
Figura 2.25 I risultati del sondaggio sono consolidati in una tabella.
Trovare il numero di risposte
Nonostante siano stati distribuiti 184 questionari, supponi di voler conoscere il numero delle
risposte ricevute poiché non tutti hanno partecipato all’iniziativa. Puoi farlo utilizzando la
funzione CONTA.VALORI() che conta il numero dei questionari restituiti, mentre puoi utilizzare
la funzione MEDIA() per calcolare la valutazione media delle risposte (Figura 2.26).
Figura 2.26 La formula =CONTA.VALORI(C2:C185) restituisce 149 partecipanti.
Valutazione media
In che modo i partecipanti valutano i corsi del centro di formazione? Buoni, medi, pessimi?
Per ottenere un risultato rapido, calcolerai il valore medio utilizzando la funzione MEDIA().
Le valutazioni vengono sommate tra loro e il totale viene diviso per il numero delle valutazioni (Figura 2.27).
Figura 2.27 Calcolare la valutazione media di tutti i corsi.
La formula =MEDIA(D2:D185) restituisce una valutazione media pari a 2,26.
02 Ex Fun.indd 71
19/04/12 12.02
72
Capitolo2 UtilizzarelefunzioniePowerPivot
Forniscono valutazioni migliori gli uomini o le donne?
Il centro di formazione vuole sapere se siano stati gli uomini o le donne a valutare i corsi in
modo più favorevole. La funzione MEDIA.SE() può restituire questo risultato.
Nota
La funzione MEDIA.SE() è disponibile in Excel 2007 ed Excel 2010.
Capitolo 2
MEDIA.SE() calcola la valutazione media di tutte le immissioni che soddisfano criteri specifici,
in questo caso uomini o donne (Figura 2.28).
Figura 2.28 Calcolare la valutazione media dei corsi per gli uomini e le donne.
La formula =MEDIA.SE(C2:C185;”uomo”;D2:D185) restituisce una valutazione media pari a
2,50 per gli uomini. Con una valutazione media di 2,50, gli uomini valutano i corsi in modo
migliore delle donne.
Inoltre, il centro di formazione vuole sapere anche in che modo le donne valutano i corsi su
Excel. La funzione MEDIA.PIÙ.SE() restituisce la risposta al quesito.
Nota
La funzione MEDIA.PIÙ.SE() è disponibile in Excel 2007 ed Excel 2010.
MEDIA.PIÙ.SE() calcola il valore medio di tutte le celle in un determinato intervallo che soddisfano più criteri specifici, in questo caso donne ed Excel (Figura 2.29).
Figura 2.29 Calcolare la valutazione media dei corsi su Excel da parte delle donne.
La formula =MEDIA.PIÙ.SE(D2:D185;C2:C185;”donna”;B2:B185;”Excel”) restituisce la valutazione media 1,38 data dalle donne sui corsi su Excel.
02 Ex Fun.indd 72
19/04/12 12.02
Utilizzarelefunzionididatabase
73
Utilizzare le funzioni di database
Le funzioni di database sono particolarmente utili in quanto analizzano dati contenuti in
tabelle con intestazioni (nomi dei campi) e righe dati (record di dati). Il blocco delle informazioni contenute nelle righe e nelle colonne costituisce un database.
●
Assicurati che gli elenchi abbiano intestazioni di colonna.
●
Assicurati che gli elenchi non contengano righe o colonne vuote.
●
Assicurati che gli elenchi non contengano celle collegate.
In Excel 2003 e versioni precedenti, l’utilizzo di Excel come database era piuttosto limitato
poiché una tabella poteva avere un massimo di 65.536 righe. In Excel 2007 questo limite è
stato esteso: ora ogni tabella per gli elenchi di dati può avere 1.048.576 righe. La stessa cosa
vale per le colonne: in Excel 2003, una tabella poteva avere 156 colonne, mentre in Excel
2007 il numero delle colonne è stato portato a 16.384.
Capitolo 2
Utilizzare le funzioni di database è più semplice se si osservano le regole seguenti:
Nomi di database dinamici
I nomi dinamici sono un modo efficace per semplificare l’utilizzo dei dati nei database di Excel.
Se specifichi un nome dinamico, puoi aprire un modello di dati per visualizzare e acquisire
dati e poi cercare record di dati, trovare dati o immettere dati.
Quando si utilizzano i nomi dinamici, è importante ricordarsi di includere una riga vuota in
fondo e una colonna vuota accanto all’ultima immissione. Ciò evita problemi se successivamente vengono aggiunte nuove colonne o righe.
File di esempio
Utilizza il foglio di lavoro Dati del file di esempio FunzioniDB vuoto.xls o FunzioniDB
vuoto.xlsx nella cartella Capitolo02. Per ulteriori informazioni sui file di esempio, vedi
la sezione “Utilizzare i file di esempio” nell’introduzione.
Per semplificare i calcoli futuri, a questo database è stato dato un nome dinamico. Indipendentemente dalle dimensioni dell’elenco, questo nome è sempre lo stesso e puoi
utilizzarlo nelle funzioni di database.
02 Ex Fun.indd 73
19/04/12 12.02
74
Capitolo2 UtilizzarelefunzioniePowerPivot
Per assegnare un nome dinamico all’elenco di Excel, procedi nel modo seguente in Excel
2007 o Excel 2010:
1. Fai clic nella cella A1 o nella prima cella del database e poi clic sul pulsante Definisci
nome (Figura 2.30) nel gruppo Nomi definiti sulla scheda Formule.
Capitolo 2
Figura 2.30 Definire nomi in Excel 2007 o Excel 2010.
2. Immetti il nome Inizio nella finestra di dialogo Nuovo nome e poi fai clic su OK (Figura
2.31).
Figura 2.31 Specificare il punto di inizio del database.
Il punto di inizio del database è stato impostato. Ora devi generare il nome dinamico
del database con la funzione SCARTO(). Prima devi sapere quante immissioni esistono
nella colonna A e nella riga 1.
3. Fai clic sull’intestazione della colonna A per selezionare l’intera colonna e immetti il
nome Riga nella casella del nome (Figura 2.32). Premi il tasto Invio per confermare.
Figura 2.32 Assegnare un nome alla colonna A.
02 Ex Fun.indd 74
19/04/12 12.02
Utilizzarelefunzionididatabase
75
Nota
Il nome Riga per la colonna A ha un senso in quanto vuoi calcolare le immissioni
della colonna A (in altre parole, le righe).
4. Per testare le impostazioni, calcola il numero delle righe nella colonna A utilizzando
la funzione CONTA.VALORI(). Fai clic su una qualsiasi cella vuota del foglio di lavoro e
immetti la formula seguente:
=CONTA.VALORI(riga)
Poiché alla colonna A è stato assegnato il nome Riga, devi fare clic solo sulla colonna
A per selezionare l’argomento della funzione. Dal momento che il numero delle righe
è stato calcolato con la funzione CONTA.VALORI(), viene preso in considerazione il
contenuto di tutte le celle, inclusi testo e valori logici.
Capitolo 2
5. Premi il tasto Invio per confermare.
Se stai utilizzando la tabella di Excel del file di esempio FunzioniDB vuoto.xlsx, il risultato sarà 7.008. Ora calcolerai il numero di immissioni nella riga 1. Per eseguire
questa operazione utilizzerai la stessa procedura utilizzata per calcolare il numero
delle immissioni nella colonna A.
6. Fai clic sull’intestazione di riga per selezionare l’intera riga e inserisci il nome Colonna
nella casella del nome (Figura 2.33).
Figura 2.33 Assegnare un nome alla riga 1.
7. Per testare le impostazioni, calcola il numero delle immissioni nella riga 1 utilizzando
la funzione CONTA.VALORI(). Fai clic su una qualsiasi cella vuota del foglio di lavoro e
immetti la formula seguente:
=CONTA.VALORI(colonna)
Se stai utilizzando la tabella di Excel del file di esempio FunzioniDB vuoto.xlsx, il risultato sarà 6.
ATTENZIONE
!
Ricordati di eliminare le funzioni utilizzate per calcolare le immissioni della riga e della
colonna.
02 Ex Fun.indd 75
19/04/12 12.02
76
Capitolo 2 Utilizzare le funzioni e PowerPivot
Assegnando un nome alla colonna e alla riga e calcolando le immissioni contenute nella
colonna e nella riga, hai già posto le basi per il nome dinamico della matrice (database). Se
aggiungi o elimini colonne o righe, il valore all’interno delle celle della formula aumenterà
o diminuirà rispettivamente.
A questo punto puoi di fatto assegnare un nome dinamico al database nell’intervallo seguente:
l’intervallo del database inizia alla cella A1 a cui hai dato il nome Inizio; questa posizione viene
estesa verso il basso per il numero di righe calcolate con la funzione =CONTA.VALORI(riga);
in questo caso, 7.008 immissioni; contemporaneamente, la posizione viene estesa sei colonne
a destra, a cominciare dalla cella Inizio, sulla base del risultato del calcolo
Capitolo 2
=CONTA.VALORI(colonna)
Ora devi sottrarre una posizione in ciascuna delle due dimensioni dell’intervallo del database.
La funzione seguente assegna il nome dinamico e definisce l’intervallo del database:
=Inizio:SCARTO(Inizio;CONTA.VALORI(Riga)-1;CONTA.VALORI(Colonna)-1)
Procedi come segue:
1. Sulla scheda Formule, fai clic sul pulsante Definisci nome nel gruppo Nomi definiti.
2. Nella finestra di dialogo Nuovo nome, immetti il nome Database.
3. Nel campo Riferito a immetti la formula riportata subito prima di questi passi, come
si vede nella Figura 2.34. Se non stai utilizzando il foglio di lavoro del file di esempio
nella Cartella02, adatta le immissioni di conseguenza.
Figura 2.34 Specificare l’intervallo del database.
4. Fai clic sul pulsante OK.
Per verificare il nome, premi il tasto F5 e immetti il nome Database nel campo Riferimento
della finestra di dialogo Vai a (Figura 2.35) che viene visualizzata. Fai clic su OK.
02 Ex Fun.indd 76
19/04/12 12.02
Utilizzare le funzioni di database 77
Capitolo 2
Figura 2.35 Cercare e mostrare aree specifiche del database.
Se il nome dinamico è stato assegnato correttamente e l’intervallo del database è esatto,
dovrebbe venire selezionato l’intero intervallo del database tra la cella A1 e la cella F7008
(Figura 2.36, ma nota che appare solo una parte dell’intervallo).
Figura 2.36 Viene selezionato l’intero database ma qui è mostrata solo una parte.
Excel 2003, Excel 2007 ed Excel 2010 offrono un metodo alternativo per assegnare nomi
dinamici personalizzati: puoi definire l’intervallo da calcolare come elenco (Excel 2003) o
come tabella (Excel 2007 o Excel 2010). Procedi come segue in Excel 2003:
1. Posiziona il punto di inserimento nell’elenco.
2. Seleziona l’opzione di menu Data/Elenco/Crea.
3. Se necessario, correggi il riferimento all’intervallo suggerito e specifica se l’elenco ha
4. Fai clic su OK per confermare.
un’intestazione.
In Excel 2007 ed Excel 2010, l’elenco viene chiamato tabella. Per creare l’intervallo tabella in
Excel 2007 o Excel 2010, procedi come segue:
1. Posiziona il punto di inserimento nell’elenco.
2. Sulla scheda Inserisci nel gruppo Tabelle, fai clic sul pulsante Tabella.
02 Ex Fun.indd 77
19/04/12 12.02
78
Capitolo2 UtilizzarelefunzioniePowerPivot
3. Se necessario, correggi il riferimento all’intervallo suggerito e specifica se l’elenco ha
4. Fai clic su OK per confermare.
intestazioni.
In entrambi i casi, si ottiene un intervallo elenco qualificato. Excel estende tutti i riferimenti
in questo intervallo elenco non appena l’intervallo viene esteso.
Le funzioni di database in pratica
Capitolo 2
Gli esempi pratici che seguono mostrano calcoli tipici che utilizzano le funzioni di database
di Excel.
File di esempio
Utilizza il foglio di lavoro Pratica del file di esempio Database_pratica.xls o Database_pratica.xlsx nella cartella Capitolo02. Per ulteriori informazioni sui file di esempio, vedi la
sezione “Utilizzare i file di esempio” nell’introduzione.
Un centro di formazione ha creato un database per registrare informazioni sui corsi tenuti
dall’istruttore. Questi dati necessitano di una valutazione. Al database è stato assegnato il
nome dinamico Info_corso. Sono disponibili i campi di database seguenti (Figura 2.37):
02 Ex Fun.indd 78
●
Data
●
Corso
●
Istruttore
●
Maschio/Femmina
●
Azienda
●
Valutazione
●
Fatturato
19/04/12 12.02
Utilizzare le funzioni di database 79
Capitolo 2
Figura 2.37 I risultati nel database.
Il centro di formazione vuole sapere quanti corsi su Excel si sono tenuti da quando si è iniziato a raccogliere le informazioni. La funzione DB.CONTA.VALORI() restituisce il risultato.
Quanti corsi su Excel si sono tenuti?
La funzione DB.CONTA.VALORI() conta il numero di celle in una colonna, elenco o database
che non sono vuote e che soddisfano condizioni specifiche (Figura 2.38).
Figura 2.38 Calcolare il numero di corsi su Excel nel database.
Definisci l’intervallo dei criteri per il filtro. L’intervallo dei criteri è composto dall’intestazione
di colonna e dai criteri di filtro nella cella sottostante. Per fare ciò, copia l’intervallo B1:B2 nelle
celle L1:L2. La formula =DB.CONTA.VALORI(Info_corso;B1;L1:L2) restituisce 23 corsi su Excel.
Puoi trovare il numero degli altri corsi modificando il filtro in L2 in Microsoft Word, per
esempio.
02 Ex Fun.indd 79
19/04/12 12.02
80
Capitolo 2 Utilizzare le funzioni e PowerPivot
Calcolare il fatturato
Ora, il centro di formazione vuole sapere quanto hanno fruttato i corsi su Excel. Per trovare
la risposta puoi utilizzare la funzione DB.SOMMA(). Questa funzione somma i numeri in una
colonna di un database che soddisfano condizioni specifiche (Figura 2.39).
Capitolo 2
Figura 2.39 Calcolare il fatturato dei corsi su Excel.
La formula =DB.SOMMA(Info_corso;G1;L1:L2) restituisce una somma pari a €
nerata dai corsi su Excel.
22.522,00
ge-
Trovare la valutazione media dei corsi
Il centro di formazione è altresì interessato a conoscere la valutazione media attribuita a
istruttori e corsi. Per trovare la risposta, puoi utilizzare la funzione DB.MEDIA(), che fornisce
la media delle valutazioni che soddisfano le condizioni specificate (Figura 2.40).
Figura 2.40 Calcolare i valori medi dai valori del database nella colonna Valutazione.
In questo caso, non hai bisogno dell’intervallo dei criteri L1:L2, perché non è richiesto alcun
filtro. La formula =DB.MEDIA(Info_corso;F1;F:F) restituisce una valutazione media pari a 1,88.
Utilizzare le funzioni cubo
Le funzioni cubo sono state introdotte la prima volta in Excel 2007. I cubi dati sono insiemi
di dati complessi e multidimensionali derivati da informazioni non elaborate, archiviate in
un database standard. Le funzioni cubo sono un po’ complesse a livello tecnico (richiedono
almeno Microsoft SQL Server 2005 e Microsoft SQL Server Analysis Services) e, inoltre, contengono termini nuovi a cui si potrebbe essere poco avvezzi.
02 Ex Fun.indd 80
19/04/12 12.02
Utilizzare le funzioni cubo 81
I cubi OLAP (online analytical processing, elaborazione analitica online), noti più semplicemente come cubi, offrono il vantaggio di avere i dati già aggregati sul server e collegati
al cubo per rendere le informazioni più prontamente disponibili. Ciò consente un’analisi di
insiemi di dati complessi.
L’elenco delle vendite potrebbe essere simile a quello nella Figura 2.41. Dal momento che
nell’elenco vengono ripetute le informazioni per catene, negozi, anni e prodotti, occorre
riepilogare tutti quei dati.
Capitolo 2
Questo libro prova a spiegare operazioni complesse basandosi su un esempio semplice.
L’esempio utilizza due catene di negozi, chiamate Nord e Sud, che hanno venduto dolciumi
(cioccolato e biscotti) dal 2008 al 2010. Ciascuna delle due catene ha due punti vendita. Le
vendite sono registrate in un database e possono essere riepilogate e valutate utilizzando
metodi comuni. Puoi utilizzare visualizzazioni preparate in Microsoft Access o in un altro
database, connessioni ai dati di Excel oppure Microsoft Query per creare la query.
Figura 2.41 Le vendite dei negozi delle società.
Puoi preparare il riepilogo con strumenti di programmazione, come quelli contenuti in Microsoft Visual Studio 2005, Visual Studio 2008 e Visual Studio 2010. Per fare ciò, devi esaminare
la struttura dei dati ed elaborare preventivamente i dati (Figura 2.42).
02 Ex Fun.indd 81
19/04/12 12.02
82
Capitolo 2 Utilizzare le funzioni e PowerPivot
Capitolo 2
Figura 2.42 Preparare il cubo per Analysis Services.
Anni, prodotti e negozi diventano dimensioni (assi del cubo), mentre le vendite diventano
misure. Puoi estendere le misure secondo necessità (per esempio, fatturato lordo, valori medi
e altri valori statistici). I KPI (Key Performance Indicators) ti consentono di utilizzare misure
aggiuntive con proprietà diverse.
Se il cubo è presente sul server (a differenza di una tabella in un database, il cubo non viene
modificato finché non è rigenerato) e hai accesso al server, hai due opzioni per creare la
tabella pivot:
●
●
Selezionare Inserisci/Tabella pivot/Origine dati esterna e poi selezionare una connessione a una cartella di lavoro.
Selezionare Dati/Da altre origini con queste opzioni:
m
Da Analysis Services
m
Da Microsoft Query
Se utilizzi la seconda opzione, ti verrà chiesto di inserire la tabella pivot in un secondo momento. Microsoft Query ti permette anche di collegarti a cubi non in linea.
L’elenco dei campi della tabella pivot nella Figura 2.43 rispecchia le dimensioni e la configurazione del cubo.
02 Ex Fun.indd 82
19/04/12 12.02
Utilizzare le funzioni cubo 83
Capitolo 2
Figura 2.43 Dal cubo alla tabella pivot.
Spetta a te visualizzare i dati che ti interessano. Puoi selezionare filtri rapporto, intestazioni
di colonna e di riga e valori (Figura 2.44).
Figura 2.44 Creare il riepilogo.
02 Ex Fun.indd 83
19/04/12 12.02
84
Capitolo2 UtilizzarelefunzioniePowerPivot
Le funzioni cubo possono essere utilizzate in due modi diversi:
●
Gli strumenti OLAP includono l’opzione Converti in formule (Figura 2.45). Questo comando converte l’intera tabella pivot (ad eccezione del filtro di rapporto). Dopo puoi
formattare la tabella nel modo preferito, ma la tabella non può essere riorganizzata
ulteriormente.
Capitolo 2
Figura 2.45 Applicare le funzioni cubo automaticamente.
●
Puoi immettere le formule nel foglio di lavoro manualmente per eseguire la ricerca
delle informazioni nel cubo.
Le funzioni cubo non sono legate alla tabella pivot, poiché una connessione cubo nella cartella
di lavoro è sufficiente. La formula seguente restituisce il fatturato lordo dei biscotti venduti
nel 2008 nel punto vendita NorthEast:
=VALORE.CUBO("non in linea";"[Misure].[Fatturato lordo]";"[Punti vendita].[Punto
vendita].[Tutti].[NorthEast]";" [Anni].[Anno].[Tutti].[2008]";"[Prodotti].[Prodotto].
[Tutti].[Biscotti]")
La formula seguente restituisce l’anno con le vendite maggiori in quel negozio:
=MEMBRO.ORDINATO.CUBO("non in linea";INSIEME.CUBO("non in linea";"([Punti vendita].
[Punto vendita].[Tutti].[NorthEast]; [Anni].Bambini)";"tutte le vendite";2;"[Misure].
[Vendite]");1)
Utilizzare le funzioni di matematica finanziaria
Se desideri utilizzare le funzioni matematico-finanziarie di Excel, dovrai avere una certa familiarità con esse. Excel può fornire i calcoli ma non le interpretazioni. Le funzioni di Excel e la
corrispondente Guida non in linea non possono essere un surrogato di un manuale ad hoc.
Si crede erroneamente che le funzioni matematico-finanziarie di Excel siano progettate
su misura per la matematica finanziaria e aziendale degli Stati Uniti e che abbiano quindi
un’attinenza solo parziale con gli altri paesi. È forse questo il motivo per cui la maggior parte
dei libri di matematica finanziaria si tiene alla larga da Excel. Se Excel viene incluso, viene
utilizzato di solito solo come semplice programma di foglio di calcolo, e le sue funzioni sono
utilizzate raramente.
02 Ex Fun.indd 84
19/04/12 12.02
Utilizzare le funzioni di matematica finanziaria 85
La matematica finanziaria copre diverse aree, tra cui la statistica e il calcolo integrale e
differenziale, ma esistono aree, come la valutazione dei warrant azionari e di altri prodotti
finanziari derivati, che non possono essere gestite da Excel.
In ogni caso, l’interesse è il prezzo del capitale preso a prestito (tasso passivo) o dato in prestito
(interessi attivi). Il tasso di interesse fa riferimento a un determinato periodo (spesso un anno).
Inizialmente esistono due diversi calcoli dell’interesse: annualità e rendimento anticipato degli
interessi. Altri termini relativi all’interesse sono interesse nominale, interesse reale e interesse
di mercato. Esistono anche termini che hanno un rapporto diretto con l’interesse: sconto di
cassa, dividendo straordinario, sconto, margine di utile lordo e deprezzamento.
Capitolo 2
Un calcolo dell’interesse semplice è caratterizzato dal fatto che l’interesse non viene sommato
al capitale alla scadenza. Questo tipo di calcolo viene utilizzato soprattutto per i periodi
brevi. Un calcolo dell’interesse semplice è fondamentalmente un calcolo di percentuali. La
matematica finanziaria offre molti termini diversi in relazione all’interesse.
L’annualità si riferisce a un normale conto di deposito fruttifero a risparmio o a un comune
mutuo ipotecario. L’interesse viene calcolato e pagato alla fine del periodo di interesse per il
capitale iniziale. Il rendimento anticipato degli interessi viene di solito utilizzato per pagare
una cambiale, per finanziamenti statali e per costruire contratti di prestito.
L’interesse viene pagato per il capitale maturato alla fine del periodo e calcolato sulla base
del tasso di interesse.
Se un risparmiatore versa denaro sul proprio conto di deposito durante l’anno, calcolare
l’interesse alla fine dell’anno non è difficile. Se il denaro si trova sul conto per quattro mesi
e il tasso di interesse è del 3%, viene pagato solo un terzo dell’interesse annuo. Ma questo
semplice calcolo della percentuale dovrebbe prendere in considerazione i giorni esatti quando
si determina la proporzione dell’anno e, per fare ciò, si utilizza la funzione GIORNO360(). Se
viene presentata una cambiale con la scadenza di un mese e la banca stabilisce un interesse
annuo del 6%, la banca trattiene (senza spese) lo 0,5% (un dodicesimo dell’interesse annuo)
dell’ammontare della cambiale e paga la differenza. Per contare i giorni, potresti utilizzare
anche qui la funzione GIORNO360(). Comunque, per questa operazione Excel offre la funzione PREZZO.SCONT().
La tabella nella Figura 2.46 descrive i calcoli passo per passo. Il numero dei giorni del periodo
viene calcolato in F2 e il risultato viene applicato al tasso di interesse annuo per determinare il tasso relativo al periodo in questione. Applicando questo tasso al capitale si ottiene
un valore pari a €9.950. Excel fornisce la funzione PREZZO.SCONT() per eseguire il calcolo.
Questo esempio chiarisce il fatto che le funzioni semplificano i calcoli ma che il loro utilizzo
richiede una certa conoscenza delle relative funzionalità.
Figura 2.46 Calcolo della cambiale come calcolo della percentuale.
02 Ex Fun.indd 85
19/04/12 12.02
86
Capitolo2 UtilizzarelefunzioniePowerPivot
Questo esempio rivela anche qualcos’altro: a che tasso di interesse (annuo) devi investire
€9.950,00 affinché ti vengano pagati €10.000,00 dopo un mese, includendo l’interesse? La
risposta è 50/9950*12=6,03%. Questo è anche un semplice calcolo di percentuale. Per questo calcolo, Excel offre la funzione REND.TITOLI.SCONT(). Esiste un’altra funzione che puoi
utilizzare: TASSO.INT(). Per ottenere il risultato devi passare a questa funzione i dati a sinistra
nella Figura 2.46.
File di esempio
Capitolo 2
Utilizza il foglio di lavoro Matematica finanziaria del file di esempio Matematica finanziaria.xls o Matematica finanziaria.xlsx nella cartella Capitolo02. Per ulteriori informazioni
sui file di esempio, vedi la sezione “Utilizzare i file di esempio” nell’introduzione.
In questo esempio (per l’annualità), l’interesse da pagare viene sommato al capitale iniziale
e diventa così parte del capitale. Ciò determina un interesse composto. Questo utilizzo delle
formule richiede che il tasso di interesse resti uguale nell’intero periodo. Ciò non sempre è
realistico, ma è comune in alcuni modelli di investimento. Se investi €10.000 per 10 anni, la
banca potrebbe offrire un tasso di interesse del 4,8%. La Figura 2.47 mostra una panoramica
dettagliata di questo conto.
Figura 2.47 Progressione di un conto con un saldo iniziale di €10.000.
Per calcolare il risultato di €15.981,32, non occorre alcuna funzione; devi soltanto aggiungere
l’interesse al capitale alla fine dell’anno.
Excel contiene un gruppo di funzioni che include la funzione VAL.FUT(). Il nome di questa
funzione è un’abbreviazione di valore futuro o anche valore accumulato. Questa funzione,
nella formula
=VAL.FUT(4,8%;10;;-10000)
02 Ex Fun.indd 86
19/04/12 12.02
Utilizzare le funzioni di matematica finanziaria 87
Il calcolo dell’interesse composto serve come base per gli esempi riportati nel resto di questa
sezione.
Esempio di calcolo di un’annualità
Un’annualità è il pagamento periodico di uno stesso importo. Tutte le funzioni disponibili in
Excel presumono che la data di pagamento dell’annualità coincida con la data dell’interesse.
Le uniche differenze riguardano le annualità anticipate e quelle in arretrato, che vengono
pagate alla fine del periodo.
Capitolo 2
in cui €10.000 vengono investiti per 10 anni a un tasso di interesse del 4,8%, restituisce
€15.981,33. Perché questa differenza? La deviazione si verifica perché ciascuna somma immessa come versamento, prelievo o credito sull’interesse deve essere arrotondata a due cifre
decimali (non esistono unità monetarie più piccole). Questo dato non può essere incorporato
dalla funzione in una formula matematica, in quanto una formula matematica esegue i calcoli con un elevato livello di precisione. Perciò occorre utilizzare la funzione ARROTONDA()
per arrotondare la somma in questa fase. Limitare le celle a due sole cifre decimali non è
sufficiente e può determinare errori.
Questo esempio può essere esteso con il depositante che aggiunge al conto altri €2.000 ogni
anno. Il calcolo del conto appare nella Figura 2.48.
Figura 2.48 Investimento più risparmi.
Anche qui la funzione VAL.FUT() è utile:
=VAL.FUT(4,8%;10;-2000;-10000)-2000
= 38903,52
€2.000 vengono investiti ogni anno per 10 anni a un tasso d’interesse del 4,8% e sommati al
saldo di apertura iniziale di €10.000. Poiché non c’è un versamento effettuato l’ultimo giorno,
occorre sottrarre gli ultimi €2.000 dai pagamenti calcolati con VAL.FUT(). In questo calcolo
c’è anche un piccolo errore di arrotondamento.
02 Ex Fun.indd 87
19/04/12 12.02
88
Capitolo 2 Utilizzare le funzioni e PowerPivot
Calcolare i rimborsi
La matematica finanziaria utilizza tre forme di base per il rimborso di un prestito: il rimborso
attraverso un unico pagamento finale (spesso in collegamento con un’assicurazione sulla vita),
il rimborso tramite rate di importo costante (comune per l’acquisto di merci) e il rimborso
tramite pagamenti di annualità per le quali l’ammontare del rimborso più l’interesse resta
uguale (il tipo di credito utilizzato comunemente per finanziare proprietà immobiliari).
L’ultimo tipo è fondamentalmente un calcolo di annualità, ma i ruoli del creditore e del debitore sono invertiti. La funzione di Excel per calcolare i rimborsi copre solo il primo e il terzo tipo.
Capitolo 2
La formula utilizzata in precedenza può essere impiegata anche per calcolare i rimborsi
delle annualità. Nella maggior parte dei casi, il tasso di interesse annuo deve essere diviso
per 12 per calcolare l’interesse mensile. L’interesse mensile viene poi utilizzato nella formula.
Per esempio, supponi che una banca offra il finanziamento seguente per il patrimonio immobiliare: un prestito di €100.000 all’interesse nominale annuo del 5,6% (un interesse fisso per
cinque anni) e un rimborso dell’1% annuo. La banca fornisce anche le seguenti informazioni:
●
Pagamento mensile: €550,00
●
Tasso percentuale annuo iniziale: 5,75%
Il pagamento mensile deriva dal calcolo di un dodicesimo del tasso d’interesse del 5,6% più
un rimborso dell’1%: 6,6%/12 dell’ammontare del prestito. Il totale è €550,00. Con la formula
seguente puoi calcolare il tasso effettivo:
=EFFETTIVO(5,6%;12)
La Figura 2.49 mostra il piano di rimborso per il primo anno, che puoi creare senza alcuna
funzione finanziaria speciale.
02 Ex Fun.indd 88
19/04/12 12.02
Utilizzare le funzioni di matematica finanziaria 89
Capitolo 2
Figura 2.49 Un piano di rimborso per il finanziamento di un’annualità.
Questo piano di rimborso mostra che, alla fine dell’anno, viene restituito più dell’1% concordato. Ciò è determinato dai pagamenti mensili nel corso dell’anno (la dodicesima parte).
Il tasso effettivo deve essere superiore all’interesse nominale poiché la somma concordata
doveva essere rimborsata più velocemente. Le funzioni di Excel ti consentono di calcolare
singoli elementi, come l’interesse del quinto mese, il saldo del capitale alla fine del 16° mese
oppure il tasso percentuale annuo che, di solito, non può essere calcolato con una semplice
equazione.
L’ordine della quotazione corrente dei prezzi non si basa su durate divise ma su parti dell’anno.
Calcolare i tassi di cambio
I calcoli del tasso di cambio e del rendimento complessivo di un investimento sono un’area
delicata della matematica finanziaria. Molte delle funzioni integrate si concentrano su queste
aree. Il tasso di cambio viene definito sempre come valore numerario relativo di uscite future
dopo avere sottratto l’interesse accantonato, se necessario. Il rendimento complessivo è la
cifra (come tasso di interesse) del valore effettivo.
Esempio di valutazione di investimento
Gli investimenti vengono spesso calcolati con analisi di investimento statiche o dinamiche.
Tali analisi potrebbero includere confronti costi/ricavi nonché il periodo di estinzione del
debito, e si basano su calcoli di costi e pagamenti. I metodi dinamici considerano l’interesse
composto e valutano versamenti e prelievi. Questi metodi non sono comunemente utilizzati
nel commercio a causa dei modelli e della raccolta statica di dati. Excel fornisce svariate
funzioni per calcoli di investimento dinamici (metodo del valore capitale e metodo del tasso
di interesse interno). Questi metodi sono l’ideale per le analisi di investimento.
02 Ex Fun.indd 89
19/04/12 12.02
90
Capitolo 2 Utilizzare le funzioni e PowerPivot
Per calcolare gli utili sulle entrate, prendi i valori mostrati nella Figura 2.50 e immettili in un
foglio di lavoro.
Capitolo 2
Figura 2.50 Un semplice esempio di rendimento di capitale interno.
Qui la funzione TIR.COST() fornisce il risultato più veloce, ma potresti utilizzare anche la
funzione TASSO() o FORMULA.RICERCA.OBIETTIVO.
Esempio di calcolo di ammortamento
Ricorda che i pagamenti sono definiti non soltanto dalla somma ma anche dalla data di
pagamento. I pagamenti potrebbero essere illustrati su una linea temporale. Fa una certa
differenza il fatto che il debitore paghi il suo debito oggi o tra un anno. Più lungo è il periodo
di remissione del debito, maggiore sarà l’importo dovuto. L’interesse è sommato al debito
e il debito viene capitalizzato.
Questo principio potrebbe essere espresso nel senso inverso: il denaro dato in ritardo vale
meno. Ciò non ha niente a che vedere con l’inflazione. Da un punto di vista finanziario, è
irrilevante che €11.000 vengano restituiti in un anno o che €10.000 vengano restituiti oggi
(sulla base di un tasso d’interesse annuo del 10%).
Quindi, i calcoli di ammortamento non fanno propriamente parte della matematica finanziaria.
Di solito, i calcoli di ammortamento vengono trattati nei manuali. Excel fornisce le funzioni
corrispondenti in un gruppo a parte.
Utilizzare le funzioni matematiche e trigonometriche
Le funzioni matematiche e trigonometriche includono la funzione SOMMA(). Questa è, probabilmente, la funzione di Excel maggiormente utilizzata. Per potenziare la funzione SOMMA(),
Excel offre la funzione SOMMA.SE(). Supponi di avere un elenco con le vendite ai clienti. Per
filtrare le vendite relative a un determinato cliente e calcolare la somma, utilizza la funzione
SOMMA.SE(), come mostrato nell’esempio seguente.
I clienti sono elencati nelle celle A2:A20, mentre le vendite sono elencate in B2:B20 (Figura
2.51). La cella D6 contiene il cliente di cui vuoi interrogare le vendite. La formula in E6 relativa
alla somma delle vendite è:
=SOMMA.SE(A2:A20;D6;B2:B20)
02 Ex Fun.indd 90
19/04/12 12.02
Utilizzarelefunzionimatematicheetrigonometriche
91
Capitolo 2
Figura 2.51 Filtrare i valori con la funzione SOMMA.SE().
Se immetti il nome del cliente in D6, puoi calcolare la somma delle vendite.
Generare dati di test casuali
Tra le funzioni matematiche e trigonometriche c’è anche la funzione CASUALE.TRA(), che
facilita l’utilizzo di Excel. Con questa funzione, infatti, puoi riempire velocemente tabelle
con dati di test e dimostrativi.
Nota
Per Excel 2003 e versioni precedenti, potrebbe essere necessario attivare il componente
aggiuntivo di questa funzione (per ulteriori informazioni, consulta la sezione “Funzioni
di analisi” precedentemente in questo capitolo). In Excel 2007 ed Excel 2010, la funzione
è disponibile per impostazione predefinita.
Immetti la funzione CASUALE.TRA() in una cella qualunque e poi inserisci il valore minimo
e massimo dell’intervallo numerico. Se i valori sono compresi tra 500 e 1000, utilizza la formula =CASUALE.TRA(500;1000). Ora puoi copiare la formula nelle celle degli altri valori per
visualizzare un valore diverso compreso tra 500 e 1000 in quelle celle (Figura 2.52).
02 Ex Fun.indd 91
19/04/12 12.02
92
Capitolo2 UtilizzarelefunzioniePowerPivot
Figura 2.52 Riempire una tabella con valori casuali.
Capitolo 2
Se apri il file o premi il tasto F9, tutti i valori vengono ricalcolati. Per convertire i valori casuali
contenuti nelle celle in valori fissi, copia l’intervallo che contiene i valori casuali, seleziona
il comando Incolla speciale nel menu di scelta rapida, attiva Valori nella finestra di dialogo
Incolla speciale (Figura 2.53) e fai clic su OK.
Figura 2.53 La finestra di dialogo Incolla speciale offre molte opzioni utili.
File di esempio
Utilizza i fogli di lavoro Matematica e Casuale del file di esempio Matematica.xls o
Matematica.xlsx nella cartella Capitolo02. Per ulteriori informazioni sui file di esempio,
vedi la sezione “Utilizzare i file di esempio” nell’introduzione.
Nella sezione matematica e trigonometria ci sono soltanto due esempi delle funzioni.
02 Ex Fun.indd 92
19/04/12 12.02
PowerPivot
93
Il meglio viene alla fine. Microsoft offre un potente strumento di analisi: il componente
aggiuntivo gratuito PowerPivot per Excel 2010. Questo componente aggiuntivo si basa sulle
tecnologie più recenti consentendo l’elaborazione di grossi gruppi di dati (fino a svariati
milioni di righe), l’integrazione ottimizzata dei dati e la possibilità di pubblicare analisi su
Internet con Microsoft SharePoint 2010. L’elenco seguente descrive i vantaggi di PowerPivot:
●
●
●
●
●
●
Capitolo 2
PowerPivot
PowerPivot combina funzioni native di Excel 2010 con un modulo in memoria per permettere agli utenti di valutare grossi gruppi di dati in modo interattivo e di utilizzare i
risultati per i calcoli. Ciò è possibile grazie al nuovo sistema di gestione memoria, che
utilizza la compressione orientata alle colonne. Tutto viene svolto nella memoria del
computer per accelerare le operazioni sui dati.
PowerPivot ottimizza l’integrazione di dati provenenti da origini diverse, inclusi database aziendali, fogli di calcolo, report, file CSV e alimentazioni di dati.
PowerPivot ignora i limiti di riga per i fogli di lavoro di Excel 2007 ed Excel 2010.
Puoi accedere a tabelle pivot, medie di dati e altre comuni caratteristiche di analisi di
Excel per creare report.
Puoi pubblicare le analisi in SharePoint 2010 per condividerle con altri utenti della tua
organizzazione. Gli altri utenti che utilizzano report di Excel Services possono servirsi
delle stesse funzioni, come le medie dati e le query veloci.
Le applicazioni e i report in SharePoint 2010 vengono aggiornati automaticamente
tramite un rilevamento simultaneo della versione.
All’indirizzo http://www.powerpivot.com puoi trovare ulteriori informazioni ed esercitazioni
(in inglese), oltre al componente aggiuntivo PowerPivot da scaricare.
Utilizzare PowerPivot in Excel 2010
In Excel 2010, con il componente aggiuntivo PowerPivot installato, fai clic sul pulsante PowerPivot sulla scheda PowerPivot per passare da Excel alla finestra PowerPivot. All’inizio la
finestra è vuota e la barra multifunzione contiene molti pulsanti non attivi. Occorre incorporare
i dati da analizzare. Fai clic sul pulsante Da database (Figura 2.54) e seleziona Da SQL Server.
02 Ex Fun.indd 93
19/04/12 12.02
94
Capitolo2 UtilizzarelefunzioniePowerPivot
Figura 2.54 Il gruppo Recupera dati esterni sulla scheda Home della finestra PowerPivot.
Capitolo 2
Nota
Il database di esempio Microsoft utilizzato è ContosoRetailDW, che al momento della
stesura del libro può essere scaricato all’indirizzo
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=18279
Il file di trasferimento ContosoBIdemoBAK.exe contiene due file da estrarre: ContosoRetailDW.bak e un elenco di Excel. ContosoRetailDW.bak è un backup di Microsoft SQL
Server che dovrai ripristinare in SQL Server.
Immetti il nome del server in Importazione guidata tabella, specifica le credenziali e seleziona
il nome del database (Figura 2.55).
Figura 2.55 Il primo passo per effettuare la connessione con il database in Importazione guidata
tabella.
02 Ex Fun.indd 94
19/04/12 12.02
PowerPivot
95
Capitolo 2
Nel passo successivo, mantieni l’opzione preselezionata per fare una selezione dall’elenco
delle tabelle e delle visualizzazioni (Figura 2.56).
Figura 2.56 Il secondo passo in Importazione guidata tabella.
Seleziona la tabella FactSales. Questa tabella contiene 3,4 milioni di righe di dati di transazioni.
Fai clic sul pulsante Seleziona tabelle correlate (Figura 2.57). Vengono incluse sei tabelle di
dati principali correlate. Fai clic sul pulsante Fine.
Figura 2.57 Selezionare tabelle e visualizzazioni dal database SQL Server.
02 Ex Fun.indd 95
19/04/12 12.02
96
Capitolo 2 Utilizzare le funzioni e PowerPivot
Se selezioni tabelle di grosse dimensioni, il caricamento dei dati può richiedere alcuni minuti.
La procedura guidata mostra l’avanzamento del caricamento (Figura 2.58).
Capitolo 2
Figura 2.58 I dati vengono caricati.
Quando i dati sono stati caricati, le tabelle vengono visualizzate nella finestra PowerPivot. La
finestra (Figura 2.59) somiglia a un foglio di lavoro di Excel ma ha funzionalità diverse. Salva
la cartella di lavoro, che ha la dimensione di 87 MB.
Per poter pubblicare i dati in SharePoint, chiedi all’amministratore del sistema di aumentare
le dimensioni massime dei dati caricati (che, per impostazione predefinita, è di 50 MB).
02 Ex Fun.indd 96
19/04/12 12.02
PowerPivot
97
Capitolo 2
Figura 2.59 La finestra PowerPivot contiene le tabelle caricate.
Se il database di origine ha un diagramma delle relazioni, cioè una definizione di tutte le
relazioni del database, il diagramma verrà utilizzato automaticamente da PowerPivot. Fai
clic destro su una colonna della tabella di ricerca per visualizzare la tabella associata. Puoi
controllare e aggiungere le relazioni tra tabelle sulla scheda Struttura (Figura 2.60).
Figura 2.60 La scheda Struttura in PowerPivot.
Se fai clic su Gestisci relazioni sulla scheda Struttura, ottieni una panoramica delle relazioni
definite (Figura 2.61). Puoi modificare le relazioni e creare eventualmente nuove relazioni
necessarie per incorporare tabelle da altre origini.
02 Ex Fun.indd 97
19/04/12 12.02
98
Capitolo 2 Utilizzare le funzioni e PowerPivot
Capitolo 2
Figura 2.61 Una panoramica di tutte le relazioni tra le tabelle.
Se visualizzi la tabella DimStore, potrai notare che mancano le informazioni su aree, paesi
e località dei negozi. Queste informazioni sono collegate tramite il campo GeographyKey
nella tabella DimGeography. Per aggiungere la tabella DimGeography dalla stessa origine,
procedi come segue:
1. Fai clic sul pulsante Da database e seleziona Da SQL Server (Figura 2.54).
2. Immetti il nome del server e le credenziali in Importazione guidata tabella. Seleziona
3. Nel passo successivo, mantieni l’opzione per fare una selezione dall’elenco delle tabelle
4. Nell’ultimo passo della procedura guidata, seleziona la casella di controllo relativa alla
5. Seleziona la tabella DimStore e fai clic sul pulsante Crea relazione sulla scheda Struttura.
il database ContosoRetailDW.
e delle visualizzazioni (Figura 2.56).
tabella DimGeography e fai clic sul pulsante Fine.
02 Ex Fun.indd 98
6. Nella finestra di dialogo Modifica relazione, seleziona la colonna GeographyKey per
la tabella DimStore. Seleziona DimGeography come tabella di ricerca collegata e GeographyKey come colonna di ricerca collegata. Fai clic su OK per uscire.
19/04/12 12.02
PowerPivot
99
L’elenco necessario per l’analisi dei dati è completo. Non occorre aggiungere altre tabelle
per creare una valutazione pivot. Ora procedi come segue:
1. Sulla scheda Home nella finestra PowerPivot, fai clic sulla freccia Tabella pivot (Figura
2.62). Come puoi vedere, sono disponibili modelli per tabelle pivot e grafici pivot.
Capitolo 2
Figura 2.62 Modelli per valutazioni pivot.
2. Nel menu, seleziona Grafico e tabella pivot (in verticale). Passerai alla finestra di Excel
3. Seleziona l’area del grafico e trascina il campo ChannelName dalla tabella DimChannel
4. Fai clic destro sul nuovo elemento media dati e, nel menu di scelta rapida, seleziona il
5. Trascina i campi ContinentName e RegionCountryName dalla tabella DimGeography
6. Trascina il campo CalendarYear dalla tabella DimDate nell’area Etichette di riga. Il nome
7. Trascina i campi TotalCost e SalesAmount dalla tabella FactSales nell’area Valori.
8. Seleziona il comando Modifica misura nei menu di scelta rapida di entrambi gli elementi.
con entrambi gli oggetti pivot vuoti sul foglio di lavoro.
nell’area Sezionamenti verticali.
comando Impostazioni campo. Immetti Sales Channel nella casella Nome personalizzato. Fai clic su OK per uscire.
nell’area Sezionamenti orizzontali. Nelle impostazioni campi degli elementi, modifica
i nomi personalizzati in Continent e Country/Region.
personalizzato nelle impostazioni campi dovrebbe essere Year.
Nelle impostazioni misura degli elementi, modifica i nomi personalizzati in Costs e
Revenue.
La Figura 2.63 mostra le impostazioni create finora.
02 Ex Fun.indd 99
19/04/12 12.02
100
Capitolo 2 Utilizzare le funzioni e PowerPivot
Capitolo 2
Figura 2.63 Il progetto del grafico pivot nel report PowerPivot.
Se necessario, formatta il grafico (per esempio, imposta il formato numerico per gli assi). Il
secondo gruppo di passi riguarda la tabella pivot. Per completare la tabella pivot, procedi
come segue:
1. Seleziona l’area della tabella pivot sotto il grafico. Noterai che i sezionamenti del grafico
2. Trascina il campo CalendarYear dalla tabella DimDate nell’area Etichette di colonna. Il
3. Trascina il campo Manufacturer dalla tabella DimProduct nell’area Etichette di riga. Il
4. Trascina i campi TotalCost e SalesAmount dalla tabella FactSales nell’area Valori. Anche
sono già impostati. Mantieni queste impostazioni.
nome personalizzato nelle impostazioni campo dovrebbe essere Year.
nome personalizzato nelle impostazioni campo dovrebbe essere Manufacturer.
qui, modifica i nomi personalizzati in Costs e Revenue.
Modifica i formati nella tabella pivot (Figura 2.64), per esempio cambia il formato numerico
o la larghezza delle colonne. Il risultato dovrebbe essere simile a quanto appare nella Figura
2.65. Assegna un nome al foglio contenente il report PowerPivot e salva il lavoro.
Figura 2.64 Il progetto della tabella pivot nel report PowerPivot.
02 Ex Fun.indd 100
19/04/12 12.02
PowerPivot
101
Capitolo 2
È stato creato un nuovo foglio di lavoro con la tabella pivot associata per il grafico. Non
eliminare questo foglio di lavoro e ricorda che, ogni volta che modifichi la tabella pivot, il
cambiamento viene applicato anche al grafico.
Figura 2.65 Un report PowerPivot composto da un grafico e una tabella che utilizzano gli stessi
slicer.
Per i report PowerPivot, occorre solo sapere come creare e progettare tabelle pivot e grafici
pivot. In altre parole, le tue conoscenze su Excel sono sufficienti per utilizzare PowerPivot.
02 Ex Fun.indd 101
19/04/12 12.02
02 Ex Fun.indd 102
19/04/12 12.02