=MATR.SOMMA.PRODOTTO(B2:B5;C2:C5)/SOMMA(C2:C5) calcola una media ponderata: ogni punteggio in B viene moltiplicato per il suo peso in C, i prodotti vengono sommati e il totale viene diviso per la somma dei pesi. MATR.SOMMA.PRODOTTO si chiama SUMPRODUCT in Excel inglese e SOMMA si chiama SUM, ed è così che la tabella mostra le formule; puoi però scriverle anche in italiano, con il punto e virgola: =MATR.SOMMA.PRODOTTO(B2:B5;C2:C5)/SOMMA(C2:C5).
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Part | Score | Weight | Average | Result | |
| 2 | Homework | 85 | 20% | Weighted | 81.2 | |
| 3 | Quizzes | 78 | 30% | Plain AVERAGE | 80.75 | |
| 4 | Midterm | 72 | 20% | |||
| 5 | Final | 88 | 30% |
=MATR.SOMMA.PRODOTTO(B2:B5;C2:C5)/SOMMA(C2:C5)Il voto ponderato è 81,2, mentre una MEDIA semplice (in inglese AVERAGE) dà 80,75, perché tratta i compiti, che valgono il 20%, come se contassero quanto l'esame finale, che vale il 30%. Cambia il punteggio del finale e il voto ponderato si sposta più di quanto farebbe con la stessa modifica sui compiti.
Excel non ha una funzione MEDIA.PONDERATA, quindi MATR.SOMMA.PRODOTTO diviso per SOMMA è la formula standard. Google Sheets ha AVERAGE.WEIGHTED(B2:B5,C2:C5).
Come funziona la formula della media ponderata
MATR.SOMMA.PRODOTTO moltiplica i due intervalli riga per riga e somma i risultati. Scritto con una colonna di appoggio, è una colonna di prodotti e la loro SOMMA:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Part | Score | Weight | Score x weight |
| 2 | Homework | 85 | 20% | 17.0 |
| 3 | Quizzes | 78 | 30% | 23.4 |
| 4 | Midterm | 72 | 20% | 14.4 |
| 5 | Final | 88 | 30% | 26.4 |
| 6 | Total | 100% | 81.2 | |
| 7 | Weighted average | 81.2 |
=SOMMA(D2:D5)Ogni parte contribuisce con il suo punteggio per il suo peso: 85 × 20% fa 17,0, 78 × 30% fa 23,4, e così via. Sommati danno 81,2. I pesi sommano a 100%, quindi qui dividere per C6 non cambia nulla, ma è ciò che tiene giusta la formula quando non sommano a 100%.
Pesi che non sommano a 100%
I pesi non devono essere percentuali. Una media dei voti si pondera per i crediti, un prezzo medio per la quantità. Dividere per la SOMMA dei pesi gestisce qualsiasi totale.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Course | Grade points | Credits | Average | Result | |
| 2 | Math | 4 | 4 | Weighted GPA | 3.51 | |
| 3 | History | 3 | 3 | Plain average | 3.48 | |
| 4 | Biology | 3.7 | 4 | Total credits | 14 | |
| 5 | Art | 2.7 | 2 | |||
| 6 | Lab | 4 | 1 |
=MATR.SOMMA.PRODOTTO(B2:B6;C2:C6)/SOMMA(C2:C6)Senza la divisione la formula restituirebbe la somma dei punti voto per i crediti, qui 49,2, non una media. Con la divisione, F2 mostra la media ponderata su 14 crediti. I corsi da quattro crediti spostano la media verso i loro voti, e il laboratorio da un credito la muove appena: cambia B6 in 2 e guarda quanto poco cambia F2 rispetto a F3.
Se i tuoi pesi sono percentuali che sommano esattamente a 100%, =MATR.SOMMA.PRODOTTO(B2:B5;C2:C5) da sola dà lo stesso risultato. Tieni comunque il /SOMMA(...): il giorno in cui un peso cambia e il totale diventa 105%, la formula senza è sbagliata e niente nel foglio lo segnala.
Media ponderata con una condizione
Per ponderare solo alcune righe, moltiplica per una condizione dentro MATR.SOMMA.PRODOTTO e somma i pesi corrispondenti con SOMMA.SE. Qui sotto, il prezzo medio di ogni regione è ponderato per la quantità venduta.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Price | Qty | Region | Average price | |
| 2 | North | Apple | $1.20 | 100 | North | $1.45 | |
| 3 | South | Pear | $1.50 | 40 | South | $1.36 | |
| 4 | North | Pear | $1.50 | 60 | |||
| 5 | South | Apple | $1.20 | 120 | |||
| 6 | North | Plum | $2.00 | 40 | |||
| 7 | South | Plum | $2.00 | 20 |
=MATR.SOMMA.PRODOTTO((A2:A7=F2)*C2:C7*D2:D7)/SOMMA.SE(A2:A7;F2;D2:D7)North ha venduto 100 mele, 60 pere e 40 prugne, quindi il suo prezzo medio è $1.45, più vicino al prezzo delle mele di quanto sarebbe una media semplice dei tre prezzi. La condizione (A2:A7=F2) vale 1 sulle righe North e 0 altrove, quindi le altre righe non aggiungono nulla al numeratore, e SOMMA.SE somma solo le quantità North al denominatore.
Esercizio: prezzo medio ponderato
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Batch | Price | Qty | Average | Result | |
| 2 | Jan | $4.20 | 100 | Weighted price | ||
| 3 | Feb | $4.50 | 40 | |||
| 4 | Mar | $3.90 | 250 | |||
| 5 | Apr | $4.80 | 10 | |||
| 6 | May | $4.10 | 120 |
Tocca a te: Hai comprato lo stesso articolo in cinque lotti a prezzi diversi. Calcola il prezzo medio per unità, ponderato per la quantità di ogni lotto. Scrivi la formula in F2.
Errori che danno una media ponderata sbagliata
- MEDIA dei prodotti.
=MEDIA(D2:D5)su una colonna di punteggio × peso divide per il numero di righe, non per i pesi, e dà un numero piccolo e senza senso. Dividi la SOMMA dei prodotti per la SOMMA dei pesi. - Dividere per il conteggio invece che per i pesi.
=MATR.SOMMA.PRODOTTO(B2:B6;C2:C6)/CONTA.NUMERI(B2:B6)è giusta solo quando ogni peso è 1. - Intervalli non allineati.
=MATR.SOMMA.PRODOTTO(B2:B6;C3:C7)abbina ogni valore al peso della riga successiva. Entrambi gli intervalli devono iniziare e finire sulle stesse righe; dimensioni diverse restituiscono#VALORE!(in inglese #VALUE!, il nome che mostra la tabella). - Un peso vuoto. Un peso vuoto conta come 0, quindi quella riga viene esclusa senza avvisi. Se un peso mancante deve fermare il calcolo, controlla prima con
=CONTA.VUOTE(C2:C6). - Fare la media delle medie. Due medie di classe di 70 (10 studenti) e 90 (30 studenti) non danno 80 come media. Ponderale per il numero di studenti e il risultato è 85; la pagina su MEDIA.SE ha la stessa trappola con le condizioni.
Domande frequenti
Come si calcola una media ponderata in Excel?
Usa =MATR.SOMMA.PRODOTTO(B2:B5;C2:C5)/SOMMA(C2:C5), con i valori in B e i pesi in C. MATR.SOMMA.PRODOTTO moltiplica ogni valore per il suo peso e somma i risultati; dividere per la somma dei pesi lo trasforma in una media.
I pesi devono sommare a 100%?
No, purché tu divida per la SOMMA dei pesi. Crediti di 3, 4, 2 e 1, o pesi di 2, 1 e 1, funzionano allo stesso modo. Solo la scorciatoia =MATR.SOMMA.PRODOTTO(B2:B5;C2:C5) senza la divisione richiede pesi che sommino esattamente a 100%.
Esiste una funzione MEDIA.PONDERATA in Excel?
No. Excel non ha una funzione predefinita per la media ponderata, quindi la combinazione di MATR.SOMMA.PRODOTTO e SOMMA è la formula standard. In Google Sheets, AVERAGE.WEIGHTED(B2:B5,C2:C5) fa lo stesso.
Come si calcola una media ponderata con una condizione?
Aggiungi la condizione a MATR.SOMMA.PRODOTTO e usa SOMMA.SE per i pesi: =MATR.SOMMA.PRODOTTO((A2:A7="North")*B2:B7*C2:C7)/SOMMA.SE(A2:A7;"North";C2:C7).
