Comment calculer une moyenne sans tenir compte des zéros sur Excel ?

Dans ce tutoriel, je vais vous montrer comment calculer une moyenne en utilisant Excel afin d’ignorer les cellules vides et les valeurs égales à zéro.

Obtenir une moyenne précise est essentiel pour de nombreuses tâches, que ce soit pour évaluer des performances, analyser des données ou suivre des statistiques.

Cependant, lorsque nous travaillons avec des données comportant des cellules vides ou des zéros, cela peut fausser les résultats et nous conduire à des conclusions erronées.

Heureusement, Excel met à notre disposition des méthodes avancées et des fonctions spécifiques pour nous permettre de calculer des moyennes fiables et précises. Dans ce tutoriel, nous allons explorer différentes méthodes et fonctions pour obtenir des résultats précis et fiables, tout en ignorant les cellules vides et les valeurs égales à zéro.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Qu'est-ce qu'une moyenne et comment la calculer ?

Avant de plonger dans les détails, il est important de comprendre ce qu'est une moyenne et pourquoi elle est importante dans le calcul statistique.

En termes simples, la moyenne (ou moyenne arithmétique) de plusieurs nombres est la somme de ces nombres divisée par leur nombre.

Elle nous donne une idée de la tendance générale des valeurs et nous permet de résumer l'ensemble des données en une seule valeur représentative.

À partir de cette définition, nous comprenons que son calcul est relativement simple, il suffit en effet d’additionner les valeurs de tous les nombres, puis de diviser ce résultat par le nombre de valeurs utilisées pour réaliser cette somme.

Cette moyenne nous donne une estimation de la performance globale, de la tendance ou de la valeur moyenne des données.

Elle est largement utilisée dans divers domaines, que ce soit pour évaluer les résultats scolaires, analyser des données financières ou mesurer des performances dans un contexte professionnel.

En comprenant la définition et le processus de calcul d'une moyenne, nous sommes maintenant prêts à explorer les différentes méthodes et fonctions d'Excel qui nous permettent d'obtenir des moyennes précises tout en ignorant les cellules vides et les valeurs égales à zéro.

2. La fonction MOYENNE

Dans la partie précédente, nous avons vu comment calculer théoriquement une moyenne arithmétique.

Mais comme nous l’avons vu lors de tutoriels précédents, Excel propose évidemment une fonction dédiée, qui va nous simplifier largement cette opération, il s’agit de la fonction MOYENNE.

Celle-ci est donc extrêmement utile pour obtenir rapidement une estimation de la tendance centrale des données.

La syntaxe de la fonction MOYENNE() est la suivante :

 =MOYENNE(plage) 

Elle n’attend donc en argument que la plage des cellules contenant les valeurs pour lesquelles nous souhaitons obtenir la moyenne.

Notez qu’il est également possible de saisir directement les valeurs numériques dont nous voulons calculer la moyenne.

La fonction MOYENNE() peut prendre en compte n'importe quel nombre de valeurs dans la plage spécifiée.

Elle additionne alors automatiquement toutes les valeurs et divise le total par le nombre de valeurs présentes.

Pour illustrer son utilisation, supposons que nous ayons une liste de 20 notes d’étudiants, et nous souhaitons calculer la moyenne de ces notes.

Pour calculer la moyenne de ces notes, nous sélectionnons la cellule B27, puis nous y saisissons la formule suivante :

 =MOYENNE(B7:B26) 

Excel additionne toutes les valeurs de la plage B7 à B26 et divise le total par le nombre de valeurs (20 dans ce cas).

Le résultat affiché est 16,30, ce qui représente la moyenne des notes :

3. Gestion des valeurs non numériques

L'une des particularités de la fonction MOYENNE() est qu'elle ne prend en compte que les valeurs numériques lors du calcul.

Cela signifie que seules les valeurs numériques seront incluses dans la somme initiale et la division effectuée par Excel pour obtenir la moyenne.

Si une valeur non numérique est présente dans la plage de cellules, Excel l'ignorera lors du calcul.

Prenons l'exemple du tableau précédent où nous avons calculé la moyenne des notes des étudiants. Supposons que nous décidions de supprimer la note de Franck (14) en supprimant la cellule B12, pour matérialiser par exemple son absence à l’examen.

Après cette suppression, Excel recalcule automatiquement la moyenne comme si la valeur de Franck n'avait jamais été incluse dans la plage des cellules prises en compte pour le calcul de la moyenne.

Il en va de même si une cellule contient une valeur qui n'est pas numérique.

Reprenons la cellule B12 et saisissons le mot "absent" à la place de la note.

Lorsque nous recalculons la moyenne, Excel ignore la valeur "absent" car elle n'est pas numérique et ne peut pas être incluse dans le calcul.

Ainsi, la moyenne de la classe reste toujours égale à 16,42.

Cependant, il est important de noter qu'une valeur de zéro (0) sera prise en compte par la fonction MOYENNE().

Si nous remplaçons la valeur "absent" par un zéro dans la cellule B12, Excel l'inclura dans le calcul de la somme initiale et de la division pour obtenir la moyenne.

Par conséquent, cela affectera le résultat final de la moyenne.

4. Comment ignorer les valeurs égales à zéro avec la fonction MOYENNE.SI

Si ce comportement par défaut ne correspond pas à ce que nous souhaitons mettre en place, ce qui sera par exemple le cas si nous souhaitons exclure les notes égales à zéro, alors nous pouvons utiliser une évolution de la fonction MOYENNE() qui est la fonction MOYENNE.SI()

En effet, celle-ci permet de calculer la moyenne des valeurs d'une plage de cellules en fonction d'un critère spécifié.

La syntaxe de la fonction MOYENNE.SI() est la suivante :

 =MOYENNE.SI(plage; critère; [plage_moyenne]) 

Où :

• Plage représente la plage de cellules dans laquelle les valeurs sont évaluées.
• Critère est la condition que les valeurs doivent satisfaire pour être incluses dans le calcul de la moyenne. Le critère peut être un nombre, une expression ou un texte.
• Plage_moyenne est un argument facultatif qui spécifie la plage de cellules dont les valeurs seront utilisées pour calculer la moyenne. Si cet argument est omis, Excel utilise automatiquement la plage spécifiée dans l'argument Plage.

Si par défaut, le calcul ne prend en compte que les valeurs numériques, nous pouvons ainsi ajouter un critère supplémentaire pour ne tenir que des valeurs strictement supérieures à zéro.

Nous utiliserons alors la formule suivante :

 =MOYENNE.SI(B7:B26;">0") 

Comme vous pouvez le constater ici, le critère utilisé doit être saisi entre guillemets pour être correctement interprété par Excel.

La moyenne de la classe repasse alors à 16,30, la note de Franck étant totalement exclue du calcul.

5. Démultiplier les critères avec la fonction MOYENNE.SI.ENS

Parfois, nous souhaitons prendre en compte plusieurs critères lors du calcul de la moyenne.

C'est là que la fonction SOMME.SI.ENS entre en jeu.

Cette fonction nous permet de spécifier plusieurs critères de filtrage pour sélectionner les données à inclure dans le calcul.

Par exemple, si nous voulons calculer la moyenne des notes comprises entre 5 et 15, nous utilisons la formule "=SOMME.SI.ENS(plage; ">=5"; plage; "<=15")".

Cette formule va également nous permettre d’exclure les extrêmes du calcul de moyenne, en utilisant en plus les fonctions PETITE.VALEUR et GRANDE.VALEUR :

 =MOYENNE.SI.ENS(B7:B26;B7:B26;"<"&GRANDE.VALEUR(B7:B26;3);B7:B26;">="&PETITE.VALEUR(B7:B26;3))  

Cette formule exclut les valeurs inférieures à la troisième plus grande valeur de la plage obtenues avec la fonction GRANDE.VALEUR.

De plus, seules les valeurs supérieures ou égales à la troisième plus petite valeur de la plage seront prises en compte dans le calcul de la moyenne grâce à PETITE.VALEUR.

5. Comment tenir compte de toutes les valeurs, y compris les cellules vides

Pour finir, si nous souhaitons tenir compte de toutes les cellules de la plage, y compris les cellules vides ou non numériques que nous considérerons comme ayant une valeur nulle, nous allons devoir utiliser une formule constituée des fonctions suivantes :

• La fonction SOMME() : permet de calculer la somme des valeurs numériques d'une plage de cellules.
• La fonction NBVAL() : permet de compter le nombre de valeurs présentes dans une plage de cellules, qu'elles soient numériques ou textuelles (c'est-à-dire toutes les cellules non vides).
• La fonction NB.VIDE() : permet de compter le nombre de cellules vides dans une plage de cellules.

Ainsi, la formule suivante va nous permettre de calculer la moyenne en tenant compte de toutes les cellules de la plage :

 =SOMME(B7:B26)/(NBVAL(B7:B26)+NB.VIDE(B7:B26))  

Dans cette formule :

• "=SOMME(B7:B26)" : Cela calcule la somme des valeurs présentes dans la plage de cellules B7 à B26, en ignorant les cellules vides et non numériques.
• "NBVAL(B7:B26)" : Cela compte le nombre de valeurs présentes dans la plage de cellules B7 à B26, qu'elles soient numériques ou textuelles. Cela inclut toutes les cellules non vides.
• "NB.VIDE(B7:B26)" : Cela compte le nombre de cellules vides dans la plage de cellules B7 à B26.

En divisant la somme des valeurs par la somme du nombre de valeurs et du nombre de cellules vides, nous obtenons la moyenne en prenant en compte toutes les cellules, y compris les cellules vides ou non numériques considérées comme ayant une valeur nulle.

Cette formule est utile lorsque nous souhaitons obtenir une moyenne globale qui tient compte de toutes les cellules de la plage, même si certaines d'entre elles ne contiennent pas de valeurs numériques ou sont vides.

Ainsi, nous obtenons une estimation de la tendance générale des données en incluant toutes les cellules de la plage dans le calcul de la moyenne.