Formatação condicional no Excel – em detalhes com exemplos

Se você precisar apresentar informações visualmente, é muito conveniente fazer isso usando a formatação condicional. O usuário que usa este serviço economiza uma enorme quantidade de energia e tempo. Para obter as informações necessárias, basta uma visualização rápida do arquivo.

Como fazer formatação condicional no Excel

Você pode encontrar “Formatação Condicional” na primeira guia da faixa de opções, indo para a seção “Estilos”. 

Em seguida, você precisa encontrar o ícone de seta um pouco para a direita com o olho, mova o cursor para ele. Em seguida, as configurações serão abertas, onde você poderá configurar com flexibilidade todos os parâmetros necessários. 

Em seguida, você precisa selecionar o operador apropriado para comparar a variável desejada com um número. Existem quatro operadores de comparação – maior que, menor que, igual a e entre. Eles estão listados no menu de regras. 

Em seguida, especificamos uma série de números no intervalo A1:A11.

Vamos formatar um conjunto de dados adequado. Em seguida, abrimos o menu de configurações “Formatação Condicional” e definimos os critérios de seleção de dados necessários. No caso de nós, por exemplo, selecionaremos o critério “Mais”. 

Uma janela com um conjunto de opções será aberta. Na parte esquerda da janela há um campo no qual você precisa especificar o número 15. Na parte direita, é indicado o método para destacar as informações, desde que atenda ao critério especificado anteriormente. O resultado será exibido imediatamente.

Em seguida, conclua a configuração com o botão OK.

Para que serve a formatação condicional?

Até certo ponto, podemos dizer que é uma ferramenta, depois de aprender que trabalhar com Excel nunca mais será o mesmo. Isso porque facilita muito a vida. Em vez de definir manualmente a formatação de uma célula que corresponde a uma determinada condição a cada vez e também tentar verificar você mesmo a conformidade com esse critério, você só precisa definir as configurações uma vez e o Excel fará tudo sozinho.

Por exemplo, você pode fazer com que todas as células que contenham um número maior que 100 fiquem vermelhas. Ou determine quantos dias faltam para o próximo pagamento e, em seguida, pinte de verde as células em que o prazo ainda não está longe o suficiente. 

Vamos dar um exemplo.

Esta tabela mostra o estoque que está em estoque. Tem colunas. como produto, vendas médias (medidas em unidades por semana), estoque e quantas semanas restam desse produto.

Além disso, a tarefa do gerente de compras é determinar essas posições, cuja reposição é necessária. Para fazer isso, você precisa olhar para a quarta coluna da esquerda, que registra o estoque de mercadorias por semana.

Suponha que o critério para determinar a causa do pânico seja inferior a 3 semanas de estoque. Isso indica que precisamos preparar um pedido. Se o estoque de mercadorias for inferior a duas semanas, isso indica a necessidade de fazer um pedido com urgência. Se a tabela contiver um grande número de posições, verificar manualmente quantas semanas restam é bastante problemático. Mesmo se você usar a pesquisa. E agora vamos ver como é a tabela, que destaca os bens escassos em vermelho.

E, de fato, é muito mais fácil de navegar. 

É verdade que este exemplo é educacional, o que é um pouco simplificado em comparação com a imagem real. E os segundos e minutos economizados com o uso regular de tais tabelas se transformam em horas. Agora basta olhar para a tabela para entender quais bens estão em falta, e não passar horas analisando cada célula (se houver milhares desses itens de commodities).

Se houver mercadorias "amarelas", você precisará começar a comprá-las. Se a posição correspondente for vermelha, você precisará fazê-lo imediatamente. 

Por valor de outra célula

Agora vamos dar uma olhada no seguinte exemplo prático.

Suponha que tenhamos uma tabela assim e nos deparamos com a tarefa de destacar linhas contendo determinados valores. 

Portanto, se o projeto ainda está sendo executado (ou seja, ainda não foi concluído, portanto está marcado com a letra “P”), precisamos deixar seu fundo vermelho. Os projetos concluídos são marcados em verde.

Nossa sequência de ações nesta situação será a seguinte:

1. Selecione um intervalo de valores. 
2. Clique no botão “Formatação Condicional” – “Criar Regra”.
3. Observe que, no nosso caso, você precisa aplicar a fórmula na forma de uma regra. A seguir, usamos a função IFpara destacar as linhas correspondentes. 

Em seguida, preencha as linhas conforme mostrado nesta imagem.

Importante! Strings devem ser referenciadas absolutamente. Se nos referirmos a uma célula, nesse caso ela é mista (com fixação de coluna).

Da mesma forma, uma regra é criada para os projetos de trabalho que não foram concluídos até o momento. 

Este é o nosso critério.

E, finalmente, temos esse banco de dados como resultado.

Múltiplas Condições

Para maior clareza, vamos pegar o intervalo A1:A11 da tabela para demonstrar como, em um exemplo prático real, você pode usar várias condições para determinar a formatação da célula.

As próprias condições são as seguintes: se o número na célula exceder o valor 6, ele será destacado em vermelho. Se for verde, então a cor é verde. E, por fim, os maiores números, mais de 20, serão destacados em amarelo. 

Existem várias maneiras de como a formatação condicional é realizada de acordo com várias regras.

1. Método 1. Selecionamos nosso intervalo, após o qual, no menu de configurações de formatação condicional, selecionamos uma regra de seleção de célula como "Mais". O número 6 é escrito à esquerda e a formatação é definida à direita. No nosso caso, o preenchimento deve ser vermelho. Depois disso, o ciclo é repetido duas vezes, mas outros parâmetros já estão definidos – mais de 10 e cor verde e mais de 20 e cor amarela, respectivamente. Você obterá tal resultado.
   

   

2. Método 2. Vá para o menu principal de configurações da ferramenta Excel “Formatação Condicional”. Lá encontramos o menu “Criar regra” e clique com o botão esquerdo sobre este item.
   

   

    Depois disso, em seguida, selecione o item “Usar fórmula …” (destacado na figura com uma moldura vermelha) e defina a primeira condição. Após isso, clique em OK. E então o ciclo é repetido para as condições subsequentes da mesma forma descrita acima, mas a fórmula é usada.

   

É importante levar isso em consideração. Em nosso exemplo, algumas células correspondem a vários critérios ao mesmo tempo. O Excel resolve esse conflito da seguinte forma: a regra acima é aplicada primeiro. 

E aqui está uma captura de tela para melhor compreensão.

Por exemplo, temos o número 24. Ele atende as três condições ao mesmo tempo. Nesse caso, haverá um preenchimento que atenda à primeira condição, apesar de cair mais na terceira. Portanto, se for importante que os números acima de 20 sejam preenchidos com amarelo, a terceira condição deve ser colocada em primeiro lugar. 

Formatação de data condicional

Agora vamos ver como a formatação condicional funciona com datas. Temos uma gama tão legal.

Nesse caso, você precisa definir uma regra de formatação condicional, como “Data”.

Depois disso, uma caixa de diálogo é aberta com todo um conjunto de condições. Você pode conhecê-los em detalhes nesta captura de tela (eles estão listados no lado esquerdo da tela na forma de uma lista).

Nós apenas temos que escolher o apropriado e clicar no botão “OK”.

Vemos que as datas que se referem à última semana no momento em que esse intervalo foi traçado foram destacadas em vermelho. 

Usando fórmulas

O conjunto de regras de formatação condicional padrão é bastante grande. Mas as situações são diferentes e a lista padrão pode não ser suficiente. Nesse caso, você pode criar sua própria regra de formatação usando uma fórmula. 

Para fazer isso, clique no botão “Criar regra” no menu de formatação condicional e selecione o item exibido na captura de tela.

Linhas (por valor da célula)

Suponha que precisamos destacar a linha que contém uma célula com um determinado valor. Nesse caso, precisamos executar a mesma sequência de ações acima, mas para strings. Ou seja, você pode escolher a qual intervalo a formatação condicional deve ser aplicada. 

Como criar uma regra

Para criar uma regra, você deve usar a opção apropriada na seção “Formatação Condicional”. Vejamos uma sequência específica de ações. 

Primeiro você precisa encontrar o item “Formatação Condicional” na faixa de opções na guia “Início”. Ele pode ser reconhecido pelas células multicoloridas características de tamanhos diferentes com uma imagem ao lado com um sinal de igual riscado. 

Há um botão "Criar regra". Você também pode clicar no botão “Gerenciar regras”, após o qual uma caixa de diálogo será aberta, através da qual você também poderá criar uma regra clicando no botão correspondente no canto superior esquerdo. 

Você também pode ver nesta janela as regras que já foram aplicadas.

Regras de seleção de células

Ok, falamos muito sobre como criar regras, mas o que é isso? A regra de seleção de células refere-se às características que são levadas em consideração pelo programa para decidir como formatar as células que se encaixam nelas. Por exemplo, as condições podem ser maiores que um certo número, menores que um certo número, uma fórmula e assim por diante. Há todo um conjunto deles. Você pode se familiarizar com eles e praticar seu uso “na caixa de areia”. 

Formatando todas as células com base em seus valores

Uma pessoa pode definir independentemente o intervalo: uma linha inteira, todo o documento ou uma única célula. O programa só pode se concentrar no valor de uma célula. Isso adiciona flexibilidade ao processo. 

Formate apenas células únicas ou duplicadas

Há um grande número de regras diretamente conectadas ao programa. Você pode se familiarizar com eles no menu “Formatação Condicional”. Em particular, para que a formatação se aplique exclusivamente a valores duplicados ou apenas a valores únicos, existe uma opção especial – “Valores duplicados”.

Se você selecionar este item, uma janela com configurações será aberta, onde você também poderá escolher a opção oposta – selecione apenas valores únicos. 

Valores de formato na faixa abaixo e acima da média

Para formatar valores abaixo ou acima da média, há também uma opção especial no mesmo menu. Mas você precisa selecionar outro submenu – “Regras para selecionar o primeiro e o último valor”. 

Formatando apenas o primeiro e o último valores

No mesmo submenu, é possível destacar apenas o primeiro e o último valor com uma cor especial, fonte e outros métodos de formatação. De acordo com a norma, existe a opção de selecionar os dez primeiros e últimos dez elementos, bem como 10% do número total de células incluídas neste intervalo. Mas o usuário pode determinar independentemente quantas células ele precisa selecionar.

Formatando apenas células com conteúdo específico

Para formatar apenas células com conteúdo específico, você deve selecionar a regra de formatação Igual a ou Texto Contém. A diferença entre eles é que, no primeiro caso, a string deve corresponder ao critério completamente e, no segundo, apenas parcialmente. 

Como você pode ver, a formatação condicional é um recurso multifuncional do programa Excel que oferece um grande número de vantagens para quem o domina. Sabemos que, à primeira vista, tudo isso pode parecer complicado. Mas, na verdade, as mãos são atraídas para ele assim que aparece para destacar um determinado trecho de texto em vermelho (ou formatá-lo de qualquer outra forma), com base em seu conteúdo ou de acordo com outro critério. Esta função está incluída no conjunto básico de conhecimentos do Excel, sem ela, mesmo o trabalho amador com planilhas e bancos de dados é impossível.