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

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

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 :

Excel formation - Calcul de moyenne - 01

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

À 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) 

Excel formation - Calcul de moyenne - 02

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 :

Excel formation - Calcul de moyenne - 03

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 formation - Calcul de moyenne - 04

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

Excel formation - Calcul de moyenne - 05

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 :

Excel formation - Calcul de moyenne - 06

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") 

Excel formation - Calcul de moyenne - 07

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))  

Excel formation - Calcul de moyenne - 08

 

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")  

Excel formation - Calcul de moyenne - 09

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

 



Articles qui pourraient vous intéresser

Comment calculer une moyenne avec un ou plusieurs critères sur Excel ?
Comment convertir des données en nombre ou en texte sur Excel ? Les fonctions N() et T()
Comment créer une référence automatiquement sur Excel ? La fonction DECALER()
Comment extraire les plus petites et plus grandes valeurs avec Excel ? Les fonctions PETITE.VALEUR() et GRANDE.VALEUR()
Comment mettre en place une référence dynamique ? La fonction INDIRECT() d’Excel
Comment calculer le PGCD et le PPCM (« Plus Grand Commun Diviseur » et « Plus Petit Commun Multiple ») sur Excel ?
Comment utiliser la fonction SOMMEPROD d’Excel
Comment nettoyer une chaîne de caractères - Supprimer les espaces indésirables et les caractères illisibles sur Excel ?
Comment compter le nombre de feuilles, de lignes ou de colonnes Excel ?
Comment extraire des parties de texte avec les fonctions DROITE() - GAUCHE() - STXT() d'Excel
Comment effectuer un test sur Excel : la fonction SI() (ainsi que ET() et OU())
Comment convertir des unités de mesure sur Excel ? La fonction CONVERT()

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2022 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.