Tom doit faire la moyenne d’une série de cellules non contiguës, à l’exclusion de toutes les cellules pouvant contenir des valeurs nulles. Plus précisément, il doit faire la moyenne des cellules A1, C1, E1, G1 et J1, mais en ne comptant que les cellules qui ne contiennent pas zéro.

Avant d’entrer dans ce qui fonctionne, examinons ce qui ne fonctionne pas.

Tout d’abord, il ne fonctionne pas d’ajouter simplement les cellules et de les diviser par 5; cela ne prend pas en compte les valeurs nulles. Deuxièmement, cela ne fonctionne pas d’utiliser COUNTIF dans le dénominateur de votre formule, comme indiqué ici:

=(A1+C1+E1+G1+J1) / COUNTIF(A1:J1,"<>0")

Cela ne fonctionne pas car il examine et compte les cellules dans toute la plage de A1: J1, pas seulement les cinq cellules que vous souhaitez prendre en compte dans la moyenne. Vous pourriez également penser que vous pouvez sélectionner vos cinq cellules non contiguës, leur donner un nom, puis utiliser le nom dans votre formule. Alors qu’Excel vous permet de créer le nom, ce qui suit donne une erreur:

=SUM(MyCells) / COUNTIF(MyCells,"<>0")

Il semble que COUNTIF ne fonctionnera qu’avec une seule plage contiguë, de sorte que la nature non contiguë de la plage MyCells jette la fonction dans un tailspin. Un problème similaire se produit si vous essayez d’utiliser une plage non contiguë avec la fonction AVERAGEIF:

=AVERAGEIF(MyCells, "<>0")

Étant donné que vous ne pouvez utiliser aucune des fonctions que vous voudrez peut-être utiliser, vous devez vous fier à une formule un peu plus longue pour calculer la moyenne. Vous pouvez calculer la moyenne de ces cinq cellules en appliquant un peu de «supercherie» à votre dénominateur, de cette manière:

=(A1+C1+E1+G1+J1) / ((A1<>0)+(C1<>0)+(E1<>0)+(G1<>0)+(J1<>0))

L’évaluation effectuée sur chaque cellule du dénominateur renvoie un 1 (pour True) ou un 0 (pour False) selon que la cellule contient une valeur non nulle ou non. Cette série de valeurs est additionnée, fournissant le nombre nécessaire de cellules non nulles pour le dénominateur.

Notez que la discussion ici a porté uniquement sur le dénominateur de la formule, pas sur le numérateur. La raison est simple: vous pouvez ajouter les cinq valeurs au numérateur; aucune valeur là n’a vraiment d’importance

Le seul endroit où ils comptent est le dénominateur, ce qui rend le calcul de cette moyenne si délicat.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (7845) s’applique à Microsoft Excel 2007, 2010, 2013, 2016, 2019 et Excel dans Office 365. Vous pouvez trouver une version de cette astuce pour l’ancienne interface de menu d’Excel ici:

link: / excel-Averaging_a_Non-Contiguous_Range [Moyenne d’une plage non contiguë].