У меня есть несколько учеников, которые выбрали первые классы на первом листе. На втором листе я хотел бы создать список для каждого класса с именами учащихся в этом классе на основе их выбора. Есть ли способ сделать это?
1 ответ
2
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) Создайте список имен
Чтобы создать столбец имен, которые выбрали курс в строке заголовка в качестве одного из 3 вариантов, можно использовать следующую формулу. В приведенном ниже примере эта формула была размещена в 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
Если студент выберет один и тот же курс более одного раза, его имя появится в списке более одного раза.