Les tableaux croisés dynamiques Excel sont incroyables (je sais que je le mentionne à chaque fois que j'écris sur les tableaux croisés dynamiques, mais c'est vrai).
Avec une compréhension de base et un peu de glisser-déposer, vous pouvez faire un tas de travail en quelques secondes.
Bien que beaucoup de choses puissent être faites en quelques clics dans les tableaux croisés dynamiques, certaines choses nécessiteraient quelques étapes supplémentaires ou un peu de travail.
Et une telle chose est de compter des valeurs distinctes dans un tableau croisé dynamique.
Dans ce didacticiel, je vais vous montrer comment compter des valeurs distinctes ainsi que des valeurs uniques dans un tableau croisé dynamique Excel.
Mais avant de me lancer dans la façon de compter des valeurs distinctes, il est important de comprendre la différence entre « nombre distinct » et « nombre unique »
Nombre distinct contre nombre unique
Bien que cela puisse sembler être la même chose, ce n'est pas.
Vous trouverez ci-dessous un exemple où il existe un ensemble de données de noms et j'ai répertorié séparément les noms uniques et distincts.
Valeurs/noms uniques sont ceux qui ne se produisent qu'une seule fois. Cela signifie que tous les noms qui se répètent et ont des doublons ne sont pas uniques. Les noms uniques sont répertoriés dans la colonne C de l'ensemble de données ci-dessus
Valeurs/noms distincts sont ceux qui se produisent au moins une fois dans l'ensemble de données. Ainsi, si un nom apparaît trois fois, il compte toujours comme un nom distinct. Ceci peut être réalisé en supprimant les valeurs/noms en double et en conservant tous ceux qui sont distincts. Les noms distincts sont répertoriés dans la colonne B de l'ensemble de données ci-dessus.
D'après ce que j'ai vu, la plupart du temps, lorsque les gens disent qu'ils veulent obtenir le nombre unique dans un tableau croisé dynamique, ils signifient en fait un nombre distinct, ce que je couvre dans ce didacticiel.Compter des valeurs distinctes dans le tableau croisé dynamique Excel
Supposons que vous ayez les données de vente comme indiqué ci-dessous :
Cliquez ici pour télécharger le fichier d'exemple et suivez
Avec l'ensemble de données ci-dessus, disons que vous voulez trouver la réponse aux questions suivantes :
- Combien y a-t-il de représentants commerciaux dans chaque région (ce qui n'est rien d'autre que le nombre distinct de représentants commerciaux dans chaque région) ?
- Combien de commerciaux ont vendu l'imprimante en 2021-2022 ?
Bien que les tableaux croisés dynamiques puissent résumer instantanément les données en quelques clics, pour obtenir le nombre de valeurs distinctes, vous devrez effectuer quelques étapes supplémentaires.
Si vous utilisez Excel 2013 ou versions ultérieures, il existe une fonctionnalité intégrée dans le tableau croisé dynamique qui vous donne rapidement le nombre distinct. Et si vous utilisez Excel 2010 ou versions antérieures, vous devrez modifier les données sources en ajoutant une colonne d'aide.Les deux méthodes suivantes sont abordées dans ce didacticiel :
- Ajout d'une colonne d'assistance dans l'ensemble de données d'origine pour compter les valeurs uniques (fonctionne dans toutes les versions).
- Ajout des données à un modèle de données et utilisation de l'option Distinct Count (disponible dans Excel 2013 et les versions ultérieures).
Il existe une troisième méthode que Roger montre dans cet article (qu'il appelle la méthode Pivot the Pivot Table).
Commençons!
Ajout d'une colonne d'assistance dans l'ensemble de données
Remarque : si vous utilisez Excel 2013 et des versions supérieures, ignorez cette méthode et passez à la suivante (car elle utilise une fonctionnalité de tableau croisé dynamique intégrée - Nombre distinct).Il s'agit d'un moyen simple de compter des valeurs distinctes dans le tableau croisé dynamique, car il vous suffit d'ajouter une colonne d'assistance aux données source. Une fois que vous avez ajouté une colonne d'assistance, vous pouvez ensuite utiliser ce nouvel ensemble de données pour calculer le nombre distinct.
Bien qu'il s'agisse d'une solution de contournement facile, cette méthode présente certains inconvénients (couverts plus loin dans ce didacticiel).
Permettez-moi d'abord de vous montrer comment ajouter une colonne d'aide et obtenir un nombre distinct.
Supposons que j'ai l'ensemble de données comme indiqué ci-dessous :
Ajoutez la formule suivante dans la colonne F et appliquez-la à toutes les cellules contenant des données dans les colonnes adjacentes.
=SI(COUNTIFS($C$2:C2,C2,$B$2:B2,B2)>1,0,1)
La formule ci-dessus utilise la fonction COUNTIFS pour compter le nombre de fois qu'un nom apparaît dans la région donnée. Notez également que la plage de critères est $C$2:C2 et $B$2:B2. Cela signifie qu'il continue de s'étendre au fur et à mesure que vous descendez dans la colonne.
Par exemple, dans la cellule E2, les plages de critères sont $C$2:C2 et $B$2:B2 et dans la cellule E3, ces plages s'étendent à $C$2:C3 et $B$2:B3.
Cela garantit que la fonction COUNTIFS compte la première instance d'un nom comme 1, la deuxième instance du nom comme 2, et ainsi de suite.
Puisque nous voulons uniquement obtenir les noms distincts, la fonction SI est utilisée qui renvoie 1 lorsqu'un nom apparaît pour une région pour la première fois et renvoie 0 lorsqu'il apparaît à nouveau. Cela garantit que seuls les noms distincts sont comptés et non les répétitions.
Voici à quoi ressemblerait votre ensemble de données lorsque vous avez ajouté la colonne d'aide.
Maintenant que nous avons modifié les données source, nous pouvons l'utiliser pour créer un tableau croisé dynamique et utiliser la colonne d'aide pour obtenir le nombre distinct du représentant des ventes dans chaque région.
Vous trouverez ci-dessous les étapes à suivre :
- Sélectionnez n'importe quelle cellule de l'ensemble de données.
- Cliquez sur l'onglet Insérer.
- Cliquez sur Tableau croisé dynamique (ou utilisez le raccourci clavier - ALT + N + V)
- Dans la boîte de dialogue Créer un tableau croisé dynamique, assurez-vous que le tableau/la plage est correct (et inclut la colonne d'aide) et que « Nouvelle feuille de calcul » est sélectionné.
- Cliquez sur OK.
Les étapes ci-dessus inséreraient une nouvelle feuille contenant le tableau croisé dynamique.
Faites glisser le champ « Région » dans la zone Lignes et le champ « D Count » dans la zone Valeurs.
Vous obtiendrez un tableau croisé dynamique comme indiqué ci-dessous :
Vous pouvez maintenant modifier l'en-tête de la colonne de « Sum of D count » en « Représentant commercial ».
Inconvénients de l'utilisation d'une colonne d'assistance :
Bien que cette méthode soit assez simple, je dois souligner quelques inconvénients liés à la modification des données source dans un tableau croisé dynamique :
- La source de données avec la colonne d'aide n'est pas aussi dynamique qu'un tableau croisé dynamique. Bien que vous puissiez découper et découper les données comme vous le souhaitez avec un tableau croisé dynamique, lorsque vous utilisez une colonne d'assistance, vous perdez une partie de cette capacité. Supposons que vous ajoutiez une colonne d'aide pour obtenir le nombre d'un représentant commercial distinct dans chaque région. Maintenant, que se passe-t-il si vous souhaitez également obtenir le nombre distinct de représentants commerciaux qui vendent des imprimantes. Vous devrez revenir aux données sources et modifier la formule de la colonne d'aide (ou ajouter une nouvelle colonne d'aide).
- Étant donné que vous ajoutez plus de données à la source du tableau croisé dynamique (qui est également ajoutée au cache croisé dynamique), cela peut entraîner une taille de fichier Excel plus élevée.
- Étant donné que nous utilisons une formule Excel, cela peut ralentir votre classeur Excel au cas où vous auriez des milliers de lignes de données.
Ajouter des données au modèle de données et résumer à l'aide d'un nombre distinct
Le tableau croisé dynamique a ajouté une nouvelle fonctionnalité dans Excel 2013 qui vous permet d'obtenir le nombre distinct tout en résumant l'ensemble de données.
Si vous utilisez une version précédente, vous ne pourrez pas utiliser cette méthode (comme vous devriez essayer d'ajouter la colonne d'aide comme indiqué dans la méthode au-dessus de celle-ci).
Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez obtenir le nombre de commerciaux uniques dans chaque région.
Voici les étapes pour obtenir une valeur de comptage distincte dans le tableau croisé dynamique :
- Sélectionnez n'importe quelle cellule de l'ensemble de données.
- Cliquez sur l'onglet Insérer.
- Cliquez sur Tableau croisé dynamique (ou utilisez le raccourci clavier - ALT + N + V)
- Dans la boîte de dialogue Créer un tableau croisé dynamique, assurez-vous que la table/plage est correcte et que la nouvelle feuille de calcul est sélectionnée.
- Cochez la case qui dit - "Ajouter ces données au modèle de données"
- Cliquez sur OK.
Les étapes ci-dessus inséreraient une nouvelle feuille contenant le nouveau tableau croisé dynamique.
Faites glisser la région dans la zone Lignes et le représentant commercial dans la zone Valeurs. Vous obtiendrez un tableau croisé dynamique comme indiqué ci-dessous :
Le tableau croisé dynamique ci-dessus donne le nombre total de commerciaux dans chaque région (et non le nombre distinct).
Pour obtenir le nombre distinct dans le tableau croisé dynamique, suivez les étapes ci-dessous :
- Cliquez avec le bouton droit sur n'importe quelle cellule de la colonne « Nombre de représentants commerciaux ».
- Cliquez sur Paramètres du champ de valeur
- Dans la boîte de dialogue Paramètres du champ de valeur, sélectionnez « Nombre distinct » comme type de calcul (vous devrez peut-être faire défiler la liste pour le trouver).
- Cliquez sur OK.
Vous remarquerez que le nom de la colonne passe de « Nombre de représentants commerciaux » à « Nombre distinct de représentants commerciaux ». Vous pouvez le changer en ce que vous voulez.
Certaines choses que vous savez lorsque vous ajoutez vos données au modèle de données :
- Si vous enregistrez vos données dans le modèle de données, puis ouvrez dans une ancienne version d'Excel, un avertissement s'affichera : « Certaines fonctions de tableau croisé dynamique ne seront pas enregistrées ». Vous ne verrez peut-être pas le nombre distinct (et le modèle de données) lorsqu'il est ouvert dans une ancienne version qui ne le prend pas en charge.
- Lorsque vous ajoutez vos données à un modèle de données et créez un tableau croisé dynamique, il n'affichera pas les options permettant d'ajouter des champs calculés et des colonnes calculées.
Cliquez ici pour télécharger le fichier exemple
Que faire si vous voulez compter des valeurs uniques (et non des valeurs distinctes) ?
Si vous souhaitez compter des valeurs uniques, vous n'avez aucune fonctionnalité intégrée dans le tableau croisé dynamique et vous devrez uniquement vous fier aux colonnes d'aide.
N'oubliez pas que les valeurs uniques et les valeurs distinctes ne sont pas les mêmes. Cliquez ici pour connaître la différence.Un exemple pourrait être lorsque vous disposez de l'ensemble de données ci-dessous et que vous souhaitez savoir combien de représentants commerciaux sont uniques à chaque région. Cela signifie qu'ils opèrent dans une seule région spécifique et pas dans les autres.
Dans de tels cas, vous devez créer une ou plusieurs colonnes d'assistance.
Pour ce cas, la formule ci-dessous fait l'affaire :
=SI(SI(COUNTIFS($C$2:$C$1001,C2,$B$2:$B$1001,B2)/COUNTIF($C$2:$C$1001,C2)1,0,1),0)
La formule ci-dessus vérifie si un nom de représentant commercial apparaît dans une seule région ou dans plusieurs régions. Il le fait en comptant le nombre d'occurrences d'un nom dans une région et en le divisant par le nombre total d'occurrences du nom. Si la valeur est inférieure à 1, cela indique que le nom apparaît dans deux ou plus de deux régions.
Si le nom apparaît dans plus d'une région, il renvoie un 0 sinon il renvoie un un.
La formule vérifie également si le nom est répété dans la même région ou non. Si le nom est répété, seule la première instance du nom renvoie la valeur 1, et toutes les autres instances renvoient 0.
Cela peut sembler un peu complexe, mais cela dépend encore une fois de ce que vous essayez d'atteindre.
Ainsi, si vous souhaitez compter des valeurs uniques dans un tableau croisé dynamique, utilisez des colonnes d'assistance et si vous souhaitez compter des valeurs distinctes, vous pouvez utiliser la fonctionnalité intégrée (dans Excel 2013 et versions ultérieures) ou utiliser une colonne d'assistance.
Cliquez ici pour télécharger le fichier exemple
Vous aimerez peut-être aussi les didacticiels suivants sur les tableaux croisés dynamiques :
- Comment filtrer les données dans un tableau croisé dynamique dans Excel
- Comment regrouper des dates dans des tableaux croisés dynamiques dans Excel
- Comment regrouper des nombres dans un tableau croisé dynamique dans Excel
- Comment appliquer une mise en forme conditionnelle dans un tableau croisé dynamique dans Excel
- Trancheurs dans le tableau croisé dynamique Excel
- Comment actualiser le tableau croisé dynamique dans Excel
- Supprimer un tableau croisé dynamique dans Excel
