Se você possui um ranking no Excel e deseja formatar os primeiros e/ou últimos lugares, saiba que isso é possível com regras bem intuitivas de formatação condicional.
Neste artigo vamos aprender a criar regras de formatação condicional para destacar as primeiras posições de um ranking (com cores iguais ou alternadas) e também as últimas, de forma totalmente automática, mesmo que sejam incluídos novos valores no futuro.
Versão do Excel utilizada na aula: Microsoft Excel 365 [versão 2211]
Versão do Excel utilizada neste tutorial: Microsoft Excel 365 [versão 2211]
Criando um ranking com a função ORDEM
Antes de necessariamente criar uma formatação condicional para destacar o posicionamento dos dados de acordo com um ranking, vamos criar nosso ranking. Dentre algumas formas possíveis, uma delas é usando a função ORDEM.
Observe a planilha abaixo. Cada participante recebeu uma quantidade de pontos, e a ordenação deles em nosso ranking seguirá o conceito mais comum: quanto maior o valor, mais alta a posição na ordenação.
Em C1, vamos inserir a função ORDEM.EQ usando como parâmetro a coluna B completa, de modo que, ao inserirmos novos dados, eles também sejam automaticamente levados em consideração.
=ORDEM.EQ(B2;B:B)
O propósito deste artigo é mostrar as regras de formatação condicional, não esmiuçar o uso da função de ordenamento. Caso precise de mais detalhes sobre a função ORDEM.EQ e suas variações de uso, confira este artigo.
Ao aplicar autopreenchimento, temos o ranking completo, com a posição de todos os participantes.
Formatando as 3 primeiras posições com a mesma cor
Como primeiro exemplo, vamos formatar as três primeiras posições do ranking usando a mesma cor, ou seja, podemos fazer isso através de uma única regra de formatação condicional. Para facilitar o aprendizado, vamos começar com uma única célula do intervalo de posições e, posteriormente, aplicaremos uma abrangência para toda a coluna e também para destacar toda a linha correspondente.
Em primeiro lugar, selecione a célula C2, onde se inicia nosso ranking. Em seguida, no grupo Estilos da guia Página Inicial, clique em Formatação Condicional e depois em Nova Regra.
Apesar de o Excel dispor de algumas regras predefinidas que poderiam nos satisfazer, vamos usar a última opção (fórmula), porque ela nos permite personalizar ainda mais nossos critérios.
No campo de fórmulas, digite:
=OU($C2=1;$C2=2;$C2=3)
A lógica não é difícil de entender. O Excel analisará uma célula de cada vez em nossa coluna C. Para que ele formate essa célula, ela precisa conter um desses três números: 1, 2 ou 3. A função lógica que nos permite analisar várias alternativas para uma mesma célula é a função OU, cujo conhecimento você pode aprofundar consultando este artigo.
De modo resumido, se C2 for igual a 1 OU a 2 OU a 3, a função OU retornará o valor lógico VERDADEIRO, de modo que, assim, nossa condição será satisfeita e nossa formatação condicional será aplicada.
É necessário travar a coluna C ($C) porque ela é a única coluna que deve ser analisada, já que é a única que contém o ordenamento de nossas posições. No entanto, as linhas não podem receber o travamento porque o Excel precisará analisá-las uma a uma.
Por fim, basta clicar no botão Formatar para definir a formatação para essa regra.
Em nosso exemplo, foi usado um preenchimento de cor amarelo, mas você definir quantas formatações desejar para uma única regra, envolvendo cor de preenchimento, formatação de fonte, bordas, etc.
Note que, se alterarmos a pontuação do primeiro participante para a maior do intervalo, o Excel formatará a célula da coluna C, porque ela satisfaz nossa condição: número 1. Entretanto, as células com os demais valores (2 e 3) não foram formatadas.
Isso aconteceu porque, no início, selecionamos apenas a célula C2 para inserir uma regra. Agora devemos expandir essa regra para as demais células da coluna. Para tal, clique na célula C2 (que é a única que contém nossa regra) e, em Formatação Condicional, selecione a opção Gerenciar Regras.
Note que, ao lado da regra, podemos alterar o campo de aplicação da mesma. Basta clicar na setinha ao lado desse campo e selecionar o intervalo de abrangência. Você pode selecionar um intervalo delimitado ou mesmo a coluna toda. Em nosso caso, vamos selecionar a coluna C inteira (C:C).
Em seguida, é só confirmar as alterações e a regra agora abrange toda a coluna C.
Podemos também abranger as demais colunas, de modo que toda a linha fique destacada. Para tal, basta alterar o intervalo de aplicação para A:C, por exemplo.
Formatando as 3 primeiras posições com cores distintas
Agora vamos aplicar uma formatação diferente para cada uma das três posições. Esse tipo de formatação é ainda mais simples, mas aqui vamos precisar de três regras, já que cada uma é atrelada a uma formatação diferenciada.
Usando a mesma planilha anterior (e agora de forma mais sucinta, já que você compreendeu os passos para o procedimento), a fórmula usada para a regra de formatação irá testar unicamente o conteúdo de uma célula e verificar se ele é igual a 1, a 2 ou a 3, cada qual em uma condição. Vamos começar com a posição 1.
Basta seguir os mesmos procedimentos: definir a formatação e depois realizar a expansão da regra, exatamente como fizemos anteriormente. As duas únicas coisas que mudam entre uma regra e outra são o teste numérico da célula (para as posições 2 e 3) e a formatação de cada uma.
Veja como ficaria nossa lista de regras para as três posições, já com todas as abrangências aplicadas para todas as colunas, de modo a destacar a linha toda.
Se você tem versões mais recentes do Excel, pode selecionar uma única regra criada e usar o botão Regra Duplicada, presente no gerenciador de regras. Essa opção realiza uma cópia da regra selecionada. Em seguida, basta dar um duplo clique na nova regra gerada para editá-la (a fórmula e a formatação).
Desse modo, as três primeiras posições serão destacadas com cores diferentes em nossa planilha.
Formatando os 3 últimos lugares
Para destacar os três últimos lugares, há duas alternativas possíveis. No caso de um ranking com uma quantidade fixa de lugares, podemos usar exatamente as regras anteriores, substituindo os números 1, 2 e 3 pelos três últimos da sequência (seguindo o mesmo exemplo, 17, 16 e 15).
No entanto, se a intenção é adicionar mais participantes, naturalmente os últimos lugares serão alterados, de modo que os três últimos números precisam ser dinâmicos, acompanhando automaticamente a quantidade de participantes. E isso é possível através da função CONT.NÚM, que conta a quantidade de células com valores numéricos em um intervalo.
Vamos observar a lógica envolvida nesse cálculo. Ao contar a quantidade de valores contidos na coluna B, temos justamente a quantidade de participantes pontuados. Nesse caso, a função CONT.NÚM é mais útil que a função CONT.VALORES, porque conta somente números, ignorando os títulos de nossas tabelas. Se cada participante tem uma pontuação, é válido pensar que cada pontuação é atrelada a um participante, portanto a quantidade de células com pontos é também a quantidade de participantes.
=CONT.NÚM(B:B)
Ao usar esta função na planilha anterior, o Excel nos retornaria o número 17, pois temos 17 células com valor numérico nessa coluna.
Agora fica mais fácil entender. Toda vez que adicionarmos uma nova pontuação, teremos uma célula a mais, portanto um número a mais na contagem. Esse número reflete justamente o último lugar do ranking: se a quantidade de participantes é 17, o último lugar sempre será o 17°; se os participantes são 18, o último lugar sempre será o 18°. Sendo assim, temos agora um número dinâmico que pode ser usado como argumento para nossa regra de formatação:
$C2=CONT.NÚM($B:$B)
Da mesma forma, o penúltimo lugar nada mais é que a subtração de 1 do último lugar. O antepenúltimo é a subtração de 2 do último lugar. Sendo assim, teríamos:
$C2=CONT.NÚM($B:$B)-1
$C2=CONT.NÚM($B:$B)-2
Ao criar uma regra para destacar os três últimos lugares usando uma mesma formatação, poderíamos fazer uso da função OU utilizando esses valores dinâmicos como referência.
=OU($C1=CONT.NÚM($B:$B);$C1=CONT.NÚM($B:$B)-1;$C1=CONT.NÚM($B:$B)-2)
A função retorna o valor verdadeiro se a célula da coluna C contiver o número total de participantes (o último lugar), ou esse número menos um, ou esse número menos dois.
Dessa forma, os últimos lugares são alterados automaticamente com a inserção de novos dados.
- Datas e horas também são considerados valores numéricos pelo Excel, porque, de fato, são números formatados para uma exibição diferente. Portanto, também são são contados pela função CONT.NÚM.
- Se você optar por analisar a coluna inteira com a formatação de últimos lugares (fazendo uso da CONT.NÚM), tenha em mente que essa coluna deve ser usada exclusivamente para isso. Qualquer dado numérico inserido em qualquer célula dela, ainda que não tenha relação com os dados abordados, será contabilizado.
- Como nossos valores agora recebem uma formatação diferenciada, é possível classificá-los usando as ferramentas de filtros.
