Como classificar uma tabela sem usar macros do Excel

Imagem
Uma facilidade interessante do Excel é a classificação de tabelas. O Excel permite que tabelas sejam classificadas com várias colunas. Basta marcar a faixa de dados a ser classificada (todas as colunas e todas as linhas) e clicar no ícone CLASSIFICAR na aba Dados. Escolhe-se quais colunas serão utilizadas para classificar e em que ordem (ascendente ou decrescente). Pronto.Agora suponha que queiramos classificar uma tabela usando como argumento uma coluna calculada. Suponha que a cada novo cálculo, queiramos reclassificar a tabela. Fica chato repetir todo o processo acima manualmente a cada nova entrada de dados na tabela. Uma macro pode resolver isso, automatizando o processo. Mas dá para fazer sem macro ? Sim !!

Para classificar uma tabela automaticamente, tudo que temos que fazer é usar a função ORDEM() do Excel.

Essa função recebe os seguintes argumentos
=ordem( número ; coluna ; ascendente_ou_descendente )

número é o valor, dentro da coluna pela qual iremos classificar.

coluna é a faixa (range) de células que contém os valores.

ascendente_ou_descendente é um valor lógico ( falso ou verdadeiro, 0 ou 1) que indica se vai ser em ordem ascendente (1 ou verdadeiro) ou descendente (0 ou falso).

A função vai retornar a “classificação” do número na tabela. Vamos dar um exemplo.

Suponha a seguinte tabela de notas num campeonato de surf.
A…………………… B
Surfista……………. Nota
Joca……………….. 7
Badu……………….. 6
Manso……………… 8
Panela……………… 9
Redondo…………… 4
Peru……………….. 3
Cacetão……………. 5

Para obter a classificação de Joca, a fórmula seria
=ordem( b2 ; $b$2:$b$8; 0 )
No caso, vai dar 3, ou seja, Joca foi o 3º colocado na competição.
Se colocarmos essa fórmula em C2 e copiarmos para c3:c8 vamos ter a classificação de cada competidor.

Legal ! temos a classificação de cada competidor mas eles ainda estão listados fora de ordem. Como fazer para colocá-los do primeiro para o último colocado com o primeiro no topo ?

Para fazer isso, temos que exibir os competidores em outro local na ordem desejada. Nenhuma fórmula do excel é capaz de mudar o conteúdo de posição. Só macros fazem isso. O que podemos fazer é escolher quais entre os valores possíveis será o exibido numa célula. Isso significa que não dá para exibir a tabela de concorrentes e depois mudar a ordem nela mesma só com fórmulas. Vamos exibir em outro local então.

Sendo assim, imagine que em outro lugar da planilha, vamos colocar a “Classificação” do campeonato. Que tal a partir de F2 ?? De F2 até F8, na ordem de primeiro para último (ou seja, descendente de notas obtidas). Aí usamos a função CORRESP() e índice()
Em F2 vamos colocar 1, em f3 2, f4 3, f5 4 e assim por diante até f8 ter 7, que é a última colocação nesse campeonato.

Qual o concorrente que ficou em 1º lugar ?? Ora, é só buscar na tabela de classificação o 1. Para saber em que célula da lista de concorrentes está o que ficou em 1º lugar, basta localizar na coluna C2:C8 onde está o 1. Isso a gente faz com Corresp() assim e vamos colocar em G2

=corresp( f2 ; $C$2:$c$8 ; 0 )

Como funciona função corresp() ? Ela funciona de forma muito semelhante a função procv() só que ela só retorna o número da linha que correspondeu ao código procurado. No caso o que está em F2 que é a posição no ranking. Nesse exemplo, vai retornar 4. Ou seja, o concorrente que obteve a pontuação máxima está na linha 4 da tabela. Copiamos então essa fórmula para G3:G8 e teremos as posições do primeiro até o último colocado. Mas não queremos a posição !! Queremos o nome do cara ! Aí que entra a função ÍNDICE() que nada mais faz do que pegar o n-ésimo elemento de uma matriz.

EM h2 então colocamos a seguinte fórmula

=índice( $a$2:$a$8 ; G2 )
Copiamos essa fórmula para h3:h8 e teremos os nomes dos concorrentes.

QUer a nota ao lado ? É só usar a função índice() novamente só que com a coluna das notas B2:B8.

Aqui tem uma planilha com as fórmulas já prontas para vocês experimentarem

 
Anúncios

15 comentários sobre “Como classificar uma tabela sem usar macros do Excel

  1. boa tarde!

    fiz a seguinte fórmula para realizar um ranking, =ÍNDICE($A$2:$A$10;CORRESP(MAIOR(ÍNDICE(ORDEM($E$2:$E$10;$E$2:$E$10;1)-LIN($E$2:$E$10)/8^7;);K13);ÍNDICE(ORDEM($E$2:$E$10;$E$2:$E$10;1)-LIN($E$2:$E$10)/8^7;);0))

    mas preciso tbm que ela me retorne com um critério de desempate, que está em outra coluna como posso fazer isso, pois essa apenas tira os erros caso deem numeros iguais

    obrigado!

    • A ideia do critério de desempate, para ser usado numa fórmula de Excel para classificar automaticamente sem macro, é criar uma regra de formaçào de uma relação de ordem.

      Pelo que entendi da fórmula, o primeiro critério de classificação está na coluna E2:E10

      Suponha que o segundo critério de classificação está na coluna D2:D10. Suponha que esse critério de desempate pode ter um valor entre 0 e 999, ou seja, 3 algarismos. … Para usarmos esse critério de desempate usamos E2:E10 multiplicado por 1000 e somamos o D2:D10. Porque multiplicar por 1000 ? Porque o principal critério sendo uma unidade maior vale mais do que 999 unidades de D2:D10

      Assim, se o ítem tem na coluna E o valor 3 na coluna D o valor 4, o resultado seria 3004
      Se outro ítem ten na coluna E o valor 2 e na doluna D o valor 5 (maior do que o D do ítem anterior) , como o E vale 1000 vezes mais, o primeiro item é “Maior”

      Portanto a fórmula ficar $E$2:$e$10 *1000 + $d$2:$d$10

      • boa tarde,

        fiz uma tabela constando os artilheiros do certame.
        como faço para no caso de haver empate em numero de gols, para que a classificação em retorne os nomes corretos, ao inves de dar o retorno de “#n/d”…..

      • Você tem que usar algum critério de desempate, nem que seja o número da linha em que o artilheiro está. Nesse caso, multiplique a quantidade de gols por 100 e soma a linha em que o artilheiro está e use isso para classificar.

  2. boa tarde….. tenho uma planilha onde gero dados de despesas, qual fórmula devo usar para para que eu veja as cinco maiores em outra planilha? lembrando que os dados são flutuantes, ou seja, o item que hoje é o terceiro de repente amanhã nem está entre os cinco maiores …..
    desde já sou grato pela atenção

    • Na planilha que tem a lista das despesas e o total de cada uma delas, adicione uma coluna chamada RANKING. Nela você deve colocar a seguinte fórmul

      =ordem( aquela_despesa ; lista_de_valores_despesa ; 0 )

      Essa fórmula vai dizer em que “posição” a despesa ficou na lista, em que classificação.

      Agora você quer as 5 maiores , basta usar um Corresp(posição ; lista de posições ) para saber em que linha está o elemento correspondente a posição desejada.

      Quer o 3º elemento ? =corresp( 3 ; lista de posições ; 0 )

      Sabendo qual é o elemento, é só usar a função =índice( ) para obter os dados daquela posição.

  3. Cara, me ajudou muito isso, mas tenho uma dúvida: Numa tabela de futebol, quando dois ou mais times empatam em pontos, como colocar os critérios de desempate(vitórias, saldo de gols, gols marcados, etc.

    • No artigo sobre como fazer uma tabela de futebol tem a dica de como fazer o desempate. Deu uma olhada ?

      • Olá amigo, usei as formulas para classificar os resultados da mega, mas quando há dois números com a mesma quantidade sorteada aparece a mensagem #N/D como resolver isso?

      • Isso acontece porque os números ficaram “empatados”. Você precisa criar um critério de desempate. Proponho que seja o próprio número dividido por 100.

        Assim, a classificação do número deve ser somado o próprio número dividido por 100. Nessa coluna de ordem você pode até ocultar as casas decimais.

  4. e nos casos em que duas ou mais pessoas reptem a mesma nota.. por meio destas fórmulas dá erro.. como podemos contornar esse erro?

    • Excelente pergunta ! A forma mais fácil é criar um critério de desempate. Se duas pessoas tem a mesma nota, como desempatá-las ? Um critério é usar o númnero da linha em que a nota está. Esse número seria somado a nota. Por exemplo, Suponha que as notas variam de 0 a 10 em incrementos de 1. Você pode somar a nota o número da linha dividido por 1000. Isso não vai afetar a nota em si. Imagine uma pessoa que tirou nota 9 e cuja nota está na linha 10 da planilha. Outra pessoa tirou a nota 9 mas sua nota está na linha 13. Assim, a “nota” para classificação seria 9,0010 e 9,0013 .. portanto, quem ficou na linha 13 está numa classificação Melhor do que quem ficou na linha 10.

  5. Pingback: O canto do João Eurico (plus) | Como fazer uma tabela de campeonato de futebol – Pontos corridos

Comente aqui ou faça uma pergunta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

w

Conectando a %s