Tableau croisé dynamique Simple pour compter les valeurs uniques

Mise à jour: vous pouvez le faire maintenant automatiquement avec Excel 2013. J'ai créé ceci comme une nouvelle réponse parce que ma réponse précédente résout en fait un problème légèrement différent.

Si vous avez cette version, sélectionnez vos données pour créer un tableau croisé dynamique, et lorsque vous créez votre table, assurez-vous que la case à cocher "Ajouter ces données au modèle de données" est cochée (voir ci-dessous).

Ensuite, lorsque votre tableau croisé dynamique s'ouvre, créez vos lignes, colonnes et valeurs normalement. Puis cliquez sur le champ dont vous souhaitez calculer le nombre distinct et modifier les paramètres de valeur du champ:

Enfin, faites défiler jusqu'à la toute dernière option et choisissez 'nombre Distinct.'

Cela devrait mettre à jour les valeurs de votre tableau croisé dynamique pour afficher les données que vous recherchez.

Je voudrais lancer une option supplémentaire dans le mix qui ne nécessite pas de formule mais qui pourrait être utile si vous avez besoin de compter des valeurs uniques dans l'ensemble sur deux colonnes différentes. En utilisant l'exemple original, je n'avais pas:

ABC   123  
ABC   123  
ABC   123   
DEF   456  
DEF   567  
DEF   456  
DEF   456

Et veulent qu'il apparaisse comme:

ABC   1  
DEF   2

, Mais quelque chose de plus comme:

ABC   123  
ABC   123  
ABC   123  
ABC   456  
DEF   123  
DEF   456  
DEF   567  
DEF   456  
DEF   456

Et voulait qu'il apparaisse comme:

ABC  
   123    3  
   456    1  
DEF  
   123    1  
   456    3  
   567    1

J'ai trouvé la meilleure façon d'obtenir mes données dans ce format et ensuite être capable de le manipuler davantage était d'utiliser le suivant:

Une fois que vous avez sélectionné "exécuter total dans", Choisissez l'en-tête de l'ensemble de données secondaire (dans ce cas, ce serait l'en-tête ou le titre de la colonne de l'ensemble de données qui comprend 123, 456 et 567). Cela vous donnera une valeur maximale avec le nombre total d'éléments dans cet ensemble, dans votre ensemble de données principal.

J'ai ensuite copié ces données, les ai collées en tant que valeurs, puis les ai placées dans un autre tableau croisé dynamique pour les manipuler plus facilement.

POUR INFO, j'avais environ un quart de million des lignes de données donc cela a fonctionné beaucoup mieux que certaines des approches de Formule, en particulier celles qui essaient de comparer entre deux colonnes/ensembles de données car il a continué à planter l'application.

La possibilité de faire un "comptage distinct" fait partie D'Excel 2013 mais n'est pas activée automatiquement.

Donc, si vous exécutez une copie EXCEL 2013, voici une excellente façon de résoudre cela sans les tracas de passer par une fonction: http://datapigtechnologies.com/blog/index.php/distinct-count-in-pivot-tables-finally-in-excel-2013/

Voir les articles uniques de Debra Dalgleish Count

J'ai trouvé que l'approche la plus simple consiste à utiliser l'option Distinct Count sous Value Field Settings (à gauche cliquez sur le champ dans le volet Values). L'option pour Distinct Count se trouve tout en bas de la liste.

Voici l'avant (haut; normal Count) et après (bas; Distinct Count)

Il n'est pas nécessaire que la table soit triée pour que la formule suivante renvoie un 1 pour chaque valeur unique présente.

En supposant que la plage de table pour les données présentées dans la question Est A1: B7 entrez la formule suivante dans la cellule C1:

=IF(COUNTIF($B$1:$B1,B1)>1,0,COUNTIF($B$1:$B1,B1))

Copiez cette formule sur toutes les lignes et la dernière ligne contiendra:

=IF(COUNTIF($B$1:$B7,B7)>1,0,COUNTIF($B$1:$B7,B7))

Il en résulte un 1 renvoyé la première fois qu'un enregistrement est trouvé et 0 pour tous les temps par la suite.

Additionnez simplement la colonne dans votre pivot tableau

Mon approche de ce problème était un peu différente de ce que je vois ici, donc je vais partager.

1. (Faites d'abord une copie de vos données)
2. Concaténer les colonnes
3. Supprimer les doublons sur la colonne concaténée
4. , Dernier - pivot sur l'ensemble

Note: Je voudrais inclure des images pour rendre cela encore plus facile à comprendre mais ne peux pas parce que c'est mon premier post;)

La réponse de Siddharth est formidable.

cependant , cette technique peut avoir des problèmes lorsque vous travaillez avec un grand ensemble de données (mon ordinateur a gelé sur 50 000 lignes). Certaines méthodes moins gourmandes en Processeur:

Vérification unique de l'unicité

1. Trier par les deux colonnes (A, B dans cet exemple)

2. Utilisez une formule qui regarde moins de données

   =IF(SUMPRODUCT(($A2:$A3=A2)*($B2:$B3=B2))>1,0,1) 
   

Plusieurs contrôles d'unicité

Si vous avez besoin de vérifier l'unicité dans différentes colonnes, vous ne pouvez pas compter sur deux sortes.

Au lieu de cela,

1. Trier une seule colonne (A)

2. Ajouter formule couvrant le nombre maximal d'enregistrements pour chaque regroupement. Si ABC peut avoir 50 lignes, la formule sera

   =IF(SUMPRODUCT(($A2:$A49=A2)*($B2:$B49=B2))>1,0,1)
   

Excel 2013 peut faire count distinct dans les pivots. Si aucun accès à 2013, et c'est une plus petite quantité de données, je fais deux copies des données brutes, et dans la copie b, sélectionnez les deux colonnes et supprimez les doublons. Ensuite, faites le pivot et comptez votre colonne B.

Vous pouvez utiliser COUNTIFS pour plusieurs critères,

= 1 / COUNTIFS (A: A,A2, B: B, B2) puis faites glisser vers le bas. Vous pouvez mettre autant de critères que vous voulez, mais cela prend beaucoup de temps à traiter.

Étape 1. Ajouter une colonne

Étape 2. Utilisez la formule =IF(COUNTIF(C2:$C$2410,C2)>1,0,1) dans le 1er enregistrement

Étape 3. faites-le Glisser vers tous les enregistrements

Étape 4. Filtre " 1 " dans la colonne avec la formule

, Vous pouvez faire une colonne supplémentaire pour stocker l'unicité, alors la somme que dans votre tableau croisé dynamique.

Ce que je veux dire, c'est que la cellule C1 devrait toujours être 1. Cellule C2 doit contenir la formule =IF(COUNTIF($A$1:$A1,$A2)*COUNTIF($B$1:$B1,$B2)>0,0,1). Copiez cette formule afin que la cellule C3 contienne =IF(COUNTIF($A$1:$A2,$A3)*COUNTIF($B$1:$B2,$B3)>0,0,1) et ainsi de suite.

Si vous avez une cellule d'en-tête, vous voudrez tous les déplacer dans une rangée et votre formule C3 devrait être =IF(COUNTIF($A$2:$A2,$A3)*COUNTIF($B$2:$B2,$B3)>0,0,1).

Si vous avez les données triées.. je suggère d'utiliser la formule suivante

=IF(OR(A2<>A3,B2<>B3),1,0)

C'est plus rapide car il utilise moins de cellules pour calculer.

Je trie habituellement les données par le champ dont j'ai besoin pour faire le nombre distinct de puis utiliser IF (A2=A1, 0, 1); vous obtenez alors un 1 dans la rangée supérieure de chaque groupe d'identifiants. Simple et ne prend pas de temps à calculer sur de grands ensembles de données.

Vous pouvez également utiliser pour la colonne d'aide VLOOKUP. J'ai testé et semble un peu plus rapide que COUNTIF.

Si vous utilisez l'en-tête et que les données commencent dans la cellule A2, dans n'importe quelle cellule de la ligne, utilisez cette formule et copiez-la dans toutes les autres cellules de la même colonne:

=IFERROR(IF(VLOOKUP(A2;$A$1:A1;1;0)=A2;0;1);1)

J'ai trouvé un moyen plus facile de le faire. Se référant à L'exemple de Siddarth Rout, si je veux compter des valeurs uniques dans la colonne A:

• Ajouter une nouvelle colonne C et remplir C2 avec la Formule "= 1 / COUNTIF ($A:$A, A2) "
• Faites glisser la formule vers le reste de la colonne
• pivot avec la colonne A comme étiquette de ligne, et Sum{colonne C) dans les valeurs pour obtenir le nombre de valeurs uniques dans la colonne a