Я хотел бы создать список на основе выборов, сделанных студентами

459
Erica Engelby

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

enter image description here

0
С сценариями да, с формулами Excel, я не верю в это. thepip3r 5 лет назад 1
Это можно сделать с помощью формул, но не так легко и чисто, как VBA (не то, чтобы VBA был тривиальным). Это может быть даже возможно с помощью сводной таблицы. Super User - это не бесплатная услуга «напиши мне код», поэтому просто опубликовать требование не входит в сферу применения. Однако, если вы решите эту проблему самостоятельно и столкнетесь с конкретной проблемой, люди, которые здесь помогут, решат эту проблему, если вы опубликуете свою работу и опишите проблему. fixer1234 5 лет назад 0
@ thepip3r это можно сделать с помощью формул Forward Ed 5 лет назад 0
@ fixer1234 Полностью согласен, что формулы не чистые! Forward Ed 5 лет назад 0

1 ответ на вопрос

2
Forward Ed

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

enter image description here

Выход: Sheet2

enter image description here

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

Вы доказали, что я неправ. Это ** было ** тривиально. :-) +1 fixer1234 5 лет назад 0
Выглядит ужасно, не тривиально. ;) thepip3r 5 лет назад 0
Дай бог, чтобы вы не поместили запятую или скобку! Хотя я учился, я непоследовательно пишу Биология. Forward Ed 5 лет назад 0

Похожие вопросы