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 maggio 2011.

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)=2011)(B2:B1000)) / (SUMPRODUCT((MONTH(A2:A1000)=5)(YEAR(A2:A1000)=2011)1))

La formula presuppone che le date e i valori inizino nella riga 2 (per consentire le intestazioni) e non superino la riga 1000. Se nei dati non sono presenti date nel mese di maggio 2011, 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)=2011),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 >4/30/11     <6/1/11

La tabella dei criteri indica che si desidera che DAVERAGE utilizzi qualsiasi cosa in cui la colonna Data contiene una data maggiore di 4/30/11 e una data inferiore a 6/1/11. 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 attiva il filtro automatico (scegli Dati | Filtro | Filtro automatico). Fare clic sulla freccia del filtro nella parte superiore della colonna A, quindi selezionare Filtro personalizzato dall’elenco a discesa. Excel visualizza la finestra di dialogo Filtro automatico personalizzato. (Vedi figura 1.)

image

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 4/30/11 e minori di 6/1/11. Quando si fa clic su OK, vengono visualizzati solo i record entro maggio 2011 e la formula del totale parziale mostra la media di quei record visibili.

ExcelTips è la tua fonte di formazione economica su Microsoft Excel.

Questo suggerimento (10670) si applica a Microsoft Excel 97, 2000, 2002 e 2003.

Puoi trovare una versione di questo suggerimento per l’interfaccia a barra multifunzione di Excel (Excel 2007 e versioni successive) qui:

valori medi per un dato mese e anno.