Comment compter le nombre de valeurs uniques dans un tableau Excel

(Last Updated On: 15 avril 2020)

Comment compter le nombre de valeurs uniques dans un tableau ?

Dans cet article, nous allons voir à travers un cas pratique comment compter des valeurs uniques dans Excel avec des formules, et comment faire la même chose simplement avec un tableau croisé dynamique.

Compter des valeurs uniques dans Excel avec une formule

Par exemple, nous avons les données de ventes suivantes, et nous souhaitons tout d’abord extraire le nombre de catégories d’item.

Pour compter le nombre d’item distincts, utilisez la formule suivante :

{=SOMME(1/NB.SI($E$4:$E$46;$E$4:$E$46))}

Le résultat de la formule nous indique qu’il y a 5 catégories d’item (crayon, classeur, stylo, pack stylos, bureau).

Note : Il s’agit d’une formule matricielle, par conséquent, vous devez appuyer sur Ctrl + Shift + Entrée au lieu de la touche Entrée pour saisir la formule.

Extraire une liste de valeurs distinctes dans une colonne

Maintenant on souhaite extraire la liste des responsables des ventes comme dans la capture ci-dessous :

  1. Entrez la formule suivante dans la cellule L8 :
    =RECHERCHE(2;1/(NB.SI($L$7:L7;$D$4:$D$46)=0);$D$4:$D$46)
  2. Recopiez la formule dans les lignes du dessous pour obtenir la liste des valeurs distinctes

Compter les valeurs uniques basées sur un critère

Maintenant que nous avons la liste des responsables des ventes, nous souhaitons savoir combien chacun a vendu de produits uniques, comme dans la capture ci-dessous.

Pour  cela, entrez la formule matricielle suivante dans la cellule M8 :

{=SOMME(SI(L8=$D$4:$D$46; 1/(NB.SI.ENS($D$4:$D$46; L8; $E$4:$E$46;$E$4:$E$46)); 0))}

Dans la formule ci-dessus, la cellule L8 contient le nom du premier responsable dont vous souhaitez compter les valeurs distinctes de produits vendus, D4:D46 est la liste des responsables, E4:E46 est la liste des produits vendus dans le tableau de données.

Rappel : Vous devez appuyer sur Ctrl + Shift + Entrée au lieu de la touche Entrée pour valider la saisie de la  formule.

Compter des valeurs uniques dans Excel avec un tableau croisé dynamique

Les dernières versions Excel 2013 et Excel 2016 ont une fonctionnalité spéciale qui permet de comptabiliser automatiquement des valeurs distinctes dans un tableau croisé dynamique. La capture d’écran suivante montre cela :

Pour créer un tableau croisé dynamique avec le comptage de valeurs distinctes, procédez comme suivant :

  1. Sélectionnez les données à inclure dans le TCD, allez dans l’onglet Insertion -> Tableaux, puis cliquez sur le “Tableau croisé dynamique”
  2. Dans la boîte de dialogue Créer un tableau croisé dynamique, sélectionnez vos options, puis assurez-vous bien de cocher la case “Ajouter ces données au modèle de données”
  3. Lorsque le TCD s’ouvre, organisez les zones Lignes, Colonnes et Valeurs comme vous le souhaitez. Si vous n’avez pas beaucoup d’expérience avec les tableaux croisés dynamiques Excel, vous pouvez lire cet article Créer un tableau croisé dynamique
  4. Déplacez le champ dont vous souhaitez calculer le nombre distinct (champ Item dans cet exemple) dans la zone Valeurs, cliquez dessus et sélectionnez “Paramètres des champs de valeurs…” dans le menu déroulant :
  5. La boîte de dialogue Paramètres des champs de valeurs s’ouvre, Allez tout en bas de la liste jusqu’à Total distinct, qui est la dernière option de la liste, sélectionnez-la et cliquez sur OK

C’est terminé ! Le tableau croisé nouvellement créé affichera le compte distinct des produits vendus pour chaque responsable.


📥TELECHARGER LE FICHIER D’EXEMPLE

Téléchargez le classeur Excel pour ce tutoriel. Seuls votre adresse e-mail et nom vous seront demandés dans la prochaine étape. Vous recevrez le lien de téléchargement par e-mail.

Compter des valeurs uniques dans Excel

Ce fichier Excel montre comment compter les valeurs uniques d'un tableau de données avec Excel.
Télécharger

Envoyer le lien de téléchargement à :

Oui, je consens à recevoir la Newsletter. Je confirme avoir lu et accepté la Politique de confidentialité.

 

Souscrivez à ma newsletter :

9 commentaires

  1. 40eme dit

    Bonjour,

    Pourquoi parfois la case à cocher “Ajouter ces données au modèle de données” de la fenêtre “Créer un Tableau Croisé Dynamique” apparaît en grisé et est donc non cliquable ?

    Merci par avance pour la solution.

  2. Romain coudes dit

    Bonjour, malgré des heures de recherche, je ne trouve pas…
    j’ai une plage à 2 colonnes (date et nom)
    je voudrais compter combien il y a de noms DIFFERENTS en fonction d’une date

    à l’aide svp

    • Lionel dit

      Bonjour Romain,

      Essayer de concaténer la date et le nom dans une troisième colonne avec une formule de type C2 = A2 & B2.
      Puis appliquez les étapes décrites dans l’article sur cette troisième colonne.

      Ca devrait fonctionner.

      Lionel

  3. murielle dit

    Bonjour,
    Je souhaiterai faire un tcd ; j’ai en données des clients, des numéros de commandes, des références
    ex :
    client cde produit
    x 123 156312
    y 658 698542
    z 999 698751
    z 888 325687
    x 456 654321
    x 789 941276
    z 555 123456
    z 555 654321
    x 456 123456
    je souhaiterai faire un tcd qui me dira par client, combien de commande j’ai eu.
    Mon résultat me rapatrie toujours mon nombre de ligne et non pas mon nombre de commande
    Comment faire ?
    Merci de votre aide

    • Lionel dit

      Bonjour Murielle

      Vous souhaitez compter le nombre distinct de commandes par client, est-ce bien ça ?

      En effet, si vous faites un TCD classique, il va compter le nombre de lignes par client si vous agrégez le champs Commande en Nombre.
      Or pour le client x, pour reprendre votre exemple, il y a 4 lignes, mais la commande 456 est présente 2 fois, donc il y a 3 commandes distinctes pour x.

      Pour arriver au résultat souhaité, le plus simple à mon avis est de créer un TCD à partir du modèle de données Power Pivot, et vous aurez l’option supplémentaire d’agrégation “Total distinct” qui comptera uniquement les commandes uniques.

      J’explique tout cela en détail dans mon programme si cela vous intéresse : https://xlbusinesstools.com/elearning1

      Lionel

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *