= ∑ ∑ ∑ ∑ ∑ ∑

Corso di
Statistica Computazionale
Prof.ssa Paola Vicard
Excel non ha una funzione per calcolare automaticamente gli indici di concentrazione
e per costruire la curva di Lorenz. Tuttavia è possibile calcolare tali indici e costruire
tale grafico con alcune procedure.
La concentrazione può essere misurata per caratteri quantitativi trasferibili.
Consideriamo una distribuzione per unità e poniamo in ordine non decrescente le sue
osservazioni a1, a2, ..., an in modo tale che a1 ≤ a2 ≤ ... ≤ an-1 ≤ an
Indichiamo con
Ai = a1 + a 2 +
+ ai =
i
j =1
aj
l’ammontare di carattere posseduto dalle i unità più povere, ovvero dalle prime i unità
statistiche della serie ordinata. Indichiamo con
A = a1 + a 2 +
+ an =
n
j =1
aj
l’ammontare complessivo di carattere nel collettivo.
Ai
che è la frazione di ammontare del carattere, sull’ammontare
A
complessivo, posseduto dalle i unità più povere.
Calcoliamo Qi =
Calcoliamo Pi =
i
che è la frazione, sul totale delle unità, delle i unità più povere.
n
Il rapporto di concentrazione si calcola come segue (le tre formule sono equivalenti)
n −1
g=
i =1
( Pi − Qi )
n −1
i =1
Pi
(1)
n −1
=1−
Qi
i =1
n −1
i =1
(2)
= 1−
Pi
2 n −1
Q
n − 1 i =1 i
(3)
Le tre espressioni sono del tutto equivalenti; la scelta di una tre queste è legata alla
semplicità della rispettiva procedura di calcolo.
Vediamo come calcolare il rapporto di concentrazione con Excel.
Nell’illustrazione della procedura useremo il foglio di lavoro “Distr. unitaria” del file
“esercizio7_dati.xls” in cui è riportato il fatturato di una società di computer ripartito
in base ai clienti.
I passi da compiere sono i seguenti:
1) ordinare i dati
2) calcolare i Pi – questo passo consiste nella costruzione di una colonna di calcolo
(chiamata i) che conta le i unità più povere (ossia, nel nostro esempio, con il minor
fatturato associato) e nella costruzione della colonna dei Pi (chiamata P[i])
3) calcolare i Qi – questo passo consiste nella costruzione di una colonna di calcolo
(chiamata A[i]) che cumula il fatturato associato alle i unità più povere (ossia con
1
Corso di
Statistica Computazionale
Prof.ssa Paola Vicard
fatturato associato più basso) e nella costruzione della colonna dei Qi (chiamata
Q[i])
4) applicare una delle 3 formule1
Osservazione: Innanzitutto notiamo che c’è una riga vuota tra le riga dei titoli e le
righe dei dati. La presenza di questa riga vuota è necessaria quando,
oltre al calcolo di g, si vuole disegnare la curva di Lorenz e calcolare
R. Infatti per tali scopi è necessario inserire P0=0 e Q0=0 (e quindi
occorre avere una riga libera).
Iniziamo con l’ordinamento dei dati:
- Cliccare su una cella della lista da ordinare
- nella barra dei comandi cliccare su dati
- quindi, nel menu che si apre, cliccare su ordina.
- Si apre una finestra di dialogo. Con riferimento a ordina per, selezionare la
variabile rispetto a cui si vuole ordinare (ossia nel nostro esempio Fatturato)
- Cliccare su ordinamento crescente
- Controllare che sia selezionato intervallo dati con riga di intestazione.
- Cliccare su OK.
Passiamo al calcolo dei Pi:
- nella cella C2 scrivere il titolo i
- nella cella C4 scrivere 1
- nella cella C5 scrivere 2
- selezionare le celle C4:C5, posizionarsi nell’angolo in basso a destra finché
il cursore appare come una crocetta. Cliccare e trascinare fino a coprire la
cella C22
- nella cella D2 scrivere il titolo P[i]
- nella cella D4 scrivere la formula =C4/C$22 e trascinare la formula fino
a coprire la cella D22
Se il calcolo è stato effettuato correttamente allora il valore della cella D22 deve
risultare pari a 1.
Passiamo al calcolo dei Qi:
- nella cella E2 scrivere il titolo A[i]
- nella cella E4 scrivere la formula =somma(B$4:B4) e trascinare la
formula fino a coprire la cella E22.
Si noti che gli A[i] vengono calcolati nello stesso modo delle frequenze
relative cumulate (che abbiamo visto nella nota4 sugli indici di posizione).
L’uguaglianza della procedura di calcolo sta nel fatto che in entrambi i casi si
tratta di valori cumulati (in particolare ammontare cumulati nel caso degli
A[i] e frequenze cumulate nel caso delle F[i])
-
nella cella B24 calcolare l’ammontare totale di fatturato. Per fare questo nella
cella B24 scrivere =SOMMA(B4:B22) oppure usare il tasto Σ di somma
automatica.
Si noti che se gli ammontari cumulati (nelle celle E4:E22) sono stati calcolati
bene allora l’ammontare totale di fatturato deve essere uguale all’ammontare
1
Osserviamo che se applichiamo la formula (3) allora è possibile saltare il passo 2) ossia non è
necessario calcolare i Pi.
2
Corso di
Statistica Computazionale
Prof.ssa Paola Vicard
cumulato associato all’unità con maggiore fatturato (ossia deve essere uguale
al contenuto della cella E22)
-
nella cella F2 scrivere il titolo Q[i]
nella cella F4 scrivere la formula =E4/B$24 e trascinare la formula fino
a coprire la cella F22.
Se il calcolo è stato effettuato correttamente allora il valore della cella F22
deve risultare pari a 1.
Passiamo al calcolo del rapporto di concentrazione.
Decidiamo di usare la formula (2) allora:
- nella cella A28 scrivere g=
- nella cella B28 scrivere formula =1-SOMMA(F4:F21)/SOMMA(D4:D21)
Se avessimo usato la formula (3) avremmo dovuto scrivere la formula
=1-2/(C22-1)*SOMMA(F4:F21)
Se avessimo deciso di usare la formula (1) allora avremmo dovuto innanzitutto
costruire la colonna delle differenze P[i]-Q[i]. Pertanto la formula (1) è quella più
onerosa dal punto di vista dei calcoli richiesti.
Come costruire la curva di Lorenz (o spezzata di concentrazione).
Questa costituisce la rappresentazione grafica delle coppie di punti (Pi, Qi).
Viene rappresentata sul piano cartesiano ponendo Pi sull’asse delle ascisse e Qi
sull’asse delle ordinate.
Per convenzione si pone P0 = Q0 = 0. La curva di Lorenz è interamente contenuta nel
triangolo di estremi (0,0), (1,0) e (1,1).
Pertanto per disegnare tale curva occorre:
1) rappresentare il segmento di equidistribuzione (ossia la bisettrice del primo
quadrante)
2) rappresentare la curva di Lorenz.
Vediamo come fare:
- Nella cella D3 scrivere 0
- Nella cella F3 scrivere 0
- Selezionare le celle D3:D22 (ossia selezionare la colonna dei P[i] titolo
escluso)
- cliccare sull’icona della creazione guidata del grafico
Passo 1
- Controllare che la pagina attiva sia Tipi standard
- Tra le opzioni in Tipo di grafico selezionare Dispers.(XY)
- Selezionare tra le Scelte disponibili nella parte destra della finestra di
dialogo la prima della terza fila (ossia “Dispersione con coordinate
unite da linee”)
- Cliccare su Avanti
Passo 2
- Controllare che la pagina attiva sia Intervallo dati
- Confermare nello spazio sotto il grafico che Intervallo dati e Serie siano
in Colonne
- Sempre all’interno della stessa finestra di dialogo passare alla pagina
Serie
Dobbiamo in questo caso inserire due serie: quella per il segmento di
distribuzione e quella della spezzata di concentrazione.
3
Corso di
Statistica Computazionale
Prof.ssa Paola Vicard
Inseriamo per prima la serie del segmento di equidistribuzione:
- Nello spazio bianco accanto a Nome scrivere Segmento di
equidistribuzione (in questo modo date un nome alla serie che
rappresentate)
- Cliccare nello spazio bianco accanto a Valori X. Per inserire i valori X,
cliccare sulla freccetta rossa alla destra dello spazio bianco accanto
alla dicitura Valori X
- Selezionare le celle D3:D22 e premere Invio
Notate che i valori X selezionati sono i P[i] e quindi sono uguali ai valori
Y che già appaiono selezionati. Questo perché stiamo disegnando la
bisettrice del primo quadrante ossia il segmento di equidistribuzione che
rappresenta la situazione (teorica) in cui le Q[i] sono uguali alle P[i].
Non cliccare su Avanti perché dobbiamo inserire la serie per la spezzata di
concentrazione.
Cliccare su Aggiungi
Nello spazio bianco accanto a Nome scrivere Spezzata di
concentrazione
- Cliccare nello spazio bianco accanto a Valori X. Per inserire i valori X,
cliccare sulla freccetta rossa alla destra dello spazio bianco accanto
alla dicitura Valori X
- Selezionare le celle D3:D22 e premere Invio
Quindi i valori X sono i P[i]
- Cliccare nello spazio bianco accanto a Valori Y. Per inserire i valori Y,
cliccare sulla freccetta rossa alla destra dello spazio bianco accanto
alla dicitura Valori Y
Quindi i valori Y sono i Q[i]
- Selezionare le celle F3:F22 e premere Invio
- Cliccare su Avanti
Passo 3
- Sulla pagina Titoli, scrivere il Titolo del grafico (nel nostro esempio
Curva di Lorenz); come Asse dei valori (X) scrivere P[i]; come
Asse dei valori (Y) scrivere Q[i]
- Sulla pagina Assi, confermare che per Asse principale è selezionato Asse
dei valori (X) e che per Asse secondario è selezionato Asse dei valori
(Y)
- Sulla pagina Griglia, deselezionare tutte i tipi di griglie
- Sulla pagina Legenda, deselezionare l’opzione Mostra legenda
- Sulla pagina Etichette dati, assicurarsi che sia selezionato Assente
- Cliccare su Avanti
Passo 4
- Posiziona il grafico Come oggetto in, ossia nel foglio di lavoro
corrente
- Cliccare su Fine
-
Terminata questa fase, è ancora necessario apportare delle modifiche al grafico.
Dal momento che la curva di Lorenz è interamente contenuta nel triangolo di estremi
(0,0), (1,0) e (1,1), dobbiamo far sì che la scale dei due assi abbiano valore massimo
pari a 1. Per fa ciò
- posizionare il cursore sull’asse orizzontale e cliccare
4
Corso di
Statistica Computazionale
-
Prof.ssa Paola Vicard
Nella barra dei comandi cliccare su Formato. Nel menu che si apre cliccare
su Asse selezionato. Si apre una finestra di dialogo.
Andare alla pagina Scala
in corrispondenza di Massimo modificare il valore che si trova scritto
inserendo 1
Cliccare su OK
Ora nello stesso modo lavoriamo sull’asse Y
- posizionare il cursore sull’asse verticale e cliccare
- Nella barra dei comandi cliccare su Formato. Nel menu che si apre cliccare
su Asse selezionato. Si apre una finestra di dialogo.
- Andare alla pagina Scala
- in corrispondenza di Massimo modificare il valore che si trova scritto
inserendo 1
- in corrispondenza di Unità Principale modificare il valore che si trova
scritto inserendo 0,1
- Cliccare su OK
Possiamo aggiustare il segmento di equidistribuzione (ossia la bisettrice) in modo che
si veda solo la linea e non gli indicatori dei punti.
- Cliccare su uno dei punti del segmento di equidistribuzione
- Nella barra dei comandi cliccare su Formato. Nel menu che si apre cliccare
su Serie dei dati selezionati. Si apre una finestra di dialogo composta da
varie pagine.
- Andare alla pagina Motivo.
- Nell’area Indicatore (pannello di destra) selezionare Assente.
- Cliccare su OK
A questo punto, se lo si ritiene opportuno, si possono fare ritocchi di tipo estetico
(cambiare il colore dell’area del grafico, cambiare il colore di una o di entrambe le
curve,...). I metodi sono gli stessi visti in precedenza per gli altri tipi di grafici.
Come calcolare l’indice di concentrazione R.
L’indice2 di concentrazione R è definito come segue
R=
n
n
1 − ( Qi + Qi −1 )( Pi − Pi −1 )
n −1
i =1
Si ricordi che R, al contrario di g, può essere calcolato sia per distribuzioni unitarie
che di frequenze.
Finora abbiamo già calcolato i Pi ed in Qi; non resta che
1) calcolare le differenze Pi – Pi-1 (questo significa costruire un colonna di calcolo
chiamata P[i]-P[i-1])
2) calcolare le differenze Qi + Qi-1 (questo significa costruire un colonna di calcolo
chiamata Q[i]+Q[i-1])
3) applicare la formula di R.
Vediamo passo passo come fare
2
che sappiamo coincidere con il rapporto di concentrazione g.
5
Corso di
Statistica Computazionale
Prof.ssa Paola Vicard
Calcolo dei Pi – Pi-1:
- nella cella G2 scrivere il titolo P[i]-P[i-1]
- nella cella G4 scrivere la formula =D4-D3 e trascinare la formula fino a
coprire la cella G22.
Calcolo dei Qi + Qi-1:
- nella cella H2 scrivere il titolo Q[i]+Q[i-1]
- nella cella H4 scrivere la formula =F4+F3 e trascinare la formula fino a
coprire la cella H22.
Calcolo di R
- nella cella I2 scrivere il titolo (P[i]-P[i-1])(Q[i]+Q[i-1])
- nella cella I4 scrivere la formula =G4*H4 e trascinare la formula fino a
coprire la cella I22.
- Nella cella I24 usare il tasto di somma automatica oppure scrivere la formula
=somma(I4:I22)
in questo modo si calcola la sommatoria presente nella formula di R
- nella cella A35 scrivere il titolo R=
- nella cella B35 scrivere la formula =C22/(C22-1)*(1-I24)
Anche in questo caso troviamo R = 0,33
Distribuzioni di frequenze
Nel caso i dati vengano forniti sotto forma di distribuzione di frequenza è possibile
costruire la curva di Lorenz e calcolare l’indice R che in questo caso assume la
seguente espressione
R=
k
n
1 − ( Qi + Qi −1 )( Pi − Pi −1 )
n −1
i =1
Le procedure di calcolo di R e di costruzione del grafico sono del tutto analoghe a
quelle viste sopra nel caso della distribuzione unitario.
Le uniche differenze si hanno nel calcolo dei Pi e dei Qi.
Nell’illustrazione useremo il foglio di lavoro “Distr. frequenze” del file
“esercizio7_dati.xls” in cui si riporta la distribuzione per reddito di 200 persone.
Qui di seguito limitiamo l’illustrazione al calcolo dei Pi e dei Qi essendo le procedure
per il calcolo di R e per la costruzione del grafico identiche a quelle viste per la
distribuzione di frequenze. Il foglio di lavoro “Distr. frequenze” del file
“esercizio7_svolto.xls” contiene il calcolo completo di R e la costruzione della curva
di Lorenz.
Osservazione: anche in questa analisi, così come in quella svolta per le distribuzioni
unitarie, c’è una riga vuota tra le riga dei titoli e le righe dei dati. La
presenza di questa riga vuota in questo caso è sempre necessaria visto
che per le distribuzioni di frequenze la concentrazione deve essere
misurata con R (e non con g).
Come prima cosa è bene verificare che le modalità siano ordinate in modo crescente.
Se queste non sono ordinate allora ordinare la distribuzione nel modo spiegato a pag.2
della presente nota.
6
Corso di
Statistica Computazionale
Prof.ssa Paola Vicard
1) calcolare i Pi – questo passo equivale al calcolo delle frequenze relative cumulate.
Consiste nella costruzione di una colonna di calcolo delle frequenze relative
(chiamate f[i]) e nella costruzione della colonna dei Pi (chiamata P[i])3.
2) calcolare i Qi – questo passo consiste nella costruzione di una colonna che
contenga, per ogni livello di reddito, l’ammontare di reddito posseduto dalle unità
che percepiscono quel reddito (colonna chiamata Reddito*n[i]). Quindi si
costruisce un’altra colonna di calcolo (chiamata A[i]) che cumula gli ammontari di
reddito calcolati nella colonna precedente. Infine si costruisce la colonna dei Qi
(chiamata Q[i])
Calcolo dei Pi:
- nella cella C2 scrivere il titolo f[i]
- nella cella C4 scrivere =B4/B$12 e trascinare fino a coprire la cella
C10
- nella cella D2 scrivere il titolo P[i]
- nella cella D4 scrivere la formula =somma(C$4:C4) e trascinare la
formula fino a coprire la cella D10
Se il calcolo è stato effettuato correttamente allora il valore della cella D10 deve
risultare pari a 1.
Calcolo dei Qi:
- nella cella E2 scrivere il titolo Reddito*n[i]
- nella cella E4 scrivere la formula =A4*B4 e trascinare la formula fino a
coprire la cella E10.
- nella cella F2 scrivere il titolo A[i]
- nella cella F4 scrivere la formula =SUM(E$4:E4) e trascinare la formula
fino a coprire la cella F10.
- nella cella E12 calcolare l’ammontare totale di fatturato. Per fare questo nella
cella E12 scrivere =SOMMA(E4:E10) oppure usare il tasto Σ di somma
automatica.
Si noti che se gli ammontari cumulati (nelle celle F4:F10) sono stati calcolati
bene allora l’ammontare totale di reddito (contenuto in E12) deve essere
uguale all’ammontare cumulato associato all’ultima modalità (ossia deve
essere uguale al contenuto della cella F10)
-
nella cella G2 scrivere il titolo Q[i]
nella cella G4 scrivere la formula =F4/E$12 e trascinare la formula fino
a coprire la cella G10.
Se il calcolo è stato effettuato correttamente allora il valore della cella G10
deve risultare pari a 1.
Terminata questa fase si può passare a costruire la curva di Lorenz.
Nella costruzione del grafico si ricordi che:
a) per la spezzata di concentrazione i valori X sono dati dai P[i] e i valori Y sono dati
dai Q[i]
b) per il segmento di equidistribuzione sia i valori X sia i valori Y sono dati dai P[i].
3
che concettualmente sono le frequenze relative cumulate.
7