Mediare escludendo i valori piccoli e grandi con Excel
Per finalità statistiche quando il valore più alto e più basso sono disomogenei rispetto al resto del collettivo e potrebbero influenzare troppo il valore finale che scaturisce dalla media aritmetica, può risultare utile calcolare tale valore escludendo dal calcolo proprio il MAX e il MIN della distribuzione.
Nell’articolo Mediare con Excel abbiamo già parlato dei vari tipi di media, qui vengono proposte due tecniche per raffinare la media aritmetica privandola appunto del valore massimo e minimo del collettivo.
Sappiamo che la media non è altro che la somma dei valori divisa per il loro conteggio (cioè per la funzione CONTA.NUMERI di cui abbiamo ampiamente parlato nell’articolo Contare con Excel).
Se ad esempio nel foglio di lavoro si ha un intervallo di valori in B3:B10, per escludere i due valori suddetti dovremmo adottare la seguente espressione:
=(SOMMA(B3:B10)-MIN(B3:B10)-MAX(B3:B10))/(CONTA.NUMERI(B3:B10)-2)
Ovvero dopo aver sommato tutti i valori con SOMMA(B3:B10), si detraggono il valore minimo con MIN(B3:B10) e massimo con MAX(B3:B10). Il risultato ottenuto è diviso per quanti sono i valori dell’intervallo con CONTA.NUMERI(B3:B10) diminuito di 2 per non considerare i due valori eliminati dalla somma.
In realtà si sarebbe anche potuta usare la funzione MEDIA.TRONCATA che esegue la media della parte interna di un insieme di dati ovvero escludendo una percentuale di valori dalla coda superiore ed inferiore dei dati e la cui sintassi è:
MEDIA.TRONCATA(Matrice;Percento) dove i due argomenti sono entrambi obbligatori
Matrice è la matrice o intervallo di valori da troncare e di cui calcolare la media; mentre Percento è il numero frazionario di coordinate da escludere dal calcolo.
Nel nostro esempio il percento da escludere è dato dal numero di valori da escludere, ovvero 2, diviso per il conteggio dei numeri presenti nell’intervallo, ovvero CONTA.NUMERI(B3:B10)
=MEDIA.TRONCATA(B3:B10;2/CONTA.NUMERI(B3:B10))
Se invece volessimo escludere i due valori più piccoli abbiamo bisogno della funzione PICCOLO, che restituisce il k-esimo valore più piccolo di un set di dati e la cui sintassi è
PICCOLO(matrice;k) dove i due argomenti sono entrambi obbligatori
Matrice è la matrice o intervallo di dati numerici di cui si desidera determinare il k-esimo valore più piccolo, mentre K è la posizione del valore da restituire, partendo dal più piccolo, nella matrice o nell’intervallo.
Si avrà quindi
=(SOMMA(B3:B10)-PICCOLO(B3:B10;1)-PICCOLO(B3:B10;2))/(CONTA.VALORI(B3:B10)-2)
In realtà si sarebbe anche potuta usare la funzione MEDIA in forma matriciale ovvero che presuppone non l’Invio finale per la sua risoluzione, ma la combinazione di tasti Ctrl+Maiusc+Invio =MEDIA(SE(B3:B10>PICCOLO(B3:B10;2);B3:B10))
