Quando calcoliamo una media semplice di un dato insieme di valori, l'assunzione è che tutti i valori abbiano lo stesso peso o importanza.
Ad esempio, se ti presenti per gli esami e tutti gli esami hanno un peso simile, la media dei tuoi voti totali sarà anche la media ponderata dei tuoi punteggi.
Tuttavia, nella vita reale, questo non è certo il caso.
Alcuni compiti sono sempre più importanti di altri. Alcuni esami sono più importanti di altri.
Ed è lì che Media ponderata entra in scena.
Ecco la definizione da manuale di media ponderata:
Vediamo ora come calcolare la media ponderata in Excel.
Calcolo della media ponderata in Excel
In questo tutorial imparerai come calcolare la media ponderata in Excel:
- Utilizzo della funzione SUMPRODOTTO.
- Utilizzo della funzione SOMMA.
Quindi iniziamo.
Calcolo della media ponderata in Excel - Funzione SUMPRODUCT
Potrebbero esserci vari scenari in cui è necessario calcolare la media ponderata. Di seguito sono riportate tre diverse situazioni in cui è possibile utilizzare la funzione SUMPRODUCT per calcolare la media ponderata in Excel
Di seguito sono riportate tre diverse situazioni in cui è possibile utilizzare la funzione SUMPRODUCT per calcolare la media ponderata in Excel
Esempio 1 - Quando i pesi si sommano fino al 100%
Supponiamo di avere un set di dati con i voti ottenuti da uno studente in diversi esami insieme ai pesi in percentuale (come mostrato di seguito):
Nei dati di cui sopra, uno studente ottiene voti in diverse valutazioni, ma alla fine deve ricevere un punteggio o un voto finale. Una media semplice non può essere calcolata qui poiché l'importanza delle diverse valutazioni varia.
Ad esempio, un quiz con un peso del 10% ha il doppio del peso rispetto a un compito, ma un quarto del peso rispetto all'esame.
In tal caso, puoi utilizzare la funzione SUMPRODUCT per ottenere la media ponderata del punteggio.
Ecco la formula che ti darà la media ponderata in Excel:
=SOMMA.PRODOTTO(B2:B8,C2:C8)
Ecco come funziona questa formula: La funzione SUMPRODUCT di Excel moltiplica il primo elemento della prima matrice con il primo elemento della seconda matrice. Quindi moltiplica il secondo elemento del primo array con il secondo elemento del secondo array. E così via…
E infine, aggiunge tutti questi valori.
Ecco un'illustrazione per chiarire.
Esempio 2 - Quando i pesi non raggiungono il 100%
Nel caso di cui sopra, i pesi sono stati assegnati in modo tale che il totale sommato raggiungesse il 100%. Ma negli scenari della vita reale, potrebbe non essere sempre così.
Diamo un'occhiata allo stesso esempio con pesi diversi.
Nel caso di cui sopra, i pesi si sommano al 200%.
Se uso la stessa formula SUMPRODUCT, mi darà il risultato sbagliato.
Nel risultato sopra, ho raddoppiato tutti i pesi e restituisce il valore medio ponderato come 153,2. Ora sappiamo che uno studente non può ottenere più di 100 su 100, non importa quanto sia brillante.
Il motivo è che i pesi non raggiungono il 100%.
Ecco la formula che otterrà questo ordinato:
=SOMMA.PRODOTTO(B2:B8,C2:C8)/SOMMA(C2:C8)
Nella formula sopra, il risultato SUMPRODUCT è diviso per la somma di tutti i pesi. Quindi, non importa cosa, i pesi si sommano sempre fino al 100%.
Esempio 3 - Quando è necessario calcolare i pesi
Nell'esempio fin qui trattato, sono stati specificati i pesi. Tuttavia, potrebbero esserci casi in cui i pesi non sono direttamente disponibili e devi prima calcolare i pesi e poi calcolare la media ponderata.
Supponiamo che tu stia vendendo tre diversi tipi di prodotti come indicato di seguito:
Puoi calcolare il prezzo medio ponderato per prodotto utilizzando la funzione SUMPRODUCT. Ecco la formula che puoi usare:
=SOMMAPRODOTTO(B2:B4,C2:C4)/SOMMA(B2:B4)
Dividendo il risultato SUMPRODUCT con la SOMMA delle quantità si assicura che i pesi (in questo caso le quantità) si sommino fino al 100%.
Calcolo della media ponderata in Excel - Funzione SOMMA
Sebbene la funzione SUMPRODUCT sia il modo migliore per calcolare la media ponderata in Excel, puoi anche utilizzare la funzione SUM.
Per calcolare la media ponderata utilizzando la funzione SOMMA, è necessario moltiplicare ciascun elemento, con la sua importanza in percentuale assegnata.
Utilizzando lo stesso set di dati:
Ecco la formula che ti darà il giusto risultato:
=SOMMA(B2*C2,B3*C3,B4*C4,B5*C5,B6*C6,B7*C7,B8*C8)
Questo metodo va bene quando hai un paio di oggetti. Ma quando hai molti articoli e pesi, questo metodo potrebbe essere ingombrante e soggetto a errori. C'è un modo più breve e migliore per farlo usando la funzione SOMMA.
Continuando con lo stesso set di dati, ecco la breve formula che ti darà la media ponderata utilizzando la funzione SOMMA:
=SOMMA(B2:B8*C2:C8)
Il trucco durante l'utilizzo di questa formula consiste nell'utilizzare Ctrl + Maiusc + Invio, invece di utilizzare semplicemente Invio. Poiché la funzione SOMMA non è in grado di gestire gli array, è necessario utilizzare Ctrl + Maiusc + Invio.
Quando premi Ctrl + Maiusc + Invio, vedresti apparire automaticamente le parentesi graffe all'inizio e alla fine della formula (vedi la barra della formula nell'immagine sopra).
Ancora una volta, assicurati che i pesi si sommino fino al 100%. In caso contrario, è necessario dividere il risultato per la somma dei pesi (come mostrato di seguito, prendendo l'esempio del prodotto):
