Ich habe einen großen Datensatz mit Studenten und den Kursen, die sie besucht haben. Jeder Student hat 12 bis 18 von etwa 80 verfügbaren Kursen besucht. Mithilfe von Excel (2013) möchte ich für jedes beliebige Kurspaar herausfinden, wie viele Studenten beide Kurse besucht haben. Ich stelle mir eine Tabelle mit den 80 Kursen als Zeilen und Spalten vor, und dann würde ich für jede Schnittmenge eine Zählung sehen, wie viele Studenten diese Kombination besucht haben.
Die Daten kommen als Excel-Datei mit einer Zeile pro Schüler und Klasse:
Student Class
Smith E101
Jones E101
Parker E101
Brown E102
Green E102
Smith E201
Jones E202
Parker E201
Brown E202
Green E203
...
Vorgesehenes Ergebnis:
E101 E102 E201 E202 E203 ...
E101 0 2 1 0
E102 0 0 1 1
E201 2 0 0 0
E202 1 1 0 0
E203 0 1 0 0
...
(Offensichtlich brauche ich nur eine diagonale Hälfte des obigen Bilds, da die andere Hälfte das Spiegelbild davon ist.)
Ich habe eine Pivot-Tabelle verwendet, um die Daten in eine Tabelle mit den Schülern als Zeilen und allen möglichen Klassen als Spalten zu bringen, wobei eine 1 angezeigt wird, wenn ein Schüler eine bestimmte Klasse besucht hat.
E101 E102 E201 E202 E203 ...
Smith 1 1
Jones 1 1
Parker 1 1
Brown 1 1
Green 1 1
...
Aber dann weiß ich nicht, wie ich mit möglichst wenig manuellem Eingriff zum gewünschten Ergebnis komme.
Kann mir jemand einen Weg vorschlagen, wie ich die gewünschte Ausgabe in Excel erzielen kann? Ich habe ziemlich ausführlich gesucht, aber nichts gefunden.
Oder sollte ich nach anderer Software suchen?
Antwort1
Dies lässt sich in Excel ganz einfach mit einer Formel durchführen, die auf Ihre Pivot-Tabelle angewendet wird.
Mit den beiden Tabellen wie folgt aufgestellt
Geben Sie die folgende Formel ein J2und verwenden Sie Strg-Eingabe/Kopieren-Einfügen/Ausfüllen-nach-unten&rechts/Automatisches Ausfüllen in den restlichen Zellen der Tabelle:
=
IF(
J$1=$I2,
"",
COUNTIFS(
INDEX($A$1:$F$6,ROW($A$1:$F$6),MATCH(J$1,$A$1:$F$1,0)):INDEX($A$1:$F$6,1+ROWS($A$1:$F$6)-ROW($A$1:$F$6),MATCH(J$1,$A$1:$F$1,0)),
1,
INDEX($A$1:$F$6,ROW($A$1:$F$6),MATCH($I2,$A$1:$F$1,0)):INDEX($A$1:$F$6,1+ROWS($A$1:$F$6)-ROW($A$1:$F$6),MATCH($I2,$A$1:$F$1,0)),
1
)
)
Erläuterung:
Das erste Argument der COUNTIFS()Funktion ist die dynamisch generierte Spalte der Pivot-Tabelle, die der Spaltenüberschrift der Ausgabetabelle entspricht. Das ist etwas einfacher zu verstehen, wenn wir uns die ausgewerteten Zwischenschritte (für Zelle L2) ansehen:
INDEX($A$1:$F$6,ROW($A$1:$F$6),MATCH(L$1,$A$1:$F$1,0)):INDEX($A$1:$F$6,1+ROWS($A$1:$F$6)-ROW($A$1:$F$6),MATCH(L$1,$A$1:$F$1,0))
→ INDEX($A$1:$F$6,1,MATCH("E201",$A$1:$F$1,0)):INDEX($A$1:$F$6,6,MATCH("E201",$A$1:$F$1,0))
→ INDEX($A$1:$F$6,1,4):INDEX($A$1:$F$6,6,4)
→$D$1:$D$6
(Beachten Sie, dass die zweiten Argumente jeweils INDEX()nur die vollständig dynamischen Start- und Endzeilen der Pivot-Tabelle sind.)
Gleiches gilt für das dritte Argument der COUNTIFS()Funktion, aber diesmal entspricht die dynamisch generierte Spalte der Pivot-Tabelle demReiheKopfzeile der Ausgabetabelle. Für Zellen L2wird Folgendes ausgewertet $B$1:$B$6:
Somit wird die COUNTIFS()Funktion in zuL2
COUNTIFS($D$1:$D$6,1,$B$1:$B$6,1)
Dies ist die Standardmethode zum Zählen der Anzahl der Zeilen (Studenten), in denenbeideSpalten enthalten ein 1(d. h. der Student war in beiden Kursen eingeschrieben).
Die Kapselungsfunktion IF()dient lediglich dazu, sicherzustellen, dass die diagonalen Zellen leer sind.