Moyenne si différent de 0 ou Moyenne sans 0 et comment utiliser MOYENNE.SI et MOYENNE.SI.ENS ?!!

Ç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, lorsque vous utilisez une fonction Moyenne et si la plage de cellules référencée dans cette fonction contient des cellules vides, vous pouvez remarquer 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 vous allez chercher le critère souhaité.
  • Critères : c’est votre condition à réaliser.
  • plage_moyenne : c’est la plage qui contient les valeurs dont vous voulez calculer la moyenne. Si la plage de critères est elle-même la plage_moyenne, vous pouvez omettre cette dernière.

Utilisez 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 souhaité.
Moyenne sans 0



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

Mais avant d'entamer cette découverte, jetez rapidement un coup d'œil sur cette vidéo pour avoir une idée sur l'utilisation de cette fonction :

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 nous cherchons les cellules contenant notre critère « Alain ».
  • B2:B8 : représente les valeurs numérique dont nous voulons 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 nous voulons 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 que j'ai mis uniquement l’opérateur de comparaison entre guillemets "<", puis j'ai 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 la formule utilisée précédemment =MOYENNE.SI(A2:A8;"<03/03/2017";C2:C8), vous pouvez 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 : >23/02/2017, <26/02/2017 et Montre femme.

=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  télécharger ici : Moyenne.Si et Moyenne.si.ens
PARTAGEZ
    Blogger Comment
    Facebook Comment

5 commentaires:

  1. La présentation du cours est cohérente et facilite la compréhension. Les Notes qui précisent certains aspects du cours sont très importantes. Par ailleurs, dans MOYENNE.SI.ENS avec deux conditions, les deux critères sont exprimés mais pas de façon explicite comme dans MOYENNE.SI.ENS avec trois conditions. Pour la MOYENNE.SI avec AJOURDHUI, je ne trouve pas le même résultat que vous.

    RépondreSupprimer
  2. La même chose existe elle pour faire la moyenne de plusieurs plage de chiffre différentes ?
    Ma formule à date: Moyenne(A2:A80;A82:A160;A162:A200) --> comment faire pour qu'elle ne prenne pas en compte les cellules = 0 ?

    RépondreSupprimer
    Réponses
    1. Essayez avec cette formule:
      =SOMME(A2:A80;A82:A160;A162:A200)/SOMME(NB.SI(INDIRECT({"A2:A80";"A82:A160";"A162:A200"});"<>0"))

      Supprimer
  3. Quand la plage de critère se trouve dans un autre classeur, comment on fait

    RépondreSupprimer
  4. L'article est clair !
    Mais avec =MOYENNE.SI(C6;C10;C14;C18;C22;C26;C30;"<>0")
    ça me met que j'ai rentré un nombre d'argument trop important.
    Même avec =MOYENNE.SI(C6;C10;C14;"<>0")

    RépondreSupprimer

Votre commentaire m'intéresse beaucoup :)