Lezione-online fornisce corsi di formazione e-learning a più di 60mila studenti.

Fb. Ig. In. Yt.

La media ponderata su Excel

Uno dei calcoli che più frequentemente capita di dover eseguire in un file di Excel è sicuramente la media aritmetica. Questo compito non dovrebbe nascondere nessuna difficoltà, a patto ovviamente che si utilizzi la formula corretta.

Se ad esempio vogliamo calcolare la media di una serie di valori, ci dobbiamo posizionare in una cella vuota e scegliere di usare la funzione MEDIA, dopodiché selezionare le celle da calcolare.

Excel ci restituirà il risultato corretto della media algebrica. Guardando l’esempio mostrato nell’’immagine possiamo notare una caratteristica molto importante.

Grafico Excel con esempio della formula della Media.
La formula della Media inserita nelle celle gialle.

La sintassi della formula che abbiamo inserito nella cella G2 è =MEDIA(B2:E2). Viene quindi eseguito il calcolo che restituisce come risultato 28.980,25 €. La formula viene ovviamente trascinata fino alla cella G12.

In pratica è come sommare l’incasso dei negozi per i quattro trimestri e poi dividere per quattro.

Ma la MEDIA è per Excel una formula di tipo statistico e di conseguenza ignora completamente le celle vuote dell’intervallo selezionato.

Si può infatti notare che nelle celle B5, B7 e B9 il risultato delle media non è pari al valore totale diviso quattro, bensì al totale diviso tre, dato che in ognuno degli intervalli c’è una cella vuota.

Se osservi la riga 3, all’altezza della colonna del 4° trimestre il valore è pari a zero, anche se il formato Contabilità usato nella tabella visualizza un trattino: in questo caso la cella viene considerata nel calcolo della media che quindi, in parole povere, divide il totale per quattro.

È quindi sconveniente per calcolare una formula come quella riportata nella prima immagine scrivere =SOMMA(B2:E2)/4, perché le celle vuote e le celle pari a zero verrebbero calcolate allo stesso modo.

In Excel invece la cella vuota ha come significato quello di dato mancante, mentre una cella con al suo interno il valore zero è pari esattamente a zero. Bisogna quindi prestare molta attenzione a come si compilano i dati.

Per completezza di informazione segnalo che le celle che hanno un contenuto testuale vengono anche loro ignorate dalla MEDIA, esattamente come se fossero vuote.

Lo stesso comportamento lo hanno anche le funzioni MAX e MIN, che vediamo nelle colonne H e I della prima immagine, e che servono per calcolare rispettivamente il valore più alto e più basso di un intervallo di celle.

La funzioneMEDIA si può trovare rapidamente nel menu a tendina del pulsante Somma automatica, presente nella scheda Home gruppo Modifica e nella scheda Formule gruppo Libreria di funzioni.

Cosa succede se dobbiamo calcolare una media ponderata con Excel?

Innanzitutto, cerchiamo di capire una media ponderata: cioè una serie di valori, ognuno dei quali ha un peso particolare. Un valore pesa tre, un altro pesa dodici, un altro ancora pesa sei e così via. Noi vogliamo ottenere una media pesata.

Tabella Excel con un esempio di media ponderata.
Un esempio di media ponderata.

Uno studente ottiene otto valutazioni per una serie di esami che equivalgono ad una serie di crediti formativi. Quando il numero di crediti è pari a dodici, l’esame pesa di più.

Come dobbiamo effettuare questo calcolo?

Ogni voto deve essere moltiplicato per il numero di crediti formativi: ogni singolo risultato deve essere sommato con gli altri esami.

Dato che in Excel non esiste una formula che calcola la media ponderata, teoricamente dobbiamo scrivere una serie di otto moltiplicazioni i cui risultati si sommino fra loro.

La sintassi dovrebbe essere: =(B2C2)+(B3C3)+(B4C4)+(B5C5)… e così via fino a B9*C9. È una soluzione ma è oggettivamente lunga, scomoda, macchinosa e facilissima da sbagliare.

Naturalmente è anche possibile scrivere le singole moltiplicazioni nella colonna D, ma in questo modo andremmo ad aggiungere una colonna. Una soluzione non conveniente per l’esempio che analizzerò in seguito.

Come facciamo quindi?

Sfruttiamo una funzione matematica di Excel fatta apposta per queste situazioni: MATR.SOMMA.PRODOTTO. La trovi nel menu del pulsante Matematica e trig.a, nella scheda Formule.

Questa formula moltiplica due o più intervalli di celle e somma i risultati fra loro. Inseriamo quindi come Matrice1 B2:B9 e come Matrice2 C2:C9, come puoi vedere nell’immagine che segue.

Grafico Excel con un esempio della formula MATR.SOMMA.PRODOTTO.
La finestra della formula MATR.SOMMA.PRODOTTO.

Ovviamente l’ordine della matrice può anche essere invertito, dato che si tratta degli intervalli di celle che si moltiplicano fra loro. Sappiamo bene infatti che fare 2 per 3 o 3 per 2 è la stessa identica cosa.

In pratica Excel ha effettuato l’espressione che stavamo scrivendo in precedenza: un vantaggio notevole. Il risultato però, ovviamente, non è corretto. 1711 non può essere una media voto per uno studente che riceve come valutazione minima 18 e massima 30.

Quello che ci resta da fare è dividere il risultato di MATR.SOMMA.PRODOTTO per la somma dei crediti formativi, ovvero la somma dei pesi.

Dunque, la formula completa è la seguente:

=MATR.SOMMA.PRODOTTO(B2:B9;C2:C9)/SOMMA(C2:C9)

Mentre invece il risultato è 26,73: che possiamo arrotondare o formattare a seconda delle necessità.

Se dobbiamo applicare lo stesso calcolo in una tabella, come quella che vediamo di seguito, dobbiamo sfruttare i riferimenti assoluti.

In pratica nella nostra formula dobbiamo mantenere bloccato l’intervallo di celle che contiene i crediti formativi che si ottengono ad ogni singolo esame. Inserendola nella cella B11, la sintassi della formula sarà: =MATR.SOMMA.PRODOTTO(B2:B9;$M$2:$M$9)/SOMMA($M$2:$M$9).

Grafico Excel con esempio di calcolo della media ponderata.
Il calcolo della media ponderata in una tabella.

In questo modo trascinando la formula verso destra i riferimenti B2:B9 diventeranno C2:C9, mentre M2:M9. Il gruppo di celle contenente i crediti formativi rimane sempre invariato, grazie alla presenza del simbolo del dollaro ($).

La riga 11 viene quindi completata molto rapidamente, come puoi vedere nella prossima immagine, e i valori vengono formattati con un solo decimale.

Esempio Excel di tabella completa.
La tabella completa.

Se vogliamo invece arrotondare realmente il tutto ad un numero avente un solo decimale, è necessario utilizzare la funzione ARROTONDA, specificare 1 nel campo Num_cifre e inserendo il calcolo completo della media ponderata all’interno di Num. Come puoi vedere nell’immagine seguente.

ARROTONDA si trova anche lei nella categoria di funzioni Matematica e trig.a.

Grafico Excel con esempio della finestra della funzione ARROTONDA.
La finestra della funzione ARROTONDA.
Commenti:
Condividi su:

Laureatosi nel 2003 in Scienze Politiche presso l'Università degli Studi di Milano, si occupa di insegnare materie informatiche dal 2004. La sua filosofia professionale lo spinge sempre ad ascoltare i reali bisogni degli allievi, per capire ciò che veramente vogliono imparare. Collabora tuttora con importanti società.

You don't have permission to register