Come calcolare la media ponderata in Excel

Il tutorial mostra due semplici modi per calcolare la media ponderata in Excel, utilizzando la funzione SUM o SUMPRODUCT.

In uno degli articoli precedenti, abbiamo discusso tre funzioni essenziali per il calcolo della media in Excel, che sono molto semplici e facili da usare. Ma cosa succede se alcuni valori hanno più "peso" di altri e di conseguenza contribuiscono maggiormente alla media finale? In tali situazioni, è necessario calcolare il media ponderata.

Sebbene Microsoft Excel non fornisca una funzione media ponderata speciale, ha un paio di altre funzioni che si dimostreranno utili nei calcoli, come dimostrato negli esempi di formula che seguono.

Qual è la media ponderata?

Media ponderata è una specie di media aritmetica in cui alcuni elementi dell'insieme di dati hanno più importanza di altri. In altre parole, a ciascun valore da calcolare viene assegnato un determinato peso.

I voti degli studenti vengono spesso calcolati utilizzando una media ponderata, come mostrato nella seguente schermata. Una media abituale è facilmente calcolabile con la funzione MEDIA di Excel. Tuttavia, vogliamo che la formula media consideri il peso di ciascuna attività elencata nella colonna C.
Calcolo della media ponderata in Excel

In matematica e statistica, si calcola la media ponderata moltiplicando ciascun valore nell'insieme per il suo peso, quindi si sommano i prodotti e si divide la somma dei prodotti per la somma di tutti i pesi.

In questo esempio, per calcolare la media ponderata (voto complessivo), moltiplichi ogni grado per la percentuale corrispondente (convertita in un decimale), somma i 5 prodotti insieme e dividi quel numero per la somma di 5 pesi:

((91*0.1)+(65*0.15)+(80*0.2)+(73*0.25)+(68*0.3)) / (0.1+0.15+0.2+0.25+0.3)=73.5

Come vedi, un voto medio normale (75,4) e una media ponderata (73,5) sono valori diversi.

Calcolo della media ponderata in Excel

In Microsoft Excel, la media ponderata viene calcolata utilizzando lo stesso approccio ma con uno sforzo molto minore in quanto le funzioni di Excel eseguiranno la maggior parte del lavoro per te.

Esempio 1. Calcolo della media ponderata utilizzando la funzione SUM

Se hai una conoscenza di base della funzione SUM di Excel, la formula seguente difficilmente richiederà alcuna spiegazione:

= SOMMA (B2 * C2, B3 * C3, B4 * C4, B5 * C5, B6 * C6,) / SUM (C2: C6)

In sostanza, esegue lo stesso calcolo descritto sopra, tranne per il fatto che fornisci riferimenti di cella invece di numeri.

Come puoi vedere nello screenshot, la formula restituisce esattamente lo stesso risultato del calcolo che abbiamo fatto un attimo fa. Notare la differenza tra la media normale restituita dalla funzione MEDIA (C8) e la media ponderata (C9).
Calcolare la media ponderata utilizzando la funzione SUM

Sebbene la formula SUM sia molto semplice e facile da capire, non è un'opzione valida se si ha un numero elevato di elementi nella media. In questo caso, è meglio utilizzare la funzione SUMPRODUCT come illustrato nel prossimo esempio.

Esempio 2. Determinazione della media ponderata con la funzione SUMPRODUCT

La funzione SUMPRODUCT di Excel si adatta perfettamente a questo compito poiché è progettata per sommare i prodotti, che è esattamente ciò di cui abbiamo bisogno. Quindi, invece di moltiplicare ogni valore in base al peso singolarmente, fornisci due matrici nella formula SUMPRODUCT (in questo contesto, un array è un intervallo continuo di celle) e quindi divide il risultato per la somma dei pesi:

= SUMPRODUCT (values_range, weights_range) / SUM (weights_range)

Supponendo che i valori medi si trovino nelle celle B2: B6 e pesi nelle celle C2: C6, la formula della media ponderata del nostro Sumproduct assume la seguente forma:

= SUMPRODUCT (B2: B6, C2: C6) / SUM (C2: C6)

Per visualizzare i valori effettivi dietro una matrice, selezionarla nella barra della formula e premere il tasto F9. Il risultato sarà simile a questo:
Per visualizzare i valori effettivi dietro una matrice, selezionarla nella barra della formula e premere il tasto F9.

Quindi, cosa fa la funzione SUMPRODUCT è moltiplicare il 1st valore in array1 di 1st valore in array2 (91 * 0.1 in questo esempio), quindi moltiplicare il 2ND valore in array1 dal 2ND valore in array2 (65 * 0,15 in questo esempio) e così via. Quando tutte le moltiplicazioni vengono eseguite, la funzione somma i prodotti e restituisce tale somma.
Calcolare la media ponderata utilizzando la funzione SUMPRODUCT

Per assicurarsi che la funzione SUMPRODUCT restituisca un risultato corretto, confrontarlo con la formula SUM dell'esempio precedente e vedrai che i numeri sono identici.

Quando si utilizza la funzione SUM o SUMPRODUCT per trovare la media del peso in Excel, i pesi non devono necessariamente aggiungere fino al 100%. Né hanno bisogno di essere espressi come percentuali. Ad esempio, puoi creare una scala priorità / importanza e assegnare un determinato numero di punti a ciascun elemento, come dimostrato nella seguente schermata:

Calcolo della media ponderata in base alla scala di importanza

Bene, questo è tutto sul calcolo della media ponderata in Excel. Puoi scaricare il foglio di calcolo della media ponderata e provare le formule sui tuoi dati. Nel prossimo tutorial, daremo un'occhiata da vicino al calcolo della media mobile. Vi ringrazio per la lettura e non vedo l'ora di vedervi la prossima settimana!

Potresti anche essere interessato a:

4.7
5
13
4
2
3
3
2
1
1
1