Ontem à noite, no programa de rádio Computer America de Craig Crossman, Joe de Boston tinha uma pergunta:
Tenho uma coluna de números de faturas. Como posso usar o Excel para marcar as duplicatas?
Eu sugeri usar formatos condicionais e a fórmula COUNTIF. Aqui estão os detalhes sobre como fazer isso funcionar.
Queremos configurar a formatação condicional para todo o intervalo, mas é mais fácil configurar um formato condicional para a primeira célula no intervalo e, em seguida, copiar esse formato condicional. Em nosso caso, a célula A1 tem um cabeçalho de número da fatura, então selecionarei a célula A2 e, no menu, selecione Formatar> Formatação condicional. A caixa de diálogo Formatação condicional começa com o menu suspenso inicial dizendo "O valor da célula é". Se você tocar na seta ao lado dela, poderá escolher "Fórmula É".
Depois de selecionar "A fórmula é", a caixa de diálogo muda de aparência. Em vez de caixas para "Entre x e y", agora há uma única caixa de fórmula. Esta caixa de fórmula é incrivelmente poderosa. Você pode digitar qualquer fórmula que imaginar, desde que essa fórmula seja avaliada como VERDADEIRA ou FALSA.
Em nosso caso, precisamos usar uma fórmula CONT.SE. A fórmula para digitar na caixa é
=COUNTIF(A:A,A2)>1
Em inglês, isso diz: "olhe por todo o intervalo da coluna A. Conte quantas células nesse intervalo têm o mesmo valor que o que está em A2. (É realmente importante que o" A2 "na fórmula esteja apontando para o célula atual - a célula na qual você está definindo a formatação condicional. Portanto, se seus dados estiverem na coluna E e você estiver definindo a primeira formatação condicional em E5, a fórmula será =COUNTIF(E:E,E5)>0). Em seguida, comparamos para ver se isso conta é> 1. Idealmente, sem duplicatas, a contagem sempre será 1 - porque a célula A2 está no intervalo - devemos encontrar exatamente uma célula na coluna A que contém o mesmo valor de A2.
Clique no botão Formatar…
Agora é hora de selecionar um formato desagradável. Existem três guias na parte superior desta caixa de diálogo Formatar células. A guia Fonte geralmente é a primeira, então você pode selecionar uma fonte vermelha e negrito, mas eu gosto de algo mais desagradável. Geralmente, clico na guia Padrões e escolho vermelho ou amarelo brilhante. Escolha a cor e clique em OK para fechar a caixa de diálogo Formatar células.
Você verá o formato selecionado na caixa "Visualização do formato a ser usado". Clique em OK para fechar a caixa de diálogo Formatação condicional …
… E nada acontece. Uau. Se esta é a primeira vez que você configura a formatação condicional, seria muito bom receber um feedback aqui de que funcionou. Mas, a menos que você tenha a sorte de que o 1098 na célula A2 seja uma duplicata de alguma outra célula, a condição não é verdadeira e parece que nada aconteceu.
Você precisa copiar a formatação condicional de A2 para as outras células em seu intervalo. Com o cursor do peitoril em A2, faça Editar> Copiar. Pressione Ctrl + Barra de espaço para selecionar a coluna inteira. Faça Editar> Colar especial. Na caixa de diálogo Colar especial, clique em Formatos. Clique OK.
Isso copiará a formatação condicional para todas as células da coluna. Agora - finalmente - você vê algumas células com a formatação vermelha, indicando que você tem uma duplicata.
É informativo ir para a célula A3 e olhar o formato condicional após a cópia. Selecione A3, pressione od para abrir a formatação condicional. A fórmula na caixa Fórmula é alterada para contar quantas vezes A3 aparece na coluna A: A.
Notas
Na pergunta de Joe, ele tinha apenas 1.700 faturas no intervalo. Eu configurei 65536 células com formatação condicional e cada célula está comparando a célula atual com 65536 outras células. No Excel 2005 - com mais linhas - o problema será ainda pior. Tecnicamente, a fórmula da primeira etapa poderia ter sido:=COUNTIF($A$2:$A$1751,A2)>1
Além disso, ao copiar o formato condicional para a coluna inteira, você poderia, em vez disso, ter selecionado apenas as linhas com dados antes de fazer Colar formatos especiais.
Mais
O outro problema que descrevi após a pergunta é que você realmente não pode classificar uma coluna com base em um formato condicional. Se você precisar classificar esses dados para que as duplicatas fiquem em uma área, siga estas etapas. Primeiro, adicione um título a B1 chamado "Duplicar?". Digite esta fórmula em B2: =COUNTIF(A:A,A2)>1.
Com o ponteiro da célula em B2, clique na alça de preenchimento automático (o pequeno quadrado no canto inferior direito da célula) para copiar a fórmula em todo o intervalo.
Agora você pode classificar pela coluna B decrescente e A crescente para ter as faturas problemáticas no topo da faixa.
Esta solução pressupõe que você deseja destacar AMBAS as faturas duplicadas para que possa descobrir manualmente quais excluir ou corrigir. Se você não deseja marcar a primeira ocorrência do duplicado, você pode ajustar a fórmula para ser: =COUNTIF($A$2:$A2,A2)>1. É importante inserir os cifrões exatamente como mostrado. Isso examinará todas as células apenas da célula atual, procurando entradas duplicadas.
Obrigado ao Joe de Boston pela pergunta!
