Comment compter les valeurs uniques avec COUNTIFS dans Excel ?

Compter les valeurs uniques avec COUNTIFS dans Excel : Le guide complet

La fonction COUNTIFS d'Excel est un outil puissant pour compter des cellules qui répondent à plusieurs critères. Bien qu'elle ne soit pas directement conçue pour compter les valeurs uniques, une combinaison astucieuse avec d'autres fonctions permet d'atteindre cet objectif. Cet article vous guidera à travers différentes méthodes pour compter les valeurs uniques en utilisant COUNTIFS, avec des exemples pratiques et des explications détaillées.

Comprendre la fonction COUNTIFS

Avant de plonger dans le comptage des valeurs uniques, il est essentiel de bien comprendre le fonctionnement de la fonction COUNTIFS. Sa syntaxe est la suivante :

=COUNTIFS(plage_critères1, critère1, [plage_critères2, critère2], ...)

• plage_critères1 : La première plage de cellules à évaluer.
• critère1 : Le critère à appliquer à la première plage.
• [plage_critères2, critère2], ... : Plages et critères supplémentaires (jusqu'à 127).

COUNTIFS renvoie le nombre de cellules qui répondent à tous les critères spécifiés. Par exemple, =COUNTIFS(A1:A10, ">10", B1:B10, "<20") compte le nombre de cellules dans la plage A1:A10 qui sont supérieures à 10, et dans la plage B1:B10 qui sont inférieures à 20.

Méthode 1 : Utilisation de COUNTIFS et SOMME

Cette méthode combine COUNTIFS avec la fonction SOMME pour compter les valeurs uniques. L'idée est de diviser 1 par le nombre d'occurrences de chaque valeur, puis de sommer ces fractions. Chaque valeur unique contribuera ainsi à la somme finale avec 1.

Étapes :

1. Identifier la plage de données : Déterminez la plage de cellules contenant les données à analyser (par exemple, A1:A10).

2. Créer la formule : Utilisez la formule suivante :

   =SOMME(1/COUNTIFS(A1:A10, A1:A10))

   • COUNTIFS(A1:A10, A1:A10) : Pour chaque cellule dans la plage A1:A10, COUNTIFS compte le nombre de fois où cette valeur apparaît dans la même plage. Par exemple, si la valeur dans A1 apparaît 3 fois dans la plage A1:A10, COUNTIFS renverra 3.
   • 1/COUNTIFS(A1:A10, A1:A10) : On divise 1 par le résultat de COUNTIFS. Dans l'exemple précédent, cela donnerait 1/3.
   • SOMME(...) : La fonction SOMME additionne toutes ces fractions. Chaque valeur unique contribuera à la somme avec 1.

3. Valider la formule comme une formule matricielle : Après avoir entré la formule, appuyez sur Ctrl+Maj+Entrée (ou Cmd+Maj+Entrée sur Mac) pour la valider comme une formule matricielle. Excel ajoutera automatiquement des accolades {} autour de la formule, indiquant qu'il s'agit d'une formule matricielle. Ne tapez pas les accolades vous-même.

Exemple :

Supposons que la plage A1:A10 contienne les valeurs suivantes :

La formule =SOMME(1/COUNTIFS(A1:A10, A1:A10)) validée comme une formule matricielle renverra 5, car il y a 5 valeurs uniques (Pomme, Banane, Orange, Kiwi, Fraise).

Avantages :

• Simple à comprendre.
• Ne nécessite pas de colonnes auxiliaires.

Inconvénients :

• Nécessite d'être validée comme une formule matricielle, ce qui peut être déroutant pour les débutants.
• Peut être lente avec de grandes plages de données.
• Si la plage contient des cellules vides, la formule renverra une erreur #DIV/0! Pour éviter cela, utilisez la fonction SIERREUR : =SIERREUR(SOMME(1/COUNTIFS(A1:A10, A1:A10)), 0)

Méthode 2 : Utilisation de COUNTIFS et une colonne auxiliaire

Cette méthode utilise une colonne auxiliaire pour déterminer si une valeur est unique et la première fois qu'elle apparaît. Ensuite, on somme les valeurs de cette colonne.

Étapes :

1. Identifier la plage de données : Déterminez la plage de cellules contenant les données à analyser (par exemple, A1:A10).
2. Créer une colonne auxiliaire : Insérez une colonne à côté de la plage de données (par exemple, en colonne B).

3. Entrer la formule dans la première cellule de la colonne auxiliaire : Dans la cellule B1, entrez la formule suivante :

   =SI(COUNTIFS($A$1:A1, A1)=1, 1, 0)

   • COUNTIFS($A$1:A1, A1) : Compte le nombre de fois où la valeur de A1 apparaît dans la plage $A$1:A1. Le $ devant le premier A1 fixe la première cellule de la plage, de sorte que la plage s'étend à mesure que la formule est copiée vers le bas.
   • SI(COUNTIFS($A$1:A1, A1)=1, 1, 0) : Si la valeur de A1 apparaît une seule fois dans la plage $A$1:A1 (c'est-à-dire, c'est la première fois qu'elle apparaît), la formule renvoie 1. Sinon, elle renvoie 0.

4. Copier la formule vers le bas : Copiez la formule de B1 vers le bas jusqu'à la dernière ligne de la plage de données (par exemple, jusqu'à B10).

5. Sommer la colonne auxiliaire : Utilisez la fonction SOMME pour additionner les valeurs de la colonne auxiliaire :

   =SOMME(B1:B10)

Exemple :

Reprenons l'exemple précédent :

La formule =SOMME(B1:B10) renverra 5.

Avantages :

• Plus facile à comprendre que la méthode matricielle.
• Moins sensible aux erreurs.

Inconvénients :

• Nécessite une colonne auxiliaire.

Méthode 3 : Utilisation de la fonction UNIQUE (Excel 365 et versions ultérieures)

Si vous utilisez Excel 365 ou une version ultérieure, la fonction UNIQUE simplifie considérablement le comptage des valeurs uniques.

Étapes :

1. Extraire les valeurs uniques : Utilisez la fonction UNIQUE pour extraire les valeurs uniques de la plage de données (par exemple, A1:A10) : =UNIQUE(A1:A10)
2. Compter les valeurs uniques : Utilisez la fonction NBVAL pour compter le nombre de valeurs dans la plage renvoyée par la fonction UNIQUE : =NBVAL(UNIQUE(A1:A10))

Exemple :

En utilisant les mêmes données que précédemment, la formule =NBVAL(UNIQUE(A1:A10)) renverra 5.

Avantages :

• Extrêmement simple et concise.
• Ne nécessite pas de colonnes auxiliaires.
• Ne nécessite pas de validation matricielle.

Inconvénients :

• Uniquement disponible dans Excel 365 et versions ultérieures.

Conseils et astuces

• Ignorer les cellules vides : Si votre plage de données contient des cellules vides et que vous ne voulez pas les compter comme des valeurs uniques, vous pouvez utiliser la fonction FILTRE pour exclure les cellules vides avant d'utiliser COUNTIFS ou UNIQUE. Par exemple, avec la méthode 3 : =NBVAL(UNIQUE(FILTRE(A1:A10,A1:A10<>"")))
• Tenir compte de la casse : Par défaut, COUNTIFS est insensible à la casse. Si vous devez faire la distinction entre majuscules et minuscules, vous pouvez utiliser les fonctions EXACT et SOMMEPROD en combinaison avec COUNTIFS.
• Utiliser des plages nommées : Pour rendre vos formules plus lisibles et faciles à maintenir, vous pouvez définir des plages nommées pour vos données.

Erreurs courantes à éviter

• Oublier la validation matricielle : Si vous utilisez la méthode 1, n'oubliez pas de valider la formule comme une formule matricielle en appuyant sur Ctrl+Maj+Entrée.
• Utiliser des références relatives incorrectes : Lors de la création de la colonne auxiliaire (méthode 2), assurez-vous d'utiliser des références absolues ($) et relatives correctement pour que la formule se copie correctement vers le bas.
• Confusion entre NB et NBVAL : Utilisez NBVAL pour compter les cellules non vides, et NB pour compter les cellules contenant des nombres. Pour compter les valeurs uniques, NBVAL est généralement plus approprié.

Conclusion

Compter les valeurs uniques dans Excel avec COUNTIFS peut sembler complexe au premier abord, mais en comprenant les différentes méthodes et en suivant les étapes attentivement, vous pouvez facilement extraire des informations précieuses de vos données. Choisissez la méthode qui convient le mieux à votre version d'Excel et à la complexité de vos données. Que vous optiez pour la méthode matricielle, la colonne auxiliaire ou la fonction UNIQUE, vous êtes maintenant équipé pour maîtriser le comptage des valeurs uniques dans Excel.