Dans mon tutoriel de somme par mois, l’un de mes lecteurs m’a demandé la formule pour calculer la moyenne par mois dans Google Sheets.
Comme j’ai des formules à la fois avec et sans tableaux, je lui réponds sous la forme d’un nouveau tutoriel. Ce post est donc la réponse à sa question.
J’espère que cela aidera également d’autres lecteurs qui aiment apprendre de nouvelles choses dans Google Sheets.
Pour calculer la moyenne par mois dans Google Sheets, nous pouvons utiliser AVERAGEIF, FILTER + AVERAGE et QUERY.
Il peut y avoir d’autres solutions également. Mais je vais me limiter à ces trois types de formules.
Parmi les formules, les deux premières sont des formules sans tableau, et la dernière est une formule avec un tableau.
Commençons par les formules sans tableau.
Moyenne par mois dans Google Sheets (Formules sans tableau)
Comme je l’ai mentionné précédemment, il existe deux types de formules sans tableau que vous trouverez ci-dessous. Les deux formules sont utilisées à des fins différentes.
Vous pouvez trouver plus de détails sur les résultats des formules ci-dessous.
Formule AVERAGEIF
Dans la plage de cellules B2:B7, saisissez les dates 25/11/2020, 26/11/2020, 1/12/2020, 6/12/2020 et 21/12/2020.
Dans la colonne suivante, c’est-à-dire dans la plage de cellules C2:C7, saisissez les nombres 5, 4, 5, 6, 5 et 11.
Nous pouvons utiliser la fonction AVERAGEIF comme suit pour obtenir la moyenne par mois dans Google Sheets.
Pour obtenir la moyenne pour le mois de novembre, nous pouvons utiliser la formule suivante :
=ArrayFormula(IFERROR(moyenne.si(mois($B$2:$B),11,$C$2:$C)))
Même si ce n’est pas une formule avec tableau, nous avons utilisé la fonction ArrayFormula car nous avons utilisé la fonction MONTH dans une plage dans la formule.
Pour la moyenne du mois de décembre, remplacez le critère 11 dans la formule par 12 :
=ArrayFormula(IFERROR(moyenne.si(mois($B$2:$B),12,$C$2:$C)))
Dans une utilisation réelle, vous pouvez spécifier les critères dans la plage E2:E13 comme suit, puis utiliser la formule suivante dans la cellule F2, et la glisser vers le bas :
=ArrayFormula(IFERROR(moyenne.si(mois($B$2:$B),E2,$C$2:$C)))
Bien que la formule ci-dessus soit facile à comprendre, même pour un débutant, elle a un inconvénient !
Qu’est-ce que c’est ?
Dans la formule AVERAGEIF ci-dessus, nous ne pouvons pas inclure la partie année.
Je veux dire que si vos données comprennent des valeurs en janvier 2020 et en janvier 2021, la formule ci-dessus ne prendra en compte que la partie mois.
La raison est que la formule AVERAGEIF ne prend en charge qu’un seul critère. Nous ne pouvons donc pas inclure la partie année dans la formule.
Alors pouvons-nous utiliser AVERAGEIFS ?
Non ! Parce que les critères mois et année sont dans la même colonne de dates. Autant que je sache, AVERAGEIFS ne le permettra pas.
Si vous souhaitez inclure le mois et l’année dans le calcul de la moyenne par mois dans Google Sheets, vous pouvez utiliser la combinaison Filter + Average comme suit.
Moyenne par mois et année avec Filter et Average
Je modifie les données pour inclure des valeurs sur plusieurs années.
Auparavant, les données étaient dans les cellules B2:C6. La nouvelle plage de données est B2:C12.
Voici les étapes pour écrire la formule qui retourne la moyenne par mois et année dans Google Sheets.
Commencez par considérer le numéro de mois 2 (février) dans la cellule E2 et l’année 2020 dans la cellule F2.
En utilisant Filter, nous devons d’abord filtrer la plage C2:C qui correspond à la plage de mois de février 2020 dans la plage de cellules B2:B.
=FILTRE($C$2:$C;mois($B$2:$B)=E2;année($B$2:$B)=F2)
La formule Filter ci-dessus filtre les valeurs selon les critères donnés.
Ensuite, nous l’insérons dans la fonction AVERAGE comme suit :
=moyenne(filtre($C$2:$C;mois($B$2:$B)=E2;année($B$2:$B)=F2))
Nous pouvons utiliser la formule Filter + Average ci-dessus dans la cellule G2 pour obtenir la moyenne par mois (février) et année (2020) dans la cellule G2.
Faites glisser cette formule jusqu’à G5, et voilà !
Moyenne par mois et année avec Query dans Google Sheets (Formule tableau)
Si vous recherchez une formule tableau pour calculer la moyenne par mois et année dans Google Sheets, il ne fait aucun doute que la fonction Query n’a pas de remplaçant.
Mais de nombreux utilisateurs de Google Sheets hésitent à utiliser Query car ils pensent que c’est un peu compliqué. Au contraire, selon mon expérience personnelle, Query est simple à utiliser et à lire.
Pour calculer la moyenne par mois et année, nous pouvons utiliser la formule Query suivante, non formatée :
=query({B2:C},"Sélectionner mois(Col1)+1,année(Col1),avg(Col2) where Col1 is not null group by mois(Col1)+1,année(Col1) order by année(Col1)",0)
Je l’ai appelée « non formatée » car je n’ai pas personnalisé les étiquettes par défaut renvoyées par la Query dans la première ligne du résultat. Veuillez consulter l’image ci-dessous (E2:G2).
Explication de la formule
Voici une explication des parties de la formule Query afin que vous puissiez les lire et les utiliser facilement.
Partie de la clause SELECT:-
Sélectionnez mois(Col1)+1, année(Col1),
Cette partie de la formule Query renvoie les valeurs dans les colonnes de résultat E (mois) et F (années).
Pourquoi ai-je utilisé mois(Col1)+1 au lieu d’utiliser mois(Col1) pour retourner les numéros de mois ?
C’est une question naturelle que tout lecteur peut poser. La raison est que les numéros de mois retournés par Query sont de 0 à 11, et non de 1 à 12.
Partie d’agrégation AVG:-
avg(Col2)
Cela permet de faire l’agrégation moyenne dans la deuxième colonne de la plage.
Partie de la clause WHERE:-
where Col1 is not null
Nous avons utilisé la plage infinie B2:C, et non la plage finie B2:C12, dans notre formule de moyenne par mois et année avec Query. Il y aurait donc plusieurs lignes vides.
La clause ci-dessus exclut ces lignes vides dans la plage.
Partie de la clause GROUP BY:-
group by mois(Col1)+1, année(Col1)
Nous voulons grouper par mois et année.
Partie de la clause ORDER BY:-
order by année(Col1)
Pour trier par année.
Si vous le souhaitez, vous pouvez inclure LABEL CLAUSE pour modifier les étiquettes dans le résultat.
Exemple :
=query({B2:C},"Sélectionner mois(Col1)+1,année(Col1),avg(Col2) where Col1 is not null group by mois(Col1)+1,année(Col1) order by année(Col1) label mois(Col1)+1'Month'",0)
Moyenne par nom de mois dans Google Sheets (Query)
Dans tous les exemples ci-dessus, la formule renvoyait des numéros de mois, et non des noms de mois.
Il se peut que beaucoup d’entre vous souhaitent obtenir les noms de mois au lieu des numéros de mois dans les résultats de la formule de moyenne par mois et année dans Google Sheets.
Nous pouvons le faire en utilisant une astuce simple avec Query. Voici la logique.
Nous allons convertir les dates en dates de fin de mois. Ainsi, au lieu de regrouper par mois, nous pouvons regrouper directement par colonne de dates.
Cela évite d’utiliser la fonction month dans Query.
Voici la Query (lisez uniquement jusqu’à la prochaine formule) :
=Query({ArrayFormula(if(B2:B="",,eomonth(B2:B,0))),C2:C},"Select Col1,avg(Col2) where Col1 is not null group by Col1",0)
Remarque : Si vous utilisez la formule ci-dessus, vous verrez des valeurs de date dans E3:E6, et non des dates, comme indiqué sur l’image. J’ai formaté les valeurs de date en date depuis le menu Format > Nombre > Date. C’est pour vous aider à comprendre le but de la conversion en fin de mois.
Nous pouvons utiliser la clause FORMAT dans la Query ci-dessus pour formater les valeurs de la colonne 1 (E3:E6) en mois et année comme suit :
=Query({ArrayFormula(if(B2:B="",,eomonth(B2:B,0))),C2:C},"Select Col1,avg(Col2) where Col1 is not null group by Col1 format Col1'mmmm-yyyy'",0)
J’espère que vous avez bien compris comment calculer la moyenne par mois dans Google Sheets.
Merci de votre attention et profitez-en bien !
Ressources :
- Trouver la moyenne des lignes visibles dans Google Sheets.
- Trouver la moyenne des dernières valeurs dans Google Sheets.
- Moyenne dans les graphiques dans Google Sheets – Ligne et colonne.
- Formule de tableau de moyenne à travers les lignes dans Google Sheets.
- Comment utiliser Regexmatch dans Averageif dans Google Sheets.
