Media dei valori per un determinato mese e anno (Microsoft Excel)
George ha un foglio di lavoro che include date (nella colonna A) e valori associati a tali date (nella colonna B). Il foglio di lavoro include i valori degli ultimi anni. Vorrebbe calcolare la media di tutti i valori per un dato mese in un dato anno. Ad esempio, George vorrebbe calcolare la media di tutti i valori per febbraio 2020.
Esistono diversi modi per affrontare questo problema. Un modo è creare una tabella pivot basata sui dati. (Le tabelle pivot sono ottime per aggregare e analizzare enormi quantità di dati.) Puoi facilmente impostare il campo del valore su Media (invece della Somma predefinita) e raggruppare la colonna Date in base alle tue esigenze.
Se preferisci non utilizzare una tabella pivot, ci sono un numero qualsiasi di formule che puoi aggiungere al tuo foglio di lavoro. Ad esempio, la seguente formula utilizza la funzione SUMPRODUCT per calcolare la media:
=SUMPRODUCT((MONTH(A2:A1000)=5)(YEAR(A2:A1000)=2020)(B2:B1000)) / (SUMPRODUCT((MONTH(A2:A1000)=5)(YEAR(A2:A1000)=2020)1))
La formula presume che le date e i valori inizino nella riga 2 (per consentire le intestazioni) e non vadano oltre la riga 1000. Se nei dati non sono presenti date nel mese di febbraio 2020, la formula restituisce un #DIV / 0! errore.
Un altro approccio consiste nell’usare una formula di matrice, come la seguente:
=AVERAGE(IF((MONTH(A2:A1000)=5)*(YEAR(A2:A1000)=2020),B2:B1000))
Questo approccio è più breve della formula SUMPRODUCT, ma devi ricordarti di tenere premuto Ctrl + Maiusc + Invio mentre inserisci la formula.
Otterrai anche la divisione per errore zero se non ci sono dati per il mese e l’anno desiderati.
Un altro approccio ancora consiste nell’utilizzare una delle funzioni di database di Excel, DAVERAGE. Tutto quello che devi fare è impostare una tabella dei criteri che definisca ciò che stai cercando. Supponiamo, ad esempio, che le intestazioni sulle colonne siano qualcosa di originale, come Data (cella A1) e Valore (cella B1).
È possibile impostare una tabella dei criteri in un altro punto, ad esempio D1: E2. La tabella potrebbe assomigliare a questo:
Date Date >1/31/20 <3/1/20
La tabella dei criteri dice che vuoi che DAVERAGE utilizzi qualsiasi cosa in cui la colonna Data contiene una data maggiore di 1/31/20 e una data inferiore a 3/1/20. Ecco la formula:
=DAVERAGE(A1:B1000,"Value",D1:E2)
Il primo parametro definisce il database, il secondo parametro indica che si desidera calcolare la media delle informazioni nella colonna Valore (colonna B) e il terzo parametro indica a DAVERAGE dove si trova la tabella dei criteri.
Un modo abbastanza semplice è applicare il filtro delle date e utilizzare la funzione SUBTOTALE. Inserisci la seguente formula in una cella:
=SUBTOTAL(101,B2:B1000)
Seleziona una cella nell’intervallo di dati e filtra i dati (visualizza la scheda Dati della barra multifunzione e fai clic sullo strumento Filtro). Fare clic sulla freccia di filtraggio nella parte superiore della colonna A, quindi selezionare Data Filters | Filtro personalizzato dall’elenco a discesa. Excel visualizza la finestra di dialogo Filtro automatico personalizzato.
(Vedi figura 1.)
Figura 1. La finestra di dialogo Filtro automatico personalizzato.
Utilizzare i controlli nella finestra di dialogo per specificare che si desidera che i record siano maggiori di 1/31/20 e minori di 3/1/20. Quando si fa clic su OK, vengono visualizzati solo i record entro febbraio 2020 e la formula del totale parziale mostra la media di quei record visibili.
Forse l’approccio più semplice, tuttavia, consiste nell’usare la funzione MEDIA.SE. Consente di calcolare una media di alcuni valori a seconda che un valore associato (la data, in questo caso) soddisfi i criteri specificati. Ecco come appare la formula:
=AVERAGEIFS(B2:B1000,A2:A1000,">31 Jan 2020",A2:A1000,"<01 Mar 2020")
Si noti che questa non è una formula di matrice e non è necessario definire una tabella dei criteri per i criteri: sono incorporati direttamente nella formula.
ExcelTips è la tua fonte di formazione economica su Microsoft Excel.
Questo suggerimento (10671) si applica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 ed Excel in Office 365. Puoi trovare una versione di questo suggerimento per l’interfaccia del menu precedente di Excel qui: