Todo mês você exporta a lista de clientes do sistema de faturamento ou a de assinantes da sua newsletter e precisa saber o que mudou em relação ao mês anterior: quem é novo, quem saiu e de quem o e-mail foi alterado. Com dois arquivos de centenas de linhas, comparar na mão não é uma opção.
Aqui está um método organizado para detectar entradas, saídas e alterações com fórmulas do Excel, com dados de exemplo e os erros que mais geram resultados falsos.
Antes de começar: escolha uma chave única
Para comparar duas listas, você precisa de um campo que identifique cada cliente sem ambiguidade e que não mude com o tempo. É a chave. As opções habituais, da melhor para a pior:
- Um código de cliente (C-001, C-002…). É o ideal: não se repete e não muda mesmo que o cliente atualize o e-mail.
- O e-mail. Serve para listas de assinantes, mas se alguém trocar de e-mail vai aparecer como uma saída e uma entrada.
- O nome. É a pior opção: existem nomes repetidos e a mesma pessoa pode estar escrita de várias formas.
Copie as duas versões da lista para a mesma pasta de trabalho, em planilhas chamadas Anterior e Atual. Trabalhar em um único arquivo simplifica as fórmulas e evita referências a pastas de trabalho fechadas.
Os dados de exemplo
Planilha Anterior (dados em A2:C5):
| A: ID | B: Nome | C: E-mail |
|---|---|---|
| C-001 | Cliente A | clientea@exemplo.com |
| C-002 | Cliente B | clienteb@exemplo.com |
| C-003 | Cliente C | clientec@exemplo.com |
| C-004 | Cliente D | cliented@exemplo.com |
Planilha Atual (dados em A2:C5):
| A: ID | B: Nome | C: E-mail |
|---|---|---|
| C-001 | Cliente A | clientea@exemplo.com |
| C-002 | Cliente B | vendas.b@exemplo.com |
| C-004 | Cliente D | cliented@exemplo.com |
| C-005 | Cliente E | clientee@exemplo.com |
À primeira vista: C-005 é uma entrada, C-003 é uma saída e C-002 trocou de e-mail. Vamos ver como o Excel detecta isso.
Detectar entradas e saídas com CONT.SE
Entradas: estão em Atual, mas não em Anterior
Na planilha Atual, digite o cabeçalho “Status” em D1 e esta fórmula em D2. Depois copie-a para baixo:
=SE(CONT.SE(Anterior!$A$2:$A$5;A2)=0;"Novo";"Já existia")
CONT.SE conta quantas vezes o ID aparece na planilha Anterior. Se o resultado for zero, o cliente é novo. Só C-005 aparece como “Novo”.
Saídas: estavam em Anterior e não estão mais
Faça o mesmo na planilha Anterior, procurando na outra direção:
=SE(CONT.SE(Atual!$A$2:$A$5;A2)=0;"Saiu";"Continua")
C-003 fica marcado como “Saiu”. Com seus dados reais, ajuste os intervalos até a última linha ou, melhor ainda, transforme cada lista em tabela em Inserir › Tabela para que os intervalos cresçam sozinhos.
Detectar alterações nos dados
O fato de um cliente continuar na lista não significa que seus dados sejam os mesmos. Para conferir o e-mail, traga o valor anterior com PROCV e compare-o com o atual. Esta fórmula, na coluna D da planilha Atual, substitui a de entradas e resolve tudo de uma vez:
=SE(CONT.SE(Anterior!$A$2:$A$5;A2)=0;"Novo";SE(PROCV(A2;Anterior!$A$2:$C$5;3;FALSO)=C2;"Sem alterações";"E-mail alterado"))
Primeiro ela verifica se o ID existe na lista anterior. Se existir, busca o e-mail que ele tinha (coluna 3 do intervalo) e o compara com o da linha atual. Resultado: C-001 e C-004 “Sem alterações”, C-002 “E-mail alterado” e C-005 “Novo”.
Conferir vários campos
Para acompanhar também o nome, adicione outra coluna com a mesma ideia, trocando o número da coluna e a célula comparada:
=SE(CONT.SE(Anterior!$A$2:$A$5;A2)=0;"";SE(PROCV(A2;Anterior!$A$2:$C$5;2;FALSO)=B2;"";"Nome alterado"))
Lembre-se de que a comparação com = não diferencia maiúsculas: “Cliente b” e “Cliente B” são considerados iguais. Para um e-mail, é exatamente o que você quer.
Listas automáticas de entradas, saídas e alterações no Excel 365
Se você tem o Excel 365 ou o Excel 2021, FILTRO retorna cada grupo completo, com todas as colunas, em uma planilha de resumo. Entradas:
=FILTRO(Atual!A2:C5;CONT.SE(Anterior!A2:A5;Atual!A2:A5)=0;"Sem entradas")
Saídas:
=FILTRO(Anterior!A2:C5;CONT.SE(Atual!A2:A5;Anterior!A2:A5)=0;"Sem saídas")
Alterações de e-mail, usando PROCX:
=FILTRO(Atual!A2:C5;SEERRO(PROCX(Atual!A2:A5;Anterior!A2:A5;Anterior!C2:C5)<>Atual!C2:C5;FALSO);"Sem alterações")
SEERRO impede que as entradas, que não existem na lista anterior e retornariam #N/D, quebrem o resultado. Deixe espaço livre abaixo de cada fórmula: se as células de destino estiverem ocupadas, você verá o erro #DESPEJAR!.
Listas de e-mails sem código de cliente
Em uma lista de assinantes, o normal é que a única chave seja o e-mail. Antes de comparar, padronize-o, porque “Ana@Exemplo.com ” e “ana@exemplo.com” são o mesmo e-mail para você, mas nem sempre para o Excel:
- Insira uma coluna auxiliar ao lado do e-mail nas duas planilhas.
- Digite
=MINÚSCULA(ARRUMAR(B2)), supondo que o e-mail esteja na coluna B, e copie para baixo. - Use essa coluna como chave nas fórmulas de
CONT.SEdas seções anteriores. - Revise as entradas e saídas com o mesmo nome: podem ser a mesma pessoa com um e-mail novo.
ARRUMAR remove os espaços excedentes e MINÚSCULA converte tudo para minúsculas, o que torna a comparação muito mais confiável.
Erros comuns e como evitá-los
- Duplicados em uma das listas. Se um cliente aparece duas vezes, as contagens deixam de ser claras e o
PROCVsó olha a primeira ocorrência. Limpe-os antes com nossa ferramenta para remover duplicados. - IDs em formatos diferentes. “C-001” e “C001”, ou 1 e “0001”, não coincidem. Padronize o formato antes de comparar.
- Intervalos que não chegam ao fim. Se você adicionar linhas e não ampliar o intervalo, os novos clientes vão aparecer como entradas ou saídas falsas.
- Esquecer o FALSO no PROCV. Sem esse último argumento, a busca é aproximada e pode retornar dados de outro cliente.
- Trabalhar no arquivo original. Faça uma cópia. São dados pessoais: guarde-os com cuidado e não os compartilhe mais do que o necessário.
Quando as listas têm muitas colunas (telefone, endereço, categoria, vendedor responsável…), criar uma fórmula por campo fica cansativo. Nesses casos, você pode usar nossa ferramenta gratuita para comparar arquivos Excel: envie a versão anterior como original e a nova como modificada e receba um arquivo com as alterações entre as duas. Depois, use as fórmulas deste artigo para classificar o que interessar.
Conclusão
Comparar duas listas de clientes se resume a três perguntas: quem é novo, quem saiu e o que mudou. Com uma chave única, CONT.SE responde às duas primeiras e PROCV ou PROCX à terceira. Se você padronizar os e-mails, eliminar duplicados e fixar bem os intervalos, terá um controle mensal confiável que pode repetir em poucos minutos.
Perguntas frequentes
O que acontece se um cliente trocar de e-mail e eu não tiver um código de cliente?
O Excel vai ver isso como uma saída (o e-mail antigo) e uma entrada (o novo). Nenhuma fórmula consegue adivinhar que é a mesma pessoa; revise manualmente as entradas e saídas que tenham o mesmo nome ou telefone.
Posso comparar listas que estão em dois arquivos diferentes?
Sim, as fórmulas aceitam referências a outra pasta de trabalho, mas as duas devem estar abertas para atualizar sem problemas. É mais prático copiar as duas planilhas para a mesma pasta de trabalho antes de começar.
As fórmulas diferenciam maiúsculas nos e-mails?
Não. Nem CONT.SE nem a comparação com = diferenciam maiúsculas, o que é conveniente, porque na prática os e-mails não levam isso em conta. Os espaços contam, por isso vale a pena usar ARRUMAR.
Funciona com listas de milhares de linhas?
Sim. CONT.SE e PROCV lidam sem problema com dezenas de milhares de linhas, embora o cálculo possa demorar um pouco mais. Se a pasta de trabalho ficar lenta, copie os resultados e cole-os como valores quando terminar.
Experimente nossas ferramentas gratuitas
Compare, converta, junte e limpe suas planilhas pelo navegador, sem instalar nada.
