Comment calculer une moyenne avec un ou plusieurs critères sur Excel ?

Dans ce tutoriel, nous allons revenir sur la notion de moyenne dans Excel. Nous allons découvrir qu’il est possible de mettre en place des critères pour déterminer les cellules que nous souhaitons intégrer dans le calcul, et celles que nous souhaitons écarter.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Dans un tutoriel précédent, que vous pourrez retrouver en suivant de lien, nous avions déjà pu découvrir la notion de moyenne.

Nous avions alors vu que ce que nous appelons régulièrement moyenne peut également s’appeler une moyenne « simple », ou « arithmétique ».

Cette notion statistique correspond à la somme des valeurs d’une série de nombres que l’on divise par le nombre de valeurs contenues dans cette série.

Pour illustrer ce tutoriel, nous allons nous appuyer sur le relevé des notes suivant :

Sur celui-ci, nous retrouvons les notes obtenues par les élèves d’une classe de 6^e.

À partir de la définition que nous venons d’énoncer juste avant, nous pouvons donc calculer la moyenne obtenue par les élèves pour chaque matière.

Pour cela, il suffit de diviser la somme totale de ces notes par le nombre de notes totales, en utilisant les fonctions SOMME() et NB(). Celles-ci permettent de déterminer respectivement la somme d’une série et son nombre d’éléments :

 =SOMME(C9:C31)/NB(C9:C31) 

Mais comme vous pouvez vous en douter, Excel propose une fonction pour réaliser ce calcul en une seule étape, et c’est justement que nous allons découvrir dans ce tutoriel.

2. La fonction MOYENNE()

Cette fonction qui permet de calculer la moyenne d’une série porte le nom de… MOYENNE() !

Celle-ci s’utilise très simplement, comme la fonction SOMME(), c’est-à-dire qu’il suffit de renseigner en argument les coordonnées de la page des cellules pour lesquelles nous souhaitons obtenir la MOYENNE() des valeurs :

 =MOYENNE(C9:C31) 

Bien entendu, le résultat retourné sera exactement le même que lors de notre calcul précédent.

Une fois la moyenne de la première matière calculée, nous pouvons étendre la formule sur les autres cellules en utilisant la poignée de recopie :

Ensuite, il faut également savoir que la fonction moyenne peut être appelée de manière automatique.

Pour cela, nous commençons par sélectionner la cellule de destination, puis nous nous rendons dans le menu Accueil afin de dérouler le menu Somme automatique pour pouvoir sélectionner Moyenne :

Excel nous insère alors la fonction Moyenne, qu’il suffit de valider en appuyant sur la touche [Entrée] du clavier :

Le raccourci clavier qui permet d’activer cette fonctionnalité est le suivant : [Alt], [L], [Q], [M]

3. Calculer une moyenne avec une condition

Pour calculer une moyenne en utilisant une condition, nous ne pouvons pas utiliser directement la fonction MOYENNE() que nous venons de voir, étant donné que celle-ci n’attend qu’un seul argument qui est la plage des cellules.

Pour cela, nous devrons utiliser sa déclinaison plus moderne qui est la fonction MOYENNE.SI() :

 =MOYENNE.SI(plage;critères;[plage_moyenne])  

Où :

• Plage est la plage de cellules sur lesquelles le critère sera appliqué,
• Critère est la condition à respecter pour calculer la moyenne. Critère peut être un nombre (exemple : « 12 »), une expression (exemple : « <12 ») ou un texte (exemple : « Fille »).
• Plage_moyenne est l'argument facultatif qui spécifie la plage des cellules sur lesquelles la moyenne doit être calculée. Si cet argument est manquant, par défaut Plage_moyenne prend la valeur de l’argument Plage.

Pour notre premier exemple, nous allons chercher à calculer la moyenne obtenue par les élèves, en fonction de leur sexe.

Pour calculer la moyenne obtenue par les filles, nous commençons donc par sélectionner les cellules de la colonne Sexe, c’est-à-dire de la cellule B9 jusqu’à B31.

Ensuite, pour le critère, nous pouvons soit le saisir entre guillemets ("Fille"), soit utiliser la cellule K13. Nous préfèrerons cette seconde méthode afin de pouvoir étendre par la suite.

Puis pour le dernier argument, il nous reste à sélectionner la plage des cellules contenant les moyennes générales, c’est-à-dire de la cellule G9 jusqu’à G31.

Avant de valider la formule, nous allons revenir sur l’argument Plage, que nous allons convertir en référence absolue en le sélectionnant et en appuyant sur la touche [F4] pour ajouter les dollars automatiquement :

Nous faisons de même pour le dernier argument Plage_moyenne :

 =MOYENNE.SI($B$9:$B$31;K13;$G$9:$G$31) 

De cette manière, une fois la cellule validée, il ne reste plus qu’à nous placer sur la cellule située juste en dessous et d’appuyer sur les touches [Ctrl]+[B] pour dupliquer la cellule :

Nous pouvons alors nous rendre compte que la moyenne des notes obtenues par les filles surpasse très légèrement celle des garçons, avec respectivement 13,15 et 13,13/20.

Il faut également savoir que nous pouvons complexifier l’argument critère en utilisant une inégalité (absolue ou relative).

Pour ce nouvel exemple, nous allons chercher à connaître la moyenne obtenue par les 10 premiers élèves de la classe.

Pour cela, nous procédons de la manière suivante :

• Pour l’argument plage, nous sélectionnons les cellules de la colonne Moyenne générale,
• Pour l’argument critères, nous saisissons l’expression « ">=15" », entre guillemets, étant donné qu’il s’agit pour Excel d’une chaîne de caractères

Ensuite, étant donné que la moyenne est à calculer sur la moyenne générale obtenue par chaque élève que nous exploitons déjà dans l’argument plage, il n’est pas nécessaire de renseigner Plage_moyenne :

 =MOYENNE.SI(G9:G31;">=15") 

Maintenant, voyons comment calculer la moyenne obtenue par les 10 premiers élèves de la classe.

Pour cela, nous utiliserons la formule suivante :

 =MOYENNE.SI.ENS(G9:G31;H9:H31;"<=10")  

Ici, il n’y a pas de difficulté particulière.

En revanche, les choses se compliqueront légèrement si nous souhaitons calculer la moyenne des 10 moins bons élèves.

Nous devrons alors dupliquer la formule, et n’en modifier que la fin.

En effet, le nombre de notes à reprendre dépendra du nombre total d’élèves de la classe, duquel nous en retrancherons 10 :

 =MOYENNE.SI.ENS(G9:G31;H9:H31;">"&(NB(H9:H31)-10))  

4. Calculer une moyenne avec plusieurs conditions

Maintenant, voyons comment démultiplier les conditions permettant de calculer la moyenne.

Pour cela, nous ne pourrons pas utiliser la fonction MOYENNE.SI() comme nous venons de le faire, mais son évolution MOYENNE.SI.ENS().

Celle-ci s’utilise de la manière suivante :

 =MOYENNE.SI.ENS(plage_moyenne;plage_critères;critères;...)  

Ici, nous retrouvons des arguments portant les mêmes noms que ceux découverts dans la partie précédente, les différences étant les suivantes :

• Pour commencer, l’argument Plage_moyenne, qui correspond toujours à la plage des cellules sur lesquelles nous devrons effectuer le calcul, est ici obligatoire,
• Et ensuite, les couples d’arguments plage_critère et critère pourront être répété de manière quasi illimitée pour démultiplier les critères (en vérité 255 fois, ce qui est déjà énorme)

Pour illustrer ce dernier cas, nous souhaiterons connaître la moyenne obtenue par les filles dont la moyenne générale est d’au moins 10/20.

Ici, nous avons bien deux critères à prendre en compte :

• Le sexe
• Et la moyenne générale

 =MOYENNE.SI.ENS(G9:G31;B9:B31;"Fille";G9:G31;">10")  

Comme vous pouvez le constater, nous faisons deux fois appel à la plage des cellules G9:G31.