Formule générique
=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))
Sommaire
Pour calculer la moyenne mensuelle, vous pouvez utiliser une formule basée sur la fonction AVERAGEIFS, avec l'aide de la fonction EOMONTH. Dans l'exemple illustré, la formule en F4 est:
=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Cette formule utilise les plages nommées «montants» (D5: D104) et «dates» (C5: C104).
Explication
La fonction AVERAGEIFS peut faire la moyenne des plages en fonction de plusieurs critères. Dans ce cas, nous configurons AVERAGEIFS pour des montants moyens par mois en utilisant deux critères: (1) les dates de correspondance supérieures ou égales au premier jour du mois, (2) les dates de correspondance inférieures ou égales au dernier jour du mois. Si nous avons codé en dur les dates de janvier 2016 dans la formule à l'aide de la fonction DATE, cela ressemblerait à ceci.
=AVERAGEIFS(amounts,dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,1,31))
Mais nous ne voulons pas coder en dur les dates, nous voulons qu'Excel génère ces dates pour nous. Normalement, c'est pénible, car si vous ajoutez des noms de mois sous forme de texte (c'est-à-dire «janvier», «février», «mars», etc.) dans la colonne F, vous devez vous donner plus de mal pour créer des dates que vous pouvez utiliser pour les critères .
Cependant, dans ce cas, nous utilisons une astuce simple pour faciliter les choses: dans la colonne F, au lieu de taper les noms de mois, nous ajoutons des dates réelles pour le premier de chaque mois (1/1/2016, 2/1/2016, 3 / 1/2016, etc.) et utilisez un format de date personnalisé ("mmm") pour afficher les noms de mois.
Cela facilite la création des critères dont nous avons besoin pour AVERAGEIFS. Pour faire correspondre les dates supérieures ou égales au premier du mois, nous utilisons:
">="&E4
Et pour faire correspondre les dates inférieures ou égales au dernier jour du mois, nous utilisons:
"<="&EOMONTH(E4,0)
EOMONTH renvoie automatiquement le dernier jour du même mois car nous fournissons zéro pour l'argument des mois.
Remarque: la concaténation avec une esperluette (&) est nécessaire lors de la création de critères basés sur une référence de cellule.
Solution de tableau croisé dynamique
Un tableau croisé dynamique est une excellente solution lorsque vous avez besoin de résumer les données par année, mois, trimestre, etc., car les tableaux croisés dynamiques fournissent des contrôles pour le regroupement automatique par date. Pour une comparaison côte à côte des formules et des tableaux croisés dynamiques, regardez cette vidéo: Pourquoi les tableaux croisés dynamiques.
