Moyenne si différent de 0 ou Moyenne sans 0 !!

Ça peut vous paraître un peu étrange ce titre, cependant ce sont des questions qui se posent très souvent par des utilisateurs qui veulent savoir comment calculer la moyenne des nombres en ignorant le zéro 0, et c’est la réponse à ces questions que vous allez découvrir dans l’article présent de la formation Excel. Ce sujet va nous mener aussi à découvrir comment calculer la moyenne des valeurs en répondant à une ou plusieurs conditions tout en étudiant les deux fonctions MOYENNE.SI et MOYENNE.SI.ENS.
Moyenne si différent de 0 ou moyenne sans 0


Nous avons vu dans l’article : Calculer la moyenne des données filtrées ou non-filtrées en utilisant la fonction MOYENNE, comment calculer une moyenne arithmétique d’une plage de cellules. Mais si cette plage de cellules contient des zéros que va-t-il se passer ?

Suivons cet exemple :(lien de téléchargement du fichier est en bas de l'article)

Moyenne avec 0


En effectuant la fonction Moyenne, Excel calcule la moyenne de tous les nombres en incluant le zéro aussi. Alors que moi je veux que mon calcul ignore cette valeur nulle.

La solution donc est d’employer la fonction MOYENNE.SI.

Mais avant d’expliquer la façon d’utiliser cette fonction, j’aimerais attirer votre attention sur deux choses à propos de la valeur 0 :

La première :quand une cellule ne contient rien, cela ne signifie pas qu’elle contient une valeur nulle. Remarquez dans l’illustration suivante que lorsque je supprime le zéro de la cellule A5, la fonction Moyenne ne prend pas en compte cette cellule devenue vide.

Moyenne avec cellule vide


La deuxième, vous pouvez remarquer que lorsque vous utilisez une fonction Moyenne et que la plage de cellules référencée dans cette fonction contient des cellules vides, vous remarquez que votre moyenne prend en compte ces cellules vides. Ceci explique alors qu’Excel les traite comme des cellules contenant des valeurs nulles même s’il n’affiche pas un zéro 0.

Cellule contenant une valeur nulle mais elle est vide


Dans ce cas vérifiez si l’option Afficher un zéro dans les cellules qui ont une valeur nulle est désactivée.
  • Et pour l’activer, cliquez sur le menu Fichier et choisissez Options.
  • Sélectionnez ensuite Options avancées.
  • Puis dans la catégorie Options d’affichage de la feuille de calcul, cochez Afficher un zéro dans les cellules qui ont une valeur nulle.
Afficher un zéro dans les cellules qui ont une valeur nulle



Comment utiliser la fonction MOYENNE.SI ?

La présence de SI dans une fonction Excel, signifie toujours que cette fonction va traiter des valeurs pour afficher un résultat qui répond à une ou plusieurs conditions.

En reprenant l’exemple précédent, notre but était de calculer la moyenne des valeurs de la plage de cellules A1:A5 en ignorant la valeur nulle (Zéro).

Syntaxe de la fonction MOYENNE.SI 

La syntaxe de la fonction MOYENNE.SI comme elle est définie par Excel est :
MOYENNE.SI(plage; critères; [plage_moyenne])

Comme vous le voyez, la fonction MOYENNE.SI contient trois arguments :
  • Plage : vous devez définir pour cet argument la référence de la plage de cellules contenant les valeurs parmi lesquelles on va chercher le critère souhaité.
  • Critères : c’est votre condition à réaliser.
  • plage_moyenne : c’est la plage qui contient les valeurs dont on veut calculer la moyenne. Si la plage de critères est elle-même la plage_moyenne, vous pouvez omettre cette dernière.

Appliquez maintenant cette syntaxe pour calculer la moyenne de la plage de cellules A1:A5 en ignorant le zéro :
  • Sélectionnez par exemple la cellule A6 et tapez =Moyenne.si(
  • Sélectionnez la plage de cellules A1:A5 et tapez point-virgule : =MOYENNE.SI(A1:A5;
  • Tapez "<>0". (N’oubliez pas les guillemets !)
  • Fermez la parenthèse et validez.
  • Voici alors le résultat désiré.
Moyenne sans 0



Découvrons ensemble d’autres exemples d’utilisation de la fonction MOYENNE.SI :

Exemple 1 : calculer la moyenne des nombres supérieurs à 200

  • C’est simple : tapez =MOYENNE.SI(A1:A5;">200")
  • Le résultat est : 433,33
Moyenne si avec condition



Note : implicitement ,le zéro est ignoré en utilisant cette formule !

Exemple 2 : Calculer la moyenne de vente par vendeur

Le tableau suivant présente des données concernant les ventes réalisées par 3 vendeurs :
On veut par exemple calculer la moyenne des ventes réalisées par Alain :
  • Alors, sélectionnez une cellule vide et tapez la formule qui suit :

=MOYENNE.SI(A2:A8;"Alain";B2:B8)

Utiliser fonction Moyenne.si avec un critère


Remarquez ici que nous avons défini les deux plages : plage de critères et plage_moyenne :
  • A2:A8 : représente la plage de critères dans laquelle on cherche les cellules contenant notre critère « Alain ».
  • B2:B8 : représente les valeurs numérique dont on veut calculer la moyenne.

Vous pouvez aussi utiliser une référence de cellule contenant votre critère au lieu de le taper à la main :
=MOYENNE.SI(A2:A8;A3;B2:B8)

Utiliser référence de cellule comme critère dans moyenne si

  • A3 est la référence de la cellule qui contient « Alain ».

Exemple 3 : Moyenne si date 

Dans cet exemple on veut trouver la moyenne des ventes réalisées avant le 3/3/2017 :
La formule à utiliser est : =MOYENNE.SI(A2:A8;"<03/03/2017";C2:C8)

Moyenne si avec date comme critère


Si vous estimez trouver la moyenne des ventes réalisées avant aujourd’hui, dans ce cas vous pouvez introduire la fonction AUJOURDHUI () comme ceci :
=MOYENNE.SI(A2:A8;"<"&AUJOURDHUI();C2:C8)

Moyenne si date avant aujourdhui


Remarquez qu’on a mis uniquement l’opérateur de comparaison entre guillemets "<", puis on a fait appel à l’esperluette pour concaténer cet opérateur et la fonction AUJOURDHUI() non mise entre guillemets : "<"&AUJOURDHUI()

La même chose aussi se dit lorsque vous définissez une référence de cellule avec un opérateur de comparaison.
Par exemple notre formule utilisée précédemment =MOYENNE.SI(A2:A8;"<03/03/2017";C2:C8), on peut l’écrire comme ça : =MOYENNE.SI(A2:A8;"<"&A5;C2:C8)

Moyenne si date en utilisant référence de cellule pour le critère


Utiliser la fonction MOYENNE.SI.ENS pour calculer une moyenne à plusieurs conditions

La fonction MOYENNE.SI.ENS permet de calculer la moyenne des nombres en répondant à un ou plusieurs critères.

Voici la syntaxe de la fonction MOYENNE.SI.ENS:

MOYENNE.SI.ENS(plage_moyenne; plage_critères1; critère1; [plage_critères2; critère2]; ...)

Faites attention lorsque vous voulez introduire les arguments de cette fonction, en effet,  le premier argument doit être la référence de la plage de cellules contenant les valeurs à calculer leur moyenne, suivie de la première plage de critère puis du premier critère, et ensuite de la deuxième plage de critères et du deuxième critère et ainsi de suite….

Si nous reprenons notre premier exemple sur comment calculer la moyenne des nombres sans prendre en compte les valeurs nulles, notre formule sera la suivante :

=MOYENNE.SI.ENS(A1:A5;A1:A5;"<>0")

Moyenne si ens sans 0


Note: Ici, notre plage de moyenne et plage de critères sont les mêmes.

Exemples d’utilisation de la fonction  MOYENNE.SI.ENS :

MOYENNE.SI.ENS avec deux conditions

Dans cet exemple nous désirons calculer la moyenne des quantités des « Montres femmes » qui se sont vendues dans plusieurs magasins et ceci avant le 26/02/2017:

  • Sélectionnez donc une cellule vide et tapez la formule suivante :

=MOYENNE.SI.ENS(C2:C20;A2:A20;"<"&A15;B2:B20;B5)

Moyenne Si Ens Exemple

MOYENNE.SI.ENS avec trois conditions

Vous pouvez aussi calculer la moyenne des quantités des « Montres femmes » vendues entre deux dates, par exemple entre le 23/02/2017 et le 26/02/2017.

  • Voici la formule utilisant MOYENNE.SI.ENS avec trois critères :

=MOYENNE.SI.ENS(C2:C20;A2:A20;">"&A5;A2:A20;"<"&A15;B2:B20;B6)

Moyenne Si Ens avec trois conditions



Note: les exemples traités dans cet article sont contenus dans un seul classeur Excel que vous pouvez le télécharger ici : Moyenne.Si et Moyenne.si.ens
PARTAGEZ

Excel Info

  • Image
  • Image
  • Image
  • Image
  • Image