Corso di
Statistica Computazionale
Prof.ssa Paola Vicard
Questa nota consiste per la maggior parte nella traduzione (con alcune integrazioni)
da “Descriptive statistics” di J. Shalliker e C. Ricketts, 2000, University of Plymouth
Consideriamo come esempio il data set contenuto nel foglio excel
“esercizio8_dati.xls”. Il data set1, riportato nel foglio di lavoro “dati esercizio”, si
riferisce a 121 fondi di investimento su cui sono state rilevate una serie di
caratteristiche tra cui: la categoria (ossia la dimensione delle aziende in cui investono
i fondi, dove la dimensione è intesa come valore dell’azienda sul mercato azionario),
l’orientamento, il rischio e i rendimenti a 5 anni. Nel foglio di lavoro “dati esercizio”
sono riportate le informazioni relative a queste quattro variabili.
Diminuisci
decimali
Creazione
guidata del
grafico
Barra dei
comandi
Somma
automatica
Tasto di incolla
funzione
Figura 1.
Gli obiettivi di questa nota sono: la costruzione di distribuzioni di frequenze (tabelle)
relative a due o tre variabili; il trattamento di dati quantitativi quando si vuole
costruire una distribuzione di frequenza doppia; il calcolo di statistiche (come media,
e varianza). Per tutte queste analisi lo strumento fondamentale è costituito dalla
tabella pivot.
Costruzione di tabelle per due variabili (tabelle a due vie)
Consideriamo il dataset e in particolare le variabili “categoria” e “rischio”. Può essere
interessante costruire una tabella congiunta per queste due variabili per studiare se
esse sono associate (ossia la categoria delle aziende in cui investono i fondi è in
qualche modo legata con il livello di rischio dei fondi).
1
Questo data set è stato preso dal sito web del libro “Statistica” 2a edizione, di Levine, Krehbiel e
Berenson, APOGEO Editore (http://www.apogeonline.com/libri/88-503-2357-3/allegato/dataset). Nel
suddetto libro il dataset ha la denominazione MUTUALFUNDS2004.xls
1
Corso di
Statistica Computazionale
Prof.ssa Paola Vicard
Lo strumento da utilizzare è la tabella pivot. Vediamo come fare:
-
cliccare su una cella qualunque del dataset
nella barra dei comandi cliccare su Dati e nel menu che si apre scegliere
Rapporto tabella pivot e grafico pivot... Si apre la finestra di dialogo
della Creazione guidata tabella pivot grafico pivot. Questa finestra
contiene il primo di quattro passi.
Passo 1
- confermare che i dati sono in un Elenco o database Microsoft Excel.
Indicare (selezionando) che il tipo di rapporto da creare è una Tabella
pivot. Cliccare su Avanti.
Passo 2
- confermare che i dati si trovano nell'
intervallo $A$1:$F$122 (nella
finestra l’intervallo selezionato all’inizio della procedura viene scritto con
i dollari). Cliccare su Avanti.
Passo 3
- Cliccare su Layout.
Ora dobbiamo dare il formato desiderato alla tabella. Intendiamo
rappresentare le variabili “categoria” e “rischio”; dobbiamo stabilire quale
delle due va nelle righe e quale delle due nelle colonne della tabella.
Decidiamo che “categoria” è nelle righe e “rischio” è nelle colonne.
- Trascinare il riquadro Categoria (che appare nella parte destra della
finestra) dentro l’area RIGA della tabella
- Trascinare il riquadro Rischio (che appare nella parte destra della
finestra) dentro l’area COLONNA della tabella
- Inoltre trascinare il riquadro Categoria (sempre quello che appare nella
parte destra della finestra) dentro l’area DATI della tabella.
In questo modo Excel produce, all’interno dell’area DATI, un riquadro
conteggio di Categoria. Questo riquadro mostra ciò che apparirà nella
tabella pivot, ossia il conteggio di quante volte le varie coppie di modalità
si presentano nella popolazione di 121 fondi.
Se, invece, di “categoria” avessimo trascinato Rischio dentro l’area
DATI, avremmo ottenuto lo stesso identico risultato2.
Cliccare su OK.
Passo 4
- indicare che si desidera collocare la tabella pivot nel foglio di lavoro
esistente a partire dalla cella H8. Cliccare su Fine.
La tabella pivot apparirà come in Figura 23.
Nella tabella pivot riportata nella Figura 2 le modalità dei due caratteri non sono
ordinate in modo crescente. Se lo si desidera è possibile ordinare le modalità
spostandole con il cursore.
2
In sostanza nell’area DATI può essere inclusa una qualunque delle due variabili nel caso in cui siano
entrambe qualitative (vedremo più avanti che l’inserimento nell’area DATI di una variabile
quantitativa produce risultati ben diversi).
3
Nella tabella: l’ultima riga costituisce la distribuzione marginale del carattere “rischio”; l’ultima
colonna (quella più a destra) è la distribuzione marginale del carattere “categoria”; le righe interne alla
tabella (tra la riga di titoli e la riga di totali) rappresentano le distribuzioni condizionate del carattere
“rischio” date, separatamente, le modalità di “categoria”; le colonne interne alla tabella (tra la colonna
di titoli e la colonna di totali) rappresentano le distribuzioni condizionate del carattere “categoria” date,
separatamente, le modalità di “rischio”.
2
Corso di
Statistica Computazionale
Prof.ssa Paola Vicard
Figura 2
Se, ad esempio, si vogliono ordinare le modalità della variabile “categoria” basta
cliccare su “grandi” e poi muovere il cursore finché (in corrispondenza del bordo
della cella contenente “grandi”) assume la forma di una freccia. A questo punto
cliccare e tenere premuto il tasto sinistro del mouse fino portare la freccia sotto al
bordo inferiore della cella in cui “grandi” deve essere collocato. Eseguire la stessa
procedura per spostare “medie”. Cliccare sulla cella contenente “medie” e quando il
cursore diventa una freccia, cliccare il tasto destro del mouse e tenere premuto
trascinando la cella “medie” fino a che la freccia arriva al bordo inferiore della cella
di destinazione.
Notate che ogni volta che si trascina una modalità, con essa viene spostata l’intera
riga a cui questa modalità appartiene.
Se si vogliono ordinare anche le modalità di “rischio” eseguire la stessa procedura.
Modificare la tabella ottenuta.
•
È possibile scrivere la distribuzione doppia come due distribuzioni univariate
collegate fra loro. Vediamo come fare:
cliccare sulla cella a sfondo grigio con scritto Rischio e trascinare fino al bordo
sinistro della cella a sfondo grigio con scritto Categoria.
In questo modo si rappresenta la distribuzione per categoria separatamente per
ogni livello di rischio.
In altre parole si rappresentano una sotto l’altra le tre distribuzioni condizionate
della variabile “categoria” dati: “rischio”= alto, “rischio”= basso e “rischio”=
medio.
Ecco come appare la nuova tabella:
3
Corso di
Statistica Computazionale
Prof.ssa Paola Vicard
Conteggio of Categoria
Rischio
Categoria
alto
grandi
medie
piccole
alto Totale
basso
grandi
medie
piccole
basso Totale
medio
grandi
medie
piccole
medio Totale
Grand Totale
Tabella 1
Totale
6
2
9
17
19
7
32
58
17
10
19
46
121
A partire dalla tabella nella Figura 2 è possibile, in modo analogo a quanto visto
sopra, costruire la tabella che mostra separatamente per ogni tipo di categoria la
distribuzione dei fondi per livello di rischio.
•
Quando, come nella tabella sopra, le righe sono date dalla combinazione di due
variabili (per ogni modalità di “rischio” sono elencate le tre modalità di
“categoria”), è possibile nascondere i dettagli relativi alla variabile più a destra
della due (in questo caso “categoria”)4.
Per fare ciò si possono utilizzare le icone nella barra degli strumenti della tabella
pivot.
(Se la barra degli strumenti della tabella pivot non è sullo schermo allora: cliccare
nella barra dei comandi su Visualizza, nel menu che si apre scegliere Barre
degli strumenti e poi Tabella Pivot.)
- Cliccare sulla cella a sfondo grigio Rischio
- Nella barra della Tabella pivot cliccare sull’icona Nascondi dettagli (la
quinta icona da destra al cui interno è raffigurato un segno meno).
Si ottiene la seguente tabella seguente:
Conteggio di Categoria
Rischio
alto
basso
medio
Grand Total
Categoria
Tabella 2
Total
17
58
46
121
Si noti che questa tabella altro non è che la distribuzione marginale del carattere
“Rischio”.
Se si desidera tornare alla tabella 1 basta cliccare nella barra Tabella pivot
sull’icona Mostra dettagli (la quarta icona da destra al cui interno è raffigurato un
segno +).
•
Torniamo a considerare la tabella in Figura 2. Possiamo invertire le righe e le
colonne. Vediamo come fare:
4
Le descrizioni delle icone nella barra degli strumenti Tabella Pivot sono relative alla versione di
Excel di Office2000 e precedenti.
4
Corso di
Statistica Computazionale
-
Prof.ssa Paola Vicard
trascinare la cella a sfondo grigio Categorie sulla cella Totale
complessivo (cella L9)
trascinare la cella a sfondo grigio Rischio nello cella Totale
Costruzione di tabelle per tre variabili (tabelle a tre vie)
Dal momento che il dataset contiene più di due variabili possiamo costruire anche
tabelle per tre variabili. Queste tabelle riportano al loro interno le frequenze con le
quali si osservano nel nostro data set le diverse terne di modalità delle tre variabili
considerate.
Si potrebbe ricominciare daccapo la costruzione della tabella pivotale ma, avendone
già costruita una per due variabili, è più immediato incorporare la terza variabile nella
tabella esistente.
- cliccare su una qualunque cella della tabella pivotale
- nella barra dei strumenti Tabella pivot cliccare sull’icona Creazione
guidata Tabella pivot (la sesta icona da destra).
In questo modo ci si trova al passo 3 della costruzione della tabella pivotale.
- cliccare su Layout
- trascinare il riquadro Orientamento (che appare nella parte destra della
finestra di dialogo) dentro l’area PAGINA della tabella
Cliccare su OK.
- indicare che si desidera collocare la tabella pivot nel foglio di lavoro
esistente a partire dalla cella H18. Cliccare su Fine.
Si ottiene la tabella (nel cerchio rosso) mostrata nella Figura 3
Figura 3
Vediamo che sopra la cella a sfondo grigio H18 (Conteggio di categoria) appare la
cella a sfondo grigio H16 (Orientamento). Accanto ad essa (ossia nella cella I16)
5
Corso di
Statistica Computazionale
Prof.ssa Paola Vicard
abbiamo la cella che consente di controllare la modalità del carattere Orientamento
condizionatamente alla quale osserviamo la distribuzione doppia (ovverosia la tabella
doppia nelle celle H18:L23). Vediamo che nella cella I16 c’è “tutto”; questo vuol dire
che la tabella doppia è la distribuzione doppia di “Categoria” e “Rischio” (notate che
infatti è uguale alla tabella doppia nelle celle H8:L13. Usando la freccetta nella cella
I16 possiamo scegliere condizionatamente a quale modalità di “Orientamento”
desideriamo vedere la distribuzione doppia di “Categoria” e “Rischio”.
Supponiamo di essere interessati a rappresentare la distribuzione di “Categoria” e
“Rischio” condizionatamente alla modalità crescita di “Orientamento”. Basta cliccare
sulla freccetta che vedete nella cella I16: Si apre un menu (dove sono elencate le
modalità di “Orientamento” inclusa la possibilità “tutto” che significa che non si è
interessati ad una distribuzione condizionata). Si clicca sulla modalità rispetto alla
quale si vuole la tabella di “Categoria” e “Rischio” e poi si clicca OK. Si ottiene la
tabella che si vede cerchiata in rosso nella Figura 4. In questa tabella vediamo che i
fondi orientati alla crescita sono 49 (cella L23). Inoltre possiamo vedere come questi
49 fondi:
si distribuiscono per tipo di categoria (ultima colonna della tabella, L20:L23)
si distribuiscono per livello di rischio (ultima riga della tabella, I23:L23)
si distribuiscono per tipo di categoria e livello di rischio (I20:L22)
si distribuiscono per tipo di categoria condizionatamente a uno specifico livello di
rischio (colonne interne alla tabella)
si distribuiscono per livello di rischio condizionatamente a una specifica categoria
(righe interne alla tabella)
Figura 4
6
Corso di
Statistica Computazionale
Prof.ssa Paola Vicard
Modificare una tabella a tre vie.
•
La variabile nell’area pagina (nel nostro esempio “Orientamento”) può essere
scambiata con una delle altre due variabili. Vediamo come fare per scambiare di
posto “Orientamento” con “Rischio”.
-
trascinare la cella a sfondo grigio Rischio I18 sotto la cella a sfondo grigio
Orientamento (ossia trascinare nella cella H17 il nome della variabile che si
vuole mettere nell’area pagina)
-
Cliccare sulla cella a sfondo grigio “Orientamento” e trascinarla nella cella
(I19) dove è scritto Totale (ossia trascinare dentro la cella dove è scritto Totale
il nome della variabile che si vuole mettere nell’area colonna).
Si ottiene la seguente tabella:
Rischio
(All)
Count of Categoria
Categoria
grandi
medie
piccole
Grand Total
Orientamento
crescita
valore
23
9
17
49
19
10
43
72
Grand Total
42
19
60
121
Tabella 3
•
Una variabile può essere eliminata dalla tabella pivotale (senza dover ripetere i
passi della costruzione della tabella). Per fare questo basta cliccare sul nome della
variabile che si vuole togliere (ossia nella cella a sfondo grigio in cui è scritto tale
nome) e trascinare la cella fuori dell’area della tabella pivotale. In questo modo
rimane una tabella a due variabili.
Se si vuole ripristinare la tabella a tre variabili basta cliccare nella barra dei
comandi su Edit e nel menu che si apre scegliere Annulla.
Costruzione di tabelle a due variabili quando uno dei caratteri è
quantitativo.
Per il momento abbiamo visto la costruzione di tabelle (a due e a tre vie) nel caso in
cui i caratteri considerati siano qualitativi. Tale metodo vale anche nel caso in cui uno
o più caratteri che si intendono rappresentare nella tabella siano quantitativi discreti
con un numero ridotto di modalità.
Consideriamo il nostro dataset e vediamo che tra i caratteri c’è “Rendimento a 5 anni”
(d’ora innanzi indichiamo questa variabile con “Rendimento”). Questo è quantitativo
continuo e, come si può vedere osservando i dati nella colonna F, assume un numero
estremamente elevato di modalità (al limite potrebbe succedere che i 121 fondi hanno
tutti rendimenti diversi).
Se noi usassimo direttamente il metodo della tabella pivotale mettendo, per esempio,
“Rendimento” nell’area RIGA, otterremmo in output una tabella con un elevatissimo
numero di righe. Questa tabella non costituirebbe una sintesi e sarebbe poco leggibile.
Pertanto prima di passare all’uso della tabella pivot, è necessario: dividere il carattere
in classi e assegnare ciascuna unità statistica alla sua classe di appartenenza.
L’operazione da eseguire è una ricodifica dei dati.
7
Corso di
Statistica Computazionale
Prof.ssa Paola Vicard
Consideriamo il carattere “Rendimento” e costruiamo quattro classi i cui estremi
superiori sono nelle celle H2:H5 e i cui valori centrali sono nelle celle I2:I5. Ciascuna
classe viene rappresentata con il suo valore centrale. La procedura di ricodifica si basa
sull’uso di funzioni logiche del tipo SE.
Vediamo come fare:
- inserire quattro colonne prima della colonna G. Per fare questo cliccare su
G. Nella barra dei comandi cliccare su Inserisci e nel menu che si apre
scegliere Colonne. Ripetere questa operazione altre tre volte.
- Nella cella G1 scrivere I classe
- Nella cella H1 scrivere II classe
- Nella cella I1 scrivere III classe
- Nella cella J1 scrivere IV classe
- Nella cella G2 scrivere la formula =SE(F2<=6;3;0)
Alternativamente questa formula può essere scritta usando Inserisci/Funzione.
Cliccare nella barra dei comandi su Inserisci e poi scegliere Funzione. Nella
finestra di dialogo che si apre scegliere Logiche tra le categorie di funzioni. Il
nume della funzione è SE. Cliccare OK. Si apre una finestra di dialogo.
Nello spazio bianco accanto a Test va inserita la regola da verificare (ossia
l’appartenenza alla prima classe). Questa regola è F2<=6
Nello spazio bianco accanto a Se_vero va inserito il valore che la funzione
assume se la regola sopra è verificata. Nel nostro caso, quindi va inserito il
valore centrale della prima classe (ossia 3) visto che ci stiamo occupando, per
il momento, della codifica della prima classe.
Nello spazio bianco accanto a Se_falso va inserito il valore che la funzione
assume se la regola sopra non è verificata. Nel nostro caso, inseriamo 0
Cliccare OK.
- Trascinare la formula fino a coprire la cella G122
- Nella cella H2 scrivere la formula =SE(E(F2>6;F2<=13);9,5;0)
La colonna H è dedicata alla II classe e quindi si verifica che i rendimenti
siano maggiori di 6 e minori o uguali a 13; questo si scrive con la funzione E
in cui le due condizioni, F2>6 e F2<=13, che devono essere verificate
congiuntamente, sono separate dal punto e virgola.
Come per la colonna G, questa formula può essere inserita anche usando
Inserisci/Funzione/Logiche e poi scegliendo la funzione SE. In questo caso
nello spazio bianco accanto a Test va scritta la regola che è E(F2>6;F2<=13).
Nel Se_vero va scritto 9,5; nel Se_falso va scritto 0. Infine cliccare su OK.
- Trascinare la formula fino a coprire la cella H122
- Nella cella I2 scrivere la formula =SE(E(F2>13;F2<=20);16,5;0)
La colonna I è dedicata alla III classe e quindi si verifica che i rendimenti
siano maggiori di 13 e minori o uguali a 20; questo si scrive con la funzione E
in cui le due condizioni, F2>13 e F2<=20, che devono essere verificate
congiuntamente, sono separate dal punto e virgola.
Come per la colonna H, questa formula può essere inserita anche usando
Inserisci/Funzione/Logiche e poi scegliendo la funzione SE. In questo caso
nello spazio bianco accanto a Test va scritta la regola che è E(F2>13;F2<=20).
Nel Se_vero va scritto 16,5; nel Se_falso va scritto 0. Infine cliccare su OK.
- Trascinare la formula fino a coprire la cella I122
- Nella cella J2 scrivere la formula =SE(F2>20;25;0)
Come per la colonna G, questa formula può essere inserita anche usando
Inserisci/Funzione/Logiche e poi scegliendo la funzione SE. In questo caso
8
Corso di
Statistica Computazionale
-
Prof.ssa Paola Vicard
nello spazio bianco accanto a Test va scritta la regola che è F2>20. Nel
Se_vero va scritto 25; nel Se_falso va scritto 0. Infine cliccare su OK.
Trascinare la formula fino a coprire la cella J122
Ora abbiamo una colonna per ciascuna classe. Nelle diverse colonne abbiamo gli zeri
in corrispondenza di rendimenti che non appartengono alla classe a cui si riferisce la
colonna. Non resta quindi che costruire una ultima colonna che metta insieme le
quattro precedentemente calcolate. Questo si fa sommando per riga i risultati delle
colonne appena costruite.
- inserire una colonna prima della colonna K. Per fare questo cliccare su K.
Nella barra dei comandi cliccare su Inserisci e nel menu che si apre scegliere
Colonne.
- Nella cella K1 scrivere Classe di rendimento
- Nella cella K2 scrivere la formula =SOMMA(G2:J2)
- Trascinare la formula fino a coprire la cella K122
La variabile “classe di rendimento” si sarebbe potuta costruire anche con un unico
passaggio. Finora si è voluto spiegare il meccanismo logico che conduce al calcolo di
tale variabile. Se avessimo voluto calcolare la variabile “classe di rendimento” con
un solo passaggio, avremmo dovuto usare la seguente espressione logica
=SE(F2<=6;3;SE(F2<=13;9,5;SE(F2<=20;16,5;25)))
Il risultato dell’uso di questa funzione è mostrato nella colonna L del file
“esercizio8_svolto.xls”.
Questa funzione può essere anche scritta usando Inserisci/Funzione/Logiche e poi
scegliendo la funzione SE. Nello spazio bianco accanto a Test va scritta la regola
F2<=6. Nel Se_vero va scritto 3; nel Se_falso va scritto
SE(F2<=13;9,5;SE(F2<=20;16,5;25)). Infine cliccare su OK.
Una volta effettuata la ricodifica, possiamo passare a costruire una tabella a doppia
entrata usando il metodo della tabella pivotale.
Costruiamo la tabella per “classe di rendimento” e “Rischio”.
- cliccare su una cella qualunque del dataset
- nella barra dei comandi cliccare su Dati e nel menu che si apre scegliere
Rapporto tabella pivot e grafico pivot...
Passo 1
- confermare che i dati sono in un Elenco o database Microsoft Excel.
Indicare (selezionando) che il tipo di rapporto da creare è una Tabella
pivot. Cliccare su Avanti.
Passo 2
- confermare che i dati si trovano nell'
intervallo $A$1:$L$122. Cliccare su
Avanti.
Passo 3
- Cliccare su Layout.
Decidiamo che “Classe di rendimento” è nelle righe e “rischio” è nelle
colonne.
- Trascinare il riquadro Classe di rendimento (che appare nella parte
destra della finestra) dentro l’area RIGA della tabella
- Trascinare il riquadro Rischio (che appare nella parte destra della
finestra) dentro l’area COLONNA della tabella
- Inoltre trascinare il riquadro Rischio (sempre quello che appare nella
parte destra della finestra) dentro l’area DATI della tabella.
9
Corso di
Statistica Computazionale
Prof.ssa Paola Vicard
In questo modo Excel produce, all’interno dell’area DATI, un riquadro
conteggio di Rischio.
In questo caso se ci interessa costruire una tabella di frequenze assolute,
dobbiamo inserire nell’area DATI la variabile “Rischio”. 5
Cliccare su OK.
Passo 4
indicare che si desidera collocare la tabella pivot nel foglio di lavoro
esistente a partire dalla cella N27. Cliccare su Fine.
La tabella pivot apparirà come in Figura 5
Figura 5
Notate che la cella che si riferisce alla modalità 9,5 di “Classe di rendimento” e alla
modalità alto di “Rischio” è vuota. Questo sta a significare che nessuno dei 121 fondi
esaminati è ad alto rischio e appartiene alla classe di rendimento con centro 9,5. Per
una migliore leggibilità della tabella può essere utile che se ci sono celle vuote allora
nella tabella appaia lo zero. Per fare questo basta cliccare su una cella qualunque della
tabella pivotale. Quindi nella barra dei strumenti Tabella pivot cliccare sull’icona
Creazione guidata Tabella pivot (la sesta da destra). Cliccare su Opzioni. Si
apre una nuova finestra. Dove è scritto Per le celle vuote mostra scrivere 0.
Cliccare su OK. Cliccare su Fine.
Prime elaborazioni su una distribuzione doppia.
Una volta che abbiamo una tabella a doppia entrata possiamo eseguire alcune
operazioni.
•
Calcolo delle frequenze relative della distribuzione doppia
•
Calcolo delle frequenze relative delle distribuzioni condizionate
5
Se inserissimo la variabile quantitativa “Classi di rendimento” (che, si ricordi, ha come modalità i
valori centrali delle classi) si otterrebbe in automatico una distribuzione di quantità e non una
distribuzione di frequenze. Per ottenere la distribuzione di frequenze occorrerebbe un passaggio
aggiuntivo.
10
Corso di
Statistica Computazionale
•
Prof.ssa Paola Vicard
Calcolo di indici (di posizione e di variabilità) con riferimento alla distribuzione
(marginale o condizionata) di una delle due variabili.
Calcolo delle frequenze relative della distribuzione doppia
- Copiare le modalità di “classe di rendimento”. Selezionare N29:N33.
Premere contemporaneamente i tasti ctrl c
- Incollare le modalità di “classe di rendimento”. Cliccare su N36. Premere
contemporaneamente i tasti ctrl v
- Copiare le modalità di “rischio”. Selezionare O28:R28. Premere
contemporaneamente i tasti ctrl c
- Incollare le modalità di “rischio”. Cliccare su O35. Premere
contemporaneamente i tasti ctrl v
- nella cella O36 scrivere la formula =O29/$R$33
notare che in questo caso occorre bloccare a denominatore lo scorrimento sia
per colonne che per righe in quanto la formula va copiata in tutte le celle della
tabella (riga e colonna dei totali incluse).
- Trascinare la formula per riga e per colonna in modo da coprire tutte le
celle incluse nel rettangolo O36:R40.
- Si può modificare il numero di decimali usando l’icona (presente nella barra
degli strumenti di Excel, si veda Figura 1 di questa nota) diminuisci decimali
oppure cliccando nella barra dei comandi su Formato/celle e scegliendo
“numero” tra le categorie possibili e fissando le “posizioni decimali”.
Calcolo delle frequenze relative delle distribuzioni condizionate
Si ricordi che le distribuzioni condizionate sono date dalle colonne interne alla tabella
(distribuzioni di “classi di rendimento” condizionate a una modalità di “rischio”) e
dalla righe interne alla tabella (distribuzioni di “rischio” condizionate a una modalità
di “classi di rendimento”).
Calcoliamo le frequenze relative delle distribuzioni di “classe di rendimento”
condizionate alle modalità di “rischio”.
- Copiare le modalità di “classe di rendimento”. Selezionare N29:N33.
Premere contemporaneamente i tasti ctrl c
- Incollare le modalità di “classe di rendimento”. Cliccare su N44. Premere
contemporaneamente i tasti ctrl v
- Copiare le modalità di “rischio”. Selezionare O28:R28. Premere
contemporaneamente i tasti ctrl c
- Incollare le modalità di “rischio”. Cliccare su O43. Premere
contemporaneamente i tasti ctrl v
- nella cella O44 scrivere la formula =O29/O$33
in questo caso si deve bloccare a denominatore lo scorrimento solo sulle righe.
- Trascinare la formula fino a coprire O48.
- Selezionare O44:O48 e trascinare fino a coprire le celle R44:R48.
Se il calcolo è eseguito in modo corretto, nell’ultima riga ci devono essere tutti 1.
Calcoliamo le frequenze relative delle distribuzioni di “rischio” condizionate alle
modalità di “classe di rendimento”.
- Copiare le modalità di “classe di rendimento”. Selezionare N29:N33.
Premere contemporaneamente i tasti ctrl c
- Incollare le modalità di “classe di rendimento”. Cliccare su N52. Premere
contemporaneamente i tasti ctrl v
11
Corso di
Statistica Computazionale
Prof.ssa Paola Vicard
Copiare le modalità di “rischio”. Selezionare O28:R28. Premere
contemporaneamente i tasti ctrl c
- Incollare le modalità di “rischio”. Cliccare su O51. Premere
contemporaneamente i tasti ctrl v
- nella cella O52 scrivere la formula =O29/$R29
in questo caso si deve bloccare a denominatore lo scorrimento solo sulle
colonne.
- Trascinare la formula fino a coprire R52.
- Selezionare O52:R52 e trascinare fino a coprire le celle O56:R56.
Se il calcolo è eseguito in modo corretto, nell’ultima colonna ci devono essere tutti 1.
-
Calcolo di indici (di posizione e di variabilità)
→ A partire da una tabella a doppia entrata.
Data una tabella a doppia entrata, il calcolo di indici di posizione e/o di variabilità con
riferimento alla distribuzione (marginale o condizionata) di una delle due variabili
viene eseguito seguendo le procedure viste nelle note precedenti. Infatti per effettuare
il calcolo è sufficiente estrarre dalla tabella la distribuzione univariata di interesse.
Se, ad esempio, si vuole calcolare la media condizionata del rendimento
condizionatamente a “rischio”=medio allora basta estrarre i dati dalla tabella
copiandone la prima colonna (quella delle modalità di “Classe di rendimento”) e la
quarta colonna (quella contenente le frequenze assolute che si riferiscono ai fondi a
medio rischio).
- nella cella T28 scrivere classe di rendimento
- nella cella U27 scrivere Rischio=medio
- nella cella U28 scrivere n[i]
- Selezionare N29:N33. Premere contemporaneamente i tasti ctrl c
- Cliccare su T29. Premere contemporaneamente i tasti ctrl v
- Selezionare Q29:Q33. Premere contemporaneamente i tasti ctrl c
- Incollare le modalità di “rischio”. Cliccare su U29. Premere
contemporaneamente i tasti ctrl v
- A questo punto si ha una distribuzione di frequenze per un carattere e
possiamo applicare: (a) quanto visto nella nota 4 per il calcolo di moda, media
e mediana (per questa occorre anche l’informazione sull’estremo superiore
delle classi che, si ricorda, è data ed è ora in N2:N5); (b) quanto visto nella
nota 5 per il calcolo della varianza e della deviazione standard.
Per calcolare la media condizionata si può usare direttamente la tabella pivot.
Si ricordi che nella tabella pivot che abbiamo costruito per “classe rendimento” e
“rischio”, nell’area DATI abbiamo inserito “Rischio” perché ci interessava avere
come output immediato le frequenze assolute.
Per usare la tabella pivot per il calcolo dei rendimenti medi dobbiamo inserire
nell’area DATI la variabile “Classe di rendimento”.
Questo si può fare o costruendo ex novo la tabella pivot o modificando la tabella pivot
già disponibile. Nel primo caso
- cliccare su una cella qualunque del dataset
- nella barra dei comandi cliccare su Dati e nel menu che si apre scegliere
Rapporto tabella pivot e grafico pivot...
Passo 1
12
Corso di
Statistica Computazionale
Prof.ssa Paola Vicard
confermare che i dati sono in un Elenco o database Microsoft Excel.
Indicare (selezionando) che il tipo di rapporto da creare è una Tabella
pivot. Cliccare su Avanti.
Passo 2
- confermare che i dati si trovano nell'
intervallo $A$1:$L$122. Cliccare su
Avanti. Se chiesto se si vuole basare la tabella pivot sugli stessi dati di
quella esistente rispondere SI.
Passo 3
- Cliccare su Layout.
“Classe di rendimento” è nelle righe e “rischio” è nelle colonne.
- Trascinare il riquadro Classe di rendimento (che appare nella parte
destra della finestra) dentro l’area RIGA della tabella
- Trascinare il riquadro Rischio (che appare nella parte destra della
finestra) dentro l’area COLONNA della tabella
- Inoltre trascinare il riquadro Classe di rendimento (sempre quello che
appare nella parte destra della finestra) dentro l’area DATI della tabella.
In questo modo Excel produce, all’interno dell’area DATI, un riquadro
Somma di Classe di rendimento.
Infatti Excel in automatico fornisce: (a) la somma di una variabile
numerica (come classe di rendimento le cui modalità sono i valori centrali
delle classi); (b) il conteggio (le frequenze) di una variabile qualitativa
Cliccare su OK.
Passo 4
indicare che si desidera collocare la tabella pivot nel foglio di lavoro esistente a
partire dalla cella N62. Cliccare su Fine.
-
Ottengo la medesima tabella se modifico quella già disponibile (nelle celle N27:R33).
Cliccare su una cella della tabella da modificare. Nella barra dei strumenti Tabella
pivot cliccare sull’icona Creazione guidata Tabella pivot (la sesta da destra).
Cliccare su Layout. Portare fuori dell’area DATI “conteggio di rischio”. Inserire
nell’area DATI “Classe di rendimento”. Cliccare OK. Cliccare Fine.
La tabella pivot andrà a sostituire quella esistente.
Data la tabella pivot ottenuta in uno qualunque dei due modi precedenti. Calcolare le
medie di Rendimento marginale oppure condizionate ai livelli di rischio è molto
semplice. Lavoriamo sulla tabella nelle celle N62:R68.
- fare doppio click su N62 (ossia Somma di classe di rendimento).
- Si apre la finestra Campo Pivot Table. Nello spazio destinato a Riepiloga per
scegliere Media
Osservate che il menu di riepiloga per è piuttosto ricco e contiene al suo
interno misure di nostro interesse: 1) Conta, con cui si ottiene la distribuzione
di frequenze; 2) Media con cui si calcolano le medie (marginale e
condizionate); 3) varianza pop con cui si calcolano le varianze (marginale e
condizionate); 4) Dev.standard pop con cui si calcolano le deviazioni
standard (marginale e condizionate)
- Cliccare OK.
Nelle celle O68:Q68 ci sono le medie del rendimento condizionate ai diversi
livelli di rischio nella cella R68 c’è la media (marginale) dei rendimenti.
→ A partire dalla distribuzione doppia per unità.
13
Corso di
Statistica Computazionale
Prof.ssa Paola Vicard
Se abbiamo a disposizione l’intera distribuzione per unità possiamo procedere al
calcolo della media dei rendimenti direttamente dalla lista dei dati (F2:F122)
ottenendo così dei valori esatti (e non approssimati come finora fatto dal momento
che ciascuna classe è stata rappresentata dal suo valore centrale).
Il calcolo della media, della varianza e della deviazione standard è molto semplice
perché si tratta di una distribuzione univariata e quindi usano direttamente le funzioni
=media(F2:F122) per la media, =VAR.POP(F2:F122) per la varianza,
=DEV.ST.POP(F2:F122) per la deviazione standard.
Il discorso è diverso se si vogliono calcolare, ad esempio, le medie condizionate.
Vediamo come fare.
Dobbiamo calcolare le medie di “rendimento” condizionatamente a “rischio”=basso,
“rischio”=medio, “rischio”=alto.
Iniziamo dalla media condizionata di rendimento dato “rischio”=basso. Questa è data
da
somma dei rendimenti dei fondi a basso rischio
.
numero di fondi a basso rischio
Vediamo che né il numeratore né il denominatore sono direttamente disponibili. Essi
sono facilmente calcolabili mediante le funzioni SOMMA.SE (per il numeratore) e
CONTA.SE (per il denominatore).
- nella cella N71 scrivere Totale rendimenti fondi basso rischio
- nella cella O71 scrivere la formula
=SOMMA.SE(D2:D122;"basso";F2:F122)
Se non vi ricordate la formula potete usare Inserisci/Funzione/matematiche e
poi scegliere SOMMA.SE. Si apre una finestra di dialogo in cui: nello spazio
bianco accanto a Intervallo dovete scrivere dove si trovano le celle su cui va
verificata la condizione del SE (ossia i dati sul rischio, D2:D122); nello
spazio bianco accanto a Criterio va scritto “basso” perchè vogliamo
selezionare solo i fondi a basso rischio (basso è scritto tra virgolette perchè
non è un numero); nello spazio bianco accanto a Int_somma dobbiamo
scrivere dove si trovano i dati da sommare (ossia i nostri rendimenti,
F2:F122).
- nella cella N72 scrivere N° fondi a basso rischio
- nella cella O72 scrivere la formula =CONTA.SE(D2:D122;"basso")
- nella cella N69 scrivere Media esatta
- nella cella P69 (ossia sotto la media condizionata a “rischio”=basso già
calcolata dalla tabella doppia) scrivere la formula =O71/O72
Nello stesso modo possiamo calcolare la media di “rendimento”
condizionatamente a “rischio”=medio.
- nella cella N73 scrivere Totale rendimenti fondi medio rischio
- nella cella O73 scrivere la formula
=SOMMA.SE(D2:D122;"medio";F2:F122)
Se non vi ricordate la formula e usate Inserisci/Funzione/matematiche e poi
SOMMA.SE i dati di input sono quelli visti nel somma.se vincolata a
“rischio”=basso con la sola differenza che nello spazio bianco accanto a
Criterio va scritto “medio” perché vogliamo selezionare solo i fondi a medio
rischio.
- nella cella N74 scrivere N° fondi a medio rischio
- nella cella O74 scrivere la formula =CONTA.SE(D2:D122;"medio")
14
Corso di
Statistica Computazionale
-
Prof.ssa Paola Vicard
nella cella Q69 (ossia sotto la media condizionata a “rischio”=medio già
calcolata dalla tabella doppia) scrivere la formula =O73/O74
Nello stesso modo possiamo calcolare la media di “rendimento”
condizionatamente a “rischio”=alto.
- nella cella N75 scrivere Totale rendimenti fondi alto rischio
- nella cella O75 scrivere la formula
=SOMMA.SE(D2:D122;"alto";F2:F122)
Se non vi ricordate la formula e usate Inserisci/Funzione/matematiche e poi
SOMMA.SE i dati di input sono quelli visti nel somma.se vincolata a
“rischio”=alto con la sola differenza che nello spazio bianco accanto a
Criterio va scritto “medio” perché vogliamo selezionare solo i fondi a medio
rischio.
- nella cella N76 scrivere N° fondi a alto rischio
- nella cella O76 scrivere la formula =CONTA.SE(D2:D122;"alto")
- nella cella O69 (ossia sotto la media condizionata a “rischio”=alto già
calcolata dalla tabella doppia) scrivere la formula =O75/O76
Infine, possiamo calcolare la media generale usando la funzione MEDIA.
Nella cella R69 scrivere la formula =MEDIA(F2:F122)
Se si vogliono calcolare le varianze condizionate allora la procedura è un pochino più
complessa. La formula della varianza più comoda da usare in questa contesto è
1
2
2
σ Rendimento|Rischio=basso
=
x 2 − µ Rendimento|Rischio=basso
n° fondi basso rischio i:rischio=basso i
Dal momento che non esiste una funzione in Excel che somma i quadrati
condizionatamente al verificarsi di una condizione, è necessario innanzitutto costruire
una colonna con i quadrati dei rendimenti per poi usare SOMMA.SE.
Passiamo al calcolo della varianza dei rendimenti condizionata a “rischio”=basso.
- Nella cella W1 scrivere Rend.^2
- nella cella W2 scrivere la formula =F2^2
- trascinare la formula fino a coprire la cella W122.
- nella cella N77 scrivere Rendimenti^2 fondi a basso rischio
- nella cella O77 scrivere la formula
=SOMMA.SE(D2:D122;"basso";W2:W122)
Adesso abbiamo tutti gli elemtni per il calcolo della varianza
- nella cella N70 scrivere Varianza esatta
- nella cella P70 scrivere la formula =O77/O72-P69^2
In modo del tutto analogo possiamo calcolare le altre varianze condizionate. Trovate i
risultati nel file “esercizio8_svolto.xls”
Per calcolare la varianza generale scrivere nella cella R70 la formula
=VAR.POP(F2:F122).
Se volete confrontare la varianze esatte calcolate ora con le varianze approssimate
calcolabili dalla tabella pivotale, basta fare doppio click sulla cella N62 (Media di
classe di rendimento) e nel menu di riepiloga per scegliere Varianza pop.
15
