Calcolare la media in base a più condizioni con AVERAGEIFS

AVERAGEIFS calcola la media dei valori che soddisfano diverse condizioni contemporaneamente. Invece di calcolare la media di ogni vendita, può chiedere Qual è stata la vendita media della regione Est a gennaio?

Completa il trio: SUMIFS calcola i totali, COUNTIFS i conteggi e AVERAGEIFS le medie, tutti con lo stesso approccio basato su più criteri. Se ne conosce una, conosce quasi tutte e tre.

AVERAGEIFS rispecchia esattamente SUMIFS. I valori di cui calcolare la media vengono per primi, seguiti dalle coppie di criteri.

• average_range — i numeri di cui calcolare la media
• criteria_range1, criteria1
• criteria_range2, criteria2

Dietro le quinte, somma i valori corrispondenti e divide il risultato per il numero di corrispondenze: in pratica, esegue SUMIFS diviso COUNTIFS in un'unica funzione ordinata.

Con la colonna A come Regione, la colonna B come Mese e la colonna C come Importo, ecco la media delle vendite della regione Est a gennaio.

Gli importi in C:C sono i valori di cui calcolare la media. Le due coppie di criteri filtrano le righe, limitandole alla regione Est e a gennaio prima di calcolare la media.

È possibile filtrare i valori di cui calcolare la media in base alla loro grandezza. Supponiamo di voler calcolare la media solo delle vendite elevate della regione Est, cioè quelle superiori a 100.

Qui la colonna C viene usata sia come average_range sia come criteria_range. AVERAGEIFS può riutilizzare senza problemi la stessa colonna per entrambi i ruoli.

Per creare un report che gli utenti possano controllare, è possibile fare riferimento alle celle invece di digitare i valori. Inserire la regione in F1 e un importo minimo in F2.

Per il confronto, unire l'operatore alla cella: ">"&F2 significa maggiore di qualunque valore contenuto in F2. Le corrispondenze basate su testo semplice, come quella della regione, non richiedono alcun operatore: basta il riferimento alla cella.

La principale insidia di AVERAGEIFS è la seguente: se nessuna riga corrisponde a tutte le condizioni, non c'è nulla di cui calcolare la media, quindi viene restituito l'errore #DIV/0!.

Questo comportamento è diverso da quello di SUMIFS, che restituisce 0, e di COUNTIFS, che restituisce 0. La media di zero elementi non è definita, quindi Excel restituisce un errore invece di fare un'ipotesi.

Racchiuda la formula in IFERROR per visualizzare un valore comprensibile quando non ci sono corrispondenze. Invece del poco leggibile #DIV/0!, l'utente vedrà un trattino o un messaggio.

In questo modo i dashboard mantengono un aspetto ordinato anche quando una combinazione di filtri non contiene dati. Quando calcola la media di dati filtrati, consideri sempre anche il caso vuoto.

Una precisazione importante: AVERAGEIFS ignora le celle vuote nell'average_range: non vengono conteggiate né nel totale né nel divisore. Una cella che contiene 0, invece, è un numero reale e viene inclusa.

Se gli zeri sono segnaposto per i dati mancanti, abbasseranno la media. Quando appropriato, consideri l'aggiunta di un criterio come C:C, "<>0" per escluderli.

È possibile aggiungere tutte le condizioni necessarie. Con la colonna D come Venditore, trovi la media delle vendite che appartengono alla regione Est, sono state effettuate a gennaio e sono state realizzate da Maria.

Ogni coppia aggiunta rende il filtro più selettivo. Poiché AVERAGEIFS usa la logica AND, solo le righe che soddisfano tutte e tre le condizioni contribuiscono alla media.

Presti attenzione all'ordine dei due gruppi di argomenti:

• AVERAGEIF: range, criteria, [average_range] — prima viene l'intervallo da verificare, mentre average_range è facoltativo e viene per ultimo.
• AVERAGEIFS: average_range, range1, crit1, ... — average_range viene sempre per primo.

Come per le altre funzioni, scegliere per impostazione predefinita la versione plurale ...IFS offre uno schema coerente per SUM, COUNT e AVERAGE.

AVERAGEIFS è alla base di molti riepiloghi affiancati. Elenchi i nomi delle regioni nella colonna F, quindi calcoli la media delle vendite di ogni regione con un'unica formula da copiare, bloccando la colonna dei criteri.

Con $C:$C come average_range, $A:$A come criteria_range e $F2 come cella relativa della regione, copiando la formula verso il basso otterrà immediatamente una media per ogni regione.

Ricordi che cosa distingue il comportamento di AVERAGEIFS da quello delle funzioni simili.

Ora è possibile calcolare la media dei valori in base a più condizioni:

• AVERAGEIFS(average_range, range1, crit1, ...) — average_range viene per primo.
• Se non ci sono corrispondenze, viene restituito #DIV/0!: lo gestisca con IFERROR.
• Le celle vuote vengono ignorate, ma gli zeri vengono inclusi; se necessario, li escluda con "<>0".
• Utilizzi ">"&F1 per definire limiti numerici dinamici.

Prossimo argomento: gestire gli intervalli di date nelle funzioni con criteri.