Se você possui um intervalo de valores em sua planilha e deseja que o Excel formate/destaque os valores que se encontram acima e/ou abaixo da média, pode fazer uso de regras de formatação condicional.
Neste artigo vamos aprender dois métodos dentro da formatação condicional para destacar valores acima ou abaixo da média, além de uma dica muito importante para lidar com células vazias.
Versão do Excel utilizada na aula: Microsoft Excel 365 [versão 2303]
Versão do Excel utilizada neste tutorial: Microsoft Excel 365 [versão 2303]
Usando regras predefinidas do Excel
O Excel tem uma categoria específica para lidar com a média de valores de um intervalo.
Considere a planilha a seguir. Nossa intenção é destacar, na coluna B, todos os valores que estão acima da média e também abaixo dela. Obviamente, a média a ser considerada é a média aritmética desse mesmo intervalo.
Selecione todos os valores dessa coluna e acesse a ferramenta Formatação Condicional, presente no grupo Estilos da guia Página Inicial. Clique em Nova Regra.
Entre as categorias, selecione Formatar apenas valores acima ou abaixo da média. Na descrição da regra, opte por igual ou acima (no caso de formatação de valores que sejam iguais ou maiores que a média do intervalo). Em seguida, clique no botão Formatar para definir o tipo de formatação a ser aplicada nas células.
Em nosso exemplo, aplicamos um preenchimento azul às células cujos valores são iguais ou maiores que a média do intervalo selecionado previamente.
Para a aplicação de uma regra que destaque os valores menores que a média, por exemplo, basta alterar o argumento de comparação.
Usando fórmulas
Uma outra forma de realizar esse mesmo procedimento é através do uso de fórmulas em uma regra de formatação. Neste caso, as possibilidades de teste e personalização são ainda maiores que nas regras predefinidas do programa.
Para melhor entendimento da lógica, selecione apenas a primeira célula do intervalo de valores a serem formatados.
Em seguida, clique em Formatação Condicional e novamente em Nova Regra. No entanto, selecione agora a categoria Usar uma fórmula para determinar quais células devem ser formatadas.
No campo de elaboração da fórmula, vamos inserir a seguinte fórmula (você também pode clicar diretamente nas células ou colunas para selecioná-las):
=$B$2>=MÉDIA($B:$B)
$B$2 refere-se à célula B2 (por enquanto travada com referência absoluta, caso você tenha clicado diretamente na célula). Essa é a célula que deve ser testada para receber a formatação. Em seguida, o comparador “>=” indica que o valor da célula B2 deve ser maior ou igual ao próximo termo. O próximo termo é justamente a função MÉDIA, que, neste caso, está analisando a média de todos os valores contidos na coluna B ($B:$B).
De forma resumida, a formatação só vai acontecer se o conteúdo de B2 for maior ou igual à média dos valores contidos na coluna B.
No entanto, aqui precisamos de uma adaptação da fórmula, porque o Excel não pode analisar apenas B2 para aplicar a formatação; cada célula posterior da coluna B também deve ser analisada, por isso precisamos retirar a referência absoluta da linha 2 ($B2). Sendo assim, nossa fórmula final seria:
=$B2>=MÉDIA($B:$B)
Como selecionamos apenas a primeira célula no início da elaboração da regra, o Excel analisa apenas a primeira célula, o que não é nosso objetivo. Com ela selecionada, clique novamente em Formatação Condicional e depois em Gerenciar Regras.
Clique na setinha de referência do campo Aplica-se a e selecione a coluna B completa (clicando sobre o título da coluna B).
Agora o Excel analisa célula por célula da coluna B para aplicar a formatação condicional.
É comum que o Excel formate valores em texto usando a lógica matemática, como aconteceu com nosso cabeçalho. Para corrigir esse problema, clique sobre a célula que contém o texto e, usando o menu de Formatação Condicional, clique em Limpar Regras e depois em Limpar Regras das Células Selecionadas.
As regras serão removidas das células selecionadas.
Para destacar os valores abaixo da média, devemos apenas alterar a lógica da fórmula, que agora deve ser:
=$B2<MÉDIA($B:$B)
A única mudança encontra-se no comparador “<” (menor).
Corrigindo células vazias
O impasse da fórmula que testa células com valores menores que a média é que, ao aplicar a abrangência para a coluna toda, as células vazias também serão analisadas, de modo que serão formatadas como valores abaixo da média, já que valores nulos são, de fato, valores abaixo da média do intervalo.
Para corrigir esse pequeno problema, podemos, dentre outras alternativas, adaptar nossa fórmula para informar ao Excel que as células em branco devem ser ignoradas. Uma forma de fazer isso é usando a função lógica E, que retorna VERDADEIRO apenas se TODAS as condições nela inseridas forem verdadeiras. Dessa forma, uma célula com valor abaixo da média só poderia ser formatada se ela comportasse realmente um valor abaixo da média E não estivesse vazia (já que o vazio, para o Excel, também é um valor).
Assim, sendo, nossa fórmula ficaria desta maneira:
=E($B2<MÉDIA($B:$B);$B2<>””)
A primeira condição da função E é justamente o teste que já havíamos concebido: verificar se o valor da célula é menor do que a média do intervalo de células. A segunda condição testa se a célula da coluna B é diferente (<>) de vazio (“”). Se as duas condições forem satisfeitas, a formatação é aplicada.
- Ao usar uma coluna inteira para o cálculo da média aritmética, cuidado para que não haja nenhum outro dado numérico “esparso” nessa coluna, pois o Excel também o levará em consideração no momento do cálculo.
- Se você deseja calcular a quantidade de valores de um intervalo que se encontram acima ou abaixo da média, confira este artigo.
- Você pode expandir a abrangência de uma regra elaborada por fórmulas, formatando uma linha completa, por exemplo. Para aprender como se faz, confira este artigo.
