Davide ha una tabella con un paio di migliaia di righe. La colonna A contiene le date, la colonna B contiene i nomi e la colonna C contiene i numeri. Deve calcolare la media degli ultimi 10 numeri (colonna C) per un determinato nome (colonna B) che si verificano prima di una determinata data (colonna A). Si chiede se esista un modo per farlo in un’unica formula o se è necessario utilizzare valori intermedi nelle colonne di supporto. La parte difficile, per Davide, è discriminare gli ultimi 10 numeri che si verificano in ordine cronologico prima di una data specifica.
Se volessi solo trovare le ultime date cronologiche per un determinato nome, potresti utilizzare la funzione MEDIA.PIÙ.SE, che è relativamente semplice:
=MEDIA.PIÙ.SE($C$2:$C$7870;$B$2:$B$7870;$E$1;$A$2:$A$7870;">="&GRANDE(SE($B$2:$B$7870=E$1;$A$2:$A$7870;"ND");10))
Questa formula presuppone che siano presenti dati nell’intervallo A2:C7870, configurati come suggerisce Davide. Si basa anche sul nome di una persona inserito nella cella E1. Restituisce una media degli ultimi 10 valori per quella persona.
David, tuttavia, voleva una media degli ultimi 10 valori per quella persona prima di una data limite specificata. Il modo più semplice per ottenerla utilizzando una formula è fare affidamento sulla funzione LET, come mostrato qui:
=LET(Date; $A$2:$A$7870; Nomi; $B$2:$B$7870; Importi; $C$2:$C$7870; NomeDaCercare; $E$1; DataLimite; $F$1; Quanti; $G$1; FiltroDate; FILTRO(Importi; Date <= DataLimite); FiltroNome; FILTRO(Nomi; Date <= DataLimite); FiltroImporti; FILTRO(FiltroDate; FiltroNome = NomeDaCercare); MEDIA(INDICE(FiltroImporti; SEQUENZA(Quanti; 1; CONTA.NUMERI(FiltroImporti); -1) ) ) )
È più complessa della precedente formula basata su MEDIA.PIÙ.SE, ma può essere compresa senza troppe difficoltà. La maggior parte dei parametri della funzione LET vengono utilizzati per definire nomi e valori, in questo modo:
Date; $A$2:$A$7870; Nomi; $B$2:$B$7870; Importi; $C$2:$C$7870; NomeDaCercare; $E$1; DataLimite; $F$1; Quanti; $G$1; FiltroDate; FILTRO(Importi; Date <= DataLimite); FiltroNome; FILTRO(Nomi; Date <= DataLimite); FiltroImporti; FILTRO(FiltroDate; FiltroNome = NomeDaCercare)
Ognuno di questi è ciò a cui Excel fa riferimento come coppia nome/valore. Ad esempio, Date è il nome e $A$2:$A$7870 è l’intervallo di celle assegnato a quel nome. Tieni presente che ogni coppia nome/valore successiva può fare affidamento sui nomi definiti in precedenza, come in FiltroNome definito come i valori restituiti da FILTRO(Nomi; Date <= DataLimite). Ciascuno di questi nomi può quindi essere utilizzato nella parte successiva della funzione:
MEDIA(INDICE(FiltroImporti; SEQUENZA(Quanti; 1; CONTA.NUMERI(FiltroImporti); -1) ) ) )
Questa parte è ciò che effettivamente restituisce la media. Affinché la formula funzioni, è necessario impostare tre parametri. La prima è la cella E1 che contiene il nome della persona desiderata, la seconda è la cella F1 che contiene la data limite e la terza è la cella G1 che contiene il numero di valori di cui vuoi calcolare la media.
Se preferisci, puoi anche utilizzare l’ordinamento e il filtraggio per ottenere le informazioni desiderate. Segui questi passaggi generali:
Se non lo hai già fatto, formatta i tuoi dati come una tabella (seleziona una cella nei tuoi dati e fai clic su Formatta come tabella nella scheda Home della barra multifunzione).
Ordina la colonna Data in ordine crescente e filtrala per mostrare solo le date precedenti alla data limite.
Filtra la colonna Nome per mostrare solo i record del nome desiderato.
Inserisci questa formula in una cella sotto l’ultimo record nella colonna C:
=SUBTOTALE(101; Intervallo)
Assicurati di sostituire Intervallo con l’intervallo di celle delle ultime 10 righe visibili. Ad esempio, nei miei dati di test questo è risultato essere l’intervallo C7737:C7797 (ho dovuto determinare manualmente l’intervallo contando il numero di righe non filtrate). La funzione SUBTOTALE che utilizza il parametro 101 restituisce la media delle righe non filtrate in quell’intervallo di celle.
Se preferisci una soluzione basata su macro, questa è piuttosto utile:
Function AvgNameBeforeDate(rRaw As Range, sName As String, dCutOff As Date) As Double
Dim rNames As Range
Dim rDates As Range
Dim rNums As Range
Dim dblAccum As Double
Dim iCnt As Integer
Set rDates = rRaw.Columns(1)
Set rNames = rRaw.Columns(2)
Set rNums = rRaw.Columns(3)
' Ordina i dati
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add2 Key:=rNames, SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add2 Key:=rDates, SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange rRaw
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' Torna indietro nei dati ordinati
dblAccum = 0
iCnt = 0
For J = rNames.Rows.Count To 1 Step -1
If rNames.Cells(J) = sName Then
' Trova il nome desiderato
If rDates.Cells(J) < dCutOff Then
' Prima della data limite
dblAccum = dblAccum + rNums.Cells(J)
iCnt = iCnt + 1
If iCnt = 10 Then
' Ora ha accumulato 10 valori
' Calcola la media e esce
AvgNameBeforeDate = dblAccum / iCnt
Exit Function
End If
End If
End If
Next J
' Restituisce 0 perché non arriva a 10 valori per la media
AvgNameBeforeDate = 0
End Function
Questa è una funzione definita dall’utente, quindi la utilizzeresti nel foglio di lavoro in questo modo:
=AvgNameBeforeDate(A2:C7870;"Sara";"1/1/2021")
Tieni presente che ci sono tre parametri: l’intervallo da analizzare (senza righe di intestazione), il nome che desideri utilizzare e la data limite. Tieni presente che la funzione ordina i tuoi dati (ciò che specifichi nel primo parametro), quindi c’è un effetto su tali dati. Se non vuoi che i tuoi dati siano ordinati, dovresti invece fare affidamento sulla formula LET precedente, tenendo presente che le funzioni LET, FILTRO e SEQUENZA sono disponibili solo su Microsoft 365 ed Excel 2021.
