Compter nombre de valeurs texte différentes

Résumé

Pour calculer dans Excel le nombre de valeurs texte différentes, il existe 2 solutions :

• Utiliser la fonction NBVAL, la fonction FILTRE et la fonction UNIQUE ;
• ou utiliser la fonction SOMMEPROD, la fonction SIERREUR et la fonction NB.SI.

Dans le premier cas on pourra choisir de compter ou non une cellule vide tandis que dans l’autre cas nous serons obligés d’ignorer toutes les cellules vides.

La fonction UNIQUE et la fonction FILTRE sont disponibles depuis la version Excel d’Office 365.

Avec Office 365 / On compte 1 cellule vide
= NBVAL( UNIQUE( B3:B14 ; FAUX ; FAUX ) )

Avec Office 365 / On ne compte pas les cellules vides
= NBVAL( UNIQUE( FILTRE( B3:B14 ; B3:B14 <> "" ; "" ) ; FAUX ; FAUX ) )

Sans Office 365 / On ne compte pas les cellules vides
= SOMMEPROD( SIERREUR( 1/NB.SI( B3:B14 ; B3:B14 ) ; 0 ) )

Ces formules comptent le nombre de valeurs texte différentes dans la plage de cellules B3:B14. La première formule comptabilise en plus une cellule vide.

Formule Excel – Compter nombre de valeurs texte différentes

Avec Office 365 / On compte 1 cellule vide
= NBVAL( 
    UNIQUE( plage_cellules ; FAUX ; FAUX ) 
  )

Avec Office 365 / On ne compte pas les cellules vides
= NBVAL( 
   UNIQUE( 
     FILTRE( plage_cellules ; plage_cellules <> "" ; "" ) ; 
   FAUX ; FAUX ) 
  )

Sans Office 365 / On ne compte pas les cellules vides
= SOMMEPROD( 
    SIERREUR( 1/NB.SI( plage_cellules ; plage_cellules ) ; 0 ) 
  )

Explications

Toutes les formules présentées ont pour objectif de compter le nombre de valeurs uniques dans une plage de cellules.

Compter les valeurs texte avec NBVAL + UNIQUE + FILTRE (dès Excel 365)

La fonction FILTRE permet de générer un tableau en appliquant un filtre. Dans notre cas, nous voulons filtrer (ou non) les cellules pour ne garder que celles contenant une valeur (on ignore les cellules vides).

= FILTRE( tableau ; critère(s) ; valeur_si_vide )
= FILTRE( B3:B14 ; B3:B14 <> "" ; "" )

Une fois les lignes vides traitées, la fonction UNIQUE permet de réduire le résultat aux seules valeurs uniques. Cette fonction nous laisse la possibilité de :

• Sélectionner toutes les valeurs texte apparaissant une ou plusieurs fois (3e argument = FAUX) ;
• Sélectionner uniquement les valeurs apparaissant une fois (3e argument = VRAI) ;

= UNIQUE( matrice ; par_ligne_ou_col ; apparait_une_fois )
= UNIQUE( FILTRE( B3:B14 ; B3:B14 <> "" ; "" ) ; FAUX ; FAUX )

Enfin, la fonction NBVAL va compter le nombre de valeurs textuelles.

= NBVAL( plage_cellules )
= NBVAL( UNIQUE( FILTRE( B3:B14 ; B3:B14 <> "" ; "" ) ; FAUX ; FAUX ) )

Compter les valeurs texte avec SOMMEPROD + SIERREUR + NB.SI

La fonction NB.SI permet de compter le nombre de cellules répondant à un critère. Ici, on indique à la fois notre plage de cellules comme plage initiale et critère. Cela aura pour effet de générer un tableau de même dimension que notre plage initiale dans laquelle nous aurons le nombre d’occurrences pour chaque valeur.

= NB.SI( plage_cellules ; critère )

= NB.SI( B3:B14 ; B3:B14 )

= {2;2;0;2;0;2;0;1;0;1;0;1}

Le résultat indique que « chaise » apparaît 2 fois, « bureau 1 » apparaît 2 fois, «  » est ignoré,…

Les valeurs trouvées servent de numérateur. Ainsi, toutes les valeurs inférieures à 1 indiquent une valeur texte présente à plusieurs reprises. Les valeurs renvoyant #DIV/0! correspondent aux cellules vides.

= 1 / {2;2;0;2;0;2;0;1;0;1;0;1}

= {0,5;0,5;#DIV/0!;0,5;#DIV/0!;0,5;#DIV/0!;1;#DIV/0!;1;#DIV/0!;1}

La fonction SIERREUR remplace les #DIV/0! par 0.

= SIERREUR( valeur ; valeur_si_erreur )

= SIERREUR( {0,5;0,5;#DIV/0!;0,5;#DIV/0!;0,5;#DIV/0!;1;#DIV/0!;1;#DIV/0!;1} ;
           0
  )

= {0,5;0,5;0;0,5;0;0,5;0;1;0;1;0;1}

Enfin, la fonction SOMMEPROD vient faire la somme des valeurs trouvées.

= SOMMEPROD( {0,5;0,5;0;0,5;0;0,5;0;1;0;1;0;1} )
--> Résultat : 5

Formules en lien

• Compter le nombre de cellules égales à un texte
• Compter le nombre de cellules contenant du texte
• Compter le nombre de cellules contenant une partie du texte (mot, caractère)
• Compter le nombre de cellules non vides
• Compter le nombre de cellules contenant une formule
• NB.SI avec plusieurs critères
• SOMMEPROD avec SI
• Trouver des doublons avec NB.SI
• Somme si date est entre deux dates
• MIN.SI.ENS sensible à la casse
• Compter le nombre de cellules contenant un texte différent de
• Nombre si date comprise entre

Retrouvez d’autres astuces dans l’article sur la fonction NB.SI.