Formula generica
(=MAX(IF(TEXT(dates,"ddd")="Mon",values)))
Sommario
Per trovare il valore massimo in un determinato giorno della settimana (ad esempio lunedì, martedì, mercoledì, ecc.) È possibile utilizzare una semplice formula di matrice basata sulle funzioni MAX, SE e TESTO. Nell'esempio mostrato, la formula nella cella F5 è:
=MAX(IF(TEXT(dates,"ddd")=F4,values))
Dove le date (B5: B15) e i valori (C5: C15) sono intervalli denominati.
Nota: questa è una formula di matrice e deve essere inserita con Control + Maiusc + Invio.
Spiegazione
Lavorando dall'interno verso l'esterno, la funzione TESTO viene utilizzata per estrarre un valore del giorno della settimana per ogni data:
=TEXT(dates,"ddd")
Ciò si traduce in una matrice come questa:
("Mon";"Tue";"Wed";"Thu";"Fri";"Mon";"Tue";"Wed";"Thu";"Fri";"Mon")
che viene poi confrontato con il testo in F4, "Mon". Il risultato è un altro array, che contiene solo valori TRUE e FALSE:
(TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE)
Nota che ogni TRUE corrisponde a un lunedì. Questo array viene restituito all'interno della funzione IF come test logico. Agisce come un filtro per escludere i valori negli altri giorni della settimana. Il risultato finale di IF, che viene restituito direttamente alla funzione MAX, è simile a questo:
=MAX((85;FALSE;FALSE;FALSE;FALSE;94;FALSE;FALSE;FALSE;FALSE;52))
MAX ignora automaticamente i valori FALSE e restituisce il valore rimanente più alto, 94.
Con AGGREGATO
Per una formula leggermente più geniale che non richiede control + shift + enter, puoi usare la funzione AGGREGATE in questo modo:
=AGGREGATE(14,6,values/(TEXT(dates,"ddd")=F4),1)
Questa è la formula utilizzata nella cella F6 nell'esempio mostrato. Qui forniamo AGGREGATO 14 per l'argomento della funzione (GRANDE) e 6 per l'argomento dell'opzione (ignora gli errori). Quindi creiamo un'espressione logica utilizzando la funzione TESTO per controllare tutte le date per il lunedì. Il risultato di questa operazione è un array di valori TRUE / FALSE, che diventano il denominatore dei valori originali. Quando viene utilizzato in un'operazione matematica, FALSE restituisce zero e genera un # DIV / 0! errore. TRUE restituisce 1 e restituisce il valore originale. La matrice finale di valori ed errori agisce come un filtro. AGGREGATO ignora tutti gli errori e restituisce il più grande (massimo) dei valori sopravvissuti.
MAXIFS
La funzione MAXIFS, disponibile in Excel Office 365, può restituire un valore massimo utilizzando uno o più criteri senza la necessità di una formula di matrice. Tuttavia, MAXIFS è una funzione basata su intervalli e non consente ad altre funzioni come TESTO di elaborare valori in intervalli di criteri. È tuttavia possibile aggiungere una colonna helper ai dati, generare valori del giorno della settimana con TEXT, quindi utilizzare MAXIFS con la colonna helper come intervallo di criteri.
