1) Создайте строку заголовка названий курсов
Перво-наперво, в sheet2 вам нужно создать горизонтальный список классов. Вы можете сделать это вручную или использовать следующую формулу, чтобы создать отсортированный список использованных уникальных имен классов по выбору учащихся для 1-3 класса. Это должно даже работать на игнорировании пустых записей класса. Единственное, о чем я могу подумать, это то, что вам нужно иметь ячейку слева от списка, равную любому из имен в списке. Другое предостережение в том, что это формула массива, которая требует CONTROL+ SHIFT+, ENTERа не только ENTERпри подтверждении формулы. Вы будете знать, что сделали все правильно, когда {} появится вокруг формулы. Обратите внимание, что {} нельзя добавлять вручную.
В этом примере я вставил следующую формулу в Sheet2! 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) Создайте список имен
Чтобы сгенерировать столбец имен, которые выбрали курс в строке заголовка в качестве одного из трех вариантов, можно использовать следующую формулу. В приведенном ниже примере эта формула была размещена в Sheet2! 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
Выход: Sheet2
Если студент выберет один и тот же курс более одного раза, его имя появится в списке более одного раза.