Compter le nombre de cellules qui ne contiennent pas plusieurs valeurs

Généralement, il est facile de compter les cellules qui ne contiennent pas une valeur spécifique à l’aide de la fonction NB.SI. Cet article vous explique pas à pas comment compter les cellules qui ne contiennent aucune des valeurs spécifiées dans une plage délimitée sous Excel.

Comment compter les cellules qui ne contiennent aucune des valeurs spécifiées ?

Comme le montre la capture d’écran ci-dessous, pour compter les cellules de la plage B3:B11 qui ne contiennent pas les valeurs listées dans D3:D4, suivez ces étapes.

Formule générique

{=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(criteria_range),range))),ROW(criteria_range)^0)>0))}

Arguments

Remarque : Cette formule doit être saisie en tant que formule matricielle. Une fois appliquée, si elle est entourée d’accolades, cela signifie qu’une formule matricielle a été créée avec succès.

Comment utiliser cette formule ?

1. Sélectionnez une cellule vide pour afficher le résultat.

2. Saisissez la formule ci-dessous dans cette cellule, puis appuyez simultanément sur les touches Ctrl + Maj + Entrée pour obtenir le résultat.

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>0))

Comment ces formules fonctionnent-elles ?

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>0))

1) --(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))):

• TRANSPOSE(D3:D4): La fonction TRANSPOSE inverse l’orientation de D3:D4 et renvoie {« count », « blank »};
• SEARCH({“count”,”blank”},B3:B11) : La fonction RECHERCHE détermine ici la position des sous-chaînes « count » et « blank » dans la plage B3:B11 et renvoie un tableau sous la forme {#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;1;#VALEUR!;#VALEUR!;8;1;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;1;
  #VALEUR!;1,7}.
• Dans ce cas, chaque cellule de la plage B3:B11 sera analysée deux fois, puisque vous souhaitez exclure deux valeurs lors du comptage ; vous obtiendrez ainsi 18 résultats dans le tableau. Chaque nombre indique la position du premier caractère des termes « count » ou « blank » dans les cellules de B3:B11.
• ISNUMBER{#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,#VALUE!;#VALUE!,8;1,#VALUE!;#VALUE!,#VALUE!;#VALUE!,
  #VALUE!;1,#VALUE!;1,7}: La fonction ESTNUMÉRIQUE renvoie VRAI si elle rencontre des nombres dans le tableau, et FAUX si elle rencontre des erreurs. Ici, le résultat obtenu est {FAUX;FAUX;FAUX;FAUX;VRAI;FAUX;FAUX;VRAI;VRAI;FAUX;FAUX;FAUX;FAUX;FAUX;VRAI;FAUX;
  VRAI;VRAI}.
• --({FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;TRUE,)
  FALSE;TRUE,TRUE}): Ces deux signes moins convertissent « VRAI » en 1 et « FAUX » en 0. Vous obtenez alors un nouveau tableau sous la forme {0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1}.

2)ROW(D3:D4)^0 : La fonction LIGNE renvoie les numéros de ligne de la référence de cellule, soit {3;4}. L’opérateur d’exponentiation (^) élève ensuite ces nombres à la puissance 0, ce qui donne {1;1}.

3) MMULT({0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1},{1;1}): La fonction MMULT renvoie le produit matriciel de ces deux tableaux : {0;0;1;1;1;0;0;1;2}, ce qui permet d’aligner les résultats avec les données initiales. Toute valeur non nulle dans ce tableau indique qu’au moins l’une des chaînes exclues a été détectée, tandis qu’un zéro signifie qu’aucune chaîne exclue n’a été trouvée.

4) SUM(1-{0;0;1;1;1;0;0;1;2}>0):

• {0;0;1;1;1;0;0;1;2}>0 : Ici, on vérifie si chaque nombre du tableau est supérieur à 0. Dès qu’un nombre l’est, la fonction renvoie VRAI ; sinon, elle renvoie FAUX. Vous obtenez alors un nouveau tableau sous la forme {FAUX;FAUX;VRAI;VRAI;VRAI;FAUX;FAUX;VRAI;VRAI}.
• 1-{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE,FALSE,TRUE;TRUE} : Puisque nous devons compter uniquement les cellules qui ne contiennent pas les valeurs spécifiées, nous inversons ces valeurs du tableau en les soustrayant de 1. L’opérateur mathématique convertit automatiquement les valeurs VRAI et FAUX en 1 et 0, et renvoie finalement le résultat sous la forme {1;1;0;0;0;1;1;0;0}.
• SUM{1;1;0;0;0;1;1;0;0}La fonction SOMME additionne tous les nombres du tableau et renvoie le résultat final : 4.

Fonctions associées

Formules associées