Formule générique
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Sommaire
Pour trouver la valeur maximale dans un mois donné, vous pouvez utiliser la fonction MAXIFS ou l'une des autres alternatives ci-dessous. Dans l'exemple illustré, la formule en G5 est:
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Où les ventes (D5: D15), les dates (B5: B15) et les valeurs (C5: C15) sont des plages nommées.
Explication
La fonction MAXIFS peut trouver la valeur maximale dans une plage en fonction d'un ou plusieurs critères. Dans l'exemple illustré, nous utilisons MAXIFS pour trouver la valeur de vente maximale basée sur un mois donné en "bracketing" des dates entre le premier jour du mois et le dernier jour du mois. Le premier critère vérifie si les dates sont supérieures ou égales au premier du mois:
dates,">="&G4,dates // first of month
Remarque: nous supposons que la date dans G4 est une date "premier du mois".
Le deuxième critère vérifie si les dates sont inférieures ou égales au dernier du mois, calculé avec la fonction EOMONTH:
dates,"<="&EOMONTH(G4,0) // last of month
Lorsque les deux critères renvoient TRUE, la date est dans le mois donné et MAXIFS renvoie la valeur maximale des dates qui répondent aux critères.
Formules alternatives
Si votre version d'Excel n'inclut pas la fonction MAXIFS, il existe d'autres options. Une option est une formule matricielle simple basée sur les fonctions MAX et IF:
(=MAX(IF(TEXT(dates,"my")=TEXT(G4,"my"),sales)))
Remarque: Il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée.
Une autre option consiste à utiliser la fonction AGGREGATE comme ceci:
=AGGREGATE(14,6,sales/(TEXT(dates,"my")=TEXT(G4,"my")),1)
Les deux options ci-dessus utilisent une méthode plus simple de test des dates basée sur la fonction TEXT qui utilise un format numérique pour extraire le mois et l'année d'une date. La fonction TEXT ne peut pas être utilisée de cette manière dans la fonction MAXIFS, car MAXIFS nécessite une plage. Ces approches alternatives sont expliquées plus en détail ici.
Le client max
Pour récupérer le client associé à la valeur max dans un mois donné, la formule dans G6 est:
(=INDEX(clients,MATCH(1,(sales=G5)*(TEXT(dates,"my")=TEXT(G4,"my")),0)))
Il s'agit d'une formule matricielle et doit être saisie avec avec Ctrl + Maj + Entrée.
L'idée ici est que nous connaissons déjà la valeur maximale en un mois (G5) et que nous pouvons utiliser cette valeur comme "clé" pour rechercher le client. Notez que nous devons répéter la logique utilisée pour isoler les valeurs dans le mois donné, afin d'éviter une fausse correspondance dans un mois différent.
Comme les alternatives mentionnées ci-dessus, cette formule utilise également un test simplifié pour les dates basé sur la fonction TEXT. En savoir plus sur INDEX et MATCH avec plusieurs critères ici.
Pour une formule tout-en-un, remplacez G5 par l'une des options de formule expliquées ci-dessus pour déterminer la valeur maximale pour un mois donné.
