Concatenar condicional entre linhas e colunas para concatenar

Question

Eu tenho uma folha de excel de atendimento que se parece com isso. Cada aluno é uma linha, cada data de aula é uma coluna. Eu tenho 48 classes no total, 66 alunos no total (Colocando isso porque a complexidade da solução deve ser viável).

Agora,paracadalinha,ouseja,cadaaluno,querosaberasdatasdasaulasperdidas.Paraoaluno6,queroque"9,19,23" seja exibido na última célula dessa linha.

Para cada linha, quero os números de Roll dos alunos que perderam as aulas naquele dia. Para data = 24, quero "7,8,9,10" exibir na última célula dessa coluna.

Como faço isso concatenar condicional sem valores de codificação rígidos para cada célula? Existe alguma fórmula que eu possa usar?

    

Answer 1

Ok, isso é meio confuso, mas funciona desde que você esteja disposto a criar e ocultar um monte de colunas extras.

Basicamente, a ideia é criar uma grade inteira de instruções IF (uma para cada célula nos dados principais de presença), e então esconder todas essas colunas e concatenar seus valores em uma coluna.

À direita dos dados principais, em uma célula na segunda linha (a linha do primeiro aluno), adicione a seguinte fórmula:

=IF(B2="a",B$1&",","")

O que isto está fazendo é dizer que se o aluno nesta fileira estiver ausente no dia, escreva o dia aqui, caso contrário, deixe em branco. (O $ em B $ 1 é essencial porque isso manterá a saída olhando para a primeira linha, mesmo em versões copiadas / coladas da fórmula).

Em seguida, copie / cole essa fórmula em um intervalo completo de células igual ao número de células de dados de presença (portanto, se os dados de presença forem 48 por 66 células, as instruções IF também deverão preencher 48 por 66 células).

Agora você verá 48 colunas de células em branco, mas com algumas células preenchidas para corresponder aos dias em que os alunos estavam ausentes.

Em seguida, em uma coluna à direita de tudo isso, na segunda linha, adicione uma fórmula como esta:

=CONCATENATE(X2,Y2,Z2,...etc...)

Substitua X2, Y2, Z2, etc pelas células das instruções IF (você pode fazer isso rapidamente clicando em cada célula e, em seguida, pressionando a tecla de vírgula).

Quando você pressiona enter, essa célula deve se parecer com algo assim: 6,13,18,

Preencha esta fórmula na planilha para considerar cada linha / aluno.

Agora, se a vírgula final for muito dolorosa de se olhar, você pode criar uma coluna outra para ver a coluna de concatenação e retornar todos os caracteres dessa coluna, exceto o último.

=IF(LEN(X2)>0,LEFT(X2,LEN(X2)-1),"")

Substitua X2 pela célula que possui a string concatenada. (Incluindo IF (LEN (X2) > 0 faz com que você não receba um erro se o aluno teve atendimento perfeito.

Depois é só esconder todas as colunas, exceto a final, e você está pronto!

Como alternativa, acho que o VBA tem uma função de concatenação que ajudaria, mas não sei como usá-lo.