1) Gere uma linha de cabeçalho com nomes de cursos

Question

Tenho vários alunos que selecionaram na primeira planilha quais turmas desejam cursar. Na segunda planilha, gostaria de gerar uma lista para cada turma com os nomes dos alunos daquela turma com base em suas seleções. Existe uma maneira de fazer isso?

Answer 1

1) Gere uma linha de cabeçalho com nomes de cursos

Primeiramente, na planilha2 você precisa gerar uma lista horizontal de classes. Você pode fazer isso manualmente ou usar a fórmula a seguir para gerar uma lista classificada de nomes de turmas exclusivos usados ​​a partir das escolhas dos alunos para as turmas 1-3. Deve até funcionar ignorando entradas de classe em branco. As únicas duas advertências que consigo pensar é que você precisa ter a célula à esquerda da lista que seja igual a qualquer um dos nomes da lista. A outra ressalva é que esta é uma fórmula de matriz e exigirá CONTROL+ SHIFT+ ENTERem vez de apenas ENTERao confirmar a fórmula. Você saberá que fez tudo certo quando {} aparecer ao redor da fórmula. Observe que { } não pode ser adicionado manualmente.

No exemplo, colei a seguinte fórmula em Sheet2!B2 e copiei para a direita até aparecerem células em branco.

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

Sendo uma fórmula de matriz, não use referência completa de linha/coluna como A:A ou 3:3, pois isso causará a execução de uma quantidade excessiva de cálculos.

2) Gere uma lista de nomes

Para gerar uma coluna de nomes que escolheram o curso na linha do cabeçalho como uma de suas 3 opções, a seguinte fórmula pode ser usada. No exemplo abaixo, esta fórmula foi colocada na Planilha2!B3 e copiada para a direita para corresponder à lista de nomes de cursos e para baixo até que haja apenas linhas em branco.

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

A função AGGREGATE pode realizar operações semelhantes a array, dependendo do número da fórmula selecionada. Quando o primeiro número do parâmetro for 14 ou 15 e alguns outros aparentemente, operações semelhantes a array serão executadas. O segundo parâmetro numérico diz ao AGGREGATE para ignorar resultados de erros, linhas ocultas, entre outras coisas em que acredito. Como resultado de cálculos semelhantes a array, evite novamente usar referências de colunas completas na função AGGREGATE.

Fonte: Folha1

Saída: Folha2

CASO o aluno escolha o mesmo curso mais de uma vez, seu nome aparecerá mais de uma vez na lista.