Formula generica
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Sommario
Per trovare il valore massimo in un determinato mese, puoi utilizzare la funzione MAXIFS o una delle altre alternative di seguito. Nell'esempio mostrato, la formula in G5 è:
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Dove le vendite (G5: G15), le date (B5: B15) e i valori (C5: C15) sono intervalli denominati.
Spiegazione
La funzione MAXIFS può trovare il valore massimo in un intervallo basato su uno o più criteri. Nell'esempio mostrato, utilizziamo MAXIFS per trovare il valore massimo delle vendite in base a un dato mese "inserendo tra parentesi" le date tra il primo giorno del mese e l'ultimo giorno del mese. Il primo criterio verifica se le date sono maggiori o uguali al primo del mese:
dates,">="&G4,dates // first of month
Nota: supponiamo che la data in G4 sia una data del "primo del mese".
Il secondo criterio verifica se le date sono inferiori o uguali all'ultima del mese, calcolate con la funzione EOMONTH:
dates,"<="&EOMONTH(G4,0) // last of month
Quando entrambi i criteri restituiscono TRUE, la data è nel mese specificato e MAXIFS restituisce il valore massimo delle date che soddisfano i criteri.
Formule alternative
Se la tua versione di Excel non include la funzione MAXIFS, ci sono altre opzioni. Un'opzione è una semplice formula di matrice basata sulle funzioni MAX e IF:
(=MAX(IF(TEXT(dates,"my")=TEXT(G4,"my"),sales)))
Nota: questa è una formula di matrice e deve essere inserita con CTRL + MAIUSC + INVIO.
Un'altra opzione è usare la funzione AGGREGATE in questo modo:
=AGGREGATE(14,6,sales/(TEXT(dates,"my")=TEXT(G4,"my")),1)
Entrambe le opzioni precedenti utilizzano un metodo più semplice per testare le date basato sulla funzione TESTO che utilizza un formato numerico per estrarre il mese e l'anno da una data. La funzione TEXT non può essere utilizzata in questo modo all'interno della funzione MAXIFS, perché MAXIFS richiede un intervallo. Questi approcci alternativi sono spiegati più dettagliatamente qui.
Il massimo cliente
Per recuperare il client associato al valore massimo in un dato mese, la formula in G6 è:
(=INDEX(clients,MATCH(1,(sales=G5)*(TEXT(dates,"my")=TEXT(G4,"my")),0)))
Questa è una formula di matrice e deve essere inserita con CTRL + MAIUSC + INVIO.
L'idea qui è che conosciamo già il valore massimo in un mese (G5) e possiamo usare quel valore come "chiave" per cercare il client. Nota che dobbiamo ripetere la logica utilizzata per isolare i valori nel mese dato, al fine di evitare una falsa corrispondenza in un mese diverso.
Come le alternative menzionate sopra, anche questa formula utilizza un test semplificato per le date basato sulla funzione TESTO. Maggiori informazioni su INDICE e CORRISPONDENZA con più criteri qui.
Per una formula all-in-one, sostituire G5 con una delle opzioni di formula spiegate sopra per determinare il valore massimo in un dato mese.
