Classificação por várias colunas de critérios

0

Estou tentando classificar meus alunos por dois critérios. Primeiro de RESULT e, em seguida, POINT .

No exemplo, a ordem resultante deve ser NUNU, NENE, NONO, NANA e NINI.

Não é possível adicionar RESULT e POINT . Eu pesquisei on-line e não consigo entender como fazer o ranking.

    
por cliffe 23.06.2018 / 06:37

3 respostas

2

Eu gostaria de sugerir uma solução com uma abordagem diferente:

EscrevaestafórmulanoCellJ2(RankCol)&preencha-o,finalmenteClassificardadosemordemcrescenteporcolunadeclassificação:

=COUNTIF($G$2:$G$6,">"&G2)+1+SUMPRODUCT(--($G$2:$G$6=G2),--($H$2:$H$6>H2))
    
por 23.06.2018 / 09:42
1

A solução é bem simples:

InsiraaseguintefórmulaemI2ectrl-enter/copy-paste/preenchaacoluna:

=G2*10^3+H2

Observe que isso só funcionará corretamente se o valor máximo permitido na coluna POINT for 999.

Para valores maiores, o 10^3 na fórmula precisa ser alterado. Por exemplo, se o valor máximo permitido for 9999, será necessário 10^4 .


Se você quiser uma fórmula mais robusta que funcione independentemente dos valores da coluna H , use:

=G2*10^CEILING(LOG10(MAX($H$2:$H$6)),1)+H2

Se você quiser a classificação "real", infelizmente não há como usar as fórmulas acima com a função RANK() , pois ela só permite referências para o segundo argumento, não para matrizes. Uma fórmula completamente diferente é necessária:

InsiraaseguintefórmulaemI2ectrl-enter/copy-paste/preenchaacoluna:

=1+SUMPRODUCT(($G$2:$G$6>G2)+($G$2:$G$6=G2)*($H$2:$H$6>H2))

Uma matriz equivalente digitada ( Ctrl + Shift + Enter ) é:

{=SUM(1,--($G$2:$G$6>G2),($G$2:$G$6=G2)*($H$2:$H$6>H2))}

Claro, se sua exigência é classificar a tabela, a solução mais simples é fazer uma classificação em várias colunas:

    
por 23.06.2018 / 07:09
0
Então, olhando para a sua pergunta anterior, bem como para esta, torna-se óbvio que você deseja classificar os alunos com base no número de A's, em seguida, pelo número de B's, etc, e para separar essas classificações com base nas somas. das marcas para cada assunto.

Com isso em mente, aqui está a fórmula da coluna sem ajuda para fazer exatamente isso:

Matrizentra(Ctrl+Desloca+Enter)aseguintefórmulaemM5ecopia-cola/fill-downnorestodacoluna(nãoesqueçaderemovero{eo}):

{= SUM( 1, --( MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))) >INDEX(MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))),1+ROW()-ROW($B$5:$L$9)) ), ( MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))) =INDEX(MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))),1+ROW()-ROW($B$5:$L$9)) ) *( MMULT(IFERROR(--$B$5:$L$9,0),--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))) >INDEX(MMULT(IFERROR(--$B$5:$L$9,0),--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))),1+ROW()-ROW($B$5:$L$9)) ) )}

A seguir, a versão minificada equivalente da fórmula acima. Eu aconselho strongmente que não use , mas use a versão de fórmula prettified. Fazer isso tornará a fórmula muito mais mais fácil de manter.

{=SUM(1,--(MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9)))))>INDEX(MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))),1+ROW()-ROW($B$5:$L$9))),(MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9)))))=INDEX(MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))),1+ROW()-ROW($B$5:$L$9)))*(MMULT(IFERROR(--$B$5:$L$9,0),--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9)))))>INDEX(MMULT(IFERROR(--$B$5:$L$9,0),--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))),1+ROW()-ROW($B$5:$L$9))))}

Explicação:

Olhando para a estrutura da fórmula prettificada, fica claro que ela é essencialmente a mesma que a fórmula "correta" inserida na matriz da minha resposta anterior

{=SUM(1,--($G$2:$G$6>G2),($G$2:$G$6=G2)*($H$2:$H$6>H2))}

com as colunas auxiliares RESULT e POINT sendo substituídas por uma função MMULT(…) e a referência de célula única nas colunas substituídas por INDEX(MMULT(…),1+ROW()-ROW($B$5:$L$9))

Se você deseja entender como a função MMULT() é usada aqui, você pode começar verificando um uso mais simples na minha resposta para outra pergunta.

Deve ser bastante óbvio como ajustar a fórmula para mais notas. Por exemplo, para adicionar um D , anexe um +($B$5:$L$9="D")*6^0 ao final dos outros e incremente os outros poderes.

Como mencionado na resposta à pergunta anterior, se a tabela for expandida com mais assuntos, o 6 deve ser aumentado, de modo que seja pelo menos um a mais do que o novo número de assuntos na tabela.

    
por 24.06.2018 / 17:29