Questo tutorial mostra come utilizzare le funzioni AVERAGEIF e AVERAGEIFS di Excel in Excel e Fogli Google per calcolare la media dei dati che soddisfano determinati criteri.
AVERAGEIF Panoramica della funzione
È possibile utilizzare la funzione AVERAGEIF in Excel per contare le celle che contengono un valore specifico, contare le celle che sono maggiori o uguali a un valore, ecc.
Per utilizzare la funzione Foglio di lavoro Excel AVERAGEIF, selezionare una cella e digitare:
(Notare come appaiono gli input della formula)
Sintassi e argomenti della funzione AVERAGEIF:
=MEDIA.SE (intervallo, criteri, [intervallo_media])
gamma - L'intervallo di celle da contare.
criteri - I criteri che controllano quali celle devono essere contate.
intervallo_medio - [opzionale] Le celle da mediare. Se omesso, viene utilizzato l'intervallo.
Che cos'è la funzione MEDIA.SE?
La funzione AVERAGEIF è una delle funzioni più vecchie utilizzate nei fogli di calcolo. Viene utilizzato per eseguire la scansione di un intervallo di celle verificando un criterio specifico e quindi fornendo la media (ovvero la media matematica) se i valori in un intervallo corrispondono a tali valori. La funzione AVERAGEIF originale era limitata a un solo criterio. Dopo il 2007, è stata creata la funzione AVERAGEIFS che consente una moltitudine di criteri. La maggior parte dell'uso generale rimane lo stesso tra i due, ma ci sono alcune differenze critiche nella sintassi che discuteremo in questo articolo.
Se non l'hai già fatto, puoi rivedere gran parte della struttura simile e degli esempi nell'articolo COUNTIFS.
Esempio di base
Consideriamo questo elenco di vendite registrate e vogliamo conoscere il reddito medio.
Poiché abbiamo avuto una spesa, il valore negativo, non possiamo semplicemente fare una media di base. Invece, vogliamo fare la media solo dei valori maggiori di 0. Il "maggiore di 0" è quello che sarà il nostro criterio in una funzione AVERAGEIF. La nostra formula per affermare questo è
=MEDIA.SE(LA2:LA7, ">0")
Esempio a due colonne
Sebbene la funzione AVERAGEIF originale sia stata progettata per consentirti di applicare un criterio all'intervallo di numeri che desideri sommare, nella maggior parte dei casi dovrai applicare uno o più criteri ad altre colonne. Consideriamo questa tabella:
Ora, se usiamo la funzione AVERAGEIF originale per scoprire quante banane abbiamo in media. Metteremo i nostri criteri nella cella D1 e dovremo fornire l'intervallo che vogliamo media come ultimo argomento, e quindi la nostra formula sarebbe
=MEDIA.SE(LA2:LA7, D1, B2:B7)
Tuttavia, quando i programmatori alla fine si sono resi conto che gli utenti volevano fornire più di un criterio, è stata creata la funzione AVERAGEIFS. Per creare una struttura che funzioni per qualsiasi numero di criteri, AVERAGEIFS richiede che l'intervallo di somma sia elencato per primo. Nel nostro esempio, ciò significa che la formula deve essere
=MEDIA.SE(B2:B7, A2:A7, D1)
NOTA: queste due formule ottengono lo stesso risultato e possono sembrare simili, quindi presta molta attenzione a quale funzione viene utilizzata per assicurarti di elencare tutti gli argomenti nell'ordine corretto.
Lavorare con date, criteri multipli
Quando si lavora con le date in un foglio di calcolo, sebbene sia possibile inserire la data direttamente nella formula, è buona norma avere la data in una cella in modo da poter semplicemente fare riferimento alla cella in una formula. Ad esempio, questo aiuta il computer a sapere che si desidera utilizzare la data 27/05/2020 e non il numero 5 diviso 27 diviso 2022.
Diamo un'occhiata alla nostra prossima tabella che registra il numero di visitatori di un sito ogni due settimane.
Possiamo specificare i punti di inizio e fine dell'intervallo che vogliamo guardare in D2 e E2. La nostra formula quindi per trovare la media del numero di visitatori in questa fascia potrebbe essere:
=MEDIA.SE(L2:B7, A2:A7, ">="&D2, A2:A7, "<="&E2)
Nota come siamo stati in grado di concatenare i confronti di "=" ai riferimenti di cella per creare i criteri. Inoltre, anche se entrambi i criteri venivano applicati allo stesso intervallo di celle (A2: A7), è necessario scrivere l'intervallo due volte, una volta per ciascun criterio.
Più colonne
Quando si utilizzano più criteri, è possibile applicarli allo stesso intervallo dell'esempio precedente oppure applicarli a intervalli diversi. Combiniamo i nostri dati di esempio in questa tabella:
Abbiamo impostato alcune celle in modo che l'utente possa inserire ciò che desidera cercare nelle celle da E2 a G2. Abbiamo quindi bisogno di una formula che sommi il numero totale di mele raccolte a febbraio. La nostra formula è simile a questa:
=MEDIA.SE(C2:C7, B2:B7, ">="&F2, B2:B7, "<="&G2, LA2:LA7, E2)
AVERAGEIFS con logica di tipo OR
Fino a questo punto, gli esempi che abbiamo usato sono stati tutti un confronto basato su AND, in cui stiamo cercando righe che soddisfano tutti i nostri criteri. Ora, considereremo il caso in cui desideri cercare la possibilità che una riga soddisfi uno o l'altro criterio.
Diamo un'occhiata a questo elenco di vendite:
Vorremmo sommare le vendite medie sia per Adam che per Bob. In primo luogo, una rapida discussione su come prendere le medie. Se hai un numero dispari di cose come 3 voci per Adam e 2 per Bob, non puoi semplicemente prendere la media delle vendite di ogni persona. Questo è noto come prendere la media delle medie e si finisce per dare una ponderazione ingiusta all'elemento che ha poche voci. Se questo è il caso dei tuoi dati, dovresti calcolare una media in modo "manuale": prendi la somma di tutti i tuoi articoli divisa per il conteggio dei tuoi articoli. Per rivedere come farlo, puoi consultare gli articoli qui:
Ora, se il numero di voci è lo stesso, come nella nostra tabella, allora hai un paio di opzioni che puoi fare. Il più semplice è aggiungere due AVERAGEIFS insieme, in questo modo, e quindi dividere per 2 (il numero di elementi nel nostro elenco)
=(MEDIA.SE(B2:B7, A2:LA7, "Adamo")+MEDIA.SE(B2:B7, LA2:LA7, "Bob"))/2
Qui, abbiamo fatto calcolare al computer i nostri punteggi individuali e poi li abbiamo sommati.
La nostra prossima opzione è utile quando hai più intervalli di criteri, in modo tale da non dover riscrivere l'intera formula ripetutamente. Nella formula precedente, abbiamo detto manualmente al computer di aggiungere due diversi AVERAGEIFS insieme. Tuttavia, puoi anche farlo scrivendo i tuoi criteri all'interno di un array, in questo modo:
=MEDIA(MEDIA.SE(B2:B7, A2:A7, {"Adam", "Bob"}))
Guarda come è costruito l'array all'interno delle parentesi graffe. Quando il computer valuterà questa formula, saprà che vogliamo calcolare una funzione AVERAGEIFS per ogni elemento nel nostro array, creando così un array di numeri. La funzione MEDIA esterna prenderà quindi quell'array di numeri e lo trasformerà in un singolo numero. Passando attraverso la valutazione della formula, sarebbe simile a questo:
=MEDIA(MEDIA.SE(B2:B7, A2:A7, {"Adam", "Bob"})) =MEDIA(13701, 21735) =17718
Otteniamo lo stesso risultato, ma siamo stati in grado di scrivere la formula in modo un po' più succinto.
Trattare con gli spazi vuoti
A volte il tuo set di dati avrà celle vuote che devi trovare o evitare. Impostare i criteri per questi può essere un po' complicato, quindi diamo un'occhiata a un altro esempio.
Nota che la cella A3 è veramente vuota, mentre la cella A5 ha una formula che restituisce una stringa di lunghezza zero di "". Se vogliamo trovare la media totale di veramente celle vuote, useremmo un criterio di "=" e la nostra formula sarebbe simile a questa:
=MEDIA.SE(B2:B7,A2:A7,"=")
D'altra parte, se vogliamo ottenere la media per tutte le celle che appaiono visivamente vuote, cambieremo i criteri in "" e la formula sarà simile a
=MEDIA.SE(B2:B7,A2:A7,"")
Facciamo un giro: cosa succede se vuoi trovare la media delle celle non vuote? Sfortunatamente, il design attuale non ti consentirà di evitare la stringa di lunghezza zero. Puoi usare un criterio di "", ma come puoi vedere nell'esempio, include ancora il valore dalla riga 5.
=MEDIA.SE(B2:B7,A2:A7,"")
Se non devi contare le celle contenenti stringhe di lunghezza zero, ti consigliamo di utilizzare la funzione LUNGHEZZA all'interno di un SUMPRODUCT
MEDIA SE in Fogli Google
La funzione AVERAGEIF funziona esattamente allo stesso modo in Fogli Google come in Excel:
