Come calcolare la media ponderata in Excel
Il tutorial dimostra due semplici modi per calcolare la media ponderata in Excel – utilizzando la funzione SOMMA 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 dei valori hanno più “peso” di altri e di conseguenza contribuiscono di più alla media finale? In tali situazioni, è necessario calcolare la media ponderata.,
Sebbene Microsoft Excel non fornisca una funzione media ponderata speciale, ha un paio di altre funzioni che si riveleranno utili nei calcoli, come dimostrato negli esempi di formula che seguono.
- Qual è la media ponderata?
- Come calcolare la media ponderata in Excel
- Formula di SOMMA per la media ponderata
- Formula SUMPRODUCT per la media ponderata
Che cos’è la media ponderata?
La media ponderata è una sorta di media aritmetica in cui alcuni elementi del set di dati hanno più importanza di altri., In altre parole, ad ogni valore da mediare viene assegnato un certo peso.
I voti degli studenti sono spesso calcolati utilizzando una media ponderata, come mostrato nella schermata seguente. Una media normale è facilmente calcolata con la funzione MEDIA di Excel. Tuttavia, vogliamo che la formula media consideri il peso di ogni attività elencata nella colonna C.
In matematica e statistica, si calcola la media ponderata moltiplicando ogni valore nel set per il suo peso, quindi si sommano i prodotti e si dividono i prodotti” somma per la somma di tutti i pesi.,
In questo esempio, al fine di calcolare la media ponderata (giudizio complessivo), si moltiplica ogni grado corrispondente percentuale (convertito in decimale), aggiungere fino a 5 prodotti insieme e dividere 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 si può vedere, un normale voto medio (il 75,4) 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 molto meno sforzo perché le funzioni di Excel faranno la maggior parte del lavoro per te.
Esempio 1. Calcolo della media ponderata utilizzando la funzione SUM
Se si dispone di una conoscenza di base della funzione SUM di Excel, la seguente formula difficilmente richiederà alcuna spiegazione:
=SUM(B2*C2, B3*C3, B4*C4, B5*C5, B6*C6,)/SUM(C2:C6)
In sostanza, esegue lo stesso calcolo descritto sopra, tranne che si forniscono riferimenti di celle anziché numeri.,
Come puoi vedere nello screenshot, la formula restituisce esattamente lo stesso risultato del calcolo che abbiamo fatto un momento fa. Si noti la differenza tra la media normale restituita dalla funzione MEDIA (C8) e la media ponderata (C9).
Sebbene la formula di SOMMA sia molto semplice e facile da capire, non è un’opzione praticabile se si dispone di un numero elevato di elementi nella media. In questo caso, è meglio utilizzare la funzione SUMPRODUCT come dimostrato nel prossimo esempio.
Esempio 2., Trovare la 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., Così, invece di moltiplicare ogni valore per il suo peso singolarmente, è la fornitura di due matrici in SUMPRODUCT formula (in questo contesto, un array è un intervallo continuo di cellule), e poi dividere il risultato per la somma dei pesi:
Supponendo che i valori medi sono nelle celle B2:B6 e pesi nelle celle C2:C6, il nostro Sumproduct Medio Ponderato di formula assume la seguente forma:
=SUMPRODUCT(B2:B6, C2:C6) / SUM(C2:C6)
Per vedere i valori effettivi dietro un array, selezionare nella barra della formula e premere il tasto F9., Il risultato sarà simile a questo:
Così, ciò che il SUMPRODUCT funzione non è moltiplicare il 1 ° valore in array1 dal 1 ° valore in array2 (91*0.1 in questo esempio), quindi moltiplicare il 2 ° valore in array1 il 2 ° valore in array2 (65*0.15 in questo esempio), e così via. Quando tutte le moltiplicazioni sono fatte, la funzione aggiunge i prodotti e restituisce quella somma.,
Per assicurarsi che la funzione SUMPRODUCT produca un risultato corretto, confrontalo 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, è possibile creare una scala di priorità / importanza e assegnare un certo numero di punti a ciascun elemento, come dimostrato nella seguente schermata:
Bene, questo è tutto sul calcolo della media ponderata in Excel. Puoi scaricare il foglio di calcolo medio ponderato e provare le formule sui tuoi dati. Nel prossimo tutorial, ci accingiamo ad avere uno sguardo da vicino al calcolo media mobile. Vi ringrazio per la lettura e non vedo l’ora di vedervi la prossima settimana!,
- Come media in Excel (MEDIA, AVERAGEIF, AVERAGEIFS)
- Come calcolare una media mobile in Excel
- Come calcolare il CAGR (compound annual growth rate) in Excel – esempi di formule
- Media, la mediana e la modalità in Excel
- Excel funzione SUBTOTALE con la formula esempi