Business IntelligenceDai dati alle decisioni

Modulo 3Come Excel può rivelarsi utile nella Business Intelligence

Il computer non è una macchina intelligente che aiuta le persone stupide, anzi è una macchina stupida che funziona solo nelle mani delle persone intelligenti. (Umberto Eco)

Somme e PercentualiDa un elenco possiamo ricavare somme e percentuali:

SommePercentuali.xls

Proviamo ora a calcolare la produttività di ogni libreria considerando 48 settimane lavorative in un anno e 40 ore a settimana:

=INT((OGGI()-C2)/365,25)

In Excel ci vengono in aiuto vari comandi/formule per costruire delle tabelle riepilogative:

 

1. Dal menù di comando, SUBTOTALI2. La funzione SUBTOTALE3. La funzione CONTA.SE4. La funzione SOMMA.SE5. La funzione FREQUENZA

Raggruppare i dati di un elenco

Per la società sportiva dell’esempio vogliamo calcolare: 

1. I versamenti divisi per i maschi e per le femmine (con il comando subtotali)

2. Ripetere l’esercizio precedente usando la funzione SOMMA.SE.

Subtotali e funzione SUBTOTALE

Subtotali.xls

Per le librerie dell’esempio vogliamo calcolare: 

1. Numero delle librerie per regione (con la funzione CONTA.SE o CONTA.VALORI) e fatturato per regione (usando la funzione SOMMA.SE)

2. Numero delle librerie per regione e fatturato per regione (con il comando subtotali)

Subtotali e funzione SUBTOTALE

Subtotali.xls

Per la società sportiva dell’esempio vogliamo calcolare la distribuzione (frequenza) delle età degli iscritti. 

• La funzione FREQUENZA ha due argomenti, la matrice dati e la matrice classi.

• La funzione FREQUENZA quando necessario, andrà inserite come formula matrice.

funzione FREQUENZA

Subtotali.xls

La formula matrice va inserita in maniera particolare: 1. si seleziona la matrice in cui inserire i

risultati;2. si scrive la formula;3. La si inserisce con la sequenza

Ctrl+Shift+InvioSi riconosce perché ha le parentesi graffeN.B. Non può essere cancellata in parte

Per il foglio clienti calcolare la “dispersione” delle vendite on line

funzione FREQUENZA

Subtotali.xls

Classe Frequeza SpesaSpesa Media

MENO DI 30 14

1.142,00

81,57

30-40 9

880,00

97,78

40-50 7

661,00

94,43

50-70 10

625,00

62,50

OLTRE 70 10

969,00

96,90

10 20 30 40 50 60 70 $ -

$ 20

$ 40

$ 60

$ 80

$ 100

$ 120

$ 140

$ 160

$ 180

0.5 1 1.5 2 2.5 3 3.5 4 4.5 5 5.5 -

20.00

40.00

60.00

80.00

100.00

120.00

Classe

Tabella di riepilogoUtilizzo del filtro per la

classificazione e del filtro avanzato per le etichette della tabella. Funzioni CONTA.SE e SOMMA.SE

Frequenza Assoluta, Relativa, Cumulata

ClasseFrequeza Assoluta

Frequenza Relativa

Frequenza Cumulata Assoluta

Frequenza Cumulata Relativa

MENO DI 30 14 28% 14 28%

30-40 9 18% 23 46%

40-50 7 14% 30 60%

50-70 10 20% 40 80%

OLTRE 70 10 20% 50 100%

Calcolare ora le tre tipologie di frequenze:

Subtotali.xls

Tabella Riepilogativa

TabellaRiepilogativa.xls

Per il foglio “DipendentiFiliali” creiamo la seguente tabella riepilogativa

Tabelle a doppia entrata

Una tabella a doppia entrata è una tabella che contiene valori congiunti di due campi messi in relazione tra loro.

…ad esempio possiamo ricavare una tabella a doppia entrata da un sondaggio sugli sport preferiti che tenga anche conto dell’età delle persone intervistate.

ChiQuadro.xls

In Excel le tabelle a doppia entrata sono le tabelle pivot:

Tabelle Pivot

TabellePivot.xls

Costruiamo ora le seguenti tabelle pivot:

Creare una tabella pivot;Modificare gli elementi e mutarne l’ordine;Formattazione e Formattazione Condizionale;

Nascondere e mostrare elementi;Funzione delle caselle.

Funzioni avanzate per tabelle Pivot

PivotAvanzato.xls

Raggruppare campi numerici…

Funzioni avanzate per tabelle Pivot

Copiare tabelle pivot mantenendo la struttura…

SELEZIONA COPIA INCOLLA

Funzioni avanzate per tabelle Pivot

SELEZIONA COPIA INCOLLA SPECIALE

Copiare solo i dati di una tabella pivot…

Funzioni avanzate per tabelle Pivot

PivotAvanzato.xls

Raggruppare i dati temporali in una tabella pivot…

PeriodoAnno

Trimestre Mese

1Mese

2Mese

3

Funzioni avanzate per tabelle Pivot

Raggruppare i dati temporali in una tabella pivot…

Funzioni avanzate per tabelle Pivot

PivotAvanzato.xls

Proviamo ora a raggruppare i dati temporali in una tabella pivot…

Struttura a livelli e funzioni avanzate

Campi organizzati su più livelli:

Merc

ato

Italia

Nord

Lombardia

Piemonte

Liguria

CentroLazio

Marche

SudPuglia

Campania

…

FranciaGermani

a

Struttura a livelli e funzioni avanzate

PivotAvanzato.xls

Con il campo pagina possiamo ottenere report riepilogativi per area:

…creando i campi “Regione” e “Area” per poter effettuare l’analisi DRILL-DOWN della tabella

Struttura a livelli e funzioni avanzate

PivotAvanzato.xls

A volte avremo la necessità di inserire un campo calcolato:

Proviamo ad esempio ad inserire il fatturato scorpaorato dell’iva…

I grafici di Excel

Year Number of crimes

Estimated Population Size

1987 13.508.700 243.400.000 1988 13.923.100 245.807.000 1989 14.251.400 248.239.000 1990 14.475.600 248.710.000 1991 14.872.900 252.177.000 1992 14.438.200 255.082.000 1993 14.144.800 257.908.000 1994 13.989.500 260.341.000 1995 13.862.700 262.755.000 1996 13.493.900 265.284.000 1997 13.175.100 267.637.000

Dai dati dell’FBI abbiamo che:

Crime in USA.xls

I grafici di Excel

1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 -

50,000,000

100,000,000

150,000,000

200,000,000

250,000,000

300,000,000

Number of crimesEstimated Population Size

YearNumber of

crimesEstimated

Population Size1987 13.508.700 243.400.000 1988 13.923.100 245.807.000 1989 14.251.400 248.239.000 1990 14.475.600 248.710.000 1991 14.872.900 252.177.000 1992 14.438.200 255.082.000 1993 14.144.800 257.908.000 1994 13.989.500 260.341.000 1995 13.862.700 262.755.000 1996 13.493.900 265.284.000 1997 13.175.100 267.637.000

Crime in USA.xls

I grafici di Excel

1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 230,000,000

235,000,000

240,000,000

245,000,000

250,000,000

255,000,000

260,000,000

265,000,000

270,000,000

12,000,000

12,500,000

13,000,000

13,500,000

14,000,000

14,500,000

15,000,000

15,500,000

Estimated Population Size

Number of crimes

Crime in USA.xls

I grafici di Excel

1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 19974.4%

4.6%

4.8%

5.0%

5.2%

5.4%

5.6%

5.8%

6.0%

Crime Rate

Crime Rate

Crime in USA.xls

YearNumber of

crimesEstimated

Population SizeCrime Rate

1987

13.508.700 243.400.000 5,6%

1988

13.923.100 245.807.000 5,7%

1989

14.251.400 248.239.000 5,7%

1990

14.475.600 248.710.000 5,8%

1991

14.872.900 252.177.000 5,9%

1992

14.438.200 255.082.000 5,7%

1993

14.144.800 257.908.000 5,5%

1994

13.989.500 260.341.000 5,4%

1995

13.862.700 262.755.000 5,3%

1996

13.493.900 265.284.000 5,1%

1997

13.175.100 267.637.000 4,9%

I grafici di Excel

Crime in USA.xls

Year Number of crimes

Estimated Population

SizeCrime Rate

1987

13.508.700 243.400.000 5,6%

1988

13.923.100 245.807.000 5,7%

1989

14.251.400 248.239.000 5,7%

1990

14.475.600 248.710.000 5,8%

1991

14.872.900 252.177.000 5,9%

1992

14.438.200 255.082.000 5,7%

1993

14.144.800 257.908.000 5,5%

1994

13.989.500 260.341.000 5,4%

1995

13.862.700 262.755.000 5,3%

1996

13.493.900 265.284.000 5,1%

1997

13.175.100 267.637.000 4,9%

Media

14.012.355 255.212.727 MDeviazione standard 496.539 8.212.198 SCorrelazione -0,42 r

13,000,000 14,000,000 15,000,000 230,000,000

235,000,000

240,000,000

245,000,000

250,000,000

255,000,000

260,000,000

265,000,000

270,000,000

Series1Linear (Series1)

Axis Title

Axis Title

I grafici di Excel

Esercitazione in Excel

Per il club del libro, cerchiamo di ottenere i seguenti grafici

Grafici.xls

0

1000

2000

3000

4000

5000

Città Bologna

Città Roma

Città Torino

Città Milano

Città BolognaCittà RomaCittà TorinoCittà Milano

_x0006_Adulto _x0007_Giovane _x0006_Totale0

1000

2000

3000

4000

5000

6000

Area NordArea Cen-tro

Verifica dell’esistenza di una relazione tra due campi

Ipotizziamo di voler sapere se due dimensioni della mia tabella sono in relazione…

0.5 1 1.5 2 2.5 3 3.5 4 4.5 5 5.50

10

20

30

40

50

60

Calcio

Sci

Tennis

PallacanestroFasce di età

N.r

o S

port

Pra

ticati

Conteggio di Sport Sport        

Età Calcio Sci Tennis Pallacanestro TotaleFino a 20 26 21 5 11 6321-30 38 19 11 17 8531-40 54 15 16 14 9941-50 34 16 13 12 75Oltre 50 42 19 27 11 99Totale 194 90 72 65 421

Verifica dell’esistenza di una relazione tra due campi

PivotAvanzato.xls

Usiamo il metodo del 2 (chi-quadro).

•Si costruisce la tabella pivot con le frequenze di due variabili qualitative;

Verifica dell’esistenza di una relazione tra due campi

PivotAvanzato.xls

Copia Valori

Valore Atteso

totriga x totcol=

TOT

Valore

2

(freqoss - freqatt

)2=freqatt

Chi quadro = somma di tutti

i chi

Gradi di libertà= (col-1) x

rig-1)

Alfa =0.05 (Grado di

certezza)

Valore Critico= Letto da

tabella

Verifica dell’esistenza di una relazione tra due campi

PivotAvanzato.xls

Possiamo allora affermare con una probabilità

del 95% che le variabili sono in relazione tra loro,Altrimenti non esiste (al

95%) alcuna relazione!

Se chi quadro > Valore Critico