Calcolare una media basata su più criteri

Davide ha una tabella con diverse migliaia di righe. La colonna A contiene le date, la colonna B contiene i nomi e la colonna C contiene gli importi. Deve calcolare la media degli ultimi 10 importi (colonna C) per un determinato nome (colonna B) che si verificano prima di una determinata data (colonna A). Si chiede se c’è un modo per farlo in un’unica formula o se ha bisogno di usare valori intermedi in colonne di supporto aggiuntive. La parte più importante, per Davide, è capire gli ultimi 10 importi che si verificano cronologicamente prima di una data specificata.

Se vuoi solo trovare le ultime date in ordine cronologico per un dato nome, puoi usare 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 la presenza di dati nell’intervallo A2:C7870, configurati secondo le richieste di Davide. Si basa anche sul nome di una persona inserito nella cella E1. Restituisce una media degli ultimi 10 valori per quella persona.

Davide, tuttavia, voleva una media degli ultimi 10 valori per quella persona prima di una data limite specificata. Il modo più semplice per trovare questo usando una formula è fare affidamento sulla funzione LET, come mostrato qui:

=LET(Date; $A$1:$A$7870, Nomi; $B$1:$B$7870; Importi; $C$1:$C$7870; NomeDaCercare; $E$1; DataDaCercare; $F$1; Quanti; $G$1; DataTest; FILTRO(Importi; Date <= DataDaCercare); NomeTest; FILTRO(Nomi; Date <= DataDaCercare); ElencoImporti; FILTRO(DataTest; NomeTest = NomeDaCercare); MEDIA(INDICE(ElencoImporti; SEQUENZA(Quanti; 1; CONTA.NUMERI(ElencoImporti); -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 viene utilizzata per definire nomi e valori, in questo modo:

Date; $A$1:$A$7870;
Nomi; $B$1:$B$7870;
Importi; $C$1:$C$7870;
NomeDaCercare; $E$1;
DataDaCercare; $F$1;
Quanti; $G$1;
DataTest; FILTRO(Importi; Date <= DataDaCercare);
NomeTest; FILTRO(Nomi; Date <= DataDaCercare);
ElencoImporti; FILTRO(DataTest; NomeTest = NomeDaCercare)

Ognuna di queste righe è ciò a cui Excel si riferisce come coppia nome/valore. Ad esempio, Date è il nome e $A$1:$A$7870 è l’intervallo di celle assegnato a quel nome. Si noti che ogni coppia nome/valore successiva può fare affidamento su nomi definiti in precedenza, come in NomeTest che viene definito come i valori restituiti da FILTRO(Nomi; Date = DataDaCercare). Ciascuno di questi nomi può quindi essere utilizzato nella parte successiva della funzione:

MEDIA(INDICE(ElencoImporti; SEQUENZA(Quanti; 1; CONTA.NUMERI(ElencoImporti); -1) ) )

Questa è la parte 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 di interruzione e la terza è la cella G1 che contiene quanti valori si desidera mediare.

Le funzioni LET, FILTRO e SEQUENZA sono disponibili solo a partire da Excel 2021 oppure su Microsoft 365.

Se preferisci, puoi anche utilizzare l’ordinamento e il filtraggio per ottenere le informazioni desiderate. Segui questi passaggi generali:

1. Se non l’hai già fatto, formatta i tuoi dati come una tabella (seleziona una cella nei dati e fai clic su Formatta come tabella nella scheda Home della barra multifunzione).
2. Ordina la colonna Data in ordine crescente e filtrala per mostrare solo le date prima della data limite.
3. Filtra la colonna Nome per mostrare solo i record per il nome desiderato.
4. 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 per le ultime 10 righe visibili. Ad esempio, nei miei dati di test questo è finito per 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 è la soluzione: