Modelli Excel per la statistica 1 by Raffaele Scurelli

Modelli pratici per Excel

Raffaele Scurelli

Modelli Excel per la Statistica

Prima edizione

Copyright©2014 Raffaele Scurelli Tutti i diritti sono riservati a norma di legge, è vietata la riproduzione, parziale o totale senza autorizzazione scritta da parte dell’autore. Qualsiasi riproduzione, parziale o totale, anche a uso interno o a scopo didattico, priva di autorizzazione scritta sarà perseguita ai sensi di legge.

Stampato in giugno 2014

Indice

Introduzione CAPITOLO 1 - FREQUENZE ED INDICI 1. 2.

5. 6. 7.

Obiettivo n.1: costruzione di una tabella analisi dati Obiettivo n.2: costruzione di un grafico partendo da una tabella dati Grafico 1 Frequenza percentuale Grafico 2 Frequenza relativa Grafico 3 Frequenza cumulata Grafico 4 Frequenza percentuale cumulata Obiettivo n.3: costruzione degli indici Moda Mediana Indice di omogeneitĂ Indice omogeneitĂ normalizzato Media Media troncata Varianza Scarto quadratico medio Obiettivo n.4: visualizzazione della casistica Perfetta equidistribuzione Perfetta noequidistribuzione Distribuzione Normale Obiettivo n.5: casistica di esempio Definizioni Modello foglio di lavoro

8 11 11 13 13 13 14 14 15 15 15 16 16 17 17 17 17 18 18 19 20 21 30 33

CAPITOLO 2 - MODELLO DI CORRELAZIONE 1. 2. 3.

4. 5.

6. 7. 8.

Obiettivo n.1: costruzione di una tabella analisi dati Obiettivo n.2: costruzione di un grafico partendo da una tabella dati Grafico 1 Grafico a dispersione Obiettivo n.3: costruzione degli indici Media Indice di covarianza metodo 1 Indice di covarianza metodo 2 Indice di covarianza automazione Scarto quadratico medio Indice di correlazione Indice di correlazione automatizzato Varianza automatizzata Indice di Pearson automatizzato Normalizzazione automatizzata Coefficiente angolare Coefficiente angolare automatizzato Intercetta Intercetta automatizzata Obiettivo n.4:costruzione strumenti di calcolo Obiettivo n.5: visualizzazione della casistica Perfetta correlazione positiva Perfetta correlazione negativa Perfetta no correlazione Obiettivo n.6: casistica di esempio Definizioni Modello foglio di lavoro

CAPITOLO 3 - CONTROLLO CAMPIONARIO 1. 2. 3. 4.

5. 6. 7. 8.

Obiettivo n.1: costruzione di una tabella analisi dati Obiettivo n.2: costruzione istogramma seriazione campionaria Obiettivo n.3: costruzione delle carte di controllo Obiettivo n.4:visualizzazione della casistica Processo sotto controllo Processo fuori controllo Processo intermedio Obiettivo n.5:casistica di esempio Definizioni Modello foglio di lavoro Bibliografia

35 35 39 39 40 40 41 41 42 42 42 43 43 44 45 45 45 46 46 47 50 50 51 52 53 63 67

69 69 75 76 82 82 85 87 92 107 108 111

Introduzione Modelli Excel© per la Statistica è il primo libro della serie Modelli pratici per Excel. In questo volume composto da tre capitoli si affrontano le tematiche pratiche delle applicazioni statistiche in ambito delle valutazioni economico produttive. Il testo è impostato in modo tale che la comprensione degli argomenti trattati risulti la più immediata possibile, comprendendone il funzionamento attraverso l’ausilio del software di calcolo Excel. Gli argomenti trattati sono: la gestione dei dati, il loro raggruppamento in modalità, il calcolo delle percentuali, degli indici di posizione e la loro visualizzazione nel primo capitolo; indice di correlazione, indice di covarianza tra due variabili e rappresentazione grafica nel secondo capitolo; campionamento e sistemi di valutazione nel terzo capitolo.

CAPITOLO 2 - MODELLO DI CORRELAZIONE Il foglio di lavoro permette di verificare la sussistenza di un legame funzionale tra due variabili.

Obiettivo n.1: costruzione di una tabella analisi dati

X Y 1 2

xi-μx yi-μy

(xi-μx)² (yi-μy)²

181 54 9774

282 74 20868

312 75 23400

272 72 19584

254 75 19050

321 81 26001

211 65 13715

-84,50 -54,50 -44,50 -24,50 -18,92 -14,92 -8,92 -4,92 1598 813 397 120

16,50 1,08 18

46,50 2,08 97

6,50 -0,92 -6

-11,50 2,08 -24

55,50 8,08 449

-54,50 102,50 -7,92 25,08 431 2571

47 18 841

7306

272 1

2162 4

42 1

132 4

3080 65

2970 63

10506

2162

34019

629

327

358

2970 223

221 64 14144

1980 80

600 24

368 98 36064

312 91 28392

n 12 12

241 68 16388

7140

211 58 12238

(xi-μx)² automa (yi-μy)²automa

Nella tabella vengono trattati i seguenti argomenti: 1. 2. 3. 4.

Prodotto delle variabili Calcolo delle medie e scarti dalla media Quadrato degli scarti dalla media Automazione del quadrato degli scarti dalla media

X *Y xi-µx ; yi-µy (xi-µ)²; (yi-µ)²

Il procedimento di automazione delle formule di calcolo prevede i seguenti passaggi: Calcolo di n

CELLA O2=CONTA.NUMERI(C2:N2) CELLA O3=CONTA.NUMERI(C3:N3)

1779 34019 1779

1.Prodotto delle variabili X Y 1

9774

12238

14144

16388

20868

23400

19584

19050

26001

CELLA C4=PRODOTTO(C2:C3)

TRASCINARE LA CASELLA PER COPIARE FORMULA

2a.Calcolo delle medie µx µy

μx μy

265,50 72,92

CELLA Q2=MEDIA(C2:N2)

CELLA Q3=MEDIA(C3:N3)

13715

36064

28392

2b.Calcolo degli scarti dalla media 2

xi-μx yi-μy

-84,50 -54,50 -44,50 -24,50 -18,92 -14,92 -8,92 -4,92 1598 813 397 120

16,50 1,08 18

46,50 2,08 97

6,50 -0,92 -6

-11,50 2,08 -24

55,50 8,08 449

-54,50 102,50 -7,92 25,08 431 2571

CELLA C6=C2-Q2 CELLA D6=D2-Q2…………………CELLA N6=N2-Q2

CELLA C7=C3-Q3 CELLA D7=D3-Q3…………………CELLAN7=N3-Q3

2c.Prodotto degli scarti dalla media

TRASCINARE LA CASELLA PER COPIARE FORMULA

CELLA C8=PRODOTTO(C6:C7)

xi-μx yi-μy

-84,50 -54,50 -18,92 -14,92 1598 813

47 18 841

7306

CELLA O8=SOMMA(C8:N8)

3. Quadrato degli scarti dalla media 3

(xi-μx)² (yi-μy)²

7140 358

2970 223

1980 80

600 24

272 1

2162 4

42 1

132 4

3080 65

2970 63

10506

2162

34019

629

327

1779

CELLA C10=POTENZA(C6;2) TRASCINARE LA CASELLA PER COPIARE FORMULA

CELLA O10=SOMMA(C10:N10)

RIPETERE PER LA VARIABILE Y

4.Automazione del quadrato degli scarti dalla media CELLA O12=DEV.Q(C2:N2); CELLA O13=DEV.Q(C3:N3);

Obiettivo n.2: costruzione di un grafico partendo da una tabella dati Grafico 1 Grafico a dispersione

Scheda Inserisci > Grafici > Grafico a dispersione> Selezione dati

181

211

221

241

282

312

272

254

321

211

368

312

Tasto destro sui punti del grafico > selezionare linea di tendenza > visualizza equazione

Il grafico dimostra la correlazione tra le due variabili, nel caso opposto i punti risulterebbero distanziati dalla linea di tendenza.

Obiettivo n.3: costruzione degli indici

μx μy μ(xi*yi) μx*μy σxy σxy automa σxy

265,50 72,92

σx σy ρ ρ auto ma σ²x automa σ²y automa χ auto ma N auto ma N auto ma No rmauto ma No rmauto ma

53 12 0,94 0,94 2835 148 0,94 0,50 0,50 0,00 0,00

a a auto ma b b auto ma

0,21 0,21 16 16

Vengono trattati i seguenti argomenti:

19968 19359

609 609 609

1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15.

Media Media prodotto variabili X Y Prodotto delle medie Indice di covarianza Indice di covarianza automa Scarto quadratico medio Indice di correlazione Indice di correlazione automa Varianza automatizzata Indice di Pearson automa Distribuzione Normale automa Coefficiente angolare Coefficiente angolare automa Intercetta Intercetta automa

µx µy µ(xi*yi) µx*µy σxy σxy automa σx ρ ρ automa σ²x σ²y χ automa N automa a a automa b b automa

Il procedimento di automazione delle formule di calcolo prevede i seguenti passaggi: Media

Automazione effettuata a pagina 36.

Media prodotto delle variabili X Y

CELLA Q4=MEDIA(C4:N4)

Prodotto delle medie

CELLA Q5=PRODOTTO(Q2:Q3)

Indice di covarianza metodo 1

CELLA Q6=Q4-Q5

Indice di covarianza metodo 2

CELLA Q8=O8/O3

Indice di covarianza automazione

ﾏベy ﾏベy automa ﾏベy

609 609 609

CELLA Q7=COVARIANZA.P(C2:N2;C3:N3)

Scarto quadratico medio

CELLA Q10=RADQ(O10/O3)

CELLA Q11=RADQ(O11/O3)

Indice di correlazione

CELLA Q12=Q8/(Q10*Q11)

Indice di correlazione automatizzato

CELLA Q13=CORRELAZIONE(C2:N2;C3:N3)

Varianza automatizzata

CELLA Q14=VAR.P(C2:N2)

CELLA Q15=VAR.P(C3:N3)

Indice di Pearson automatizzato

CELLA Q16=PEARSON(C2:N2;C3:N3)

Distribuzione Normale automatizzata

CELLA Q17=DISTRIB.NORM.N(O17;Q2;Q10;VERO)

CELLA Q18=DISTRIB.NORM.N(O18;Q3;Q11;VERO)

CELLA Q19=NORMALIZZA(O17;Q2;Q10)

CELLA Q20=NORMALIZZA(O18;Q3;Q11)

Coefficiente angolare CELLA Q22=Q7/Q14

Coefficiente angolare automatizzato

CELLA Q23=PENDENZA(C3:N3;C2:N2)

Intercetta

CELLA Q24=Q3-(Q22*Q2)

Intercetta automatizzata

CELLA Q25=INTERCETTA(C3:N3;C2:N2)

Obiettivo n.4:costruzione strumenti di calcolo

X Y

265,50 N auto ma 72,92 N auto ma No rmauto ma No rmauto ma

0,50 0,50 0,00 0,00

1.Visualizzazione della distribuzione Normale per la media e la deviazione standard specificate

Scheda Inserisci > Grafici > Grafico a linee > Selezione dati

Grafico Funzione Normalizza

Scheda Inserisci > Grafici > Grafico a linee > Selezione dati

Grafico Funzione Normalizza

2. Visualizzazione della variabile Y determinato il valore di X

Y=aX+b coef.angolare coef.angolare intercetta intercetta

X Y

a a auto ma b b auto ma

0,21 0,21 16 16

COMPUTO 265,50 72,92

CELLA O29=(Q22*O28)+Q24

Il grafico dimostra la correlazione tra le due variabili, nel caso opposto i punti risulterebbero distanziati dalla linea di tendenza.

Obiettivo n.5: visualizzazione della casistica Perfetta correlazione positiva Visualizzare il modello di correlazione tra i seguenti prodotti in riferimento ai dati di vendita mensili. Software applicativo

8740

8825

8760

8820

8825

8870

8864

8832

8875

8978

9140

9250

Smartphone

18956

19563

19213

19542

19600

19852

19754

19542

19854

20541

21540

22310

X Y

8740 18956 2E+08 x i -μx yi -μy

8825 19563 2E+08

8760 19213 2E+08

8820 19542 2E+08

8825 19600 2E+08

8870 19852 2E+08

8864 19754 2E+08

8832 19542 2E+08

8875 19854 2E+08

-158 -73 -138 -78 -1066 -459 -809 -480 168734 33640 111879 37580

-73 -422 30930

-28 -170 4810

-34 -268 9188

-66 -480 31817

-23 -168 3912

(x i -μx)² 25043 5366 19113 6123 5366 798 (yi -μy)² 1136889 210911 654886 230640 178295 28985 22500 22000

8978 20541 2E+08

9140 21540 2E+08

μx μy μ(xi*yi) μx*μy 80 242 352 σxy 519 1518 2288 σxy automa 41370 366916 804716 1645490 σxy

1173 4389 541 6360 58443 123728 71958 230640 28308 269102 2303565 5233800 (x i -μx)² automa (yi -μy)²automa

21500 21000

x 20500

8752 19083

20000

8898 20022

X Y

9044 20961

y = 6,4166x - 37074

19500 19000 18500 8700

8800

8900

9000

9100

9200

n 12 12

9250 22310 2E+08

9300

σx σy ρ ρ auto ma 10577978 σ²x automa σ²y automa χ auto ma 8898 N auto ma 20022 N auto ma No rmauto ma No rmauto ma 256442

10577978

256442,25

Y=aX+b coef.angolare coef.angolare intercetta intercetta

a a auto ma b b auto ma

8898 20022 178300110 178162986

137124 137124 137124 146 939 1,00 1,00 21370 881498 1,00 0,50 0,50 0,00 0,00 6,42 6,42 -37074 -37074

Estremo di massimo correlazione perfetta positiva. Valutazione dei dati: 1. 2. 3. 4. 5. 6. 7.

µx µy σxy σx σy ρ χ

8898 20022 137124 146 939 1.00 1.00

Il calcolo della media e della varianza ci permette di definire un range di probabile collocazione dei punti di seriazione, individuati con i tre punti grigi . La covarianza risulta elevata, l’indice di correlazione è a valore massimo.

Perfetta correlazione negativa Visualizzare il modello di correlazione tra i seguenti prodotti in riferimento ai dati di vendita mensili. Cd

1005

2015

3051

4012

5789

6879

7845

8654

9523

10235

11254

12456

Dischi

1242

1151

1047

986

860

716

651

515

439

313

217

159

X Y

5789 860 5E+06

6879 716 5E+06

7845 651 5E+06

8654 515 4E+06

-2881 295

-1104 169

-3242417 -2242331 -1366531 -848984

-186236

-14 25 -349

952 -40 -38391

-310494

(x i -μx)² 34670507 23796510 14762245 8301121 1219184 (yi -μy)² 303234 211293 126499 86828 28448

201 608

x i -μx yi -μy

1005 1242 1E+06

2015 1151 2E+06

3051 1047 3E+06

4012 986 4E+06

-5888 551

-4878 460

-3842 356

1400 1200 1000 800

3301 337

600 y

6893 691

10485 1045

400 200 0 0

2000

4000

6000

1761 -176

9523 439 4E+06

10235 313 3E+06

11254 217 2E+06

12456 159 2E+06

2630 -252

3342 -378

4361 -474

5563 -532

-663595 -1264327 -2068489 -2961282

n 12 12

μx μy μ(xi*yi) μx*μy σxy σxy automa -15193424 σxy

6893 691 3499357 4765476 -1266119 -1266119 -1266119

905987 3100534 6916023 11167850 19016867 30945115 154802144 σx 3592 1627 31093 63672 143136 224992 283379 1504811 σy 354 (x i -μx)² automa 154802144 ρ -1,00 (yi -μy)²automa 1504811 ρ auto ma -1,00 σ²x automa 12900179 σ²y automa 125401 χ auto ma -1,00 X 6893 N auto ma 0,50 Y 691 N auto ma 0,50 y = -0,0981x + 1367,9 No rmauto ma 0,00 No rmauto ma 0,00 Y=aX+b coef.angolare a -0,10 coef.angolare a auto ma -0,10 intercetta b 1368 8000 10000 12000 14000 intercetta b auto ma 1368

Estremo di massimo correlazione perfetta negativa. Valutazione dei dati: 1. 2. 3. 4. 5. 6. 7.

µx µy σxy σx σy ρ χ

6893 691 -1266119 3592 354 -1.00 -1.00

Perfetta no correlazione Visualizzare il modello di correlazione tra i seguenti prodotti in riferimento ai dati di vendita mensili. Matite

8421

8542

8475

8547

8518

8475

8486

8412

8445

8451

8401

8501

Bottiglie

1654

1545

1245

1345

1348

1258

1298

1265

1245

1421

1411

1421

X Y

8421 1654 1E+07 x i -μx yi -μy

8542 1545 1E+07

-52 69 283 174 -14652 12012

(x i -μx)² 2687 (yi -μy)² 79900

4784 30160

8475 1245 1E+07

8547 1345 1E+07

8518 1348 1E+07

8475 1258 1E+07

8486 1298 1E+07

8412 1265 1E+07

8445 1245 1E+07

8451 1421 1E+07

8401 1411 1E+07

8501 1421 1E+07

2 -126 -274

74 -26 -1953

45 -23 -1054

2 -113 -246

13 -73 -966

-61 -106 6469

-28 -126 3516

-22 50 -1084

-72 40 -2849

28 50 1399

5 15960

5501 693

2040 544

5 12844

173 5378

3701 11307

775 15960

477 2467

5160 793 1573 2467 (x i -μx)² automa (yi -μy)²automa

1800 1600

n 12 12

319 26100 179255 26099,67 179255

1400 1200 1000

800 y

8426 1249

8473 1371

X Y

8519 1494

600 400 200 0 8350

8400

8450

8500

8550

8600

μx 8473 μy 1371 μ(xi*yi) 11619105 μx*μy 11619079 σxy 27 σxy auto ma 27 σxy 27

Y=aX+b coef.angolare coef.angolare intercetta intercetta

σx σy ρ ρ auto ma σ²x automa σ²y automa χ auto ma 8473 N auto ma 1371 N auto ma No rmauto ma No rmauto ma a a auto ma b b auto ma

47 122 0,00 0,00 2175 14938 0,00 0,50 0,50 0,00 0,00 0,01 0,01 1268 1268

Valutazione dei dati: 1. 2. 3. 4. 5. 6. 7.

µx µy σxy σx σy ρ χ

8473 1371 27 47 122 0.00 0.00

Obiettivo n.6: casistica di esempio Esempio 1 Visualizzare il modello di correlazione tra la spesa pubblicitaria e l’aumento di vendita di un prodotto. Pubblicità

3500

3800

3850

6850

8560

9100

Prodotto

2423

2541

2487

2514

2528

2541

2565

2687

2695

2721

2725

2748

X Y

3500 2423 8E+06 x i -μx yi -μy

6850 2514 2E+07

6850 2528 2E+07

-3557 -3257 -3207 -207 -175 -57 -111 -84 622120 185359 355673 17343

-207 -70 14449

(x i -μx)² 12649878 (yi -μy)² 30596

3800 2541 1E+07

3850 2487 1E+07

10282711

42711

3240

12303

7042

4888

2750 2700 2650

x 2600

4956

7057

y = 0,0426x 2492 + 2297,5 2598

2550 2500 2450 2400 0

2000

8560 2565 2E+07

8560 2687 2E+07

8560 2695 2E+07

9100 2721 2E+07

9100 2725 2E+07

9100 2748 3E+07

n 12 12

μx 7057 μy 2598 μ(xi*yi) 18520543 μx*μy 18332632 -207 1503 1503 1503 2043 2043 2043 σxy 187911 -57 -33 89 97 123 127 150 σxy auto ma 187911 11763 -49485 133922 145949 251500 259674 306670 2254937 σxy 187911

42711 2260011 2260011 2260011 4175211 4175211 4175211 52972267 σx 2101 3240 1084 7936 9425 15150 16150 22525 133577 σy 106 (x i -μx)² automa 52972267 ρ 0,85 (yi -μy)²automa 133577 ρ automa 0,85 σ²x automa 4414356 σ²y automa 11131 χ auto ma 0,85 X 7057 N auto ma 0,50 9158 Y 2598 N auto ma 0,50 2703 No rmauto ma 0,00 No rmauto ma 0,00 Y=aX+b coef.angolare a 0,04 coef.angolare a auto ma 0,04 intercetta b 2298 4000 6000 8000 10000 intercetta b auto ma 2298

10605878

2800

6850 2541 2E+07

Valutazione dei dati: 1. 2. 3. 4. 5. 6. 7.

µx µy σxy σx σy ρ χ

7057 2598 187911 2101 106 0.85 0.85

Il calcolo della media e della varianza ci permette di definire un range di probabile collocazione dei punti di seriazione, individuati con i tre punti grigi . La covarianza e l’indice di correlazione presenta un valore non molto elevato. E’ probabile che modificando la seriazione distribuendone gli aumenti di spesa, l’indice risulti maggiormente elevato.

3500

3800

3850

4850

5850

6850

7560

8560

9100

9800

10300

10660

Prodotto

2423

2541

2487

2514

2528

2541

2565

2687

2695

2721

2725

2748

X Y

3500 2423 8E+06 x i -μx yi -μy

3800 2541 1E+07

3850 2487 1E+07

4850 2514 1E+07

5850 2528 1E+07

-3557 -3257 -3207 -2207 -1207 -175 -57 -111 -84 -70 622120 185359 355673 185176 84366

(x i -μx)² 12649878 (yi -μy)² 30596

10282711

3240

12303

2750 2700 2650

x 2600 y

2550 2500 2450 2400

7560 2565 2E+07

8560 2687 2E+07

9100 2695 2E+07

9800 2721 3E+07

10300 2725 3E+07

10660 2748 3E+07

n 12 12

μx 7057 μy 2598 μ(xi*yi) 18586867 μx*μy 18332632 -207 503 1503 2043 2743 3243 3603 σxy 254235 -57 -33 89 97 123 127 150 σxy auto ma 254235 11763 -16568 133922 198374 337659 412174 540800 3050817 σxy 254235

42711 253344 2260011 4175211 7525878 10519211 12984011 77624267 σx 2543 3240 1084 7936 9425 15150 16150 22525 133577 σy 106 (x i -μx)² automa 77624267 ρ 0,95 (yi -μy)²automa 133577 ρ auto ma 0,95 σ²x automa 6468689 σ²y automa 11131 χ auto ma 0,95 X 7057 N 0,50 auto ma y = 0,0393x + 2320,6 4513 7057 9600 Y 2598 N auto ma 0,50 2492 2598 2703 No rmauto ma 0,00 No rmauto ma 0,00 Y=aX+b coef.angolare a 0,04 coef.angolare a auto ma 0,04 intercetta b 2321 0 2000 4000 6000 8000 10000 12000 intercetta b auto ma 2321

10605878

2800

6850 2541 2E+07

4869378 1456044

7042

4888

Valutazione dei dati: 1. 2. 3. 4. 5. 6. 7.

µx µy σxy σx σy ρ χ

7057 2598 254235 2543 106 0.95 0.95

Distribuendo gli aumenti della variabile X nei periodi immediatamente successivi, covarianza ed indice di correlazione aumentano rispettivamente di 66234 e 0.10.

Esempio 2 Visualizzare il modello di correlazione tra la vendita di camicie e pantaloni. Camicie

254

271

245

231

215

221

235

241

354

312

251

261

Pantaloni

119

125

109

103

106

136

128

118

137

μx μy μ(xi*yi) μx*μy σxy σxy automa σxy

258 115 30035 29622 413 413 413

σx σy ρ ρ auto ma σ²x automa σ²y automa χ auto ma 258 N auto ma 115 N auto ma No rmauto ma No rmauto ma

38 14 0,79 0,79 1442 188 0,79 0,50 0,50 0,01 0,00

X Y

x i -μx yi -μy

(x i -μx)² (yi -μy)²

254 119 30226

271 125 33875

245 109 26705

231 103 23793

215 98 21070

221 106 23426

235 95 22325

241 106 25546

354 136 48144

312 128 39936

251 118 29618

261 137 35757

-4 4 -14

13 10 134

-13 -6 75

-27 -12 319

-43 -17 724

-37 -9 329

-23 -20 452

-17 -9 149

96 21 2025

54 13 707

-7 3 -20

3 22 75

13 16

180 100

158 36

707 144

1813 289

1338 81

510 400

275 81

9296 441

2961 169

250

43 12 9 484 (x i -μx)² automa (yi -μy)²automa

n 12 12

4956 17307 2250 17306,92 2250

200

X Y

150

x y 100

220 101

258 115

296 129

y = 0,5882x - 10,708

50 0 150

200

250

300

350

400

Y=aX+b coef.angolare coef.angolare intercetta intercetta

a a auto ma b b auto ma

0,29 0,29 41 41

Valutazione dei dati: 1. 2. 3. 4. 5. 6. 7.

µx µy σxy σx σy ρ χ

258 115 410 38 14 0.79 0.79

Il calcolo della media e della varianza ci permette di definire un range di probabile collocazione dei punti di seriazione, individuati con i tre punti grigi . La covarianza ed indice di correlazione presentano un valore discreto. Un azione che potrebbe aumentare la correlazione potrebbe essere quella di vendere in promozione i prodotti correlati.

Visualizzare il modello di correlazione tra la vendita di camicie e pantaloni, il negozio applica una promozione acquistando un camicia si ottiene il 20% di sconto nell’acquisto di un pantalone della stessa marca. Camicie

264

285

256

241

224

228

239

248

369

325

275

289

Pantaloni

135

148

129

128

124

129

135

139

206

187

154

165

X Y

264 135 35640

285 148 42180

256 129 33024

241 128 30848

224 124 27776

228 129 29412

239 135 32265

248 139 34472

369 206 76014

325 187 60775

275 154 42350

289 165 47685

x i -μx yi -μy

-6 -13 83

15 0 -4

-14 -19 274

-29 -20 592

-46 -24 1122

-42 -19 813

-31 -13 414

-22 -9 206

99 58 5703

55 39 2122

5 6 27

19 17 314

(x i -μx)² (yi -μy)²

39 176

218 0

203 371

856 410

2139 588

1785 371

977 176

495 86

9752 3335

2998 1502

250

23 352 33 281 (x i -μx)² automa (yi -μy)²automa

n 12 12

x y 100

230 124

270 148

311 173

50 0 150

200

250

300

350

400

σx σy ρ ρ auto ma σ²x automa σ²y automa χ auto ma 6893 N auto ma 691 N auto ma No rmauto ma No rmauto ma

41 25 0,97 0,97 1653 611 0,97 1,00 1,00 162,90 21,97

19834 7326 19834,25 7326

X Y

y = 0,5882x - 10,708

270 148 41037 40065 972 972 972

11666

200 150

μx μy μ(xi*yi) μx*μy σxy σxy auto ma σxy

Y=aX+b coef.angolare coef.angolare intercetta intercetta

a a auto ma b b auto ma

Valutazione dei dati: 1. 2. 3. 4. 5. 6. 7.

µx µy σxy σx σy ρ χ

270 148 972 41 25 0.97 0.97

La media di vendita è aumentata per entrambi i prodotti, ciò significa che la promozione ha incrementato le vendite dei due prodotti. Covarianza ed indice di correlazione risultano aumentati di 562 e 0.18.

0,59 0,59 -11 -11

Esempio 3 Visualizzare il modello di correlazione tra la vendita di due software della stessa marca. Software A

5478

5896

6542

6421

6891

7210

7325

7541

7654

7896

7898

7985

Software B

2415

2685

3125

3085

3210

3452

3125

3085

3051

2954

2874

2812

X Y

5478 2415 1E+07 x i -μx yi -μy

5896 2685 2E+07

6542 3125 2E+07

6421 3085 2E+07

6891 3210 2E+07

7210 3452 2E+07

7325 3125 2E+07

7541 3085 2E+07

-1583 -1165 -519 -640 -170 149 -574 -304 136 96 221 463 909541 354772 -70424 -61213 -37591 68732

264 136 35738

480 96 45840

7654 3051 2E+07

7896 2954 2E+07

7898 2874 2E+07

7985 2812 2E+07

n 12 12

μx 7061 μy 2989 μ(xi*yi) 21192176 μx*μy 21109517 593 835 837 924 σxy 82660 62 -35 -115 -177 σxy auto ma 82660 36493 -29558 -96556 -163859 991915 σxy 82660

(x i -μx)² 2507208 1358196 269794 410134 29042 22077 69476 230000 351155 696529 699872 853006 7496489 σx (yi -μy)² 329955 92670 18383 9136 48657 213983 18383 9136 3793 1254 13321 31477 790147 σy (x i -μx)² automa 7496489 ρ 4000 (yi -μy)²automa 790147 ρ auto ma 3500 σ²x automa σ²y automa 3000 χ auto ma 2500 X 6893 N auto ma x 6271 7061 7852 Y 691 N auto ma 2000 y 2733 2989 3246 No rmauto ma y = 0,1323x + 2055,1 1500 No rmauto ma 1000 Y=aX+b coef.angolare a 500 coef.angolare a auto ma 0 intercetta b 5000 6000 7000 8000 9000 intercetta b auto ma

790 257 0,41 0,41 624707 65846 0,41 0,42 0,00 -0,21 -8,96 0,13 0,13 2055 2055

Valutazione dei dati: 1. 2. 3. 4. 5. 6. 7.

µx µy σxy σx σy ρ χ

7061 2989 82660 790 257 0.41 0.41

La covarianza presenta un valore non elevato, indice di correlazione piuttosto basso. Dal grafico si nota una diminuizione di vendita del software B a partire dal punto [7210;3452] in controtendenza all’aumento del software A. Se separiamo l’analisi in due parti vedremo che vi correlazione positiva nei primi 6 mesi e correlazione negativa nei 6 mesi successivi.Un azione di vendita a pacchetto con riduzione di prezzo in caso di acquisto congiunto potrebbe ridurre il trend negativo.

Software A

5478

5896

6542

6421

6891

7210

Software B

2415

2685

3125

3085

3210

3452

X Y

5478 2415 1E+07 x i -μx yi -μy

5896 2685 2E+07

6542 3125 2E+07

6421 3085 2E+07

-928 -510 136 -580 -310 130 538743 158373 17591

15 90 1315

(x i -μx)² 861803 260440 (yi -μy)² 336787 96307 4000 3500 3000 2500

x y

2000 1500

18405 16813

6891 3210 2E+07

7210 3452 2E+07

n 6 6

μx 6406 μy 2995 μ(xi*yi) 19386949 μx*μy 19189104 485 804 σxy 197845 215 457 σxy auto ma 197845 104042 367008 1187072 σxy 197845

215 8040

234902 645880 2021645 σx 46082 208544 712573 σy 2021645 ρ 712573 ρ auto ma σ²x automa σ²y automa χ auto ma 6893 N auto ma 5826 y = 0,5872x 6406 - 766,35 6987 691 N auto ma 2651 2995 3340 No rmauto ma No rmauto ma

1000 500 0 5000

6000

7000

8000

Valutazione dei dati: 1. 2. 3. 4. 5. 6. 7.

µx µy σxy σx σy ρ χ

6406 2995 197845 580 345 0.99 0.99

La covarianza presenta un valore elevato, indice di correlazione elevato.

a a auto ma b b auto ma

580 345 0,99 0,99 336941 118762 0,99 0,80 0,00 0,84 -6,69 0,59 0,59 -766 -766

Software A

7325

7541

7654

7896

7898

7985

Software B

3125

3085

3051

3085

3125

3074

X Y

7325 3125 2E+07 x i -μx yi -μy

7541 3085 2E+07

7654 3051 2E+07

-392 -176 -63 142 102 68 -55397 -17813 -4219

(x i -μx)² 153272 30800 (yi -μy)² 20022 10302

3906 4556

7898 2874 2E+07

μx 7717 μy 2984 μ(xi*yi) 22997403 μx*μy 23022178 180 182 269 σxy -24774 -30 -110 -172 σxy auto ma -24774 -5295 -19874 -46048 -148647 σxy -24774

32220 870

32942 72092 325234 σx 11990 29412 77154 σy (x i -μx)² automa 325233,5 ρ (yi -μy)²automa 77154 ρ auto ma σ²x automa σ²y automa χ auto ma X 6893 N auto ma Y 691 N auto ma No rmauto ma No rmauto ma Y=aX+b coef.angolare a coef.angolare a auto ma intercetta b intercetta b auto ma

3300

3100

7484 2870

7717 2984

7949 3097

2900 y = -0,457x + 6510,3

2700

2500 7000

8000

9000

7985 2812 2E+07

n 6 6

7896 2954 2E+07

Valutazione dei dati: 1. 2. 3. 4. 5. 6. 7.

µx µy σxy σx σy ρ χ

7717 2984 -24774 233 113 -0.94 -0.94

La covarianza presenta un valore elevato, indice di correlazione elevato.

233 113 -0,94 -0,94 54206 12859 -0,94 0,00 0,00 -3,54 -20,22 -0,46 -0,46 6510 6510

Esempio 4 Visualizzare il modello di correlazione libri con stesso mercato di vendita. Libro A

654

623

647

625

684

628

712

714

689

654

652

643

Libro B

245

236

287

264

241

275

278

267

298

265

279

241

μx μy μ(xi*yi) μx*μy σxy σxy auto ma σxy

660 265 174964 174790 174 174 174

σx σy ρ ρ auto ma σ²x automa σ²y automa χ auto ma 6893 N auto ma 691 N auto ma No rmauto ma No rmauto ma

31 19 0,30 0,30 937 370 0,30 1,00 1,00 203,58 22,18

X Y

n 12 12

654 623 647 625 684 628 712 714 689 654 652 643 245 236 287 264 241 275 278 267 298 265 279 241 160230 147028 185689 165000 164844 172700 197936 190638 205322 173310 181908 154963 x i -μx yi -μy

(x i -μx)² (yi -μy)²

-6 -20 126

-37 -29 1073

-13 22 -300

-35 -1 24

24 -24 -558

-32 10 -335

52 13 688

54 2 125

29 33 953

-6 0 -2

-8 14 -121

41 387

1400 822

180 499

1254 0

556 560

1051 107

2661 178

2871 5

817 1111

41 0

71 303 205 560 (x i -μx)² automa (yi -μy)²automa

350

-17 -24 412

2085 11247 4435 11246,92 4435

300 250 200

630 245

150 y

660 265

X Y

691 284 y = 0,1854x + 142,26

100 50 0 620

640

660

680

700

720

Y=aX+b coef.angolare coef.angolare intercetta intercetta

a a auto ma b b auto ma

0,19 0,19 142 142

Valutazione dei dati: 1. 2. 3. 4. 5. 6. 7.

µx µy σxy σx σy ρ χ

661 265 176 31 19 0.30 0.30

Ad una prima valutazione potrebbe sembrare che non sussiste correlazione tra le due variabili, tuttavia è compito di chi esegue la valutazione individuare ed intuire una eventuale correlazione con dati grezzi, prima che si effettui il calcolo degli indici; potrebbe risultare fuorviante la singola valutazione, come avviene in questo caso; se rielaboriamo i dati in trimestri otterremo opposta valutazione.

Libro A

1924

1937

2115

1951

Libro B

768

780

843

785

n X

1924

1937

2115

1951

μx

768

780

843

785

μy

1477632 1510860 1782945 1531535

xi -μx

-58

-45

133

yi -μy

-26

-14

1502

627

6529

277

(xi -μx)²

3335

2003

17756

(yi -μy)²

676

196

2401

1982 794

μ(xi *yi )

1575743

μx*μy

1573510

-31

σxy

2234

-9

σxy automa

2234

8934

σxy

2234

946

24039

σx

3354

σy

24039

3354

ρ automa

0,99

σ²x

6010

1000 800

78 29 0,99

automa

σ²y automa

600

1904 765

400

1982 794

2059 823

y = 0,3716x + 57,483

0,99

6893

N automa

1,00

691

N automa

0,00

Normautoma

63,35

Normautoma

-3,56

0,37

200 a

0 1850 1900 1950 2000 2050 2100 2150

automa

µx µy σxy σx σy ρ χ

0,37

b automa

Valutazione dei dati: 1. 2. 3. 4. 5. 6. 7.

839

χ automa

1982 794 2234 78 29 0.99 0.99

In questo caso la covarianza aumenta 12.69 volte e l’indice di correlazione di 3.3 volte.

Esempio 5 Visualizzare il modello di correlazione tra il prodotto A e il prodotto B venduti in un ecommerce visibili nella stessa pagina di riferimento. Prodotto A

245

256

261

265

264

271

278

275

282

275

286

291

Prodotto B

X Y

x i -μx yi -μy

(x i -μx)² (yi -μy)²

245 15 3675

256 16 4096

261 12 3132

265 10 2650

264 6 1584

271 7 1897

278 5 1390

275 8 2200

282 3 846

275 14 3850

286 12 3432

291 13 3783

-26 5 -127

-15 6 -87

-10 2 -19

-6 0 0

-7 -4 28

0 -3 -1

7 -5 -37

4 -2 -9

11 -7 -80

4 4 17

15 2 29

20 3

663

218

95 4

33 0

46 17

0 10

53 26

18 4

127 50

18 15

18 16

233 410 4 9 (x i -μx)² automa (yi -μy)²automa

n 12 12

258 6

271 10

6 4 2

y = -0,1181x + 42,047

0 240

250

260

270

280

290

300

σx σy ρ ρ auto ma σ²x automa σ²y automa χ auto ma 6893 N auto ma 691 N auto ma No rmauto ma No rmauto ma

13 4 -0,37 -0,37 159 16 -0,37 1,00 1,00 524,59 168,09

1912 197 1912,25 197

X Y

283 14

271 10 2711 2730 -19 -19 -19

-226

14 12

μx μy μ(xi*yi) μx*μy σxy σxy auto ma σxy

Y=aX+b coef.angolare coef.angolare intercetta intercetta

a a auto ma b b auto ma

-0,12 -0,12 42 42

Valutazione dei dati: 1. 2. 3. 4. 5. 6. 7.

µx µy σxy σx σy ρ χ

272 11 -18 11 4 -0.36 -0.36

L’indice di correlazione evidenzia una correlazione discretamente negativa, la collocazione nella stessa pagina di riferimento non determina l’incremento congiunto dei due prodotti.

DEFINIZIONI

Numerosità seriazione

Variabile

µx

Media X

Sommatoria degli x valori osservati diviso n

1/n Ʃi xi

µy

Media Y

Sommatoria degli y valori osservati diviso n

1/n Ʃi yi

xi-μx

Scarti dalla media variabile X

Indica omogeità o dispersione dei valori della seriazione, elemento di calcolo per l’indice di covarianza

yi-μy

Scarti dalla media variabile Y

Indica omogeità o dispersione dei valori della seriazione, elemento di calcolo per l’indice di covarianza

(xi-μx)²

Quadrato degli scarti dalla media variabile X

Elemento di calcolo della varianza

(yi-μy)²

Quadrato degli scarti dalla media variabile Y

Elemento di calcolo della varianza

μ(xi*yi)

Media dal prodotto variabile X Y

Elemento di calcolo per l’indice di covarianza metodo secondo

Prodotto delle medie

Elemento di calcolo per l’indice di covarianza metodo secondo

μx*μy

σxy

Indice di covarianza

Indica l’eventuale convergenza o divergenza tra le variabili oggetto di analisi

1/n Ʃ (xi-μx) (yi-μy) [μ(xi*yi)](μx*μy)

σ²x

Varianza variabile X

Media dei quadrati degli scarti dalla media

1/n Ʃ(xi-μx)²

σ²y

Varianza variabile Y

Media dei quadrati degli scarti dalla media

1/n Ʃ(yi-μy)²

σx

Scarto quadratico medio variabile X

Risultato comparabile con i valori e gli indici di posizione

[1/n Ʃ(xi-μx)²]½

σy

Scarto quadratico medio variabile Y

Risultato comparabile con i valori e gli indici di posizione

[1/n Ʃ(yi-μy)²]½

Indice di correlazione

Indica il grado di correlazione tra le due variabili

σxy/ σx* σy

Indice di Pearson

Indica il grado di correlazione tra le due variabili

Ʃ (xi-μx )(yi-μy)

N X~N(µ,σ²)

Distribuzione Normale

Normalizza

[Ʃ(xi-μx)² Ʃ(yi-μy)²]½

Restituisce la distribuzione Normale per la media e la varianza indicate Valore normalizzato

Pendenza

Indica la pendenza dell’equazione Y=aX+b

σxy/ σ²x

Intercetta

Indica l’intercetta dell’equazione Y=aX+b

µy- a* µx

MODELLO FOGLIO DI LAVORO