Formula generica
=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))
Sommario
Per calcolare la media mensile, puoi utilizzare una formula basata sulla funzione MEDIA.SE, con l'aiuto della funzione EOMONTH. Nell'esempio mostrato, la formula in F4 è:
=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Questa formula utilizza gli intervalli denominati "importi" (G5: D104) e "date" (C5: C104).
Spiegazione
La funzione MEDIA.SE può calcolare la media degli intervalli in base a più criteri. In questo caso, configuriamo AVERAGEIFS per la media degli importi in base al mese utilizzando due criteri: (1) date di corrispondenza maggiori o uguali al primo giorno del mese, (2) date di corrispondenza inferiori o uguali all'ultimo giorno del mese. Se abbiamo inserito le date per gennaio 2016 nella formula utilizzando la funzione DATA, apparirebbe così.
=AVERAGEIFS(amounts,dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,1,31))
Ma non vogliamo inserire date nel codice, vogliamo che Excel generi queste date per noi. Normalmente, questo è un problema, perché se aggiungi i nomi dei mesi come testo (ad esempio "Gennaio", "Febbraio", "Marzo", ecc.) Nella colonna F devi fare altri guai per creare date che puoi usare per i criteri .
Tuttavia, in questo caso, utilizziamo un semplice trucco per semplificare le cose: nella colonna F, invece di digitare i nomi dei mesi, aggiungiamo le date effettive per il primo di ogni mese (1/1/2016, 2/1/2016, 3 / 1/2016, ecc.) E utilizza un formato data personalizzato ("mmm") per visualizzare i nomi dei mesi.
Ciò semplifica la creazione dei criteri necessari per AVERAGEIFS. Per abbinare date maggiori o uguali al primo del mese, usiamo:
">="&E4
E per abbinare date inferiori o uguali all'ultimo giorno del mese, utilizziamo:
"<="&EOMONTH(E4,0)
EOMONTH restituisce automaticamente l'ultimo giorno dello stesso mese perché forniamo zero per l'argomento mesi.
Nota: la concatenazione con una e commerciale (&) è necessaria quando si creano criteri basati su un riferimento di cella.
Soluzione tabella pivot
Una tabella pivot è un'ottima soluzione quando è necessario riepilogare i dati per anno, mese, trimestre e così via, poiché le tabelle pivot forniscono controlli per il raggruppamento automatico per data. Per un confronto affiancato di formule e tabelle pivot, guarda questo video: Perché le tabelle pivot.
