Destacamos células no Excel para que possamos identificar facilmente as áreas de foco nos dados. A formatação de células no Excel também é feita para visualizar os valores dos dados com base na cor e no estilo. Por exemplo, você pode querer se concentrar nos 3 menores valores em seus dados. É difícil fazer em um grande volume de dados, mas se você pudesse dizer ao Excel para destacar os 3 valores mais baixos, então seria muito fácil. Felizmente, podemos fazer isso usando a opção de formatação condicional no Excel.
É fácil destacar apenas os 3 menores valores no Excel, mas fica um pouco complicado quando adicionamos critérios. Neste artigo, aprenderemos como destacar os 3 menores valores com uma condição.
Fórmula genérica em formatação condicional
= AND (verificar célula = critérios, célula de valor<= PEQUENO (SE (intervalo de critérios = critérios, intervalo de valores),3))
Verifique a célula: É a célula que você deseja verificar se atende ao primeiro critério (a categoria).
Critério: Os critérios que você deseja filtrar.
Célula de valor: É a célula que contém um valor numérico. Vamos compará-lo com o 3º menor valor em sua categoria, no faixa de valor.
Intervalo de critérios: É o intervalo absoluto que contém os critérios.
Faixa de valor: O intervalo que contém todos os valores.
Vamos ver um exemplo para deixar as coisas claras.
Exemplo: destaque as três últimas vendas realizadas pelo Departamento 1
Aqui, tenho um arquivo de trabalho de amostra que contém vendas feitas por departamentos diferentes em meses diferentes.
Quero destacar as três últimas vendas realizadas pelo Departamento 1.
Vamos identificar as variáveis que temos aqui.
Verifique a célula: $ B2 (queremos verificar se B2 contém "concessionário 1". Como não queremos que a coluna mude, usamos o sinal $. Isso realça toda a linha.)
Critério: "Departamento 1"
Célula de valor: $ C2
Intervalo de critérios: $ B $ 2: $ B $ 12
Faixa de valor: $ C $ 2: $ C $ 12
Agora que sabemos todos os argumentos que precisamos passar para a fórmula, é hora de implementar a fórmula genérica acima no conjunto de dados.
- Selecione os dados. Ir para casa? formatação condicional ? Nova regra
- Selecione a opção "Usar uma fórmula para determinar qual célula formatar".
- Na caixa de texto abaixo, escreva esta fórmula
= AND ($ B2 = "Concessionário 1", $ C2 <= PEQUENO (SE ($ B $ 2: $ B $ 12 = "Concessionário 1", $ C $ 2: $ C $ 12), 3))
Clique no botão OK. E está feito. As três últimas vendas ou, digamos, as três menores vendas do departamento 1 são destacadas.
Como funciona?
Vamos analisar a fórmula por dentro.
Esta fórmula é uma fórmula de matriz, mas CTRL + SHIFT + ENTER não é necessário na formatação condicional.
Uma vez que temos duas condições, a primeira é a Concessionária 1 e a segunda são os valores menores ou iguais ao terceiro menor valor na Concessionária 1. Ambos precisam ser VERDADEIROS. Para isso, usamos a função AND.
$ B2 = "Concessionário 1"
O primeiro critério é fácil de verificar. Simplesmente colocamos esta declaração $ B2 = "Concessionário 1". Será TRUE se a célula atual B2 contém Concessionária 1.
$ C2 <=PEQUENA(E SE($ B $ 2: $ B $ 12 = "Concessionário 1", $ C $ 2: $ C $ 12), 3)
Para o segundo critério, precisamos filtrar Concessionária 1 e encontrar o terceiro menor valor nela. Em seguida, compare a célula de critérios com o terceiro menor valor. Isso é feito usando a instrução $ C2 <= PEQUENO (SE ($ B $ 2: $ B $ 12 = "Concessionária 1", $ C $ 2: $ C $ 12), 3)
Quando o avaliamos, esta fórmula resolverá desta forma:
Verificamos a faixa $ B $ 2: $ B $ 12 e obtemos células que contêm "Concessionária 1" usando a declaração $ B $ 2: $ B $ 12 = "Concessionária 1". Teremos uma matriz de VERDADEIRO e FALSO.
$ C2 <= PEQUENO (SE ({VERDADEIRO; FALSO; VERDADEIRO; FALSO; FALSO; FALSO; FALSO; VERDADEIRO; FALSO; FALSO; VERDADEIRO}, $ C $ 2: $ C $ 12), 3)
Para cada TRUE, o valor será retornado da faixa de valores $ C $ 2: $ C $ 12 e teremos outro array.
$ C2 <= PEQUENO ({12; FALSO; 25; FALSO; FALSO; FALSO; FALSO; 18; FALSO; FALSO; 14}, 3)
Agora a função SMALL retornará o terceiro menor valor deste array. (FLASE será ignorado).
Agora temos:
$ C2 <= 18
Finalmente, a fórmula é
= AND ($ B2 = "Concessionário 1", $ C2 <= 18)
Agora ele verifica se B2 contém Concessionária 1. É verdade. Portanto, é VERDADEIRO
= E (VERDADEIRO, $ C2 <= 18)
Em seguida, verifica se C2 é menor ou igual a 18 (a terceira menor venda na concessionária 1). É VERDADEIRO (12).
= E (VERDADEIRO, VERDADEIRO)
Uma vez que ambas as instruções são TRUE, a fórmula retorna TRUE e temos nossa primeira LINHA destacada com cores. O mesmo acontece em cada linha.
Fewww !!! Foi difícil explicar. Espero ter sido claro o suficiente para fazer você entender o método. Na prática, é fácil.
Aqui usamos critérios codificados, mas também podemos fornecer referência de célula. Isso tornará a fórmula mais flexível.
Aqui para Critério Eu usei a referência de célula de E1. Qualquer que seja a concessionária que você tenha na E1, os três menores valores dessa concessionária serão destacados.
A fórmula na formatação condicional é:
= E ($ B2 = $ E $ 1, $ C2 <= PEQUENO (SE ($ B $ 2: $ B $ 12 = $ E $ 1, $ C $ 2: $ C $ 12), 3))
Então, sim, é assim que você pode destacar o valor dos três piores com uma condição no Excel. Espero que o artigo tenha sido útil. Se você tiver qualquer dúvida, use a seção de comentários abaixo. Você pode fazer perguntas relacionadas a este artigo ou a qualquer outro artigo relacionado ao Excel. Teremos o maior prazer em ajudá-lo.
Formatação condicional baseada em outro valor de célula
IF e formatação condicional
Formatação condicional com fórmula 2016
Formatação condicional usando VBA no Microsoft Excel
Destacar células que contêm texto específico
Como somar várias colunas com condição
Artigos populares:
50 atalhos do Excel para aumentar sua produtividade
A função VLOOKUP no Excel
CONT.SE no Excel 2016
Como usar a função SUMIF no Excel