Compter le nombre de cellules qui ne contiennent pas plusieurs valeurs

Normalement, il est facile de compter les cellules qui ne contiennent pas une valeur spécifique avec la fonction SOMME.SI. Cet article vous guidera à travers les étapes pour compter le nombre de cellules qui ne contiennent pas plusieurs valeurs dans une plage spécifiée dans Excel.

Comment compter les cellules qui ne contiennent pas plusieurs valeurs ?

Comme le montre la capture d'écran ci-dessous, pour compter les cellules dans B3:B11 qui ne contiennent pas les valeurs listées dans D3:D4, vous pouvez procéder comme suit.

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 sous forme de formule matricielle. Après avoir appliqué la formule, si des accolades apparaissent autour de celle-ci, 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. Entrez la formule ci-dessous et appuyez simultanément sur Ctrl + Maj + Entrée pour obtenir le résultat.

=SOMME(1-(PRODUITMAT(--(ESTNUM(CHERCHE(TRANSPOSE(D3:D4);B3:B11)));LIGNE(D3:D4)^0)>0))

Comment ces formules fonctionnent-elles ?

=SOMME(1-(PRODUITMAT(--(ESTNUM(CHERCHE(TRANSPOSE(D3:D4);B3:B11)));LIGNE(D3:D4)^0)>0))

1) --(ESTNUM(CHERCHE(TRANSPOSE(D3:D4);B3:B11))) :

• TRANSPOSE(D3:D4) : La fonction TRANSPOSE change l'orientation de D3:D4 et renvoie {"count","blank"};
• CHERCHE({"count","blank"};B3:B11): La fonction CHERCHE ici trouve la position des sous-chaînes "count" et "blank" dans la plage B3:B11 et renvoie un tableau comme {#VALEUR!,#VALEUR!;#VALEUR!,#VALEUR!;1,#VALEUR!;#VALEUR!,8;1,#VALEUR!;#VALEUR!,#VALEUR!;#VALEUR!,#VALEUR!;1,
  #VALEUR!;1,7}.
• Dans ce cas, chaque cellule dans B3:B11 sera recherchée deux fois car il y a deux valeurs que vous souhaitez exclure lors du comptage des cellules, donc vous obtiendrez 18 valeurs dans le tableau. Chaque nombre dans le tableau indique la position du premier caractère de "count" ou "blank" dans chaque cellule de B3:B11.
• ESTNUM{#VALEUR!,#VALEUR!;#VALEUR!,#VALEUR!;1,#VALEUR!;#VALEUR!,8;1,#VALEUR!;#VALEUR!,#VALEUR!;#VALEUR!,
  #VALEUR!;1,#VALEUR!;1,7}: La fonction ESTNUM renvoie VRAI si elle rencontre des nombres dans le tableau, et FAUX si elle rencontre des erreurs. Ici, le résultat retourné est {FAUX,FAUX;FAUX,FAUX;VRAI,FAUX;FAUX,VRAI;VRAI,FAUX;FAUX,FAUX;FAUX,FAUX;VRAI,FAUX;
  VRAI,VRAI}.
• --({FAUX,FAUX;FAUX,FAUX;VRAI,FAUX;FAUX,VRAI;VRAI,FAUX;FAUX,FAUX;FAUX,FAUX;VRAI,
  FAUX;VRAI,VRAI}): Ces deux signes moins convertissent "VRAI" en 1 et "FAUX" en 0. Vous obtenez alors un nouveau tableau comme {0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1}.

2) LIGNE(D3:D4)^0 : La fonction LIGNE renvoie les numéros de ligne de la référence de cellule : {3;4}, puis l'opérateur d'exponentiation (^) renvoie le résultat des nombres 3 et 4 élevés à la puissance 0, et finalement retourne le résultat comme {1;1}.

3) PRODUITMAT({0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1},{1;1}) : La fonction PRODUITMAT renvoie le produit matriciel de ces deux tableaux : {0;0;1;1;1;0;0;1;2} pour correspondre aux données originales. Tout nombre non nul dans le tableau représente une valeur où au moins une des chaînes exclues a été trouvée, et zéro indique qu'aucune chaîne exclue n'a été trouvée.

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

• {0;0;1;1;1;0;0;1;2}>0: Ici, vérifiez si chaque nombre dans le tableau est supérieur à 0. Si un nombre est supérieur à 0, renvoie VRAI, sinon renvoie FAUX. Vous obtiendrez ensuite un nouveau tableau comme {FAUX;FAUX;VRAI;VRAI;VRAI;FAUX,FAUX,VRAI;VRAI}.
• 1-{FAUX;FAUX;VRAI;VRAI;VRAI;FAUX,FAUX,VRAI;VRAI}: Comme nous devons compter uniquement les cellules qui ne contiennent pas les valeurs spécifiées, nous devons inverser ces valeurs dans le tableau en les soustrayant de 1. Ici, l'opérateur mathématique convertit automatiquement les valeurs VRAI et FAUX en 1 et 0, et finalement retourne le résultat comme {1;1;0;0;0;1;1;0;0}.
• SOMME{1;1;0;0;0;1;1;0;0}: La fonction SOMME additionne tous les nombres dans le tableau et retourne le résultat final comme 4.

Fonctions connexes

Formules connexes