1) Создайте строку заголовка с названиями курсов.

Question

У меня есть несколько студентов, которые выбрали, в каких классах они хотят учиться, на первом листе. На втором листе я хотел бы создать список для каждого класса с именами студентов в этом классе на основе их выбора. Есть ли способ сделать это?

Answer 1

1) Создайте строку заголовка с названиями курсов.

Во-первых, в sheet2 вам нужно сгенерировать горизонтальный список классов. Вы можете сделать это вручную или использовать следующую формулу для генерации отсортированного списка используемых уникальных имен классов из выборов учеников для классов 1-3. Это должно работать даже при игнорировании пустых записей классов. Единственные два предостережения, которые я могу придумать, это то, что вам нужно иметь ячейку слева от списка, которая равна любому из имен в списке. Другое предостережение заключается в том, что это формула массива и потребует CONTROL+ SHIFT+ ENTERвместо того, чтобы просто ENTERподтвердить формулу. Вы поймете, что сделали все правильно, когда вокруг формулы появятся { }. Обратите внимание, что { } нельзя добавить вручную.

В примере я вставил следующую формулу на Лист2!B2 и скопировал ее вправо, пока не появятся пустые ячейки.

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

Поскольку это формула массива, не используйте полную ссылку на строку/столбец, например A:A или 3:3, так как это приведет к выполнению чрезмерного объема вычислений.

2) Составьте список имен

Для того, чтобы сгенерировать столбец имен, которые выбрали курс в строке заголовка в качестве одного из своих 3 вариантов, можно использовать следующую формулу. В примере ниже эта формула была помещена в Лист2!B3 и скопирована вправо, чтобы соответствовать списку названий курсов, и вниз, пока не останутся только пустые строки.

=IFERROR(INDEX(Sheet1!$A:$A,AGGREGATE(14,6,ROW(Sheet1!$B$2:$D$9)/(Sheet1!$B$2:$D$9=B$2),ROW(A1))),"")

Функция AGGREGATE может выполнять операции, подобные массиву, в зависимости от выбранного номера формулы. Когда первый параметр равен 14 или 15 и несколько других, по-видимому, будут выполнены операции, подобные массиву. Второй параметр-число сообщает AGGREGATE игнорировать результаты ошибок, скрытые строки и некоторые другие вещи, как я полагаю. В результате вычислений, подобных массиву, снова избегайте использования полных ссылок на столбцы в функции AGGREGATE.

Источник: Лист1

Вывод: Лист2

ЕСЛИ студент выбирает один и тот же курс более одного раза, его имя будет отображаться в списке более одного раза.