Tuto Excel : Compter les valeurs distinctes d'un tableau croisé dynamique

Rédigé par Hanane Mouqqadim, Publié le 08 Février 2021, Mise à jour le Dimanche, 30 Janvier 2022 20:43
Participez au vote ☆☆☆☆☆★★★★★

Dans ce tutoriel, nous expliquons différentes façons de compter les valeurs distinctes dans un tableau croisé dynamique Excel.

Les tableaux croisés dynamiques Excel, vous permettent de faire des calculs et d’exécuter plusieurs tâches en quelques clics. Cependant, certaines choses nécessiteraient quelques étapes supplémentaires ou un peu de temps et de travail. Par exemple, compter des valeurs distinctes dans un tableau croisé dynamique n’est pas une tâche intuitive. Par défaut, lorsque vous utilisez une plage de données contenant des valeurs en double pour créer un tableau croisé dynamique, le tableau croisé dynamique générée comptera tous les enregistrements sans exceptions. Mais, parfois, on souhaite compter les valeurs distinctes dans une colonne pour obtenir le bon résultat. Vous pourriez par exemple avoir des questions telles que :

  • Combien de clients distincts ont effectué des achats dans chaque ville ?
  • Combien de produits distincts ont été vendus par chaque fournisseur ?
  • Combien de vendeurs distincts ont vendu chaque produit par ville ?

Bien qu’il n'y ait pas de fonction intégrée pour compter les valeurs distinctes dans un tableau croisé dynamique normal, dans ce tutoriel, vous découvrirez des solutions de contournement faciles et efficaces. Suivez le reste du tutoriel pour plus de détails.

Table des matières

Différence entre valeurs distinctes et valeurs uniques

Méthode 1 : ajouter une colonne d'assistance dans l'ensemble de données

Méthode 2 : ajouter les données au modèle données et calculer le total distinct

Comment calculer le nombre des valeurs uniques et non des valeurs distinctes

Différence entre valeurs distinctes et valeurs uniques

Avant de vous montrer comment vous pouvez compter des valeurs distinctes, expliquons d’abord la différence entre « valeur distincte » et « valeur unique ». Pour la plupart de vous, les deux concepts peuvent paraitre ou signifier la même chose, mais ce n’est pas le cas.

Pour vous montrer la différence, nous avons répertorié dans l’exemple ci-dessous les noms uniques et distincts de produits :

Examinons cet exemple pour expliquer la différence :

  • Les valeurs uniques : sont les valeurs qui n'apparaissent qu'une seule fois. Cela signifie que tous les noms qui ont plus d’une seule occurrence ne sont pas uniques. Par exemple, « Orange » et « Kiwi » sont citées une seule fois dans la colonne « Nom produits », ainsi, ils s’agissent des valeurs uniques.
  • Les valeurs distinctes : sont les valeurs qui apparaissent au moins une fois dans l'ensemble de données. Cela inclus donc les valeurs qui apparaissent une, deux, trois ou plusieurs fois. Dans l’exemple de la capture ci-dessus, « Pomme » et « Banane » sont des valeurs qui apparaissent deux fois dans la liste des produits et sont donc des valeurs distinctes. Aussi « Orange » et « Kiwi » sont des valeurs qui apparaissent une seule fois et sont également des valeurs distinctes.

Méthode 1 : ajouter une colonne d'assistance dans l'ensemble de données

Prenons l’exemple ci-dessous. Nous avons un ensemble de données de ventes qui affiche la date, le fournisseur, la ville et le produit vendu :

Avec cet ensemble de données, supposons que vous souhaitiez trouver la réponse aux questions suivantes :

  • Combien y a-t-il de fournisseurs dans chaque ville ?
  • Combien de fournisseurs ont vendu les bananes en 2019 ?

Bien que vous puissiez créer un tableau croisé dynamique pour résumer instantanément l’ensemble de ces données en quelques clics, l’affichage de nombre de valeurs distinctes vous demandera quelques étapes supplémentaires.

Le grand avantage de cette première méthode est que vous pouvez l’utiliser dans n'importe quelle version d'Excel. Le principe de cette solution consiste à ajouter une colonne d'assistance aux données source que vous utiliserez par la suite pour compter les valeurs distinctes dans votre tableau croisé dynamique. Commençons d'abord par ajouter une colonne d'assistance et compter les valeurs distinctes.

  1. Pour commencer, dans une nouvelle colonne à côté des données, veuillez saisir la formule ci-dessous. Pour notre exemple nous allons entrer la formule dans la cellule « E2 » :

=SI(NB.SI.ENS($B$2:B2;B2;$C$2:C2;C2)> 1;0;1)

  1. Appliquez la formule à toutes les autres cellules contenant des données dans les colonnes adjacentes comme illustré dans la capture ci-dessous :

La formule que nous avons créée utilise la fonction NB.SI.ENS pour calculer le nombre de fois qu'un fournisseur apparaît dans une ville. La fonction NB.SI.ENS garantit alors que seules les valeurs distinctes sont comptées et non les répétitions.

Le nombre d’occurrence retourné par la fonction NB.SI.ENS est envoyé à la fonction SI qui renvoie « 1 » lorsqu'un fournisseur apparaît pour une ville pour la première fois et renvoie «  » lorsqu'il apparaît pour plusieurs fois.

Après avoir ajouté la colonne d'assistance, vous devrez obtenir un résultat comme illustré dans a capture ci-dessous :

Les données sources sont maintenant prêtes, et la colonne d’assistance est bien configurée. Vous pouvez maintenant créer votre tableau croisé dynamique et utiliser la colonne d'assistance pour obtenir le nombre de fournisseurs distincts dans chaque ville. Suivez le reste des étapes.

  1. Sélectionnez votre jeu donné ou cliquez simplement sur une cellule dans l’ensemble de données.
  2. Créez le tableau croisé dynamique en cliquant sur le bouton de commande « Tableau croisé dynamique» qui se trouve dans le groupe « Tableaux » dans l'onglet « Insertion » :
  1. Lorsque vous cliquez dessus, la boîte de dialogue « Créer un tableau croisé dynamique» s’ouvre. Assurez-vous que l’option « Sélectionner un tableau ou une plage » est sélectionnée et vérifiez que la plage de cellules dans la zone de texte « Tableau/plage » est correcte et que la colonne d’assistance est aussi incluse :
  1. Assurez-vous que l’option « Nouvelle feuille de calcul» est sélectionnée.
  2. Cliquez sur « OK» pour terminer.
  3. Vous devriez maintenant avoir une nouvelle feuille contenant le tableau croisé dynamique vide :
  1. Commencez maintenant à créer votre rapport de synthèse à l'aide du nouveau tableau croisé dynamique en faisant glisser les champs dans leurs zone appropriée. Dans notre exemple, nous glissons le champ «Ville» dans la zone « Lignes » et le champ « Distinctes » dans la zone « Valeurs » comme illustré dans la capture ci-dessous :
  1. Au fur et à mesure que vous glissez les champs dans les zones appropriées, Excel rempli le tableau crois dynamique pour vous. Pour l’exemple de ce tutoriel, nous obtenons en résultat un tableau croisé dynamique comme indiqué ci-dessous :

Et voilà ! vous avez bien obtenu le nombre de fournisseurs distincts pour chaque ville. Si vous le souhaitez, vous pouvez modifier l’en-tête de colonne de « Somme du Distinctes » en « Nombre de fournisseurs ».

Comme vous pouvez le remarquer, cette méthode est assez simple et facile à implémenter. Cependant, il existe quelques inconvénients que nous devions souligner concernant la modification des données source dans un tableau croisé dynamique. En fait, la source de données avec la colonne d'assistance n'est pas aussi dynamique qu'un tableau croisé dynamique. Vous venez d’utiliser cette méthode pour obtenir le nombre de fournisseurs distincts dans chaque ville. Maintenant, que se passe-t-il si vous voulez également obtenir le nombre distinct de fournisseurs vendant des bananes. Vous devrez revenir aux données source et modifier la formule que vous avez créé dans la colonne d'assistance, ou, vous pouvez également ajouter une nouvelle colonne d'assistance et y insérer la nouvelle formule. En faisant ainsi :

  • Vous risquez d’augmenter la taille de votre fichier Excel en ajoutant à chaque fois plus de données à la source du tableau croisé dynamique.
  • Vous ralentissez votre classeur Excel puisque vous utilisez des formules Excel pour la colonne d’assistance. Cela peut être le cas surtout lorsque vous auriez des milliers de lignes de données.

Bien que ces inconvénients puissent être négligeables, vous pouvez les dépasser en utilisant une deuxième méthode de contournement que nous expliquons ci-dessous.

Méthode 2 : ajouter les données au modèle données et calculer le total distinct

La méthode que nous venons de voir fonctionne pour toutes les versions d’Excel, mais, à partir d’Excel 2013 Microsoft a ajouté une nouvelle fonctionnalité qui vous permet d'obtenir le nombre distinct tout en résumant l'ensemble de vos données. Alors, si vous utilisez une version précédente ne perdez pas votre temps à lire cette partie et utilisez plutôt la première méthode en ajoutant une colonne d'assistance.

Prenons un autre exemple de données. Comme illustré dans la capture ci-dessous, nous avons un jeu de données comportant des dates dans la colonne « A » et les noms de fruit vendu pour chaque date dans la colonne « B ». Comme vous pouvez le remarquer, dans une date, il peut y avoir des produits répétés pour plusieurs fois.

Nous utilisons la méthode habituelle pour créer un tableau croisé dynamique à partir de ces données. Lorsque nous ajoutions le nombre de produits, Excel compte tous les produits pour chaque date comme indiqué dans la capture ci-dessous :

Comme vous pouvez le voir, le tableau croisé dynamique donne le nombre total des produits vendus pour chaque date sans exclure les doublons. Mais pour nous, nous voulons voir uniquement le nombre de produits distinct, ainsi, s'il y a un produit en double dans une date, nous voulons le compter une seule fois.

Pour obtenir un décompte distinct dans le tableau croisé dynamique, suivez les étapes ci-dessous :

  1. Cliquez sur n'importe quelle cellule dans votre jeu de données.
  2. Accédez à l'onglet « Insertion».
  3. Dans le groupe « Tableaux» cliquez sur la commande « Tableau croisé dynamique » :
  1. La boîte de dialogue « Créerun tableau croisé dynamique » s’ouvre, vérifiez la plage affichée dans la zone « Tableau/plage » et assurez-vous que l’option « Nouvelle feuille de calcul » est sélectionnée :
  1. Cochez la case en regard de l’option « Ajouter ces données au modèle de données» :
  1. Cliquez sur « OK» et Excel insèrera une nouvelle feuille de calcul qui a le nouveau tableau croisé dynamique vide :
  1. Dans le volet « Champs de tableau croisé dynamique», faites glisser le champ pour lequel vous souhaitez calculer le nombre distinct à la zone « Valeurs ». Dans notre exemple, nous glissons le champ « Produit ».
  2. Faites glisser le champ « Date» dans la zone « Lignes » :
  1. A ce stade, nous ferons une modification qui résoudra tout le problème. Cliquez sur la flèche en regard de votre champ de valeur et sélectionnez « Paramètres des champs de valeur» comme illustré dans la capture ci-dessous :
  1. La boîte de dialogue « Paramètres des champs de valeur» s’ouvre. Sous « Résumer le champ de valeur par », faites défiler la liste et sélectionnez « Total distinct » comme type de calcul :
  1. Cliquez sur « OK» pour terminer. Comme vous pouvez le voir, Excel modifie le tableau croisé dynamique pour calculer le total distinct de produits vendus pour chaque date :

Vous remarquerez que le nom de la colonne passe de « Nombre de produit » à « Nombre distinct de Produit ». Vous pouvez le changer en ce que vous voulez.

Bien que cette méthode soit plus simple et ne demande pas de colonne d’assistance, il existe des notes que nous devons souligner lorsque vous ajoutez vos données au modèle de données :

  • En enregistrant vos données dans le modèle de données, vous obtiendrez un avertissement si vous essayez de l’ouvrir dans une ancienne version d'Excel qui ne le prend pas en charge. Dans ce cas, Il se peut que vous ne voyiez pas le nombre distinct et le modèle de données.
  • Lorsque vous créez un tableau croisé dynamique à partir de données ajoutées au modèle de données, Il ne sera pas possible d’ajouter des champs calculés et des colonnes calculées.

Comment calculer le nombre des valeurs uniques et non des valeurs distinctes

Maintenant, que faire si vous voulez compter les valeurs uniques et non pas les valeurs distinctes. Pour ce cas, Excel n’a pas de fonction intégrée dans le tableau croisé dynamique, vous devrez donc utiliser une colonne d'assistance. Supposons que vous avez un ensemble de données comme illustré dans la capture ci-dessous et que vous souhaitez savoir combien de fournisseurs uniques sont dans chaque ville. Nous obtenons alors le nombre de fournisseurs qui opèrent dans une ville spécifique uniquement et pas dans les autres.

Ajoutez une colonne d'assistance et appliquez la formule ci-dessous :

=SI(SI(NB.SI.ENS($B$2:$B$12;B2; $C$2:$C$12;C2)/NB.SI($B$2:$B$12;B2)<1;0;1); SI(NB.SI(B$2:B$12; B2)> 1;0;1); 0)

Expliquons comment fonctionne cette formule :

  • NB.SI.ENS($B$2:$B$12;B2; $C$2:$C$12;C2)/NB.SI($B$2:$B$12;B2) : premièrement, la formule vérifie si un nom de fournisseur apparaît dans une seule ville ou dans plusieurs villes. Elle compte donc le nombre d'occurrences d'un fournisseur dans une ville et le divise par le nombre total d'occurrences de ce fournisseur dans l’ensemble de données.
  • SI(SI(NB.SI.ENS($B$2:$B$12;B2; $C$2:$C$12;C2)/NB.SI($B$2:$B$12;B2)<1;0;1) : ensuite,la formule teste si la valeur retournée est inférieure à « 1 », si oui, cela indique donc que le fournisseur apparaît dans deux ou plusieurs villes. Ainsi, cette formule renvoie «  » lorsqu’un fournisseur apparaît dans plus d'une ville, et renvoie « 1 » dans le cas contraire.
  • SI(NB.SI(B$2:B$12; B2)> 1;0;1) : dans le dernier teste, la formule vérifie si le fournisseur est répété dans la même ville ou non. Si le nom du fournisseur est trouvé plus d’une seule fois, seule la première instance du fournisseur renvoie la valeur « 1 » et toutes les autres instances renvoient « 0 ».

Après avoir créé la colonne d’assistance et bien ajouté la formule, vous pouvez créer le tableau croisé dynamique comme nous l’avons fait dans la première méthode. Pour la plupart de vous, cette technique peut sembler un peu complexe, mais cela dépend de votre jeu de données et de ce que vous essayez de réaliser.

Conclusion

Pour conclure, si votre objectif est de compter les valeurs uniques dans un tableau croisé dynamique, vous devez demander l’aide des colonnes d'assistance et si votre objectif est de compter les valeurs distinctes, vous pouvez donc utiliser les colonnes d’assistance pour toutes les versions d’Office, ou bien la fonctionnalité intégrée dans la version Excel 2013 et ultérieures.