Funzioni statistiche di Excel per il calcolo delle medie: Tutorial con esempi, seconda parte

Excel fornisce un’ampia gamma di funzioni statistiche che eseguono calcoli dalla media, mediana e moda di base fino alla distribuzione statistica più complessa e ai test di probabilità.

In questo articolo andremo ad approfondire le funzioni statistiche di Excel, per il calcolo della media.

Tieni presente che alcune funzioni statistiche sono state introdotte nelle versioni recenti di Excel e quindi non sono disponibili nelle versioni precedenti.

Funzioni per il calcolo delle Medie

AVERAGE

La funzione AVERAGE è una delle funzioni statistiche di Excel. La funzione restituisce la media dei valori numerici immessi nella funzione. In parole semplici, somma tutti i valori specificati nella funzione, quindi li divide per il conteggio e restituisce il risultato.

Sintassi

= AVERAGE(number1,number2,…)

argomenti

• numero1 : il primo numero che desideri utilizzare per il calcolo della media.
• [numero2] : il secondo numero che si desidera utilizzare per il calcolo della media.

Esempio

Per vedere come opera la funzione AVERAGE vediamo un esempio:

Nel primo esempio abbiamo inserito gli argomenti direttamente nella funzione.

Nel secondo esempio, abbiamo fatto riferimento a un intervallo contenente numeri. Puoi fare riferimento alla cella illimitata utilizzando un intervallo continuo e se vuoi fare riferimento a un intervallo dinamico puoi utilizzare una tabella per quello.

Puoi fare riferimento alla cella illimitata utilizzando un intervallo continuo e se vuoi fare riferimento a un intervallo dinamico puoi utilizzare una tabella.

Nel terzo esempio abbiamo fatto riferimento ad un intervallo in cui le celle sono formattate come valore di testo. In questo caso, puoi convertire quei numeri di testo in numeri reali per calcolare la media.

Nel quarto esempio abbiamo un apostrofo prima di ogni valore in ogni cella e quindi ignorato dalla funzione.

AVERAGEA

La funzione AVERAGEA di Excel è elencata nella categoria Funzioni statistiche di Microsoft Excel. Restituisce la media dei numeri specificati nella funzione, ma a differenza di AVERAGE, considera valori booleani e numeri formattati come testo.

Sintassi

=AVERAGEA(valore1,valore2,…)

argomenti

• value1 : un valore che è un numero, un valore logico o un numero memorizzato come testo.
• [valore2] : un valore che è un numero, un valore logico o un numero memorizzato come testo.

Esempio

Per capire la funzione AVERAGEA dobbiamo vedere un esempio:

Il valore restituito dalla funzione è 10,17 che è “(0+0+1+10+20+30)/6”.

AVERAGEIF

La funzione AVERAGEIF di Excel è elencata nella categoria Funzioni statistiche di Microsoft Excel. Restituisce la media dei numeri che soddisfano più condizioni specificate . 

Sintassi

= AVERAGEIF( range, criteria, [average_range] )

Argomenti

• range:  una matrice di valori (o un intervallo di celle contenenti valori) da testare rispetto ai criteri forniti.
• criteria:  La condizione da testare rispetto a ciascuno dei valori nell’intervallo fornito.
• [average_range]:  Una matrice facoltativa di valori numerici (o celle contenenti numeri) per cui deve essere calcolata la media, se il valore corrispondente nell’intervallo soddisfa i criteri forniti.

Se l’argomento [average_range] viene omesso, la media viene calcolata per i valori nell’intervallo fornito iniziale.

I criteri forniti possono essere:

un valore numerico (inclusi numeri interi, decimali, date, ore e valori logici) (ad esempio 10, 01/01/2008, TRUE)
O
una stringa di testo (ad esempio “Testo”, “Giovedì”) – DEVE essere fornita tra virgolette
O
un’espressione (ad esempio “>12”, “<>0”) – DEVE essere fornita tra virgolette.
Tieni inoltre presente che la funzione AVERAGEIF di Excel non fa distinzione tra maiuscole e minuscole. Quindi, ad esempio, le stringhe di testo “TEXT” e “text” verranno valutate come uguali.

Esempio

Per capire la funzione AVERAGEIF dobbiamo provarla in un esempio.

Le celle A16-A20 del seguente foglio di calcolo mostrano cinque esempi della funzione AVERAGEIF di Excel.

Per ogni chiamata alla funzione AVERAGEIF di Excel, l’argomento range (da testare rispetto ai criteria) è l’intervallo di celle A1-A14 e l’argomento [average_range] (contenente i valori di cui calcolare la media) è l’intervallo di celle B1-B14.

Tieni presente che, nelle celle A16, A18 e A20 del foglio di calcolo sopra, il valore di testo “Thursday” e le espressioni “>2” e “<>TRUE” sono racchiusi tra virgolette. Questo è essenziale per tutti i testi o le espressioni.

AVERAGEIFS

La funzione AVERAGEIFS di Excel è elencata nella categoria Funzioni statistiche di Microsoft Excel. Restituisce la media dei numeri che soddisfano più condizioni specificate . A differenza di AVERAGEIF, puoi impostare più condizioni e calcolare la media solo per i numeri che soddisfano tutte le condizioni.

Sintassi

= AVERAGEIFS( average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

Argomenti

• average_range:  Una matrice di valori numerici (o celle contenenti numeri) per cui deve essere calcolata la media.
• criteria_range1, [criteria_range2], …: Matrici di valori (o intervalli di celle contenenti valori) da testare rispetto ai rispettivi criteria1, criteria2, … (Gli array criteria_range forniti devono avere tutti la stessa lunghezza).
• criteria1, [criteria2], …: Le condizioni da testare rispetto ai valori in criteria_range1, [criteria_range2], …

Esempio

Vediamo ora un esempio della funzione AVERAGEIFS:

Nell’esempio seguente, abbiamo utilizzato la funzione AVERAGEIFS per calcolare la quantità media venduta dal venditore “Pietro” e per il prodotto “B”. Abbiamo inserito i criteri direttamente nella funzione e abbiamo due voci della vendita del prodotto B da parte di Peter.

Nell’esempio seguente, abbiamo utilizzato AVERAGEIFS con un asterisco per calcolare il prezzo medio della frutta la cui quantità è maggiore di 20 unità e ha B nel nome.

Nei dati seguenti, abbiamo due frutti che soddisfano questi criteri.

MEDIAN

La funzione MEDIAN di Excel restituisce la mediana statistica (il valore medio) di un elenco di numeri forniti.

Sintassi

= MEDIAN( number1, [number2], ... )

Argomenti

gli argomenti numerici sono un insieme di uno o più valori numerici (o matrici di valori numerici), per i quali si desidera calcolare la mediana

Notare che:

• Se è presente un numero pari di valori nel set di dati fornito, viene restituita la media dei due valori medi;
• Se una matrice fornita contiene celle vuote, testo o valori logici, questi valori vengono ignorati durante il calcolo della mediana.
• Nelle versioni attuali di Excel (Excel 2007 e successive), è possibile fornire fino a 255 argomenti numerici alla funzione Mediana, ma in Excel 2003 la funzione può accettare solo fino a 30 argomenti numerici . Tuttavia, ciascuno degli argomenti numerici può essere un array di molti valori.

Esempio

Il seguente foglio di calcolo mostra tre esempi della funzione Median:

Considera che, negli esempi precedenti:

• L’esempio nella cella B2 riceve un numero pari di valori e quindi la mediana viene calcolata come la media dei due valori medi, 8 e 9;
• L’esempio nella cella B3 include la cella vuota A8. Questa cella viene ignorata nel calcolo della mediana.

Per ulteriori dettagli sulla funzione MEDIAN di Excel, consultare il sito Web di Microsoft Office .

MODE

La funzione MODE di Excel restituisce la MODE statistica (il valore più frequente) di un elenco di numeri forniti. Se nei dati forniti sono presenti 2 o più valori ricorrenti, la funzione restituisce il valore più basso tra questi

Sintassi

= MODE( number1, [number2], ... )

Argomenti

sono un insieme di uno o più valori numerici (o matrici di valori numerici), per i quali si desidera calcolare la MODE statistica.

Nota:

• Nelle versioni attuali di Excel (Excel 2007 e successive), è possibile fornire fino a 255 argomenti numerici alla funzione MODE, ma in Excel 2003 la funzione può accettare solo fino a 30 argomenti numerici .
• Il testo e i valori logici all’interno di una matrice di numeri fornita vengono ignorati dalla funzione Mode.

Esempi funzioni MODE

Esempio 1

Il seguente foglio di calcolo mostra la funzione MODE Excel, utilizzata per calcolare la MODE statistica dell’insieme di valori nelle celle A1-A6.

Esempio 2

Il seguente foglio di calcolo mostra la funzione MODE, utilizzata per calcolare la MODE statistica dell’insieme di valori nelle celle A1-A10.

Si noti che in questo caso sono presenti due mode nei dati.

Nel caso precedente, dove i dati nella colonna A del foglio di calcolo precedente hanno due MODE statistiche (3 e 4), la funzione MODE restituisce il più basso di questi due valori.

Per ulteriori dettagli ed esempi della funzione MODE di Excel, consultare il sito Web di Microsoft Office .

MODE.SNGL

La funzione MODE.SNGL di Excel restituisce la MODE statistica (il valore più frequente) di un elenco di numeri forniti. Se nei dati forniti sono presenti 2 o più valori ricorrenti, la funzione restituisce il valore più basso tra questi.

La funzione Mode.Sngl è una novità di Excel 2010 e pertanto non è disponibile nelle versioni precedenti di Excel. Tuttavia, la funzione è semplicemente una versione rinominata della funzione MODE disponibile nelle versioni precedenti di Excel.

Sintassi

= MODE.SNGL( number1, [number2], ... )

Argomenti

sono un insieme di uno o più valori numerici (o matrici di valori numerici), per i quali si desidera calcolare la MODE.SNGL statistica.

Esempi funzioni MODE.SNGL

Esempio 1

Il seguente foglio di calcolo mostra la funzione MODE.SNGL Excel, utilizzata per calcolare la MODA statistica dell’insieme di valori nelle celle A1-A6.

Esempio 2

Il seguente foglio di calcolo mostra la funzione MODE.SNGL, utilizzata per calcolare la moda statistica dell’insieme di valori nelle celle A1-A10.

Si noti che in questo caso sono presenti due mode nei dati.

Nel caso precedente, dove i dati nella colonna A del foglio di calcolo precedente hanno due MODE statistiche (3 e 4), la funzione MODE.SNGL restituisce il più basso di questi due valori.

Per ulteriori dettagli ed esempi della funzione MODE.SNGL di Excel, consultare il sito Web di Microsoft Office .

GEOMEAN

La Media geometrica è una misura della media che indica il valore tipico di un insieme di numeri. Questa misura può essere utilizzata solo per valori positivi.

La media geometrica di un insieme di valori, y ₁ , y ₂ , …, y _n si calcola con la formula:

Si noti che la media geometrica è sempre inferiore o uguale alla media aritmetica.

La funzione Geomean di Excel calcola la media geometrica di un insieme di valori fornito.

Sintassi

= GEOMEAN( number1, [number2], ... )

Argomenti

uno o più valori numerici positivi (o matrici di valori numerici), per i quali si desidera calcolare la media geometrica.

Nelle versioni attuali di Excel (Excel 2007 e successive), la funzione può accettare fino a 255 argomenti numerici , ma in Excel 2003 la funzione può accettare solo fino a 30 argomenti numerici . Tuttavia, ogni argomento può essere una matrice di valori o un intervallo di celle, ciascuna delle quali può contenere molti valori.

Esempio

La cella B1 del foglio di calcolo mostra un semplice esempio della funzione geomean di Excel, utilizzata per calcolare la media geometrica dei valori nelle celle A1-A5.

In questo esempio, la funzione Geomean restituisce il valore 1.622671112 .

HARMEAN

La media armonica è una misura della media calcolata come il reciproco della media aritmetica dei reciproci. Questo può essere calcolato solo per valori positivi.

La media armonica di un insieme di valori, y1, y2, …, yn è quindi data dalla formula:

la media armonica è sempre minore o uguale alla media geometrica e la media geometrica è sempre minore o uguale alla media aritmetica.

La funzione Harmean di Excel calcola la media armonica di un insieme di valori fornito.

Sintassi

= HARMEAN( number1, [number2], ... )

Argomenti

uno o più valori numerici positivi (o matrici di valori numerici), per i quali si desidera calcolare la media armonica.

Nelle versioni attuali di Excel (Excel 2007 e successive), la funzione può accettare fino a 255 argomenti numerici, ma in Excel 2003 la funzione può accettare solo fino a 30 argomenti numerici. Tuttavia, ogni argomento può essere una matrice di valori o un intervallo di celle, ciascuna delle quali può contenere molti valori.

Esempio

La cella B1 del foglio di calcolo a destra mostra un semplice esempio della funzione Harmean di Excel, utilizzata per calcolare la media armonica dei valori nelle celle A1-A5.

In questo esempio, la funzione Harmean restituisce il valore 1.229508197.

TRIMMEAN

La funzione TRIMMEAN (nota anche come media troncata) è una misura della media che indica la tendenza centrale di un insieme di valori.

La media troncata viene calcolata scartando alcuni valori alle estremità dell’intervallo di valori, prima di calcolare la media aritmetica dei valori rimanenti. Ciò impedisce che la media calcolata venga distorta da valori estremi (noti anche come valori anomali, tecnicamente outliers).

Sintassi

= TRIMMEAN( array, percent )

Argomenti

• array – Una matrice di valori numerici per i quali si desidera calcolare la media tronca.
• percent – La percentuale di valori che si desidera eliminare dall’array fornito.

Si noti che il valore percentuale specificato è la percentuale totale dei valori da escludere dal calcolo. Questa percentuale viene divisa per due per ottenere il numero di valori rimossi da ciascuna estremità dell’intervallo.

Va inoltre notato che, quando Excel calcola quanti valori eliminati dall’array di valori forniti, la percentuale calcolata viene arrotondata per difetto al multiplo di 2 più vicino. Ad esempio, se si desidera calcolare la media tronca di un array di 10 valori, quindi:

• Una percentuale del 15% corrisponde a 1,5 valori, che verranno arrotondati per difetto a 0 (ovvero nessun valore verrà scartato dall’array prima del calcolo della media);
• Una percentuale del 20% corrisponde a 2 valori, quindi verrà scartato 1 valore da ciascuna estremità dell’intervallo prima di calcolare la media dei valori rimanenti;
• Una percentuale del 25% corrisponde a 2,5 valori, che verranno arrotondati per difetto a 2 (ovvero, 1 valore verrà scartato da ciascuna estremità dell’intervallo prima di calcolare la media dei valori rimanenti).

Esempio

Le celle B1-B3 del foglio di calcolo seguente mostrano 3 esempi della funzione trimmean di Excel, tutti utilizzati per calcolare la media troncata dei valori nelle celle A1-A10, per diversi valori percentuali .

Tieni presente che, nella cella B1 del foglio di calcolo sopra, l’argomento percentuale fornito è 15%. Poiché nell’array fornito sono presenti 10 valori, il numero di valori da ignorare è 1,5 arrotondato per difetto al multiplo di 2 più vicino che è zero.

Funzioni per il calcolo delle Permutazioni

PERMUT

Il numero di permutazioni per un dato numero di oggetti è il numero di combinazioni in ogni ordine possibile.

Le permutazioni differiscono dalle combinazioni in quanto, per una permutazione, l’ordine degli oggetti è importante, ma in una combinazione l’ordine non ha importanza.

Il numero di permutazioni possibili è dato dalla formula:

dove k è il numero di oggetti scelti e n è il numero di oggetti possibili.

La funzione Excel Permut calcola il numero di permutazioni di un numero specificato di oggetti da un set di oggetti.

Sintassi

= PERMUT( number, number_chosen )

Argomenti

• number: Il numero totale di oggetti disponibili
• number_chosen: Il numero di oggetti in ciascuna permutazione (ovvero il numero di oggetti selezionati dal set)

Si noti che, se uno qualsiasi degli argomenti viene fornito come valore decimale, verrà troncato in numeri interi dalla funzione Permut.

Esempio

Nel seguente foglio di calcolo, la funzione Excel Permut viene utilizzata per calcolare il numero di permutazioni di sei oggetti, selezionati da insiemi di dimensioni diverse:

PERMUTATIONA

Le funzioni Excel Permut e Permutationa calcolano entrambe il numero di permutazioni di una selezione di oggetti da un set.

Tuttavia, le due funzioni differiscono in quanto la funzione Permut non conta le ripetizioni mentre la funzione Permutationa conta le ripetizioni.

Ad esempio, in un insieme di 3 oggetti, a , b , c , quante permutazioni ci sono di 2 oggetti?

• La funzione Permut restituisce il risultato 6 (permutazioni: ab , ac , ba , bc , ca , cb );
• La funzione Permutazionea restituisce il risultato 9 (permutazioni: aa , ab , ac , ba , bb , bc , ca , cb , cc ).

La funzione Excel Permutationa calcola il numero di permutazioni di un numero specificato di oggetti da un set di oggetti.

Sintassi

= PERMUTATIONA( number, number_chosen )

Argomenti

• number: Il numero totale di oggetti nel set (deve essere ≥ 0).
• number_chosen: Il numero di oggetti selezionati dal set (deve essere ≥ 0).

Si noti che, se uno qualsiasi degli argomenti viene fornito come valore decimale, verrà troncato in numeri interi dalla funzione PERMUTATIONA.

Esempio

Nel seguente foglio di calcolo, la funzione Excel PERMUTATIONA viene utilizzata per calcolare il numero di permutazioni di sei oggetti, selezionati da insiemi di dimensioni diverse:

Funzioni per il calcolo degli intervalli di confidenza

CONFIDENCE

In Excel 2010, la funzione CONFIDENCE è stata sostituita dalla funzione Confidence.Norm.

Sebbene sia stata sostituita, le versioni attuali di Excel dispongono ancora della funzione Confidence (memorizzata nell’elenco delle funzioni di compatibilità), per consentire la compatibilità con le versioni precedenti di Excel.

Tuttavia, la funzione Confidence potrebbe non essere disponibile nelle versioni future di Excel, pertanto è consigliabile utilizzare la funzione Confidence.Norm, se possibile.

La funzione Confidence di Excel utilizza una distribuzione normale per calcolare un valore di confidenza che può essere utilizzato per costruire l’intervallo di confidenza per una media della popolazione, per una probabilità fornita e una dimensione del campione. Si presuppone che la deviazione standard della popolazione sia nota.

Sintassi

= CONFIDENCE( alpha, standard_dev, size )

Argomenti

• alfa: Il livello di significatività (= 1 – livello di confidenza). (Ad esempio, un livello di significatività di 0,05 equivale a un livello di confidenza del 95%).
• standard_dev: La deviazione standard della popolazione.
• size: La dimensione del campione di popolazione.

Per calcolare l’intervallo di confidenza per una media della popolazione, il valore di confidenza restituito deve essere quindi aggiunto e sottratto alla media del campione. Cioè. per il campione media x:

Confidence Interval =   x   ±   CONFIDENCE

Esempio

Nel foglio di calcolo seguente, la funzione di confidenza di Excel viene utilizzata per calcolare l’intervallo di confidenza con una significatività di 0,05 (ovvero un livello di confidenza del 95%), per la media di un campione di altezze di 100 uomini. La media campionaria è 1,8 metri e la deviazione standard è 0,07 metri.

La funzione precedente restituisce un valore di confidenza pari a 0,013719748

Pertanto l’intervallo di confidenza è 1,8 ± 0,013719748, che equivale all’intervallo compreso tra 1,786280252 e 1,813719748

CONFIDENCE.NORM

Nelle statistiche, l’intervallo di confidenza è l’intervallo in cui è probabile che rientri un parametro della popolazione, per una data probabilità.

Per esempio. Per una data popolazione e una probabilità del 95%, l’intervallo di confidenza è l’intervallo in cui è probabile che un parametro della popolazione ricada al 95%.

Si noti che l’accuratezza dell’intervallo di confidenza dipende dal fatto che la popolazione abbia una distribuzione normale.

La funzione Confidence.Norm di Excel utilizza una distribuzione normale per calcolare un valore di confidenza che può essere utilizzato per costruire l’intervallo di confidenza per una media della popolazione, per una probabilità fornita e una dimensione del campione. Si presuppone che la deviazione standard della popolazione sia nota.

Sintassi

= CONFIDENCE.NORM( alpha, standard_dev, size )

Argomenti

• alfa: Il livello di significatività (= 1 – livello di confidenza). (Ad esempio, un livello di significatività di 0,05 equivale a un livello di confidenza del 95%).
• standard_dev: La deviazione standard della popolazione.
• size: La dimensione del campione di popolazione.

Per calcolare l’intervallo di confidenza per una media della popolazione, il valore di confidenza restituito deve essere quindi aggiunto e sottratto alla media del campione. Cioè. per il campione media x:

Confidence Interval =   x   ±   CONFIDENCE

Esempio

Nel foglio di calcolo seguente, la funzione di confidenza di Excel viene utilizzata per calcolare l’intervallo di confidenza con una significatività di 0,05 (ovvero un livello di confidenza del 95%), per la media di un campione di altezze di 100 uomini. La media campionaria è 1,8 metri e la deviazione standard è 0,07 metri.

La funzione precedente restituisce un valore di confidenza pari a 0,013719748

Pertanto l’intervallo di confidenza è 1,8 ± 0,013719748, che equivale all’intervallo compreso tra 1,786280252 e 1,813719748

Ercole Palmeri