webentwicklung-frage-antwort-db.com.de

Einfache Pivot-Tabelle zum Zählen eindeutiger Werte

Dies scheint eine einfache Pivot-Tabelle zu sein, mit der man lernen kann. Ich möchte eine Zählung der eindeutigen Werte für einen bestimmten Wert durchführen, für den ich eine Gruppierung vornehme.

Ich habe zum Beispiel folgendes:

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

Was ich möchte, ist ein Pivot-Tisch, der mir Folgendes zeigt:

ABC   1
DEF   2

Die einfache Pivot-Tabelle, die ich erstelle, gibt mir nur Folgendes (Anzahl der Zeilen):

ABC   3
DEF   4  

Aber ich möchte stattdessen die Anzahl der eindeutigen Werte.

Was ich wirklich versuche, ist herauszufinden, welche Werte in der ersten Spalte nicht für alle Zeilen den gleichen Wert in der zweiten Spalte haben. Mit anderen Worten, "ABC" ist "gut", "DEF" ist "schlecht"

Ich bin mir sicher, dass es einen einfacheren Weg gibt, aber ich dachte, ich würde Pivot Table ausprobieren ...

133
user1586422

Fügen Sie eine dritte Spalte ein und fügen Sie in Zelle C2 diese Formel ein

=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)

und kopiere es nach unten. Erstellen Sie nun Ihren Pivot basierend auf der 1. und 3. Spalte. Siehe Schnappschuss

enter image description here

106
Siddharth Rout

UPDATE: Sie können dies jetzt automatisch mit Excel 2013 tun. Ich habe dies als neue Antwort erstellt, da meine vorherige Antwort tatsächlich ein etwas anderes Problem löst.

Wenn Sie über diese Version verfügen, wählen Sie Ihre Daten aus, um eine Pivot-Tabelle zu erstellen, und stellen Sie beim Erstellen Ihrer Tabelle sicher, dass das Kontrollkästchen "Diese Daten zum Datenmodell hinzufügen" aktiviert ist (siehe unten).

Tick the box next to 'Add this data to the Data Model'

Erstellen Sie dann beim Öffnen der Pivot-Tabelle Ihre Zeilen, Spalten und Werte wie gewohnt. Klicken Sie dann auf das Feld, dessen eindeutige Anzahl Sie berechnen möchten, und bearbeiten Sie die Feldwerteinstellungen: Edit field value settings

Scrollen Sie abschließend bis zur letzten Option und wählen Sie "Distinct Count". Choose the option 'Distinct Count'

Dadurch sollten Ihre Pivot-Tabellenwerte aktualisiert werden, um die gesuchten Daten anzuzeigen.

246

Ich möchte eine zusätzliche Option in den Mix integrieren, für die keine Formel erforderlich ist, die jedoch hilfreich sein kann, wenn Sie eindeutige Werte innerhalb des Sets in zwei verschiedenen Spalten zählen müssen. Nach dem ursprünglichen Beispiel hatte ich nicht:

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

und möchte, dass es wie folgt aussieht:

ABC   1  
DEF   2

Aber eher so etwas wie:

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

und wollte, dass es so aussieht:

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

Ich habe den besten Weg gefunden, meine Daten in dieses Format zu bringen und sie dann weiter zu manipulieren, indem ich Folgendes benutzte:

enter image description here

Wenn Sie "Laufende Summe in" ausgewählt haben, wählen Sie die Überschrift für den sekundären Datensatz aus (in diesem Fall ist dies die Überschrift oder der Spaltentitel des Datensatzes, der 123, 456 und 567 enthält). Auf diese Weise erhalten Sie einen Maximalwert für die Gesamtzahl der Elemente in diesem Satz innerhalb Ihres Primärdatensatzes.

Ich habe diese Daten dann kopiert, als Werte eingefügt und dann in eine andere Pivot-Tabelle eingefügt, um sie einfacher zu bearbeiten.

Zu Ihrer Information, ich hatte ungefähr eine Viertelmillion Datenzeilen, so dass dies viel besser funktionierte als einige der Formelansätze, insbesondere solche, die versuchen, zwei Spalten/Datensätze zu vergleichen, weil die Anwendung dadurch immer wieder abstürzte.

10

Die Möglichkeit, eine "eindeutige Zählung" durchzuführen, ist Teil von Excel 2013, wird jedoch nicht automatisch aktiviert.

Wenn Sie eine Excel 2013-Kopie ausführen, können Sie dies auf brillante Weise lösen, ohne eine Funktion durchlaufen zu müssen: http://datapigtechnologies.com/blog/index.php/distinct-count-in- Pivot-Tabellen-endlich-in-Excel-2013 /

7
Greg

Ich fand die einfachste Möglichkeit, die Option Distinct Count unter Value Field Settings zu verwenden ( mit der linken Maustaste auf das Feld im Values Feld). Die Option für Distinct Count befindet sich ganz am Ende der Liste.

Location of where to click

Hier sind die vor (TOP; normal Count) und nach (BOTTOM; Distinct Count)

COUNT

DISTINCT COUNT

6
Peter

Siehe Debra Dalgleishs Zähle einzigartige Gegenstände

enter image description here

4
brettdj

Für die folgende Formel muss die Tabelle nicht sortiert werden, damit für jeden vorhandenen eindeutigen Wert eine 1 zurückgegeben wird.

angenommen, der Tabellenbereich für die in der Frage dargestellten Daten ist A1: B7. Geben Sie die folgende Formel in Zelle C1 ein:

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

Kopieren Sie diese Formel in alle Zeilen und die letzte Zeile enthält:

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

Dies führt dazu, dass beim ersten Auffinden eines Datensatzes eine 1 und danach immer eine 0 zurückgegeben wird.

Summieren Sie einfach die Spalte in Ihrer Pivot-Tabelle

3
Jeff Baumet

Mein Ansatz zu diesem Problem war ein wenig anders als das, was ich hier sehe, also teile ich es.

  1. (Machen Sie zuerst eine Kopie Ihrer Daten)
  2. Verketten Sie die Spalten
  3. Entfernen Sie Duplikate in der verketteten Spalte
  4. Last - Pivot auf der Ergebnismenge

Anmerkung: Ich möchte Bilder einbinden, um dies noch verständlicher zu machen, kann es aber nicht, da dies mein erster Beitrag ist;)

2

Siddharths Antwort ist großartig.

Allerdings kann diese Technik bei der Arbeit mit einer großen Datenmenge Probleme bereiten (mein Computer hat sich auf 50.000 Zeilen eingefroren). Einige weniger prozessorintensive Methoden:

Einzelprüfung der Eindeutigkeit

  1. Nach den beiden Spalten sortieren (A, B in diesem Beispiel)
  2. Verwenden Sie eine Formel, die weniger Daten berücksichtigt

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

Mehrere Eindeutigkeitsprüfungen

Wenn Sie die Eindeutigkeit in verschiedenen Spalten überprüfen müssen, können Sie sich nicht auf zwei Arten verlassen.

Stattdessen,

  1. Einzelne Spalte sortieren (A)
  2. Fügen Sie eine Formel für die maximale Anzahl von Datensätzen für jede Gruppierung hinzu. Wenn ABC möglicherweise 50 Zeilen enthält, lautet die Formel

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

Excel 2013 kann die Anzahl der Pivots unterscheiden. Wenn kein Zugriff auf 2013 möglich ist und es sich um eine geringere Datenmenge handelt, erstelle ich zwei Kopien der Rohdaten und wähle in Kopie b beide Spalten aus und entferne Duplikate. Machen Sie dann den Pivot und zählen Sie Ihre Spalte b.

1
Zachary

Sie können COUNTIFS für mehrere Kriterien verwenden,

= 1/COUNTIFS (A: A, A2, B: B, B2) und dann ziehen. Sie können so viele Kriterien eingeben, wie Sie möchten, aber die Verarbeitung nimmt in der Regel viel Zeit in Anspruch.

1
user5100077

Schritt 1. Eine Spalte hinzufügen

Schritt 2. Verwenden Sie die Formel = IF(COUNTIF(C2:$C$2410,C2)>1,0,1) im 1. Datensatz

Schritt 3. Ziehen Sie es auf alle Datensätze

Schritt 4. Filter '1' in der Spalte mit der Formel

1
Abhishek Verma

Sie können für die Hilfsspalte auch VLOOKUP verwenden. Ich habe getestet und sieht ein bisschen schneller aus als COUNTIF.

Wenn Sie eine Kopfzeile verwenden und die Daten in der Zelle A2 beginnen, verwenden Sie in jeder Zelle in der Zeile diese Formel und kopieren Sie sie in alle anderen Zellen in derselben Spalte:

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

Normalerweise sortiere ich die Daten nach dem Feld, das ich für die eindeutige Zählung benötige, und verwende dann IF (A2 = A1,0,1). Sie erhalten dann eine 1 in der oberen Reihe jeder Gruppe von IDs. Einfach und ohne großen Zeitaufwand zu berechnen.

0

Wenn Sie die Daten sortiert haben, schlage ich vor, die folgende Formel zu verwenden

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

Dies ist schneller, da weniger Zellen für die Berechnung benötigt werden.

0
SumitB

Sie können eine zusätzliche Spalte erstellen, um die Eindeutigkeit zu speichern, und dann dass in Ihrer Pivot-Tabelle summieren.

Ich meine, Zelle C1 sollte immer 1 sein. Die Zelle C2 sollte die Formel =IF(COUNTIF($A$1:$A1,$A2)*COUNTIF($B$1:$B1,$B2)>0,0,1) enthalten. Kopieren Sie diese Formel nach unten, sodass die Zelle C3=IF(COUNTIF($A$1:$A2,$A3)*COUNTIF($B$1:$B2,$B3)>0,0,1) usw. enthält.

Wenn Sie über eine Header-Zelle verfügen, möchten Sie diese alle in einer Zeile nach unten verschieben, und Ihre C3 -Formel sollte =IF(COUNTIF($A$2:$A2,$A3)*COUNTIF($B$2:$B2,$B3)>0,0,1) lauten.

0
lc.