Compter nombre de valeurs texte différentes

Fonctions utilisées :

Article mis à jour le 20/07/2023

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

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

4.9/5 - (23 votes)

Vous avez apprécié cet article ?

Pour me soutenir, cliquez sur cette publicité. Pas besoin d’acheter, un clic suffit.

Merci. 🤩

Annonces

Yann

Créateur de Cleex, ancien contrôleur de gestion dans l’industrie, j’ai à coeur de vous proposer des ressources de qualité pour vous aider à travailler avec Excel.