Calcolo delle medie per data (Microsoft Excel)
Supponiamo di avere un enorme foglio di lavoro che contiene tutte le letture delle precipitazioni per una data località negli ultimi cento anni circa. Nelle celle A2: A42733 hai le date, dal 1 gennaio 1903 al 31 dicembre 2019.
Nelle celle B2: B42733 hai le misurazioni per ogni data. Inoltre, alcune delle misurazioni possono essere zero (se non ci sono precipitazioni per il giorno) o vuote (se non è stata effettuata alcuna lettura quel particolare giorno). Con tutte queste informazioni, si desidera calcolare la piovosità storica media per un dato giorno dell’anno.
Una soluzione prevede l’uso di formule di matrice, come descritto in dettaglio qui:
-
Seleziona tutte le celle nella colonna A che contengono date e assegna a questo intervallo il nome Date.
-
Seleziona tutte le celle corrispondenti nella colonna B; questi contengono i dati sulle precipitazioni per le date nella colonna A. Assegnare a questo intervallo il nome Pioggia.
-
Nella colonna D, a partire dalla cella D2, posizionare tutti i giorni dell’anno. Dovresti finire con D2 attraverso D366 pieni di date.
-
Nella cella E2, inserisci la seguente formula di matrice (termina la formula premendo Maiusc + Ctrl + Invio). Il risultato della formula è la somma di tutte le celle nell’intervallo Rainfall, per la data specificata nella cella D2.
-
Nella cella F2, inserisci la seguente formula di matrice (termina la formula premendo Maiusc + Ctrl + Invio). Il risultato della formula è il numero di celle nell’intervallo Pioggia, per la data nella cella D2, che hanno un valore in esse.
-
Nella cella G2, inserisci la seguente formula regolare. Questa è la media per la data nella cella D2.
-
Seleziona l’intervallo E2: G2 e copia per tutte le date mostrate nella colonna D.
Questo approccio funziona, ma richiede un po ‘di tempo per il calcolo. Questo perché hai effettivamente inserito 822 formule di matrice, ciascuna delle quali controlla oltre 42.000 celle. Questo richiede molto lavoro e di conseguenza potrebbe sembrare che la tua macchina si sia “bloccata” dopo aver completato il passaggio 7. Non si è bloccata; ci vorrà solo un po ‘per completare i calcoli.
Per diminuire il numero di calcoli che devono essere eseguiti, è possibile utilizzare una variazione nei passaggi precedenti. Seguire i passaggi da 1 a 3, come indicato, quindi inserire la seguente formula di matrice nella cella E2:
=AVERAGE(IF((Dates<>0)(Rainfall<>0)(MONTH(Dates)=MONTH(D2))*(DAY(Dates)=DAY(D2)),Rainfall))
È quindi possibile copiare la formula per tutte le date mostrate nella colonna D.
Il risultato di questa formula è la piovosità media effettiva, la stessa mostrata nella colonna G nell’approccio precedente.
È possibile ridurre ulteriormente il sovraccarico di calcolo semplicemente eliminando tutta la tabella che calcola le medie per ogni giorno dell’anno. Con le date e le precipitazioni nelle colonne A e B, segui questi passaggi:
-
Seleziona tutte le celle nella colonna A che contengono date e assegna a questo intervallo il nome Date.
-
Seleziona tutte le celle corrispondenti nella colonna B; questi contengono i dati sulle precipitazioni per le date nella colonna A. Assegnare a questo intervallo il nome Pioggia.
-
Nella cella D2, inserire la data per la quale si desidera controllare la piovosità media. (L’anno non è importante; nel calcolo vengono utilizzati solo il mese e il giorno.)
-
Immettere la seguente formula nella cella E2:
=AVERAGE(IF((Dates<>0)(Rainfall<>0)(MONTH(Dates)=MONTH(D2))*(DAY(Dates)=DAY(D2)),Rainfall))
Questo è tutto. Ora puoi modificare la data nella cella D2 come desideri e la cella E2 indicherà sempre la piovosità media per quella data. La formula nella cella E2 è la stessa della formula utilizzata nell’ultimo approccio; la differenza è che non lo stai calcolando per tutti i giorni di un anno, quindi il calcolo viene eseguito molto più rapidamente.
Un altro approccio prevede l’utilizzo delle funzionalità di filtro di Excel.
Prima di poterli utilizzare correttamente, tuttavia, è necessario creare una colonna che mostri solo il mese e il giorno per ciascuna data nei dati. Usa questa formula nella cella C2:
=MONTH(A2) & "-" & DAY(A2)
Ora, attiva il filtro automatico (visualizza la scheda Dati sulla barra multifunzione | Gruppo Ordina e filtra | Filtro) e utilizza l’elenco a discesa nella parte superiore della nuova colonna per selezionare la data per la quale desideri una media. Si utilizza quindi la seguente formula, collocata in qualsiasi cella desiderata, per mostrare la media delle precipitazioni per la data selezionata:
=SUBTOTAL(1,B:B)
ExcelTips è la tua fonte di formazione economica su Microsoft Excel.
Questo suggerimento (10319) 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 di menu precedente di Excel qui: