Il tutorial mostra come calcolare le medie in Excel con valori numerici e con altri tipi di dati. Potrete anche imparare a usare le funzioni MEDIA.SE e MEDIA.PIÙ.SE per mediare le celle che soddisfano determinati criteri.
In parole povere, calcolare la media di un insieme di valori, è scoprire il valore più comune nell’insieme. Ad esempio, se alcuni atleti hanno corso i 100m, potreste voler conoscere il risultato medio – vale a dire quanto tempo la maggior parte dei velocisti ci metteranno a completare la gara.
In matematica, la media è chiamata media aritmetica, o semplicemente media, ed è calcolata sommando un insieme di numeri e successivamente dividendo per il conteggio di quei numeri.
Nell’esempio precedente, se il primo atleta copre la distanza in 10,5 secondi, il secondo in 10,7 secondi, e il terzo in 11,2 secondi, il tempo medio è 10,8 secondi: =(10,5 + 10,7 + 11,2)/3.
Per calcolare la media in Excel, non sarà necessario scrivere tali espressioni matematiche, le potenti funzioni media di Excel faranno il lavoro dietro le quinte. Più avanti in questo tutorial, mostreremo la sintassi di ogni funzione con esempi pratici del loro uso.
- MEDIA – trova la media di celle con numeri
- MEDIA.VALORI – trova la media di celle con tutti i tipi di dati (numeri, booleani e valori di testo)
- MEDIA.SE – trova la media delle celle che corrispondono ad un determinato criterio
- MEDIA.PIÙ.SE – trova la media delle celle che corrispondono a vari criteri
La funzione MEDIA di Excel
È possibile utilizzare la funzione MEDIA in Excel per restituire la media (media aritmetica) delle celle specificate.
MEDIA(num1; [num2]; ...)
Num1, num2, … sono i valori numerici per i quali si desidera trovare la media. Il primo argomento è necessario, quelli successivi sono opzionali, e fino a 255 argomenti possono essere inclusi nella stessa formula. I parametri possono essere forniti come numeri, riferimenti di cella, o intervalli.
Utilizzare la funzione MEDIA in Excel – esempi di formule
MEDIA è una delle funzioni di Excel più semplici da usare e i seguenti esempi dimostrano.
Esempio 1. Calcolare una media di numeri
Per trovare una media di determinati numeri, è possibile fornirli direttamente nella formula MEDIA di Excel. Ad esempio, =MEDIA (1;2;3;4) restituisce 2,5 come risultato.
Per calcolare una media dei numeri presenti in un determinato intervallo, come A1:A10, si inserisce tale intervallo nella formula:
=MEDIA(A1:A10)
Per restituire una media di celle non adiacenti, fornite ciascuna cella individualmente, per esempio:
=MEDIA(A1; C1; D1)
E, naturalmente, nulla vi vieta di includere valori, riferimenti di cella e intervalli nella stessa formula, se ne avete bisogno. Ad esempio, la seguente formula calcola la media di 2 intervalli e 1 singola cella:
=MEDIA(B3:B5; B7:B9; B11)
A parte i numeri, è possibile utilizzare la funzione MEDIA di Excel per calcolare una media di altri valori numerici quali percentuali e tempi, come dimostrato negli esempi che seguono.
Esempio 2. Calcolare una percentuale media in Excel
Se avete una colonna di percentuali nel foglio, come si fa ad ottenere il tasso percentuale medio? Utilizzando un normale formula MEDIA di Excel 🙂
Esempio 3. Calcolare un tempo medio in Excel
Come ricorderete, all’inizio di questo tutorial, abbiamo trovato il tempo medio di tre velocisti dei 100m con un calcolo piuttosto semplice. Ma cosa succede se necessitate di calcolare tempi medi che includano ore, minuti e secondi? Calcolare manualmente diverse unità di tempo, sarebbe un vero e proprio dolore … ma la formula MEDIA in Excel risolve tutto perfettamente.
La funzione MEDIA di Excel – Alcuni punti di attenzione!
Come avete appena visto, utilizzare la funzione MEDIA in Excel è facile. Tuttavia, ha alcune peculiarità di cui è necessario essere a conoscenza.
- Le celle con valore pari a zero (0) sono incluse nella media
- Le celle che contengono stringhe di testo, valori booleani VERO e FALSO e celle vuote vengono ignorate. Se volete includere i valori booleani e le rappresentazioni testuali di numeri nel calcolo, utilizzate la funzione MEDIA.VALORI
- I valori booleani digitati direttamente nella formula MEDIA di Excel vengono conteggiati. Ad esempio, la formula =MEDIA(VERO; FALSO) restituisce 0,5, che è la media di 1 e 0
La funzione MEDIA.VALORI di Excel
La funzione MEDIA.VALORI è simile a MEDIA in quanto calcola la media (media aritmetica) dei valori nei suoi argomenti. La differenza è che MEDIA.VALORI include nel calcolo tutte le celle non vuote, sia che contengano numeri, testo, valori booleani VERO e FALSO, e stringhe vuote restituiti da altre formule.
MEDIA.VALORI(val1; [val2]; ...)
val1, val2, … sono valori, matrici, riferimenti di cella o intervalli di cui si desidera fare la media. Solo il primo argomento è necessario, gli altri (fino a 255) sono opzionali.
La funzione MEDIA.VALORI di Excel – Alcuni punti di attenzione!
Come accennato in precedenza, la funzione MEDIA.VALORI elabora diversi tipi di valore: numeri, stringhe di testo, valori logici VERO e FALSO. Nelle formule MEDIA.VALORI:
- Le celle vuote vengono ignorate
- I valori di testo, tra cui le stringhe vuote (“”) restituite da altre formule, vengono valutate come 0
- Il valore booleano VERO viene valutato come 1 e FALSO come 0
Ad esempio, la formula =MEDIA.VALORI(2; FALSO) restituisce 1, che è la media di 2 e 0. La formula =MEDIA.VALORI(2; VERO) restituisce 1.5, che è la media di 2 e 1.
Quindi, se non desiderate includere valori booleani e stringhe di testo nei calcoli, utilizzate la funzione MEDIA di Excel piuttosto che MEDIA.VALORI.
La funzione MEDIA.SE di Excel
La funzione MEDIA.SE di Excel calcola la media (media aritmetica) di tutte le celle che soddisfano un criterio specificato.
MEDIA.SE(intervallo;criteri; [intervallo_media])
La funzione MEDIA.SE ha i seguenti argomenti, i primi 2 sono necessari, l’ultimo è opzionale:
- intervallo – l’intervallo di celle da verificare rispetto al criterio fornito
- criteri – la condizione utilizzata per determinare le celle da mediare. I criteri possono essere forniti sotto forma di un numero, un’espressione logica, un valore di testo o un riferimento di cella, ad esempio 5, “>5”, “gatto”, o A2
- intervallo_media – le celle di cui si vuole realmente la media (opzionale). Se omesso, la formula calcolerà una media dei valori nell’argomento intervallo.
La funzione MEDIA.SE è disponibile in Excel 2016, Excel 2013, Excel 2011 per Mac, Excel 2010 e 2007.
Utilizzare la funzione MEDIA.SE in Excel – esempi di formule
E ora, vediamo come sia possibile utilizzare la funzione di Excel MEDIA.SE con casi reali per trovare una media di celle che soddisfino criteri.
Esempio 1. Mediare celle che corrispondono esattamente ai criteri
L’uso classico della funzione MEDIA.SE in Excel è trovare una media delle celle che corrispondono esattamente ad un determinato criterio. In questo esempio, cerchiamo di mediare solo le vendite (B2:B8) corrispondenti agli ordini di Banane (A2:A8):
=MEDIA.SE(A2:A8; "banane"; B2:B8)
Invece di inserire la condizione direttamente in una formula, è possibile digitarla in una cella separata e fare riferimento a quella cella nella formula:
=MEDIA.SE(A2:A8; E1; B2:B8)
Esempio 2. Mediare celle, che corrispondono parzialmente ai criteri (caratteri jolly)
Nelle formule di MEDIA.SE di Excel, è possibile utilizzare i caratteri jolly nell’argomento criteri, per mediare le celle in base ad una corrispondenza parziale:
- Utilizzate un punto interrogativo (?) per trovare qualsiasi carattere singolo
- Utilizzate un asterisco (*) per trovare qualsiasi sequenza di caratteri
- Per trovare effettivamente un punto interrogativo o un asterisco, digitate una tilde (~) prima del carattere nei criteri
Nel precedente esempio, supponete di avere 3 diversi tipi di banane e che desiderate trovare la loro media. La seguente formula funzionerà benissimo:
=MEDIA.SE(A2:A8; "banane*"; B2:B8)
Se la parola chiave è suscettibile di essere preceduta e/o seguita da altri caratteri, aggiungete un asterisco sia prima della parola che dopo di essa, così: =MEDIA.SE(A2:A8; “*banane*”; B2:B8).
Per trovare la media di tutti gli elementi escludendo tutti i tipi di banana, utilizzate la seguente formula:
=MEDIA.SE(A2:A8; "<>*(banana)*"; B2:B8)
Esempio 3. Mediare celle in base a criteri numerici e operatori logici
Molto spesso, capita di voler mediare celle in cui la quantità è maggiore o minore di un certo valore. Ad esempio, abbiamo un elenco di numeri nella colonna A e vogliamo trovare una media di quelli che sono maggiori di 10.
Il modo corretto per inserire un tale criterio è quello di racchiudere l’operatore logico e il numero tra virgolette. Così, la formula in Excel sarebbe la seguente:
=MEDIA.SE(A2:A7; ">10")
Un’altra attività comune può essere quella di trovare la media dei numeri che non sono uguali a zero. Per questo, avete bisogno dell’operatore logico “diverso da” nell’argomento criteri della formula MEDIA.SE:
=MEDIA.SE(A2:A7; "<>0")
Come avrete notato, non abbiamo usato il terzo argomento [intervallo_media] in nessuna delle formule di cui sopra in quanto vogliamo trovare la media nell’intervallo iniziale.
Esempio 4. MEDIA.SE per celle vuote o non vuote
Quando si esegue l’analisi dei dati in Excel, è possibile aver bisogno di trovare una media di numeri corrispondenti sia a celle vuote che a celle non vuote.
Mediare se vuote
Per includere le celle vuote, che non contengono assolutamente nulla (nessuna formula, nessuna stringa di lunghezza zero), inserite “=” nell’argomento criteri.
Ad esempio, la seguente formula calcola una media delle celle C2:C8 se una cella nella colonna B nella stessa riga è assolutamente vuota:
=MEDIA.SE(B2:B8; "="; C2:C8)
Per mediare valori in corrispondenza di celle visivamente vuote, comprese quelli che contengono stringhe vuote restituite da altre funzioni (per esempio, le celle con una formula come =””), utilizzate “” come criteri. Per esempio:
=MEDIA.SE(B2:B8; ""; C2:C8)
Mediare se non vuote
Per trovare la media dei valori corrispondenti alle celle non vuote, digitate “<>” in criteri.
Ad esempio, la seguente formula MEDIA.SE calcola una media delle celle C2:C8 se una cella nella colonna B nella stessa riga non è vuota:
=MEDIA.SE(B2:B8; "<>"; C2:C8)
Esempio 5. Utilizzare i riferimenti di cella e altre funzioni nei criteri di MEDIA.SE
Invece di digitare i criteri in una formula, è possibile fare riferimento ad una certa cella in cui gli utenti possono inserire valori diversi, senza alterare la formula MEDIA.SE.
Nel caso in cui un riferimento di cella è un esatto criterio di corrispondenza, digitatelo semplicemente nell’argomento criteri come abbiamo fatto nell’esempio 1:
=MEDIA.SE(A2:A8; E1; B2:B8)
Se in criteri usate un’espressione logica con un riferimento di cella o un’altra funzione, poi dovete racchiudere l’operatore logico tra virgolette e aggiungere una e commerciale (&) per concatenare un riferimento di cella o una funzione.
Ad esempio, per calcolare le vendite medie (C2:C8) se sono superiori al valore in E4, utilizzate la seguente formula:
=MEDIA.SE(C2:C8; ">"&E4)
Con delle date in B2:B8, la seguente formula restituisce la media delle vendite (C2:C8) che abbiamo realizzato fino alla data corrente:
=MEDIA.SE(B2:B8; "<="&OGGI(); C2:C8)
La funzione MEDIA.PIÙ.SE di Excel
La funzione MEDIA.PIÙ.SE in Excel è la controparte plurale di MEDIA.SE. Permette di inserire più condizioni e restituisce la media (media aritmetica) delle celle che soddisfano tutti i criteri specificati (logica AND – Tutti i criteri devono essere soddisfatti).
MEDIA.PIÙ.SE(intervallo_media; intervallo_criteri1; criteri1; [intervallo_criteri2; criteri2]; ...)
La funzione MEDIA.PIÙ.SE ha i seguenti argomenti:
- intervallo_media – l’intervallo di celle che si desidera mediare
- intervallo_criteri1, intervallo_criteri2, … – da 1 a 127 intervalli da testare in base ai criteri specificati. intervallo_criteri1 è richiesto, quelli successivi sono opzionali
- criteri1, criteri2, … – da 1 a 127 criteri che determinano quali celle mediare. I criteri possono essere forniti sotto forma di numeri, espressioni logiche, valori di testo o riferimenti di cella. criteri1 è necessario, i criteri aggiuntivi sono opzionali
La funzione MEDIA.PIÙ.SE è disponibile in Excel 2016, Excel 2013, Excel 2011 per Mac, Excel 2010 e 2007.
Utilizzare la funzione MEDIA.PIÙ.SE in Excel – esempi di formule
Come già accennato, la funzione di Excel MEDIA.PIÙ.SE trova la media delle sole celle che soddisfano tutti i criteri specificati. In sostanza, lo si utilizza in modo simile a MEDIA.SE, tranne che, nella stessa formula, è possibile specificare più di un intervallo_criteri e più criteri.
Esempio 1. Mediare celle con criteri multipli (testo e numeri)
Supponendo di disporre di un elenco di prodotti nella colonna A e degli importi di vendita in colonna B, cerchiamo di scoprire la media delle vendite di banane che sono maggiori di € 100.
In questa formula MEDIA.PIÙ.SE:
- intervallo_media è B2:B8 (le celle che si desidera mediare se sono soddisfatte entrambe le condizioni)
- intervallo_criteri1 è A2:A8 (colonna Prodotti) e criteri1 è “banane”
- intervallo_criteri2 è B2:B8 (colonna Vendite) e criteri2 è “>100”
Assemblando insieme questi componenti, si ottiene la seguente formula:
=MEDIA.PIÙ.SE(B2:B8; A2:A8; "banana"; B2:B8; ">100")
E se, nella formula, si sostituiscono i valori “testuali” con i riferimenti di cella, si otterrà qualcosa di simile a questo:
Come potete vedere, solo due celle (B3 e B5) soddisfano entrambe le condizioni, e quindi solo queste celle vengono mediate.
Esempio 2. Mediare celle in base alle date
In questo esempio, cerchiamo di ottenere una media degli articoli consegnati prima del 21-feb-2016 il cui stato è definito (la cella della colonna corrispondente non è vuota). Con la quantità in colonna D (intervallo_media), le date in colonna B (intervallo_criteri1) e lo stato in colonna C (intervallo_criteri2), la formula è la seguente:
=MEDIA.PIÙ.SE(D2:D8; B2:B8; "<21/2/2016"; C2:C8; "<>")
In criteri1, abbiamo immesso una data preceduta da un operatore di confronto. In criteri2, abbiamo inserito “<>” che dice ad Excel di includere in intervallo_criteri2 solo le celle non vuote (colonna C in questo esempio).
Le funzioni MEDIA.SE e MEDIA.PIÙ.SE di Excel – Alcuni punti di attenzione!
Le funzioni MEDIA.SE e MEDIA.PIÙ.SE di Excel hanno molto in comune, in particolare:
- Nell’argomento intervallo_media, le celle vuote, i valori booleani VERO e FALSO e i valori testuali vengono ignorati
- Negli argomenti criteri/intervallo_criteri, le celle vuote vengono trattate come valori pari a zero (0)
- Se intervallo_media contiene solo celle vuote o valori di testo, entrambe le funzioni restituiscono l’errore #DIV0!
- Se neanche una cella soddisfa i criteri (tutti i criteri in caso di MEDIA.PIÙ.SE), anche in questo caso viene restituito l’errore #DIV0!
Particolarità di MEDIA.SE
intervallo_media non deve necessariamente essere della stessa dimensione di intervallo. Tuttavia, le celle effettivamente da mediare sono determinate dalle dimensioni dell’argomento intervallo. In altre parole, la cella in alto a sinistra in intervallo_media è trattata come la cella di partenza, e comprende il numero di colonne e righe contenute nell’argomento intervallo.
Particolarità di MEDIA.PIÙ.SE
A differenza della funzione MEDIA.SE, MEDIA.PIÙ.SE richiede che ogni intervallo_criteri sia della stessa dimensione di intervallo_media.
Come mediare celle con criteri multipli e logica OR (O – oppure)
Dal momento che la funzione di Excel MEDIA.PIÙ.SE funziona con la logica AND e la funzione MEDIA.SE consente solo 1 criterio, dovremo inventarci la nostra formula per calcolare una media con logica OR. In altre parole, faremo una formula per calcolare la media in Excel se una delle condizioni specificate è soddisfatta.
Esempio 1. Mediare con logica OR in base a più criteri testuali
Supponiamo che vogliate ottenere un media delle vendite (C2:C8) sia di banane che di mele (A2:A8). Per calcolare questo, avrete bisogno di una formula matriciale che utilizzi alcune funzioni di Excel:
=MEDIA(SE(VAL.NUMERO(CONFRONTA(A2:A8;{"banane"."mele"};0));B2:B8))
Ricordiamo che le formule matriciali devono essere inserite tramite Ctrl + Maiusc + Invio, e non semplicemente con Invio.
Di tutte le formule di Excel per calcolare una media discusse finora, questa è la più particolare (anche se, ci sono altri due esempi più avanti 😉 Il seguente schema renderà più facile la sistemazione della formula per i vostri fogli di lavoro:
=MEDIA(SE(VAL.NUMERO(CONFRONTA(intervallo; {"criteri1"."criteri2". …}; 0)); intervallo_media))
Esempio 2. Mediare con logica OR basata su criteri numerici con operatori di confronto
Se volete mediare celle in base a diversi criteri numerici e condizioni maggiore di/minore di, combinate con logica OR, la formula discussa nel precedente esempio non funzionerà perché non potete far rientrare quelle espressioni logiche in una matrice. La soluzione utilizza la funzione SOMMA in una formula matriciale.
Supponendo di avere le Quantità in colonna B e le Vendite in colonna C, e che vogliate mediare le vendite che hanno un valore superiore a 50 sia nella colonna B che nella colonna C. In più, volete evitare duplicati, cioè non volete contare una riga due volte perché ha valore superiore a 50 sia nella colonna B che nella colonna D.
Ecco la formula che fa per voi:
=SOMMA(SE(--((C2:C8>50)+(D2:D8>50))>0;D2:D8;0))/SOMMA(--(((C2:C8>50)+(D2:D8>50))>0))
Ricordate, è una formula matriciale, e quindi è necessario premere Ctrl + Maiusc + Invio per inserirla in modo corretto.
Come si può vedere, la formula si compone di 2 parti. Nella prima parte, si utilizza la funzione SE con l’istruzione OR nell’argomento test (C2:C8>50) + (D2:D8>50). Come probabilmente sapete, nelle formule matriciali, il segno più (+) agisce come un operatore o (per maggiori dettagli, si rimanda all’articolo Come usare le matrici e le formule matriciali – Parte 3). Quindi, la prima parte della formula somma i valori nella colonna C se una condizione è soddisfatta. La seconda parte riporta il numero di tali celle, e poi si divide la somma per il conteggio per trovare la media.
E naturalmente, è possibile specificare una condizione diversa per ogni intervallo. Ad esempio, per ottenere la media delle vendite se la colonna C è superiore a 50 o colonna D è superiore a 100, utilizzate le seguenti espressioni: (C2:C8>50) + (D2:D8>100). L’intera formula sarà la seguente:
=SOMMA(SE(--((C2:C8>50)+(D2:D8>100))>0;D2:D8;0))/SOMMA(--(((C2:C8>50)+(D2:D8>100))>0))
Esempio 3. Mediare con logica OR in base a celle vuote/non vuote
La formula per mediare con più criteri OR corrispondenti a celle vuote e non vuote è molto simile a quella che abbiamo appena discusso.
Formula per celle non vuote
La seguente formula matriciale trova la Q.tà media (colonna B) se una data (col. B) o uno stato (col. C) è presente, vale a dire se le colonne B o C non sono vuote.
=SOMMA(SE(--((B2:B8<>"") + (C2:C8<>""))>0;D2:D8;0))/SOMMA(--(((B2:B8<>"") + (C2:C8<>""))>0))
Per rendere la formula più compatta, è possibile concatenare gli intervalli utilizzando una e commerciale (&):
=SOMMA(SE((B2:B8&C2:C8)<>"";D2:D8;0))/SOMMA( --((B2:B8&C2:C8)<>""))
Formula per celle vuote
Per mediare i valori in colonna D corrispondenti a celle vuote in B o in C, occorre sostituire l’operatore “non vuota” (<> “”) con operatore “vuota” (= “”). Concatenare gli intervalli con una e commerciale non funziona in questo caso. Anche questa è una formula matriciale, quindi ricordatevi di premere Ctrl + Maiusc + Invio, non solo Invio.
=SOMMA(SE(--((B2:B8="") + (C2:C8=""))>0;D2:D8;0))/SOMMA(--(((B2:B8="") + (C2:C8=""))>0))
