Ç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.
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)
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.
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.
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.
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é.
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
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)
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)
- 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)
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)
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)
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")
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 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)
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
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épondreSupprimerLa même chose existe elle pour faire la moyenne de plusieurs plage de chiffre différentes ?
RépondreSupprimerMa formule à date: Moyenne(A2:A80;A82:A160;A162:A200) --> comment faire pour qu'elle ne prenne pas en compte les cellules = 0 ?
Essayez avec cette formule:
Supprimer=SOMME(A2:A80;A82:A160;A162:A200)/SOMME(NB.SI(INDIRECT({"A2:A80";"A82:A160";"A162:A200"});"<>0"))
Quand la plage de critère se trouve dans un autre classeur, comment on fait
RépondreSupprimerL'article est clair !
RépondreSupprimerMais 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")