Encontrando a última célula não vazia em uma linha com um conjunto específico de valores

Question

Tenho uma planilha com dados de alunos, onde cada linha representa um único aluno. Para qualquer turma, existem múltiplas colunas 1-N, onde N é o maior número de vezes que qualquer um dos alunos repetiu o curso. Então, alguns dados de exemplo, onde um curso foi repetido no máximo 4 vezes, com o que desejo calculado na coluna E:

+---+-------+-------+-------+-------+-----------+
|   |   A   |   B   |   C   |   D   |     E     |
+---+-------+-------+-------+-------+-----------+
| 1 | 225-1 | 225-2 | 225-3 | 225-4 | 225-final |
| 2 | F     | EP    | C     |       | C         |
| 3 | A     |       |       |       | A         |
| 4 | W     | D     | W     | F     | F         |
| 5 | EP    | C     |       |       | C         |
+---+-------+-------+-------+-------+-----------+

Já descobri a lookupfunção, que parece me aproximar do que desejo. Então, por exemplo, LOOKUP("X",A2:D2)obterei a coluna não vazia mais alta (ou seja, a mais recente no alfabeto) para um determinado aluno. Dados os dados acima, eu obteria uma saída como a seguinte:

+---+-------+-------+-------+-------+-----------+
|   |   A   |   B   |   C   |   D   |     E     |
+---+-------+-------+-------+-------+-----------+
| 1 | 225-1 | 225-2 | 225-3 | 225-4 | 225-final |
| 2 | F     | EP    | C     |       | F         |
| 3 | A     |       |       |       | A         |
| 4 | W     | D     | W     | F     | W         |
| 5 | EP    | C     |       |       | EP        |
+---+-------+-------+-------+-------+-----------+

Os problemas com isso são os seguintes:

1. Em vez do valor mais alto da coluna não vazia, na verdade quero o mais baixo. Portanto, os alunos que obtiverem nota D, F, W, EP ou EF poderão substituir a turma e (esperançosamente) terão um desempenho melhor em uma tentativa futura. Quero capturar a tentativa mais recente que não seja W, EP ou EF (veja abaixo). Observe que essa nem sempre é uma nota melhor - tivemos alunos que tiraram D, substituíram a nota e depois ganharam F. Eu gostaria de capturar o F.

2. Uma questão que complica isso é que os alunos podem receber uma nota de EP ou EF, que não deveria ser incluída, e acabar substituindo as notas mais altas, dada a minha abordagem atual, e substituiriam um F se eu pudesse reverter lookup- isso indica que um aluno teve uma desistência emergencial, sendo aprovado ou reprovado no curso. Acho que posso substituir todos os valores de EP e EF por WP ou WF para simplificar as coisas. Se eu fazer isso afetar sua resposta, observe isso!

Answer 1

Seguindo sua primeira matriz, parece que você está interessado na última célula não vazia dessa linha. Com a condição eles não devem ser iguais a EP, EFnem W. Será esta uma suposição correta? Se sim, use abaixo:

Fórmula em E2:

=INDEX(A2:D2,,AGGREGATE(14,3,(A2:D2<>"")*(A2:D2<>"W")*(A2:D2<>"EP")*(A2:D2<>"EF")*(COLUMN(A2:D2)),1))

Arraste para baixo....

Se não é isso que você deseja, pode me dizer quais são as restrições? No momento, às vezes você explica que o último valor é o que você procura e, no final, declara restrições.

EDITAR:

Como é que isso funciona?

AGGREGATEobterá o número mais alto (de acordo com o parâmetro 14) retornado da equação (A2:D2<>"")*(A2:D2<>"W")*(A2:D2<>"EP")*(A2:D2<>"EF")*(COLUMN(A2:D2), que retornará uma matriz de 1s e 0s com base em todas as regras especificadas, multiplicado pelo respectivo número da coluna. O resultado do número de coluna mais alto será o valor de qualificação.

Em seguida, ele usará esse maior número como parâmetro de coluna na INDEXfunção.