George a une feuille de calcul qui comprend les dates (dans la colonne A) et les valeurs associées à ces dates (dans la colonne B). La feuille de calcul comprend les valeurs des dernières années. Il aimerait calculer la moyenne de toutes les valeurs pour un mois donné dans une année donnée. Par exemple, George aimerait calculer la moyenne de toutes les valeurs pour février 2020.

Il existe plusieurs manières d’aborder ce problème. Une façon consiste à créer un tableau croisé dynamique basé sur vos données. (Les tableaux croisés dynamiques sont parfaits pour agréger et analyser d’énormes quantités de données.) Vous pouvez facilement définir le champ de valeur sur Moyenne (au lieu de la somme par défaut) et regrouper la colonne Dates par ce que vous voulez.

Si vous préférez ne pas utiliser de tableau croisé dynamique, vous pouvez ajouter un certain nombre de formules à votre feuille de calcul. Par exemple, la formule suivante utilise la fonction SUMPRODUCT pour calculer la moyenne:

=SUMPRODUCT((MONTH(A2:A1000)=5)(YEAR(A2:A1000)=2020)(B2:B1000)) / (SUMPRODUCT((MONTH(A2:A1000)=5)(YEAR(A2:A1000)=2020)1))

La formule suppose que vos dates et valeurs commencent à la ligne 2 (pour autoriser les en-têtes) et ne dépassent pas la ligne 1000. S’il n’y a pas de dates dans les données du mois de février 2020, la formule renvoie un #DIV / 0! Erreur.

Une autre approche consiste à utiliser une formule matricielle, telle que la suivante:

=AVERAGE(IF((MONTH(A2:A1000)=5)*(YEAR(A2:A1000)=2020),B2:B1000))

Cette approche est plus courte que la formule SUMPRODUCT, mais vous devez vous rappeler de maintenir les touches Ctrl + Maj + Entrée enfoncées lorsque vous entrez la formule.

Vous obtiendrez également la division par erreur zéro s’il n’y a pas de données pour le mois et l’année souhaités.

Une autre approche encore consiste à utiliser l’une des fonctions de base de données d’Excel, DAVERAGE. Tout ce que vous avez à faire est de créer un tableau de critères qui définit ce que vous recherchez. Supposons, par exemple, que les en-têtes des colonnes soient quelque chose d’original, comme Date (cellule A1) et Valeur (cellule B1).

Vous pouvez configurer un tableau de critères à un autre endroit, tel que D1: E2. Le tableau pourrait ressembler à ceci:

Date         Date >1/31/20     <3/1/20

Le tableau des critères indique que vous souhaitez que DAVERAGE utilise tout ce dont la colonne Date contient une date supérieure à 1/31/20 et une date inférieure à 3/1/20. Voici la formule:

=DAVERAGE(A1:B1000,"Value",D1:E2)

Le premier paramètre définit votre base de données, le deuxième paramètre indique que vous souhaitez faire la moyenne des informations dans la colonne Valeur (colonne B) et le troisième paramètre indique à DAVERAGE où se trouve votre table de critères.

Un moyen assez simple consiste à appliquer un filtrage des dates et à utiliser la fonction SOUS-TOTAL. Entrez la formule suivante dans une cellule:

=SUBTOTAL(101,B2:B1000)

Sélectionnez une cellule dans votre plage de données et filtrez vos données (affichez l’onglet Données du ruban et cliquez sur l’outil Filtre). Cliquez sur la flèche de filtrage en haut de la colonne A, puis choisissez Filtres de date | Filtre personnalisé dans la liste déroulante. Excel affiche la boîte de dialogue Filtre automatique personnalisé.

(Voir la figure 1.)

image

Figure 1. La boîte de dialogue Filtre automatique personnalisé.

Utilisez les commandes de la boîte de dialogue pour spécifier que vous voulez des enregistrements supérieurs à 1/31/20 et inférieurs à 3/1/20. Lorsque vous cliquez sur OK, seuls les enregistrements de février 2020 sont affichés et la formule de sous-total affiche la moyenne de ces enregistrements visibles.

Cependant, l’approche la plus simple consiste peut-être à utiliser la fonction AVERAGEIFS. Il vous permet de calculer une moyenne de certaines valeurs selon que la valeur associée (la date, dans ce cas) répond aux critères que vous spécifiez. Voici à quoi ressemble la formule:

=AVERAGEIFS(B2:B1000,A2:A1000,">31 Jan 2020",A2:A1000,"<01 Mar 2020")

Notez qu’il ne s’agit pas d’une formule matricielle et que vous n’avez pas besoin de définir une table de critères pour vos critères, ils sont directement intégrés à la formule.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (10671) s’applique à Microsoft Excel 2007, 2010, 2013, 2016, 2019 et Excel dans Office 365. Vous pouvez trouver une version de cette astuce pour l’ancienne interface de menu d’Excel ici:

link: / excel-Averaging_Values_for_a_Given_Month_and_Year [Moyennage des valeurs pour un mois et une année donnés].