Preciso formatar condicionalmente células com valores diferentes entre as mesmas colunas,seseu valor para uma coluna específica é o mesmo.
Cenário
Minha planilha é um banco de dados de contatos, onde cada linha é um registro de contato, e cada coluna contém um tipo de dado (sobrenome, rua 1, rua 2, cidade, estado, etc) para cada registro de contato.
A planilha é um amálgama das informações de contato mantidas por diferentes escritórios da nossa empresa – combinamos todas as informações de contato na mesma fonte para que possamos concordar com quaisquer discrepâncias e manter todos os nossos contatos no mesmo lugar daqui para frente.
Alguns contatos possuem apenas um registro – como quando apenas um de nossos escritórios tinha dados desse contato, portanto não pode discordar dos dados que temos de qualquer outro escritório; outros contatos possuem vários registros (ou seja, pode haver apenas uma linha para Fred Johnson, mas quatro linhas para Jane Smith). Cada contato precisa ter exatamente as mesmas informações de contato antes que possamos carregá-lo em um banco de dados na nuvem ou criará duplicatas.
Passamos muito tempo tentando concordar dados entre registros da mesma pessoa e agora precisamos descobrir onde existem as discrepâncias restantes.
Concatenei os campos Sobrenome e Nome para que as linhas com o mesmo valor neste campo possam ser comparadas entre si - o objetivo é destacar células com dados diferentes na mesma coluna quando comparadas com outros registros com o mesmo valor em a coluna LastFirst (se houver).
Como posso fazer isso?
Até agora, estou ciente de que a função Localizar Especial pode destacar células quando comparadas com uma linha ou coluna designada, mas preciso que as comparações sejam feitas com linhas que tenham o mesmo valor para uma coluna específica.
Tenho acesso ao Excel 2010 e 2013 para realizar esta função.
Obrigado!
Responder1
Pode haver soluções mais simples, mas você não precisa concatenar nomes e sobrenomes para criar uma coluna "Nome completo".
Prévia da nossa produção
(as células brancas à direita mostram qual saída nossa fórmula produzirá para conduzir a formatação condicional):
Aqui está nossa fórmula
=COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2)
-COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2,C$1:C$100,IF(C2="","",C2))
Lembre-se de ajustar para se adequar ao número de linhas que você tem em seu conjunto de dados e à posição das colunas de nome e sobrenome, se não estiverem em A e B.
Também presumi que seus primeiros dados sem nome começam na célula C2 - ou seja, este é o bit de dados no canto superior esquerdo que você deseja verificar em busca de incompatibilidades. Edite os "C" nesta fórmula se necessário.
Aplicando a formatação condicional
Copie a fórmula acima (depois de fazer as alterações necessárias) e clique na célula superior esquerda que deseja verificar se há incompatibilidades (vou me referir a "C2" a partir daqui). Agora, selecione o Conditional Formattingbotão na faixa de opções e escolha New Rule.
Selecione Use a formula to determine which cells to formate cole sua fórmula na caixa de texto (certifique-se de que não haja espaços - se você copiou de cima, precisará retirar alguns). Agora você pode clicar Formatpara definir como deseja destacar as incompatibilidades. Optei por um preenchimento vermelho (berrante!). Clique OKaté voltar à planilha.
Se não houver incompatibilidade na célula C2, pode parecer que nada aconteceu, mas isso ocorre porque ainda precisamos aplicar a regra a todo o seu conjunto de dados. Mantendo C2 ainda selecionado, clique Manage Rulesno Conditional Formattingmenu da faixa de opções.
Agora você pode selecionar todo o intervalo que deseja verificar se há incompatibilidades. Na Applies tocaixa, clique e arraste para selecionar tudo que deseja comparar (ou se você tiver muitas linhas, para acelerar basta digitar a referência da célula =$C$2:$Z$999)
Clique em OK e pronto!
Como funciona
Esta fórmula é usada COUNTIFS()para contar quantas linhas existem para aquela pessoa:
=COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2)
Em seguida, calculamos quantas linhas têm o nome dessa pessoa E o valor na coluna que você está verificando. Se todas as linhas forem idênticas, esse número deverá corresponder exatamente ao primeiro COUNTIFS().
COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2,C$1:C$100,IF(C2="","",C2))
Se subtrairmos o último do primeiro, e todas as linhas corresponderem, a fórmula gerará 0 e não fará nenhuma formatação condicional. Porém, se algo for diferente, a saída será 1 ou superior, acionando a formatação condicional.
Observação
Tive que agrupar os COUNTIFS()critérios finais em uma IF()declaração para lidar com espaços em branco - COUNTIFSnão gosto muito de espaços em branco (parece não ter certeza se devo contá-los como 0 ou "". Estranho).
Download do arquivo
Este documento de exemplo também édisponível para download.