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
