Comment compter les éléments uniques (count distinct) dans un tableau croisé dynamique Excel. Trois méthodes, pour différentes versions d’Excel. Obtenez le classeur gratuit pour suivre
Merci à Roger Govier, qui a créé ce tutoriel et ce classeur.
Compte unique
Si vous utilisez un tableau croisé dynamique pour résumer vos données, vous pouvez vous poser des questions telles que :
- Combien de clients uniques (distincts) ont effectué des achats dans chaque région ?
- Combien de produits uniques ont été vendus dans chaque magasin ?
- Combien de vendeurs uniques ont vendu chaque produit par région ?
Il n’y a pas de fonction intégrée de » compte unique » dans un tableau croisé dynamique normal, mais dans ce tutoriel, vous verrez comment obtenir un compte unique d’éléments dans un tableau croisé dynamique.
- Dans Excel 2013 et les versions ultérieures, créez un modèle de données
- Dans Excel 2010, et les versions ultérieures, utilisez une technique qui « Pivote le tableau croisé dynamique ».
- Ou, dans les versions plus anciennes, ajoutez une nouvelle colonne aux données sources, et utilisez CountIf.
- Si le complément Power Pivot est installé, utilisez-le pour montrer un compte distinct
Cette vidéo montre les étapes pour montrer un compte distinct avec le modèle de données.
Introduction
Souvent, nous avons de grands ensembles de données que nous voulons analyser à l’aide d’un tableau croisé dynamique, mais nous voulons seulement compter les occurrences uniques de certains éléments de données. Malheureusement, les tableaux croisés dynamiques n’ont jamais eu la possibilité intégrée de compter les valeurs uniques.
Dans cet exemple, le fichier échantillon comporte 4999 enregistrements qui montrent les ventes de produits, avec la région et le nom du vendeur. Les quelques premiers enregistrements sont présentés dans la capture d’écran ci-dessous. Vous pouvez télécharger le fichier d’exemple au lien ci-dessous.
Créer un tableau croisé dynamique à partir des données nous donnerait quelque chose comme ce qui suit, si nous ajoutons Personne à la zone Valeurs du tableau croisé dynamique et que nous le résumons avec Compte.
Cependant, cela nous donne un compte de toutes les transactions, et non le compte du nombre unique de personnes qui ont effectué ces transactions dans chaque Région.
Obtenir un comptage unique
Il existe des solutions de contournement que vous pouvez utiliser, pour obtenir un comptage unique :
- Méthode 1 : Dans Excel 2013 et les versions ultérieures, ajoutez les données sources du tableau croisé dynamique au modèle de données, et un comptage unique peut être effectué facilement.
- Méthode 2 : Dans Excel 2010 et ultérieur, utilisez la technique du » pivot a pivot « .
- Méthode 3 : Dans les anciennes versions d’Excel, ajoutez une colonne aux données sources pour placer un 1 dans les cellules d’une ligne où la valeur est unique, et un 0 dans toutes les autres cellules. Ensuite, l’addition de cette colonne supplémentaire fournit la valeur unique.
- Méthode 4 : utilisez PowerPivot pour créer le tableau croisé dynamique, et utilisez ses fonctions pour créer un compte unique. Voir les détails ci-dessous.
Les quatre méthodes sont décrites ci-dessous, ainsi qu’une comparaison des performances entre les méthodes 2 et 3.
1. Ajouter au modèle de données – Excel 2013 et versions ultérieures
Dans Excel 2013, si vous ajoutez les données sources d’un tableau croisé dynamique au modèle de données du classeur, il est facile de créer un compte unique.
NOTE : Cette technique crée un tableau croisé dynamique basé sur OLAP, qui présente certaines limitations, comme l’absence de regroupement et de champs calculés ou d’éléments calculés. Si vous avez besoin de ces fonctionnalités restreintes, essayez plutôt la méthode » pivot a pivot « .
Créer un compte unique (Distinct Count)
Pour créer le tableau croisé dynamique, suivez ces étapes :
- Sélectionnez une cellule dans le tableau de données source.
- Au bas de la boîte de dialogue Créer un tableau croisé dynamique, ajoutez une coche à « Ajouter ces données au modèle de données »
- Cliquez sur OK
Pour configurer la disposition du tableau croisé dynamique, suivez ces étapes :
- Dans le tableau croisé dynamique, ajoutez Région à la zone Row.
- Ajoutez ces 3 champs à la zone Valeurs — Personne, Unités, Valeur
- Le champ Personne contient du texte, il s’agit donc par défaut de Compte de personne. Le compte indique le nombre total de transactions dans chaque région, et non un compte unique de personnes
Pour obtenir un compte unique de personnes dans chaque région, suivez ces étapes :
- Cliquez avec le bouton droit sur l’une des valeurs du champ Personne
- Cliquez sur Paramètres du champ de valeur
- Dans la liste Résumer le champ de valeur par, faites défiler jusqu’en bas, et cliquez sur Distinct Count, puis cliquez sur OK
Le champ Personne change, et au lieu d’afficher le compte total des transactions, il affiche un compte distinct des noms des vendeurs.
2. Pivot du Pivot – Excel 2010
Pour obtenir un compte unique de vendeurs par région dans Excel 2010, nous pouvons produire un rapport Pivot, puis « Pivot du Pivot » pour obtenir notre résultat final.
Créer le premier tableau Pivot
- Créer un tableau Pivot à partir de ces données, avec Région et Personne dans la zone Rows
- Ajouter des unités et une valeur dans la zone Values. Parce que Personne est un champ de texte, le tableau croisé dynamique l’affichera automatiquement comme « Compte de ».
- Formater le tableau croisé dynamique avec la mise en page de rapport tabulaire
- Définir toutes les étiquettes d’élément pour qu’elles se répètent dans chaque ligne.
- Changez les légendes, pour supprimer la « Somme de »
TIP : tapez le nom du champ original, avec un caractère d’espacement à la fin, par exemple « unités »
Ce tableau ressemble maintenant à un tableau de données normal, mais c’est un résumé du tableau de données complet original avec 4 999 lignes.
Créer une plage nommée pour la cellule de départ
Puis, vous allez nommer la première cellule du tableau croisé dynamique.
- Sélectionnez la cellule B2, qui est l’en-tête de la colonne Région du tableau croisé dynamique
- Cliquez dans la barre de formule, et tapez un nom pour la cellule — pvtStart
- Appuyez sur Entrée, pour compléter le nom
Créer une plage nommée pour la ligne de fin
La prochaine étape consiste à calculer l’emplacement de la dernière ligne du tableau croisé dynamique. Nous allons utiliser la fonction MATCH, avec un très grand nombre, pour trouver le dernier numéro de la colonne D, et obtenir son numéro de ligne. Cette formule ne fonctionnera que dans une colonne comportant des chiffres.
- Sur la feuille de données, dans la cellule M2, entrez cette formule, pour calculer la dernière ligne de la colonne Unités du tableau croisé dynamique :
- =MATCH(9.9E+307,Méthode_1!D:D,1)
Pour nommer cette cellule, sélectionnez la cellule M2, et dans la zone Nom, tapez PvtEnd, et appuyez sur Entrée.
Créer une plage nommée dynamique
Puis, nous allons créer une plage nommée dynamique, pour faire référence à ce nouveau tableau. Cette plage commencera dans la cellule nommée pvtstart (la cellule d’en-tête de la région), et se terminera dans la dernière ligne de données de la colonne E (la plage nommée, PvtEnd).
- Sur l’onglet Formules du ruban, cliquez sur Définir le nom
- Dans la boîte de dialogue Nouveau nom, entrez le nom, maDonnée
- Dans la liste déroulante Étendue, sélectionnez Classeur
- Dans la zone Se réfère à, tapez cette formule:
=pvtStart:INDEX(Méthode_1 !$E:$E, PvtEnd) - Cliquez sur OK, pour créer la plage nommée
Créer le deuxième tableau croisé dynamique
Enfin, créez un deuxième tableau croisé dynamique, basé sur la plage dynamique, mesDonnées.
Mettez Région dans la zone Row, et Personne, Unités et Valeur dans la zone Valeurs
Ce tableau indique le nombre de vendeurs uniques par régions, comme indiqué ci-dessous. Par exemple, dans le premier tableau croisé dynamique, la région Est a montré 8 noms uniques dans la colonne personne, et c’est le compte dans ce tableau croisé dynamique.
Rafraîchir les tableaux croisés dynamiques
Naturellement, comme il y a 2 tableaux croisés dynamiques impliqués dans cette solution, les deux doivent être rafraîchis après que toute donnée a été ajoutée ou modifiée dans la table source. Il est essentiel que le premier PT soit rafraîchi en premier, suivi d’un rafraîchissement du tableau croisé dynamique final.
Vous pouvez rafraîchir les tableaux croisés dynamiques manuellement, ou utiliser une amacro. Pour utiliser une macro, ajoutez l’une des procédures suivantes à la feuille contenant votre tableau croisé dynamique final.
A) Si les deux tableaux croisés dynamiques sont sur la même feuille, utilisez ce code :
Private Sub Worksheet_Activate() Me.PivotTables(1).PivotCache.Refresh Me.PivotTables(2).PivotCache.Refresh End Sub
B) Si les tableaux croisés dynamiques sont sur des feuilles différentes, alors utilisez ce code (remplacez le nom de votre feuille réelle par « yoursheetname ») :
Private Sub Worksheet_Activate() Sheets("yoursheetname").PivotTables(1).PivotCache.Refresh Me.PivotTables(1).PivotCache.Refresh End Sub
(Facultatif) Nommez la plage de données du tableau croisé dynamique
Si vous utilisez des macros pour rafraîchir les tableaux croisés dynamiques, vous pourriez également utiliser une macro pour réinitialiser la plage nommée myData après chaque rafraîchissement. Ajoutez la procédure suivante à votre classeur, puis appelez cette procédure SetmyData dans les procédures Worksheet_Activate.
Sub SetmyData()'Change "PT_A" to whatever name'you have given to your first Pivot Table'that is to be used as the source'for for the final Pivot Table'Also change the sheet name'from "Method_1" to'your sheet name if it is differentDim PTName As StringDim ShName As StringPTName = "PT_A"ShName = "Method_1"ThisWorkbook.Names.Add _ Name:="myData", _ RefersTo:=Sheets(ShName) _ .PivotTables(PTName) _ .TableRange1End Sub
3. Utiliser COUNTIF — Excel 2007 et antérieur
Dans les versions antérieures d’Excel, les tableaux croisés dynamiques n’ont pas la possibilité de répéter les étiquettes de ligne. Pour obtenir un compte unique de vendeurs par région, vous pouvez ajouter une nouvelle colonne dans les données source, et entrer une formule COUNTIF
Vous pouvez télécharger le fichier d’exemple au lien ci-dessous.
Ajouter la formule COUNTIF
Suivez ces étapes, pour ajouter la formule :
Dans les données source, ajoutez un nouvel intitulé de colonne — » Unique » — dans la cellule J1
Dans la cellule J2, saisissez cette formule, et copiez-la vers le bas jusqu’à la dernière ligne de données :
=IF(COUNTIF($E$5 :E5,E5)>1,0,1)
La première référence de la plage ($E$5) est Absolue, et la seconde est relative (E5), donc au fur et à mesure que nous copions la formule vers le bas de la page, la plage va s’étendre de $E$5:E5 à $E$5:E6, puis $E$5:E7 et ainsi de suite.
Dans chaque ligne, nous vérifions combien de fois la valeur de la colonne E (Personne) est apparue, jusqu’à la ligne actuelle. Si elle est supérieure à une fois, le résultat est zéro, donc cette personne n’est pas recomptée. Cela nous permet de voir le nombre de fois uniques dans l’ensemble de données où le nom de chaque Personne est apparu.
Avec l’ajout de la colonne supplémentaire, les données ressembleraient à ceci . La deuxième instance de » Harry » renvoie un zéro dans la colonne Unique.
NOTE : Si des personnes portant le même nom vendent dans différentes régions :
- Dans Excel 2007 ou une version ultérieure : Utilisez COUNTIFS, pour vérifier plusieurs critères. Par exemple, =IF(COUNTIFS($D$5:$D5,D5,$E$5:E5,E5)=1,1,0)
- En Excel 2003 ou antérieur, concaténer les noms de Région et de Personne dans une nouvelle colonne, puis utiliser la formule COUNTIF pour trouver les valeurs uniques dans cette colonne concaténée.
Créer un tableau croisé dynamique
Pour voir les comptes uniques dans un tableau croisé dynamique :
- Créer un tableau croisé dynamique à partir de ces données, avec Région dans la zone Rows
- Ajouter Unique, Unités et Valeur dans la zone Valeurs.
- Changez l’en-tête « Unique » en « Compte de personne » ou « Personne »
Nous pouvons voir qu’il y a 30 personnes qui ont fait les ventes, et le nombre de chacune d’entre elles qui sont dans chaque région, et si nous choisissons de développer n’importe quelle région, alors nous verrions les totaux individuels pour chaque personne.
Comparaison des performances
Si possible, utilisez la méthode « pivot a pivot », qui est beaucoup plus rapide.
Méthode COUNTIF
L’utilisation d’une formule COUNTIF dans les données sources fonctionne, et nous donne notre résultat souhaité, et sur un ensemble de données relativement petit, la méthode est acceptable. Mais, elle est très coûteuse en termes de temps de traitement, et sur de très grands ensembles de données, elle peut être extrêmement lente.
L’utilisation de « FastExcel » de Charles Williams pour calculer le temps de calcul du classeur donne le résultat présenté ci-dessous, où le temps pris pour recalculer la feuille de données est de 224 millisecondes.
Méthode Pivot a Pivot
La méthode « Pivot a Pivot » a également produit le résultat correct, avec un compte unique de Personne, comme requis. En outre, elle ne repose pas sur des colonnes supplémentaires dans les données sources, ni sur la saisie de nouvelles formules.
Le classeur résultant est plus petit, et encore une fois en utilisant « FastExcel » de Charles Williams pour calculer le temps de calcul de la feuille de données est incroyablement rapide 1.2 millisecondes
Avec de petits ensembles de données, le moment du recalcul peut ne pas être critique, mais lorsqu’on a affaire à de plus grands ensembles de données de 300 000 à 400 000 lignes, alors ces différences deviendraient très matérielles.
Power Pivot
Si vous avez installé le complément PowerPivot, vous pouvez l’utiliser pour afficher un compte distinct pour un champ. Cette vidéo montre les étapes de création d’un tableau croisé dynamique Power Pivot, et l’ajout d’un champ avec le compte unique.
Pour des instructions écrites, voir les étapes d’Excel 2013 sur mon blog sur les tableaux croisés dynamiques. Il existe également des instructions pour Excel 2010.
Fichier d’exemple : pour suivre la vidéo, téléchargez le fichier d’exemple qui a été utilisé pour cette vidéo
Obtenir les fichiers d’exemple
- Modèle de données : Téléchargez le classeur d’exemple de modèle de données, qui comporte l’exemple de la méthode 1 (modèle de données). Le fichier est zippé, et au format xlsx. Le fichier ne contient pas de macros.
- Non Modèle de données : Téléchargez le classeur d’exemple sans modèle de données, qui contient des exemples pour la méthode 2 (pivot un pivot) et la méthode 3 (colonne dans les données source). Le fichier est zippé, et au format xlsb. Le fichier ne contient pas de macros.
- Vidéo Power Pivot : Pour suivre la vidéo Power Pivot de la méthode 4, téléchargez le fichier d’exemple qui a été utilisé pour la vidéo
Tutoriels connexes
Nommer des plages
Compter unique dans PowerPivot
Fichiers d’exemple – Roger Govier
À propos du développeur
Roger Govier est un MVP Excel basé au Royaume-Uni qui entreprend des missions dans Excel et VBA pour des clients du monde entier. Bien qu’il apprécie le défi intellectuel que représente la résolution de problèmes à l’aide de fonctions de feuilles de calcul, Roger affirme être intrinsèquement paresseux, de sorte qu’il cherche toujours un moyen rapide et simple de fournir des solutions solides et exploitables.
Trouver d’autres tutoriels et fichiers échantillons de Roger ici : Fichiers d’échantillons – Roger Govier
Vous pouvez contacter Roger à l’adresse suivante : [email protected]
Roger Govier
.