Calcolo della Media Ponderata e Deviazione Standard

CONSIDERARE PESI E SCOSTAMENTI NEL CALCOLO DELLA MEDIA

Excel contempla varie funzioni per calcolare la media. A fianco della funzione MEDIA presente nel pulsante SOMMA AUTOMATICA , ci sono le funzioni MEDIANA, MODA e poi le funzioni per la deviazione standard ed anche le funzioni per il calcolo condizionale della media (MEDIA.SE e MEDIA.PIÙ.SE). Magari parleremo di alcune di queste funzioni in una prossima newsletter. Quello che ci preme evidenziare sono due aspetti della media puramente aritmetica rappresentata dalla funzione MEDIA, nello specifico:

• Non tiene conto di eventuali pesi dati ai singoli valori risultato.
• Non tiene conto degli scostamenti o, per meglio dire, delle oscillazioni che possono esserci tra i vari valori che concorrono alla media.

CALCOLARE LA MEDIA PONDERATA

Non esiste in Excel una funzione per calcolare la media ponderata (o pesata). Ma prima di iniziare, cosa si intende per media ponderata ed in cosa differisce dalla media aritmetica? Nella media ponderata viene assegnato ad ogni valore un coefficiente che si chiama “peso”. Questo rappresenta il maggiore o minore rilievo dato ad un valore, il che incide sul calcolo finale. Dal punto di vista matematico la media ponderata è data dal prodotto (moltiplicazione) tra la somma dei valori e la somma dei pesi diviso la somma dei pesi.

La media ponderata si può applicare anche a calcoli strettamente aziendali (ad esempio su prezzi, quantità vendute ecc.) ma il campo di applicazione più tradizionale e “didascalico” è quello relativo ad un ambiente didattico come i voti presi a determinati esami ed il peso di ogni esame. Come esempio prendiamo il prospetto seguente che mostra una serie di esami universitari sostenuti con i rispettivi voti ed i pesi, rappresentati dai singoli crediti formativi.

Per fare questo calcolo in Excel bisogna innanzitutto usare la funzione MATR.SOMMA.PRODOTTO che moltiplica la somma di tutti i valori di due o più matrici. Nel nostro caso la prima matrice è quella con i voti (B2:B16) mentre la seconda è quella con i crediti formativi (C2:C16). Il risultato di questa somma tra i vari prodotti deve essere diviso per la somma dei vari crediti formativi dell’intervallo celle C2:C16. Vediamo nell’immagine sotto l’esito della MEDIA e del calcolo della media ponderata.

• Nella cella G1 c’è la classica funzione della media applicata all’intervallo che comprende i voti presi: =MEDIA(B2:B16). Il risultato è 25,07
• Nella cella G2 la funzione presente è quella cui si è fatto cenno sopra: =MATR.SOMMA.PRODOTTO(B2:B16;C2:C16)/SOMMA(C2:C16). Analizzando nel dettaglio, MATR.SOMMA.PRODOTTO dà come risultato 3231 mentre la somma dei pesi dà come risultato 130. Dividendo 3231 per 130 si ottiene 24,85.

Come si può notare la media è più bassa (differenza di -0,21) perché evidentemente alcuni voti buoni sono stati presi in esami con credito formativo basso ed alcuni voti meno buoni in esami con credito formativo alto.

TENERE CONTO DEGLI SCOSTAMENTI TRA VALORI NEL CALCOLO DELLA MEDIA

Per quanto concerne gli scostamenti tra i valori, la funzione MEDIA non li tiene in considerazione. Facciamo un esempio. La media dei valori 1001 ed 1 è 501. Allo stesso modo, la media dei valori 500 e 502 è 501, ma nel primo caso i valori hanno una scala diversa tra di loro mentre nel secondo sono più uniformi. In questo caso non serve fare ricorso a funzioni terze ma esiste una funzione appropriata in Excel che si chiama DEV.ST.C. Prendiamo il caso della figura sottostante

Come si può notare, la media presente nelle celle J10 e K10 è perfettamente uguale: 25,875. Ma i valori nell’intervallo celle J2:J9 sono più altalenanti, passando da 18 a 30 mentre nell’intervallo celle K2:K9 sono più vicini tra di loro, passando da 25 a 27.

La funzione che è presente nella cella J11 e poi estesa a destra è =DEV.ST.C(J2:J9). Il risultato è 5,11 mentre per le celle della colonna K è 0,99. Più il valore è alto più l’oscillazione tra i valori che determinano la media è ampia.

In sostanza, la funzione ci dice che l’esito finale della media è lo stesso ma che nel primo caso ci si è arrivati attraverso un percorso fatto di alti e bassi, mentre nel secondo caso ci si è arrivati con un percorso più lineare. Lo scarto minore tra i valori rende la media più affidabile e fornisce una rappresentazione dei dati maggiormente attendibile.

Segui ML Training su:   Facebook – LinkedIn