J’ai présenté dans un article séparé comment calculer une moyenne arithmétique dans Excel avec la fonction MOYENNE.
Je vais maintenant détailler comment calculer une moyenne conditionnelle, c’est-à-dire une moyenne qui ne prend en compte que les valeurs qui respectent un ou plusieurs critères définis. En fonction du nombre de conditions que l’on souhaite appliquer, on pourra utiliser 2 fonctions :
- MOYENNE.SI : pour ne faire la moyenne que des valeurs qui respectent une condition.
- MOYENNE.SI.ENS : pour ne faire la moyenne que des valeurs qui respectent plusieurs conditions. Bien qu’on puisse également l’utiliser avec une seule condition.
J’aborde aussi dans un autre article le cas du calcul d’une moyenne pondérée.
Moyenne avec une Seule Condition : La fonction MOYENNE.SI
La fonction MOYENNE.SI (Version anglaise : AVERAGEIF) « détermine la moyenne (espérance arithmétique) des cellules satisfaisant une condition ou des critères particuliers ».
Sous Excel, la fonction et ses arguments se présentent sous la forme suivante :
=MOYENNE.SI(Plage; Critères; [Plage Moyenne])L’argument « Critères » correspond à la condition qui devra être remplie par l’argument « Plage ». La plupart du temps on utilisera les Opérateurs de Comparaison d’Excel.
Si la Plage de cellules contenant les valeurs numériques pour lesquelles on souhaite obtenir la moyenne est la même que celle sur laquelle on souhaite poser une condition, alors on utilisera uniquement 2 arguments : Plage et Critères. Cela peut être le cas si l’on souhaite faire la moyenne des valeurs uniquement plus grandes que 10 (ou autre valeur) :
=MOYENNE.SI(A1:A100; ">10")En revanche, si l’on souhaite se baser sur un autre critère situé dans une plage de cellules équivalente (de même taille) à la plage de cellules pour laquelle on souhaite calculer la moyenne, alors on utilisera 3 arguments : Plage, Critères, Plage Moyenne, comme illustré ci-dessous :
Nous disposons d’un ensemble de Ventes catégorisées par Région, Entitée, Départment: à titre illustratif (dénué de sens), nous souhaitons obtenir la moyenne des ventes par Entitée, ainsi que la Moyenne des ventes par Région.
La plage pour laquelle notre condition est appliquée se trouve dans la colonne B (Entreprise est égale à la cellule F2 « Entreprise 1 ») et les valeurs, pour lesquelles nous souhaitons obtenir la moyenne si cette condition est remplie, se trouvent dans la colonne D.
=MOYENNE.SI(B1:B11; F2; D2:D11)Faire une Moyenne si Différent de 0
Si l’on souhaite effectuer une moyenne en ignorant les 0, on se tournera vers la fonction « MOYENNE.SI » avec comme condition pour notre plage de valeur « <> 0 » (différent de 0).
=MOYENNE.SI(A1:A1000; "<>0")Moyenne des Valeurs Positives ou Négatives
Dans le cas où l’on souhaite ignorer les valeurs négatives, ou les valeurs positives, dans notre moyenne, on appliquera dans le premier cas la condition « > 0 », pour ne faire que la moyenne des valeurs positives :
=MOYENNE.SI(A1:A1000; ">0")Et dans le deuxième cas, on appliquera la condition « < 0 », pour ne faire que la moyenne des valeurs négatives :
=MOYENNE.SI(A1:A1000; "<0")Moyenne des N Valeurs les Plus Grandes (Ou Plus Petites)
Il peut nous arriver de vouloir obtenir la moyenne pour uniquement les N valeurs les plus grandes d’une plage de cellules. Pour ce faire, on utilisera la fonction de moyenne conditionnelle MOYENNE.SI en combinaison avec la fonction GRANDE.VALEUR (qui renvoie la N-ième plus grande valeur d’une plage de cellule), c’est cette dernière fonction qui nous permettra de déterminer notre condition.
Notez que si la plage contient plusieurs fois la N-ième plus grande valeur, celle ci sera intégrée plusieurs fois dans le calcul de la moyenne (qui ne sera donc pas limitée à la moyenne de N valeurs).
=MOYENNE.SI(A1:A1000; ">=" & GRANDE.VALEUR(A1:A1000; N))Si l’on souhaite de la même façon la moyenne des N valeurs les plus petites, on utilisera en combinaison la fonction PETITE.VALEUR
=MOYENNE.SI(A1:A1000; "<=" & PETITE.VALEUR(A1:A1000; N))Moyenne avec Plusieurs Conditions : La Fonction MOYENNE.SI.ENS
La fonction MOYENNE.SI.ENS (Version anglaise : AVERAGEIFS) « détermine la moyenne (espérance arithmétique) des cellules spécifiées par un ensemble de conditions ou de critères ». Elle permet de faire une moyenne à plusieurs conditions.
Sous Excel, la fonction et ses arguments se présentent sous la forme suivante :
=MOYENNE.SI.ENS(Plage Moyenne; Plage Critères 1; Critères 1; [Plage Critères 2]; [Critères 2]; ...)On donnera au minimum 3 arguments pour cette fonction.
« Plage Moyenne » correspond aux cellules contenant les valeurs numériques pour lesquelles on souhaite calculer la moyenne.
« Plage Critères » correspond aux cellules qui sera utilisée pour évaluer la conditions « Critère »
« Critères » correspond à la condition à laquelle chaque cellule de la Plage Critères devra répondre pour être incluse dans le calcul de la moyenne.
Reprenons le même exemple que précédemment avec cette fois ci deux conditions à appliquer sur notre moyenne : Région et Entitée.
Moyenne si Compris Entre 2 Valeurs
A l’aide de la fonction MOYENNE.SI.ENS, il est ainsi possible de calculer une moyenne en ne prenant en compte que les nombres compris entre 2 valeurs. On demande à Excel : « Fait la moyenne des valeurs qui sont comprises entre X et Y ». On utilisera 2 conditions pour créer les bornes inférieures et supérieures.
Par exemple, pour calculer la moyenne des nombres d’une plage uniquement si ils sont compris entre 1 et 100, en incluant 1 et 100, on utilisera les conditions « >= 1 » et « <= 100 » :
=MOYENNE.SI.ENS(A1:A1000; A1:A1000; ">=1"; A1:A1000; "<=100")