Come calcolare le medie ponderate in Excel con SUMPRODUCT
In genere, quando si calcola an media o media aritmetica, ogni numero ha uguale valore o peso. La media viene calcolata sommando un intervallo di numeri e quindi dividendo questo totale per il numero di valori nel gamma. Una media ponderata, invece, considera uno o più numeri nell'intervallo che valgono di più o hanno un peso maggiore rispetto agli altri numeri.
Le istruzioni in questo articolo si applicano a Excel 2019, 2016, 2013, 2010, 2007; Excel per Microsoft 365, Excel Online, Excel per Mac, Excel per iPad, Excel per iPhone ed Excel per Android.
Come funziona la funzione SUMPRODUCT
SUMPRODUCT moltiplica gli elementi di due o più array e poi aggiunge i prodotti per calcolare le medie ponderate. Ad esempio, alcuni voti a scuola, come gli esami intermedi e finali, di solito valgono più dei normali test o compiti. Se si utilizza la media per calcolare il voto finale di uno studente, le prove intermedie e finali avrebbero un peso maggiore.
In una situazione in cui due array con quattro elementi ciascuno vengono inseriti come argomenti per la funzione SUMPRODUCT:
- Il primo elemento di array1 viene moltiplicato per il primo elemento di array2.
- Il secondo elemento di array1 viene moltiplicato per il secondo elemento di array2.
- Il terzo elemento di array1 viene moltiplicato per il terzo elemento di array2.
- Il quarto elemento di array1 viene moltiplicato per il quarto elemento di array2.
Successivamente, i prodotti delle quattro operazioni di moltiplicazione vengono sommati e restituiti dalla funzione come risultato.
Sintassi e argomenti di SUMPRODUCT
di una funzione sintassi fa riferimento al layout della funzione e include il nome della funzione, le parentesi e gli argomenti. La sintassi per la funzione SUMPRODUCT è:
=SOMMAPRODOTTO(Array1,Array2,Array3,...Array255)
Gli argomenti per la funzione SUMPRODUCT sono:
- Matrice1: Il primo argomento dell'array (obbligatorio).
- Array2,Array3,...Array255: Array aggiuntivi (opzionali), fino a 255. Con due o più array, la funzione moltiplica gli elementi di ogni array e poi somma i risultati.
Gli elementi dell'array possono essere riferimenti di cella alla posizione dei dati nel foglio di lavoro o dei numeri separati da operatori aritmetici, come i segni più (+) o meno (-). Se si immettono numeri non separati da operatori, Excel li tratta come dati di testo.
Gli argomenti dell'array devono avere lo stesso numero di elementi in ogni array. In caso contrario, SUMPRODUCT restituisce il #VALUE! valore di errore. Se alcuni elementi dell'array non sono numeri, come dati di testo, SUMPRODUCT li tratta come zeri.
Inserisci la formula di ponderazione
L'esempio mostrato in questo articolo calcola la media ponderata per il voto finale di uno studente utilizzando la funzione SUMPRODUCT.
La funzione esegue ciò:
- Moltiplicando i vari segni per il loro fattore di peso individuale.
- Sommando insieme i prodotti di queste operazioni di moltiplicazione.
- Dividendo la suddetta somma per il totale del fattore di ponderazione 7 (1+1+2+3) per le quattro valutazioni.
Come la maggior parte delle altre funzioni in Excel, SUMPRODUCT può essere inserito in un foglio di lavoro utilizzando il Libreria delle funzioni trovato sul formule scheda. Poiché la formula di ponderazione in questo esempio utilizza SUMPRODUCT in modo non standard (il risultato della funzione è diviso per il fattore di ponderazione), la formula di ponderazione deve essere digitata in un foglio di lavoro cellula.
Per inserire la formula SUMPRODUCT per calcolare una media ponderata, aprire un foglio di lavoro vuoto, inserire i dati nelle righe 1 attraverso 6 dall'immagine sopra e segui questi passaggi:
Seleziona cella C7 per farlo Cellula attiva (questa è la posizione in cui verrà visualizzato il voto finale dello studente).
Digita la formula =SOMMA.PRODOTTO(B3:B6,C3:C6)/(1+1+2+3) nella cella. La formula viene visualizzata nella barra della formula.
premi il accederetasto sulla tastiera.
La risposta 78.6 appare nella cella C7 (la tua risposta potrebbe avere di più decimali).
La media non ponderata per gli stessi quattro punti sarebbe 76.5. Poiché lo studente ha ottenuto risultati migliori per gli esami intermedi e finali, pesare la media ha aiutato a migliorare il voto complessivo.
SUMPRODUCT Varianti della formula
Per sottolineare che i risultati della funzione SUMPRODUCT sono divisi per la somma dei pesi per ogni gruppo di valutazione, il divisore (la parte che esegue la divisione) viene inserito come:
(1+1+2+3)
La formula di ponderazione complessiva potrebbe essere semplificata inserendo il numero 7 (la somma dei pesi) come divisore. La formula sarebbe quindi:
=SOMMA.PRODOTTO(B3:B6,C3:C6)/7.
Questa scelta va bene se il numero di elementi nell'array di ponderazione è piccolo e possono essere facilmente sommati, ma diventa meno efficace all'aumentare del numero di elementi nell'array di ponderazione, rendendo la loro aggiunta più difficile.
Un'altra opzione, e probabilmente la scelta migliore, dato che utilizza riferimenti di cella anziché numeri nel totale del divisore, sarebbe quella di utilizzare il Funzione SOMMA per sommare il divisore. La formula è quindi:
=SOMMAPRODOTTO(B3:B6,C3:C6)/SOMMA(B3:B6)
Di solito è meglio inserire nelle formule riferimenti di cella piuttosto che numeri effettivi. Ciò semplifica l'aggiornamento se i dati della formula cambiano.
Ad esempio, se nell'esempio i fattori di ponderazione per Compiti viene modificato in 0,5 e Test è modificato in 1.5, le prime due forme della formula devono essere modificate manualmente per correggere il divisore.
Nella terza variante, solo i dati nelle celle B3 e B4 devono essere aggiornati e la formula ricalcola il risultato.