Calcolare la media in base a criteri con AVERAGEIF

Ha già calcolato i totali con SUMIF e i conteggi con COUNTIF. Il terzo componente della famiglia è AVERAGEIF, che calcola la media solo dei valori che soddisfano una condizione.

Invece di calcolare la media di tutte le vendite, può calcolare solo quella delle vendite della regione East o dei punteggi superiori a 50. In un unico passaggio combina un filtro e una media in modo semplice e chiaro.

AVERAGEIF utilizza la stessa struttura in tre parti di SUMIF:

• range le celle da verificare
• criteria la condizione
• average_range le celle di cui calcolare la media

Esamina range, individua le corrispondenze e poi calcola la media delle celle corrispondenti in average_range.

Con le regioni in A2:A10 e gli importi in B2:B10, per calcolare la vendita media della regione East verifichi la colonna A cercando East e calcoli la media dei valori corrispondenti nella colonna B.

In pratica, l'operazione equivale a un SUMIF diviso per un COUNTIF, ma AVERAGEIF racchiude tutto in un'unica formula leggibile.

Se la colonna che verifica è anche quella di cui vuole calcolare la media, può omettere il terzo argomento. Per calcolare la media solo dei punteggi nell'intervallo B2:B10 superiori a 70:

In questo caso la colonna B viene sia verificata sia utilizzata per la media. Quando omette average_range, il foglio di calcolo usa range per entrambe le operazioni.

Come per le altre funzioni IF, memorizzi la condizione in una cella per ottenere maggiore flessibilità. Se D1 contiene il nome di una regione, faccia riferimento a D1 nel criterio.

Ora una sola cella controlla la media. Modificando D1 da East a West, il risultato viene ricalcolato immediatamente: è ideale per una tabella riepilogativa o per un selettore in un dashboard.

AVERAGEIF accetta gli operatori tra virgolette, proprio come le funzioni simili. Per calcolare la media solo delle vendite pari o superiori a 100:

• ">=100" 100 o più
• "<50" sotto 50
• "<>0" escludendo gli zeri

L'ultimo criterio è particolarmente utile perché calcola la media ignorando le voci pari a zero, che altrimenti abbasserebbero il valore medio.

Per utilizzare una soglia contenuta in una cella, concateni l'operatore con la cella usando una e commerciale. Se D1 contiene una soglia, calcoli la media di tutti i valori superiori a essa.

& costruisce il testo della condizione a partire da ">" e dal valore in D1. È lo stesso metodo di concatenazione utilizzato con SUMIF e COUNTIF.

AVERAGEIF presenta un problema che le altre funzioni non hanno. Se nessuna riga corrisponde, non c'è nulla per cui dividere e si verifica un errore #DIV/0!.

Ad esempio, calcolare la media di una regione che non esiste nei dati restituisce questo errore. Il foglio di calcolo sta indicando che il filtro non ha trovato alcuna cella, non che la formula sia errata.

Racchiuda la formula in IFERROR per mostrare un messaggio comprensibile invece di #DIV/0! quando non ci sono corrispondenze.

Ora una regione vuota mostrerà il testo Nessun dato invece di un errore preoccupante. In questo modo i report mantengono un aspetto curato anche quando mancano alcune categorie.

AVERAGEIF calcola la media solo delle celle contenenti numeri tra quelle corrispondenti. Le celle realmente vuote in average_range vengono ignorate, quindi non vengono trattate come zero.

Questo è importante: un valore mancante non sposterà la media verso lo zero come farebbe uno 0 effettivo. Se vuole escludere anche gli zeri, aggiunga il criterio "<>0" alla colonna dei valori.

Crei un riepilogo ordinato: elenchi ogni categoria nelle celle D2, D3 e D4, quindi scriva una formula AVERAGEIF che faccia riferimento alla cella con l'etichetta e la copi verso il basso.

Ora ogni riga mostra il valore medio della propria categoria, affiancato al totale SUMIF e al conteggio COUNTIF delle lezioni precedenti. Insieme formano un report compatto, interamente basato su formule.

Verifichi la sua comprensione del comportamento di AVERAGEIF.

Ora può calcolare medie condizionali con AVERAGEIF. Punti chiave:

• L'ordine è range, criteria, average_range; ometta l'ultimo argomento per calcolare la media dello stesso intervallo verificato.
• Supporta testo, numeri e operatori come ">=100".
• L'assenza di corrispondenze produce un errore #DIV/0!, quindi lo gestisca con IFERROR.
• Le celle vuote vengono ignorate, non trattate come zero.

Ora imparerà a trovare corrispondenze parziali nel testo usando i caratteri jolly.