1) Erstellen Sie eine Kopfzeile mit Kursnamen

Question

Ich habe mehrere Schüler, die auf dem ersten Blatt ausgewählt haben, in welchen Klassen sie sein möchten. Auf dem zweiten Blatt möchte ich für jede Klasse eine Liste mit den Namen der Schüler in dieser Klasse basierend auf ihren Auswahlen erstellen. Gibt es eine Möglichkeit, dies zu tun?

Answer 1

1) Erstellen Sie eine Kopfzeile mit Kursnamen

Das Wichtigste zuerst: In Blatt 2 müssen Sie eine horizontale Liste von Klassen erstellen. Sie können dies entweder manuell tun oder die folgende Formel verwenden, um eine sortierte Liste der verwendeten eindeutigen Klassennamen aus den Schülerauswahlen für Klasse 1-3 zu erstellen. Es sollte sogar funktionieren, leere Klasseneinträge zu ignorieren. Die einzigen zwei Einschränkungen, die mir einfallen, sind, dass die Zelle links von der Liste gleich einem der Namen in der Liste sein muss. Die andere Einschränkung ist, dass dies eine Array-Formel ist und CONTROL+ SHIFT+ erfordert ENTER, anstatt nur ENTERbeim Bestätigen der Formel. Sie wissen, dass Sie es richtig gemacht haben, wenn { } um die Formel herum erscheinen. Beachten Sie, dass die { } nicht manuell hinzugefügt werden dürfen.

Im Beispiel habe ich die folgende Formel in Blatt2!B2 eingefügt und nach rechts kopiert, bis leere Zellen erschienen.

=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),"")

Da es sich um eine Array-Formel handelt, verwenden Sie keine vollständigen Zeilen-/Spaltenbezüge wie A:A oder 3:3, da dies zu einer übermäßigen Anzahl von Berechnungen führen würde.

2) Erstellen Sie eine Liste mit Namen

Um eine Spalte mit Namen zu generieren, die den Kurs in der Kopfzeile als eine ihrer 3 Auswahlmöglichkeiten gewählt haben, kann die folgende Formel verwendet werden. Im folgenden Beispiel wurde diese Formel in Sheet2!B3 eingefügt und nach rechts kopiert, um der Liste der Kursnamen zu entsprechen, und nach unten, 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 arrayähnliche Operationen ausführen, je nach ausgewählter Formelnummer. Wenn die erste Parameternummer 14 oder 15 und anscheinend noch ein paar andere ist, werden arrayähnliche Operationen ausgeführt. Der zweite Zahlenparameter weist AGGREGATE an, Fehlerergebnisse, ausgeblendete Zeilen und einige andere Dinge zu ignorieren, glaube ich. Vermeiden Sie aufgrund der arrayähnlichen Berechnungen erneut die Verwendung vollständiger Spaltenreferenzen innerhalb der AGGREGATE-Funktion.

Quelle: Sheet1

Ausgabe: Sheet2

Wenn ein Student denselben Kurs mehr als einmal wählt, erscheint sein Name mehr als einmal in der Liste.