Excel ha reso il calcolo della media di più celle un compito molto semplice: basta usare la funzione MEDIA (MEDIA). Ma cosa succede se alcuni valori hanno più peso di altri? Ad esempio, in molti corsi, i test hanno più peso dei compiti. Per tali casi, è necessario calcolare media ponderata.
Excel non ha una funzione per calcolare la media ponderata, ma c'è una funzione che farà la maggior parte del lavoro per te: MATR.SOMMA.PRODOTTO (SOMMA PRODOTTO). E anche se non hai mai utilizzato questa funzione prima, entro la fine di questo articolo la utilizzerai come un professionista. Il metodo che utilizziamo funziona in qualsiasi versione di Excel e in altri fogli di calcolo come Fogli Google.
Prepariamo la tavola
Se intendi calcolare una media ponderata, avrai bisogno di almeno due colonne. La prima colonna (colonna B nel nostro esempio) contiene i punteggi di ogni compito o test. La seconda colonna (colonna C) contiene i pesi. Più peso significa maggiore influenza del compito o del test sul voto finale.
Per capire cos'è il peso, puoi pensarlo come una percentuale del tuo voto finale. In realtà, non è così, poiché in questo caso i pesi dovrebbero sommarsi fino al 100%. La formula che analizzeremo in questa lezione calcolerà tutto correttamente e non dipende dall'importo a cui si sommano i pesi.
Entriamo nella formula
Ora che la nostra tabella è pronta, aggiungiamo la formula alla cella B10 (Va bene qualsiasi cella vuota). Come con qualsiasi altra formula in Excel, iniziamo con un segno di uguale (=).
La prima parte della nostra formula è la funzione MATR.SOMMA.PRODOTTO (SOMMA PRODOTTO). Gli argomenti devono essere racchiusi tra parentesi, quindi li apriamo:
=СУММПРОИЗВ(
=SUMPRODUCT(
Quindi, aggiungi gli argomenti della funzione. MATR.SOMMA.PRODOTTO (SUMPRODUCT) può avere più argomenti, ma di solito ne vengono usati due. Nel nostro esempio, il primo argomento sarà un intervallo di celle. SI2: SI9A che contiene i punteggi.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
Il secondo argomento sarà un intervallo di celle DO2: DO9, che contiene i pesi. Questi argomenti devono essere separati da un punto e virgola (virgola). Quando tutto è pronto, chiudi le parentesi:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
Ora aggiungiamo la seconda parte della nostra formula, che dividerà il risultato calcolato dalla funzione MATR.SOMMA.PRODOTTO (SUMPRODUCT) dalla somma dei pesi. Discuteremo più avanti perché questo è importante.
Per eseguire l'operazione di divisione, continuiamo la formula già inserita con il simbolo / (barra diretta), quindi scrivere la funzione SUM (SOMMA):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
Per la funzione SUM (SOMMA) specificheremo solo un argomento: un intervallo di celle DO2: DO9. Non dimenticare di chiudere le parentesi dopo aver inserito l'argomento:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
Pronto! Dopo aver premuto il tasto entrare, Excel calcolerà la media ponderata. Nel nostro esempio, il risultato finale sarà 83,6.
Come funziona
Analizziamo ogni parte della formula, iniziando con la funzione MATR.SOMMA.PRODOTTO (SUMPRODUCT) per capire come funziona. Funzione MATR.SOMMA.PRODOTTO (SUMPRODUCT) calcola il prodotto del punteggio di ogni articolo e il suo peso, quindi somma tutti i prodotti risultanti. In altre parole, la funzione trova la somma dei prodotti, da cui il nome. Quindi per Compiti 1 moltiplicare 85 per 5, e per Il test moltiplica 83 per 25.
Se ti stai chiedendo perché dobbiamo moltiplicare i valori nella prima parte, immagina che maggiore è il peso del compito, più volte dobbiamo considerare il voto per esso. Per esempio, Attività 2 contato 5 volte e Esame finale – 45 volte. Ecco perchè Esame finale ha un impatto maggiore sul voto finale.
Per confronto, quando si calcola la normale media aritmetica, ogni valore viene preso in considerazione una sola volta, ovvero tutti i valori hanno lo stesso peso.
Se potessi guardare sotto il cofano di una funzione MATR.SOMMA.PRODOTTO (SUMPRODUCT), abbiamo visto che in effetti lei crede questo:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
Fortunatamente, non abbiamo bisogno di scrivere una formula così lunga perché MATR.SOMMA.PRODOTTO (SUMPRODUCT) fa tutto questo automaticamente.
Una funzione in sé MATR.SOMMA.PRODOTTO (SUMPRODUCT) ci restituisce un numero enorme − 10450. A questo punto entra in gioco la seconda parte della formula: /SOMMA(C2:C9) or /SOMMA(C2:C9), che riporta il risultato nell'intervallo normale dei punteggi, fornendo la risposta 83,6.
La seconda parte della formula è molto importante perché permette di correggere automaticamente i calcoli. Ricordi che i pesi non devono aggiungere fino al 100%? Tutto questo grazie alla seconda parte della formula. Ad esempio, se aumentiamo uno o più valori di peso, la seconda parte della formula si dividerà semplicemente per il valore maggiore, ottenendo nuovamente la risposta corretta. Oppure possiamo rendere i pesi molto più piccoli, ad esempio specificando valori come 0,5, 2,5, 3 or 4,5e la formula continuerà a funzionare correttamente. È fantastico, vero?