John ha un enorme foglio di lavoro che include dati giornalieri che coprono circa sessant’anni. Vuole trovare una formula che calcolerà i valori mediani per ogni mese nel periodo di tempo coperto dai dati.

Prima di proporre una soluzione, è bene formulare alcune ipotesi. Ai fini di questo suggerimento, supponiamo che i dati giornalieri si trovino nelle colonne A e B. Nella colonna A ci sono le date associate ai dati e nella colonna B ci sono i valori dei dati effettivi per ciascuna di quelle date. Inoltre, le celle A1 e B1 contengono le intestazioni per ogni colonna. Ciò significa che i dati effettivi sono approssimativamente nell’intervallo A2: B22000.

Per semplificare l’utilizzo delle formule, è necessario definire i nomi per i dati in entrambe le colonne A e B. Selezionare l’intervallo di date (ad esempio A2: A22000) e assegnargli un nome come AllDates. Seleziona l’intervallo di dati corrispondenti (ad esempio, B2: B22000) e utilizza lo stesso processo per creare un nome come AllData.

È possibile utilizzare formule di matrice per calcolare i valori mediani effettivi. Ciò comporta l’impostazione di un’altra tabella di dati per contenere le mediane. Posizionare titoli come “Mese” nella cella E1 e “Mediana” nella cella F1. Nella cella E2 inserisci il primo giorno del primo mese del tuo set di dati, ad esempio 1/1/1940. Nella cella E3 inserire una data che è un mese dopo, ad esempio 2/1/1940. Seleziona queste due celle (E2: E3) e trascina il quadratino di riempimento verso il basso per il numero di mesi che desideri nella tabella dei dati.

Se non ci sono spazi vuoti nei tuoi sessant’anni di dati, inserisci la seguente formula nella cella F2:

=MEDIAN(IF(DATE(YEAR(AllDates),MONTH(AllDates),1)=E2,AllData))

Finalizza la formula premendo Ctrl + Maiusc + Invio, che indica a Excel che si tratta di una formula di matrice. È quindi possibile copiare la formula in F2 in ciascuna delle celle nella colonna F che ha un mese corrispondente nella colonna E. La formula analizza le date nella colonna B e se l’anno e il mese sono uguali a qualsiasi data inserita nella cella E2 , quindi la mediana viene calcolata da tutti i punti dati corrispondenti.

Se ci sono spazi vuoti nei tuoi sessant’anni di dati (una data nella colonna A senza un valore corrispondente nella colonna B), la formula considera lo spazio vuoto come se fosse un valore zero. Se sono presenti spazi vuoti, ciò potrebbe causare mediane distorte. Per aggirare questo problema, potresti usare una diversa formula di matrice che controlla e ignora i valori vuoti:

=MEDIAN(IF((DATE(YEAR(AllDates),MONTH(AllDates),1)=E2)*ISNUMBER(AllData),AllData))

C’è un avvertimento sull’utilizzo di formule di matrice in questo modo. Se hai sessant’anni di dati, con circa 22.000 valori individuali, allora sono ancora molti mesi: circa 720. Ciò significa che stai creando 720 formule di matrice, ciascuna delle quali analizza 22.000 valori di dati per arrivare a una risposta. Questo è un sacco di calcoli in corso, quindi noterai un rallentamento nella reattività di Excel ogni volta che ricalcola il foglio di lavoro.

Se la lentezza diventa un problema, potresti considerare di riorganizzare i tuoi dati originali in modo che ogni riga del foglio di lavoro rappresenti un singolo mese. La colonna A potrebbe contenere il mese per la riga (1/1/1940, 2/1/1940, 3/1/1940, ecc.) E le colonne B: AF sarebbero giorni da 1 a 31 per ogni mese. Le celle intersecanti nella tabella potrebbero quindi contenere il punto dati per ogni giorno del mese ed è possibile utilizzare la funzione MEDIANA nella colonna AG per calcolare la mediana per ogni mese. Ciò si traduce ancora in 720 formule, ma si tratta di formule regolari che ciascuna deve elaborare solo un mese di dati anziché le formule di matrice che devono elaborare sessant’anni di dati. Il risultato sono calcoli molto più veloci.

Ovviamente, per la maggior parte delle persone l’idea di riorganizzare una quantità così grande di dati è sufficiente per tenerti sveglio la notte. Invece, puoi adottare un approccio completamente diverso all’analisi dei dati. Questo approccio è possibile perché una mediana è una funzione statistica molto facile da calcolare manualmente. È sufficiente ordinare il set di dati e, se il numero di elementi nel set di dati è dispari, selezionare l’elemento centrale. Se il numero di elementi è pari, prendi la media dei due elementi centrali.

Per prepararsi ad analizzare i dati, ci sono un paio di cose da fare.

Innanzitutto, sarà utile avere un modo per identificare in modo univoco il mese di ogni punto dati. Nella cella C2 inserire la seguente formula:

=100*Year(A2)+Month(A2)

Ciò si traduce in un valore come 194001, 194002, 194003, ecc. Viene memorizzato nella colonna C. Questo è il valore del mese univoco. Ora, dovresti ordinare i dati per colonna C e poi per colonna B. Entrambi gli ordinamenti dovrebbero essere in ordine crescente, in modo da finire con i tuoi dati prima ordinati per anno / mese e poi per valore entro l’anno / mese.

Successivamente è necessario aggiungere i subtotali ai dati. Scegli Subtotali dal menu Dati, che visualizza la finestra di dialogo Subtotale. Si desidera aggiungere un totale parziale a ogni modifica nella colonna C.La funzione da utilizzare è Conteggio e si desidera aggiungere il totale parziale alla colonna B.Quando si fa clic su OK, si ottengono 720 totali parziali, uno per ogni mese nei dati intervallo, ognuno dei quali mostra un conteggio del numero di elementi di dati presenti in quel mese.

Per ottenere le mediane, aggiungi una formula alla cella D2:

IF(RIGHT(B2,5)="Count", IF(MOD(C2,2)=1, INDIRECT("B"&(ROW()-1)-C2/2+1/2), (INDIRECT("B"&(ROW()-1)-C2/2)+INDIRECT("B"&(ROW()-1)-C2/2+1))/2), "")

La formula esamina il contenuto della cella B2 e se contiene la parola “Conteggio”, sa che si tratta di una riga del totale parziale. In tal caso, verifica se il conteggio nella cella C2 è pari o dispari. Se è dispari, la funzione INDIRETTO viene utilizzata per acquisire qualunque sia il valore mediano per il mese. Se è pari, i due valori medi del mese vengono sommati e divisi a metà.

Il risultato è che ora hai valori mediani per ogni mese, nella stessa riga dei tuoi subtotali. Quindi, puoi comprimere la struttura per i dati (fai clic sui segni più nell’area della struttura a sinistra dei tuoi dati)

in modo da mostrare solo quelle righe del totale parziale.

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

Questo suggerimento (12727) 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:

Ricavare i valori mediani mensili.