1) Erzeugen Sie eine Kopfzeile mit Kursnamen
Zunächst einmal müssen Sie in Sheet2 eine horizontale Klassenliste erstellen. Sie können dies entweder manuell tun oder Sie können die folgende Formel verwenden, um eine sortierte Liste der verwendeten eindeutigen Klassennamen aus den Auswahlmöglichkeiten der Schüler für Klasse 1-3 zu generieren. Es sollte sogar funktionieren, wenn leere Klasseneinträge ignoriert werden. Die einzigen zwei Einschränkungen, die mir einfallen, sind, dass Sie die Zelle links von der Liste haben müssen, die mit einem der Namen in der Liste übereinstimmt. Der andere Nachteil ist, dass dies eine Matrixformel ist und CONTROL+ SHIFT+ erfordert, ENTERanstatt nur ENTERdie Formel zu bestätigen. Sie werden wissen, dass Sie es richtig gemacht haben, wenn {} in der Formel angezeigt wird. Beachten Sie, dass {} möglicherweise nicht manuell hinzugefügt wird.
Im Beispiel habe ich die folgende Formel in Sheet2! B2 eingefügt und nach rechts kopiert, bis leere Zellen angezeigt wurden.
=IFERROR(INDEX(Sheet1!$B$2:$D$9,SMALL(IF(SMALL(IF(COUNTIF($A$2:A2,Sheet1!$B$2:$D$9)+ISBLANK(Sheet1!$B$2:$D$9)=0,COUNTIF(Sheet1!$B$2:$D$9,"<"&Sheet1!$B$2:$D$9)+1,""),1)=IF(ISBLANK(Sheet1!$B$2:$D$9),"",COUNTIF(Sheet1!$B$2:$D$9,"<"&Sheet1!$B$2:$D$9)+1),ROW(Sheet1!$B$2:$D$9)-MIN(ROW(Sheet1!$B$2:$D$9))+1),1),MATCH(MIN(IF(COUNTIF($A$2:A2,Sheet1!$B$2:$D$9)+ISBLANK(Sheet1!$B$2:$D$9)>0,"",COUNTIF(Sheet1!$B$2:$D$9,"<"&Sheet1!$B$2:$D$9)+1)),INDEX(IF(ISBLANK(Sheet1!$B$2:$D$9),"",COUNTIF(Sheet1!$B$2:$D$9,"<"&Sheet1!$B$2:$D$9)+1),SMALL(IF(SMALL(IF(COUNTIF($A$2:A2,Sheet1!$B$2:$D$9)+ISBLANK(Sheet1!$B$2:$D$9)=0,COUNTIF(Sheet1!$B$2:$D$9,"<"&Sheet1!$B$2:$D$9)+1,""),1)=IF(ISBLANK(Sheet1!$B$2:$D$9),"",COUNTIF(Sheet1!$B$2:$D$9,"<"&Sheet1!$B$2:$D$9)+1),ROW(Sheet1!$B$2:$D$9)-MIN(ROW(Sheet1!$B$2:$D$9))+1),1),,1),0),1),"") Verwenden Sie als Arrayformel keine vollständigen Zeilen- / Spaltenreferenzen wie A: A oder 3: 3, da sonst zu viele Berechnungen ausgeführt werden.
2) Erzeugen Sie eine Liste mit Namen
Um eine Spalte mit Namen zu generieren, die den Kurs in der Kopfzeile als eine ihrer drei Optionen auswählt, kann die folgende Formel verwendet werden. Im folgenden Beispiel wurde diese Formel in Sheet2! B3 platziert und nach rechts kopiert, um die Liste der Kursnamen abzugleichen, und so lange, bis nur noch leere Zeilen vorhanden sind.
=IFERROR(INDEX(Sheet1!$A:$A,AGGREGATE(14,6,ROW(Sheet1!$B$2:$D$9)/(Sheet1!$B$2:$D$9=B$2),ROW(A1))),"") Die AGGREGATE-Funktion kann abhängig von der ausgewählten Formelnummer Array-ähnliche Operationen ausführen. Wenn die erste Parameternummer 14 oder 15 ist und anscheinend einige andere, werden Array-ähnliche Operationen ausgeführt. Der zweite number-Parameter weist AGGREGATE an, Fehlerergebnisse zu ignorieren, verborgene Zeilen zwischen einigen anderen Dingen, die ich glaube. Vermeiden Sie als Ergebnis der Array-ähnlichen Berechnungen die Verwendung vollständiger Spaltenverweise innerhalb der AGGREGATE-Funktion.
Quelle: Blatt1
Ausgabe: Sheet2
WENN ein Schüler denselben Kurs mehr als einmal wählt, erscheint sein Name mehr als einmal in der Liste.