Formula generica
=SUMPRODUCT(weights,values)/SUM(weights)
Sommario
Per calcolare una media ponderata, è possibile utilizzare la funzione SUMPRODUCT insieme alla funzione SUM. Nell'esempio mostrato, la formula in G5, copiata verso il basso, è:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
dove i pesi sono l'intervallo denominato I5: K5.
Spiegazione
Una media ponderata, chiamata anche media ponderata, è una media in cui alcuni valori contano più di altri. In altre parole, alcuni valori hanno più "peso". Possiamo calcolare una media ponderata moltiplicando i valori da mediare per i pesi corrispondenti, quindi dividendo la somma dei risultati per la somma dei pesi. In Excel, questo può essere rappresentato con la formula generica di seguito, dove pesi e valori sono intervalli di celle:
=SUMPRODUCT(weights,values)/SUM(weights)
Nel foglio di lavoro mostrato, i punteggi per 3 test vengono visualizzati nelle colonne da C a E ei pesi sono nei pesi dell'intervallo denominato (I5: K5). La formula nella cella G5 è:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
Lavorando dall'interno verso l'esterno, utilizziamo prima la funzione SUMPRODUCT per moltiplicare i pesi per i punteggi corrispondenti e sommare il risultato:
=SUMPRODUCT(weights,C5:E5) // returns 88.25
SUMPRODUCT moltiplica prima gli elementi corrispondenti dei due array, quindi restituisce la somma del prodotto:
=SUMPRODUCT((0.25,0.25,0.5),(90,83,90)) =SUMPRODUCT((22.5,20.75,45)) =88.25
Il risultato viene quindi diviso per la somma dei pesi:
=88.25/SUM(weights) =88.25/SUM((0.25,0.25,0.5)) =88.25/1 =88.25
Poiché la formula viene copiata nella colonna G, l'intervallo denominato pesa I5: K5 non cambia, poiché si comporta come un riferimento assoluto. Tuttavia, i punteggi in C5: E5, inseriti come riferimento relativo, si aggiornano in ogni nuova riga. Il risultato è una media ponderata per ogni nome nell'elenco come mostrato. La media nella colonna F viene calcolata solo per riferimento con la funzione MEDIA:
=AVERAGE(C5:E5)
Pesi che non sommano a 1
In questo esempio, i pesi sono configurati per aggiungere fino a 1, quindi il divisore è sempre 1 e il risultato è il valore restituito da SUMPRODUCT. Tuttavia, una bella caratteristica della formula è che i pesi non devono essere aggiunti fino a 1.
Ad esempio, potremmo usare un peso di 1 per le prime due prove e un peso di 2 per la finale (poiché la finale è due volte più importante) e la media pesata sarà la stessa:
Nella cella G5, la formula è risolta in questo modo:
=SUMPRODUCT(weights,C5:E5)/SUM(weights) =SUMPRODUCT((1,1,2),(90,83,90))/SUM(1,1,2) =SUMPRODUCT((90,83,180))/SUM(1,1,2) =353/4 =88.25
Nota: i valori tra parentesi graffe () sopra sono intervalli espressi come array.
Trasposizione di pesi
La funzione SUMPRODUCT richiede che le dimensioni dell'array siano compatibili. Se le dimensioni non sono compatibili, SUMPRODUCT restituirà un errore #VALUE. Nell'esempio seguente, i pesi sono gli stessi dell'esempio originale, ma sono elencati in un intervallo verticale:
Per calcolare una media ponderata con la stessa formula, dobbiamo "capovolgere" i pesi in una matrice orizzontale con la funzione TRANSPOSE in questo modo:
=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)
Dopo l'esecuzione di TRANSPOSE, l'array verticale:
=TRANSPOSE((0.25;0.25;0.5)) // vertical array
diventa:
=(0.25,0.25,0.5) // horizontal array
E da questo punto la formula si comporta come prima.
Per saperne di più: array verticali e orizzontali.
