Tutoriel Excel: Comment compter sans doublons dans Excel

Cours et tutoriel excel / cours excel gratuit / tutoriel excel gratuit 

Bienvenue dans ce cours Excel qui vous montre comment compter sans doublons dans Excel

L’analyse des données implique généralement de grands ensembles de données et, à un moment donné, il peut être nécessaire de connaître le nombre de valeurs qui n’apparaissent qu’une seule fois dans l’ensemble de données. Dans ce cas, vous compterez sans doublons. Les valeurs uniques sont des valeurs qui n’apparaissent qu’une seule fois dans un jeu de données. Si vous êtes confronté à des montagnes de données, compter sans doublons peut être très ardu. Plus encore, Excel n’a pas de formule spéciale pour compter sans doublons. Cependant, il y a toujours une issue ! Ce didacticiel servira de guide sur la façon de compter des valeurs uniques et distinctes sans les dupliquer.

Jetez un œil aux chiffres du tableau ci-dessus ; les valeurs uniques ne sont pas dupliquées. Ils n’apparaissent pas plus d’une fois. Alors que les valeurs distinctes sont les différents nombres dans la collection. Dans le tableau ci-dessous, nous avons séparé les valeurs uniques des valeurs distinctes.

Excel: utilisation de la formule matricielle NB.SI

La fonction NB.SI compte la fréquence d’occurrence de chaque valeur dans la plage. Pour obtenir le nombre de valeurs uniques, vous devez les additionner. Vous pouvez le faire efficacement en combinant les fonctions SOMME et NB.SI. Une combinaison de deux fonctions peut compter des valeurs uniques sans duplication.

Ci-dessous la syntaxe :

=SUM(IF(COUNTIF(data, data)=1,1,0)).

La formule contient trois fonctions distinctes – SUM, IF et COUNTIF.

La fonction NB.SI” compte le nombre de fois qu’un nombre particulier apparaît dans la plage.

“Fonction SI” analyse les résultats renvoyés par la fonction “NB.SI”. Il conserve les 1 pour les valeurs uniques et remplace les autres valeurs par “0”.

Remarque : Appuyez toujours sur Ctrl + Maj + Entrée lors de la saisie de votre formule matricielle.

CTRL + SHIFT + ENTER permettent à Excel de reconnaître la formule comme une fonction matricielle. valeur unique= =SOMME(SI(NB.SI(A2:A10, A2:A10)=1,1,0))

Vous pouvez également utiliser la formule SUMPRODUCT pour éviter l’utilisation de CTRL+SHIFT+ENTER.

Ci-dessous la syntaxe :

=SOMMEPROD(1/NB.SI(plage, critères)).

Dans l’exemple ci-dessous, nous avons une liste d’éléments avec des doublons.

Si nous utilisons la formule SOMME pour compter le nombre total d’éléments, il y aura des doublons. Pour exclure les doublons, vous devez suivre ces étapes.

Étape 1 : Accédez à la cellule D1 et entrez cette formule “=SUMPRODUCT(1/COUNTIF( B1:B11,B1:B11)). B1:B11 est la plage de tableau dans laquelle vous souhaitez compter le nombre total de valeurs uniques dans la liste.

Si nous utilisons la formule SOMME pour compter le nombre total d’éléments, il y aura des doublons. Pour exclure les doublons, vous devez suivre ces étapes.

Étape 1 : Accédez à la cellule D1 et entrez cette formule “=SUMPRODUCT(1/COUNTIF( B1:B11,B1:B11)). B1:B11 est la plage de tableau dans laquelle vous souhaitez compter le nombre total de valeurs uniques dans la liste.

Étape 2 : Appuyez sur Entrée et les résultats seront affichés dans la cellule D1. D’après les résultats affichés (6), nous pouvons voir qu’il n’y a pas de doublons.

La formule ci-dessus compte les valeurs des six éléments A/B/C/D/E/F.

Utilisation d’une combinaison de SUM, IF, FREQUENCY, MATCH et ROW Function

Si nous voulons compter des valeurs uniques qui excluent tous les doublons (qui apparaissent dans plus d’un produit), utilisez la combinaison de ces fonctions.

La fonction somme permet d’additionner les valeurs.
Pour chaque condition vraie de la fonction SI, attribuez la valeur 1.
La fonction FRÉQUENCE vous permet de compter le nombre de valeurs en ignorant les textes et les zéros. Dans la première occurrence de la valeur distincte, elle renvoie un nombre égal au nombre d’occurrences de cette valeur. Elle renvoie zéro pour les occurrences qui ont la même valeur après la première occurrence.
La fonction MATCH renvoie la position de la valeur de texte dans une plage de tableau. La valeur de retour agit comme argument de la fonction pour FREQUENCE.
La fonction ROW renvoie le numéro de référence de la ligne.

Exemple : Saisissez la formule suivante pour compter les valeurs uniques en excluant tous les doublons

=SOMME(SI(FREQUENCE(MATCH(B1:B11,B1:B11,0),LIGNE(B1:B11)-LIGNE(B1)+1)=1,1))

Appuyez sur Entrée. La valeur unique est 3 (élément D/E/F).

Utilisation de la fonction ISNUMBER pour compter les nombres numériques

La fonction que nous avons utilisée précédemment compte, à la fois les textes et les nombres, sans duplication. Pour compter uniquement les chiffres sans duplication, vous devez inclure la fonction ISNUMBER dans la formule pour trouver des valeurs uniques.

=SUM(IF(ISNUMBER(A2:A10)*COUNTIF(A2:A10,A2:A10)=1,1,0))

Remarque : Appuyez toujours sur Ctrl + Maj + Entrée lors de la saisie de votre formule matricielle.

Pendant ce temps, l’avantage de cette fonction est qu’elle compte également les dates et les heures.

Utilisation de la fonction ISTEXT pour compter les valeurs de texte

Vous pouvez compter le nombre de textes sans duplication en incluant la fonction ISTEXT dans la formule matricielle comme indiqué ci-dessous :

=SOMME(SI(ISTEXT(A2:A10)*NB.SI(A2:A10,A2:A10)=1,1,0))

Cette formule affichera le nombre de textes uniques. Il exclut les erreurs, les cellules vides, les nombres logiques, les nombres, etc.

Appuyez toujours sur Ctrl + Maj + Entrée lors de la saisie de votre formule matricielle.

Utilisation du tableau croisé dynamique pour compter les valeurs de texte

Un moyen simple de compter la valeur est de créer le tableau croisé dynamique et d’utiliser le nombre d’éléments

Créer un tableau croisé dynamique

Utilisez le nombre d’articles

Tableau de sortie